Local file system to ASM migration

Incrementally updating rman backups

Incrementally updating backups is an important feature of Oracle database available from oracle 10g onwards.I am going to test some of the scenarios related to this backup strategy.We need to have enough space on the disk to keep the backups for this strategy.

• First day of the backup will be similar to L0 backup.We run 2 commands daily.First command will take the rman incremental backup and second will rollforward our existing backup with the backup we have just taken.

1. BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;
2. RECOVER COPY OF DATABASE WITH TAG WEEKLY;

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;

Starting backup at 03-SEP-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/test/system01.dbf
output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf tag=WEEKLY RECID=1 STAMP=793046771
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbf
output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf tag=WEEKLY RECID=2 STAMP=793046805
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbf
output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf tag=WEEKLY RECID=3 STAMP=793046815
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 03-SEP-12
channel ORA_DISK_1: finished piece 1 at 03-SEP-12
piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_ncsn1_WEEKLY_84bmw4gy_.bkp tag=WEEKLY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbf
output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf tag=WEEKLY RECID=4 STAMP=793046822
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 03-SEP-12

RMAN> RECOVER COPY OF DATABASE WITH TAG WEEKLY;

Starting recover at 03-SEP-12

using channel ORA_DISK_1

no copy of datafile 1 found to recover

no copy of datafile 2 found to recover

no copy of datafile 3 found to recover

no copy of datafile 4 found to recover

Finished recover at 03-SEP-12

RMAN> list backup;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

1       Incr 1  9.36M      DISK        00:00:02     03-SEP-12

        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: WEEKLY

        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_ncsn1_WEEKLY_84bmw4gy_.bkp

  SPFILE Included: Modification time: 03-SEP-12

  SPFILE db_unique_name: TEST

  Control File Included: Ckp SCN: 954398       Ckp time: 03-SEP-12

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

/

System altered.

SQL> /

System altered.

SQL> /

System altered.

• On the second day it will rollforward the changes to existing database backup

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;

Starting backup at 03-SEP-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/test/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbf

channel ORA_DISK_1: starting piece 1 at 03-SEP-12

channel ORA_DISK_1: finished piece 1 at 03-SEP-12

piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnfhol_.bkp tag=WEEKLY comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 03-SEP-12

channel ORA_DISK_1: finished piece 1 at 03-SEP-12

piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_ncsn1_WEEKLY_84bngxyo_.bkp tag=WEEKLY comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 03-SEP-12

RMAN> RECOVER COPY OF DATABASE WITH TAG WEEKLY;

Starting recover at 03-SEP-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile copies to recover

recovering datafile copy file number=00001 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf

recovering datafile copy file number=00002 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf

recovering datafile copy file number=00003 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf

recovering datafile copy file number=00004 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnfhol_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnfhol_.bkp tag=WEEKLY

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 03-SEP-12

• If we add a tablespace to the database ,next rman backup will acknowledge the change and it will take the backup of new datafile.

SQL> create tablespace test1 datafile '/u01/app/oracle/oradata/test/test1.dbf' size 50m;

Tablespace created.

RMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY DATABASE;

Starting backup at 03-SEP-12

using channel ORA_DISK_1

no parent backup or copy of datafile 5 found

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/test/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/test/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/test/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/test/users01.dbf

channel ORA_DISK_1: starting piece 1 at 03-SEP-12

channel ORA_DISK_1: finished piece 1 at 03-SEP-12

piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnlr6s_.bkp tag=WEEKLY comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/test/test1.dbf

output file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_test1_84bnmk9h_.dbf tag=WEEKLY RECID=9 STAMP=793047571

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting incremental level 1 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 03-SEP-12

channel ORA_DISK_1: finished piece 1 at 03-SEP-12

piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_ncsn1_WEEKLY_84bnmoh4_.bkp tag=WEEKLY comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 03-SEP-12

RMAN>

RECOVER COPY OF DATABASE WITH TAG WEEKLY;

RMAN>

Starting recover at 03-SEP-12

using channel ORA_DISK_1

no copy of datafile 5 found to recover

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile copies to recover

recovering datafile copy file number=00001 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf

recovering datafile copy file number=00002 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf

recovering datafile copy file number=00003 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf

recovering datafile copy file number=00004 name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnlr6s_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_09_03/o1_mf_nnnd1_WEEKLY_84bnlr6s_.bkp tag=WEEKLY

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 03-SEP-12

