Description:-
In this is the article
we are going to see step-by-step to create a physical standby database
using RMAN in Oracle Database 19c.
Parameters needs to configure both side for Dataguard,
PRIMARY :
[oracle@trichy
~]$ . oraenv
ORACLE_SID
= [cdb1] ? prod
The
Oracle base remains unchanged with value /u01/app/oracle
[oracle@trichy
~]$ sqlplus / as sysdba
SQL*Plus:
Release 19.0.0.0.0 - Production on Sat Jun 15 09:14:12 2019
Version
19.3.0.0.0
Copyright
(c) 1982, 2019, Oracle. All rights reserved.
Connected
to an idle instance.
SQL>
startup
ORACLE
instance started.
Total
System Global Area 2415917880 bytes
Fixed
Size 8899384
bytes
Variable
Size 520093696 bytes
Database
Buffers 1879048192 bytes
Redo
Buffers 7876608
bytes
Database
mounted.
Database
opened.
Make sure primary is in archivelog mode
SQL>
archive log list
Database
log mode Archive Mode
Automatic
archival
Enabled
Archive
destination USE_DB_RECOVERY_FILE_DEST
Oldest
online log sequence 4
Next
log sequence to archive 6
Current
log sequence
6
Primary side:
Check FORCE LOGGING is enabled,
SQL>
select force_logging from v$database;
FORCE_LOGGING
----------------
NO
SQL>
alter database force logging;
Database
altered.
SQL>
select force_logging from v$database;
FORCE_LOGGING
----------------
YES
SQL>
alter system set log_archive_config='dg_config=(PROD,STANDBY)';
System
altered.
SQL>
alter system set log_archive_dest_1='location=/u01/archive
valid_for=(all_logfiles,primary_role)';
System
altered.
SQL>
alter system set log_archive_dest_2='service=STANDBY lgwr async noaffirm
valid_for=(all_logfiles,primary_role) db_unique_name=STANDBY';
System
altered.
SQL>
alter system set log_archive_dest_state_2=enable;
System
altered.
SQL>
alter system set log_archive_dest_state_1=enable;
System
altered.
SQL>
alter system set remote_login_passwordfile=exclusive scope=spfile;
System
altered.
SQL>
alter system set standby_file_management=auto;
System
altered.
SQL>
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
System
altered.
SQL>
alter system set log_archive_max_processes=10;
System
altered.
SQL>
alter system set db_unique_name=PROD scope=spfile;
System
altered.
SQL>
alter system set fal_client=PROD;
System
altered.
SQL>
alter system set fal_server=STANDBY;
System
altered.
SQL>
shut immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
startup
SQL>
create pfile='/home/oracle/initprod.ora' from spfile;
File
created.
*.db_unique_name='STANDBY'
*.fal_client='STANDBY'
*.fal_server='PROD'
*.log_archive_config='dg_config=(PROD,STANDBY)'
*.log_archive_dest_1='location=/u01/archive
valid_for=(all_logfiles,primary_role)'
*.log_archive_dest_2='service=PROD
lgwr async noaffirm valid_for=(all_logfiles,primary_role) db_unique_name=PROD'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=10
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
[oracle@trichy
~]$ scp -r initprod.ora oracle@192.168.1.14:/u01/app/oracle/product/19.0.0/dbhome_1/dbs
The
authenticity of host '192.168.1.14 (192.168.1.14)' can't be established.
ECDSA
key fingerprint is SHA256:jU17jN8XF6AHRAi6HsDXnoQdDYXOlScWvmZWxXK+kw8.
ECDSA
key fingerprint is MD5:e9:ac:84:59:86:d4:47:ab:e5:ac:89:23:b1:1c:1e:8c.
Are
you sure you want to continue connecting (yes/no)? yes
Warning:
Permanently added '192.168.1.14' (ECDSA) to the list of known hosts.
oracle@192.168.1.14's
password:
initprod.ora
100%
1566 1.1MB/s 00:00
[oracle@trichy
~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/dbs
[oracle@trichy
dbs]$ ls
hc_prod.dat
init.ora lkPROD orapwprod spfileprod.ora
Copy password file from primary to standby server,
[oracle@trichy
dbs]$ scp -r orapwprod
oracle@192.168.1.14:/u01/app/oracle/product/19.0.0/dbhome_1/dbs
oracle@192.168.1.14's
password:
orapwprod
Listener and TNS Configuration :-
LISTENER_CONFIG
SID_LIST_LISTENER
=
(SID_LIST
=
(SID_DESC
=
(GLOBAL_DBNAME
= PROD)
(ORACLE_HOME
=/u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME
= prod)
)
)
LISTENER
=
(DESCRIPTION_LIST
=
(DESCRIPTION
=
(ADDRESS
= (PROTOCOL = TCP)(HOST =192.168.1.15 )(PORT = 1521))
(ADDRESS
= (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
TNS_CONFIG
PROD
=
(DESCRIPTION
=
(ADDRESS_LIST
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = 192.168.1.15)(PORT = 1521))
)
(CONNECT_DATA
=
(SERVER
= DEDICATED)
(SERVICE_NAME
= prod)
)
)
STANDBY
=
(DESCRIPTION
=
(ADDRESS_LIST
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
)
(CONNECT_DATA
=
(SERVER
= DEDICATED)
(SERVICE_NAME
= prod)
)
)
Make the respective directories for standby database,
STANDBY :
[oracle@trichy
~]$ mkdir -p /u01/app/oracle/admin/prod/adump
[oracle@trichy
~]$ mkdir -p /u01/app/oracle/oradata/PROD/
[oracle@trichy
~]$ mkdir -p /u01/app/oracle/fast_recovery_area/PROD/
Edit the parameters in pfile for standby
*.db_unique_name='STANDBY'
*.fal_client='STANDBY'
*.fal_server='PROD'
*.log_archive_config='dg_config=(PROD,STANDBY)'
*.log_archive_dest_1='location=/u01/archive
valid_for=(all_logfiles,primary_role)'
*.log_archive_dest_2='service=PROD
lgwr async noaffirm valid_for=(all_logfiles,primary_role) db_unique_name=PROD'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=10
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
Listener and TNS Configuration :-
STANDBY_LISTENER_CONFIG
SID_LIST_LISTENER
=
(SID_LIST
=
(SID_DESC
=
(GLOBAL_DBNAME
= PROD)
(ORACLE_HOME
=/u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME
= prod)
)
)
LISTENER
=
(DESCRIPTION_LIST
=
(DESCRIPTION
=
(ADDRESS
= (PROTOCOL = TCP)(HOST =192.168.1.14 )(PORT = 1521))
(ADDRESS
= (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
STANDBY_TNS_CONFIG
PROD
=
DESCRIPTION
=
(ADDRESS_LIST
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = 192.168.1.15)(PORT = 1521))
)
(CONNECT_DATA
=
(SERVER
= DEDICATED)
(SERVICE_NAME
= prod)
)
)
STANDBY
=
(DESCRIPTION
=
(ADDRESS_LIST
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = 192.168.1.14)(PORT = 1521))
)
(CONNECT_DATA
=
(SERVER
= DEDICATED)
(SERVICE_NAME
= prod)
)
)
Keep the database in NOMOUNT stage to create standby
database,
[oracle@trichy
~]$ . oraenv
ORACLE_SID
= [cdb1] ? prod
The
Oracle base remains unchanged with value /u01/app/oracle
[oracle@trichy
~]$ sqlplus / as sysdba
SQL*Plus:
Release 19.0.0.0.0 - Production on Sat Jun 15 09:14:12 2019
Version
19.3.0.0.0
Copyright
(c) 1982, 2019, Oracle. All rights reserved.
Connected
to an idle instance.
SQL>
startup nomount
ORACLE
instance started.
Total
System Global Area 2415917880 bytes
Fixed
Size 8899384 bytes
Variable
Size 520093696 bytes
Database
Buffers 1879048192 bytes
Redo
Buffers 7876608 bytes
SQL>
In
standby side,check RMAN connection.Connect primary database as TARGET and
standby database as AUXILIARY(creating new instance)
[oracle@trichy
dbs]$ rman target sys/oracle@PROD
Recovery
Manager: Release 19.0.0.0.0 - Production on Sat Jun 15 10:42:32 2019
Version
19.3.0.0.0
Copyright
(c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected
to target database: PROD (DBID=422602599)
RMAN> connect auxiliary sys/oracle@STANDBY
connected
to auxiliary database: PROD (not mounted)
RMAN>
duplicate target database for standby from active database
dorecover nofilenamecheck;
Starting
Duplicate Db at 15-JUN-19
using
target database control file instead of recovery catalog
allocated
channel: ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=36 device type=DISK
current
log archived
contents
of Memory Script:
{
backup
as copy reuse
passwordfile
auxiliary format '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwprod'
;
}
executing
Memory Script
Starting
backup at 15-JUN-19
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=68 device type=DISK
Finished
backup at 15-JUN-19
duplicating
Online logs to Oracle Managed File (OMF) location
contents
of Memory Script:
{
restore
clone from service 'PROD' standby controlfile;
}
executing
Memory Script
Starting
restore at 15-JUN-19
using
channel ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel
ORA_AUX_DISK_1: using network backup set from service PROD
channel
ORA_AUX_DISK_1: restoring control file
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
output
file name=/u01/app/oracle/oradata/PROD/control01.ctl
output
file name=/u01/app/oracle/fast_recovery_area/PROD/control02.ctl
Finished
restore at 15-JUN-19
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/PROD/temp01.dbf";
switch
clone tempfile all;
set
newname for datafile 1 to
"/u01/app/oracle/oradata/PROD/system01.dbf";
set
newname for datafile 3 to
"/u01/app/oracle/oradata/PROD/sysaux01.dbf";
set
newname for datafile 4 to
"/u01/app/oracle/oradata/PROD/undotbs01.dbf";
set
newname for datafile 7 to
"/u01/app/oracle/oradata/PROD/users01.dbf";
restore
from
nonsparse from service
'PROD'
clone database
;
sql
'alter system archive log current';
}
executing
Memory Script
executing
command: SET NEWNAME
renamed
tempfile 1 to /u01/app/oracle/oradata/PROD/temp01.dbf in control file
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
Starting
restore at 15-JUN-19
using
channel ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel
ORA_AUX_DISK_1: using network backup set from service PROD
channel
ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel
ORA_AUX_DISK_1: restoring datafile 00001 to
/u01/app/oracle/oradata/PROD/system01.dbf
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:08:04
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel
ORA_AUX_DISK_1: using network backup set from service PROD
channel
ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel
ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD/sysaux01.dbf
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:11:23
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel
ORA_AUX_DISK_1: using network backup set from service PROD
channel
ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel
ORA_AUX_DISK_1: restoring datafile 00004 to
/u01/app/oracle/oradata/PROD/undotbs01.dbf
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:59
channel
ORA_AUX_DISK_1: starting datafile backup set restore
channel
ORA_AUX_DISK_1: using network backup set from service PROD
channel
ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel
ORA_AUX_DISK_1: restoring datafile 00007 to
/u01/app/oracle/oradata/PROD/users01.dbf
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished
restore at 15-JUN-19
sql
statement: alter system archive log current
current
log archived
contents
of Memory Script:
{
restore
clone force from service 'PROD'
archivelog
from scn 2187262;
switch
clone datafile all;
}
executing
Memory Script
Starting
restore at 15-JUN-19
using
channel ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: starting archived log restore to default destination
channel
ORA_AUX_DISK_1: using network backup set from service PROD
channel
ORA_AUX_DISK_1: restoring archived log
archived
log thread=1 sequence=9
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
channel
ORA_AUX_DISK_1: starting archived log restore to default destination
channel
ORA_AUX_DISK_1: using network backup set from service PROD
channel
ORA_AUX_DISK_1: restoring archived log
archived
log thread=1 sequence=10
channel
ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished
restore at 15-JUN-19
datafile
1 switched to datafile copy
input
datafile copy RECID=1 STAMP=1011006513 file
name=/u01/app/oracle/oradata/PROD/system01.dbf
datafile
3 switched to datafile copy
input
datafile copy RECID=2 STAMP=1011006513 file
name=/u01/app/oracle/oradata/PROD/sysaux01.dbf
datafile
4 switched to datafile copy
input
datafile copy RECID=3 STAMP=1011006513 file
name=/u01/app/oracle/oradata/PROD/undotbs01.dbf
datafile
7 switched to datafile copy
input
datafile copy RECID=4 STAMP=1011006513 file
name=/u01/app/oracle/oradata/PROD/users01.dbf
contents
of Memory Script:
{
set
until scn 2190007;
recover
standby
clone
database
delete
archivelog
;
}
executing
Memory Script
executing
command: SET until clause
Starting
recover at 15-JUN-19
using
channel ORA_AUX_DISK_1
starting
media recovery
archived
log for thread 1 with sequence 9 is already on disk as file
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_9_1010764267.arc
archived
log for thread 1 with sequence 10 is already on disk as file
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1010764267.arc
archived
log file
name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_9_1010764267.arc
thread=1 sequence=9
archived
log file
name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1010764267.arc
thread=1 sequence=10
media
recovery complete, elapsed time: 00:00:04
Finished
recover at 15-JUN-19
contents
of Memory Script:
{
delete
clone force archivelog all;
}
executing
Memory Script
released
channel: ORA_DISK_1
released
channel: ORA_AUX_DISK_1
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=68 device type=DISK
deleted
archived log
archived
log file
name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_9_1010764267.arc RECID=1
STAMP=1011006509
deleted
archived log
archived
log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1010764267.arc
RECID=2 STAMP=1011006510
Deleted
2 objects
Finished
Duplicate Db at 15-JUN-19.
Post check status for Standby
database,
1. SQL>
select name,database_role,open_mode
from
v$database;
2.
3. NAME
DATABASE_ROLE OPEN_MODE
4. ———
————-------- ——————–
5. PROD
PHYSICAL STANDBY MOUNTED
6.
7.
8. Enable
the recovery:
9.
10. SQL>
alter database
recover managed standby database disconnect from session;
11.
12. Database
altered.
13.
14.
15. Check
the Standby database sync status with primary:
16.
17. SELECT
ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence
Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#)
“Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT
THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#))
ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT
THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY
THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
18.
19. Thread
Last Sequence
Received Last Sequence Applied Difference
20. ————
———————————— ———–------------------- ---------------
21. 1 10 10 0
Thanks
Srini
No comments:
Post a Comment
No one has ever become poor by giving