RMAN errors out with ORA-01008: not all variables bound

One of our DB has a problem with RMAN backups with the error as below.

DBGSQL:     TARGET> select count(*) into :dbstate from v$parameter where lower(name) = '_dummy_instance' and upper(value) = 'TRUE'
DBGSQL:        sqlcode = 1008
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-01008: not all variables bound

This is frustrating when doing any task with RMAN, even you can't log in to RMAN prompt as it says initialization of internal recovery manager package failed. The database version here is 11.2.0.2.0. Oracle support says this is related to bug 9877980 which happens when cursor sharing is set to force or similar.

We have cursor_sharing set to force. So obviously we have hit a bug.
The possible workaround is to just flush the shared pool. Yes, just flush the shared pool.

SQL> alter system flush shared_pool;
System altered.

Now you will be able to connect to the database through RMAN without any issues.

The detailed notes can be obtained from Oracle Support Doc ID 1472116.1.
By the way, the issue is fixed in 11.2.0.3 and above.

Happy troubleshooting...

APPLIED column on v$archived_log when Standby database is setup

We use a simple concept to check for any standby gap or logs apply problems in all the high availability (Dataguard) servers in our environment. Use the below query in a script and make it run from cron periodically so we don’t have to go and check manually all the time.

spool /home/oracle/dba_scripts/logs/standby_db_sync_err.txt

select PRIM-SEC from

