Oracle Applications DBA Blog

The Case.. The Mess up and The Quick Fix or Re Creating APPS Synonyms without using ADADMIN

My current post(or a story rather) deals about a real life mess up and the quck solution we did to fix it with the least possible down time.
The content in this post is scritly for informational purposes only. DO NOT try this method even on your TEST or Development environment.

The Case
We all know that the APPS schema in Oracle Applications does not really hold many objects but holds synonyms to all the application objects in various schemas.
Also it is a common practice for the development environment to have a schema similar to the apps schema in Oracle Applications with only read access.
Some of these synonyms had gone invalid after a cloning process and were giving a synonym translation invalid error. It was  decided to drop and recreate all the synonyms for the READ ONLY schema.

A dynamic script was crated for dropping all the synonyms form this schema.

spool /tmp/dropsynonym.sql
select 'drop synonym '||SYNONYM_NAME ||';' from user_synonyms;
spool off

This was supposed to be executed in the READ ONLY schema.

After this the synonyms were to be recreated again using another script

spool /tmp/synonym.sql
select distinct 'create synonym "'||object_name||'" for '||owner||'."'||object_n
ame||'";' from dba_objects where owner = 'APPS' ;
spool off

The above process worked fine always and seemed like a fairly tale.. until one fine day..

The Mess up
It all looked OK that day until a poor soul did this.

The drop synonym script was executed from the APPS schema instead of the APPS read only schema. The result.. All the  synonyms from the APPS user got knocked off. Everything in Oracle Applications came crashing down.

(This is the part where my pager starts beeping..)

After we realized what had happened the first thought to strike my mind was to run adadmin and re create the grants and  synonyms. But as expected adadmin also refused to work in the absence of the apps synonyms. Classic!!

The Quick Fix
We did have multiple environments of oracle applications at this particular client so we just made up a script to create all the synonyms again from a working similar instance. We this script in the APPS schema of the working environment.

spool fixsynonym.sql
select 'create synonym "'||synonym_name||'" for '||table_owner||'."'||table_name||'";' from user_synonyms;
spool off

Next we ran the fixsynonym.sql from the apps schema of the environment which had all its synonyms deleted.
Once done we were able to get back almost all of our synonyms and atleast were able to get adadmin working back again.

Next we ran adadmin and selected to recreate the grants and sysnonyms for the APPS Schema.
We did a clean shutdown of the instance and then brought it up again.. and it was back to perfect.

Posted by Sam at 02:22 AM | Permalink | Comments (13) | TrackBacks (0)

RMAN for an APPS DBA

Its been long since i but up a post here. But have been tied up with a new project lately, i also apologize that i could not get back to your comments in time but will have them answered as soon as possible.
Also lately i have been working with RMAN, and found out that it has a steep learning curve but once you are through with the initial startup it actually is fun to use. RMAN or the Oracle Recovery Manager is a vast topic so i am just touching up the day to day commands that you might probably use and need to understand to get going.

Connecting to RMAN.
You can connect to the RMAN with or without the recovery catalog. there are multiple ways for connecting to RAMN.You can get the rman prompt by simply typing RMAN

RMAN>

you can connect to the target database either by using a username and password or with supplying the username and password as the database owner.

RMAN> connect target username/password@string
RMAN> connect target /

If you wish to connect to the rman catalog you can use the connect catalog command

RMAN> connect catalog username/password@string

To connect to an AUXILIARY database (maostly used for duplicating a database for cloning) use the CONNECT AUXILIARY command

RMAN> connect AUXILIARY username/password@string

again if you wish to connect as the owner of the AUXILIARY database you could use a '/'

RMAN> connect AUXILIARY /


RMAN CHANNELS

The ALLOCATE CHANNEL command is used to establish a channel between the RMAN and the database instance. You can either allocate a single channel for your whole backup job or specify multiple channels, once RMAN finishes the job with a  particular channel it releases that channel. The allocate channel is specified within the run braces.

Additionally you can use the DEVICE TYPE clause to  specify either a DISK to the channel or a TAPE.

RUN
{
  ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
  ....................................

}

In the above case a channel is allocated for RAMN and the device type is TAPE.

Along with the ALLOCATE CHANNEL command you can also specify the PRAMS command to use the media management library of your media manager.

