OS: Oracle Linux 7.4
DB: 11.2.0.4
Important Notes: First of all, we have tow know that we can't use scan name for active duplication from RAC and we can only use one of the nodes - no parallel.
On Standby side (auxiliary) we will use listener from database home not grid home. If we will use grid home then duplication will fail.
Step 1. Enable database in force logging mode if not - Primary
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
Step 2. Enable Archiving - Primary
srvctl stop database -d avisdb
srvctl start database -d avisdb -o mount
SQL>alter database archivelog;
SQL> alter database open;
(SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;)
Step 3. Add Standby Logfiles for each thread - Primary Database
alter database add standby logfile thread 1
group 7 ('+DATA','+FRA') SIZE 300M,
group 8 ('+DATA','+FRA') SIZE 300M,
group 9 ('+DATA','+FRA') SIZE 300M,
group 10 ('+DATA','+FRA') SIZE 300M;
alter database add standby logfile thread 2
group 11 ('+DATA','+FRA') SIZE 300M,
group 12 ('+DATA','+FRA') SIZE 300M,
group 13 ('+DATA','+FRA') SIZE 300M,
group 14 ('+DATA','+FRA') SIZE 300M;
Step 4. Execute followings in SQL Plus - Primary
alter system set db_unique_name=avisdb scope=spfile;
alter system set log_archive_config='DG_CONFIG=(avisdb,stby)' scope=both;
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=avisdb' scope=both;
alter system set log_archive_dest_2='SERVICE=stby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby' scope=both;
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
alter system set FAL_SERVER=stby scope=both;
alter system set FAL_CLIENT=avisdb scope=both;
alter system set DB_FILE_NAME_CONVERT='+DATA/stby/datafile','+DATA/avisdb/datafile' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='+DATA','+DATA','+FRA','+FRA' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
Step 5. Create standby controlfile and copy to Standby Database location - Primary DATABASE
alter database create standby controlfile as '/home/oracle/stby.ctl';
[oracle@rac1 ~]$ scp stby.ctl oracle@dbstby:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
oracle@dbstby's password:
stby.ctl 100% 33MB 86.6MB/s 00:00
Step 6. Copy password file to standbay ../dbs and change name - Primary DATABASE
[oracle@rac1 ~]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwavisdb1 oracle@dbstby:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
oracle@dbstby's password:
orapwavisdb1
mv orapwavisdb1 orapwstby
Step 7. Update listener.ora in Primary - Primary Database
/u01/app/11.2.0/grid/network/admin
[oracle@rac1 admin]$ cat listener.ora
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
# listener.ora.rac1 Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora.rac1
# Generated by Oracle configuration tools.
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3 = ON
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2 = ON
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_DUP = ON
LISTENER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
LISTENER_SCAN3 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN3))
)
)
LISTENER_SCAN2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN2))
)
)
LISTENER_SCAN1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = avisdb1)
)
)
Step 8. Update tnsnames.ora file in Primary -Primary Database
[oracle@db admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
avisdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =rac1)(PORT = 1521)) #HOST=rac-scan or rac1
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = avisdb)
(UR=A)
)
)
STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbstby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
(UR=A)
)
)
Step 9. Restart LISTENER - Primary Database
[oracle@rac1 admin]$ srvctl stop listener
[oracle@rac1 admin]$ srvctl start listener
Step 10. Add Standby Database parameters to system file for oracle which is used for ora environment. - Standby Database
[oracle@duplicate admin]$ vi /etc/oratab
stby:/u01/app/oracle/product/12.2.0/dbhome_1:N
Step 11. Update tnsnames.ora - Standby Database
oracle@dbstby admin]$ cat tnsnames.ora
avisdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =rac1)(PORT = 1521)) #HOST=rac-scan or rac1
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = avisdb)
(UR=A)
)
)
STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbstby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
(UR=A)
)
)
Step 12. Stop listener from GRID_HOME. Create new listener in ORACLE_HOME and start it - Standby Database
srvctl stop listener
srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is not running
[oracle@dbstby admin]$ vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbstby)(PORT = 1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME=stby)
(GLOBAL_DBNAME=stby)
)
)
export PATH=$PATH:$HOME/.local/bin:$HOME/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin
lsnrctl start
lsnrctl status
Step 13. Create new directories for new database data files and control files as - Standby Database
[oracle@standby oracle]$ mkdir -p /u01/app/oracle/admin/stby/adump
Step 14. Create parameter file initstbyad.ora - Standby Database
[oracle@duplicate dbs]$ vi initstby.ora
DB_NAME=avisdb
Step 15. Set parameters - Standby Database
[oracle@dbstby dbs]$ sqlplus / as sysdba
startup nomount;
create spfile from pfile;
startup nomount force;
alter system set control_files='+DATA','+FRA' scope=spfile;
alter system set db_unique_name=stby scope=spfile;
alter system set DB_FILE_NAME_CONVERT='+DATA/avisdb/datafile','+DATA/stby/datafile' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='+DATA','+DATA','+FRA','+FRA' scope=spfile;
alter system set log_archive_max_processes=10 scope=spfile;
alter system set fal_client='stby' scope=both;
alter system set fal_server='avisdb' scope=both;
alter system set standby_file_management='AUTO';
alter system set log_archive_config='DG_CONFIG=(avisdb,stby)' scope=both;
alter system set db_recovery_file_dest_size=2000G scope=spfile;
alter system set db_recovery_file_dest='+FRA' scope=spfile;
startup nomount force;
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stby' scope=both;
alter system set log_archive_dest_2='SERVICE=avisdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=avisdb' scope=both;
Change to RMAN
[oracle@duplicate stbyad]$ rman target=/
connected to target database: avisdb (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/stby.ctl'; (This is the controlfile created on Step 7 from CSD.txt)
SQL> alter system set compatible='11.2.0.4.0' scope=spfile;
(SQL> startup nomount force;
SQL> alter database mount; )
or
SQL> startup mount force;
SQL> startup nomount force;
Step 16. Duplication for standby - Primary Database
[oracle@rac1 ~]$ rman target=sys/xxxxxx@avisdb auxiliary=sys/xxxxxx@stby
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 2 09:10:09 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: AVISDB (DBID=676626585)
connected to auxiliary database: AVISDB (not mounted)
RMAN> spool log to '/home/oracle/output.log';
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 channel prmy5 type disk;
7> allocate auxiliary channel stby1 type disk;
8> duplicate target database for standby from active database
9> dorecover
10> nofilenamecheck;
11> }
Step 17. Update tnsnames.ora in each Node to apply logs.
Change node name to scan name. And add new service name to each RAC node for standby to apply logs.
Step 18. Update listener.ora in Node1 as remove
(SID_LIST =
(SID_DESC =
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = avisdb2)
)
)
Step 19. Issue the following command to start Redo Apply
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Step 20. Then drop tempfile metada where it defined in nodes other than duplicated (here Node2 tempfile).
alter database tempfile '+DATA/avisdb/tempfile/temp2' drop;
Step 21. Verify the Physical Standby Database Is Performing Properly
Query the V$MANAGED_STANDBY view to verify that redo is being transmitted from the primary database and applied to the standby database.
SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR RFS 1 33 IDLE
N/A MRP0 1 33 APPLYING_LOG
The query output should show one line for the primary database with a CLIENT_PROCESS of LGWR. This indicates that redo
transport is functioning correctly and the primary redo thread is being sent to the standby.
SQL> select thread#, sequence#, applied from v$archived_log;
THREAD# SEQUENCE# APPLIED
--------- ---------- ---------
1 54 YES
1 55 YES
1 56 IN-MEMORY
Friday, January 19, 2018
Creating a Physical Standby Database Using Active Duplication Method
OS: Oracle Linux 7.3
DB: 12.2.0
Step 1. Enable Forced Logging
As part of preparing the primary database for standby database creation, you place the primary database in FORCE LOGGING mode.
To check the database logging mode:
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------
NO
You can do this after database creation using the following SQL statement:
SQL> ALTER DATABASE FORCE LOGGING;
Note: When you issue this statement, the primary database must at least be mounted (and it can also be open).
Step 2. Configure the Primary Database to Receive Redo Data
It is a best practice to configure the primary database to receive redo if this is the first
time a standby database is added to the configuration.
The primary database can then quickly transition to the standby role and begin receiving redo data, if necessary.
To create a standby redo log, use the SQL ALTER DATABASE ADD STANDBY LOGFILE statement. For example:
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog1.rdo') SIZE 200M;
or
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
We added 4 standby logfile in our test environment.
Note: Each standby redo log file must be at least as large as the largest redo log file in the redo log of the redo source database. For administrative ease, Oracle recommends that
all redo log files in the redo log at the redo source database and the standby redo log
at a redo transport destination be of the same size.
The standby redo log must have at least one more redo log group than the redo log at
the redo source database, for each redo thread at the redo source database. At the redo
source database, query the V$LOG view to determine how many redo log groups are in
the redo log at the redo source database and query the V$THREAD view to determine
how many redo threads exist at the redo source database.
Step 3. Set initialization parameters on Primary Database
SQL> alter system set db_unique_name=orcl scope=spfile;
SQL> alter system set log_archive_config='DG_CONFIG=(orcl,stbyad)' scope=both;
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both;
SQL> alter system set log_archive_dest_2='SERVICE=stbyad LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbyad' scope=both;
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> alter system set FAL_SERVER=stbyad scope=both;
SQL> alter system set FAL_CLIENT=orcl scope=both;
SQL> alter system set DB_FILE_NAME_CONVERT='/stbyad/','/orcl/' scope=spfile;
SQL> alter system set LOG_FILE_NAME_CONVERT='/stbyad/','/orcl/' scope=spfile;
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
SQL> alter system set remote_login_passwordfile=exclusive; -->REMOTE LOGIN (default is 'exclusive')
SQL> startup force;
Step 4. Update tnsnames.ora file in Primary
[oracle@db admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
STBYAD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = duplicate)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stbyad)
(UR=A)
)
)
Step 5. Update listener.ora in Primary
[oracle@db admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=orcl)
(GLOBAL_DBNAME=orcl)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
lsnrctl reload;
Step 6. Install only database software on Standby host and create listener with netca if not created by default.
Step 7. Update listener.ora in Standby
[oracle@duplicate admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME=stbyad)
(GLOBAL_DBNAME=stbyad)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = duplicate)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
Step 8. Create tnsnames.ora in Standby
[oracle@duplicate admin]$ cat tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
STBYAD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = duplicate)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stbyad)
(UR=A)
)
)
Step 9. Add Standby Database parameters to system file for oracle which is used for ora environment.
[oracle@duplicate admin]$ vi /etc/oratab
stbyad:/u01/app/oracle/product/12.2.0/dbhome_1:N
Step 10. Copy the remote login password file from primary to standby
[oracle@db dbs]$ scp orapworcl oracle@duplicate:/u01/app/oracle/product/12.2.0/dbhome_1/dbs
oracle@duplicate's password:
orapworcl 100% 3584 3.5KB/s 00:00
[oracle@duplicate dbs]mv orapworcl orapwstbyad
Step 11. Create new directories for new database data files and control files as
[oracle@standby oracle]$ mkdir -p /u01/app/oracle/oradata/stbyad
[oracle@standby oracle]$ mkdir -p /u01/app/oracle/recovery_area/stbyad
[oracle@standby oracle]$ mkdir -p /u01/app/oracle/admin/stbyad/adump
Step 12. Create parameter file initstbyad.ora with content in Standby
[oracle@duplicate dbs]$ cat initstbyad.ora
DB_NAME=orcl
Step 13. On Standby
[oracle@duplicate ~]$ sqlplus sys/222333@stbyad as sysdba
SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initorcl.ora';
SQL> create spfile from pfile;
SQL> startup nomount force;
SQL> alter system set control_files='/u01/app/oracle/oradata/stbyad/control01.ctl','/u01/app/oracle/recovery_area/stbyad/control02.ctl' scope=spfile;
SQL> alter system set db_unique_name=stbyad scope=spfile;
SQL> alter system set DB_FILE_NAME_CONVERT='/orcl/','/stbyad/' scope=spfile;
SQL> alter system set log_file_name_convert='/orcl/','/stbyad/' scope=spfile;
SQL> alter system set log_archive_max_processes=10 scope=spfile;
SQL> alter system set fal_client='stbyad' scope=both;
SQL> alter system set fal_server='orcl' scope=both;
SQL> alter system set standby_file_management='AUTO';
SQL> alter system set log_archive_config='DG_CONFIG=(orcl,stbyad)' scope=both;
SQL> alter system set db_recovery_file_dest_size=8016M scope=spfile;
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/recovery_area' scope=spfile;
SQL> startup nomount force;
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbyad' scope=both;
SQL> alter system set log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' scope=both;
Change to RMAN
[oracle@duplicate stbyad]$ rman target=/
connected to target database: ORCL (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/stby.ctl'; (This is the controlfile created on Step 7 from CSD.txt)
SQL> startup nomount force;
Step 14. On Primary
[oracle@db ~]$ rman target=sys/222333@orcl auxiliary=sys/222333@stbyad
Recovery Manager: Release 12.2.0.1.0 - Production on Tue Dec 26 01:03:20 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1489109641)
connected to auxiliary database: ORCL (not mounted)
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;
7> duplicate target database for standby from active database
8> nofilenamecheck;
9> }
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=67 device type=DISK
allocated channel: prmy2
channel prmy2: SID=65 device type=DISK
allocated channel: prmy3
channel prmy3: SID=70 device type=DISK
allocated channel: prmy4
channel prmy4: SID=86 device type=DISK
allocated channel: stby
channel stby: SID=36 device type=DISK
Starting Duplicate Db at 26-DEC-17
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl' auxiliary format
'/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwstbyad' ;
}
executing Memory Script
Starting backup at 26-DEC-17
Finished backup at 26-DEC-17
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/cntrlstbyad.dbf';
}
executing Memory Script
Starting backup at 26-DEC-17
channel prmy1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20171226T010520
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 26-DEC-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
"/u01/app/oracle/oradata/stbyad/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/stbyad/system01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/stbyad/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/stbyad/undotbs01.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/stbyad/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/stbyad/system01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/stbyad/sysaux01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/stbyad/undotbs01.dbf" datafile
7 auxiliary format
"/u01/app/oracle/oradata/stbyad/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/stbyad/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 26-DEC-17
channel prmy1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
channel prmy2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
channel prmy3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
channel prmy4: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/users01.dbf
output file name=/u01/app/oracle/oradata/stbyad/users01.dbf tag=TAG20171226T010526
channel prmy4: datafile copy complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/stbyad/undotbs01.dbf tag=TAG20171226T010526
channel prmy3: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/stbyad/system01.dbf tag=TAG20171226T010526
channel prmy1: datafile copy complete, elapsed time: 00:00:25
output file name=/u01/app/oracle/oradata/stbyad/sysaux01.dbf tag=TAG20171226T010526
channel prmy2: datafile copy complete, elapsed time: 00:00:25
Finished backup at 26-DEC-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=5 STAMP=963709552 file name=/u01/app/oracle/oradata/stbyad/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=963709552 file name=/u01/app/oracle/oradata/stbyad/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=963709552 file name=/u01/app/oracle/oradata/stbyad/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=963709552 file name=/u01/app/oracle/oradata/stbyad/users01.dbf
Finished Duplicate Db at 26-DEC-17
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN>
Step 15. On Standby
SQL> Alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR RFS 1 64 IDLE
N/A MRP0 1 64 APPLYING_LOG
SQL> select thread#, sequence#, applied from v$archived_log;
THREAD# SEQUENCE# APPLIED
---------- ---------- ---------
1 62 YES
1 61 YES
1 63 IN-MEMORY
DB: 12.2.0
Step 1. Enable Forced Logging
As part of preparing the primary database for standby database creation, you place the primary database in FORCE LOGGING mode.
To check the database logging mode:
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------
NO
You can do this after database creation using the following SQL statement:
SQL> ALTER DATABASE FORCE LOGGING;
Note: When you issue this statement, the primary database must at least be mounted (and it can also be open).
Step 2. Configure the Primary Database to Receive Redo Data
It is a best practice to configure the primary database to receive redo if this is the first
time a standby database is added to the configuration.
The primary database can then quickly transition to the standby role and begin receiving redo data, if necessary.
To create a standby redo log, use the SQL ALTER DATABASE ADD STANDBY LOGFILE statement. For example:
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog1.rdo') SIZE 200M;
or
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
We added 4 standby logfile in our test environment.
Note: Each standby redo log file must be at least as large as the largest redo log file in the redo log of the redo source database. For administrative ease, Oracle recommends that
all redo log files in the redo log at the redo source database and the standby redo log
at a redo transport destination be of the same size.
The standby redo log must have at least one more redo log group than the redo log at
the redo source database, for each redo thread at the redo source database. At the redo
source database, query the V$LOG view to determine how many redo log groups are in
the redo log at the redo source database and query the V$THREAD view to determine
how many redo threads exist at the redo source database.
Step 3. Set initialization parameters on Primary Database
SQL> alter system set db_unique_name=orcl scope=spfile;
SQL> alter system set log_archive_config='DG_CONFIG=(orcl,stbyad)' scope=both;
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both;
SQL> alter system set log_archive_dest_2='SERVICE=stbyad LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbyad' scope=both;
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> alter system set FAL_SERVER=stbyad scope=both;
SQL> alter system set FAL_CLIENT=orcl scope=both;
SQL> alter system set DB_FILE_NAME_CONVERT='/stbyad/','/orcl/' scope=spfile;
SQL> alter system set LOG_FILE_NAME_CONVERT='/stbyad/','/orcl/' scope=spfile;
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
SQL> alter system set remote_login_passwordfile=exclusive; -->REMOTE LOGIN (default is 'exclusive')
SQL> startup force;
Step 4. Update tnsnames.ora file in Primary
[oracle@db admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
STBYAD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = duplicate)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stbyad)
(UR=A)
)
)
Step 5. Update listener.ora in Primary
[oracle@db admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=orcl)
(GLOBAL_DBNAME=orcl)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
lsnrctl reload;
Step 6. Install only database software on Standby host and create listener with netca if not created by default.
Step 7. Update listener.ora in Standby
[oracle@duplicate admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME=stbyad)
(GLOBAL_DBNAME=stbyad)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = duplicate)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
Step 8. Create tnsnames.ora in Standby
[oracle@duplicate admin]$ cat tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
STBYAD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = duplicate)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stbyad)
(UR=A)
)
)
Step 9. Add Standby Database parameters to system file for oracle which is used for ora environment.
[oracle@duplicate admin]$ vi /etc/oratab
stbyad:/u01/app/oracle/product/12.2.0/dbhome_1:N
Step 10. Copy the remote login password file from primary to standby
[oracle@db dbs]$ scp orapworcl oracle@duplicate:/u01/app/oracle/product/12.2.0/dbhome_1/dbs
oracle@duplicate's password:
orapworcl 100% 3584 3.5KB/s 00:00
[oracle@duplicate dbs]mv orapworcl orapwstbyad
Step 11. Create new directories for new database data files and control files as
[oracle@standby oracle]$ mkdir -p /u01/app/oracle/oradata/stbyad
[oracle@standby oracle]$ mkdir -p /u01/app/oracle/recovery_area/stbyad
[oracle@standby oracle]$ mkdir -p /u01/app/oracle/admin/stbyad/adump
Step 12. Create parameter file initstbyad.ora with content in Standby
[oracle@duplicate dbs]$ cat initstbyad.ora
DB_NAME=orcl
Step 13. On Standby
[oracle@duplicate ~]$ sqlplus sys/222333@stbyad as sysdba
SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initorcl.ora';
SQL> create spfile from pfile;
SQL> startup nomount force;
SQL> alter system set control_files='/u01/app/oracle/oradata/stbyad/control01.ctl','/u01/app/oracle/recovery_area/stbyad/control02.ctl' scope=spfile;
SQL> alter system set db_unique_name=stbyad scope=spfile;
SQL> alter system set DB_FILE_NAME_CONVERT='/orcl/','/stbyad/' scope=spfile;
SQL> alter system set log_file_name_convert='/orcl/','/stbyad/' scope=spfile;
SQL> alter system set log_archive_max_processes=10 scope=spfile;
SQL> alter system set fal_client='stbyad' scope=both;
SQL> alter system set fal_server='orcl' scope=both;
SQL> alter system set standby_file_management='AUTO';
SQL> alter system set log_archive_config='DG_CONFIG=(orcl,stbyad)' scope=both;
SQL> alter system set db_recovery_file_dest_size=8016M scope=spfile;
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/recovery_area' scope=spfile;
SQL> startup nomount force;
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbyad' scope=both;
SQL> alter system set log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' scope=both;
Change to RMAN
[oracle@duplicate stbyad]$ rman target=/
connected to target database: ORCL (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/stby.ctl'; (This is the controlfile created on Step 7 from CSD.txt)
SQL> startup nomount force;
Step 14. On Primary
[oracle@db ~]$ rman target=sys/222333@orcl auxiliary=sys/222333@stbyad
Recovery Manager: Release 12.2.0.1.0 - Production on Tue Dec 26 01:03:20 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1489109641)
connected to auxiliary database: ORCL (not mounted)
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;
7> duplicate target database for standby from active database
8> nofilenamecheck;
9> }
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=67 device type=DISK
allocated channel: prmy2
channel prmy2: SID=65 device type=DISK
allocated channel: prmy3
channel prmy3: SID=70 device type=DISK
allocated channel: prmy4
channel prmy4: SID=86 device type=DISK
allocated channel: stby
channel stby: SID=36 device type=DISK
Starting Duplicate Db at 26-DEC-17
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl' auxiliary format
'/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwstbyad' ;
}
executing Memory Script
Starting backup at 26-DEC-17
Finished backup at 26-DEC-17
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/cntrlstbyad.dbf';
}
executing Memory Script
Starting backup at 26-DEC-17
channel prmy1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20171226T010520
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 26-DEC-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
"/u01/app/oracle/oradata/stbyad/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/stbyad/system01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/stbyad/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/stbyad/undotbs01.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/stbyad/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/stbyad/system01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/stbyad/sysaux01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/stbyad/undotbs01.dbf" datafile
7 auxiliary format
"/u01/app/oracle/oradata/stbyad/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/stbyad/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 26-DEC-17
channel prmy1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
channel prmy2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
channel prmy3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
channel prmy4: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/users01.dbf
output file name=/u01/app/oracle/oradata/stbyad/users01.dbf tag=TAG20171226T010526
channel prmy4: datafile copy complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/stbyad/undotbs01.dbf tag=TAG20171226T010526
channel prmy3: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/stbyad/system01.dbf tag=TAG20171226T010526
channel prmy1: datafile copy complete, elapsed time: 00:00:25
output file name=/u01/app/oracle/oradata/stbyad/sysaux01.dbf tag=TAG20171226T010526
channel prmy2: datafile copy complete, elapsed time: 00:00:25
Finished backup at 26-DEC-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=5 STAMP=963709552 file name=/u01/app/oracle/oradata/stbyad/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=963709552 file name=/u01/app/oracle/oradata/stbyad/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=963709552 file name=/u01/app/oracle/oradata/stbyad/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=963709552 file name=/u01/app/oracle/oradata/stbyad/users01.dbf
Finished Duplicate Db at 26-DEC-17
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN>
Step 15. On Standby
SQL> Alter database recover managed standby database using current logfile disconnect;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';
CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR RFS 1 64 IDLE
N/A MRP0 1 64 APPLYING_LOG
SQL> select thread#, sequence#, applied from v$archived_log;
THREAD# SEQUENCE# APPLIED
---------- ---------- ---------
1 62 YES
1 61 YES
1 63 IN-MEMORY
Subscribe to:
Posts (Atom)