Search

Monday, October 1, 2012

Oracle 11gr2 dataguard setup with flashback enabled with zero down time using RMAN ACTIVE Database Duplication method

Dataguard setup can be achieve with zero time only possible if  Primary DB already archive log enabled. Otherwise down time required.

Primary DB : suriya
Standby DB: suriyadr

Prerequistes:
1. Primary DB must be in archive log mode.(
2. In this setup Flashback feature enabled in Primary DB

1) Enableing database to archive log mode:

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence 1
Current log sequence 2

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfileprime.ora

SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initsuriya.ora' from spfile;

File created.

SQL> !mkdir -p /u01/app/oracle/oradata/suriya/archive
Created

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/suriya/archive' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
 
SQL> startup mount;
ORACLE instance started.

Total System Global Area 818401280 bytes
Fixed Size 2217792 bytes
Variable Size 482347200 bytes
Database Buffers 331350016 bytes
Redo Buffers 2486272 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/prime/archive
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL>

SQL> !ls -lrt /u01/app/oracle/oradata/suriya/archive
total 26388
-rw-r----- 1 oracle dba 26984960 Sep 29 02:36 1_2_795233705.dbf

2) From here consider database already archive log enabled so we can setup the dataguard with zero down-time

SQL> select force_logging from v$database;

FOR
---
YES

SQL> col member for a56
SQL> select l.group#, member, bytes, l.status from v$log l, v$logfile lf where lf.group#=l.group# order by group#;

GROUP# MEMBER BYTES
---------- -------------------------------------------------------- ----------
STATUS
----------------
1 /u01/app/oracle/oradata/prime/redo01.log 52428800
INACTIVE

2 /u01/app/oracle/oradata/prime/redo02.log 52428800
INACTIVE

3 /u01/app/oracle/oradata/prime/redo03.log 52428800
CURRENT


SQL> alter database add standby logfile '/u01/app/oracle/oradata/suriya/stby_redo01.log' size 50M;
Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/suriya/stby_redo02.log' size 50M;
Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/suriya/stby_redo03.log' size 50M;
Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/suriya/stby_redo04.log' size 50M;
Database altered.

SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
GROUP# BYTES
---------- ----------
4 52428800
5 52428800
6 52428800
7 52428800

SQL> select value from v$parameter where name = 'db_unique_name';
VALUE
--------------------------------------------------------------------------------
prime

SQL> select group#, member, type, IS_RECOVERY_DEST_FILE, status from v$logfile;

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


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


1 /u01/app/oracle/oradata/suriya/redo01.log ONLINE NO


GROUP# MEMBER TYPE IS_
---------- -------------------------------------------------------- ------- ---
STATUS
-------
4 /u01/app/oracle/oradata/suriya/stby_redo01.log STANDBY NO


5 /u01/app/oracle/oradata/suriya/stby_redo02.log STANDBY NO


6 /u01/app/oracle/oradata/suriya/stby_redo03.log STANDBY NO


GROUP# MEMBER TYPE IS_
---------- -------------------------------------------------------- ------- ---
STATUS
-------
7 /u01/app/oracle/oradata/suriya/stby_redo04.log STANDBY NO

7 rows selected.

sql>alter system set log_archive_config='DG_CONFIG=(SURIYA, SURIYADR)';

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/flash_recovery_area/SURIYA/onlinelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SURIYA';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=SURIYADR LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SURIYADR';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.

SQL> alter system set FAL_SERVER=SURIYADR;
System altered.

SQL> alter system set FAL_CLIENT=SURIYA;
System altered.

SQL> alter system set standby_file_management=auto;
System altered.

SQL> alter system set remote_loging_passwordfile=exclusive;
System altered.

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

System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/flash_recovery_area/SURIYADR/onlinelog/','/u01/app/oracle/flash_recovery_area/SURIYA/onlinelog/' scope=spfile;

System altered.

Setup SqlNet connectivity between primary database and standby database ON BOTH NODE
Primary side ( Listener configuration)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.81 )(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = suriya )
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = suriya)
)
)

primary side (tnsnames.ora)

SURIYADR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.83)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = suriyadr)
)
)


Standby side Listener Configuration
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.83)(PORT = 1525))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = suriyadr )
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = suriyadr)
)
)

standby database tns configuration

SURIYA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.81)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = suriya)
)
)

#this standby database tns entry configuration is required on standby location because
#we are performing the active database duplication from standby side database.

SURIYADR=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.83)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = suriyadr)
)
)

creating password file
orapwd file=orapwsuriya password=oracle entries=5 force=y

[oracle@rak1 dbs]$ scp orapwsuriya oracle@192.168.1.83:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwsuriyadr
orapwsuriya                                                                                                                           100% 2048 2.0KB/s 00:00

Pfile created for standby database and parameter changes are also achieved for flashback database

