Oracle rdbms
software is installed with one database(dell) following is details:
Primary DB_NAME=dell IP
- 192.168.56.105
Primary DB_UNIQUE_name=dell
On Physical
standby I have installed only rdbms software (std will be my standby database)
Standby DB_NAME=dell IP
- 192.168.56.106
Standby DB_UNIQUE_name=std
Required
Parameters:
|
Parameter
|
Recommended Setting for Standby Database
|
|
DB_Name
|
Must be same on parimary and on all
dtandby
|
|
DB_UNIQUE_NAME
|
Must be different on parimary and on
all dtandby
|
|
LOG_ARCHIVE_CONFIG
|
This parameter includes db_unique_name
which are the part of Dataguard configuration
|
|
LOG_ARCHIVE_DEST_n
|
Define local and remote archive log file
location
|
|
LOG_ARCHIVE_DEST_STATE_n
|
Define state of archiving (ENABLE or
DIFER)
|
|
REMOTE_LOGIN_PASSWORDFILE
|
Must be in EXCLUSIVE Mode
|
|
FAL_SERVER
|
Use for archivelog gap resolution
(required only in physical standby server)
|
|
DB_FILE_NAME_CONVERT
|
required when directory structure is
different datafile
|
|
LOG_FILE_NAME_CONVERT
|
required when directory structure is
different logfile
|
|
STANDBY_FILE_MANAGEMENT
|
Keep auto to create file automatically on
standby
|
Steps
Perform following steps on Primary database:
Make sure database
is in archivelog mode
select name from v$database;
if note use following command to change to archive mode
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
Make sure
database is in force logging mode
select force_logging from v$database;
alter database force logging;
Verify
DB_NAME and DB_UNIQUE_NAME of Primary Database
show parameter db_name
show parameter db_unique_name
Make
DB_UNIQUE_NAME to be part of dataguard.(std service we will create soon)
alter system set
log_archive_config='dg_config=(dell,std)';
Set
archivelog destinations
alter system set log_archive_dest_1='location=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
valid_for=(all_logfiles,all_roles) db_unique_name=dell' scope=both;
alter system set log_archive_dest_2='service=std LGWR
ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=std' scope=both;
alter system set log_archive_dest_state_1='ENABLE'
scope=both;
alter system set log_archive_dest_state_2='ENABLE'
scope=both;
Set remote
login password to exclusive
alter system set remote_login_passwordfile=exclusive
scope=spfile;
show parameter remote_login
Set fal
server and file name convert parameter incase if dirrctory structure is
different in primary and standby database
alter system set fal_server=std scope=both;
alter system set fal_client=std scope=both;
alter system set db_file_name_convert='std','dell'
scope=spfile;
alter system set log_file_name_convert='std','dell'
scope=spfile;
alter system set log_archive_max_processes=10 scope=both;
alter system set standby_file_management=auto scope=both;
Now
configure required service (dell and std)
netmgr
Now backup
Primary Database using rman
rman target /
RMAN> backup database plus archivelog;
Now Create
standby controlfile and pfile
alter database create standby controlfile as
'/tmp/stdcontrol.ctl';
create pfile='/tmp/initstd.ora' from spfile;
Modify
initstd.ora file
1.
Change
db_unique_name
2.
Change fal_server
3.
Change
log_archive_dest_n
4.
Change location of
controlfile
ps: Standby
add directory
[oracle@standby ~]$ mkdir -p
/u01/app/oracle/admin/std/adump
[oracle@standby ~]$ mkdir -p /u01/app/oracle/oradata/std/
[oracle@standby ~]$ mkdir -p
/u01/app/oracle/fast_recovery_area/std/
Create appropriate
directory on physical standby and copy backupset, archivelog, pfile, standby
controlfile and password file to physical standby database
# Standby controlfile to all locations.
scp /tmp/stdcontrol.ctl
oracle@standby:/u01/app/oracle/std/control01.ctl
cp control01.ctl /u01/app/oracle/fast_recovery_area/std/control02.ctl
Archivelogs and backups
scp -r /u01/app/oracle/fast_recovery_area/DELL
oracle@standby:/u01/app/oracle/fast_recovery_area/
# Parameter file.
scp /tmp/initstd.ora oracle@standby:/tmp/initstd.ora
# Remote login password file.
scp /u01/app/oracle/product/11.2.0.4/db/dbs/orapwdell oracle@standby:/u01/app/oracle/product/11.2.0.4/db/dbs/
change orapwdell to orapwstd
On physical
standby server
Create
service on physical standby database and update /etc/oratab file
netmgr
Update /etc/oratab (std:
/u01/app/oracle/product/11.2.0/dbhome_1:N)
Now start
listener on both server
lsnrctl start
Restore
backup on standby:
create spfile from pfile;
vi
/etc/oratab
std: /u01/app/oracle/product/11.2.0/dbhome_1:N
. oraenv
std
sqlplus / as sysdba
create spfile from pfile=’/tmp/initstd.ora’
exit
Restore
backupfile
rman target /
startup mount
restore database;
Create
standby redolog files on primary and standby database
alter database add standby logfile
('/u01/app/oracle/oradata/std/standby_redo01.log') size 50M;
alter database add standby logfile ('/u01/app/oracle/oradata/std/standby_redo02.log')
size 50M;
alter database add standby logfile ('/u01/app/oracle/oradata/std/standby_redo03.log')
size 50M;
alter database add standby logfile
('/u01/app/oracle/oradata/std/standby_redo04.log') size 50M;
select member from v$logfile where type='STANDBY';
On Primary
Database
alter database add standby logfile ('/u01/app/oracle/oradata/dell/standby_redo01.log')
size 50M;
alter database add standby logfile ('/u01/app/oracle/oradata/dell/standby_redo02.log')
size 50M;
alter database add standby logfile ('/u01/app/oracle/oradata/dell/standby_redo03.log')
size 50M;
alter database add standby logfile
('/u01/app/oracle/oradata/dell/standby_redo04.log') size 50M;
select member from v$logfile where type='STANDBY';
Now start
redo apply process on standby
alter database recover managed standby database
disconnect from session;
Following
command is use to stop redo apply process
alter database recover managed standby database cancel;
Test Log
Transport
On the primary server, check the latest archived redo log
and force a log switch.
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select sequence#, first_time, next_time from
v$archived_log order by sequence#;
alter system switch logfile;
Check the
now archived redo log has arrived at the standby server and been applied.
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select sequence#, first_time, next_time, applied from
v$archived_log order by sequence#;
select name, open_mode, database_role, db_unique_name,
protection_mode from v$database;
NAME
OPEN_MODE
DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ----------------
--------------------
PROTECTION_MODE
--------------------
DELL
MOUNTED PHYSICAL
STANDBY std
MAXIMUM PERFORMANCE
Steps to Configure Read Only Standby
step standby
shutdown immediate;
startup mount;
alter database open read only;
Change start
redo apply process on standby
shutdown immediate;
startup mount;
alter database recover managed standby database
disconnect from session;
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select sequence#, first_time, next_time, applied from
v$archived_log order by sequence#;
Active Data Guard aconfiguration
step standby
shutdown immediate;
startup mount;
alter database open read only;
alter database recover managed standby database
disconnect from session;
select name, open_mode, database_role, protection_mode
from v$database;
Snapshot Standby Configuration
step standby
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database convert to snapshot standby;
SQL> alter database open;
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY
SQL> create table hr.emp5 as select * from hr.employees;
Table created.
SQL> select count(*) from hr.emp5;
COUNT(*)
----------
107
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area
784998400 bytes
Fixed Size 2257352 bytes
Variable Size
515903032 bytes
Database Buffers
264241152 bytes
Redo Buffers 2596864 bytes
Database mounted.
shutdown again
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area
784998400 bytes
Fixed Size 2257352 bytes
Variable Size
515903032 bytes
Database Buffers
264241152 bytes
Redo Buffers 2596864 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database
disconnect;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> select count(*) from hr.emp5;
select count(*) from hr.emp5
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed
tables/views only
Database Switchover Roles
step standby
SQL> select name, open_mode, database_role,
protection_mode from v$database;
NAME
OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- ---------- ----------------
--------------------
DELL
MOUNTED PHYSICAL STANDBY
MAXIMUM PERFORMANCE
SQL> select sequence#, first_time, next_time, applied
from v$archived_log order by sequence#;
step primary
SQL> select name, open_mode, database_role,
db_unique_name, protection_mode from v$database;
NAME
OPEN_MODE
DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ----------------
------------------------------
PROTECTION_MODE
--------------------
DELL READ
WRITE PRIMARY dell
MAXIMUM PERFORMANCE
step primary
SQL> alter database commit to switchover to standby;
SQL> shutdown immediate;
SQL> start nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database
disconnect from session;
SQL> select name, open_mode, database_role,
db_unique_name, protection_mode from v$database;
step standby
SQL> alter database commit to switchover to primary;
SQL> shutdown immediate;
SQL> startup
SQL> select name, open_mode, database_role,
protection_mode from v$database;
SQL> select sequence#, first_time, next_time from
v$archived_log order by sequence#;
SQL> alter system switch logfile;
SQL> select sequence#, first_time, next_time from
v$archived_log order by sequence#;
step primary
SQL> select sequence#, first_time, next_time, applied
from v$archived_log order by sequence#;