allocate channel ch1 type 'sbt_tape' parms
        'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.samdb)';

The above example shows the prams parameter for a Tivoli Storage Media.

BACKUP using RMAN
You can use the backup command to backup your database,tablespace, datafile , control file , SPFILE, archived log, or backup set.

To backup the database

RMAN>BACKUP DATABASE;

To backup individual tablespaces use the command

RMAN>BACKUP TABLESPACE GLD, GLX;

To backup database along with the archivelogs

RMAN>BACKUP DATABASE PLUS ARCHIVELOG;

To backup the SPFILE of the target database.

RMAN>BACKUP SPFILE:

To backup your controlfile

RMAN>BACKUP CURRENT CONTROLFILE;

You can also use the INCLUDE CONTROLFILE option to include the backup of your current control file along with any other backups you may perform.

RMAN>BACKUP DATABASE PLUS ARCHIVELOG INCLUDE CURRENT CONTROLFILE;

This backups your database, archive logs and also a copy of your current control file.

You could also TAG your backups by adding the TAG clause along with your BACKUP command.

RMAN>BACKUP DATABASE TAG = 'HOT SAMDB 050107 BACKUP'

Incremental Backups
You can have RMAN backup only the blocks that have changed after the last backup. There are two levels of incremental backups in RMAN.

Level 0 and Level 1

The Level 0 backup is almost similar to a full database backup. All subsequent incremental backups will be based on this backup.

The Level 1 backup is again categorized into types

Differential - This backs up all the blocks that have changed between the most recent level 1 or Level 0 backup
Cumulative - This backups all the blocks that have changed between the most recent level 0 backup.

By default incremental backups are differential

You must have Level 0 backup before you can use incremental backups.

RMAN>BACKUP INCREMENTAL LEVEL 0 DATABASE;

This creates a level 0 incremental backup of your database.

RMAN>BACKUP INCREMENTAL LEVEL 1 DATABASE;

This command will backup all the blocks that have changed since the last Level 0 or Level 1 backup.

Listing RMAN Backups

The LIST command is used for listing the backups taken from RMAN.

To list the backup of the database

RMAN>LIST BACKUP OF DATABASE;

To list a copy of a datafile

RMAN>LIST COPY OF DATAFILE 'sam/oradata/system01.dbf';

To list a backup of a database taken between two dates

RMAN>LIST BACKUP OF DATABASE COMPLETED BETWEEN '01-APR-2007' AND '08-MAY-2007';

you can also use the completed clause to list backup copies of datafiles between two dates.

To list backups by tag use

RMAN>LIST BACKUP TAG 'HOT SAMDB 050107 BACKUP';

REPORT

You can use the REPORT command to determine which are the files that need to be backed.

RMAN>REPORT NEED BACKUP;

This lists the files that need to be backed up based on the retention policy in place.

RMAN RESTORE

The RESTORE command can be used to restore backups from RMAN, you can restore a database, tablespace,  datafile , control file,SPFILE or archived log.

You can either allocate or configure channels for your restore operation or let RMAN allocate channels manually for the restore.

The following example shows a simple, complete database restore and recovery

RUN
{
  RESTORE DATABASE;
  RECOVER DATABASE;
}


This example show restoring a SPFILE

RMAN>RESTORE SPFILE

This will restore in the default location

RMAN>RESTORE SPFILE TO '/tmp/spfileTEMP.ora'

This will restore in a location other than the default.

Cloning with RMAN

If you are doing a hot cloning of your instance you can use RMAN do to the database cloning and then run adcfgclone with the DBTechStack option, as a part of your Oracle Applications cloning.

To clone an database using RMAN you must
Connect to the target (Source instance)
Connect to the recovery catalog
Connect to the auxiliary (to be cloned instance)

To restore the dbf files in the new server you cane use the SET NEWNAME FOR DATAFILE command within the RUN braces as below

run {
set newname for datafile 1 to '/u01/sam//oradata/sys01.dbf';
set newname for datafile 2 to ..............................

likewise for all your database files.

In the end use the DUPLICATE command to clone the database.

RMAN>duplicate target database to samcldb;

Posted by Sam at 11:33 PM | Permalink | Comments (3) | TrackBacks (0)