Copy the init parameter nessarey change
*.audit_file_dest='/u01/app/oracle/admin/suriyadr/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/suriyadr/control01.ctl','/u01/app/oracle/flash_recovery_area/suriyadr/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/suriya','/u01/app/oracle/oradata/suriyadr'
*.db_name='suriya'
db_unique_name=suriyadr
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=suriyaXDB)'
*.fal_client='SURIYADR'
*.fal_server='SURIYA'
*.log_archive_config='DG_CONFIG=(SURIYA, SURIYADR)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SURIYADR'
*.log_archive_dest_2='SERVICE=SURIYA LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SURIYA'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='/u01/app/oracle/flash_recovery_area/SURIYADR/onlinelog/','/u01/app/oracle/flash_recovery_area/SURIYA/onlinelog/'
*.memory_target=822083584
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'


[oracle@rak1 tmp]$ scp initsuriyadr.ora oracle@192.168.1.83:/u01/app/oracle/product/11.2.0/db_1/dbs/

3)Creating nessary directory structure in standby database location

[oracle@rak3 admin]$ mkdir -p /u01/app/oracle/admin/suriyadr/adump
[oracle@rak3 admin]$ mkdir -p /u01/app/oracle/oradata/suriyadr
[oracle@rak3 admin]$ mkdir -p /u01/app/oracle/flash_recovery_area/suriyadr
[oracle@rak3 admin]$ mkdir -p /u01/app/oracle/flash_recovery_area/SURIYADR/onlinelog

In standby database location primary directory required because (during the media recovery from the standby database, it creates the redo logs generation as same directory structure of redolog file location as primary database. So i am creating the following the directory structure in standby location.

mkdir -p /u01/app/oracle/oradata/suriya/

also creating the following directory structure in primary database location

mkdir -p /u01/app/oracle/oradata/suriyadr/


4)Check the connectivity from standby database location net configuration are archived properly and start
the RMAN active database duplication

SQL> conn sys/oracle@suriya as sysdba;
Connected.
SQL> conn sys/oracle@suriyadr as sysdba

5)On standby DB Locatoin

[oracle@rak3 admin]$ . oraenv
ORACLE_SID = [oracle] ? suriyadr
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@rak3 admin]$ rman target sys/oracle@suriya auxiliary sys/oracle@suriyadr

This output is created by RMAN
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 29 09:50:39 2012

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

connected to target database: SURIYA (DBID=3309137318)

[oracle@rak3 admin]$ rman target sys/oracle@suriya auxiliary sys/oracle@suriyadr

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 29 09:56:19 2012

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

connected to target database: SURIYA (DBID=3309137318)
connected to auxiliary database: SURIYA (not mounted)

Duplicate for standby

Below is the RMAN run command to perform the database duplication from and active database along with brief explanations for the options used in the duplicate.

FOR STANDBY – the duplicate is for use as a standby so a DBID change will not be forced.
FROM ACTIVE DATABASE – instructs RMAN to use the active target database instead of disk based backups.
DORECOVER – do recovery bringing the standby database up to the current point in time.
SPFILE – values for parameters specific to the auxiliary instance can be set here.
NOFILENAMECHECK – this option is added because the duplicate database files uses the same name as the source database.

RMAN> duplicate target database for standby from active database dorecover;

Starting Duplicate Db at 29-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=27 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwsuriya' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwsuriyadr' ;
}
executing Memory Script

Starting backup at 29-SEP-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
Finished backup at 29-SEP-12

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/suriyadr/control01.ctl';
restore clone controlfile to '/u01/app/oracle/flash_recovery_area/suriyadr/control02.ctl' from
'/u01/app/oracle/oradata/suriyadr/control01.ctl';
}
executing Memory Script

Starting backup at 29-SEP-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_suriya.f tag=TAG20120929T095829 RECID=1 STAMP=795261527
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 29-SEP-12

Starting restore at 29-SEP-12
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 29-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/suriyadr/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/suriyadr/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/suriyadr/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/suriyadr/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/suriyadr/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/suriyadr/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/suriyadr/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/suriyadr/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/suriyadr/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 29-SEP-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/suriya/system01.dbf
output file name=/u01/app/oracle/oradata/suriyadr/system01.dbf tag=TAG20120929T095915
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/suriya/sysaux01.dbf

.
.
.

RMAN>

NOTE : We get warnings at the end of the rman  active rupilicatoin method. these can be ignored as these are due to the initialization parameter STANDBY_FILE_MANAGEMENT is set to AUTO.

 
6)On the standby start the managed recovery process.

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

Database altered.

7)So RMAN duplicate is completed along with the dataguard environment. Now need to validate that that logs are shipping and applying. On the standby database issue the following command.

SQL> select sequence#, first_time, next_time, applied
  2  from v$archived_log
  3  order by sequence#;

(Note: applied column must "yes" for all archive logs)

8)On the primary database location switch the redo logs a few times and use archive log list to view information on the oldest, current and next log sequence.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> archive log list

SQL> select sequence#, first_time, next_time, applied
  2  from v$archived_log
  3  order by sequence#;

(Note: applied column must "yes" for all archive logs and match the sequence# from the primary location output taken from point:2)


Posted by at