Posts Tagged ‘Database DBA’

Oracle Database : How to Shutdown Oracle Database – Shutdown Basics

October 3rd, 2018, posted in Oracle Queries

Oracle Database and Instance

The Database is a set of physical operating system files. These files actually holds the user data and the metadata (or the data dictionary). Every running Oracle database is associated with (atleast) an Oracle instance. The Instance refers to the set of Oracle background processes or threads and a shared memory area (SGA). An instance can mount and open at most one database in its life. A database may be mounted and opened by one or more instances (using RAC) and the number of instances mounting a single database can fluctuate over time.Problem sys@standby> startup mount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2217952 bytes Variable Size 490735648 bytes Database Buffers 335544320 bytes Redo Buffers 6606848 bytes Database mounted. sys@standby> alter database recover managed standby database using current logfile disconnect; alter database recover managed standby database using current logfile disconnect * ERROR at line 1: ORA-01153: an incompatible media recovery is active Cause This indicates a currently running media recovery process. Action sys@standby> alter database recover managed standby database cancel; sys@standby> alter database recover managed standby database using current logfile disconnect; Note When shutting down physical standby database, firstly turn off media recovery process. Otherwise the next time when starting up redo apply again, you will encounter error ORA-01153.

Database Shutdown

During a database shutdown we close the database and terminates the instance.

Different Modes in Database Shutdown
There are different modes to bring down the database:
1. Shutdown immediate
2. Shutdown transactional
3. Shutdown normal
4. Shutdown abort
No user session will be permitted once you issue any of these Shutdown commands.

Shutdown Immediate

– Oracle Database terminates any executing SQL statements and disconnects users.
– Active transactions are terminated and uncommitted changes are rolled back.
– Oracle then performs a checkpoint and then close the online datafiles.

$ sqlplus / as sysdba
SQL> shutdown immediate

Shutdown Transactional

– This mode prevents users from starting new transactions, but waits for all current transactions to complete before shutting down.
– Oracle then performs a checkpoint and then close the online datafiles.

$ sqlplus / as sysdba
SQL> shutdown transactional

Shutdown Normal

– The database waits for all connected users to disconnect before shutting down.
– It waits till all the current transactions end.
– Oracle then performs a checkpoint and then close the online datafiles.

$ sqlplus / as sysdba
SQL> shutdown normal

Shutdown Abort

– Oracle Closes the datafiles without any checkpoint.
– This is the fastest shutdown mode.
– Instance recovery is required in the next startup and hence it will take time.

$ sqlplus / as sysdba
SQL> shutdown abort

Different Phases in Database Shutdown

Close the Database

– Oracle writes the data in the SGA to the disk, updates the file headers and closes the online datafiles and the redo log files.
– But the database will still be mounted.

Dismount the Database

– After the database is closed, Oracle Database unmounts the database to disassociate it from the instance.
– After a database is unmounted, Oracle Database closes the control files of the database.
– At this point, the instance remains in memory.

Shutdown the Instance

– The last step is to remove the shared memory (SGA) and terminate the background processes.
Sometimes shutdown does not cleanup the SGA or background process completely. This can cause error during the next startup. In such situation we can force a instance startup


Oracle Database : How to Startup Oracle Database – Startup Basics

October 1st, 2018, posted in Oracle Queries

Oracle Database and Instance

The Database is a set of physical operating system files. These files actually holds the user data and the metadata (or the data dictionary). Every running Oracle database is associated with (atleast) an Oracle instance. The Instance refers to the set of Oracle background processes or threads and a shared memory area (SGA). An instance can mount and open at most one database in its life. A database may be mounted and opened by one or more instances (using RAC) and the number of instances mounting a single database can fluctuate over time.

Database statup

For any normal user to access the database, the instance has to be started up and it should mount and open the corresponding database. We term the entire steps as database startup. In short, database startup includes the following steps:

1. Start an instance.
2. Mount the database.
3. Open the database.

Database startup requires SYSDBA privilege. To start a database :

