Environment:
OS: Red Hat Enterprise Linux Server release 6.10
Oracle: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Hostname: primary.localdomain
stdby.localdomain
Oracle binary and Oracle database accdb has been installed on host primary where as we have installed Oracle binaries only on the standby host stdby. While installing Oracle on standby host we have selected install software only.
Primary
Verify database is on archive log mode or not. Convert primary database to archivelog mode.
SELECT log_mode FROM v$database;
Shutdown immediate
startup mount
alter database archivelog;
alter database open;
Check force logging has been enabled or not. Set database into force logging.
select name, force_logging from v$database;
alter database force logging;
Verify db_name and db_unique_name parameter. Both value should be same for primary database but they will be different for standby database.
show parameter db_name
show parameter db_unique_name
Configure Standby related Oracle database parameters.
alter system set log_archive_config='DG_CONFIG=(accdb,accstdb)';
alter system set log_archive_dest_2='SERVICE=accstdb NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=accstdb';
alter system set log_archive_dest_state_2=enable;
alter system set fal_server='accstdb';
alter system set fal_client='accdb';
alter system set standby_file_management=auto;
Check additional parameters
show parameter passwordfile
show parameter log_archive_format
Add static services to the listener
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = accdb)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = accdb)
)
(SID_DESC =
(GLOBAL_DBNAME = accdb_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = accdb)
)
)
Configure tnsnames.
ACCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = accdb)
)
)
ACCSTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stdby.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = accdb)
)
)
Reload listener and check listener status
lsnrctl reload
lsnrctl status
Create pfile to oracle home from current spfile.
Create pfile from spfile;
Now ship recently created pfile and password file from primary to standby server.
cd
scp pfile oracle@stdby:
cd $ORACLE_HOME/dbs
scp orapwaccdb oracle@stdby:
Ship listener and tnsnames files to standy server.
cd $ORACLE_HOME/network/admin
scp *.ora stdby:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
Add standby redolog files to primary database
ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo01.log' SIZE 52428800;
ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo02.log' SIZE 52428800;
ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo03.log' SIZE 52428800;
ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo04.log' SIZE 52428800;
Standby
Create required directories on standby server
mkdir -p /u01/app/oracle/admin/accdb/adump
mkdir -p /u01/app/oracle/oradata/accdb
mkdir -p /u01/app/oracle/admin/DB11G/accdb
mkdir -p /u01/app/oracle/flash_recovery_area
mkdir -p /u01/app/oracle/flash_recovery_area/accdb
Open listener.ora file with the text editor and modify the high lighted sections as host and GLOBAL_DBNAME.
vi listener.ora
Reload listener and check listener status
lsnrctl reload
lsnrctl status
Open pfile with text editor and modify highlighted sections.
cd
vi pfile
Copy Password file.
cp orapwaccdb /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
Login to standby server and create spfile from pfile. Once spfile is created startup database on nomount mode.
sqlplus / as sysdba
create spfile from pfile='/home/oracle/pfile';
startup nomount
Primary Database
Validate network connectivity with tnsping to accdb and accstdb tnsnames
tnsping accdb
tnsping accstdb
Connect to standby server as user sys. Just to make sure we can connect to standby server.
sqlplus sys@accstdb as sysdba
If you are able to connect to the standby server then connect rman target as primary and auxiliary as standby server. Fix if there is any issue connecting to standy from the primary.
rman target / auxiliary sys@accstdb
Execute duplicate command to create standby database from active database
duplicate target database for standby from active database nofilenamecheck;
Add logfile to standby database;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE '/u01/app/oracle/oradata/accdb/redo01.log' SIZE 52428800;
ALTER DATABASE ADD LOGFILE '/u01/app/oracle/oradata/accdb/redo02.log' SIZE 52428800;
ALTER DATABASE ADD LOGFILE '/u01/app/oracle/oradata/accdb/redo03.log' SIZE 52428800;
Add redo log and standby redo log files
ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo01.log' SIZE 52428800;
ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo02.log' SIZE 52428800;
ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo03.log' SIZE 52428800;
ALTER DATABASE ADD standby LOGFILE '/u01/app/oracle/oradata/accdb/stdby_redo04.log' SIZE 52428800;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Check database open_mode and its role
select name, open_mode, database_role from v$database;
Alter database to recovery mode.
alter database recover managed standby database disconnect;
Verify logfile switch.
alter system switch logfile;
We can noticed RFS receives archive log and apply
Active Standby database with Realtime apply
Cancel recovery on managed standby and open database on read only mode.
alter database recover managed standby database cancel;
alter database open read only;
Start recovery on managed standby
alter database recover managed standby database using current logfile;
Create table on primary.
create table abc(sn number);
insert into abc values(1);
/
commit;
Verify table and rows on standby server
select * from abc;
For details please visit Oracle documentation
https://docs.oracle.com/database/121/DGBKR/sofo.htm#DGBKR390
0 comments:
Post a Comment