Tuesday, September 4, 2012

Local file system to ASM migration


Posted by at No comments:

Monday, September 3, 2012

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.

Posted by at No comments:

Saturday, September 1, 2012

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
Posted by at No comments:

Sunday, August 26, 2012

Make index invisible


If we want to test the impact of dropping the index,we can do it in an easy way by making the index invisible.Here I am going to execute a query which will utilize an existing index PRODUCTS_PROD_CAT_IX.


SQL> set autotrace on EXPLAIN


SQL> select prod_category,avg(amount_sold) from sh.sales s,sh.products p where p.prod_id=s.prod_id group by prod_category;

PROD_CATEGORY                                      AVG(AMOUNT_SOLD)
-------------------------------------------------- ----------------
Software/Other                                           34.1313997
Hardware                                                 1344.50776
Electronics                                              125.551667
Peripherals and Accessories                              108.824588
Photo                                                    188.064642


Execution Plan
----------------------------------------------------------
Plan hash value: 1197568639

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                      |     5 |   255 |   520   (8)| 00:00:07 |       |       |
|   1 |  HASH GROUP BY           |                      |     5 |   255 |   520   (8)| 00:00:07 |       |       |
|*  2 |   HASH JOIN              |                      |    72 |  3672 |   519   (7)| 00:00:07 |       |       |
|   3 |    VIEW                  | VW_GBC_5             |    72 |  2160 |   516   (7)| 00:00:07 |       |       |
|   4 |     HASH GROUP BY        |                      |    72 |   648 |   516   (7)| 00:00:07 |       |       |
|   5 |      PARTITION RANGE ALL |                      |   918K|  8075K|   489   (2)| 00:00:06 |     1 |    28 |
|   6 |       TABLE ACCESS FULL  | SALES                |   918K|  8075K|   489   (2)| 00:00:06 |     1 |    28 |
|   7 |    VIEW                  | index$_join$_002     |    72 |  1512 |     3  (34)| 00:00:01 |       |       |
|*  8 |     HASH JOIN            |                      |       |       |            |          |       |       |
|   9 |      INDEX FAST FULL SCAN| PRODUCTS_PK          |    72 |  1512 |     1   (0)| 00:00:01 |       |       |
|  10 |      INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX |    72 |  1512 |     1   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("P"."PROD_ID"="ITEM_1")
   8 - access(ROWID=ROWID)




Now lets make the index invisible and compare the difference.

SQL> alter index SH.PRODUCTS_PROD_CAT_IX invisible;

Index altered.

SQL> set autotrace on EXPLAIN
SQL> select prod_category,avg(amount_sold) from sh.sales s,sh.products p where p.prod_id=s.prod_id group by prod_category;

PROD_CATEGORY                                      AVG(AMOUNT_SOLD)
-------------------------------------------------- ----------------
Software/Other                                           34.1313997
Hardware                                                 1344.50776
Electronics                                              125.551667
Photo                                                    188.064642
Peripherals and Accessories                              108.824588


Execution Plan
----------------------------------------------------------
Plan hash value: 504757596

----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |     5 |   255 |   520   (7)| 00:00:07 |       |       |
|   1 |  HASH GROUP BY          |          |     5 |   255 |   520   (7)| 00:00:07 |       |       |
|*  2 |   HASH JOIN             |          |    72 |  3672 |   519   (7)| 00:00:07 |       |       |
|   3 |    VIEW                 | VW_GBC_5 |    72 |  2160 |   516   (7)| 00:00:07 |       |       |
|   4 |     HASH GROUP BY       |          |    72 |   648 |   516   (7)| 00:00:07 |       |       |
|   5 |      PARTITION RANGE ALL|          |   918K|  8075K|   489   (2)| 00:00:06 |     1 |    28 |
|   6 |       TABLE ACCESS FULL | SALES    |   918K|  8075K|   489   (2)| 00:00:06 |     1 |    28 |
|   7 |    TABLE ACCESS FULL    | PRODUCTS |    72 |  1512 |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("P"."PROD_ID"="ITEM_1")

So if we compare the two explain plans ,in the second plan we are doing full table scan instead of index fast full scan and also the cost of the second explain plan is high.
Posted by at No comments:

Thursday, August 16, 2012

RMAN Cloning issues

I saw this issue few days back , it was related to some issue in 10.2.0.3 version database.We were doing point in time rman cloning from production to test.In the middle of the cloning it errored out saying that it is not able to create the control file.We created the control file manually including all the datafiles in the script.Then we followed the oracle support doc id  360962.1 to resume the rest of the cloning steps.

We restarted the cloning following the step 5 of the doc.

$ rman target / auxiliary sys/oracle@

RMAN> run { 
   set new name for datafile n to 'X';
   set until scn 1412841;
   recover clone database;
   alter clone database open resetlogs;
}

At the end of the script it was throwing error RMAN-00600 .It was strange why it was throwing this error.After sometime we noticed that this error was because we have not given the "Set newname for  datafile n to [newname]" in the script.Note that we are using the parameter db_file_name_convert in the parameter file of the test database.Still we changed it in the script and ran it again.This time set newname was taking almost 20 mins on each file .Looking at the primary database I found that it was doing a full table scan on X$KCCLH


