oracle dataguard setup

Creating Physical Standby using RMAN Duplicate Without Shutting down The Primary [ID 789370.1]

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

Modified 03-JUL-2010 Type HOWTO Status MODERATED

In this Document
Goal
Solution
References



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

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.



Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 - Release: 10.2 to 10.2
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4
Goal
The following note describes step-by-step procedure to create physical standby by using RMAN duplicate without shutting down the primary (Production) database.

Database Name :- prim
Primary db_unique_name :- prim
standby db_unique_name :- stdby

Primary Hostname :- raca.idc.oracle.com
standby Hostname :- core1.idc.oracle.com

Solution
1.Enable force logging.
2.Create SRL(standby redo logs).
3.Make proper changes in the parameter file of primary.
4.Backup the database that includes backup of datafiles, archivelogs and controlfile for standby and copy the backups to standby server.
5.Create the parameter file for standby,
6.Establish the connectivity from primary to standby.
7. Move backup to standby.
8 and 9. Start the standby instance.
10.Use RMAN Duplicate command to create the standby database.
11. Create SRL.
12.Start the MRP process,
13. Verify whether the log are shipped and applied properly @the standby

STEP: 1

Enable Force Logging on primary,

SQL> ALTER DATABASE FORCE LOGGING;
NOTE: Create password file if not present, also check if archiving enabled.

STEP: 2
Configure a Standby Redo Log on primary,


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 '/u01/app/oracle/databases/prim/redo/log3a.log' size 50m;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/databases/prim/redo/log4a.log' size 50m;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/databases/prim/redo/log5a.log' size 50m;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/databases/prim/redo/log6a.log' size 50m;


NOTE:
1. To check the number of SRL,
(maximum number of logfiles for each thread + 1) * maximum number of threads
2. The SRL size equal to ORL size.


For example, if the primary database has 3 log files for each thread and 2 threads, then 8 standbys redo log file groups are needed on the standby database.
Verify the standby redo log file groups were created.
SQL> SELECT GROUP#,ThREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

STEP :3

Modify the primary initialization parameter for dataguard on primary,


SQL>alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stdby)';
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/databases/prim/redo/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim';
SQL>alter system set LOG_ARCHIVE_DEST_2='SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
SQL>alter system set FAL_SERVER=stdby;
SQL>alter system set FAL_CLIENT=prim;
SQL>alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/databases/stdby/data/','/u01/app/oracle/databases/prim/data' scope=spfile;
SQL>alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/databases/stdby/redo/','/u01/app/oracle/databases/prim/redo' scope=spfile;
SQL>alter system set standby_file_management=auto;
STEP:4
Run the backup job at the primary by connecting to target and catalog DB(if any)

run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/u01/app/oracle/databases/stage/%U';
backup archivelog all format '/u01/app/oracle/databases/stage/%U';
backup current controlfile for standby format '/u01/app/oracle/databases/stage/%U';
}

STEP :5

Create parameter file on primary copy it to standby and make the necessary changes,

SQL>create pfile='/u01/app/oracle/databases/prim/stage/initstdby.ora' from spfile;
STEP :6

Establish the connectivity,
Create net services on both primary and standby,
At prim server,

prim =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =raca.idc.oracle.com )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)
)
)

stdby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =core1.idc.oracle.com )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stdby)
)
)

At standby server,

prim =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =raca.idc.oracle.com )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)
)
)

stdby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =core1.idc.oracle.com )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stdby)
)
)

STEP : 7
Move the files to standby server,

a. Create the same directory on standby server and copy the backups.
os standby,

