Oracle's Barking !!!: Physical Standby : RMAN Active Duplicate method (Without backup)

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

## 11g Standby : RMAN Active Duplicate method (Without backup) ##

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

Primary Database Information

*************************************

Host: source.localdomain (192.168.56.101)

DB_NAME: prod

DB_UNIQUE_NAME: prod

Disk_Group: /disk01, /FRA

Standby Database Information

*************************************

Host: target.localdomain (192.168.56.102)

DB_NAME: prod

DB_UNIQUE_NAME: prod_sty

Disk_Group: /disk02, /FRA

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

Basic pre-check before deploying Active Standby database server.

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

(i) Primary Server, Instance, Databases details.

********************************************************

NAME INSTANCE_NAME HOST_NAME DATABASE_ROLE LOG_MODE LOGINS OPEN_MODE STARTUP_TIME VERSION

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

PROD prod source.localdomain PRIMARY ARCHIVELOG ALLOWED READ WRITE 2017:07:31 22:54:15 11.2.0.4.0

(ii) Disgroups Details of Primary Side.

**********************************************

[oracle@source ~]$ df -h /disk01

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/vg_source-lv_root

86G 14G 68G 17% /

[oracle@source ~]$ df -h /FRA

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/vg_source-lv_root

86G 14G 68G 17% /

(iii) Archive log Should be enable & Flash Recovery Area Details.

******************************************************************************

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 18

Next log sequence to archive 20

Current log sequence 20

SQL> show parameter recovery

NAME TYPE VALUE

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

db_recovery_file_dest string /FRA/prod

db_recovery_file_dest_size big integer 4182M

recovery_parallelism integer 0

(iv) check whether standby server pinging from primary.

******************************************************************

[oracle@source ~]$ ping 192.168.56.102

PING 192.168.56.102 (192.168.56.102) 56(84) bytes of data.

64 bytes from 192.168.56.102: icmp_seq=1 ttl=64 time=0.510 ms

64 bytes from 192.168.56.102: icmp_seq=2 ttl=64 time=0.458 ms

64 bytes from 192.168.56.102: icmp_seq=3 ttl=64 time=0.465 ms

64 bytes from 192.168.56.102: icmp_seq=4 ttl=64 time=0.540 ms

--- 192.168.56.102 ping statistics ---

4 packets transmitted, 4 received, 0% packet loss, time 3331ms

rtt min/avg/max/mdev = 0.458/0.493/0.540/0.037 ms

(v) check whether primary server pinging from standby.

*******************************************************************

[oracle@target ~]$ ping 192.168.56.101

PING 192.168.56.101 (192.168.56.101) 56(84) bytes of data.

64 bytes from 192.168.56.101: icmp_seq=1 ttl=64 time=0.195 ms

64 bytes from 192.168.56.101: icmp_seq=2 ttl=64 time=0.402 ms

64 bytes from 192.168.56.101: icmp_seq=3 ttl=64 time=0.216 ms

64 bytes from 192.168.56.101: icmp_seq=4 ttl=64 time=0.184 ms

--- 192.168.56.101 ping statistics ---

4 packets transmitted, 4 received, 0% packet loss, time 3305ms

rtt min/avg/max/mdev = 0.184/0.249/0.402/0.089 ms

(vi) File-System Details of Standby Side.

************************************************

[oracle@target ~]$ df -h /disk02

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/vg_target-lv_root

86G 22G 60G 27% /

[oracle@target ~]$ df -h /FRA

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/vg_target-lv_root

86G 22G 60G 27% /

**************

Primary Site :

**************

Step: 1

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

Enable Database Force Logging ( @Primary Site).

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

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string prod

SQL> show parameter db_uni

NAME TYPE VALUE

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

db_unique_name string prod

Step: 2

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

Setting up up Log_Archive_Config Parameter ( @Primary Site).

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

SQL> show parameter LOG_ARCHIVE_CONFIG

NAME TYPE VALUE

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

log_archive_config string

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,prod_sty)';

System altered.

