Configuring standby database
from primary database (Active Database)
Primary
Database = rman
Standby
Database = rman2
Important
Parameter
For primary db:
1. db_name = rman (use same name for all
standby databases)
2. db_unique_name = rman (a
unique db name to uniquely identify primary and standby db because db_name is
same)
3
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/rman'
(destination for local archive)
4. *.log_archive_dest_2='service=rman2 ( destination for standby database)
5. Standby_file_management=
auto (it automatically create file at standby db site)
Note : STANDBY_FILE_MANAGEMENT is set to AUTO so that when Oracle files are
added or dropped from the primary database, these changes are made to the
standby databases automatically. The STANDBY_FILE_MANAGEMENT is only applicable
to the physical standby databases.
For Standby db:
initrman2.ora
//this is my ora file for standby
*.db_name='rman'
*.db_unique_name='rman2'
*.db_file_name_convert='/u01/app/oracle/oradata/rman','/u01/app/oracle/oradata/rman2'
//specify the path name and datafile
location of primary database
datafile followed by standby location
*.log_file_name_convert='/u01/app/oracle/oradata/rman','/u01/app/oracle/oradata/rman2'
//specify
the path name and redo logfile location of primary
database redo logfile followed by standby
location
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/rman2'
//location
of archives coming from primary
database
*.fal_server=rman //the value is tns service name, to fetch archived log
from primary site when log gap is detected at standby site
*.fal_client=rman2 //the value is tns service
name to copies missing archive to standby
database.
Steps for configuring
standby database
Both side create listener
and Tns
Both
side create password file with same password
Both
side db_name must be same
At standby database side
Configure
listerner on standby side and start the listener
start the
standby database in nomount state
SQL> startup
nomount
ORACLE
instance started.
Total System
Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 197135448 bytes
Database
Buffers 109051904 bytes
Redo Buffers 6336512 bytes
Now on
Primary Side ,
Do the Below
command , to connect with RMAN
SQL>
!rman target sys/oracle auxiliary sys/oracle@rman2
Recovery
Manager: Release 11.2.0.1.0 - Production on Sat Mar 19 19:01:13 2016
Copyright
(c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
connected to
target database: RMAN (DBID=1792754794)
connected to
auxiliary database: RMAN (not mounted)
RMAN>
duplicate target database for standby from active database;
Starting
Duplicate Db at 19-MAR-16
using target
database control file instead of recovery catalog
allocated
channel: ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=18 device type=DISK
contents of
Memory Script:
{
backup as copy reuse
targetfile
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwrman' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwrman2' ;
}
executing
Memory Script
Starting
backup at 19-MAR-16
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=47 device type=DISK
Finished
backup at 19-MAR-16
contents of
Memory Script:
{
backup as copy current controlfile for
standby auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/cntrlrman2.dbf';
}
executing
Memory Script
Starting
backup at 19-MAR-16
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting datafile copy
copying
standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_rman.f
tag=TAG20160319T190131 RECID=5 STAMP=906922893
channel
ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished
backup at 19-MAR-16
contents of
Memory Script:
{
sql clone 'alter database mount standby
database';
}
executing
Memory Script
sql
statement: alter database mount standby database
contents of
Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/rman2/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/rman2/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/rman2/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/rman2/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/rman2/users01.dbf";
set newname for datafile 6 to
"/u01/app/oracle/oradata/rman2test01";
backup as copy reuse
datafile
1 auxiliary format
"/u01/app/oracle/oradata/rman2/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/rman2/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/rman2/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/rman2/users01.dbf" datafile
6 auxiliary format
"/u01/app/oracle/oradata/rman2test01" ;
sql 'alter system archive log current';
}
executing
Memory Script
executing
command: SET NEWNAME
renamed
tempfile 1 to /u01/app/oracle/oradata/rman2/temp01.dbf in control file
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
executing
command: SET NEWNAME
Starting
backup at 19-MAR-16
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting datafile copy
input
datafile file number=00001 name=/u01/app/oracle/oradata/rman/system01.dbf
output file
name=/u01/app/oracle/oradata/rman2/system01.dbf tag=TAG20160319T190141
channel
ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel
ORA_DISK_1: starting datafile copy
input
datafile file number=00002 name=/u01/app/oracle/oradata/rman/sysaux01.dbf
output file
name=/u01/app/oracle/oradata/rman2/sysaux01.dbf tag=TAG20160319T190141
channel
ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel
ORA_DISK_1: starting datafile copy
input
datafile file number=00003 name=/u01/app/oracle/oradata/rman/undotbs01.dbf
output file
name=/u01/app/oracle/oradata/rman2/undotbs01.dbf tag=TAG20160319T190141
channel
ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel
ORA_DISK_1: starting datafile copy
input
datafile file number=00006 name=/u01/app/oracle/oradata/rmantest01
output file
name=/u01/app/oracle/oradata/rman2test01 tag=TAG20160319T190141
channel
ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel
ORA_DISK_1: starting datafile copy
input
datafile file number=00004 name=/u01/app/oracle/oradata/rman/users01.dbf
output file
name=/u01/app/oracle/oradata/rman2/users01.dbf tag=TAG20160319T190141
channel
ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished
backup at 19-MAR-16
sql
statement: alter system archive log current
contents of
Memory Script:
{
switch clone datafile all;
}
executing
Memory Script
datafile 1
switched to datafile copy
input
datafile copy RECID=5 STAMP=906922992 file name=/u01/app/oracle/oradata/rman2/system01.dbf
datafile 2
switched to datafile copy
input
datafile copy RECID=6 STAMP=906922992 file
name=/u01/app/oracle/oradata/rman2/sysaux01.dbf
datafile 3
switched to datafile copy
input
datafile copy RECID=7 STAMP=906922992 file
name=/u01/app/oracle/oradata/rman2/undotbs01.dbf
datafile 4
switched to datafile copy
input
datafile copy RECID=8 STAMP=906922992 file
name=/u01/app/oracle/oradata/rman2/users01.dbf
datafile 6
switched to datafile copy
input
datafile copy RECID=9 STAMP=906922992 file
name=/u01/app/oracle/oradata/rman2test01
RMAN-05535:
WARNING: All redo log files were not defined properly.
RMAN-05535:
WARNING: All redo log files were not defined properly.
RMAN-05535:
WARNING: All redo log files were not defined properly.
Finished
Duplicate Db at 19-MAR-16
Now on
Standby Side , do the Below Command
SQL> alter database recover managed standby database disconnect;
// This is used to start MRP (media recovery
process) process
Other Basic Commands
How to stop mrp process
SQL> alter database recover managed standby database cancel;
How to check mrp process is
running or not
$ps –ef |grep mrp
How to Start the Standby db
in read only mode
SQL> alter database recover managed standby database cancel ; // stops the MRP process
SQL> alter database open ; // opens in read only mode
Necessary Views, not to Forget :
- v$archive_dest - To check if any error in transferring archives
- v$managed_standby - Current status information for oracle
database process
3 v$archived_log - To check how many archives are applied at standby
database Side
Common Error I faced During
Setup :
RMAN-00571:
===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK
FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-00554: initialization of internal recovery
manager package failed
RMAN-04005: error from target database:
ORA-12528: TNS:listener: all appropriate instances
are blocking new connections
Solution :
Add (UR = A) in TnsNames.ora file , after your
Service Name
Example :
..
RMAN2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
oracleasm1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rman2)
(UR = A)
)
)
Error 2 :
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-00554:
initialization of internal recovery manager package failed
RMAN-04005:
error from target database:
ORA-12154:
TNS:could not resolve the connect identifier specified
OR
RMAN-00571:
===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:
===========================================================
RMAN-00554: initialization
of internal recovery manager package failed
RMAN-04005:
error from target database:
ORA-12514:
TNS:listener does not currently know of service requested in connect descriptor
Solution
:
Check Yout
TnsNames.ora & also Listener.ora
File.
Check the
opening brackests and Closing Brackets.
Also Check
Database SID is Properly specified or Not.
Ooho..
.. So Big Task. . . Right ??? :D
So Guys , Now Lets Learn ROLE - TRANSFER
… How to Perform SWITCHOVER
STEP 1
On
Primary Site :
SQL>
select database_role , switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
----------------------- -----------------------------
PRIMARY TO
STANDBY
Note :
Primary DATABASE should be in ‘Open Mode’
SQL>
alter database commit to switchover to physical standby;
Database
altered.
OR You can Use Below Query , in case
Above Query Gives Error
SQL> alter database commit to switchover to PHYSICAL STANDBY with SESSION SHUTDOWN;
Primary >
Shutdown Immediate
//Standby is
Still either ’Mounted’ in redo apply
mode or open mode for ‘read only ‘access.
STEP 2
Now, Lets
Go to
Standby Site
You will see
Pending Status , see
SQL>
select database_role , switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
----------------
--------------------------------------------
PHYSICAL
STANDBY SWITCHOVER
PENDING
So now, we
will switch Standby site to primary, Lets do it with below command
Standby site
:
SQL> alter database commit to switchover to
primary;
alter database commit to switchover to primary
*
ERROR at
line 1:
ORA-16139:
media recovery required
Oops , media
Recovery is Recovered here..
So I will do
SQL> recover managed standby database finish;
Primary Site - - - >SQL>
startup mount //Mount
the Primary Site
Done, Now check the Status Using Below Query Standby Site
SQL>
select database_role , switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
----------------
----------------------------------------------
PRIMARY TO
STANDBY
Check on
Primary Site - - -
SQL>
select database_role , switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
----------------
-----------------------------------------------
PHYSICAL
STANDBY TO PRIMARY
ERROR FACED
during SWITCHOVER
SQL> alter database commit to switchover to
primary;
alter database commit to switchover to primary
*
ERROR at
line 1:
ORA-16139:
media recovery required
SQL>
recover managed standby database finish;
ORA-00283:
recovery session canceled due to errors
ORA-01111:
name for data file 5 is unknown - rename to correct file
ORA-01110:
data file 5: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005'
ORA-01157:
cannot identify/lock data file 5 - see DBWR trace file
ORA-01111:
name for data file 5 is unknown - rename to correct file
ORA-01110:
data file 5: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005'
SQL>
alter database create datafile
'/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005' as
'/u01/app/oracle/oradata/rman2/demots.dbf';
Database
altered.
SQL> recover
managed standby database finish;
Media
recovery complete.
SQL> alter database commit to switchover to
primary;
Database
altered.
SQL>