$mkdir -p /u01/app/oracle/databases/stage/
[oracle@raca prim]$ scp /u01/app/oracle/databases/stage/* core1.idc.oracle.com:/u01/app/oracle/databases/stage/

b. Alternatively we can copy the backups to standby different directory. On standby server connect to RMAN target as primary and auxiliary instance or if your primary is having catalog database then connect to target as primary, catalog database and auxiliary instance. Catalog those backup pieces to let the controlfile of primary or catalog database to know the backup information.



For details on how to catalog backup piece refer,

NOTE 470463.1 - How To Catalog Backups / Archivelogs / Datafile Copies / Controlfile Copies


STEP: 8

Make the necessary changes on the copied initstdby.ora file on standby.

db_name=prim
db_unique_name=stdby
log_archive_config='DG_CONFIG=(prim,stdby)'
log_archive_dest_1='LOCATION=/u01/app/oracle/databases/stdby/redo/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
log_archive_dest_2='SERVICE=prim LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim'
log_archive_dest_state_1=ENABLE
db_file_name_convert='/u01/app/oracle/databases/prim/data/','/u01/app/oracle/databases/stdby/data/'
log_file_name_convert='/u01/app/oracle/databases/prim/redo/','/u01/app/oracle/databases/stdby/redo/'
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
standby_archive_dest='/u01/app/oracle/databases/stdby/arch/'
standby_file_management=AUTO
fal_client=stdby
fal_server=prim

STEP: 9

Create standby instance,

create the same password as that of primary. Alternatively we can copy the password file from primary and rename it on standby.

For example from primary copry the password file,

scp /u01/app/oracle/dbs/opapwprim core1.idc.oracle.com:/u01/app/oracle/dbs/
on standby,

$mv orapwprim orapwstdby

or create new password same as primary as mentioned below,

a. in UNIX,

$export ORACLE_SID
$orapwd file='$ORACLE_HOME/dbs/orapwstdby' password=sys entries=10;

in Windows,

d:>set ORACLE_SID=stdby
d:>oradim -new -sid stdby -intpwd sys

b Create necessary directories and start the auxiliary instance on standby

[oracle@core1 stdby]$ pwd
/u01/app/oracle/databases/stdby
[oracle@core1 stdby]$mkdir data redo oradata stage udump bdump cdump


SQL>create spfile from pfile='/u01/app/oracle/databases/stage/initstdby.ora';

SQL>start nomount;

STEP : 10 Use RMAN duplicate for standby on standby,
When 'dorecover' is specified in the duplicate for stanby command then do a archivelog switch on the target database and run the duplicate.

Example :
on primary,

sql> alter system archive log current;
on standby,
Connect to target(i.e. prim via connect string) and catalog database(if any) and this auxiliary database by,

$RMAN target /@prim catalog RMAN/RMAN@catdb auxiliary /

if no catalog database,

$RMAN target sys/@prim auxiliary /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 5 12:30:56 2009

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

connected to target database: PRIM (DBID=3971986030)
connected to auxiliary database: PRIM (DBID=3971986030, not open)


RMAN> duplicate target database for standby dorecover;
NOTE: We can use without dorecover clause also,
Example,
RMAN>duplicate target database for standby;

STEP : 11 Create SRL,

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 '/u01/app/oracle/databases/prim/redo/log3a.log' size 50m;
Refer, Step 2.

STEP : 12 Start the MRP(managed recovery process) on standby,


SQL> select name,db_unique_name,database_role, from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ---------------- --------------
PRIM STDBY PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect;

STEP : 13

Enable the log_archive_dest_2 on primary, which is to send the logs to standby server.

SQL>alter system set log_archive_dest_state_2=enable;

STEP :14

Check the standby whether it is in SYNC with primary,

A. Check the v$archived view on standby,
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
29 06-MAR-09 06-MAR-09
30 06-MAR-09 06-MAR-09
31 06-MAR-09 06-MAR-09
32 06-MAR-09 06-MAR-09
33 06-MAR-09 06-MAR-09
34 06-MAR-09 06-MAR-09
35 06-MAR-09 06-MAR-09
36 06-MAR-09 06-MAR-09
37 06-MAR-09 06-MAR-09
38 06-MAR-09 06-MAR-09
39 06-MAR-09 06-MAR-09

B. Do the log switch on primary,

SQL> ALTER SYSTEM SWITCH LOGFILE;

C. On standby,

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
29 06-MAR-09 06-MAR-09
30 06-MAR-09 06-MAR-09
31 06-MAR-09 06-MAR-09
32 06-MAR-09 06-MAR-09
33 06-MAR-09 06-MAR-09
34 06-MAR-09 06-MAR-09
35 06-MAR-09 06-MAR-09
36 06-MAR-09 06-MAR-09
37 06-MAR-09 06-MAR-09
38 06-MAR-09 06-MAR-09
39 06-MAR-09 06-MAR-09

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
40 06-MAR-09 07-MAR-09

SQL> select sequence#,applied from v$archived_log order by sequence#;
SEQUENCE# APP
---------- ---
29 YES
30 YES
31 YES
32 YES
33 YES
34 YES
35 YES
36 YES
37 YES
38 YES
39 YES

SEQUENCE# APP
---------- ---
40 YES[@more@]