SQL> show parameter LOG_ARCHIVE_CONFIG

NAME TYPE VALUE

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

log_archive_config string DG_CONFIG=(prod,prod_sty)

Step: 3

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

Setting up Log_Archive_Dest_1 (To store Local Archive log ) & Log_Archive_Dest_2 ( To Send local archive to remote server 'Standby' ) Parameter ( @Primary Site).

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

SQL> show parameter LOG_ARCHIVE_DEST_1

NAME TYPE VALUE

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

log_archive_dest_1 string

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod';

System altered.

SQL> show parameter LOG_ARCHIVE_DEST_2

NAME TYPE VALUE

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

log_archive_dest_2 string

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=prod_sty ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod_sty';

System altered.

Step: 4

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

Setting up up following parameters ( @Primary Site).

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

SQL> show parameter LOG_ARCHIVE_FORMAT

NAME TYPE VALUE

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

log_archive_format string %t_%s_%r.dbf

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

System altered.

SQL> show parameter LOG_ARCHIVE_MAX_PROCESSES

NAME TYPE VALUE

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

log_archive_max_processes integer 4

SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

NAME TYPE VALUE

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

remote_login_passwordfile string EXCLUSIVE

SQL> sho parameter fal_client

NAME TYPE VALUE

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

fal_client string

SQL> ALTER SYSTEM SET fal_client='prod';

System altered.

SQL> sho parameter fal_server

NAME TYPE VALUE

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

fal_server string

SQL> ALTER SYSTEM SET fal_server='prod_sty';

System altered

SQL> show parameter DB_FILE_NAME_CONVERT

NAME TYPE VALUE

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

db_file_name_convert string

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/disk02','/disk01' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/FRA','/FRA','/disk02','/disk01' SCOPE=SPFILE;

System altered.

SQL> show parameter STANDBY_FILE_MANAGEMENT

NAME TYPE VALUE

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

standby_file_management string MANUAL

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

Step: 5

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

Setting up listener file to add static entry of Primary ( @Primary Site).

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

LISTENER_PROD =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1522))

)

SID_LIST_LISTENER_PROD =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = prod)

)

*********************************************************************

Remark: First stop dynamic listener the start static listener

*********************************************************************

[oracle@source admin]$ lsnrctl stop

[oracle@source admin]$ lsnrctl start LISTENER_PROD

Step: 6

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

Setting up tnsname.ora file with following configuration ( @Primary Site).

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

PROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = prod)

)

#######################################################################

PROD_STY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1527))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = prod_sty)

)

Step: 7

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

Transfer password file to standby dbs location via scp command ( @Primary Site).

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

[oracle@dell ~]$ cd /u01/app/oracle/porduct/11.2.0/db_1/dbs/

[oracle@dell dbs]$ ls -lrth orapwprod

-rw-r----- 1 oracle oinstall 1.5K Oct 10 18:53 orapwprod

[oracle@dell stby]$ scp orapwprod oracle@192.168.56.102:/u01/app/oracle/product/11.2.0/db_1/dbs

The authenticity of host '192.168.56.102 (192.168.56.102)' can't be established.

RSA key fingerprint is 49:bf:88:ea:da:29:80:0e:36:68:e4:9c:79:68:02:21.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.56.102' (RSA) to the list of known hosts.

oracle@192.168.56.102's password:

orapwprod 1536 1.5KB/s 00:00

*************

Standby Site:

*************

Step: 8

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

Edit listener file to add static entry of standby instance ( @Standby Site).

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

LISTENER_PROD_STY =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1527))

)

SID_LIST_LISTENER_PROD_STY =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = prod_sty)

)

Step: 9

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

Start your listener and Check listener status ( @Standby Site).

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

[oracle@target admin]$ lsnrctl start LISTENER_PROD_STY

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 31-JUL-2017 23:30:28

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/target/listener_prod_sty/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.102)(PORT=1527)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.102)(PORT=1527)))

STATUS of the LISTENER

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

