configuring standby database using rman duplicate command without backup
Servers details:
|
Environments
|
hostname
|
Ip address
|
instance
|
|
Primary server
|
Prim-host
|
primdb
|
|
|
Standby Server
|
Stdby-host
|
stdbydb
|
|
-
Install 18c grid standalone software in the DR
server
-
Install the RDBMS home similar version as prod
12.2.0
Prepare primary
server:
1.
Check database is in archivelog mode or not.
SQL>
archive log list
Database
log mode Archive Mode
Automatic
archival Enabled
Archive
destination
USE_DB_RECOVERY_FILE_DEST
Oldest
online log sequence 123816
Next
log sequence to archive 123818
Current
log sequence 123818
2.
Check the force logging is enabled or not in
primary database.
SQL>
select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
Enable force logging using below command.
SQL>
ALTER DATABASE FORCE LOGGING;
Database
altered.
SQL>
select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
3.
Create password file in primary database.
orapwd
file=orapwPRIMDB password=Ora935#kfd
4.
Create standby redologs in primary database to
enable real time apply.
set
lines 180
col
MEMBER for a60
select
b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group#
= b.group#;
THREAD# GROUP# MEMBER
B.BYTES/1024/1024
---------- ----------
------------------------------------------------------------ -----------------
1 1 +DATA/PRIMDB/ONLINELOG/group_1.259.1028024215 200
1 1 +DATA/PRIMDB/ONLINELOG/group_1.260.1028024217 200
1 2 +DATA/PRIMDB/ONLINELOG/group_2.261.1028024219 200
1 2 +DATA/PRIMDB/ONLINELOG/group_2.262.1028024221 200
1 3 +DATA/PRIMDB/ONLINELOG/group_3.263.1028024223 200
1 3 +DATA/PRIMDB/ONLINELOG/group_3.264.1028024223 200
ALTER
DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 200M;
ALTER
DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 200M;
ALTER
DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 200M;
ALTER
DATABASE ADD STANDBY LOGFILE GROUP 7 SIZE 200M;
SQL>
select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group#
= b.group#;
GROUP# MEMBER
BYTES
----------
------------------------------------------------------------ ----------
4 +DATA/PRIMDB/ONLINELOG/group_4.801.1041440071 209715200
4 +DATA/PRIMDB/ONLINELOG/group_4.478.1041440071 209715200
6 +DATA/PRIMDB/ONLINELOG/group_6.545.1041439901 209715200
6 +DATA/PRIMDB/ONLINELOG/group_6.368.1041439901 209715200
5 +DATA/PRIMDB/ONLINELOG/group_5.311.1041440081 209715200
5 +DATA/PRIMDB/ONLINELOG/group_5.567.1041440081 209715200
7 +DATA/PRIMDB/ONLINELOG/group_7.474.1041440103 209715200
7 +DATA/PRIMDB/ONLINELOG/group_7.771.1041440105 209715200
8 rows selected.
5.
Set parameter in primary database.
SQL>
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMDB,STDBYDB)' scope=both;
System
altered.
SQL>
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMDB' scope=both;
System
altered.
SQL>
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STDBYDB LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBYDB' scope=both;
System
altered.
SQL>
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
System
altered.
SQL>
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
System
altered.
SQL>
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System
altered.
SQL>
ALTER SYSTEM SET fal_server=STDBYDB scope=both;
System
altered.
6.
Take pfile as backup
SQL>
create pfile='/tmp/primpfile.ora' from spfile;
File
created.
7.
Copy pfile and password file to standby server
$ORACLE_HOME/dbs location.
Standby server
configuration:
1.
Rename the pfile as initSTDBYDB and Modify as
below.
*.audit_file_dest='/opt/oracle/oracle_base/admin/STDBYDB/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_file_record_keep_time=38
*.control_files='+DATA/STDBYDB/CONTROLFILE/current.257.1041446223','+DATA/STDBYDB/CONTROLFILE/current.258.1041446227'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='PRD2020D'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=107374182400
*.db_unique_name='STDBYDB'
*.diagnostic_dest='/opt/oracle/oracle_base'
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=STDBYDBXDB)'
*.fal_server='PRIMDB'
*.local_listener=''
*.log_archive_config='DG_CONFIG=(PRIMDB,STDBYDB)'
*.log_archive_dest_1='LOCATION=+DATA
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STDBYDB'
*.log_archive_dest_2='SERVICE=PRIMDB LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMDB'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=15G
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1400
*.sga_max_size=50G
*.sga_target=50G
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
2.
rename the password file as orapwSTDBYDB
3.
add entry in /etc/oratab file.
vi /etc/oratab
STDBYDB:/opt/oracle/product/12.2.0.1/dbhome_1:N
4.
start the instance in nomount
$. oraenv
STDBYDB
$sqlplus / as sysdba
startup nomount
configuring
network:
1.
add tns entry in both primary and standby (in
RDBMS home)
tnsnames.ora in primary
PRIMDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prim-host)(PORT
= 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PRIMDB)
)
)
STDBYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby-host
)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STDBYDB)
(UR=A)
)
)
tnsnames.ora in standby (RDBMS home)
PRIMDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prim-host)(PORT
= 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PRIMDB)
)
)
STDBYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby-host
)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STDBYDB)
(UR=A)
)
)
2.
configure listener in standby server.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby-host)(PORT
= 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY =
EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STDBYDB_DGMGRL)
(ORACLE_HOME = /opt/oracle/product/18.0.0/grid)
(SID_NAME = STDBYDB)
)
)
3.
Tnsping check.
oracle@Stdby-host:~[DB->STDBYDB]$tnsping
STDBYDB
TNS Ping Utility for Linux:
Version 12.2.0.1.0 - Production on 27-MAY-2020 10:39:52
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve
the alias
Attempting to contact
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stdby-host)(PORT
= 1521))) (CONNECT_DATA = (SERVICE_NAME = STDBYDB) (UR=A)))
OK (10 msec)
oracle@Stdby-host:~[DB->STDBYDB]$tnsping
PRIMDB
TNS Ping Utility for Linux:
Version 12.2.0.1.0 - Production on 27-MAY-2020 10:39:57
Copyright (c) 1997, 2016,
Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve
the alias
Attempting to contact
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = prim-host)(PORT
= 1521))) (CONNECT_DATA = (SERVICE_NAME = PRIMDB)))
OK (30 msec)
4.
Password file authentication check from standby
server
oracle@Stdby-host:~[DB->STDBYDB]$sqlplus
sys/Ora935#kfd@STDBYDB as sysdba
SQL*Plus: Release 12.2.0.1.0
Production on Wed May 27 10:47:20 2020
Copyright (c) 1982, 2016,
Oracle. All rights reserved.
Last Successful login time: Wed
May 27 2020 08:32:16 +00:00
Connected to:
Oracle Database 12c Enterprise
Edition Release 12.2.0.1.0 - 64bit Production
SQL> select host_name from
v$instance;
HOST_NAME
----------------------------------------------------------------
Stdby-host
oracle@Stdby-host:~[DB->STDBYDB]$sqlplus
sys/Ora935#kfd@PRIMDB as sysdba
SQL*Plus: Release 12.2.0.1.0
Production on Wed May 27 10:49:11 2020
Copyright (c) 1982, 2016,
Oracle. All rights reserved.
Last Successful login time: Wed
May 27 2020 10:48:48 +00:00
Connected to:
Oracle Database 12c Enterprise
Edition Release 12.2.0.1.0 - 64bit Production
SQL> select host_name from
v$instance;
HOST_NAME
----------------------------------------------------------------
Prim-host
5.
Password file authentication check from primary
server.
oracle@Prim-host:~[DB->PRIMDB]$sqlplus
sys/Ora935#kfd@PRIMDB as sysdba
SQL*Plus: Release 12.2.0.1.0
Production on Wed May 27 12:50:33 2020
Copyright (c) 1982, 2016,
Oracle. All rights reserved.
Last Successful login time: Wed
May 27 2020 12:50:26 +02:00
Connected to:
Oracle Database 12c Enterprise
Edition Release 12.2.0.1.0 - 64bit Production
SQL> select host_name from
v$instance;
HOST_NAME
----------------------------------------------------------------
Prim-host
SQL> exit
Disconnected from Oracle Database
12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
oracle@Prim-host:~[DB->PRIMDB]$sqlplus
sys/AppleT0p#^1@STDBYDB as sysdba
SQL*Plus: Release 12.2.0.1.0
Production on Wed May 27 12:51:07 2020
Copyright (c) 1982, 2016,
Oracle. All rights reserved.
Last Successful login time: Wed
May 27 2020 12:47:21 +02:00
Connected to:
Oracle Database 12c Enterprise
Edition Release 12.2.0.1.0 - 64bit Production
SQL> select host_name from
v$instance;
HOST_NAME
----------------------------------------------------------------
Stdby-host
Database restore
for standby:
1.
Prepare script restore_stdby_db.sh
$cat restore_stdby_db.sh
rman target sys/Ora935#kfd@PRIMDB
AUXILIARY sys/AppleT0p#^1@STDBYDB log=
RMAN_restore.log << EOF
run
{
DUPLICATE TARGET DATABASE FOR
STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;
}
EOF
If file location is different in standby use file convert as below in
command.
DUPLICATE TARGET DATABASE FOR
STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE
SET db_unique_name='stdbydb'
SET db_file_name_convert='/u01/primdb/','/u01/stdbydb'
SET
log_file_name_convert='/u01/primdb/','/u01/stdbydb'
SET job_queue_processes='0'
NOFILENAMECHECK;
2.
Put database in recovery mode.
SQL>
SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM
V$DATABASE;
NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE
---------
-------------------- ------------------------------ ----------------
--------------------
PRD2020D MOUNTED STDBYDB PHYSICAL STANDBY MAXIMUM
PERFORMANCE
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
3.
Add DR database to HA(standalone grid
infrastructure).
$.
oraenv
+ASM
$srvctl
add database -d STDBYDB -o /opt/oracle/product/12.2.0.1/dbhome_1 -p +DATA/STDBYDB/PARAMETERFILE/spfile.644.1041511849
4.
Modify startup option and database role.
Export grid home/ASM.
$.
oraenv
+ASM
srvctl
modify database -d STDBYDB -role PHYSICAL_STANDBY -startoption MOUNT