• Lets check the recovery.I am going to delete the users01 datafile belonging to the users tablespace.

SQL> !rm -rf /u01/app/oracle/oradata/test/users01.dbf

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  622149632 bytes

Fixed Size                  2215904 bytes

Variable Size             377487392 bytes

Database Buffers          239075328 bytes

Redo Buffers                3371008 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/u01/app/oracle/oradata/test/users01.dbf'

• To recover I have to restore the backup image copies and apply the archive log changes.

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Sep 3 19:03:31 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2091192679, not open)

RMAN> restore database;

Starting restore at 03-SEP-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring datafile 00001

input datafile copy RECID=13 STAMP=793047598 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf

destination for restore of datafile 00001: /u01/app/oracle/oradata/test/system01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00001

output file name=/u01/app/oracle/oradata/test/system01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00002

input datafile copy RECID=12 STAMP=793047598 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf

destination for restore of datafile 00002: /u01/app/oracle/oradata/test/sysaux01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00002

output file name=/u01/app/oracle/oradata/test/sysaux01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00003

input datafile copy RECID=11 STAMP=793047598 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf

destination for restore of datafile 00003: /u01/app/oracle/oradata/test/undotbs01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00003

output file name=/u01/app/oracle/oradata/test/undotbs01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00004

input datafile copy RECID=10 STAMP=793047598 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf

destination for restore of datafile 00004: /u01/app/oracle/oradata/test/users01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00004

output file name=/u01/app/oracle/oradata/test/users01.dbf RECID=0 STAMP=0

channel ORA_DISK_1: restoring datafile 00005

input datafile copy RECID=9 STAMP=793047571 file name=/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_test1_84bnmk9h_.dbf

destination for restore of datafile 00005: /u01/app/oracle/oradata/test/test1.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00005

output file name=/u01/app/oracle/oradata/test/test1.dbf RECID=0 STAMP=0

Finished restore at 03-SEP-12

RMAN> recover database;

Starting recover at 03-SEP-12

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_7_84bnpcd5_.arc

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_8_84bnpdcg_.arc

archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_9_84bnphy5_.arc

archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_10_84bnpjwf_.arc

archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_11_84bnpkpo_.arc

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_12_84bnpld7_.arc

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_7_84bnpcd5_.arc thread=1 sequence=7

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_8_84bnpdcg_.arc thread=1 sequence=8

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_9_84bnphy5_.arc thread=1 sequence=9

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_10_84bnpjwf_.arc thread=1 sequence=10

media recovery complete, elapsed time: 00:00:01

Finished recover at 03-SEP-12

• As an alternate and fast process we can switch the datafile to the backup copy

SQL> !rm /u01/app/oracle/oradata/test/system01.dbf

SQL> startup

ORACLE instance started.

Total System Global Area  622149632 bytes

Fixed Size                  2215904 bytes

Variable Size             377487392 bytes

Database Buffers          239075328 bytes

Redo Buffers                3371008 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/test/system01.dbf'

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Sep 3 19:10:25 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST (DBID=2091192679, not open)

RMAN> switch database to copy;

using target database control file instead of recovery catalog

datafile 1 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_system_84bmsbx4_.dbf"

datafile 2 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_sysaux_84bmts04_.dbf"

datafile 3 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_undotbs1_84bmvw32_.dbf"

datafile 4 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_users_84bmw5hk_.dbf"

datafile 5 switched to datafile copy "/u01/app/oracle/flash_recovery_area/TEST/datafile/o1_mf_test1_84bnmk9h_.dbf"

RMAN> recover database;

Starting recover at 03-SEP-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

starting media recovery

archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_7_84bnpcd5_.arc

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_8_84bnpdcg_.arc

archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_9_84bnphy5_.arc

archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_10_84bnpjwf_.arc

archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_11_84bnpkpo_.arc

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_12_84bnpld7_.arc

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_7_84bnpcd5_.arc thread=1 sequence=7

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_8_84bnpdcg_.arc thread=1 sequence=8

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_9_84bnphy5_.arc thread=1 sequence=9

archived log file name=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_09_03/o1_mf_1_10_84bnpjwf_.arc thread=1 sequence=10

media recovery complete, elapsed time: 00:00:01

Finished recover at 03-SEP-12