Alias LISTENER_PROD_STY

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 31-JUL-2017 23:30:28

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/target/listener_prod_sty/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.102)(PORT=1527)))

Services Summary...

Service "prod_sty" has 1 instance(s).

Instance "prod_sty", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Step: 10

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

Edit tnsname.ora file to add entry ( @Standby Site).

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

vi this file via oracle user "/u01/app/oracle/DELLuct/11.2.0/db_1/network/admin/tnsnames.ora"

PROD =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = prod)

)

######################################################################

PROD_STY =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1527))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = prod_sty)

)

Step: 11

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

Create directory structure adump via oracle user ( @Standby Site).

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

[oracle@dell-dr opt]$ mkdir -p /u01/app/oracle/admin/prod_sty/adump

[oracle@target dbs]$ mkdir -p /disk02/prod/prod

[oracle@target dbs]$ mkdir -p /FRA/prod_sty

Step: 12

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

Make a copy of password file "orapwdell" of with the name of standby db_unique_name of "orapwdell_dr" ( @Standby Site).

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

[oracle@dell-dr dbs]$ mv orapwprod orapwprod_sty

Step: 13

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

Make a dummy initdell_dr.ora pfile with only to parameter. ( @Standby Site).

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

[oracle@dell-dr dbs]$ vi prod_sty.ora

db_unique_name='prod_dr'

db_name='prod'

Step: 14

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

Set the environment for standby server like SID & ORACLE_HOME ( @Standby Site).

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

vi /etc/oratab

prod_sty:/u01/app/oracle/product/11.2.0/db_1:N

:wq

[oracle@target dbs]$ . oraenv

ORACLE_SID = [test] ? prod_sty

The Oracle base remains unchanged with value /u01/app/oracle

Step: 15

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

Connect with sqlplus with sysdba & startup instance in "NOMOUNT" state with dummy pfile. ( @Standby Site).

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

[oracle@target ~]$ . oraenv

ORACLE_SID = [oracle] ? prod_sty

The Oracle base has been set to /u01/app/oracle

[oracle@target dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 31 23:38:18 2017

Connected to an idle instance.

SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/product/11.2.0/db_1/dbs/initprod_sty.ora'

ORACLE instance started.

Total System Global Area 217157632 bytes

Fixed Size 2251816 bytes

Variable Size 159384536 bytes

Database Buffers 50331648 bytes

Redo Buffers 5189632 bytes

SQL>

Step: 16

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

Open duplicate session and connect with rman ( @Standby Site).

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

[oracle@target ~]$ rman TARGET sys/sys@PROD AUXILIARY sys/sys@PROD_STY

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 31 23:39:39 2017

connected to target database: PROD (DBID=362197903)

connected to auxiliary database: PROD (not mounted)

RMAN>

Step: 17

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

Run the following script at rman prompt ( @Standby Site).

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

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 'prod','prod_sty'

set db_unique_name='prod_sty'

set db_file_name_convert='/disk01','/disk02'

set log_file_name_convert='/FRA','/FRA','/disk01','/disk02'

set control_files='/disk02/prod/prod/controlfile_01.ctl'

set log_archive_max_processes='5'

set fal_client='prod_sty'

set fal_server='prod'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(prod_sty,prod)'

set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

set log_archive_dest_2='service=prod NOAFFIRM ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=prod'

NOFILENAMECHECK;

}

Example :

**********

RMAN> run {

2> allocate channel prmy1 type disk;

3> allocate channel prmy2 type disk;

4> allocate channel prmy3 type disk;

5> allocate channel prmy4 type disk;

6> allocate auxiliary channel stby type disk;

8> duplicate target database for standby from active database

9> spfile

10> parameter_value_convert 'prod','prod_sty'

11> set db_unique_name='prod_sty'

12> set db_file_name_convert='/disk01','/disk02'

13> set log_file_name_convert='/FRA','/FRA','/disk01','/disk02'

14> set control_files='/disk02/prod/prod/controlfile_01.ctl'

15> set log_archive_max_processes='5'

16> set fal_client='prod_sty'

17> set fal_server='prod'

18> set standby_file_management='AUTO'

19> set log_archive_config='dg_config=(prod_sty,prod)'

20> set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

21> set log_archive_dest_2='service=prod NOAFFIRM ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=prod'

22> NOFILENAMECHECK;

23> }

