Configuration of Duplicate Active Standby 11.2.0.4.0 on OracleLinux 6.4
******************************************************
If this is your first time building a standby, there is some terminology you need to know before going into any of the steps in creating your physical standby. It will help you to better understand your dataguard environment and what is being done, instead of simply copying a number of steps. These are just the definitions in Oracle’s documentation, but they will help you avoid the impracticable search.LOG_ARCHIVE_DEST_n : It controls different aspects of how redo transport services transfer redo data from primary database destination to a standby.
This parameter has several attributes that are needed to setup your Dataguard environment, I will only mention the critical ones:
ASYNC : This is the default, the redo data generated by a transaction need not have been received at a destination which has this attribute before that transaction can commit.
OR
AFFIRM & NOAFFIRM : Control whether a redo transport destination acknowledges received redo data before or after writing it to the standby redo log. The default is NOAFFIRM.
DB_UNIQUE_NAME : Specifies a unique name for the database at this destination. You must specify a name; there is no default value.
VALID_FOR : Identifies when redo transport services can transmit redo data to destinations based on the following factors:
redo_log_type : whether online redo log files, standby redo log files, or both are currently being archived on the database at this destination
database_role : whether the database is currently running in the primary or the standby role
FAL_SERVER : Specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name.
FAL_CLIENT : Specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the FAL_SERVER initialization parameter, to refer to the FAL client.
The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database).
LOG_ARCHIVE_CONFIG : Enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs.
This parameter has several attributes, the most important for this exercise is below:
DG_CONFIG : Specifies a list of up to 30 unique database names (defined with the DB_UNIQUE_NAME initialization parameter) for all of the databases in the Data Guard configuration.
Primary Database Information
****************************
Host: dell.localdomain (192.168.56.101)
DB_NAME: dell
DB_UNIQUE_NAME: dell
Disk_Group: +DATA_01, +FRA_01
Standby Database Information
****************************
Host: dell-dr.localdomain (192.168.56.102)
DB_NAME: dell
DB_UNIQUE_NAME: dell_dr
Disk_Group: +DATA_DG_01, +FRA_DG_01
Basic pre-check before deploying Active Standby database server.
(i) Primary Server, Instance, Databases details.
NAME INSTANCE_NAME HOST_NAME DATABASE_R LOG_MODE LOGINS OPEN_MODE FLASHBACK_ON STARTUP_TIME VERSION
-------- ---------- ----------------------------------- ---------- --------------- ---------- --------------- --------------
DELL dell dell.localdomain PRIMARY ARCHIVELOG ALLOWED READ WRITE YES 2016:10:10 17:54:48 11.2.0.4.0
(ii) Disgroups Details of Primary Side.
GROUP_NUMBER NAME STATE TYPE Totalspace(GB) Freespace(GB) Pct_FREE_% OFFLINE_DISKS
------------ --------------- -------------------- --------------- ------ -------------- ------------- ---------- -----------
1 DATA_01 CONNECTED NORMAL 11.9960938 7.91601563 65.9882774 0
2 FRA_01 MOUNTED EXTERN 11.9960938 11.9453125 99.5766851 0
(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 3
Next log sequence to archive 5
Current log sequence 5
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA_01
db_recovery_file_dest_size big integer 4182M
recovery_parallelism integer 0
(iv) check whether standby server pinging from primary.
[oracle@dell ~]$ 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=2.00 ms
64 bytes from 192.168.56.102: icmp_seq=2 ttl=64 time=0.267 ms
64 bytes from 192.168.56.102: icmp_seq=3 ttl=64 time=0.238 ms
64 bytes from 192.168.56.102: icmp_seq=4 ttl=64 time=0.416 ms
64 bytes from 192.168.56.102: icmp_seq=5 ttl=64 time=0.220 ms
64 bytes from 192.168.56.102: icmp_seq=6 ttl=64 time=0.230 ms
^C64 bytes from 192.168.56.102: icmp_seq=7 ttl=64 time=0.275 ms
64 bytes from 192.168.56.102: icmp_seq=8 ttl=64 time=0.253 ms
^C
--- 192.168.56.102 ping statistics ---
8 packets transmitted, 8 received, 0% packet loss, time 7858ms
rtt min/avg/max/mdev = 0.220/0.487/2.000/0.574 ms
(v) check whether primary server pinging from standby.
[root@dell-dr 11.2.0.4.0]# 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.288 ms
64 bytes from 192.168.56.101: icmp_seq=2 ttl=64 time=0.310 ms
64 bytes from 192.168.56.101: icmp_seq=3 ttl=64 time=0.625 ms
64 bytes from 192.168.56.101: icmp_seq=4 ttl=64 time=0.634 ms
^C
--- 192.168.56.101 ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3544ms
rtt min/avg/max/mdev = 0.288/0.464/0.634/0.166 ms
(vi)Disgroups Details of Standby Side.
GROUP_NUMBER NAME STATE TYPE Totalspace(GB) Freespace(GB) OFFLINE_DISKS
------------ --------------- -------------------- --------------- ------ -------------- ------------- ---------- -----------
1 +DATA_DG_01 CONNECTED NORMAL 11.9960938 11.9960938 0
2 FRA_DG_01 MOUNTED EXTERN 11.9960938 11.9960938 0
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 dell
SQL> show parameter db_uni
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string dell
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=(dell,dell_dr)';
System altered.
SQL> show parameter LOG_ARCHIVE_CONFIG
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(dell,dell_dr)
Step : 3 Setting up 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> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+FRA_01 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dell';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=dell_dr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dell_dr';
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> ALTER SYSTEM SET fal_client='dell';
System altered.
SQL> ALTER SYSTEM SET fal_server='dell_dr';
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='+DATA_DG_01','+DATA_01' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='+FRA_DG_01','+FRA_01','+DATA_DG_01','+DATA_01' 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 tnsname.ora file with following configuration ( @Primary Site).
DELL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dell.localdomain)
(UA = A)
)
)
#######################################################################
DELL_DR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dell_dr)
(UA = A)
)
)
Step : 6 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 orapwdell
-rw-r----- 1 oracle oinstall 1.5K Oct 10 18:53 orapwdell
[oracle@dell stby]$ scp orapwdell 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:
orapwdell 100% 1536 1.5KB/s 00:00
Step : 7 Create Standby Redo Logs ( @Primary Site).
The DUPLICATE command automatically creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server.
ASMCMD> mkdir +FRA_01/DELL/STANDBYLOG (connected as grid user using asmcmd)
SQL> alter system set standby_file_management=manual scope=both;
System altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FRA_01/DELL/STANDBYLOG/standby_group_04.log' SIZE 52M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FRA_01/DELL/STANDBYLOG/standby_group_05.log' SIZE 52M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FRA_01/DELL/STANDBYLOG/standby_group_06.log' SIZE 52M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FRA_01/DELL/STANDBYLOG/standby_group_07.log' SIZE 52M;
Database altered.
SQL> alter system set standby_file_management=auto scope=both;
System altered.
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
3 ONLINE +DATA_01/dell/onlinelog/group_3.266.924888225 NO
3 ONLINE +DATA_01/dell/onlinelog/group_3.267.924888233 YES
2 ONLINE +DATA_01/dell/onlinelog/group_2.264.924888205 NO
2 ONLINE +DATA_01/dell/onlinelog/group_2.265.924888215 YES
1 ONLINE +DATA_01/dell/onlinelog/group_1.262.924888187 NO
1 ONLINE +DATA_01/dell/onlinelog/group_1.263.924888197 YES
4 STANDBY +FRA_01/dell/standbylog/standby_group_04.log NO
5 STANDBY +FRA_01/dell/standbylog/standby_group_05.log NO
6 STANDBY +FRA_01/dell/standbylog/standby_group_06.log NO
7 STANDBY +FRA_01/dell/standbylog/standby_group_07.log NO
10 rows selected.
Standby Site :
**************
Step : 7 Edit listener file to add static entry of standby instance ( @Standby Site).
Go to this location via grid user "/u01/app/grid/11.2.0/grid_home/network/admin" & vi listener.ora file.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=dell_dr)
)
)
Step : 8 Reload your listener and Check listener status ( @Standby Site).
[grid@dell-dr admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.4.0 - DELLuction on 10-OCT-2016 19:08:05
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[grid@dell-dr admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - DELLuction on 10-OCT-2016 19:08:14
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - DELLuction
Start Date 10-OCT-2016 16:52:51
Uptime 0 days 2 hr. 15 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/grid/11.2.0/grid_home/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/dell-dr/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dell-dr.localdomain)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "dell_dr" has 1 instance(s).
Instance "dell_dr", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Step : 9 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"
DELL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dell.localdomain)
)
)
######################################################################
DELL_DR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dell_dr)
(UA = A)
)
)
Step : 10 Create directory structure for standby redo log ( @Standby Site).
Step : 11 Create directory structure adump via oracle user ( @Standby Site).
[oracle@dell-dr opt]$ mkdir -p /u01/app/oracle/admin/dell_dr/adump
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]$ cp orapwdell orapwdell_dr
Step : 13 Make a dummy initdell_dr.ora pfile with only to parameter. ( @Standby Site).
[oracle@dell-dr dbs]$ vi initdell_dr.ora
db_unique_name='dell_dr'
db_name='dell'
[oracle@dell-dr dbs]$ ls -lrth
total 12K
-rw-r--r-- 1 oracle oinstall 2.8K May 15 2009 init.ora
-rw-r--r-- 1 oracle oinstall 40 Oct 10 19:30 initdell_dr.ora
-rw-r----- 1 oracle oinstall 1.5K Oct 10 19:31 orapwdell_dr
-rw-r----- 1 oracle oinstall 1.5K Oct 10 19:31 orapwdell
Step : 14 Set the environment for standby server like SID & ORACLE_HOME ( @Standby Site).
[oracle@dell-dr dbs]$ . oraenv
ORACLE_SID = [oracle] ? dell_dr
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/delluct/11.2.0/db_1
The Oracle base has been set to /u01/app/oracle
[oracle@dell-dr dbs]$
Step : 15 Connect with sqlplus with sysdba & startup instance in "NOMOUNT" state with dummy pfile. ( @Standby Site).
[oracle@dell-dr dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 delluction on Mon Oct 10 19:33:15 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/product/11.2.0/db_1/dbs/initdell_dr.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).
RMAN> rman TARGET sys/sys@DELL AUXILIARY sys/sys@DELL_DR
Recovery Manager: Release 11.2.0.4.0 - delluction on Mon Oct 10 19:55:50 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DELL (DBID=3895628907)
connected to auxiliary database: DELL (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 'dell','dell_dr'
set db_unique_name='dell_dr'
set db_file_name_convert='+DATA_01/DELL','+DATA_DG_01/DELL_DR'
set log_file_name_convert='+FRA_01/DELL','+FRA_DG_01/DELL_DR','+DATA_01/DELL','+FRA_DG_01/DELL_DR'
set control_files='+DATA_DG_01'
set log_archive_max_processes='5'
set fal_client='dell_dr'
set fal_server='dell'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(dell_dr,dell)'
set log_archive_dest_2='service=dell NOAFFIRM ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=dell'
NOFILENAMECHECK;
}
Example :
**********
RMAN> 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 'dell','dell_dr'
set db_unique_name='dell_dr'
set db_file_name_convert='+DATA_01/DELL/','+DATA_DG_01/DELL_DR/'
set log_file_name_convert='+FRA_01/DELL/','+FRA_DG_01/DELL_DR/','+DATA_01/DELL/','+FRA_DG_01/DELL_DR/'
set control_files='+DATA_DG_01'
set log_archive_max_processes='5'
set fal_client='dell_dr'
set fal_server='dell'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(dell,dell_dr)'
set log_archive_dest_2='service=dell NOAFFIRM ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=dell'
NOFILENAMECHECK;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22>
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=64 device type=DISK
allocated channel: prmy2
channel prmy2: SID=56 device type=DISK
allocated channel: prmy3
channel prmy3: SID=47 device type=DISK
allocated channel: prmy4
channel prmy4: SID=68 device type=DISK
allocated channel: stby
channel stby: SID=20 device type=DISK
Starting Duplicate Db at 18-OCT-16
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdell' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdell_dr' targetfile
'+DATA_01/dell/spfiledell.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/spfiledell_dr.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfiledell_dr.ora''";
}
executing Memory Script
Starting backup at 18-OCT-16
Finished backup at 18-OCT-16
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfiledell_dr.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/dell_dr/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=dell_drXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=+FRA_01 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dell_dr'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''dell_dr'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''+DATA_01/DELL/'', ''+DATA_DG_01/DELL_DR/'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''+FRA_01/DELL/'', ''+FRA_DG_01/DELL_DR/'', ''+DATA_01/DELL/'', ''+FRA_DG_01/DELL_DR/'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''+DATA_DG_01'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
5 comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''dell_dr'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''dell'' 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=(dell,dell_dr)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=dell NOAFFIRM ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=dell'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/dell_dr/adump'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=dell_drXDB)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=+FRA_01 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dell_dr'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''dell_dr'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''+DATA_01/DELL/'', ''+DATA_DG_01/DELL_DR/'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''+FRA_01/DELL/'', ''+FRA_DG_01/DELL_DR/'', ''+DATA_01/DELL/'', ''+FRA_DG_01/DELL_DR/'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''+DATA_DG_01'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''dell_dr'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''dell'' 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=(dell,dell_dr)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=dell NOAFFIRM ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=dell'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 545262632 bytes
Database Buffers 289406976 bytes
Redo Buffers 2355200 bytes
allocated channel: stby
channel stby: SID=23 device type=DISK
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA_DG_01/dell_dr/controlfile/current.263.925528925'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '+DATA_DG_01/dell_dr/controlfile/current.257.925528925';
sql clone "alter system set control_files =
''+DATA_DG_01/dell_dr/controlfile/current.257.925528925'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA_DG_01/dell_dr/controlfile/current.263.925528925'' comment= ''Set by RMAN'' scope=spfile
Starting backup at 18-OCT-16
channel prmy1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_dell.f tag=TAG20161018T032205 RECID=11 STAMP=925528927
channel prmy1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 18-OCT-16
sql statement: alter system set control_files = ''+DATA_DG_01/dell_dr/controlfile/current.257.925528925'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 545262632 bytes
Database Buffers 289406976 bytes
Redo Buffers 2355200 bytes
allocated channel: stby
channel stby: SID=23 device type=DISK
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
"+data_dg_01";
switch clone tempfile all;
set newname for datafile 1 to
"+data_dg_01";
set newname for datafile 2 to
"+data_dg_01";
set newname for datafile 3 to
"+data_dg_01";
set newname for datafile 4 to
"+data_dg_01";
set newname for datafile 5 to
"+data_dg_01";
backup as copy reuse
datafile 1 auxiliary format
"+data_dg_01" datafile
2 auxiliary format
"+data_dg_01" datafile
3 auxiliary format
"+data_dg_01" datafile
4 auxiliary format
"+data_dg_01" datafile
5 auxiliary format
"+data_dg_01" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +data_dg_01 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 18-OCT-16
channel prmy1: starting datafile copy
input datafile file number=00001 name=+DATA_01/dell/datafile/system.256.924887969
channel prmy2: starting datafile copy
input datafile file number=00002 name=+DATA_01/dell/datafile/sysaux.257.924887969
channel prmy3: starting datafile copy
input datafile file number=00005 name=+DATA_01/dell/datafile/example.269.924888355
channel prmy4: starting datafile copy
input datafile file number=00003 name=+DATA_01/dell/datafile/undotbs1.258.924887969
output file name=+DATA_DG_01/dell_dr/datafile/undotbs1.278.925528953 tag=TAG20161018T032225
channel prmy4: datafile copy complete, elapsed time: 00:01:06
channel prmy4: starting datafile copy
input datafile file number=00004 name=+DATA_01/dell/datafile/users.259.924887971
output file name=+DATA_DG_01/dell_dr/datafile/users.277.925529017 tag=TAG20161018T032225
channel prmy4: datafile copy complete, elapsed time: 00:00:15
output file name=+DATA_DG_01/dell_dr/datafile/example.271.925528949 tag=TAG20161018T032225
channel prmy3: datafile copy complete, elapsed time: 00:03:43
output file name=+DATA_DG_01/dell_dr/datafile/sysaux.270.925528949 tag=TAG20161018T032225
channel prmy2: datafile copy complete, elapsed time: 00:04:43
output file name=+DATA_DG_01/dell_dr/datafile/system.264.925528947 tag=TAG20161018T032225
channel prmy1: datafile copy complete, elapsed time: 00:05:14
Finished backup at 18-OCT-16
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=11 STAMP=925529261 file name=+DATA_DG_01/dell_dr/datafile/system.264.925528947
datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=925529261 file name=+DATA_DG_01/dell_dr/datafile/sysaux.270.925528949
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=925529261 file name=+DATA_DG_01/dell_dr/datafile/undotbs1.278.925528953
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=925529261 file name=+DATA_DG_01/dell_dr/datafile/users.277.925529017
datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=925529261 file name=+DATA_DG_01/dell_dr/datafile/example.271.925528949
Finished Duplicate Db at 18-OCT-16
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
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)
Happy Learning ..!
Pradeep Kumar
+919972201100
No comments:
Post a Comment