Different scenarios
Standby database
|
OS backup
|
using standby C.F restore
and then RECOVER MANAGED STANDBY DATABASE
|
|
Standby database
|
RMAN backup
|
manual
|
using standby C.F restore
and then RECOVER MANAGED STANDBY DATABASE
|
Standby database
|
RMAN backup
|
with duplicate
|
duplicate target database for standby
|
Standby database
|
Active RMAN backup
|
Using duplicate target
database for standby from active database
|
|
Method 1 : OS backup
Standby database with OS backup without duplicate
1. Copy the production backup files to the standby server. (Copy of individual datafiles after primary database was shutdown)
2. Copy the standby controlfile to the standby server. (ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:\stbycf.f';)
3. Alter the control_files and archive parameters in the standby init.ora
4. ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('C:\Oracle\Oradata\TSH1\redo1a.log','C:\Oracle\Oradata\TSH1\redo1b.log') SIZE 500K;
5. Copy all archive logs from the primary to the standby server
6. Mount and Recover :
SQL> CONNECT sys/password AS SYSDBA
SQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
7. Open Read Only or Activate as Primary
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
------------or
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Method 2 : RMAN backup without duplicate (manual method)
Standby database with RMAN backup without duplicate
1. Copy the production backup and archivelog files to the standby server.
RMAN> backup plus archivelog
2. Take Control file backup.Copy the standby controlfile ,
parameter file and password file to standby
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:\stbycf.f';
RMAN> run
{
allocate channel c1 type disk;
backup current controlfile for standby;
}
Note : password file should be same in standby and production but different in clone
3. Alter the control_files and archive parameters in the standby init.ora. Create the SPFILE from pfile and restore controlfile from Backup and then mount
db_unique_name='DB11G_STBY'
fal_server='DB11G'
log_archive_dest_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
RMAN9i> restore controlfile from '/oracle/11gr2/flash_recovery_area/11gr2/backupset/2014_11_06/o1_mf_ncnnf_TAG20141106T150009_3oqff2fb_.bkp';
or
RMAN11g> restore standby controlfile from '/oracle/11gr2/flash_recovery_area/11gr2/backupset/2014_11_06/o1_mf_ncnnf_TAG20141106T150009_3oqff2fb_.bkp';
RMAN> STARTUP MOUNT; -- You can mount since you have already copied the C.F to the control_file location
or
RMAN_11g> sql 'alter database mount standby database';
4. Restore the database
RMAN> run {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK ;
set newname for datafile 1 to '/oracle/app/oracle/product/11gr2/oradata/standby/data/sys01.dbf' ;
....
.....
SQL "ALTER DATABASE RENAME FILE ''/oracle/11gr2/oradata/primary/log/redo01.log'' TO ''/oracle/app/oracle/product/11gr2/oradata/standby/log/redo01.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/oracle/11gr2/oradata/primary/log/redo02.log'' TO ''/oracle/11gr2/oradata/standby/log/redo02.log'' ";
restore database;
switch datafile all ;
}
5. Create Redo Logs and Standby Redo Logs
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('C:\Oracle\Oradata\TSH1\redo1a.log','C:\Oracle\Oradata\TSH1\redo1b.log') SIZE 500K;
6. Ensure all archive logs from the primary are avaialble to the standby server
RMAN> list backup of archivelog all;
select the sequence x with the highest values in Low Scn
RMAN> recover database until sequence x+1;
7. Mount and Recover :
SQL> CONNECT sys/password AS SYSDBA
SQL> STARTUP NOMOUNT PFILE=C:\Oracle\Admin\TSH1\pfile\init.ora
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
8. Open Read Only or Activate as Primary
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
------------or
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Method 3 : RMAN backup with duplicate
1. Backup control file for standby and take backup
RMAN_PROD > backup current controlfile for standby format '/u01/backup/standbycontrol.ctl';
RMAN_PROD > rman target /
RMAN> run
2> {
3> allocate channel c2 device type disk format '/u01/backup/%u';
4> backup database plus archivelog;
5> }
2. Copy backup files , stanby control file , pfile and password file to standby server on the same location where backup was taken in prod.
Modify pfile to add entry standby related entries.
Modify tnsnames.ora to add entry for primary and standby database.
3. Startup auxilary in nomount
SQL> startup nomount;
RMAN_STDBY > rman target sys/prodpasswd auxiliary sys@stdbypasswd
RMAN_STDBY > duplicate target database for standby dorecover;
duplicate will first restore the control file from /backup and create control files in control_files locations or flash_recovery_area.
Then it will put standby database in mount state and restore all other datafiles.
4. Start managed recovery
SQL_STDBY > alter database recover managed standby database disconnect from session;
5. Archive some logs on prod and check if they are applied on standby.
FOR STANDBY – the duplicate is for use as a standby so a DBID change will not be forced.
FROM ACTIVE DATABASE – instructs RMAN to use the active target database instead of disk based backups.
DORECOVER – do recovery bringing the standby database up to the current point in time.
SPFILE – values for parameters specific to the auxiliary instance can be set here.
NOFILENAMECHECK – this option is added because the duplicate database files uses the same name as the source database.
Clone database with connecting to prod/target : with backup : using duplicate
Clone database without connecting to prod/target : with backup location : using duplicate
Clone database from Active database : without backup : using duplicate
Method 4 : RMAN backup with Active duplicate
Create Active Dataguard over the network with duplicate
---- First on Primary server
Database Name :- WHITE
Primary db_unique_name :- WHITE
standby db_unique_name :- BLACK
Primary Database Server Name :- DC1
Standby Database Server Name :- DC2
1. Creat the password file if one does not exist.
Prod:> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(white,black)';
Prod:> alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATA/archivelog/white/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=white';
i.e when in either role(primary or standby) store logfiles ( both redo logfiles or archive redo log files ) to location(+DATA/archivelog/white/) for database with db_unique_name=white
Prod:> alter system set LOG_ARCHIVE_DEST_2='SERVICE=black LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=black';
i.e when in primary role send online redo logfiles using tns service=black to a database with db_unique_name=black
Prod:> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
Prod:> alter system set FAL_SERVER=black;
Prod:> alter system set FAL_CLIENT=white;
Prod:> alter system set DB_FILE_NAME_CONVERT='+DATA/black/datafile/','+DATA/white/datafile/','+DATA/black/tempfile/','+DATA/white/tempfile/' scope=spfile;
Prod:> alter system set LOG_FILE_NAME_CONVERT='+DATA/black/onlinelog/','+DATA/white/onlinelog/' scope=spfile;
2. Add standby redolog files.
Prod:> alter database add standby logfile size 5m; ( 3 or 4 )
---- Now on Standby server
3. Copy from production or create pfile in standby
change paramter like control_files,local_listener , db_unique_name='black'
4. Copy from production or create a password file for standby database
The username is required to be SYS and the password needs to be the same on the Primary and Standby.
The password file name must match the instance name/SID used at the standby site, not the DB_NAME.
5. Nomount and run duplicate
STDby:> startup nomount
STDby:> rman target sys/oracle@white auxiliary sys/oracle@black
RMAN> run {
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database dorecover
spfile
parameter_value_convert 'white','black'
set db_unique_name='black'
set db_file_name_convert='/white/','/black/'
set log_file_name_convert='/white/','/black/'
set control_files='+DATA'
set log_archive_max_processes='5'
set fal_client='black'
set fal_server='white'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(white,black)'
;
}
STDby:> select name from v$datafile;
STDby:> select name from v$tempfile;
STDby:> select member,type from v$logfile;
STDby:> select name,database_role,db_unique_name from v$database;
STDby:> alter database recover managed standby database disconnect from session;
STDby:> select open_mode from v$database;
STDby:> archive log list;
6. Start managed recovery
SQL> alter database recover managed standby database disconnect from session;
Creating a Physical Standby Database with RMAN dupliacte(without any backup )
Another Example :
Copy the Primary init.ora and change control_files , db_unique_name , local_listener and audit_file_dest parameters.Using this file , start the standby stdbydb in nomount state (C.F will be created by rman duplicate on successfull restoration)
Set up connectivity from standby to production and also to connect standby in mounted state.
Standby : listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx2)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdbydb)
(ORACLE_HOME = /oracle/app/product/11.2.0/dbhome_1 )
(SID_NAME = stdbydb)
)
)
ADR_BASE_LISTENER = /oracle/app
-- listener entry for data broker
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdbydb_DGMGRL )
(ORACLE_HOME = /oracle/app/product/11.2.0/dbhome_1 )
(SID_NAME = stdbydb )
)
)
Standby : tnsnames.ora
stdbydb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdbydb) (UR=A)
)
)
LISTENER_stdbydb =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx2)(PORT = 1521))
)
)
--connectivity to standby itself for data broker
stdbydb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdbydb_DGMGRL) (UR=A)
)
)
--connectivity to production
proddb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb)
)
)
Primary : listener.ora
-- listener entry for data broker
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = proddb_DGMGRL )
(ORACLE_HOME = /oracle/app/product/11.2.0/dbhome_1 )
(SID_NAME = proddb )
)
)
Primary : tnsnames.ora
--connectivity to standby for data broker
stdbydb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lnx2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdbydb_DGMGRL) (UR=A)
)
)
Standby Server :> rman target sys/oracle@proddb auxiliary sys/oracle@stdbydb
RMAN> run {
allocate channel prmy1 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
;
}
SQL> select open_mode,database_role from V$Database;
Data Broker
Prepare the Listener and tnsnames files
Enable the broker on both pri and standby databases
SQL> show parameters broker
SQL> alter system set dg_broker_start=true scope=both;
Check connectivity from both servers to both pri and standby databases :
Prod server : sqlplus sys/oracle@stdbydb as sysdba ; and sqlplus sys/oracle@proddb as sysdba ;
Standby Server : sqlplus sys/oracle@stdbydb as sysdba ; and sqlplus sys/oracle@proddb as sysdba ;
Broker configuration
dgmgrl sys/oracle
DGMGRL> create configuration dgproddb as primary database is proddb connect identifier is proddb;
--Configuration "dgproddb" created with primary database "proddb"
DGMGRL> add database stdbydb as connect identifier is stdbydb maintained as physical;
--Database "stdbydb" added
DGMGRL> show configuration;
DGMGRL> enable configuration;
DGMGRL> enable database stdbydb;
Primary : select NAME,DB_UNIQUE_NAME,HOST_NAME,OPEN_MODE,DATABASE_ROLE from v$database, v$instance;
Standby : select NAME,DB_UNIQUE_NAME,HOST_NAME,OPEN_MODE,DATABASE_ROLE from v$database, v$instance;
