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
No comments:
Post a Comment