How to Create Physical Standby Database using RMAN Backup Without Using Duplicate Command
Contents
___________________________________________________________________________________________________
0. Introduction
1. Environment
On Primary (Step 2 to Step 12)
2. Enable Forced Logging on Primary
3. Check Password File on Primary
4. Configure a Standby Redo Log on Primary
5. Verify Archive Mode Enabled on Primary
6. Set Primary Database Initialization Parameters
7. Backup Primary Database for configure Standby
8. Transfer PASSWORD FILE TO STANDBY SIDE
9. Transfer Backup from Primary to Standby
10. Transfer pfile from primary to standby
11. Configure TNS for Primary
12. Verify connection ‘AS SYSDBA’ from Primary
On STANDBY (Step 13 to Step 24)
13. Configure TNS for STANDBY
14. Configure init parameter for STANDBY
15. Add oratab entry
16. Create required directories
17. Startup Nomount
18. Verify connection ‘AS SYSDBA’ from Standby
19. Restore the Standby Controlfile
20. Mount standby database
21. Catalog all the backup pieces
22. Restore and Recover Database
23. Verify Standby redo logs
24. Enable MRP on STANDBY
25. Verify Sync
26. Verify ..Lets Test
________________________________________________________________________________________________
PLEASE NOTE in 12c Data Guard is set up at the Container level and not the individual Pluggable database level as the redo log files only belong to the Container database and the individual pluggable databases do not have their own online redo log files. Definition of Active Dataguard: Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., while continuously applying changes received from the production/primary database. Goal : How to Create Physical Standby Database using RMAN Backup Without Using Duplicate Command
Source: Platform : Linuxx86_64 Server Name : RAC1.RAJASEKHAR.COM, IP: 192.168.2.101 DB Version : Oracle 12.2.0.1 File system : Normal Database Name : UOIN1CON DB_UNIQUE_NAME : UOIN1CON Flashback : Disabled Oracle Home Path: /u01/app/oracle/product/12.2.0.1 Target: Platform : Linuxx86_64 Server Name : RAC2.RAJASEKHAR.COM, IP: 192.168.2.102 DB Version : Oracle 12.2.0.1 File system : Normal Database Name : UOIN1CON DB_UNIQUE_NAME : UOIN1CON_DG Flashback : Disabled Oracle Home Path: /u01/app/oracle/product/12.2.0.1
On Primary (Step 2 to Step 12)
2. Enable Forced Logging on Primary
SQL> select name, open_mode,cdb from v$database; NAME OPEN_MODE CDB --------- -------------------- --- UOIN1CON READ WRITE NO SQL> select force_logging from v$database; FORCE_LOGGING --------------------------------------- NO SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> select force_logging from v$database; FORCE_LOGGING --------------------------------------- YES <---- SQL>
3. Check Password File on Primary
[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/12.2.0.1/dbs
[oracle@rac1 dbs]$ ls -ltr orapwUOIN1CON
-rw-r-----. 1 oracle dba 3584 Dec 14 12:26 orapwUOIN1CON
[oracle@rac1 dbs]$
4. Configure a Standby Redo Log on Primary
-- Since we have 3 online redo log file groups, we need to create 4(3+1) Standby redo log file groups -- Standy Redo logs files come into picture only when protection mode is Maximum Availability and Maximum Protection. SQL> set lines 180 SQL> col MEMBER for a60 SQL> select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#; THREAD# GROUP# MEMBER BYTES ---------- ---------- ------------------------------------------------------------ ---------- 1 3 /u02/oracle/oradata/UOIN1CON/redo03.log 209715200 1 2 /u02/oracle/oradata/UOIN1CON/redo02.log 209715200 1 1 /u02/oracle/oradata/UOIN1CON/redo01.log 209715200 SQL> SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u02/oracle/oradata/UOIN1CON/redo04.log') SIZE 200M; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u02/oracle/oradata/UOIN1CON/redo05.log') SIZE 200M; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u02/oracle/oradata/UOIN1CON/redo06.log') SIZE 200M; Database altered. SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u02/oracle/oradata/UOIN1CON/redo07.log') SIZE 200M; Database altered. SQL> SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ CON_ID ---------- ------- ------- ------------------------------------------------------------ --- ---------- 3 ONLINE /u02/oracle/oradata/UOIN1CON/redo03.log NO 0 2 ONLINE /u02/oracle/oradata/UOIN1CON/redo02.log NO 0 1 ONLINE /u02/oracle/oradata/UOIN1CON/redo01.log NO 0 4 STANDBY /u02/oracle/oradata/UOIN1CON/redo04.log NO 0 5 STANDBY /u02/oracle/oradata/UOIN1CON/redo05.log NO 0 6 STANDBY /u02/oracle/oradata/UOIN1CON/redo06.log NO 0 7 STANDBY /u02/oracle/oradata/UOIN1CON/redo07.log NO 0 7 rows selected. SQL> SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#; GROUP# MEMBER BYTES ---------- ------------------------------------------------------------ ---------- 4 /u02/oracle/oradata/UOIN1CON/redo04.log 209715200 5 /u02/oracle/oradata/UOIN1CON/redo05.log 209715200 6 /u02/oracle/oradata/UOIN1CON/redo06.log 209715200 7 /u02/oracle/oradata/UOIN1CON/redo07.log 209715200 SQL>
5. Verify Archive Mode Enabled on Primary
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/oracle/archive/UOIN1CON
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL>
6. Set Primary Database Initialization Parameters
SQL> alter system set db_unique_name='UOIN1CON' scope=spfile; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(UOIN1CON,UOIN1CON_DG)' scope=both; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u02/oracle/archive/UOIN1CON VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=UOIN1CON' scope=both; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=UOIN1CON_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=UOIN1CON_DG' scope=both; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both; System altered. SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET fal_client=UOIN1CON scope=both; System altered. SQL> Please note: The FAL_CLIENT database initialization parameter is no longer required from 11gR2 SQL> ALTER SYSTEM SET fal_server=UOIN1CON_DG scope=both; System altered. SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u02/oracle/oradata/UOIN1CON_DG','/u02/oracle/oradata/UOIN1CON' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u02/oracle/oradata/UOIN1CON_DG','/u02/oracle/oradata/UOIN1CON' SCOPE=SPFILE; System altered. SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; System altered. SQL> SQL> create pfile='/home/oracle/initUOIN1CON_after.ora' from spfile; File created. SQL> SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@rac1 ~]$ cat /home/oracle/initUOIN1CON_after.ora UOIN1CON.__data_transfer_cache_size=0 UOIN1CON.__db_cache_size=369098752 UOIN1CON.__inmemory_ext_roarea=0 UOIN1CON.__inmemory_ext_rwarea=0 UOIN1CON.__java_pool_size=16777216 UOIN1CON.__large_pool_size=33554432 UOIN1CON.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment UOIN1CON.__pga_aggregate_target=587202560 UOIN1CON.__sga_target=687865856 UOIN1CON.__shared_io_pool_size=33554432 UOIN1CON.__shared_pool_size=218103808 UOIN1CON.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/UOIN1CON/adump' *.audit_trail='db' *.compatible='12.2.0' *.control_files='/u02/oracle/oradata/UOIN1CON/control01.ctl','/u02/oracle/oradata/UOIN1CON/control02.ctl' *.db_block_size=8192 *.db_file_name_convert='/u02/oracle/oradata/UOIN1CON_DG','/u02/oracle/oradata/UOIN1CON' *.db_name='UOIN1CON' *.db_unique_name='UOIN1CON' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=UOIN1CONXDB)' *.fal_client='UOIN1CON' *.fal_server='UOIN1CON_DG' *.log_archive_config='DG_CONFIG=(UOIN1CON,UOIN1CON_DG)' *.log_archive_dest_1='LOCATION=/u02/oracle/archive/UOIN1CON VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=UOIN1CON' *.log_archive_dest_2='SERVICE=UOIN1CON_DG LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=UOIN1CON_DG' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=30 *.log_file_name_convert='/u02/oracle/oradata/UOIN1CON_DG','/u02/oracle/oradata/UOIN1CON' *.memory_target=1201m *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' [oracle@rac1 ~]$
7. Backup Primary Database for configure Standby
[oracle@rac1 ~]$ mkdir -p /u02/oracle/backup/UOIN1CON [oracle@rac1 ~]$ mkdir -p /u02/oracle/backup/UOIN1CON [oracle@rac1 ~]$ [oracle@rac1 ~]$ cd /u02/oracle/backup/UOIN1CON [oracle@rac1 UOIN1CON]$ vi rmanbackup.sh [oracle@rac1 UOIN1CON]$ chmod 775 rmanbackup.sh [oracle@rac1 UOIN1CON]$ [oracle@rac1 UOIN1CON]$ cat rmanbackup.sh ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/12.2.0.1 BACKUP_LOG_PATH=/u02/oracle/backup/UOIN1CON export ORACLE_BASE export ORACLE_HOME export ORACLE_SID=UOIN1CON export BACKUP_LOG_PATH LOG_FILE=${BACKUP_LOG_PATH}/backup_db.log $ORACLE_HOME/bin/rman msglog=${LOG_FILE} << EOF connect target / run { allocate channel t1 type disk; allocate channel t2 type disk; allocate channel t3 type disk; backup database format '/u02/oracle/backup/UOIN1CON/database_%d_%u_%s'; release channel t1; release channel t2; release channel t3; } sql 'alter system archive log current'; run { allocate channel a1 type disk; allocate channel a2 type disk; allocate channel a3 type disk; backup archivelog all format '/u02/oracle/backup/UOIN1CON/arch_%d_%u_%s'; release channel a1; release channel a2; release channel a3; } run { allocate channel c1 type disk; backup current controlfile for standby format '/u02/oracle/backup/UOIN1CON/Control_%d_%u_%s'; release channel c1; } exit; EOF [oracle@rac1 UOIN1CON]$ [oracle@rac1 UOIN1CON]$ ls -ltr total 4 -rwxrwxr-x. 1 oracle dba 982 Jan 3 16:44 rmanbackup.sh [oracle@rac1 UOIN1CON]$ [oracle@rac1 UOIN1CON]$ nohup ./rmanbackup.sh & [1] 6460 [oracle@rac1 UOIN1CON]$ nohup: ignoring input and appending output to `nohup.out' [oracle@rac1 UOIN1CON]$ jobs -l [1]+ 6460 Running nohup ./rmanbackup.sh & <----- [oracle@rac1 UOIN1CON]$ [oracle@rac1 UOIN1CON]$ ls -ltr total 1589364 -rwxrwxr-x. 1 oracle dba 982 Jan 3 16:44 rmanbackup.sh -rw-r-----. 1 oracle dba 3964928 Jan 3 16:45 database_UOIN1CON_05tmdo64_5 -rw-r-----. 1 oracle dba 395157504 Jan 3 16:46 database_UOIN1CON_04tmdo64_4 -rw-r-----. 1 oracle dba 723779584 Jan 3 16:46 database_UOIN1CON_03tmdo64_3 -rw-r-----. 1 oracle dba 106033664 Jan 3 16:46 arch_UOIN1CON_09tmdo7a_9 -rw-r-----. 1 oracle dba 165638144 Jan 3 16:46 arch_UOIN1CON_08tmdo7a_8 -rw-r-----. 1 oracle dba 221474304 Jan 3 16:46 arch_UOIN1CON_07tmdo7a_7 -rw-r-----. 1 oracle dba 761344 Jan 3 16:46 arch_UOIN1CON_0atmdo7j_10 -rw-r-----. 1 oracle dba 10665984 Jan 3 16:46 Control_UOIN1CON_0ctmdo7o_12 -rw-------. 1 oracle dba 96 Jan 3 16:46 nohup.out -rw-r--r--. 1 oracle dba 5507 Jan 3 16:46 backup_db.log [oracle@rac1 UOIN1CON]$ [oracle@rac1 UOIN1CON]$ cat backup_db.log Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jan 3 16:45:53 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. RMAN> connected to target database: UOIN1CON (DBID=1821803680) RMAN> 2> 3> 4> 5> 6> 7> 8> 9> using target database control file instead of recovery catalog allocated channel: t1 channel t1: SID=75 device type=DISK allocated channel: t2 channel t2: SID=96 device type=DISK allocated channel: t3 channel t3: SID=97 device type=DISK Starting backup at 03-JAN-19 channel t1: starting full datafile backup set channel t1: specifying datafile(s) in backup set input datafile file number=00001 name=/u02/oracle/oradata/UOIN1CON/system01.dbf channel t1: starting piece 1 at 03-JAN-19 channel t2: starting full datafile backup set channel t2: specifying datafile(s) in backup set input datafile file number=00003 name=/u02/oracle/oradata/UOIN1CON/sysaux01.dbf input datafile file number=00007 name=/u02/oracle/oradata/UOIN1CON/users01.dbf channel t2: starting piece 1 at 03-JAN-19 channel t3: starting full datafile backup set channel t3: specifying datafile(s) in backup set input datafile file number=00005 name=/u02/oracle/oradata/UOIN1CON/oggdata01.dbf input datafile file number=00004 name=/u02/oracle/oradata/UOIN1CON/undotbs01.dbf channel t3: starting piece 1 at 03-JAN-19 channel t3: finished piece 1 at 03-JAN-19 piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_05tmdo64_5 tag=TAG20190103T164555 comment=NONE channel t3: backup set complete, elapsed time: 00:00:01 channel t2: finished piece 1 at 03-JAN-19 piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_04tmdo64_4 tag=TAG20190103T164555 comment=NONE channel t2: backup set complete, elapsed time: 00:00:25 channel t1: finished piece 1 at 03-JAN-19 piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_03tmdo64_3 tag=TAG20190103T164555 comment=NONE channel t1: backup set complete, elapsed time: 00:00:35 Finished backup at 03-JAN-19 Starting Control File and SPFILE Autobackup at 03-JAN-19 piece handle=/u01/app/oracle/product/12.2.0.1/dbs/c-1821803680-20190103-01 comment=NONE Finished Control File and SPFILE Autobackup at 03-JAN-19 released channel: t1 released channel: t2 released channel: t3 RMAN> sql statement: alter system archive log current RMAN> 2> 3> 4> 5> 6> 7> 8> 9> allocated channel: a1 channel a1: SID=75 device type=DISK allocated channel: a2 channel a2: SID=96 device type=DISK allocated channel: a3 channel a3: SID=97 device type=DISK Starting backup at 03-JAN-19 current log archived channel a1: starting archived log backup set channel a1: specifying archived log(s) in backup set input archived log thread=1 sequence=2 RECID=2 STAMP=994879681 input archived log thread=1 sequence=3 RECID=3 STAMP=994928354 channel a1: starting piece 1 at 03-JAN-19 channel a2: starting archived log backup set channel a2: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=1 STAMP=994859424 channel a2: starting piece 1 at 03-JAN-19 channel a3: starting archived log backup set channel a3: specifying archived log(s) in backup set input archived log thread=1 sequence=4 RECID=4 STAMP=996595104 input archived log thread=1 sequence=5 RECID=5 STAMP=996596970 input archived log thread=1 sequence=6 RECID=6 STAMP=996596978 channel a3: starting piece 1 at 03-JAN-19 channel a2: finished piece 1 at 03-JAN-19 piece handle=/u02/oracle/backup/UOIN1CON/arch_UOIN1CON_08tmdo7a_8 tag=TAG20190103T164634 comment=NONE channel a2: backup set complete, elapsed time: 00:00:07 channel a2: starting archived log backup set channel a2: specifying archived log(s) in backup set input archived log thread=1 sequence=7 RECID=7 STAMP=996597993 input archived log thread=1 sequence=8 RECID=8 STAMP=996597994 channel a2: starting piece 1 at 03-JAN-19 channel a3: finished piece 1 at 03-JAN-19 piece handle=/u02/oracle/backup/UOIN1CON/arch_UOIN1CON_09tmdo7a_9 tag=TAG20190103T164634 comment=NONE channel a3: backup set complete, elapsed time: 00:00:09 channel a1: finished piece 1 at 03-JAN-19 piece handle=/u02/oracle/backup/UOIN1CON/arch_UOIN1CON_07tmdo7a_7 tag=TAG20190103T164634 comment=NONE channel a1: backup set complete, elapsed time: 00:00:10 channel a2: finished piece 1 at 03-JAN-19 piece handle=/u02/oracle/backup/UOIN1CON/arch_UOIN1CON_0atmdo7j_10 tag=TAG20190103T164634 comment=NONE channel a2: backup set complete, elapsed time: 00:00:01 Finished backup at 03-JAN-19 Starting Control File and SPFILE Autobackup at 03-JAN-19 piece handle=/u01/app/oracle/product/12.2.0.1/dbs/c-1821803680-20190103-02 comment=NONE Finished Control File and SPFILE Autobackup at 03-JAN-19 released channel: a1 released channel: a2 released channel: a3 RMAN> 2> 3> 4> 5> allocated channel: c1 channel c1: SID=75 device type=DISK Starting backup at 03-JAN-19 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set including standby control file in backup set channel c1: starting piece 1 at 03-JAN-19 channel c1: finished piece 1 at 03-JAN-19 piece handle=/u02/oracle/backup/UOIN1CON/Control_UOIN1CON_0ctmdo7o_12 tag=TAG20190103T164648 comment=NONE channel c1: backup set complete, elapsed time: 00:00:01 Finished backup at 03-JAN-19 Starting Control File and SPFILE Autobackup at 03-JAN-19 piece handle=/u01/app/oracle/product/12.2.0.1/dbs/c-1821803680-20190103-03 comment=NONE Finished Control File and SPFILE Autobackup at 03-JAN-19 released channel: c1 RMAN> Recovery Manager complete. [oracle@rac1 UOIN1CON]$
8. Transfer PASSWORD FILE TO STANDBY SIDE
-- Copy the password file from the primary to standby $ORACLE_HOME/dbs and rename it to the standby database name. -- The username is required to be SYS and the password needs to be the same on the Primary and Standby. -- The best practice for this is to copy the passwordfile as suggested. -- The password file name must match the instance name/SID used at the standby site, not the DB_NAME. [oracle@rac1 UOIN1CON]$ cd /u01/app/oracle/product/12.2.0.1/dbs/ [oracle@rac1 dbs]$ ls -ltr orapwUOIN1CON -rw-r-----. 1 oracle dba 3584 Dec 14 12:26 orapwUOIN1CON [oracle@rac1 dbs]$ [oracle@rac1 dbs]$ scp orapwUOIN1CON oracle@rac2:/u01/app/oracle/product/12.2.0.1/dbs/orapwUOIN1CON_DG oracle@rac2's password: orapwUOIN1CON 100% 3584 3.5KB/s 00:00 [oracle@rac1 dbs]$
9. Transfer Backup from Primary to Standby
[oracle@rac1 ~]$ cd /u02/oracle/backup/UOIN1CON/ [oracle@rac1 UOIN1CON]$ ls -ltr total 1589364 -rwxrwxr-x. 1 oracle dba 982 Jan 3 16:44 rmanbackup.sh -rw-r-----. 1 oracle dba 3964928 Jan 3 16:45 database_UOIN1CON_05tmdo64_5 -rw-r-----. 1 oracle dba 395157504 Jan 3 16:46 database_UOIN1CON_04tmdo64_4 -rw-r-----. 1 oracle dba 723779584 Jan 3 16:46 database_UOIN1CON_03tmdo64_3 -rw-r-----. 1 oracle dba 106033664 Jan 3 16:46 arch_UOIN1CON_09tmdo7a_9 -rw-r-----. 1 oracle dba 165638144 Jan 3 16:46 arch_UOIN1CON_08tmdo7a_8 -rw-r-----. 1 oracle dba 221474304 Jan 3 16:46 arch_UOIN1CON_07tmdo7a_7 -rw-r-----. 1 oracle dba 761344 Jan 3 16:46 arch_UOIN1CON_0atmdo7j_10 -rw-r-----. 1 oracle dba 10665984 Jan 3 16:46 Control_UOIN1CON_0ctmdo7o_12 -rw-------. 1 oracle dba 96 Jan 3 16:46 nohup.out -rw-r--r--. 1 oracle dba 5507 Jan 3 16:46 backup_db.log [oracle@rac1 UOIN1CON]$ [oracle@rac1 UOIN1CON]$ scp database_UOIN1CON* oracle@rac2:/u02/oracle/backup/UOIN1CON/ oracle@rac2's password: database_UOIN1CON_03tmdo64_3 100% 690MB 43.1MB/s 00:16 database_UOIN1CON_04tmdo64_4 100% 377MB 26.9MB/s 00:14 database_UOIN1CON_05tmdo64_5 100% 3872KB 3.8MB/s 00:00 [oracle@rac1 UOIN1CON]$ [oracle@rac1 UOIN1CON]$ scp Control_UOIN1CON* oracle@rac2:/u02/oracle/backup/UOIN1CON/ oracle@rac2's password: Control_UOIN1CON_0ctmdo7o_12 100% 10MB 10.2MB/s 00:00 [oracle@rac1 UOIN1CON]$ [oracle@rac1 UOIN1CON]$ scp arch_UOIN1CON* oracle@rac2:/u02/oracle/backup/UOIN1CON/ oracle@rac2's password: arch_UOIN1CON_07tmdo7a_7 100% 211MB 70.4MB/s 00:03 arch_UOIN1CON_08tmdo7a_8 100% 158MB 52.7MB/s 00:03 arch_UOIN1CON_09tmdo7a_9 100% 101MB 25.3MB/s 00:04 arch_UOIN1CON_0atmdo7j_10 100% 744KB 743.5KB/s 00:00 [oracle@rac1 UOIN1CON]$
10. Transfer pfile from primary to standby
[oracle@rac1 ~]$ scp initUOIN1CON_after.ora oracle@rac2:/u02/oracle/backup/UOIN1CON/
oracle@rac2's password:
initUOIN1CON_after.ora 100% 1780 1.7KB/s 00:00
[oracle@rac1 ~]$
[oracle@rac1 admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER_12C = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = UOIN1CON) (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1) (SID_NAME = UOIN1CON) ) ) LISTENER_12C = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1621)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621)) ) ) ADR_BASE_LISTENER_12C = /u01/app/oracle [oracle@rac1 admin]$ [oracle@rac1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. UOIN1CON_DG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621)) ) (CONNECT_DATA = (SERVICE_NAME = UOIN1CON_DG) ) ) UOIN1CON = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = UOIN1CON) ) ) LISTENER_12C = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621)) [oracle@rac1 admin]$ [oracle@rac1 admin]$ lsnrctl status LISTENER_12C LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 03-JAN-2019 18:19:39 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.rajasekhar.com)(PORT=1621))) STATUS of the LISTENER ------------------------ Alias LISTENER_12C Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 03-JAN-2019 17:18:26 Uptime 0 days 1 hr. 1 min. 13 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener_12c/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1621))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621))) Services Summary... Service "UOIN1CON" has 1 instance(s). Instance "UOIN1CON", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@rac1 admin]$
12. Verify connection ‘AS SYSDBA’ from Primary
[oracle@rac1 ~]$ sqlplus SYS/SYS@UOIN1CON AS SYSDBA
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 3 18:28:47 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
On STANDBY (Step 13 to Step 24)
[oracle@rac2 admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER_12C = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = UOIN1CON_DG) (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1) (SID_NAME = UOIN1CON_DG) ) ) LISTENER_12C = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1621)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1621)) ) ) ADR_BASE_LISTENER_12C = /u01/app/oracle [oracle@rac2 admin]$ [oracle@rac2 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. UOIN1CON_DG = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621)) ) (CONNECT_DATA = (SERVICE_NAME = UOIN1CON_DG) ) ) UOIN1CON = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621)) ) (CONNECT_DATA = (SERVICE_NAME = UOIN1CON) ) ) LISTENER_12C = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1621)) [oracle@rac2 admin]$ [oracle@rac2 admin]$ lsnrctl status LISTENER_12C LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 03-JAN-2019 18:14:00 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac2.rajasekhar.com)(PORT=1621))) STATUS of the LISTENER ------------------------ Alias LISTENER_12C Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 03-JAN-2019 18:01:29 Uptime 0 days 0 hr. 12 min. 30 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.2.0.1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/rac2/listener_12c/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1621))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1621))) Services Summary... Service "UOIN1CON_DG" has 1 instance(s). Instance "UOIN1CON_DG", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@rac2 admin]$
14. Configure init parameter for STANDBY
[oracle@rac2 UOIN1CON]$ cat initUOIN1CON_DG.ora UOIN1CON_DG.__data_transfer_cache_size=0 UOIN1CON_DG.__db_cache_size=369098752 UOIN1CON_DG.__inmemory_ext_roarea=0 UOIN1CON_DG.__inmemory_ext_rwarea=0 UOIN1CON_DG.__java_pool_size=16777216 UOIN1CON_DG.__large_pool_size=33554432 UOIN1CON_DG.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment UOIN1CON_DG.__pga_aggregate_target=587202560 UOIN1CON_DG.__sga_target=687865856 UOIN1CON_DG.__shared_io_pool_size=33554432 UOIN1CON_DG.__shared_pool_size=218103808 UOIN1CON_DG.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/UOIN1CON_DG/adump' *.audit_trail='db' *.compatible='12.2.0' *.control_files='/u02/oracle/oradata/UOIN1CON_DG/control01.ctl','/u02/oracle/oradata/UOIN1CON_DG/control02.ctl' *.db_block_size=8192 *.db_file_name_convert='/u02/oracle/oradata/UOIN1CON','/u02/oracle/oradata/UOIN1CON_DG' *.db_name='UOIN1CON' *.db_unique_name='UOIN1CON_DG' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=UOIN1CON_DGXDB)' *.fal_client='UOIN1CON_DG' *.fal_server='UOIN1CON' *.log_archive_config='DG_CONFIG=(UOIN1CON,UOIN1CON_DG)' *.log_archive_dest_1='LOCATION=/u02/oracle/archive/UOIN1CON_DG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=UOIN1CON_DG' *.log_archive_dest_2='SERVICE=UOIN1CON LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=UOIN1CON' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=30 *.log_file_name_convert='/u02/oracle/oradata/UOIN1CON','/u02/oracle/oradata/UOIN1CON_DG' *.memory_target=1201m *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.processes=300 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' [oracle@rac2 UOIN1CON]$
[oracle@rac2 ~]$ cat /etc/oratab | grep -i UOIN1CON_DG
UOIN1CON_DG:/u01/app/oracle/product/12.2.0.1:N
[oracle@rac2 ~]$
16. Create required directories
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/UOIN1CON_DG/adump [oracle@rac2 ~]$ mkdir -p /u02/oracle/oradata/UOIN1CON_DG [oracle@rac2 ~]$ mkdir -p /u02/oracle/archive/UOIN1CON_DG [oracle@rac2 ~]$
[oracle@rac2 ~]$ . oraenv ORACLE_SID = [UOIN1CON_DG] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@rac2 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 3 17:55:32 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/u02/oracle/backup/UOIN1CON/initUOIN1CON_DG.ora'; ORACLE instance started. Total System Global Area 1275068416 bytes Fixed Size 8620272 bytes Variable Size 939525904 bytes Database Buffers 318767104 bytes Redo Buffers 8155136 bytes SQL> SQL> create spfile from pfile='/u02/oracle/backup/UOIN1CON/initUOIN1CON_DG.ora'; File created. SQL> shut immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> SQL> startup nomount; ORACLE instance started. Total System Global Area 1275068416 bytes Fixed Size 8620272 bytes Variable Size 939525904 bytes Database Buffers 318767104 bytes Redo Buffers 8155136 bytes SQL>
18. Verify connection ‘AS SYSDBA’ from Standby
[oracle@rac2 ~]$ sqlplus SYS/SYS@UOIN1CON_DG AS SYSDBA
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 3 18:30:01 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
19. Restore the Standby Controlfile
[oracle@rac2 ~]$ cd /u02/oracle/backup/UOIN1CON/ [oracle@rac2 UOIN1CON]$ ls -ltr total 1589356 -rw-r-----. 1 oracle dba 723779584 Jan 3 17:02 database_UOIN1CON_03tmdo64_3 -rw-r-----. 1 oracle dba 395157504 Jan 3 17:02 database_UOIN1CON_04tmdo64_4 -rw-r-----. 1 oracle dba 3964928 Jan 3 17:02 database_UOIN1CON_05tmdo64_5 -rw-r-----. 1 oracle dba 10665984 Jan 3 17:02 Control_UOIN1CON_0ctmdo7o_12 -rw-r-----. 1 oracle dba 221474304 Jan 3 17:03 arch_UOIN1CON_07tmdo7a_7 -rw-r-----. 1 oracle dba 165638144 Jan 3 17:03 arch_UOIN1CON_08tmdo7a_8 -rw-r-----. 1 oracle dba 106033664 Jan 3 17:03 arch_UOIN1CON_09tmdo7a_9 -rw-r-----. 1 oracle dba 761344 Jan 3 17:03 arch_UOIN1CON_0atmdo7j_10 -rw-r--r--. 1 oracle dba 1780 Jan 3 17:06 initUOIN1CON_after.ora -rw-r--r--. 1 oracle dba 1756 Jan 3 17:54 initUOIN1CON_DG.ora [oracle@rac2 UOIN1CON]$ [oracle@rac2 UOIN1CON]$ [oracle@rac2 UOIN1CON]$ . oraenv ORACLE_SID = [UOIN1CON_DG] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@rac2 UOIN1CON]$ [oracle@rac2 UOIN1CON]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jan 3 18:36:19 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: UOIN1CON (not mounted) RMAN> restore standby controlfile from '/u02/oracle/backup/UOIN1CON/Control_UOIN1CON_0ctmdo7o_12'; Starting restore at 03-JAN-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=28 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u02/oracle/oradata/UOIN1CON_DG/control01.ctl output file name=/u02/oracle/oradata/UOIN1CON_DG/control02.ctl Finished restore at 03-JAN-19 RMAN>
RMAN> sql 'alter database mount standby database';
sql statement: alter database mount standby database
released channel: ORA_DISK_1
RMAN>
21. Catalog all the backup pieces
RMAN> catalog start with '/u02/oracle/backup/UOIN1CON';
searching for all files that match the pattern /u02/oracle/backup/UOIN1CON
List of Files Unknown to the Database
=====================================
File Name: /u02/oracle/backup/UOIN1CON/initUOIN1CON_DG.ora
File Name: /u02/oracle/backup/UOIN1CON/Control_UOIN1CON_0ctmdo7o_12
File Name: /u02/oracle/backup/UOIN1CON/initUOIN1CON_after.ora
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u02/oracle/backup/UOIN1CON/Control_UOIN1CON_0ctmdo7o_12
List of Files Which Were Not Cataloged
=======================================
File Name: /u02/oracle/backup/UOIN1CON/initUOIN1CON_DG.ora
RMAN-07517: Reason: The file header is corrupted
File Name: /u02/oracle/backup/UOIN1CON/initUOIN1CON_after.ora
RMAN-07517: Reason: The file header is corrupted
RMAN>
22. Restore and Recover Database
RMAN> list backup of archivelog all; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 7 101.12M DISK 00:00:04 03-JAN-19 BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20190103T164634 Piece Name: /u02/oracle/backup/UOIN1CON/arch_UOIN1CON_09tmdo7a_9 List of Archived Logs in backup set 7 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 4 1542199 15-DEC-18 1659977 03-JAN-19 1 5 1659977 03-JAN-19 1669907 03-JAN-19 1 6 1669907 03-JAN-19 1669928 03-JAN-19 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 8 157.96M DISK 00:00:06 03-JAN-19 BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20190103T164634 Piece Name: /u02/oracle/backup/UOIN1CON/arch_UOIN1CON_08tmdo7a_8 List of Archived Logs in backup set 8 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 1 1415033 14-DEC-18 1432129 14-DEC-18 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 9 211.21M DISK 00:00:07 03-JAN-19 BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20190103T164634 Piece Name: /u02/oracle/backup/UOIN1CON/arch_UOIN1CON_07tmdo7a_7 List of Archived Logs in backup set 9 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 2 1432129 14-DEC-18 1486417 14-DEC-18 1 3 1486417 14-DEC-18 1542199 15-DEC-18 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 10 743.00K DISK 00:00:00 03-JAN-19 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20190103T164634 Piece Name: /u02/oracle/backup/UOIN1CON/arch_UOIN1CON_0atmdo7j_10 List of Archived Logs in backup set 10 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 7 1669928 03-JAN-19 1672222 03-JAN-19 1 8 1672222 03-JAN-19 1672247 03-JAN-19 <--- 8+1 =9 RMAN> [oracle@rac2 UOIN1CON]$ cat restore_db.sh ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/12.2.0.1 BACKUP_LOG_PATH=/u02/oracle/backup/UOIN1CON export ORACLE_BASE export ORACLE_HOME export ORACLE_SID=UOIN1CON_DG export BACKUP_LOG_PATH LOG_FILE=${BACKUP_LOG_PATH}/restore_db.log /u01/app/oracle/product/12.2.0.1/bin/rman msglog=${LOG_FILE} << EOF connect target / run { allocate channel t1 type disk; allocate channel t2 type disk; allocate channel t3 type disk; set until sequence 9; <---- 8+1 restore database; recover database; } exit EOF [oracle@rac2 UOIN1CON]$ [oracle@rac2 UOIN1CON]$ chmod 775 restore_db.sh [oracle@rac2 UOIN1CON]$ [oracle@rac2 UOIN1CON]$ nohup ./restore_db.sh & [1] 13033 [oracle@rac2 UOIN1CON]$ nohup: ignoring input and appending output to `nohup.out' [oracle@rac2 UOIN1CON]$ jobs -l [1]+ 13033 Running nohup ./restore_db.sh & [oracle@rac2 UOIN1CON]$ [oracle@rac2 UOIN1CON]$ [oracle@rac2 UOIN1CON]$ cat restore_db.log Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jan 3 19:06:40 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. RMAN> connected to target database: UOIN1CON (DBID=1821803680, not open) RMAN> 2> 3> 4> 5> 6> 7> 8> using target database control file instead of recovery catalog allocated channel: t1 channel t1: SID=69 device type=DISK allocated channel: t2 channel t2: SID=64 device type=DISK allocated channel: t3 channel t3: SID=68 device type=DISK executing command: SET until clause Starting restore at 03-JAN-19 channel t1: starting datafile backup set restore channel t1: specifying datafile(s) to restore from backup set channel t1: restoring datafile 00004 to /u02/oracle/oradata/UOIN1CON_DG/undotbs01.dbf channel t1: restoring datafile 00005 to /u02/oracle/oradata/UOIN1CON_DG/oggdata01.dbf channel t1: reading from backup piece /u02/oracle/backup/UOIN1CON/database_UOIN1CON_05tmdo64_5 channel t2: starting datafile backup set restore channel t2: specifying datafile(s) to restore from backup set channel t2: restoring datafile 00003 to /u02/oracle/oradata/UOIN1CON_DG/sysaux01.dbf channel t2: restoring datafile 00007 to /u02/oracle/oradata/UOIN1CON_DG/users01.dbf channel t2: reading from backup piece /u02/oracle/backup/UOIN1CON/database_UOIN1CON_04tmdo64_4 channel t3: starting datafile backup set restore channel t3: specifying datafile(s) to restore from backup set channel t3: restoring datafile 00001 to /u02/oracle/oradata/UOIN1CON_DG/system01.dbf channel t3: reading from backup piece /u02/oracle/backup/UOIN1CON/database_UOIN1CON_03tmdo64_3 channel t1: piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_05tmdo64_5 tag=TAG20190103T164555 channel t1: restored backup piece 1 channel t1: restore complete, elapsed time: 00:00:07 channel t2: piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_04tmdo64_4 tag=TAG20190103T164555 channel t2: restored backup piece 1 channel t2: restore complete, elapsed time: 00:00:40 channel t3: piece handle=/u02/oracle/backup/UOIN1CON/database_UOIN1CON_03tmdo64_3 tag=TAG20190103T164555 channel t3: restored backup piece 1 channel t3: restore complete, elapsed time: 00:00:50 Finished restore at 03-JAN-19 Starting recover at 03-JAN-19 starting media recovery channel t1: starting archived log restore to default destination channel t1: restoring archived log archived log thread=1 sequence=7 channel t1: restoring archived log archived log thread=1 sequence=8 channel t1: reading from backup piece /u02/oracle/backup/UOIN1CON/arch_UOIN1CON_0atmdo7j_10 channel t1: piece handle=/u02/oracle/backup/UOIN1CON/arch_UOIN1CON_0atmdo7j_10 tag=TAG20190103T164634 channel t1: restored backup piece 1 channel t1: restore complete, elapsed time: 00:00:01 archived log file name=/u02/oracle/archive/UOIN1CON_DG/1_7_994854306.dbf thread=1 sequence=7 archived log file name=/u02/oracle/archive/UOIN1CON_DG/1_8_994854306.dbf thread=1 sequence=8 Oracle Error: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/u02/oracle/oradata/UOIN1CON_DG/system01.dbf' media recovery complete, elapsed time: 00:00:00 Finished recover at 03-JAN-19 released channel: t1 released channel: t2 released channel: t3 RMAN> Recovery Manager complete. [oracle@rac2 UOIN1CON]$ No Worries about these errors...we can safely ignore and proceed further.
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- -------------------- ------------------------------ ---------------- -------------------- UOIN1CON MOUNTED UOIN1CON_DG PHYSICAL STANDBY MAXIMUM PERFORMANCE SQL> SQL> set lines 180 SQL> col member for a50 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ CON_ID ---------- ------- ------- -------------------------------------------------- --- ---------- 3 ONLINE /u02/oracle/oradata/UOIN1CON_DG/redo03.log NO 0 2 ONLINE /u02/oracle/oradata/UOIN1CON_DG/redo02.log NO 0 1 ONLINE /u02/oracle/oradata/UOIN1CON_DG/redo01.log NO 0 4 STANDBY /u02/oracle/oradata/UOIN1CON_DG/redo04.log NO 0 5 STANDBY /u02/oracle/oradata/UOIN1CON_DG/redo05.log NO 0 6 STANDBY /u02/oracle/oradata/UOIN1CON_DG/redo06.log NO 0 7 STANDBY /u02/oracle/oradata/UOIN1CON_DG/redo07.log NO 0 7 rows selected. SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#; GROUP# MEMBER BYTES ---------- -------------------------------------------------- ---------- 4 /u02/oracle/oradata/UOIN1CON_DG/redo04.log 209715200 5 /u02/oracle/oradata/UOIN1CON_DG/redo05.log 209715200 6 /u02/oracle/oradata/UOIN1CON_DG/redo06.log 209715200 7 /u02/oracle/oradata/UOIN1CON_DG/redo07.log 209715200 SQL>
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. SQL> / ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION * ERROR at line 1: ORA-01153: an incompatible media recovery is active <--- This means standby database configured correctly SQL>
On Primary
SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance; NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME --------- -------------------- ---------------- ---------------- UOIN1CON READ WRITE PRIMARY UOIN1CON SQL> select max(sequence#) from v$archived_log where archived='YES'; MAX(SEQUENCE#) -------------- 21 <---- SQL>
On STANDBY
SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance; NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME --------- -------------------- ---------------- ---------------- UOIN1CON MOUNTED PHYSICAL STANDBY UOIN1CON_DG SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 21 <---- SQL>
On Primary
SQL> set lines 180 SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- -------------------- ------------------------------ ---------------- -------------------- UOIN1CON READ WRITE UOIN1CON PRIMARY MAXIMUM PERFORMANCE SQL> CREATE TABLE TEST (A NUMBER); Table created. SQL> INSERT INTO TEST VALUES (1); 1 row created. SQL> COMMIT; Commit complete. SQL> SELECT COUNT(*) FROM TEST; COUNT(*) ---------- 1 <----- SQL> ALTER SYSTEM SWITCH LOGFILE; System altered. SQL>
On Standby
SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- -------------------- ------------------------------ ---------------- -------------------- UOIN1CON MOUNTED UOIN1CON_DG PHYSICAL STANDBY MAXIMUM PERFORMANCE SQL> SQL> alter database recover managed standby database cancel; Database altered. SQL> SQL> alter database open; Database altered. SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- -------------------- ------------------------------ ---------------- -------------------- UOIN1CON READ ONLY UOIN1CON_DG PHYSICAL STANDBY MAXIMUM PERFORMANCE SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. SQL> / ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION * ERROR at line 1: ORA-01153: an incompatible media recovery is active SQL> SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE; NAME OPEN_MODE DB_UNIQUE_NAME DATABASE_ROLE PROTECTION_MODE --------- -------------------- ------------------------------ ---------------- -------------------- UOIN1CON READ ONLY WITH APPLY UOIN1CON_DG PHYSICAL STANDBY MAXIMUM PERFORMANCE SQL> SELECT COUNT(*) FROM TEST; COUNT(*) ---------- 1 <---- SQL>
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/
Awesome Explanation very well keep posting.
Thank you for the compliment
hi,
what is the location of ur datafile in primary database?
How it got restored to /u02/oracle/oradata/UOIN1CON_DG? when you not used set newname clause in your restore script?
Please clarify.
Hello Bala,
Please check db_file_name_convert values.
Thank you,
Rajasekhar
Hi Rajesjh,
Oracle says, db_file_name_convert and log_file_name works only for duplicate and not for RMAN restore and recover.Since I am a newbie, got confused. Anyway thanks for your inputs and your blog is amazing
DOC ID: Step By Step Guide To Create Physical Standby Database Using RMAN Backup and Restore (Doc ID 469493.1)
Comment:
———
# The below convert parameters are only applicable only when you use RMAN DUPLICATE . They donot work for Normal RMAN RESTORE operation (You would need to use Set newname for datafile )
#
db_file_name_convert=
log_file_name_convert=
Do we need downtime for RMAN restore?
No Downtime Required