(select MAX(SEQUENCE#) PRIM FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),

(select MAX(SEQUENCE#) SEC FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');

spool off

Today I received log applied gaps error alert mail stating we have a log applied gap of 24 logs. My script runs every hour and sends the alert mail only when the log applied gap crosses 10 logs since I have delay parameter set in the database.

Note: I used the term “log applied gaps” as I’m meaning that the term is used to differentiate the logs applied on primary with logs applied on standby database.

I first thought that there might be some activity going on in the database and the gap will catch up soon as this happens most of the time, but now the gaps increased to 35, the next hour. So something is wrong , and I should check it.

I used the following query on the standby database which gave the below.

SQL> select sequence#, process, status from v$managed_standby;

 SEQUENCE# PROCESS   STATUS

---------- --------- ------------

         0 ARCH      CONNECTED

         0 ARCH      CONNECTED

         0 ARCH      CONNECTED

         0 ARCH      CONNECTED

         0 ARCH      CONNECTED

     65999 MRP0      WAIT_FOR_LOG

     66007 RFS       IDLE

     66008 RFS       IDLE

     66009 RFS       IDLE

This is obvious, RFS gets the logs as and when generated at primary and MRP waits until the “delay” time reaches before it applies. The gap is not much but only 10 logs. Then I thought why the script gives wrong information?

I ran the script manually to check and still receive the same alert mail of log applied gap 35. So I ran the query separately in primary database to check/troubleshoot what is going on. I got the below response from the primary database.

SQL> select MAX(SEQUENCE#) PRIM FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES';

      PRIM

----------

     66010

SQL> select MAX(SEQUENCE#) SEC FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES';

       SEC

----------

     65975

Ok, I just wait for some time and check the same again but to see the same result. (Many a times, in standby environment I get the results delayed. J )

Now, it’s time to dig Oracle support documents to find what is really going on. I thought it might be some sort of bug as I haven’t faced such an issue before. As my prediction it turns to be a bug.

The MOS article “APPLIED - Column not updated if Heartbeat-ARCH hangs [ID 1369630.1]” states the issue we are dealing with is due to the bug “Bug 6113783 - Arch processes can hang indefinitely on network [ID 6113783.8]”.

The bug is fixed in 11.2.0.1 base release and I’m currently running my database in 10.2.0.4.

There are 2 options provided.

1. Upgrade DB to 11.2.0.1 or higher
2. Use the work around of killing the hung ARCH process in the primary database.

Option 2 looked feasible at this time as upgrading of the DB requires a lot of approvals, application compatibility, etc., etc.,.

So how to find the hung ARCH process?

MOS says Look for Messages like

"ARCn: Becoming the heartbeat ARCH"

in the ALERT.LOG of the Primary Database to determine the current Heartbeat ARCn-Process.

So I searched my alert log file to find the below.

Fri Aug 17 05:04:08 2012

ARC2: Becoming the heartbeat ARCH

ARC3 started with pid=103, OS id=28106

Fri Aug 17 05:04:08 2012

Check for the OS process id for ARC2

[oracle@slv00112 bdump]$ ps -ef|grep ora_arc

oracle   32224     1  0  2012 ?        02:43:53 ora_arc0_edisipd

oracle   32226     1  0  2012 ?        02:21:49 ora_arc1_edisipd

oracle   32232     1  0  2012 ?        02:41:07 ora_arc2_edisipd

oracle   32234     1  0  2012 ?        02:26:41 ora_arc3_edisipd

oracle   23429 16642  0 04:51 pts/1    00:00:00 grep ora_arc

Kill the Hung ARCH process

[oracle@slv00112 bdump]$ kill -9 32232

Now check the alert log file again to find the below.

ARCH: Detected ARCH process failure

ARCH: STARTING ARCH PROCESSES

ARC2: Archival started

ARCH: STARTING ARCH PROCESSES COMPLETE

ARC2 started with pid=67, OS id=23425

ARC2: Becoming the heartbeat ARCH

Wed Jan 16 04:53:47 2013

Ok, now the new process has been generated. Hence check with the primary database whether the APPLIED column is updated.

SQL> select MAX(SEQUENCE#) SEC FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES';

       SEC

----------

     66003

Now it has been updated successfully. J

A simple RMAN crosscheck mess up!!

Today I was attempting to rebuild a standby database as we had done some mess up with the production by placing some of the tables in "nologging" mode and relocating few datafiles due to file system space issues. It is easy to recover the standby by replacing the files from the primary and using the standby_management=manual mode to relocate the files in the standby as well. But the problem is we don't know where we started and where the situation is... Primary database is running fine after the maintenance but we lack the high availability as the standby is a day back. Hence I have decided to rebuild the standby which will take not more than 2~3 hours with a valid backup. Remember this is a database of version 10.2.0.4.0 running on Linux EL5.

Steps that has been done.

1. Backup taken from source database (Primary)

2. Backup pieces were moved to target server and the some of the backup pieces on the primary server is deleted as thinking as this DB is a 11g DB where we have feature of source database is not required to create clone. Then after realising the DB as 10g, again replaced the backups on the server.

3. Control file backup is taken for standby and transfered to the target server.

Now the actual story starts.

On target server.

RMAN> run

{

configure channel device type disk parallel 8;

duplicate target database for standby nofilenamecheck dorecover;

}

2> 3>

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found "parallel": expecting one of: "clear, connect, debug, format, kbytes, maxopenfiles, maxpiecesize, parms, readrate, rate, send, to, trace"

RMAN-01007: at line 3 column 36 file: standard input

RMAN>

Starting Duplicate Db at 06-AUG-12

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=541 devtype=DISK

allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: sid=540 devtype=DISK

allocated channel: ORA_AUX_DISK_3

channel ORA_AUX_DISK_3: sid=539 devtype=DISK

allocated channel: ORA_AUX_DISK_4

channel ORA_AUX_DISK_4: sid=538 devtype=DISK

allocated channel: ORA_AUX_DISK_5

channel ORA_AUX_DISK_5: sid=537 devtype=DISK

contents of Memory Script:

{

   set until scn  5170555877;

   restore clone standby controlfile;

   sql clone 'alter database mount standby database';

}

executing Memory Script

..

..

..

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 06-AUG-12

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=537 devtype=DISK

allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: sid=538 devtype=DISK

allocated channel: ORA_AUX_DISK_3

channel ORA_AUX_DISK_3: sid=539 devtype=DISK

allocated channel: ORA_AUX_DISK_4

channel ORA_AUX_DISK_4: sid=540 devtype=DISK

allocated channel: ORA_AUX_DISK_5

channel ORA_AUX_DISK_5: sid=541 devtype=DISK

creating datafile fno=2 name=/fs05/oradata/edisipd/edisipd_UNDOTBS_01.dbf

creating datafile fno=5 name=/fs05/oradata/edisipd/edisipd_UNDOTBS_02.dbf

creating datafile fno=8 name=/fs02/oradata/edisipd/edisipd_EDIAPP_DATA_03.dbf

creating datafile fno=15 name=/fs02/oradata/edisipd/edisipd_EDIAPP_DATA_10.dbf

creating datafile fno=17 name=/fs02/oradata/edisipd/edisipd_EDIAPP_DATA_12.dbf

creating datafile fno=18 name=/fs02/oradata/edisipd/edisipd_EDIAPP_DATA_13.dbf

creating datafile fno=19 name=/fs02/oradata/edisipd/edisipd_EDIAPP_DATA_14.dbf

creating datafile fno=23 name=/fs02/oradata/edisipd/edisipd_EDIAPP_DATA_18.dbf

..

..

..

using channel ORA_AUX_DISK_2

using channel ORA_AUX_DISK_3

using channel ORA_AUX_DISK_4

using channel ORA_AUX_DISK_5

starting media recovery

archive log thread 1 sequence 48527 is already on disk as file /fs01/oradata/edisipd/archive/edisipd_/1_724685163_48527.arc

Oracle Error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/fs02/oradata/edisipd/edisipd_SYSTEM_01.dbf'

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 08/07/2012 02:49:36

RMAN-03015: error occurred in stored script Memory Script

RMAN-06053: unable to perform media recovery because of missing log

RMAN-06025: no backup of log thread 1 seq 48412 lowscn 5159567844 found to restore

RMAN-06025: no backup of log thread 1 seq 48411 lowscn 5159485436 found to restore

RMAN-06025: no backup of log thread 1 seq 48410 lowscn 5159410674 found to restore

RMAN-06025: no backup of log thread 1 seq 48409 lowscn 5159350092 found to restore

RMAN-06025: no backup of log thread 1 seq 48408 lowscn 5159263205 found to restore

Check the part where it states "creating datafile fno=......

This is the first time I come across this statement in recovery (standby/clone) of a database. I'm not sure why it is creating file rather recovering it from the backup. I waited for sometime so that the recover completes.

Recover completes with error stating missing logs. Ok, this is expected as I don’t have some of the logs in the standby site.

Now, I started the recovery to see where the MRP stuck does, I mean which log MRP is looking for.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /fs01/oradata/edisipd/archive/edisipd_

Oldest online log sequence     48550

Next log sequence to archive   0

Current log sequence           48552

SQL> select process, sequence#, status from v$managed_Standby;

PROCESS    SEQUENCE# STATUS

--------- ---------- ------------

ARCH               0 CONNECTED

ARCH               0 CONNECTED

ARCH               0 CONNECTED

ARCH               0 CONNECTED

ARCH               0 CONNECTED

RFS                0 IDLE

RFS                0 IDLE

RFS                0 IDLE

MRP0               3 WAIT_FOR_GAP

9 rows selected.

This is to my shock!! MRP is waiting for sequence number 3. Man! This is a full backup you are recovering and why in the name of God, MRP looks for sequence# 3? I have to go years back for the log sequence# 3 :-)

I thought for a second and reran the create controlfile statement on the primary database to make sure I have the correct controlfile, transferred it to standby site and started recovery after mounting the database.

SQL> recover standby database;

ORA-00279: change 310139 generated at 07/18/2010 13:52:22 needed for thread 1

ORA-00289: suggestion :

/fs01/oradata/edisipd/archive/edisipd_/1_724685163_3.arc

ORA-00280: change 310139 for thread 1 is in sequence #3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00308: cannot open archived log

'/fs01/oradata/edisipd/archive/edisipd_/1_724685163_3.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

This again is looking for sequence# 3. So something is not right. But what is not right?

Then I checked manually myself with the backup pieces on both the servers, whether both the servers have equal number of files and their sizes. They are perfect.

Now I logged into primary database RMAN to see how many pieces have been produced while taking backup. 14 pieces have been produced and I have 14 pieces on the physical file system. So still I couldn't find what is wrong.

Now, once again I counted the number of pieces produced and got this..

RMAN> list backup tag ‘FORSTANDBY’;

..

..

..

  57      Full 5163187056 06-AUG-12 /fs04/oradata/edisipd/edisipd_EDIAPP_INDEX_12.dbf

  69      Full 5163187056 06-AUG-12 /fs05/oradata/edisipd/edisipd_EDIAPP_INDEX_24.dbf

  77      Full 5163187056 06-AUG-12 /fs06/oradata/edisipd/edisipd_EDIADT_DATA_02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

31223   Full    3.16G      DISK        00:28:35     06-AUG-12

        BP Key: 31464   Status: EXPIRED  Compressed: YES  Tag: FORSTANDBY

        Piece Name: /backups/stbkp/ForStandby_sknhuobu_1_1

  List of Datafiles in backup set 31223

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  23      Full 5163317451 06-AUG-12 /fs02/oradata/edisipd/edisipd_EDIAPP_DATA_18.dbf

  25      Full 5163317451 06-AUG-12 /fs02/oradata/edisipd/edisipd_EDIAPP_DATA_20.dbf

  54      Full 5163317451 06-AUG-12 /fs04/oradata/edisipd/edisipd_EDIAPP_INDEX_09.dbf

  64      Full 5163317451 06-AUG-12 /fs05/oradata/edisipd/edisipd_EDIAPP_INDEX_19.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

31224   Full    4.03G      DISK        00:45:33     06-AUG-12

        BP Key: 31465   Status: EXPIRED  Compressed: YES  Tag: FORSTANDBY

        Piece Name: /backups/stbkp/ForStandby_sinhunrb_1_1

  List of Datafiles in backup set 31224

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

..

..

..

Some of the backup pieces were in EXPIRED status. I now remember my colleague called me today morning to inform that she moved some of the backup pieces to other file system and issued the "crosscheck backup" RMAN command when the files where deleted from primary server after transfer to standby site. After this the backup pieces were restored to the file system so the files were not cataloged to the control file.

The reason why “create datafile” is issued by RMAN is now clear. Controlfile has the details about the datafiles but the backup pieces were missing to restore/recover. Hence it creates the datafiles on the given name and it should start recover from the beginning and hence looking for sequence# 3. My confusion is now at a halt. J

Now I crosschecked the pieces once again using RMAN crosscheck command so that these pieces were cataloged to the control file. Then I started the cloning for standby which ran perfect as expected.

For the detailed steps on creating a standby database using RMAN please check the below link.

http://oraclemamukutti.blogspot.in/2011/05/creating-standby-database-using-rman_04.html

Thank you!!

Recover archive gaps in standby database from primary using RMAN incremental backups

A Physical Standby database synchs with Primary by continuous apply of archive logs from a Primary Database. In case of an archive log gone missing or corrupt, We have to bring back the standby to sync with the primary.

When the logs missing or corrupt is less in number (say below 15), we can ship the logs which were missing in the standby site from the primary site (scp/sftp/ftp) and then we can register the log file in the standby so that the gap can be resolved.

Find the archives which are missing by issueing the following command.

SQL> select * from v$archive_gap;

This would give the gap sequences. Or you can use the v$managed_standby view to find where the log apply stuck.

SQL> select sequence#,process,status from v$managed_standby;

Here u can see status as wait for log for say sequence# 100 but your primary would've proceeded to sequence# 110

At primary

SQL> select max(sequence#) from v$archived_log;      ---> This would show you 110

Copy the logs to the standby site from the primary site

$ scp log_file_name_n.arc oracle@standby:/log/file/location/log_file_name_n.arc

At standby site

SQL> alter database register logfile '/log/file/location/log_file_name_n.arc';

logfile registered

Do the log file registration at the standby site until all the missing log files are registered. Now apply would take place and your standby will become sync with the primary.

This is easy process if you have missing or corrupt logs in lesser number. But when the difference is huge (say around 500 logs) this method is very time consuming and not a proper approach. Else you have to rebuild the standby database from scratch.

As an enhancement from 10g, an incremental backup created with BACKUP INCREMENTAL... FROM SCN can be used to refresh the standby database with changes at the primary database since the last SCN at Standby and then managed recovery can resume i.e. Compensate for the missing archive logs.

Let us see the steps involved.

Step 1: On the primary:

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

144710998

On the standby:

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

130158742

Clearly there is a difference. But this by itself does not indicate a problem; since the standby is expected to lag behind the primary (this is an asynchronous non-real time apply setup). The real question is how much it is lagging in the terms of wall clock.

To know that use the scn_to_timestamp function to translate the SCN to a timestamp:

SQL> select scn_to_timestamp(144710998) from dual;

SCN_TO_TIMESTAMP(1447102)

-------------------------------

18-AUG-11 08.54.28.000000000 AM

Run the same query to know the timestamp associated with the SCN of the standby database as well

SQL> select scn_to_timestamp(130158742) from dual;

SCN_TO_TIMESTAMP(1301571)

-------------------------------

13-AUG-11 07.19.27.000000000 PM

Note: Run it on the primary database, since it will fail in the standby in a mounted mode

This shows that the standby is four and half days lagging!

Step 2: [Standby] Stop the managed standby apply process:

SQL> alter database recover managed standby database cancel;

Database altered.

Step 3: [Standby] Shutdown the standby database

SQL> shut immediate

Step 4: [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck:

RMAN> run {

2> allocate channel c1 type disk format '/u01/backup/%U.bkp';

3> backup incremental from scn 130158740 database;

4> }

Step 5: [Primary] On the primary, create a new standby controlfile:

SQL> alter database create standby controlfile as '/u01/backup/for_standby.ctl';

Database altered.

or

RMAN> backup current controlfile for standby format '/backups/tempfol/_%U';

Step 6: [Primary] Copy these files to standby host:

oracle@dba1 /u01/backup]$ scp * oracle@dba2:/u01/backup

Step 7: [Standby] Bring up the instance in nomount mode:

SQL> startup nomount

Step 8: [Standby] Check the location of the controlfile:

SQL> show parameter control_files

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_files string /u01/oradata/standby_cntfile.ctl

Step 9: [Standby] Replace the controlfile with the one you just created in primary.

 $ cp /u01/backup/for_standby.ctl /u01/oradata/standby_cntfile.ctl

or

RMAN> RESTORE STANDBY CONTROLFILE FROM '/u01/backup/_v8o069ee_1_1';

Step 10: [Standby] Mount the standby database:

SQL> alter database mount standby database;

Step 11: [Standby] Connect to RMAN. RMAN does not know about these files yet; so you must let it know – by a process called cataloging. Catalog these files:

$ rman target=/

RMAN> catalog start with '/u01/backup';

Step 12: Recover these files:

RMAN> recover database;

Step 13: After some time, the recovery fails with the message:

archive log filename=/u01/oradata/1_18108_697108460.dbf thread=1 sequence=18109

ORA-00310: archived log contains sequence 18108; sequence 18109 required

This happens because we have come to the last of the archived logs. The expected archived log with sequence# 18108 has not been generated yet.

Step 14: At this point exit RMAN and start managed recovery process:

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Step 15: Check the SCN’s in primary and standby:

[Standby] SQL> select current_scn from v$database;

CURRENT_SCN

-----------

144747125

[Primary] SQL> select current_scn from v$database;

CURRENT_SCN

-----------

144747111

Now they are very close to each other. The standby has now caught up.

CREATING A STANDBY DATABASE USING RMAN STEPS - (UNIX / LINUX)

Overview

This article provides a quick introduction on how to create an Oracle standby database using the RMAN DUPLICATE TARGET DATABASE FOR STANDBY command on the UNIX / Linux operating environment. For this demonstration, we will create a standby database using RMAN's DUPLICATE command to a different host other than the primary database.

You can use the Recovery Manager DUPLICATE TARGET DATABASE FOR STANDBY command to create a standby database. RMAN automates the following steps of the creation procedure: 

1. Restores the standby control file.
2. Restores the primary datafile backups and copies.
3. Optionally, RMAN recovers the standby database (after the control file has been mounted) up to the specified time or to the latest archived redo log generated.
4. RMAN leaves the database mounted so that the user can activate it, place it in manual or managed recovery mode, or open it in read-only mode.

RMAN cannot fully automate creation of the standby database because you must manually create an initialization parameter file for the standby database, start the standby instance without mounting the control file, and perform any Oracle Net setup required before performing the creation of the standby. Also, you must have RMAN backups of all datafiles available as well as a control file backup that is usable as a standby control file.

Pre-requisites

Please refer the below link for the pre-requisites required for the creation of standby database using RMAN

http://oraclemamukutti.blogspot.com/2011/05/creating-standby-database-using-rman.html

Steps Required

1. Create Password File for Standby Database

Login to the standby database server and create a password file:

$ orapwd file=/u01/app/oracle/product/9.2.0/dbs/orapwORA920

OR Copy the primary database password file to the standby $ORACLE_HOME/dbs location.

2. Create a Standby Controlfile

There are several ways in which to create a standby control file to be used with the standby database instance. In most cases, the easiest way is to perform this is within RMAN. Keep in mind that RMAN will need to have a copy of the standby control file within the catalog before RMAN can use it with the duplicate ... for standby command. From the target (primary) database server, use the following:

$ ORACLE_SID=ORA920; export ORACLE_SID

$ rman target /

RMAN> backup current controlfile for standby format='/orabackup/rman/ORA920/stby_cfile.%U';

3. Record Last Log Sequence

You will need to specify a point in time after the creation of the standby control file. To do this, perform a few log switches and then record the last log sequence number from the v$archived_log view. From the target (primary) database instance:

SQL> alter system switch logfile;

SQL> alter system switch logfile;

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

           208

4. Backup New Archive Log Files

After creating the standby control file, you will need to backup the newly created archive redo logs (created from the alter system switch logfile; command above):

$ rman target /

RMAN> backup archivelog all delete input;

5. Create Directory Structure on Standby (Auxiliary) Server

On the standby database server, create all needed directories for the standby database

6. Create an Initialization Parameter for the Standby Database

Now, copy a version of the text initialization parameter from the target database to the standby database server and make the necessary changes for the standby database:

From the target (primary) database server:

$ export ORACLE_SID=ORA920

$ sqlplus "/ as sysdba"

SQL> create pfile='/u01/app/oracle/product/9.2.0/dbs/initORA920.ora' from spfile;

After creating and copying the initialization parameter for the standby database, change At least the following parameters for the standby database:

fal_client                  =

fal_server                  =

log_archive_dest_2          =

log_archive_dest_state_2    =

standby_file_management     = AUTO

NOTE: Keep in mind that the db_name initialization parameter of the standby database must match the db_name parameter for the primary database. This is required wether the standby database is on the same or different host from the primary database.

If you were to attempt to start two databases on the same host with the same db_name parameter, you will get the following error:

ORA-01102: Cannot mount database in exclusive mode

To get around this, you will need to modify the init.ora file of the standby database and add the parameter lock_name_space. You would set this parameter to a value different from the db_name parameter. Oracle will then use this name to lock memory segments without changing the db_name. For example,

lock_name_space=

7. Start the Standby (Auxiliary) Instance

On the standby database server, start the Oracle instance in nomount mode:

$ ORACLE_SID=ORA920; export ORACLE_SID

$ sqlplus "/ as sysdba"

SQL> startup nomount

8. Ensure Oracle Net Connectivity to Standby (Auxiliary) Database

Modify both the listener.ora and tnsnames.ora file to be able to connect to the standby (auxiliary) database.

Once the Oracle networking files are configured for theprimary & the standby (auxiliary) database host, ensure to start the TNS listener with the latest (valid) listener.ora file:

     $ lsnrctl stop

  $ lsnrctl start

9. Mount or Open the Target Database

As mentioned in the pre-requisites section of this article, the target database must be either opened or mounted.

$ ORACLE_SID=ORA920; export ORACLE_SID

$ sqlplus "/ as sysdba"

SQL> startup open

10. Ensure You Have the Necessary Backups and Archived Redo Log Files

As mentioned in the pre-requisites section of this article, ensure that you have a current backup that you wish to use to create the standby database. Login to query the RMAN catalog.

RMAN> list backup summary

11. Create Standby Database

Login to target (primary) and (standby) auxiliary database using RMAN. All of this should be performed from the target database server

NOTE: Notice that the parameter NOFILENAMECHECK must be used when you are duplicating a database to a different host with the same file system (directory structure)

Run the following:

$ rman target sys/sys@primary auxiliary sys/sys@standby

Where primary & standby are net service names

     RMAN>

The following RUN block can be used to fully duplicate the target database from the latest full backup. This will create the standby database:

run {

    # Set the last log sequence number

    set until sequence = 208 thread = 1;

    # Allocate the channel for the duplicate work

    allocate auxiliary channel ch1 type disk;

    # Duplicate the database to ORA920

    duplicate target database for standby dorecover nofilenamecheck ;

}

RMAN> exit

12. Put the Standby in Managed Recovery Mode

On the standby database, run the following:

$ sqlplus "/ as sysdba"

SQL> recover standby database;

SQL> alter database recover managed standby database disconnect;

Database altered

CREATING A STANDBY DATABASE USING RMAN - (UNIX / LINUX) - Pre-reqiusites

Pre-requisites

Pre- requisites for creating the standby database using RMAN are as follows.

Target database must be mounted or open

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Jan 3 19:42:02 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup open

ORACLE instance started.

Total System Global Area  252777660 bytes

Fixed Size                   451772 bytes

Variable Size             218103808 bytes

Database Buffers           33554432 bytes

Redo Buffers                 667648 bytes

Database mounted.

Database opened.

Enable Archiving

If archiving is not enabled, issue the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

Enable Forced Logging

Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:

SQL> ALTER DATABASE FORCE LOGGING;

This statement can take a considerable amount of time to complete, because it waits for all unlogged direct write I/O to finish.

Create a Password File

Create a password file if one does not already exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed

Configure a Standby Redo Log – For real time apply

A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone

Perform the following steps to configure the standby redo log.

Step 1   Ensure log file sizes are identical on the primary and standby databases.

The size of the current standby redo log files must exactly match the size of the current primary database online redo log files

Step 2   Determine the appropriate number of standby redo log file groups.

Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:
(maximum number of logfiles for each thread + 1) * maximum number of threads

Step 3   Verify related database parameters and settings.

Verify the values used for the MAXLOGFILES and MAXLOGMEMBERS clauses on the SQL CREATE DATABASE statement will not limit the number of standby redo log file groups and members that you can add. The only way to override the limits specified by the MAXLOGFILES and MAXLOGMEMBERS clauses is to re-create the primary database or control file.

Step 4   Create standby redo log file groups.

To create new standby redo log file groups and members, you must have the ALTER DATABASE system privilege. The standby database begins using the newly created standby redo data the next time there is a log switch on the primary database.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10

     2> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;

Step 5   Verify the standby redo log file groups were created.

To verify the standby redo log file groups are created and running correctly, invoke a log switch on the primary database, and then query either the V$STANDBY_LOG view or the V$LOGFILE view on the standby database once it has been created.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

A valid full database backup of the target database

From the target (primary) database, you will need to create a full backup of the database.

RMAN> backup database plus archivelog delete input;

RMAN> list backup summary;