$ export ORACLE_SID=[SID of the instance]
$ export ORACLE_HOME= [location of ORACLE_HOME]
$ sqlplus / as sysdba
SQL> startup

The above syntax assumes you have a pfile or spfile in the default location ($ORACLE_HOME/dbs). If you are using a non default parameter file, the startup command is:

$ sqlplus / as sysdba
SQL> startup pfile=[file name and location]

Nomount stage

When you issue a ‘Startup’ command, this is the first stage. ie, starting up the instance. Here,
– Oracle will read the parameter file (spfile or pfile) in the default location or in the location specified in the startup command.
– It will then allocate the memory area (SGA) and starts the background processes. Together we call it as the instance.
– Please note that no database is associated with the instance at this point. We can start the instance alone using the command:

$ sqlplus / as sysdba
SQL> startup nomount
ORACLE instance started.

Total System Global Area 304807936 bytes
Fixed Size 2221000 bytes
Variable Size 113249336 bytes
Database Buffers 184549376 bytes
Redo Buffers 4788224 bytes

The instance is now started and the database is in nomount stage. Starting the instance in nomount stage is usually required for database creation or for creating or recovering the controlfiles.

Mount stage

– In this phase the instance will mount the database.
– Mounting the instance means associating the started instance with a specified database.
– For this, the instance checks the controlfiles specified under CONTROL_FILES parameter and opens it.
– It then reads the control files to find the names of the data files and the online redo log files that it will attempt to access when opening the database.
– The database is still closed and only the DBA can access it.
– This stage is normally used for maintenance operations like renaming datafiles, enabling and disabling archiving options.
– Adding, dropping, or renaming redo log files is also done in mount stage.
– For performing full database recovery database is opened in mount stage.

To mount a database:

$ sqlplus / as sysdba
SQL> startup mount

To mount a database from nomount stage:

$ sqlplus / as sysdba
SQL> alter database mount

Open stage

– This is the final stage and here Oracle opens the online datafiles and redolog files.
– If any of the required files are not present, media recovery is required .
– It also ensures the datafiles are consistent. Incase of a normal shutdown, the in-memory changes will be written to disk as part of the shutdown checkpoint.
– But if the instance crashed (or shutdown abort), then Oracle Database performs the instance recovery in the next startup.Oracle : Size Of Database,Oracle,Size Of Database,Database,data files, redo log files, control files, temporary files,Oracle data files,Oracle redo log files,Oracle control files,Oracle temporary files,Enabling And Checking the Status of Flashback On Database,Oracle Database,Oracle DBA,Enabling Flashback On Database,Checking the Status of Flashback On Database, Status of Flashback On Database, Enable Flashback On Database, Enabling Flashback On Database,Enable Flashback On Oracle Database, Enabling Flashback On Oracle Database,

To open a database :

$ sqlplus / as sysdba
SQL> startup

To open a database from mount stage:

$ sqlplus / as sysdba
SQL> alter database open

To verify :

SQL> select open_mode from v$database;


AD ADMIN Utilities

September 17th, 2018, posted in Oracle Queries, Solaris, Uncategorized

Oracle application,oracle AdAdmin,Oracle application AdAdmin,oracle apps,oracle apps envirnoment,Oracle DBA,Oracle Application DBA,Oracle Database DBA,Application DBA,Database DBA,AdAdmin


Ad admin is a utility which is used to perform

Login as a Applmgr user:
[applmgr@apps ~]$ adadmin

Preliminary Tasks:

1. Running the environment file.
2. Verifying that ORACLE_HOME set properly.
3. Ensuring that ORACLE_HOME/bin and AD_TOP/bin are in your path.
4. Shutting down the concurrent managers when relinking certain files or performing certain database tasks.
5. Ensuring Sufficient temporary disk space.

Ad administration prompts:
Your default directory is ‘/u01/app/apps/uatappl’.
Is this the correct APPL_TOP [Yes]

Filename [adadmin.log] :

APPL_TOP is set to /u01/app/apps/uatappl

Please enter the batchsize [1000] :

Enter the password for your ‘SYSTEM’ ORACLE schema: manager