SQL>  alter database open;

Database altered.

11g Standby creation on the same host with Flashback enabled at both the sites

This topic is not something new but still I have seen some of the issues while creating a standby on the same host.so I have copied all the issues in this post while creating the standby.Following conventions are followed

Primary:orcl
Standby :stby

We are going to keep the DB_name for both the databases same and the db_unique name different.

• To start with lets make an entry of the standby database in the /etc/oratab

[oracle@localhost ~]$ tail -f /etc/oratab
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
orcl:/u01/app/oracle/product/11.1.0/db_1:Y
stby:/u01/app/oracle/product/11.1.0/db_1:Y

• I have found that the database is in no archivelog mode.So lets put it in archivelog mode

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> shutdown immediate;

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  217157632 bytes

Fixed Size                  2211928 bytes

Variable Size             159387560 bytes

Database Buffers           50331648 bytes

Redo Buffers                5226496 bytes

Database mounted.

SQL>

SQL> alter database archivelog;

Database altered.

SQL> alter database open ;

Database altered.

SQL> select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

• Lets add standby redo logfiles to primary.The idea is to add n+1 redo log files where n stands for the primary redo log files.It will be usefull at the time of switcover as well .Take care of the size of the redo log files.

SQL> select GROUP#,TYPE,MEMBER from v$logfile;

    GROUP# TYPE

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

MEMBER

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

         3 ONLINE

/u01/app/oracle/oradata/orcl/redo03.log

         2 ONLINE

/u01/app/oracle/oradata/orcl/redo02.log

         1 ONLINE

/u01/app/oracle/oradata/orcl/redo01.log

SQL> select bytes/1024/1024 from v$log;

BYTES/1024/1024

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

             50

             50

             50

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stby_redo01' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stby_redo02' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stby_redo03' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/stby_redo04' size 50m;

Database altered.

• Lets add the few of the mandatory parameters for the creation of the standby

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,stby)';

System altered.

SQL> Alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=stby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

System altered.

SQL> alter system set FAL_SERVER=stby;

System altered.

SQL> alter system set FAL_CLIENT=orcl;

System altered.

SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stby' scope=spfile;

System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stby' scope=spfile;

System altered.

SQL>

• Please mind this step.It is very important.If you won't add a static entry to the listener file of the database it will be impossible to connect to our auxiliary database which is stby in our case.

[oracle@localhost admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =(SID_DESC =(GLOBAL_DBNAME = stby.localdomain)(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)(SID_NAME = stby)))

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@localhost admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl.localdomain)

    )

  )

stby =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = stby.localdomain)

    )

  )

[oracle@localhost admin]$ tnsping stby

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 31-AUG-2012 16:06:22

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stby.localdomain)))

OK (10 msec)

• In 11g it is important to copy the password file following the naming convention.Dont try to recreate the password file ,it doesn't work sometimes.In our case we are creating the standby on the same host so we just copy and rename the password file.

[oracle@localhost admin]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ ls -lrt

total 24

-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora

-rw-r----- 1 oracle oinstall   24 Aug 24 16:32 lkORCL

-rw-r--r-- 1 oracle oinstall  916 Aug 25 12:05 initorcl.ora

-rw-r----- 1 oracle oinstall 1536 Aug 29 15:05 orapworcl

-rw-rw---- 1 oracle oinstall 1544 Aug 31 15:28 hc_orcl.dat

-rw-r----- 1 oracle oinstall 3584 Aug 31 15:51 spfileorcl.ora

[oracle@localhost dbs]$ cp orapworcl orapwstby

Database opened.
SQL> SQL> SQL> create pfile from spfile;

File created.

• This step didn't work for me don't know why.I tried to copy the parameter file,renamed and modified few of the parameters .After that I was not able to make the remote connection to the stby database.So I created the pfile again with only 3 parameters (db_name,db_unique_name and db_block_size) and it worked.

[oracle@localhost dbs]$ cp initorcl.ora initstby.ora

[oracle@localhost dbs]$ export ORACLE_SID=stby

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 1 20:29:40 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 1 20:29:40 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount

ORA-09925: Unable to create audit trail file

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

Additional information: 9925

[oracle@localhost dbs]$ cd /u01/app/oracle/admin/

[oracle@localhost admin]$

[oracle@localhost admin]$

[oracle@localhost admin]$

[oracle@localhost admin]$ ls