using target database control file instead of recovery catalog

allocated channel: prmy1

channel prmy1: SID=43 device type=DISK

allocated channel: prmy2

channel prmy2: SID=46 device type=DISK

allocated channel: prmy3

channel prmy3: SID=42 device type=DISK

allocated channel: prmy4

channel prmy4: SID=44 device type=DISK

allocated channel: stby

channel stby: SID=20 device type=DISK

Starting Duplicate Db at 01-AUG-17

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwprod' auxiliary format

'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwprod_sty' targetfile

'/u01/app/oracle/product/11.2.0/db_1/dbs/spfileprod.ora' auxiliary format

'/u01/app/oracle/product/11.2.0/db_1/dbs/spfileprod_sty.ora' ;

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

}

executing Memory Script

Starting backup at 01-AUG-17

Finished backup at 01-AUG-17

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

contents of Memory Script:

{

sql clone "alter system set audit_file_dest =

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

'''' scope=spfile";

sql clone "alter system set db_recovery_file_dest =

''/FRA/prod_sty'' comment=

'''' scope=spfile";

sql clone "alter system set dispatchers =

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

'''' scope=spfile";

sql clone "alter system set db_unique_name =

''prod_sty'' comment=

'''' scope=spfile";

sql clone "alter system set db_file_name_convert =

''/disk01'', ''/disk02'' comment=

'''' scope=spfile";

sql clone "alter system set log_file_name_convert =

''/FRA'', ''/FRA'', ''/disk01'', ''/disk02'' comment=

'''' scope=spfile";

sql clone "alter system set control_files =

''/disk02/prod/prod/controlfile_01.ctl'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_max_processes =

5 comment=

'''' scope=spfile";

sql clone "alter system set fal_client =

''prod_sty'' comment=

'''' scope=spfile";

sql clone "alter system set fal_server =

''prod'' 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=(prod_sty,prod)'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_dest_1 =

''LOCATION=USE_DB_RECOVERY_FILE_DEST'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_dest_2 =

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

'''' scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

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

sql statement: alter system set db_recovery_file_dest = ''/FRA/prod_sty'' comment= '''' scope=spfile

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

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

sql statement: alter system set db_file_name_convert = ''/disk01'', ''/disk02'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/FRA'', ''/FRA'', ''/disk01'', ''/disk02'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''/disk02/prod/prod/controlfile_01.ctl'' comment= '''' scope=spfile

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

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

sql statement: alter system set fal_server = ''prod'' 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=(prod_sty,prod)'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''LOCATION=USE_DB_RECOVERY_FILE_DEST'' comment= '''' scope=spfile

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

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1653518336 bytes

Fixed Size 2253784 bytes

Variable Size 1006636072 bytes

Database Buffers 637534208 bytes

Redo Buffers 7094272 bytes

allocated channel: stby

channel stby: SID=18 device type=DISK

contents of Memory Script:

{

backup as copy current controlfile for standby auxiliary format '/disk02/prod/prod/controlfile_01.ctl';

}

executing Memory Script

Starting backup at 01-AUG-17

channel prmy1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prod.f tag=TAG20170801T001239 RECID=6 STAMP=950832760

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

Finished backup at 01-AUG-17

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

"/disk02/prod/prod/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/disk02/prod/prod/system01.dbf";

set newname for datafile 2 to

"/disk02/prod/prod/sysaux01.dbf";

set newname for datafile 3 to

"/disk02/prod/prod/undotbs01.dbf";

set newname for datafile 4 to

"/disk02/prod/prod/users01.dbf";

set newname for datafile 5 to

"/disk02/prod/prod/example01.dbf";

backup as copy reuse

datafile 1 auxiliary format

"/disk02/prod/prod/system01.dbf" datafile

2 auxiliary format

"/disk02/prod/prod/sysaux01.dbf" datafile

3 auxiliary format

"/disk02/prod/prod/undotbs01.dbf" datafile

4 auxiliary format

"/disk02/prod/prod/users01.dbf" datafile

5 auxiliary format

"/disk02/prod/prod/example01.dbf" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /disk02/prod/prod/temp01.dbf in control file

executing command: SET NEWNAME

Starting backup at 01-AUG-17

channel prmy1: starting datafile copy

input datafile file number=00001 name=/disk01/prod/prod/system01.dbf

channel prmy2: starting datafile copy

input datafile file number=00002 name=/disk01/prod/prod/sysaux01.dbf

channel prmy3: starting datafile copy

input datafile file number=00005 name=/disk01/prod/prod/example01.dbf

channel prmy4: starting datafile copy

input datafile file number=00003 name=/disk01/prod/prod/undotbs01.dbf

output file name=/disk02/prod/prod/undotbs01.dbf tag=TAG20170801T001246

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

channel prmy4: starting datafile copy

input datafile file number=00004 name=/disk01/prod/prod/users01.dbf

output file name=/disk02/prod/prod/users01.dbf tag=TAG20170801T001246

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

output file name=/disk02/prod/prod/example01.dbf tag=TAG20170801T001246

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

output file name=/disk02/prod/prod/sysaux01.dbf tag=TAG20170801T001246

channel prmy2: datafile copy complete, elapsed time: 00:01:31

output file name=/disk02/prod/prod/system01.dbf tag=TAG20170801T001246

channel prmy1: datafile copy complete, elapsed time: 00:01:41

Finished backup at 01-AUG-17

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=6 STAMP=950832867 file name=/disk02/prod/prod/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=7 STAMP=950832867 file name=/disk02/prod/prod/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=8 STAMP=950832867 file name=/disk02/prod/prod/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=9 STAMP=950832867 file name=/disk02/prod/prod/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=10 STAMP=950832867 file name=/disk02/prod/prod/example01.dbf

Finished Duplicate Db at 01-AUG-17

released channel: prmy1

released channel: prmy2

released channel: prmy3

released channel: prmy4

released channel: stby

RMAN>

Loging to Sqlplus and create standby log, First count online redo log on primary and create standby log on primary +1, means if primary have N online redo log then @ standby you have to create N+1

*********************************************************************************

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/disk02/prod/prod/standby_redo_01.log' size 52M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/disk02/prod/prod/standby_redo_02.log' size 52M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/disk02/prod/prod/standby_redo_03.log' size 52M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/disk02/prod/prod/standby_redo_04.log' size 52M;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Standy Created Now check it's details:

**************************************

NAME INSTANCE_NAME HOST_NAME DATABASE_ROLE LOG_MODE LOGINS OPEN_MODE STARTUP_TIME VERSION

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

PROD prod_sty target.localdomain PHYSICAL STANDBY ARCHIVELOG ALLOWED MOUNTED 2017:08:01 00:12:37 11.2.0.4.0

Keep it in recovery:

****************

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Verifications : On Standby:

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

On Primary :

Do some redo log switches

SQL> ALTER SYSTEM SWITCH LOGFILE;

On Standby:

Verify that the recent archived log file is shifted to standby side and applied with the following commands.

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

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

Other Useful Information:

To Cancel the Standby Recovery Mode:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Useful commands To Troubleshoot:

select message from v$dataguard_status where dest_id = 2;

select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;

select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log;

select thread#, sequence#, first_change#, next_change# from v$log_history;

To Verify Recover Process On Standby:

select thread#, sequence# from v$log where status='CURRENT'; (On Primary)

select thread#, sequence#, status from v$managed_standby where process=’MRP0'; (On Standby)`

Oracle's Barking !!!

Wednesday, August 2, 2017

Physical Standby : RMAN Active Duplicate method (Without backup)

No comments:

Post a Comment