Enter the ORACLE password of Application Object Library [APPS] : apps

Ad admin Log files:

The main AD Administration log file is called adadmin.log by default. This name can be
Changed when starting up AD Administration.

Errors and warnings are listed in the log file

General Applications file menu

There are five functional choices in the generate applications file menu.

1. Generate Message Files:
This task generates message binary files in the $PROD_TOP/mesg directory from oracle application object library tables.

We generally perform this task only when instructed to do so in a readme file of a patch.

2. Generate Form Files:This task generate binary oracle forms file for all installed languages from the form definition files. Extension (*.fmx)

Perform this task whenever we have issue with a form or set forms.

Oracle application uses these binary files to display the data entry forms.

When we choose Generate form files:
It prompts following:

Generate Report files:
This task generates binary report files for all installed languages. Extension of the file name like (*.rdf)

When we choose Generate report files menu, it prompts the following.

Generate Graphics files:
This task generates Oracle graphics files for all installed languages. Extension of the file name like (*.ogd)

The serious of prompts and actions in this task are very similar to the prompts and actions in the Generate form files task.

Generate Product JAR files:
This generate product jar files task prompts
Do you wish to force generation of all jar files? [No]
If we choose No, it only generates JAR (Jave Archive) files that are missing or out of date.
Choose yes for this option when generating JAR files after upgrading the developer technology stack or after updating your Java version.

Maintain Applications Files tasks

Relink Application programs:
This task relinks all your oracle applications binary executables.
Select this task after us:
1. Install new version of the database or a technology stack component.
2. Install an underlying technology component used with oracle applications.
3. Apply a patch to the application technology stack.
4. Apply a patch to the operating systems

These tasks execute AD relink utility. Use AD admin, not the AD relink utility directly, to relink non AD-executables.
Create Applications environment file:
Select this task when you want to:
1. Create an environment file with settings that are different from your current environment file.
2. Recreate an environment file that is missing or currept.

Copy files to destinations:
The copy files to destinations task copies files from each product area to central locations where they can easily referenced by oracle applications.

Use this option to update the java, HTML and media files in the common directories (such as JAVA_TOP, OA_TOP) when users have issues accessing them.

1. Java file are copied to JAVA_TOP
2. HTML files are copied to OAH_TOP
3. Media files are copied to OAM_TOP

Convert Character Set:

1. This task converts the character set of all translatable files in APPL_TOP.
2. You should select this task when changing the base language or adding additional languages to oracle applications
3. You may need to convert database character set and file system character set to one that will support the additional languages.

Maintain snapshot information:

1. This task record details for each file in the APPL_TOP (like file name and file version).
2. They also record summary information’s about patches that have been applied to the APPL_TOP.
3. The maintain snapshot information task stores information about files, file versions and bug fixes present in an APPL_TOP.
4. You must run Maintain snapshot information option once for each APPL_TOP before you apply any patch that contains a “compatible feature prereq” line on that APPL_TOP.

Check for Missing files:

1. The check for missing files task verifies files needed to install, upgrade, or run oracle applications for the current configuration are in the current APPL_TOP.
2. Choose this task if you suspect there are files missing in your APPL_TOP.

Maintain Database Entries tasks:

Validate Apps Schema:

Validate apps schema task run SQL script (advrfapp.sql) against the apps schema to verify the integrity of the schema.
It determines:

1. Problems you must fix(not specific to apps schema)
2. problems you must fix(specific to apps schema)
3. Issues you may want to address(specific to apps schema)
A report called APPSschemaname.lst is produced in APPL_TOP/admin//out.
This report contains information about how to fix the issues. We can find following things by running the Validate Apps schema.

1. Missing or invalid package.
2. Missing or invalid synonyms.
3. Invalid objects in apps schema.

This task is more effective if run:

1. Immediately after an upgrading or applying maintenance pack
2. After a patch is applied.
3. After performing export/import (migration)
4. When doing custom development in the apps schema.

Recreate Grants and Synonyms:

This task recreates grants and synonyms for oracle application public schema (applsyspub)
Recreate grants on some packages from system to apps

Run this task when grants and synonyms are missing from the database. This may occur as a result of
1. Custom development
2. Incomplete database migrations
3. Patches and administrative sessions that failed to run successfully to completion

Maintain multi-lingual tables:

MLS or multilingual support is oracle application’s ability to operate in a multi languages simultaneously. When running Maintain multi-lingual task you can select the number of parallel workers. In generally run during the NLS install and maintenance processes. This task runs the NLINS.sql script for every product. It invokes pl/sql routines that maintain multilingual tables and untranslated rows.

Check Dual Tables:

This task looks for a dual table accessible by oracle applications and ensures the correct grants are set up. If such table not exists or if an existing DUAL table has more than one row, AD administration displays error. If a DUAL table containing only one row exists, AD admin completes successfully.

Maintain multiple reporting currencies:

This option varies depending on whether you currently have multiple reporting currencies (MRC) enabled or not.
If MRC functionality is implemented in your database, the option reads maintain multiple reporting currencies.

Convert to multiple organizations:

To convert in to multiple-org does the following thing:

1. Confirms that you want to run the task
2. Asks for the number of parallel workers
3. Create script to disable and re-enable triggers in the APPS schema
4. Disable all triggers in the apps schema
5. Converts seed data and transaction data to multiple organizations in parallel.
6. Re-enable all previously disabled triggers in the apps schema.

Compile and Reload Database Entries tasks:

Compile Apps Schema:

This task compiles uncompiled program units (pl/sql and java) in the apps schema.
You can perform this task with multiple workers.
When running this task, AD administration prompts,
Run Invoker’s Rights processing in incremental mode [No]?
Type Yes at this prompt to run Invoker Rights processing only on packages that have changed
Since Invoker Rights processing was last run or accept the default to run Invoker Rights
Processing on all packages.
During the upgrade progress.

Compile Menu Information:

This option compiles menu data structures.
Choose this task after uploading menu entries.

It asks if you want to force compilation for all menus,

1. If you choose the default [no] only menus with changes are saved
2. If you enter yes all menus are compiled

Compile flexfield:

Run this task if the readme of a patch indicates that this step should be performed.
Details of the task with a list of compilation status of every flexfield are written to a log file.
The name of the log file is in the format .req. The main AD Administration log file contains the exact name of this log file.

Reload JAR Files to Database:

This option runs the loadjava utility to reload all appropriate oracle applications
JAR files into the database.

Change Maintenance mode:

1. Must be enabled before patching oracle applications
2. Improves patching performance
3. Restricts users access to system
4. Is enabled and disabled using AD administration


Workflow : Workflow Component wfcmp

June 9th, 2018, posted in Oracle Queries

Oracle Applicaition,Oracle Database,Oracle DBA,Oracle Applicaition DBA,Oracle Database DBA,Applicaition DBA,Database DBA,Oracle EBS Applicaition DBA,Oracle EBS Database DBA,Oracle,Oracle Applicaition,Oracle Database,Workflow : Workflow Component wfcmp,Oracle Applicaition Workflow,Oracle Database Workflow,oracle ebs logo,oracle ebs logo,oracle e business suite logo,

Query :

from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)

Select Number And Convert Number From English To Arabic In Oracle

April 23rd, 2018, posted in Oracle Queries
CREATE FUNCTION numToEasternArabic(
  in_value IN NUMBER
  p_num  VARCHAR2(100) := TO_CHAR( in_value );
  p_char CHAR(1);
  o_str  NVARCHAR2(100);
  FOR i IN 1 .. LENGTH( p_num ) LOOP
    p_char := SUBSTR( p_num, i, 1 );
    o_str := o_str
          || CASE p_char
             WHEN '.'
             THEN N'.'
             ELSE UNISTR(
                    '\' || TO_CHAR(
                             TO_NUMBER( p_char ) + 660,
  RETURN o_str;

Query 1:

SELECT numToEasternArabic( 1438 )


|                     ١٤٣٨ |