SELECT RECID , STAMP , THREAD# , SEQUENCE# , FIRST_CHANGE# LOW_SCN , FIRST_TIME LOW_TIME , NEXT_CHANGE# NEXT_SCN , RESETLOGS_CHANGE# , RESETLOGS_TIME
FROM V$LOG_HISTORY
WHERE RECID BETWEEN :1 AND :1 AND RESETLOGS_TIME IS NOT NULL AND STAMP >= :1 ORDER BY RECID 




At the end of the script it had thrown the error again.This time something different.


RMAN-03002: failure of recover command at XX/XX/20XX 01:07:22
ORA-19870: error reading backup piece 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
ORA-19563: datafile header validation failed for file +XXXXXXXXXXXXXXXXXXXXXXXXXX


There are 2 errors .First one is saying the "error  reading from the backuppiece"and the second one saying "File header mismatch " .We verified that the backuppiece was valid by RMAN validate command but the second issue was still a mystery.

In my opinion instead of  "Set newname for  datafile n to [newname]" I should have used " Set newname for  clone datafile n to [newname]".

I didn't get the opportunity to try that option as it was kind of urgent so  we manually restored and applied the archivelogs  using following and the database was ready.

RMAN>recover database until logseq X;
Alter database open resetlogs;

But it doesn't feel good if you spent so much time in  doing the right thing and  something so simple is the workaround.This issue will remain a mystery for me as long as I am not able to reproduce this issue in test .
If anyone reading this gets the opportunity to try the "clone" clause let me know if that worked for you .

Posted by at No comments:

Corruption in the data file blocks

Friends I have seen this issue today.It was related to corrupt blocks reported in the alert log file.It was like following entry in the alert log.

Reread of blocknum=2233910, file=+DG1/main/datafile/db01.76820. found same corrupt data

After initial investigation we found that there is an entry in the V$database block corruption


SQL> select * from gv$database_block_corruption;

   INST_ID      FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ---------- ------------------ ---------
         1         36    2233910          1                  0 FRACTURED
         2         36    2233910          1                  0 FRACTURED
        
we decided to run the dbverify utility .


dbv userid=sys/password file=+DG1/main/datafile/db01.76820

it showed no block corrupted but still we had entry in  the v$database_block_corruption.


We then followed the support doc 336133.1.Following query showed that the object is an index but its size is more than 20GB .If we have to rebuild it then it will take lot of time.


 select segment_name, segment_type, owner
       from dba_extents
      where file_id =
        and  between block_id
            and block_id + blocks -1;

 To ascertain that this is indeed a corruption we ran rman validate on the datafile.This populated the v$database_block_corruption again and guess what!!
it didn't show any corruption as I was already doubting about it.

Posted by at No comments:

Restore Database with Current Redo log corruption

Hi Friends.I am writing here because I had seen this scenario where current redo log has got corrupted .We all know that point in time recovery is the solution in this case but following points are to be noted
  • Database size is almost 25 TB. 
  • When alter system checkpoint is not working.
  • We are ready to loose some of the data but not willing to go for the outage that big for point in time recovery.
I heard that somebody did something with the control file and without actual restore he was able to go past the time in current log corruption.I did some testing on my test database .It might not be 100% correct so your suggestions are invited but it worked in my case.To simulate the case we will perform corruption in the current redo log.

SQL> select GROUP#,SEQUENCE#,MEMBERS,ARCHIVED, STATUS from v$log;

    GROUP#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
         1         24          1 YES INACTIVE
         2         23          1 YES INACTIVE
         3         25          1 NO  CURRENT


So 3 is the current redolog.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
         3         ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
NO

         2         ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
NO

Here we want to corrupt the file /u01/app/oracle/oradata/orcl/redo03.log.Lets do the following.

[root@localhost ~]# dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo03.log count=1 seek=1 bs=8192
0+0 records in
0+0 records out
0 bytes (0 B) copied, 4.9343e-05 seconds, 0.0 kB/s

shutdown and startup the database

SQL> startup
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             545263184 bytes
Database Buffers          297795584 bytes
Redo Buffers                5132288 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 3523
Session ID: 1 Serial number: 5

select GROUP#,substr(member,1,60) from v$logfile;
    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- ---------- --- ---------------- -------------
         1          1         27          1 NO  CURRENT                1128975
         3          1         25          1 NO  INACTIVE               1127602
         2          1         26          1 NO  INACTIVE               1128972

So group 3 is not archived as seen from above

Now we want to create as control file manually

SQL> alter database backup controlfile to trace;

Database altered.


It looks like following

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/orcl/system01.dbf',
  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
  '/u01/app/oracle/oradata/orcl/users01.dbf',
  '/u01/app/oracle/oradata/orcl/example01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_08_17/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_08_17/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
#RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
#ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
#ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.

Move both the control files to backup

[oracle@localhost orcl]$ mv control02.ctl control02.ctl_bkp

startup nomount the instance


SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 17 03:30:37 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @control.sql

Control file created.

ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


ALTER SYSTEM ARCHIVE LOG ALL
*
ERROR at line 1:
ORA-01649: operation not allowed with a backup control file


ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE


so the database has created the control files at the 2 locations described in the spfile.After opening the database with the resetlogs we have the database open for read and write.The questions is thet I have not performed point in time recovery and I am ready to loose the data in the redo log files.That will be a compromise but think about the time in restoring the 25TB database.How much outage it will cause.I still don't know is it is the right way to do it or not but still there is a way.If you know a better way or if this is not correct please let me know.






























Posted by at No comments:
Subscribe to: Posts (Atom)