Steps to Create Physical Standby Database in Oracle
Introduction
Here Steps to Create Physical Standby Database in Oracle will build a physical standby setup. Our assumption is primary database is already up and running fine And ORACLE_HOME is installed on standby server.
SERVER ----->PRIMARY---->primary_host
DB_UNIQUE_NAME---->PRIMARY---->PRODDB
SERVER ----->STANDBY---->standby_host
DB_UNIQUE_NAME---->STANDBY---->PRODSTAN
PRIMARY
Make sure database is archive log mode and enable force logging
PRIMARY > select log_mode from v$database;
LOG_MODE
----------------
ARCHIVELOG
Enable force loggin:
FORCE LOGGING is required, every changes in database will go to redo logs, which will be applied on standby system
SQL > ALTER DATABASE FORCE LOGGING;
Database altered.
[PRIMARY] Add standby logfiles;
In standby setup, archives log from primary shipped to standby and applied there. But if the primary database crashes, then the online redo logs will stay in primary side. As these logs hasn’t been archives, means those won’t be applied to standby, Which results in data loss. So if we add standby logfile, Data guard writes the Primary’s current redo log to a “standby redo log” allowing complete recovery in case of Primary site is lost.
SQL > select GROUP#,BYTES/1024/1024,thread# from v$log;
GROUP# BYTES/1024/1024 THREAD#
---------- --------------- ------------------------------
1 138 1
2 138 1
3 138 1
4 138 1
5 138 1
6 138 1
7 138 1
8 138 1
8 rows selected.
SQL >col member for a45
SQL > set pagesize 200
SQL > set lines 200
SQL >select GROUP#,MEMBER from v$logfile;
GROUP# MEMBER
---------- ---------------------------------------------
1 /u01/redo1/PROD/redo_01a.dbf
1 /u01/redo2/PROD/redo_01b.dbf
2 /u01/redo1/PROD/redo_02a.dbf
2 /u01/redo2/PROD/redo_02b.dbf
3 /u01/redo1/PROD/redo_03a.dbf
3 /u01/redo2/PROD/redo_03a.dbf
4 /u01/redo1/PROD/redo_04a.dbf
4 /u01/redo2/PROD/redo_04a.dbf
5 /u01/redo1/PROD/redo_05a.dbf
5 /u01/redo2/PROD/redo_05a.dbf
6 /u01/redo1/PROD/redo_06a.dbf
6 /u01/redo2/PROD/redo_06a.dbf
7 /u01/redo1/PROD/redo_07a.dbf
7 /u01/redo2/PROD/redo_07b.dbf
8 /u01/redo1/PROD/redo_08a.dbf
8 /u01/redo2/PROD/redo_08b.dbf
Dynamics query to generate the sqls.
SQL > select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'prodstan_'||
regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||
''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
/ 2 3 4 5 6 7
Create Standby redo
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_01a.dbf' size 124217729;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_01b.dbf' size 124217729;
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_02a.dbf' size 124217729;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_02b.dbf' size 124217729;
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_03a.dbf' size 124217729;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_03a.dbf' size 124217729;
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_04a.dbf' size 124217729;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_04a.dbf' size 124217729;
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_05a.dbf' size 124217729;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_05a.dbf' size 124217729;
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_06a.dbf' size 124217729;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_06a.dbf' size 124217729;
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_07a.dbf' size 124217729;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_07b.dbf' size 124217729;
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_08a.dbf' size 124217729;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_08b.dbf' size 124217729;
16 rows selected.
SQL> alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_01a.dbf' size 124217729;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_01b.dbf' size 124217729;
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_02a.dbf' size 124217729;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_02b.dbf' size 124217729;
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_03a.dbf' size 124217729;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_03a.dbf' size 124217729;
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_04a.dbf' size 124217729;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_04a.dbf' size 124217729;
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_05a.dbf' size 124217729;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_05a.dbf' size 124217729;
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_06a.dbf' size 124217729;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_06a.dbf' size 124217729;
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_07a.dbf' size 134217728;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_07b.dbf' size 134217728;
alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_08a.dbf' size 134217728;
alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_08b.dbf' size 134217728;
database altered
[PRIMARY] create password file
cd $ORACLE_HOME/dbs
orapwd file=orapwPRODDB password=prod123 entries=30
[PRIMARY]Configure listener and tns entries as below.
cat tnsnames.ora
PRODDB =
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = primary-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODDB)))
PRODSTAN =
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = standby-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODSTAN)))
cat listener.ora
LISTENER_PRODDB =
(address_list =
(address =
(protocol = tcp)
(host = primary-host)
(port = 1571)
)
)
SID_LIST_LISTENER_PRODDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRODDB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0)
(SID_NAME = PRODDB)
)
)
lsnrctl start LISTENER_PRODDB
[PRIMARY]Do below changes in spfile of primary
log_archive_config parameter enables sending and receiving of archive logs to remote dest
DG_CONFIG - need to specify the list of db_unique_name of both primary and standby.
alter system set log_archive_config='DG_CONFIG=(PRODDB,PRODSTAN)' scope=BOTH;
--- archive destination in local(primary server)
alter system set log_archive_dest_1='LOCATION=/u01/arch/PROD VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDB' scope=BOTH;
---log_archive_dest_2 parameter defines the archive destination of standby
--- ASYNC - This is used for maximum performance mode(DEFAULT) .Means redo logs generated in primary need not be shipped/applied in standby
alter system set log_archive_dest_2='SERVICE=PRODSTAN LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSTAN' scope=BOTH;
alter system set log_archive_dest_state_2='ENABLE' scope=BOTH;
alter system set log_archive_dest_state_1='ENABLE' scope=BOTH;
Tns entry name for primary
alter system set fal_client='PRODDB' scope=BOTH;
--- tns entry name for primary -- i.e It will fetch the archives from standby .
alter system set fal_server='PRODSTAN' scope=BOTH;
[PRIMARY]So after the changes the spfile in primary will look as below.
strings spfilePRODDB.ora
*.audit_trail='DB'
*.compatible='11.2.0'
*.control_files='/u01/data1/PROD/control1.ora','/u01/data2/PROD/control2.ora','/u01/undo/PROD/control3.ora'
*.core_dump_dest='/u01/admin/PROD/cdump'
*.db_block_size=8193
*.db_cache_size=3009M
*.db_file_multiblock_read_count=63
*.db_keep_cache_size=512M
*.db_name='PRODDB'
*.db_unique_name='PRODDB'
*.diagnostic_dest='/u01/admin/PROD/diag'
*.dml_locks=100
*.fal_client='PRODDB'
*.fal_server='PRODSTAN'
*.instance_name='PRODDB'
*.job_queue_processes=2
*.log_archive_config='DG_CONFIG=(PRODDB,PRODSTAN)'
*.log_archive_dest_1='LOCATION=/u01/arch/PROD VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDB'
*.log_archive_dest_2='SERVICE=PRODSTAN LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSTAN'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='arch_PROD_%t_%s_%r.arc'
*.log_buffer=31367168# log buffer update
*.log_checkpoint_interval=300000
*.max_dump_file_size='unlimited'
*.nls_date_format='DD-MON-RR'
*.os_authent_prefix='NULL'
*.pga_aggregate_target=1500M
*.processes=600
*.remote_dependencies_mode='SIGNATURE'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.sga_max_size=5152M# internally adjusted
*.shared_pool_size=512M
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=30000
*.undo_tablespace='UNDO'
STANDBY
[STANDBY ] create directory structure same as that point
mkdir -p /u01/data1/PROD/
mkdir -p /u01/data2/PROD/
mkdir -p /u01/arch/PROD
mkdir -p /u01/undo/PROD
mkdir -p /u01/admin/PROD/cdump
mkdir -p /u01/admin/PROD/diag
[STANDBY]pfile and password file creation in $ORACLE_HOME/dbs location:
-----copy the password file from production and rename to standby name.
cd $ORACLE_HOME/dbs
scp oracle@primary-host:/u01/app/oracle/product/11.2.0/dbs/orapwPRODDB orapwPRODSTAN
-----create an init file with minimum parameter as below
vi initPRODSTAN.ora
DB_NAME=PRODDB
DB_UNIQUE_NAME=PRODSTAN
control_files='/u01/data1/PROD/control1.ora','/u01/data2/PROD/control2.ora','/u01/undo/PROD/control3.ora'
fal_client='PRODSTAN'
fal_server='PRODDB'
db_file_name_convert='/u01/data1/PROD/','/u01/data1/PROD/','/u01/data2/PROD/','/u01/data2/PROD/'
log_file_name_convert = '/u01/redo1/PROD/,'/u01/redo1/PROD/','/u01/redo2/PROD/','/u01/redo2/PROD/'
standby_file_management='AUTO'
log_archive_config='DG_CONFIG=(PRODDB,PRODSTAN)'
log_archive_dest_1='LOCATION=/u01/arch/PRODDB VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODSTAN'
log_archive_dest_2='service=PRODDB ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=PRODDB'
[STANDBY] Listener and tns setup
cat tnsnames.ora
PRODDB =
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = primary-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODDB)))
PRODSTAN =
(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = standby-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODSTAN)))
cat listener.ora
LISTENER_PRODSTAN =
(address_list =
(address =
(protocol = tcp)
(host = standby-host)
(port = 1571)
)
)
SID_LIST_LISTENER_PRODSTAN =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRODSTAN)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0)
(SID_NAME = PRODSTAN)
)
)
lsnrctl start LISTENER_PRODSTAN
[STANDBY]start the standby database in nomount state:
SQL > startup nomount
ORACLE instance started.
Total System Global Area 5415597569 bytes
Fixed Size 3170303 bytes
Variable Size 805970240 bytes
Database Buffers 3502926846 bytes
Redo Buffers 3530176 bytes
[STANDBY]Now check the connectivity between primary and standby using sys password
-----In primary:
$ sqlplus sys/prod123@PRODSTAN as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun May 21 15:17:56 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
IN STANDBY:
sqlplus sys/prod123@PRODDB as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun May 21 15:17:56 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
STANDBY:
[STANDBY]Now connect to rman and run the duplicate command as mentioned below.
rman target sys/prod123@PRODDB auxiliary sys/prod123@PRODSTAN
Recovery Manager: Release 11.2.0.3.0 - Production on Tue June 16 08:42:46 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRODDB (DBID=728741429)
connected to auxiliary database: PRODDB (not mounted)
rman >
run
{
allocate channel primy1 type disk;
allocate channel primy2 type disk;
allocate channel primy3 type disk;
allocate channel primy4 type disk;
allocate channel primy5 type disk;
allocate channel primy6 type disk;
allocate channel primy7 type disk;
allocate auxiliary channel stanby1 type disk;
allocate auxiliary channel stanby2 type disk;
allocate auxiliary channel stanby3 type disk;
allocate auxiliary channel stanby4 type disk;
allocate auxiliary channel stanby5 type disk;
allocate auxiliary channel stanby6 type disk;
allocate auxiliary channel stanby7 type disk;
allocate auxiliary channel stanby8 type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE nofilenamecheck;
}
[STANDBY]Once duplication is done. Start the recovery
SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
DATABASE ALTERED.
SQL > select process,status,sequence# from v$managed_standby:
If you wish you open the standby with read only mode as active dataguard, then use below one.
sql> alter datbase open;
sql> alter database recover managed standby database using current logfile disconnect from session;
The physcial standby setup is ready for use
Note: Info on Physical standby datbase in oracle it may be differ in your environment like production,testing,development and directory structures and na,ming conventions etc
THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME
Thank you for posting this blog very informative
ReplyDelete