orcl

[oracle@localhost admin]$ mkdir stby

[oracle@localhost admin]$ cd orcl

[oracle@localhost orcl]$ ls -lrt

total 20

drwxr-x--- 2 oracle oinstall  4096 Aug 24 16:35 pfile

drwxr-x--- 2 oracle oinstall  4096 Aug 26 17:45 dpdump

drwxr-x--- 2 oracle oinstall 12288 Sep  1 20:23 adump

[oracle@localhost orcl]$ cd ../stby

[oracle@localhost stby]$ mkdir pfile dpdump adump

[oracle@localhost dbs]$ sqlplus sys/oracle@stby as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 1 20:34:15 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

[oracle@localhost dbs]$ tnsping stby

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-SEP-2012 20:35:07

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stby.localdomain)))

OK (0 msec)

• As a workaround I created the initstby.ora with only 3 parameters and my remote connection to standby worked.

[oracle@localhost dbs]$ cat initstby.ora

db_name='orcl'

db_unique_name='stby'

db_block_size=8192

• This is most easy step now.We just have to start the rman session and start the script

[oracle@localhost dbs]$ rman target sys/oracle@orcl auxiliary sys/oracle@stby

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 1 21:01:25 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1319955483)

connected to auxiliary database: ORCL (not mounted)

run {

allocate channel prmy1 type disk;

allocate channel prmy2 type disk;

allocate channel prmy3 type disk;

allocate channel prmy4 type disk;

allocate auxiliary channel stby type disk;

duplicate target database for standby from active database

spfile

  parameter_value_convert 'orcl','stby'

  set db_unique_name='stby'

  set db_file_name_convert='/orcl/','/stby/'

  set log_file_name_convert='/orcl/','/stby/'

  set control_files='/u01/app/oracle/oradata/stby/control01.ctl'

  set log_archive_max_processes='5'

  set fal_client='stby'

  set fal_server='orcl'

  set standby_file_management='AUTO'

  set log_archive_config='dg_config=(orcl,stby)'

  set log_archive_dest_1='service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'

;

}

10>   parameter_value_convert 'orcl','stby'

11>   set db_unique_name='stby'

12>   set db_file_name_convert='/orcl/','/stby/'

13>   set log_file_name_convert='/orcl/','/stby/'

14>   set control_files='/u01/app/oracle/oradata/stby/control01.ctl'

15>   set log_archive_max_processes='5'

16>   set fal_client='stby'

  set fal_server='orcl'

17> 18>   set standby_file_management='AUTO'

19>   set log_archive_config='dg_config=(orcl,stby)'

20>   set log_archive_dest_1='service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'

21> ;

22> }

using target database control file instead of recovery catalog

allocated channel: prmy1

channel prmy1: SID=57 device type=DISK

allocated channel: prmy2

channel prmy2: SID=52 device type=DISK

allocated channel: prmy3

channel prmy3: SID=47 device type=DISK

allocated channel: prmy4

channel prmy4: SID=58 device type=DISK

allocated channel: stby

channel stby: SID=19 device type=DISK

Starting Duplicate Db at 01-SEP-12

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/u01/app/oracle/product/11.1.0/db_1/dbs/orapworcl' auxiliary format

 '/u01/app/oracle/product/11.1.0/db_1/dbs/orapwstby'   targetfile

 '/u01/app/oracle/product/11.1.0/db_1/dbs/spfileorcl.ora' auxiliary format

 '/u01/app/oracle/product/11.1.0/db_1/dbs/spfilestby.ora'   ;

   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.1.0/db_1/dbs/spfilestby.ora''";

}

executing Memory Script

Starting backup at 01-SEP-12

Finished backup at 01-SEP-12

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.1.0/db_1/dbs/spfilestby.ora''

contents of Memory Script:

{

   sql clone "alter system set  audit_file_dest =

 ''/u01/app/oracle/admin/stby/adump'' comment=

 '''' scope=spfile";

   sql clone "alter system set  dispatchers =

 ''(PROTOCOL=TCP) (SERVICE=stbyXDB)'' comment=

 '''' scope=spfile";

   sql clone "alter system set  db_unique_name =

 ''stby'' comment=

 '''' scope=spfile";

   sql clone "alter system set  db_file_name_convert =

 ''/orcl/'', ''/stby/'' comment=

 '''' scope=spfile";

   sql clone "alter system set  log_file_name_convert =

 ''/orcl/'', ''/stby/'' comment=

 '''' scope=spfile";

   sql clone "alter system set  control_files =

 ''/u01/app/oracle/oradata/stby/control01.ctl'' comment=

 '''' scope=spfile";

   sql clone "alter system set  log_archive_max_processes =

 5 comment=

 '''' scope=spfile";

   sql clone "alter system set  fal_client =

 ''stby'' comment=

 '''' scope=spfile";

   sql clone "alter system set  fal_server =

 ''orcl'' comment=

 '''' scope=spfile";

   sql clone "alter system set  standby_file_management =

 ''AUTO'' comment=

 '''' scope=spfile";

   sql clone "alter system set  log_archive_config =

 ''dg_config=(orcl,stby)'' comment=

 '''' scope=spfile";

   sql clone "alter system set  log_archive_dest_1 =

 ''service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'' comment=

 '''' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/stby/adump'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=stbyXDB)'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''stby'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/orcl/'', ''/stby/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/orcl/'', ''/stby/'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''/u01/app/oracle/oradata/stby/control01.ctl'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_max_processes =  5 comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''stby'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''orcl'' comment= '''' scope=spfile

sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_config =  ''dg_config=(orcl,stby)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2211928 bytes

Variable Size                159387560 bytes

Database Buffers              50331648 bytes

Redo Buffers                   5226496 bytes

allocated channel: stby

channel stby: SID=18 device type=DISK

contents of Memory Script:

{

   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/stby/control01.ctl';

}

executing Memory Script

Starting backup at 01-SEP-12

channel prmy1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.1.0/db_1/dbs/snapcf_orcl.f tag=TAG20120901T210539 RECID=2 STAMP=792882341

channel prmy1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 01-SEP-12

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/u01/app/oracle/oradata/stby/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/stby/system01.dbf";

   set newname for datafile  2 to

 "/u01/app/oracle/oradata/stby/sysaux01.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/stby/undotbs01.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/stby/users01.dbf";

   set newname for datafile  5 to

 "/u01/app/oracle/oradata/stby/example01.dbf";

   backup as copy reuse

   datafile  1 auxiliary format

 "/u01/app/oracle/oradata/stby/system01.dbf"   datafile

 2 auxiliary format

 "/u01/app/oracle/oradata/stby/sysaux01.dbf"   datafile

 3 auxiliary format

 "/u01/app/oracle/oradata/stby/undotbs01.dbf"   datafile

 4 auxiliary format

 "/u01/app/oracle/oradata/stby/users01.dbf"   datafile

 5 auxiliary format

 "/u01/app/oracle/oradata/stby/example01.dbf"   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/stby/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 01-SEP-12

channel prmy1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

channel prmy2: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

channel prmy3: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

channel prmy4: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf

output file name=/u01/app/oracle/oradata/stby/example01.dbf tag=TAG20120901T210550

channel prmy4: datafile copy complete, elapsed time: 00:00:57

channel prmy4: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

output file name=/u01/app/oracle/oradata/stby/undotbs01.dbf tag=TAG20120901T210550

channel prmy3: datafile copy complete, elapsed time: 00:01:18

output file name=/u01/app/oracle/oradata/stby/users01.dbf tag=TAG20120901T210550

channel prmy4: datafile copy complete, elapsed time: 00:00:35

output file name=/u01/app/oracle/oradata/stby/sysaux01.dbf tag=TAG20120901T210550

channel prmy2: datafile copy complete, elapsed time: 00:02:38

output file name=/u01/app/oracle/oradata/stby/system01.dbf tag=TAG20120901T210550

channel prmy1: datafile copy complete, elapsed time: 00:02:49

Finished backup at 01-SEP-12

sql statement: alter system archive log current

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=2 STAMP=792882523 file name=/u01/app/oracle/oradata/stby/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=3 STAMP=792882524 file name=/u01/app/oracle/oradata/stby/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=4 STAMP=792882524 file name=/u01/app/oracle/oradata/stby/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=792882524 file name=/u01/app/oracle/oradata/stby/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=6 STAMP=792882524 file name=/u01/app/oracle/oradata/stby/example01.dbf

Finished Duplicate Db at 01-SEP-12

released channel: prmy1

released channel: prmy2

released channel: prmy3

released channel: prmy4

released channel: stby

RMAN>

• So the standby creation has been successful .Let's see if recovery is working

SQL> select recovery_mode from v$archive_dest_status;

RECOVERY_MODE
-----------------------
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE

On the standby database-

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

On primary database switch logfile

Log of the standby database 


Sat Sep 01 21:16:00 2012
Media Recovery Waiting for thread 1 sequence 45 (in transit)
Sat Sep 01 21:16:00 2012
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance stby - Archival Error
ORA-16014: log 5 sequence# 43 not archived, no available destinations
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/stby/stby_redo02'
Errors in file /u01/app/oracle/diag/rdbms/stby/stby/trace/stby_arc4_9364.trc:
ORA-16014: log 5 sequence# 43 not archived, no available destinations
ORA-00312: online log 5 thread 1: '/u01/app/oracle/oradata/stby/stby_redo02'
Sat Sep 01 21:16:00 2012
RFS[4]: Selected log 6 for thread 1 sequence 45 dbid 1319955483 branch 792174751
Recovery of Online Redo Log: Thread 1 Group 6 Seq 45 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/stby/stby_redo03
Sat Sep 01 21:16:04 2012
Archiver process freed from errors. No longer stopped
Media Recovery Waiting for thread 1 sequence 46
RFS[4]: Selected log 7 for thread 1 sequence 46 dbid 1319955483 branch 792174751
Recovery of Online Redo Log: Thread 1 Group 7 Seq 46 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/stby/stby_redo04

• See the error above .It is because I made a mistake in setting the archive log location So I set it again.

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stby';

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     45
Next log sequence to archive   0
Current log sequence           47
SQL> alter system set log_archive_dest_2='service=orcl ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl'
  2  ;

System altered.

alert logfile of standby database


RFS[7]: Assigned to RFS process 11388
RFS[7]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 3425
Primary database is in MAXIMUM PERFORMANCE mode
RFS[7]: No standby redo logfiles of size 102400 blocks available
RFS[7]: Opened log for thread 1 sequence 48 dbid 1319955483 branch 792174751
Archived Log entry 4 added for thread 1 sequence 48 rlc 792174751 ID 0x4ead2e1b dest 2:
RFS[7]: No standby redo logfiles of size 102400 blocks available
RFS[7]: Opened log for thread 1 sequence 49 dbid 1319955483 branch 792174751
Sat Sep 01 21:24:04 2012
Media Recovery Log /u01/app/oracle/flash_recovery_area/STBY/archivelog/2012_09_01/o1_mf_1_48_845nbm58_.arc
Media Recovery Waiting for thread 1 sequence 49 (in transit)

Now it's using the correct logfile location

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     52

Next log sequence to archive   54

Current log sequence           54

SQL> select thread#,max(sequence#),applied from v$archived_log group by thread#,applied;

   THREAD# MAX(SEQUENCE#) APPLIED

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

         1             52 YES

         1             53 IN-MEMORY

• For rest of the steps I am going to follow  support Doc ID 316392.1 to configure the dataguard broker and test the switchover scenario

On primary database 

SQL> alter system set dg_broker_start=false ;

System altered.

SQL> alter system set DG_BROKER_CONFIG_FILE1='/u01/app/oracle/oradata/orcl/dr1_orcl.dat';

System altered.

SQL> alter system set DG_BROKER_CONFIG_FILE2='/u01/app/oracle/oradata/orcl/dr2_orcl.dat';

System altered.

SQL> alter system set dg_broker_start=true ;

System altered.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

[oracle@localhost ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle@orcl

Connected.

DGMGRL> create configuration orcl_config as primary database is orcl connect identifier is orcl;

Configuration "orcl_config" created with primary database "orcl"

On Standby database 

SQL> alter system set dg_broker_start=false ;

System altered.

SQL> alter system set DG_BROKER_CONFIG_FILE1='/u01/app/oracle/oradata/orcl/dr1_stby.dat';

System altered.

SQL> alter system set DG_BROKER_CONFIG_FILE2='/u01/app/oracle/oradata/orcl/dr2_stby.dat'

  2  ;

System altered.

SQL> alter system set dg_broker_start=true ;

System altered.

[oracle@localhost ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle@orcl

Connected.

DGMGRL> add database stby  as connect identifier is stby maintained as physical;

Database "stby" added

DGMGRL> show configuration

Configuration - orcl_config

  Protection Mode: MaxPerformance

  Databases:

    orcl - Primary database

    stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration

Configuration - orcl_config

  Protection Mode: MaxPerformance

  Databases:

    orcl - Primary database

    stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> show database stby

Database - stby

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: OFF

  Instance(s):

    stby

Database Status:

SUCCESS

• Lets perform the switchover to the standby database stby using DGMGRL

[oracle@localhost ~]$ dgmgrl /

DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> connect sys/oracle@orcl

Connected.

DGMGRL> switchover to stby;

Performing switchover NOW, please wait...

New primary database "stby" is opening...

Operation requires shutdown of instance "orcl" on database "orcl"

Shutting down instance "orcl"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "orcl" on database "orcl"

Starting instance "orcl"...

Unable to connect to database

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:

        start up and mount instance "orcl" of database "orcl"

• After this we have to start the standby database in mount state and start the recovery

[oracle@localhost ~]$ export ORACLE_SID=stby

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 3 14:14:59 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> select database_role from v$database;

DATABASE_ROLE

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

PRIMARY

[oracle@localhost ~]$ export ORACLE_SID=orcl

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 3 14:15:38 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  217157632 bytes

Fixed Size                  2211928 bytes

Variable Size             159387560 bytes

Database Buffers           50331648 bytes

Redo Buffers                5226496 bytes

Database mounted.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>  alter database recover managed standby database using current logfile disconnect;

Database altered.

• To confirm if the standby is applying the redo logs lets make some logfile switches on the new primary(stby).

[oracle@localhost ~]$ export ORACLE_SID=stby

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 3 14:19:03 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

Contents of alert logfile of new standby (orcl)

Thread 1 advanced to log sequence 73 (LGWR switch)

  Current log# 1 seq# 73 mem# 0: /u01/app/oracle/oradata/stby/redo01.log

Mon Sep 03 14:19:17 2012

Archived Log entry 37 added for thread 1 sequence 72 ID 0x4eb99875 dest 1:

Mon Sep 03 14:19:17 2012

LNS: Standby redo logfile selected for thread 1 sequence 73 for destination LOG_ARCHIVE_DEST_2

Thread 1 advanced to log sequence 74 (LGWR switch)

  Current log# 2 seq# 74 mem# 0: /u01/app/oracle/oradata/stby/redo02.log

Mon Sep 03 14:19:18 2012

Archived Log entry 39 added for thread 1 sequence 73 ID 0x4eb99875 dest 1:

LNS: Standby redo logfile selected for thread 1 sequence 74 for destination LOG_ARCHIVE_DEST_2

Mon Sep 03 14:19:25 2012

Thread 1 cannot allocate new log, sequence 75

Checkpoint not complete

  Current log# 2 seq# 74 mem# 0: /u01/app/oracle/oradata/stby/redo02.log

Thread 1 advanced to log sequence 75 (LGWR switch)

  Current log# 3 seq# 75 mem# 0: /u01/app/oracle/oradata/stby/redo03.log

Mon Sep 03 14:19:26 2012

Archived Log entry 42 added for thread 1 sequence 74 ID 0x4eb99875 dest 1:

LNS: Standby redo logfile selected for thread 1 sequence 75 for destination LOG_ARCHIVE_DEST_2

• Now switchover testing is complete so we can do switchover again to make the orcl primary and stby as standby.

[oracle@localhost trace]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/oracle@stby

Connected.

DGMGRL> show configuration

Configuration - orcl_config

  Protection Mode: MaxPerformance

  Databases:

    stby - Primary database

    orcl - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> switchover to orcl

Performing switchover NOW, please wait...

New primary database "orcl" is opening...

Operation requires shutdown of instance "stby" on database "stby"

Shutting down instance "stby"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "stby" on database "stby"

Starting instance "stby"...

Unable to connect to database

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:

        start up and mount instance "stby" of database "stby"

DGMGRL>

[oracle@localhost trace]$ export ORACLE_SID=orcl

[oracle@localhost trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 3 14:22:33 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> select database_role from v$database;

DATABASE_ROLE

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

PRIMARY

I am not going to perform failover deliberaltely because I'll have to rebuild the primary after a failover .

To get over the ORA-12514 error static entry should be added for db_unique_name_DGMGRL.db_domain

http://docs.oracle.com/cd/B19306_01/server.102/b14230/install.htm#BABECEJC