RMAN DUPLICATE FROM… Active Standby

Posted by

8

Recently I have published post about issue with RMAN duplication of Oracle database when standby database was used as a target. Later in updates of mentioned post I have confirmed that with fix for BUG:11715084 everything works as expected.

Today I have decided to make post with step-by-step procedure with which I have tested mentioned feature – making standby database from standby database.
I will Simplify setup As Much As Possible(SAMAP):

  1. single machine will be used as a host for all 3 databases
  2. OMF(Oracle Managed Files) will be used for managing files location and file names
  3. FRA(Fast/Flash Recovery Area) will be used for archive logs

1) Network Setup

Because I use only single server – all modifications will be done locally.

I will configure two more databases: orcl2 and orcl3

1.1) Static instance registration:

we have to configure static instance registration information to listener because at the duplicate stage RMAN have to be able to connect to idle instance:

modify $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=orcl)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
    )
    (SID_DESC=
      (SID_NAME=orcl1)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
    )
    (SID_DESC=
      (SID_NAME=orcl2)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
    )
  )

We have to reload new configuration data
lsnrctl reload
lsnrctl services

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 24-JUL-2012 14:09:24
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "orcl1" has 1 instance(s).
  Instance "orcl1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "orcl2" has 1 instance(s).
  Instance "orcl2", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully

1.2) Lets add database names resolution with SID specified

modify $ORACLE_HOME/network/admin/tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = demo10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID=orcl)
    )
  )
ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = demo10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID=orcl1)
    )
  )
ORCL2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = demo10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID=orcl2)
    )
  )

2) Setup original database:

I have fresh database orcl  created using DBCA

Archive log mode is enables and FRA is used as a target for archived logs.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     97
Next log sequence to archive   99
Current log sequence           99

I will make several modifications to support Data Guard in this environment – no extensive comments because it’s not an introduction to Oracle Data Guard:

ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SET log_archive_config=’DG_CONFIG=(orcl,orcl1,orcl2)’;
ALTER SYSTEM SET log_archive_dest_1=’SERVICE=orcl1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl1‘;
ALTER SYSTEM SET standby_file_management=auto;

3) Password files in DataGuard

because we must have the same SYS password in all DataGuard environment(actually, You may use REDO_TRANSPORT_USER to specify another user that will be used for Redo Transport),

I’ll just copy original password file to all my databases in environment:

cp $ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapworcl1
cp $ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapworcl2

4) Creating directories for Control Files

because of control file that will be copied from primary may not be placed in FRA(because of FRA at that time will not exist yet) I will create directories for control files for both standby databases(I use capital letters because of OMF will later use the same directory for placing database files)

mkdir -p /u01/app/oracle/oradata/ORCL1
mkdir -p /u01/app/oracle/oradata/ORCL2

5) Lets create first standby

5.1) using RMAN for starting first standby instance WITHOUT any PARAMETER FILE

rman target sys/oracle@ORCL1

startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl1.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2226456 bytes
Variable Size                 92276456 bytes
Database Buffers              58720256 bytes
Redo Buffers                   5439488 bytes

5.2) Duplicating ACTIVE primary

rman TARGET sys/oracle@ORCL AUXILIARY sys/oracle@ORCL1
DUPLICATE DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET “db_unique_name”=”orcl1”
SET “DB_CREATE_FILE_DEST”=”/u01/app/oracle/oradata”
SET “control_files”=”/u01/app/oracle/oradata/ORCL1/control01.ctl”
SET FAL_SERVER=”orcl”
;
At the end of this step You will have new standby database created from Active primary – without the need of any backups.

5.3) starting managed recovery

before starting managed recovery I’ll just add some standby log files to support LGWR(LNS) as a transport service – so we will be able use real-time apply feature:

ORCL1 SQL>

ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

6) Lets create second standby using first standby as a source

6.1) using RMAN for starting first standby WITHOUT any PARAMETER FILE

rman target sys/oracle@ORCL2

startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2226456 bytes
Variable Size                 92276456 bytes
Database Buffers              58720256 bytes
Redo Buffers                   5439488 bytes

6.2) Duplicating from ACTIVE standby – I use ORCL1 as a target

rman TARGET sys/oracle@ORCL1 AUXILIARY sys/oracle@ORCL2
DUPLICATE DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET “db_unique_name”=”orcl2”
SET “DB_CREATE_FILE_DEST”=”/u01/app/oracle/oradata”
SET “control_files”=”/u01/app/oracle/oradata/ORCL2/control01.ctl”
SET FAL_SERVER=”orcl1”
;
At the end of this step You will have new second standby database created from ACTIVE STANDBY DATABASE – without the need of any backups and without ANY LOAD ON PRIMARY DATABASE!

