My environment details-
SRORCL is Primary CDB
TRORCL is standby CDB
C:\Users\Mahesh>set ORACLE_SID=SRORCL
C:\Users\Mahesh>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Feb 23 21:36:04 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB MOUNTED
SQL> alter pluggable database SRPDB open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB READ WRITE NO
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL>
Enable force logging-
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
Add standby redolog files-
SQL> select thread#, group# ,bytes/1024/1024 from v$log order by 1,2;
THREAD# GROUP# BYTES/1024/1024
---------- ---------- ---------------
1 1 200
1 2 200
1 3 200
SQL> alter database add standby logfile thread 1 group 4 size 200M,group 5 size 200M,group 6 size 200M, group 7 size
Database altered.
SQL> select thread#, group# ,bytes/1024/1024 from v$log order by 1,2;
THREAD# GROUP# BYTES/1024/1024
---------- ---------- ---------------
1 1 200
1 2 200
1 3 200
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 1 0 YES UNASSIGNED
5 1 0 YES UNASSIGNED
6 1 0 YES UNASSIGNED
7 1 0 YES UNASSIGNED
Set password file-
F:\app\Mahesh\product\12.2.0\dbhome_1\database>orapwd file=pwdSRORCL password=Ahana!1234
Set data guard related parameters-
F:\app\Mahesh\product\12.2.0\dbhome_1\dbs>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Feb 23 22:03:15 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show parameter remote_login_passwordfil
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string F:\APP\MAHESH\PRODUCT\12.2.0\D
BHOME_1\DATABASE\SPFILESRORCL.
ORA
SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=SRORCL' scope=both;
System altered.
SQL> alter system set log_archive_dest_2='service=TRORCL NOAFFIRM LGWR ASYNC COMPRESSION=ENABLE VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TRORCL' 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_config='DG_CONFIG=(SRORCL,TRORCL)' scope=both;
System altered.
SQL>
Set fall client and fal server-
SQL> alter system set fal_client='SRORCL' scope=both;
System altered.
SQL> alter system set fal_server='TRORCL' scope=both;
System altered.
Create tns entry-
SRORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Mahesh-PC)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SRORCL)
)
)
TRORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Mahesh-PC)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TRORCL)
)
)
Create init file for standby-
SQL> create pfile='F:\app\Mahesh\product\12.2.0\dbhome_1\database\initTRORCL.ora' from spfile;
File created.
SQL>
then edit it,
trorcl.__data_transfer_cache_size=0
trorcl.__db_cache_size=427819008
trorcl.__inmemory_ext_roarea=0
trorcl.__inmemory_ext_rwarea=0
trorcl.__java_pool_size=4194304
trorcl.__large_pool_size=20971520
trorcl.__oracle_base='F:\app\Mahesh'#ORACLE_BASE set from environment
trorcl.__pga_aggregate_target=209715200
trorcl.__sga_target=734003200
trorcl.__shared_io_pool_size=33554432
trorcl.__shared_pool_size=230686720
trorcl.__streams_pool_size=0
*.audit_file_dest='F:\app\Mahesh\admin\TRORCL\adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='F:\ORADATA\TRORCL\CONTROLFILE\O1_MF_H5544MB6_.CTL','F:\ORADATA\ORAFRA\TRORCL\CONTROLFILE\O1_MF_H5544MKZ_.CTL'
*.db_block_size=8192
*.db_create_file_dest='F:\Oradata'
*.db_name='SRORCL'
*.db_unique_name='TRORCL'
*.db_recovery_file_dest='F:\Oradata\Orafra'
*.db_recovery_file_dest_size=14250m
*.diagnostic_dest='F:\app\Mahesh'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TRORCLXDB)'
*.enable_pluggable_database=true
*.fal_client='TRORCL'
*.fal_server='SRORCL'
*.log_archive_config='DG_CONFIG=(SRORCL,TRORCL)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=TRORCL'
*.log_archive_dest_2='service=SRORCL NOAFFIRM LGWR ASYNC COMPRESSION=ENABLE VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SROCRL'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=200m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=700m
*.undo_tablespace='UNDOTBS1'
Create new service for standby instance(only for windows)-
F:\app\Mahesh\product\12.2.0\dbhome_1\database>oradim -new -sid TRORCL -syspwd Ahana!1234
Instance created.
F:\app\Mahesh\product\12.2.0\dbhome_1\database>set ORACLE_SID=TRORCL
F:\app\Mahesh\product\12.2.0\dbhome_1\database>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Feb 23 22:38:46 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='INITTRORCL.ora';
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 8923240 bytes
Variable Size 348129176 bytes
Database Buffers 369098752 bytes
Redo Buffers 7852032 bytes
SQL>
Take full backup of the primary database-
F:\app\Mahesh\product\12.2.0\dbhome_1\database>set ORACLE_SID=SRORCL
F:\app\Mahesh\product\12.2.0\dbhome_1\database>rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Sun Feb 23 22:41:12 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: SRORCL (DBID=3570490003)
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT = 'F:\RMAN_BKP\backup_%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:\RMAN_BKP\backup_%U';
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK to 'F:\RMAN_BKP\cf_%F';
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'F:\RMAN_BKP\cf_%F';
new RMAN configuration parameters are successfully stored
RMAN>
RUN
{
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
allocate channel T1 type disk;
allocate channel T2 type disk;
allocate channel T3 type disk;
allocate channel T4 type disk;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
BACKUP as compressed backupset database FORMAT 'F:\RMAN_BKP\DBFULL_BKP_%U’;
backup current controlfile format 'F:\RMAN_BKP\cf_%d_%U_%t’;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT’;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT’;
backup archivelog all FORMAT 'F:\RMAN_BKP\Archive_%d_%T_%s_%p’ ;
release channel T1;
release channel T2;
release channel T3;
release channel T4;
}
sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
allocated channel: T1
channel T1: SID=395 device type=DISK
allocated channel: T2
channel T2: SID=21 device type=DISK
allocated channel: T3
channel T3: SID=142 device type=DISK
allocated channel: T4
channel T4: SID=268 device type=DISK
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored
Starting backup at 24-FEB-20
channel T1: starting compressed full datafile backup set
channel T1: specifying datafile(s) in backup set
input datafile file number=00001 name=F:\ORADATA\SRORCL\DATAFILE\O1_MF_SYSTEM_H553WVGR_.DBF
channel T1: starting piece 1 at 24-FEB-20
channel T2: starting compressed full datafile backup set
channel T2: specifying datafile(s) in backup set
input datafile file number=00005 name=F:\ORADATA\SRORCL\DATAFILE\O1_MF_UNDOTBS1_H553ZQT8_.DBF
channel T2: starting piece 1 at 24-FEB-20
channel T3: starting compressed full datafile backup set
channel T3: specifying datafile(s) in backup set
input datafile file number=00003 name=F:\ORADATA\SRORCL\DATAFILE\O1_MF_SYSAUX_H553YMYJ_.DBF
channel T3: starting piece 1 at 24-FEB-20
channel T4: starting compressed full datafile backup set
channel T4: specifying datafile(s) in backup set
input datafile file number=00009 name=F:\ORADATA\SRORCL\FAC23F4A1C7247F38CD2DD57F9DF74CE\DATAFILE\O1_MF_SYSAUX_H5555GRL_.DBF
channel T4: starting piece 1 at 24-FEB-20
channel T2: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\DBFULL_BKP_2DUP9SLG_1_1 tag=TAG20200224T000711 comment=NONE
channel T2: backup set complete, elapsed time: 00:00:17
channel T2: starting compressed full datafile backup set
channel T2: specifying datafile(s) in backup set
input datafile file number=00004 name=F:\ORADATA\SRORCL\DATAFILE\O1_MF_SYSAUX_H5547FR7_.DBF
channel T2: starting piece 1 at 24-FEB-20
channel T2: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\DBFULL_BKP_2GUP9SM2_1_1 tag=TAG20200224T000711 comment=NONE
channel T2: backup set complete, elapsed time: 00:01:06
channel T2: starting compressed full datafile backup set
channel T2: specifying datafile(s) in backup set
input datafile file number=00002 name=F:\ORADATA\SRORCL\DATAFILE\O1_MF_SYSTEM_H5547FV4_.DBF
channel T2: starting piece 1 at 24-FEB-20
channel T3: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\DBFULL_BKP_2EUP9SLH_1_1 tag=TAG20200224T000711 comment=NONE
channel T3: backup set complete, elapsed time: 00:01:24
channel T3: starting compressed full datafile backup set
channel T3: specifying datafile(s) in backup set
input datafile file number=00008 name=F:\ORADATA\SRORCL\FAC23F4A1C7247F38CD2DD57F9DF74CE\DATAFILE\O1_MF_SYSTEM_H5555GK8_.DBF
channel T3: starting piece 1 at 24-FEB-20
channel T4: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\DBFULL_BKP_2FUP9SLJ_1_1 tag=TAG20200224T000711 comment=NONE
channel T4: backup set complete, elapsed time: 00:01:22
channel T4: starting compressed full datafile backup set
channel T4: specifying datafile(s) in backup set
input datafile file number=00006 name=F:\ORADATA\SRORCL\DATAFILE\O1_MF_UNDOTBS1_H5547FVN_.DBF
channel T4: starting piece 1 at 24-FEB-20
channel T1: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\DBFULL_BKP_2CUP9SLG_1_1 tag=TAG20200224T000711 comment=NONE
channel T1: backup set complete, elapsed time: 00:01:29
channel T1: starting compressed full datafile backup set
channel T1: specifying datafile(s) in backup set
input datafile file number=00010 name=F:\ORADATA\SRORCL\FAC23F4A1C7247F38CD2DD57F9DF74CE\DATAFILE\O1_MF_UNDOTBS1_H5555GVJ_.DBF
channel T1: starting piece 1 at 24-FEB-20
channel T1: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\DBFULL_BKP_2KUP9SO9_1_1 tag=TAG20200224T000711 comment=NONE
channel T1: backup set complete, elapsed time: 00:00:07
channel T1: starting compressed full datafile backup set
channel T1: specifying datafile(s) in backup set
channel T4: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\DBFULL_BKP_2JUP9SO7_1_1 tag=TAG20200224T000711 comment=NONE
channel T4: backup set complete, elapsed time: 00:00:11
channel T4: starting compressed full datafile backup set
channel T4: specifying datafile(s) in backup set
input datafile file number=00007 name=F:\ORADATA\SRORCL\DATAFILE\O1_MF_USERS_H5540W5W_.DBF
channel T4: starting piece 1 at 24-FEB-20
including current control file in backup set
channel T1: starting piece 1 at 24-FEB-20
channel T2: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\DBFULL_BKP_2HUP9SO5_1_1 tag=TAG20200224T000711 comment=NONE
channel T2: backup set complete, elapsed time: 00:00:25
channel T2: starting compressed full datafile backup set
channel T2: specifying datafile(s) in backup set
input datafile file number=00011 name=F:\ORADATA\SRORCL\FAC23F4A1C7247F38CD2DD57F9DF74CE\DATAFILE\O1_MF_USERS_H5558920_.DBF
channel T2: starting piece 1 at 24-FEB-20
channel T4: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\DBFULL_BKP_2MUP9SOL_1_1 tag=TAG20200224T000711 comment=NONE
channel T4: backup set complete, elapsed time: 00:00:09
channel T4: starting compressed full datafile backup set
channel T4: specifying datafile(s) in backup set
including current SPFILE in backup set
channel T4: starting piece 1 at 24-FEB-20
channel T3: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\DBFULL_BKP_2IUP9SO6_1_1 tag=TAG20200224T000711 comment=NONE
channel T3: backup set complete, elapsed time: 00:00:26
channel T1: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\DBFULL_BKP_2LUP9SOK_1_1 tag=TAG20200224T000711 comment=NONE
channel T1: backup set complete, elapsed time: 00:00:03
channel T4: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\DBFULL_BKP_2OUP9SP0_1_1 tag=TAG20200224T000711 comment=NONE
channel T4: backup set complete, elapsed time: 00:00:02
channel T2: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\DBFULL_BKP_2NUP9SOV_1_1 tag=TAG20200224T000711 comment=NONE
channel T2: backup set complete, elapsed time: 00:00:04
Finished backup at 24-FEB-20
Starting backup at 24-FEB-20
channel T1: starting full datafile backup set
channel T1: specifying datafile(s) in backup set
including current control file in backup set
channel T1: starting piece 1 at 24-FEB-20
channel T1: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\CF_SRORCL_2PUP9SPC_1_1_1033171756 tag=TAG20200224T000916 comment=NONE
channel T1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-FEB-20
sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT
sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT
Starting backup at 24-FEB-20
current log archived
channel T1: starting archived log backup set
channel T1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=1 STAMP=1033152770
channel T1: starting piece 1 at 24-FEB-20
channel T2: starting archived log backup set
channel T2: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=2 STAMP=1033161260
input archived log thread=1 sequence=4 RECID=3 STAMP=1033164576
input archived log thread=1 sequence=5 RECID=4 STAMP=1033164603
input archived log thread=1 sequence=6 RECID=5 STAMP=1033165055
input archived log thread=1 sequence=7 RECID=6 STAMP=1033166658
channel T2: starting piece 1 at 24-FEB-20
channel T3: starting archived log backup set
channel T3: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=7 STAMP=1033166663
input archived log thread=1 sequence=9 RECID=8 STAMP=1033168516
input archived log thread=1 sequence=10 RECID=9 STAMP=1033168522
input archived log thread=1 sequence=11 RECID=10 STAMP=1033168527
input archived log thread=1 sequence=12 RECID=11 STAMP=1033168531
input archived log thread=1 sequence=13 RECID=12 STAMP=1033169253
channel T3: starting piece 1 at 24-FEB-20
channel T4: starting archived log backup set
channel T4: specifying archived log(s) in backup set
input archived log thread=1 sequence=20 RECID=19 STAMP=1033170533
input archived log thread=1 sequence=21 RECID=20 STAMP=1033171628
input archived log thread=1 sequence=22 RECID=21 STAMP=1033171762
input archived log thread=1 sequence=23 RECID=22 STAMP=1033171763
input archived log thread=1 sequence=24 RECID=23 STAMP=1033171765
channel T4: starting piece 1 at 24-FEB-20
channel T1: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\ARCHIVE_SRORCL_20200224_90_1 tag=TAG20200224T000925 comment=NONE
channel T1: backup set complete, elapsed time: 00:00:07
channel T1: starting archived log backup set
channel T1: specifying archived log(s) in backup set
input archived log thread=1 sequence=14 RECID=13 STAMP=1033169256
input archived log thread=1 sequence=15 RECID=14 STAMP=1033169373
input archived log thread=1 sequence=16 RECID=15 STAMP=1033169492
input archived log thread=1 sequence=17 RECID=16 STAMP=1033170408
input archived log thread=1 sequence=18 RECID=17 STAMP=1033170528
input archived log thread=1 sequence=19 RECID=18 STAMP=1033170530
channel T1: starting piece 1 at 24-FEB-20
channel T2: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\ARCHIVE_SRORCL_20200224_91_1 tag=TAG20200224T000925 comment=NONE
channel T2: backup set complete, elapsed time: 00:00:05
channel T3: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\ARCHIVE_SRORCL_20200224_92_1 tag=TAG20200224T000925 comment=NONE
channel T3: backup set complete, elapsed time: 00:00:03
channel T4: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\ARCHIVE_SRORCL_20200224_93_1 tag=TAG20200224T000925 comment=NONE
channel T4: backup set complete, elapsed time: 00:00:02
channel T1: finished piece 1 at 24-FEB-20
piece handle=F:\RMAN_BKP\ARCHIVE_SRORCL_20200224_94_1 tag=TAG20200224T000925 comment=NONE
channel T1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-FEB-20
released channel: T1
released channel: T2
released channel: T3
released channel: T4
RMAN>
Startup standby in nomount and do the duplicate-
F:\app\Mahesh\product\12.2.0\dbhome_1\database>set ORACLE_SID=TRORCL
F:\app\Mahesh\product\12.2.0\dbhome_1\database>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 29 16:08:44 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='initTRORCL.ora';
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 8923240 bytes
Variable Size 348129176 bytes
Database Buffers 369098752 bytes
Redo Buffers 7852032 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
F:\app\Mahesh\product\12.2.0\dbhome_1\database>rman auxiliary /
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Feb 24 00:10:33 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to auxiliary database: TRORCL (not mounted)
RUN
{
allocate auxiliary channel t1 type disk;
allocate auxiliary channel t2 type disk;
allocate auxiliary channel t3 type disk;
duplicate target database for standby backup location 'F:\RMAN_BKP' dorecover ;
}
allocated channel: t1
channel t1: SID=261 device type=DISK
allocated channel: t2
channel t2: SID=379 device type=DISK
allocated channel: t3
channel t3: SID=10 device type=DISK
Starting Duplicate Db at 24-FEB-20
contents of Memory Script:
{
restore clone standby controlfile from 'F:\RMAN_BKP\CF_SRORCL_2PUP9SPC_1_1_1033171756';
}
executing Memory Script
Starting restore at 24-FEB-20
channel t2: skipped, AUTOBACKUP already found
channel t3: skipped, AUTOBACKUP already found
channel t1: restoring control file
channel t1: restore complete, elapsed time: 00:00:10
output file name=F:\ORADATA\TRORCL\CONTROLFILE\O1_MF_H5544MB6_.CTL
output file name=F:\ORADATA\ORAFRA\TRORCL\CONTROLFILE\O1_MF_H5544MKZ_.CTL
Finished restore at 24-FEB-20
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 until scn 1849796;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 2 to new;
set newname for clone tempfile 3 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 11 to new;
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to F:\ORADATA\TRORCL\DATAFILE\O1_MF_TEMP_%U_.TMP in control file
renamed tempfile 2 to F:\ORADATA\TRORCL\80F837C1EA84404897B4B69B7763A16F\DATAFILE\O1_MF_TEMP_%U_.TMP in control file
renamed tempfile 3 to F:\ORADATA\TRORCL\FAC23F4A1C7247F38CD2DD57F9DF74CE\DATAFILE\O1_MF_TEMP_%U_.TMP in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 24-FEB-20
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to F:\ORADATA\TRORCL\DATAFILE\O1_MF_SYSTEM_%U_.DBF
channel t1: reading from backup piece F:\RMAN_BKP\DBFULL_BKP_2CUP9SLG_1_1
channel t2: starting datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
channel t2: restoring datafile 00003 to F:\ORADATA\TRORCL\DATAFILE\O1_MF_SYSAUX_%U_.DBF
channel t2: reading from backup piece F:\RMAN_BKP\DBFULL_BKP_2EUP9SLH_1_1
channel t3: starting datafile backup set restore
channel t3: specifying datafile(s) to restore from backup set
channel t3: restoring datafile 00005 to F:\ORADATA\TRORCL\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF
channel t3: reading from backup piece F:\RMAN_BKP\DBFULL_BKP_2DUP9SLG_1_1
channel t3: piece handle=F:\RMAN_BKP\DBFULL_BKP_2DUP9SLG_1_1 tag=TAG20200224T000711
channel t3: restored backup piece 1
channel t3: restore complete, elapsed time: 00:00:25
channel t3: starting datafile backup set restore
channel t3: specifying datafile(s) to restore from backup set
channel t3: restoring datafile 00009 to F:\ORADATA\TRORCL\FAC23F4A1C7247F38CD2DD57F9DF74CE\DATAFILE\O1_MF_SYSAUX_%U_.DBF
channel t3: reading from backup piece F:\RMAN_BKP\DBFULL_BKP_2FUP9SLJ_1_1
channel t2: piece handle=F:\RMAN_BKP\DBFULL_BKP_2EUP9SLH_1_1 tag=TAG20200224T000711
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:42
channel t2: starting datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
channel t2: restoring datafile 00002 to F:\ORADATA\TRORCL\80F837C1EA84404897B4B69B7763A16F\DATAFILE\O1_MF_SYSTEM_%U_.DBF
channel t2: reading from backup piece F:\RMAN_BKP\DBFULL_BKP_2HUP9SO5_1_1
channel t2: piece handle=F:\RMAN_BKP\DBFULL_BKP_2HUP9SO5_1_1 tag=TAG20200224T000711
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:25
channel t2: starting datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
channel t2: restoring datafile 00004 to F:\ORADATA\TRORCL\80F837C1EA84404897B4B69B7763A16F\DATAFILE\O1_MF_SYSAUX_%U_.DBF
channel t2: reading from backup piece F:\RMAN_BKP\DBFULL_BKP_2GUP9SM2_1_1
channel t3: piece handle=F:\RMAN_BKP\DBFULL_BKP_2FUP9SLJ_1_1 tag=TAG20200224T000711
channel t3: restored backup piece 1
channel t3: restore complete, elapsed time: 00:00:41
channel t3: starting datafile backup set restore
channel t3: specifying datafile(s) to restore from backup set
channel t3: restoring datafile 00008 to F:\ORADATA\TRORCL\FAC23F4A1C7247F38CD2DD57F9DF74CE\DATAFILE\O1_MF_SYSTEM_%U_.DBF
channel t3: reading from backup piece F:\RMAN_BKP\DBFULL_BKP_2IUP9SO6_1_1
channel t1: piece handle=F:\RMAN_BKP\DBFULL_BKP_2CUP9SLG_1_1 tag=TAG20200224T000711
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:01:15
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00006 to F:\ORADATA\TRORCL\80F837C1EA84404897B4B69B7763A16F\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF
channel t1: reading from backup piece F:\RMAN_BKP\DBFULL_BKP_2JUP9SO7_1_1
channel t1: piece handle=F:\RMAN_BKP\DBFULL_BKP_2JUP9SO7_1_1 tag=TAG20200224T000711
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:07
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00010 to F:\ORADATA\TRORCL\FAC23F4A1C7247F38CD2DD57F9DF74CE\DATAFILE\O1_MF_UNDOTBS1_%U_.DBF
channel t1: reading from backup piece F:\RMAN_BKP\DBFULL_BKP_2KUP9SO9_1_1
channel t1: piece handle=F:\RMAN_BKP\DBFULL_BKP_2KUP9SO9_1_1 tag=TAG20200224T000711
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:07
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00007 to F:\ORADATA\TRORCL\DATAFILE\O1_MF_USERS_%U_.DBF
channel t1: reading from backup piece F:\RMAN_BKP\DBFULL_BKP_2MUP9SOL_1_1
channel t3: piece handle=F:\RMAN_BKP\DBFULL_BKP_2IUP9SO6_1_1 tag=TAG20200224T000711
channel t3: restored backup piece 1
channel t3: restore complete, elapsed time: 00:00:23
channel t3: starting datafile backup set restore
channel t3: specifying datafile(s) to restore from backup set
channel t3: restoring datafile 00011 to F:\ORADATA\TRORCL\FAC23F4A1C7247F38CD2DD57F9DF74CE\DATAFILE\O1_MF_USERS_%U_.DBF
channel t3: reading from backup piece F:\RMAN_BKP\DBFULL_BKP_2NUP9SOV_1_1
channel t1: piece handle=F:\RMAN_BKP\DBFULL_BKP_2MUP9SOL_1_1 tag=TAG20200224T000711
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
channel t3: piece handle=F:\RMAN_BKP\DBFULL_BKP_2NUP9SOV_1_1 tag=TAG20200224T000711
channel t3: restored backup piece 1
channel t3: restore complete, elapsed time: 00:00:01
channel t2: piece handle=F:\RMAN_BKP\DBFULL_BKP_2GUP9SM2_1_1 tag=TAG20200224T000711
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:30
Finished restore at 24-FEB-20
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=12 STAMP=1033171972 file name=F:\ORADATA\TRORCL\DATAFILE\O1_MF_SYSTEM_H5719444_.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=13 STAMP=1033171973 file name=F:\ORADATA\TRORCL\80F837C1EA84404897B4B69B7763A16F\DATAFILE\O1_MF_SYSTEM_H571BFM9_.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=1033171973 file name=F:\ORADATA\TRORCL\DATAFILE\O1_MF_SYSAUX_H57194GK_.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=1033171973 file name=F:\ORADATA\TRORCL\80F837C1EA84404897B4B69B7763A16F\DATAFILE\O1_MF_SYSAUX_H571C6YF_.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=16 STAMP=1033171974 file name=F:\ORADATA\TRORCL\DATAFILE\O1_MF_UNDOTBS1_H57194QW_.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=17 STAMP=1033171974 file name=F:\ORADATA\TRORCL\80F837C1EA84404897B4B69B7763A16F\DATAFILE\O1_MF_UNDOTBS1_H571CGQM_.DBF
datafile 7 switched to datafile copy
input datafile copy RECID=18 STAMP=1033171975 file name=F:\ORADATA\TRORCL\DATAFILE\O1_MF_USERS_H571CX5Z_.DBF
datafile 8 switched to datafile copy
input datafile copy RECID=19 STAMP=1033171975 file name=F:\ORADATA\TRORCL\FAC23F4A1C7247F38CD2DD57F9DF74CE\DATAFILE\O1_MF_SYSTEM_H571C7H3_.DBF
datafile 9 switched to datafile copy
input datafile copy RECID=20 STAMP=1033171975 file name=F:\ORADATA\TRORCL\FAC23F4A1C7247F38CD2DD57F9DF74CE\DATAFILE\O1_MF_SYSAUX_H5719YFS_.DBF
datafile 10 switched to datafile copy
input datafile copy RECID=21 STAMP=1033171976 file name=F:\ORADATA\TRORCL\FAC23F4A1C7247F38CD2DD57F9DF74CE\DATAFILE\O1_MF_UNDOTBS1_H571CP4H_.DBF
datafile 11 switched to datafile copy
input datafile copy RECID=22 STAMP=1033171976 file name=F:\ORADATA\TRORCL\FAC23F4A1C7247F38CD2DD57F9DF74CE\DATAFILE\O1_MF_USERS_H571CXMV_.DBF
contents of Memory Script:
{
set until scn 1849796;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 24-FEB-20
starting media recovery
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=22
channel t1: restoring archived log
archived log thread=1 sequence=23
channel t1: restoring archived log
archived log thread=1 sequence=24
channel t1: reading from backup piece F:\RMAN_BKP\ARCHIVE_SRORCL_20200224_93_1
channel t1: piece handle=F:\RMAN_BKP\ARCHIVE_SRORCL_20200224_93_1 tag=TAG20200224T000925
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
archived log file name=F:\ORADATA\ORAFRA\TRORCL\ARCHIVELOG\2020_02_24\O1_MF_1_22_H571DQ16_.ARC thread=1 sequence=22
channel clone_default: deleting archived log(s)
archived log file name=F:\ORADATA\ORAFRA\TRORCL\ARCHIVELOG\2020_02_24\O1_MF_1_22_H571DQ16_.ARC RECID=2 STAMP=1033171991
archived log file name=F:\ORADATA\ORAFRA\TRORCL\ARCHIVELOG\2020_02_24\O1_MF_1_23_H571DQ1V_.ARC thread=1 sequence=23
channel clone_default: deleting archived log(s)
archived log file name=F:\ORADATA\ORAFRA\TRORCL\ARCHIVELOG\2020_02_24\O1_MF_1_23_H571DQ1V_.ARC RECID=1 STAMP=1033171991
archived log file name=F:\ORADATA\ORAFRA\TRORCL\ARCHIVELOG\2020_02_24\O1_MF_1_24_H571DQ1K_.ARC thread=1 sequence=24
channel clone_default: deleting archived log(s)
archived log file name=F:\ORADATA\ORAFRA\TRORCL\ARCHIVELOG\2020_02_24\O1_MF_1_24_H571DQ1K_.ARC RECID=3 STAMP=1033171991
media recovery complete, elapsed time: 00:00:04
Finished recover at 24-FEB-20
Finished Duplicate Db at 24-FEB-20
released channel: t1
released channel: t2
released channel: t3
RMAN>
Start MRP and open in read-only if you want active dataguard-
F:\app\Mahesh\product\12.2.0\dbhome_1\database>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 29 16:12:13 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select name ,open_mode from v$database ;
NAME OPEN_MODE
--------- --------------------
SRORCL MOUNTED
SQL> recover managed standby database disconnect from session ;
Media recovery complete.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 66 10240 1942
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
RFS RECEIVING 1 67 119 1
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 66 12180 12181
9 rows selected.
SQL> recover managed standby database cancel ;
Media recovery complete.
SQL> alter database open read only;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB MOUNTED
SQL> alter pluggable database SRPDB open read only;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB READ ONLY NO
SQL> select A.*,B.Applied "Last Standby Seq Applied" , A.Received - B.Applied "Gap" from
2 (select thread#, max(sequence#) Received
3 from v$archived_log val, v$database vdb
4 where val.resetlogs_change# = vdb.resetlogs_change#
5 group by thread#) A,
6 (select thread#, max(sequence#) Applied
7 from v$archived_log val, v$database vdb
8 where val.resetlogs_change# = vdb.resetlogs_change#
9 and val.applied='YES'
10 group by thread# ) B
11 where A.thread#=B.thread#
12 order by 1;
THREAD# RECEIVED Last Standby Seq Applied Gap
---------- ---------- ------------------------ ----------
1 68 68 0
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 66 10240 1942
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 67 1 213
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 68 1 11
RFS IDLE 1 69 435 1
RFS IDLE 0 0 0 0
8 rows selected.
SQL> recover managed standby database disconnect from session ;
Media recovery complete.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 66 10240 1942
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 67 1 213
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 68 1 11
RFS IDLE 1 69 547 1
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 69 547 409600
9 rows selected.
SQL>
Query to check sync status-
select A.*,B.Applied "Last Standby Seq Applied" , A.Received - B.Applied "Gap" from
(select thread#, max(sequence#) Received
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread#) A,
(select thread#, max(sequence#) Applied
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied='YES'
group by thread# ) B
where A.thread#=B.thread#
order by 1;
