=================================================
How to Create Dataguard physical standby database
=================================================
###################
On Production
###################
--------------------------------
Step 0. Primary DB Configuration
--------------------------------
[root@DBPROD ~]# uname --kernel-name --kernel-release --kernel-version
Linux 3.8.13-16.2.1.el6uek.x86_64 #1 SMP Thu Nov 7 17:01:44 PST 2013
[root@DBPROD ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
-------------------------------------
Step 1. Primary DB should use SPFILE
-------------------------------------
SQL> show parameter spfile;
NAME TYPE VALUE
------- ---------- --------------------------------------------
spfile string /u01/app/oracle/11gR2/dbs/spfileDBPROD.ora
-------------------------------------------------
Step 2. Primary DB ARCHIVELOG Mode
-------------------------------------------------
SQL>SELECT log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
-----------------------------------------------
Step 3. Primary DB in force loggings
-----------------------------------------------
SQL>SELECT force_logging FROM v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
------------------------------------------------
Step 4. DB Unique Name
------------------------------------------------
Note: DB_NAME on both PRIMARY & STANDBY should be same
DB_UNIQUE_NAME should be different on both.
SQL>show parameter db_name;
SQL>show parameter db_unique_name;
----------------------------------------
Step 5. Set Dataguard Parameters
----------------------------------------
Note:LOG_ARCHIVE_CONFIG enables or disables the sending of redo logs to remote destinatins.
Also receipt of remote logs. specifies unique database name for each db in Data Guard Configuration.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DBPROD,DBSTAND)' SCOPE=SPFILE;
System altered.
Note: LOG_ARCHIVE_DEST_n must contain LOCATION OR SERVICE attribute.
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u05/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBPROD' SCOPE=SPFILE;
System altered.
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DBSTAND VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSTAND' SCOPE=SPFILE;
System altered.
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;
System altered.
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.
Note: FAL_SERVER specifies the FAL(Fetch Archive Log) server for standby database.
SQL>ALTER SYSTEM SET FAL_SERVER=DBSTAND;
System altered.
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;
System altered.
Note: This Parameter only requires if directory structure on PRIMARY is different in STANDBY.
SQL>ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DBSTAND','DBPROD' SCOPE=SPFILE;
System altered.
Note: This Parameter only requires if directory structure on PRIMARY is different in STANDBY.
SQL>ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DBSTAND','DBPROD' SCOPE=SPFILE;
System altered.
-------------------------------------
Step 6. Modify TNSNAMES.ORA File
-------------------------------------
DBPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DBPROD.lab)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DBPROD)
)
)
DBSTAND =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DBSTAND.lab)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DBSTAND)
)
)
-----------------------------------
Step 7. Backup Primary Database
-----------------------------------
[oracle@DBPROD ~]$ rman target /
connected to target database: DBPROD (DBID=1792084780)
RMAN> backup format '/u02/rman/DBPROD/DBPROD_bkup_%t_%s_%p.bak' database plus archivelog;
Starting backup at 14-JUN-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=849547450
...
Finished backup at 14-JUN-14
Starting backup at 14-JUN-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=27 RECID=28 STAMP=850204583
channel ORA_DISK_1: starting piece 1 at 14-JUN-14
channel ORA_DISK_1: finished piece 1 at 14-JUN-14
piece handle=/u02/rman/DBPROD/DBPROD_bkup_850204583_32_1.bak tag=TAG20140614T075623 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-JUN-14
Starting Control File and SPFILE Autobackup at 14-JUN-14
piece handle=/u02/rman/DBPROD/cf_c-3503257845-20140614-00 comment=NONE
Finished Control File and SPFILE Autobackup at 14-JUN-14
-----------------------------------
Step 8. Create CNTRL FILE
-----------------------------------
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/control01.ctl';
Database altered.
#######################
ON STANDBY DATABASE
#######################
--------------------------------
Step 0. Standby DB Configuration
--------------------------------
[root@DBSTAND ~]# uname --kernel-name --kernel-release --kernel-version
Linux 3.8.13-16.2.1.el6uek.x86_64 #1 SMP Thu Nov 7 17:01:44 PST 2013
[root@DBSTAND ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
-------------------------------------
Step 1. Install Oracle Software-Only
-------------------------------------
Note: Software has been installed on /u01
ORACLE_SID=DBSTAND
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/12cR1
-------------------------------------
Step 2. Create PFILE & CONTROL FILE
-------------------------------------
--On Primary
[oracle@DBPROD dbs]$ scp /tmp/control01.ctl oracle@DBSTAND.lab:/u03/oradata/datafiles/control01.ctl
Note: Create PFILE from SPFILE on PROD and Copy over to Standby and modify it
SQL> CREATE PFILE FROM SPFILE;
File created.
[oracle@DBPROD dbs]$ scp initDBPROD.ora oracle@DBSTAND.lab:/u01/app/oracle/12cR1/dbs/initDBSTAND.ora
oracle@DBSTAND.lab's password:
initDBPROD.ora 100% 940 0.9KB/s 00:00
[oracle@DBPROD dbs]$ rm initDBPROD.ora
--On Standby
Modified initDBSTAND.ora
[oracle@DBSTAND dbs]$ cat initDBSTAND.ora
*.compatible='12.1.0.0.0'
*.control_files='/u03/oradata/datafiles/control01.ctl','/u03/oradata/datafiles/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DBPROD'
*.db_unique_name='DBSTAND'
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
*.fal_server='DBPROD'
*.log_archive_config='DG_CONFIG=(DBPROD,DBSTAND)'
*.log_archive_dest_1='LOCATION=/u05/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBSTAND'
*.log_archive_dest_2='SERVICE=DBPROD VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBPROD'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=3
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
-------------------------------------
Step 3. Startup Standby
-------------------------------------
[oracle@DBSTAND dbs]$ echo $ORACLE_SID
DBSTAND
[oracle@DBSTAND dbs]$ echo $ORACLE_HOME
/u01/app/oracle/12cR1
[oracle@DBSTAND dbs]$ . oraenv
ORACLE_SID = [DBSTAND] ? DBSTAND
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/12cR1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBSTAND dbs]$ sqlplus / as sysdba;
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2286656 bytes
Variable Size 159386560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5152768 bytes
SQL> CREATE SPFILE FROM PFILE;
File created.
SQL> !rm $ORACLE_HOME/dbs/initDBSTAND.ora
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2286656 bytes
Variable Size 159386560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5152768 bytes
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/12cR1/dbs/spfi
leDBSTAND.ora
--------------------------------------------
Step 4. Modify LISTENER & TNSNAMES.ORA Files
--------------------------------------------
Note: Make sure tnsping is working and listener is up
[oracle@DBSTAND ~]$ tnsping DBPROD
[oracle@DBPROD ~]$ tnsping DBSTAND
-------------------------------------
Step 5. Create directory structure
-------------------------------------
Note: Make sure the directory structure is same as PROD(if not, use LOG/DB_FILE_NAME_CONVERT)
--On Primary run this to get all the DATAFILES location
RMAN> report schema;
-----------------------------------------
Step 6. Copy Backup files
-----------------------------------------
--backup files
SQL> !scp -r oracle@DBPROD:/u02/rman/DBPROD/* oracle@DBSTAND:/u02/rman/DBPROD/
Note: I have created same directory structure in standby for backup as it was in primary so RMAN know the path
-----------------------------------------
Step 7. Create Password File
-----------------------------------------
Copy pwd file from DBPROD to DBSTAND and rename it
-----------------------------------------
Step 8. Restore Standby DB from RMAN bkup
-----------------------------------------
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2286656 bytes
Variable Size 159386560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5152768 bytes
SQL> alter database mount;
Database altered.
[oracle@DBSTAND dbs]$ rman target /
connected to target database: DBPROD (not mounted)
RMAN> crosscheck backup;
RMAN> restore database;
channel ORA_DISK_1: piece handle=/u02/rman/DBPROD/DBPROD_bkup_850204537_31_1.bak tag=TAG20140614T075412
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 14-JUN-14
-----------------------------------------
Step 9. Add Standby logs
-----------------------------------------
--Standby
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo01.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo02.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo03.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo04.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo05.log') SIZE 50M;
Database altered.
-----------------------------------------
Step 9. Add standby logs Production
-----------------------------------------
--Production
SQL> ALTER DATABASe ADD STANDBY LOGFILE ('/u03/oradata/redo/standby_redo01.log') SIZE 50M;
SQL> ALTER DATABASe ADD STANDBY LOGFILE '/u03/oradata/redo/standby_redo02.log')SIZE 50M;
SQL> ALTER DATABASe ADD STANDBY LOGFILE ('/u03/oradata/redo/standby_redo03.log') SIZE 50M;
SQL> ALTER DATABASe ADD STANDBY LOGFILE ('/u03/oradata/redo/standby_redo04.log') SIZE 50M;
SQL> ALTER DATABASe ADD STANDBY LOGFILE ('/u03/oradata/redo/standby_redo05.log') SIZE 50M;
Database altered.
---------------------------------------
Step 10. Start Apply Process - Standby
---------------------------------------
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL>ALTER DATABASE OPEN READ ONLY;
SQL>SELECT OPEN_MODE FROM V$DATABASE;
OPEN_MODE
--------
READ ONLY
------------------
VERIFICATION
------------------
--PRIMARY
SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
THREAD# Last Primary Seq Generated
---------- --------------------------
1 36
--STANDBY
SQL> select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY')
group by thread# order by 1;
THREAD# Last Standby Seq Applied
---------- ------------------------
1 36
SQL> SELECT NAME,DATABASE_ROLE,OPEN_MODE FROM V$DATABASE;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
DBPROD PHYSICAL STANDBY MOUNTED
Enjoy.....Abid Malik
How to Create Dataguard physical standby database
=================================================
###################
On Production
###################
--------------------------------
Step 0. Primary DB Configuration
--------------------------------
[root@DBPROD ~]# uname --kernel-name --kernel-release --kernel-version
Linux 3.8.13-16.2.1.el6uek.x86_64 #1 SMP Thu Nov 7 17:01:44 PST 2013
[root@DBPROD ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
-------------------------------------
Step 1. Primary DB should use SPFILE
-------------------------------------
SQL> show parameter spfile;
NAME TYPE VALUE
------- ---------- --------------------------------------------
spfile string /u01/app/oracle/11gR2/dbs/spfileDBPROD.ora
-------------------------------------------------
Step 2. Primary DB ARCHIVELOG Mode
-------------------------------------------------
SQL>SELECT log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
-----------------------------------------------
Step 3. Primary DB in force loggings
-----------------------------------------------
SQL>SELECT force_logging FROM v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
------------------------------------------------
Step 4. DB Unique Name
------------------------------------------------
Note: DB_NAME on both PRIMARY & STANDBY should be same
DB_UNIQUE_NAME should be different on both.
SQL>show parameter db_name;
SQL>show parameter db_unique_name;
----------------------------------------
Step 5. Set Dataguard Parameters
----------------------------------------
Note:LOG_ARCHIVE_CONFIG enables or disables the sending of redo logs to remote destinatins.
Also receipt of remote logs. specifies unique database name for each db in Data Guard Configuration.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DBPROD,DBSTAND)' SCOPE=SPFILE;
System altered.
Note: LOG_ARCHIVE_DEST_n must contain LOCATION OR SERVICE attribute.
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u05/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBPROD' SCOPE=SPFILE;
System altered.
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DBSTAND VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSTAND' SCOPE=SPFILE;
System altered.
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;
System altered.
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.
Note: FAL_SERVER specifies the FAL(Fetch Archive Log) server for standby database.
SQL>ALTER SYSTEM SET FAL_SERVER=DBSTAND;
System altered.
SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;
System altered.
Note: This Parameter only requires if directory structure on PRIMARY is different in STANDBY.
SQL>ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DBSTAND','DBPROD' SCOPE=SPFILE;
System altered.
Note: This Parameter only requires if directory structure on PRIMARY is different in STANDBY.
SQL>ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DBSTAND','DBPROD' SCOPE=SPFILE;
System altered.
-------------------------------------
Step 6. Modify TNSNAMES.ORA File
-------------------------------------
DBPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DBPROD.lab)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DBPROD)
)
)
DBSTAND =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DBSTAND.lab)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DBSTAND)
)
)
-----------------------------------
Step 7. Backup Primary Database
-----------------------------------
[oracle@DBPROD ~]$ rman target /
connected to target database: DBPROD (DBID=1792084780)
RMAN> backup format '/u02/rman/DBPROD/DBPROD_bkup_%t_%s_%p.bak' database plus archivelog;
Starting backup at 14-JUN-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=849547450
...
Finished backup at 14-JUN-14
Starting backup at 14-JUN-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=27 RECID=28 STAMP=850204583
channel ORA_DISK_1: starting piece 1 at 14-JUN-14
channel ORA_DISK_1: finished piece 1 at 14-JUN-14
piece handle=/u02/rman/DBPROD/DBPROD_bkup_850204583_32_1.bak tag=TAG20140614T075623 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-JUN-14
Starting Control File and SPFILE Autobackup at 14-JUN-14
piece handle=/u02/rman/DBPROD/cf_c-3503257845-20140614-00 comment=NONE
Finished Control File and SPFILE Autobackup at 14-JUN-14
-----------------------------------
Step 8. Create CNTRL FILE
-----------------------------------
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/control01.ctl';
Database altered.
#######################
ON STANDBY DATABASE
#######################
--------------------------------
Step 0. Standby DB Configuration
--------------------------------
[root@DBSTAND ~]# uname --kernel-name --kernel-release --kernel-version
Linux 3.8.13-16.2.1.el6uek.x86_64 #1 SMP Thu Nov 7 17:01:44 PST 2013
[root@DBSTAND ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
-------------------------------------
Step 1. Install Oracle Software-Only
-------------------------------------
Note: Software has been installed on /u01
ORACLE_SID=DBSTAND
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/12cR1
-------------------------------------
Step 2. Create PFILE & CONTROL FILE
-------------------------------------
--On Primary
[oracle@DBPROD dbs]$ scp /tmp/control01.ctl oracle@DBSTAND.lab:/u03/oradata/datafiles/control01.ctl
Note: Create PFILE from SPFILE on PROD and Copy over to Standby and modify it
SQL> CREATE PFILE FROM SPFILE;
File created.
[oracle@DBPROD dbs]$ scp initDBPROD.ora oracle@DBSTAND.lab:/u01/app/oracle/12cR1/dbs/initDBSTAND.ora
oracle@DBSTAND.lab's password:
initDBPROD.ora 100% 940 0.9KB/s 00:00
[oracle@DBPROD dbs]$ rm initDBPROD.ora
--On Standby
Modified initDBSTAND.ora
[oracle@DBSTAND dbs]$ cat initDBSTAND.ora
*.compatible='12.1.0.0.0'
*.control_files='/u03/oradata/datafiles/control01.ctl','/u03/oradata/datafiles/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='DBPROD'
*.db_unique_name='DBSTAND'
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=true
*.fal_server='DBPROD'
*.log_archive_config='DG_CONFIG=(DBPROD,DBSTAND)'
*.log_archive_dest_1='LOCATION=/u05/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DBSTAND'
*.log_archive_dest_2='SERVICE=DBPROD VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBPROD'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=3
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
-------------------------------------
Step 3. Startup Standby
-------------------------------------
[oracle@DBSTAND dbs]$ echo $ORACLE_SID
DBSTAND
[oracle@DBSTAND dbs]$ echo $ORACLE_HOME
/u01/app/oracle/12cR1
[oracle@DBSTAND dbs]$ . oraenv
ORACLE_SID = [DBSTAND] ? DBSTAND
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/12cR1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBSTAND dbs]$ sqlplus / as sysdba;
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2286656 bytes
Variable Size 159386560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5152768 bytes
SQL> CREATE SPFILE FROM PFILE;
File created.
SQL> !rm $ORACLE_HOME/dbs/initDBSTAND.ora
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2286656 bytes
Variable Size 159386560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5152768 bytes
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/12cR1/dbs/spfi
leDBSTAND.ora
--------------------------------------------
Step 4. Modify LISTENER & TNSNAMES.ORA Files
--------------------------------------------
Note: Make sure tnsping is working and listener is up
[oracle@DBSTAND ~]$ tnsping DBPROD
[oracle@DBPROD ~]$ tnsping DBSTAND
-------------------------------------
Step 5. Create directory structure
-------------------------------------
Note: Make sure the directory structure is same as PROD(if not, use LOG/DB_FILE_NAME_CONVERT)
--On Primary run this to get all the DATAFILES location
RMAN> report schema;
-----------------------------------------
Step 6. Copy Backup files
-----------------------------------------
--backup files
SQL> !scp -r oracle@DBPROD:/u02/rman/DBPROD/* oracle@DBSTAND:/u02/rman/DBPROD/
Note: I have created same directory structure in standby for backup as it was in primary so RMAN know the path
-----------------------------------------
Step 7. Create Password File
-----------------------------------------
Copy pwd file from DBPROD to DBSTAND and rename it
-----------------------------------------
Step 8. Restore Standby DB from RMAN bkup
-----------------------------------------
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2286656 bytes
Variable Size 159386560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5152768 bytes
SQL> alter database mount;
Database altered.
[oracle@DBSTAND dbs]$ rman target /
connected to target database: DBPROD (not mounted)
RMAN> crosscheck backup;
RMAN> restore database;
channel ORA_DISK_1: piece handle=/u02/rman/DBPROD/DBPROD_bkup_850204537_31_1.bak tag=TAG20140614T075412
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 14-JUN-14
-----------------------------------------
Step 9. Add Standby logs
-----------------------------------------
--Standby
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo01.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo02.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo03.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo04.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/u03/oradata/redo/standdb_redo05.log') SIZE 50M;
Database altered.
-----------------------------------------
Step 9. Add standby logs Production
-----------------------------------------
--Production
SQL> ALTER DATABASe ADD STANDBY LOGFILE ('/u03/oradata/redo/standby_redo01.log') SIZE 50M;
SQL> ALTER DATABASe ADD STANDBY LOGFILE '/u03/oradata/redo/standby_redo02.log')SIZE 50M;
SQL> ALTER DATABASe ADD STANDBY LOGFILE ('/u03/oradata/redo/standby_redo03.log') SIZE 50M;
SQL> ALTER DATABASe ADD STANDBY LOGFILE ('/u03/oradata/redo/standby_redo04.log') SIZE 50M;
SQL> ALTER DATABASe ADD STANDBY LOGFILE ('/u03/oradata/redo/standby_redo05.log') SIZE 50M;
Database altered.
---------------------------------------
Step 10. Start Apply Process - Standby
---------------------------------------
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL>ALTER DATABASE OPEN READ ONLY;
SQL>SELECT OPEN_MODE FROM V$DATABASE;
OPEN_MODE
--------
READ ONLY
------------------
VERIFICATION
------------------
--PRIMARY
SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
THREAD# Last Primary Seq Generated
---------- --------------------------
1 36
--STANDBY
SQL> select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY')
group by thread# order by 1;
THREAD# Last Standby Seq Applied
---------- ------------------------
1 36
SQL> SELECT NAME,DATABASE_ROLE,OPEN_MODE FROM V$DATABASE;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
DBPROD PHYSICAL STANDBY MOUNTED
SQL> SELECT NAME,DATABASE_ROLE,OPEN_MODE FROM V$DATABASE;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
DBPROD PRIMARY READ WRITE
Enjoy.....Abid Malik
No comments:
Post a Comment