6.3) starting redo shipping from ORCL1 to ORCL2 – Cascading Standby

ORCL1 SQL>

ALTER SYSTEM SET set log_archive_dest_2=’SERVICE=orcl2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl2‘;

6.4) starting managed recovery

before starting managed recovery I’ll just add some standby log files to support LGWR(LNS) as a transport service – so we will be able use real-time apply feature:

ORCL2 SQL>

ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Conclusion:

  • really-really interesting and easy to implement feature, especially when used with OMF and FRA
  • keep in mind that there is a bug mentioned in RMAN DUPLICATE FROM Active Database From Standby ends with ORA-01671 so I have fix for it installed in my environment
  • RMAN ARCHIVELOG DELETION POLICY may be used for simplifying archive log maintenance in DataGuard environment, making it really self-managed – will make short post about it later.

References:

1075908.1 Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE…FROM ACTIVE DATABASE

8 thoughts on “RMAN DUPLICATE FROM… Active Standby”

  1. Pingback: RMAN DUPLICATE FROM Active Database From Standby ends with ORA-01671 « Oleksandr Denysenko's Blog

  2. Why bother with
    “cp $ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapworcl1”
    “cp $ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapworcl2” ?

    “mv $ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapw”
    would do the trick. 🙂

    Reply

    • Alexei,
      Actually, I tried to write general guide for mentioned topic,
      but following your tip will restrict environment to having both standby-es on the same machine…

      In any case – Thanks for sharing this Tip.

      Oleksandr

      Reply

      • The point is that you dont’t have to worry about the name of orapw file and one file could be enough.

        Actually, this tip (having just one orapw file instead of multiple orapw$ORACLE_SID files) allows to have only one password file for all the databases using the same $ORACLE_HOME .

        Indeed, if the instance does not find $ORACLE_HOME/dbs/orapw$ORACLE_SID it will be looking for this generic $ORACLE_HOME/dbs/orapw file (and use it even if REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE !) .

        It is very handy,especially when you have/want to use the same passwords for all your databases and especially useful for standby and policy-managed RACs (when you don’t [want to bother to] know for sure the name of the instance a particular host will have).

        But beware that if you use this generic orapw file and change, let’s say sys password, it will be changed for all the instances using this generic orapw. So, if it is not your intention, you have to ‘cp orapw orapw$ORACLE_SID’. It is the only restriction I see.

  3. Alexei,
    I think that this Tip is useful for development or testing environment,
    but I’ll not recommend it in production, unless customer doesn’t have own DBA
    and security setup need to be simplified as much as possible
    BTW:
    personally I prefer to follow general practice and if pwfile need to be shared,
    that just make link(unix/linux ln) to common file

    Reply

    • I agree, every company has its own rules, traditions and habits.

      I’ve found this default behaviour somewhere in Oracle documentation (don’t remember where) when I was wondering how policy-managed RAC uses orapw files, because it doesn’t know for sure on which host a particular instance will start up next time (if you, let’s say, resize your server pool) because you cannot specify it with srvctl, like spfile. So, it should be some generic password file naming.

      Reply

  4. Hi,

    I am trying to clone a database from active database with RMAN DUPLICATE TARGET TO FROM ACTIVE DATABASE. However, the RMAN target database that I’m using is already a PRIMARY for a dataguard setup and it appears this leads to an inconsistent state when RMAN starts recovering the auxiliary database, throwing an error such as:

    RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed start until change 60225729434 using backup controlfile
    ORA-00283: recovery session canceled due to errors
    ORA-19909: datafile 1 belongs to an orphan incarnation
    ORA-01110: data file 1: ‘+//system01.dbf’

    So, the question I really want to ask is whether we can create a DUPLICATE/CLONE from an ACTIVE DATABASE that is the PRIMARY of an existing DATA GUARD. So far it doesn’t appear to work. It works only if the target database is a STANDALONE i.e. not having any existing standby’s.

    Thanks.

    Reply

    • Hello,
      It is clear that there are no restrictions on duplicating PRIMARY database from existing DATA GUARD environment using DUPLICATE FROM ACTIVE DATABASE.
      it looks line you have some issue with incarnations and you have really strange file name ‘+//system01.dbf’ which looks like ASM based, but has wrong syntax.

      I have no details on your environment, so restricted in possibilities of helping.

      Oleksandr.

      Reply

Leave a comment