Hello Friend's ,
Welcome to a new post
I this post we will discuss and provide steps to configure database setup for hetrogenous.
Step 1 : Primary Pfile :
cdbfly.__data_transfer_cache_size=0
cdbfly.__db_cache_size=729808896
cdbfly.__inmemory_ext_roarea=0
cdbfly.__inmemory_ext_rwarea=0
cdbfly.__java_pool_size=0
cdbfly.__large_pool_size=4194304
cdbfly.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
cdbfly.__pga_aggregate_target=209715200
cdbfly.__sga_target=1073741824
cdbfly.__shared_io_pool_size=50331648
cdbfly.__shared_pool_size=272629760
cdbfly.__streams_pool_size=0
cdbfly.__unified_pga_pool_size=0
*.audit_file_dest='D:\app\Administrator\admin\CDBFLY\adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='D:\app\Administrator\oradata\CDBFLY\control01.ctl','D:\app\Administrator\fast_recovery_area\CDBFLY\control02.ctl'
*.db_block_size=8192
*.db_name='CDBFLY'
*.db_recovery_file_dest='D:\app\Administrator\fast_recovery_area'
*.db_recovery_file_dest_size=12732m
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDBFLYXDB)'
*.enable_pluggable_database=true
*.fal_client='CDBFLY'
*.fal_server='CDBFDR'
*.local_listener='LISTENER_CDBFLY'
*.log_archive_format='arch_%t_%s_%r.arc'
*.log_archive_max_processes=5
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=200m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1024m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
################ Primary DB Init Parameters #################
*.log_archive_config='DG_CONFIG=(CDBFLY,CDBFDR)'
*.log_archive_dest_1='LOCATION=D:\Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDBFLY'
*.log_archive_dest_2='service=CDBFDR LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=CDBFDR'
*.db_file_name_convert='+DATA/CDBFDR/DATAFILES','D:\APP\ADMINISTRATOR\ORADATA\CDBFLY','+DATA/CDBFDR/DATAFILES/PDBSEED','D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\PDBSEED','+DATA/CDBFDR/DATAFILES/FLYDB','D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB'
*.log_file_name_convert='+DATA/CDBFDR/LOGFILES','D:\APP\ADMINISTRATOR\ORADATA\CDBFLY'
*.fal_client='CDBFLY'
*.fal_server='CDBFDR'
*.standby_file_management='AUTO'
Step 2 : Standby Pfile :
cdbfly.__data_transfer_cache_size=0
cdbfly.__db_cache_size=729808896
cdbfly.__inmemory_ext_roarea=0
cdbfly.__inmemory_ext_rwarea=0
cdbfly.__java_pool_size=0
cdbfly.__large_pool_size=4194304
cdbfly.__oracle_base='D:\app\Administrator'#ORACLE_BASE set from environment
cdbfly.__pga_aggregate_target=209715200
cdbfly.__sga_target=1073741824
cdbfly.__shared_io_pool_size=50331648
cdbfly.__shared_pool_size=272629760
cdbfly.__streams_pool_size=0
cdbfly.__unified_pga_pool_size=0
*.audit_file_dest='/oracle/app/audit'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='+DATA/CDBFDR/control01.ctl','+RECO/CDBFDR/control02.ctl'
*.db_block_size=8192
*.db_name='CDBFLY'
*.db_unique_name='CDBFDR'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=12732m
*.diagnostic_dest='D:\app\Administrator'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CDBFDRXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_CDBFDR'
*.log_archive_dest_1='LOCATION=+RECO'
*.log_archive_format='%t_%s_%r.arc'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=200m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1024m
*.undo_tablespace='UNDOTBS1'
################ Standby DB Init Parameters #################
*.log_archive_config='DG_CONFIG=(CDBFDR,CDBFLY)'
*.log_archive_dest_1='LOCATION=+RECO VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDBFDR'
*.log_archive_dest_2='SERVICE=CDBFLY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDBFLY'
*.log_archive_format='arch_%t_%s_%r.arc'
*.log_archive_max_processes=5
*.db_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\CDBFLY','+DATA/CDBFDR/DATAFILES','D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\PDBSEED','+DATA/CDBFDR/DATAFILES/PDBSEED','D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB','+DATA/CDBFDR/DATAFILES/FLYDB'
*.log_file_name_convert='D:\APP\ADMINISTRATOR\ORADATA\CDBFLY','+DATA/CDBFDR/LOGFILES'
*.fal_client='CDBFDR'
*.fal_server='CDBFLY'
*.standby_file_management='AUTO'
If standby is made on windows add instance as below .
set ORACLE_HOME=
set PATH=%PATH%:%ORACLE_HOME%/bin
oradim -new -SID BOSTON -INTPWD primarySysPassword -STARTMODE auto -PFILE "%ORACLE_HOME%\database\initBOSTON.ora"
Now getting on with the steps .
Step 3 : Add standby logfiles on primary (Windows)
alter database add standby logfile group 4 'D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\REDO04.LOG' size 200M;
alter database add standby logfile group 5 'D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\REDO05.LOG' size 200M;
alter database add standby logfile group 6 'D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\REDO06.LOG' size 200M;
Tns file example Primary (Windows) :
# tnsnames.ora Network Configuration File: D:\WINDOWS.X64_193000_db_home\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
CDBFLY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.4.14)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDBFLY)
)
)
CDBFDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.4.61)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDBFDR)
)
)
LISTENER_CDBFLY =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.4.14)(PORT = 1521))
Step 4 : listener file example (Windows):
SID_LIST_LSNR_FLY =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = CDBFDR)
(ORACLE_HOME = D:\WINDOWS.X64_193000_db_home)
(SID_NAME = CDBFDR)
)
(SID_DESC =
(GLOBAL_DBNAME = CDBFLY)
(ORACLE_HOME = D:\WINDOWS.X64_193000_db_home)
(SID_NAME = CDBFLY)
)
)
LSNR_FLY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.4.14)(PORT = 1525))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
Step 5 : Tns file example Standby (Linux):
CDBFLY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.4.14)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDBFLY)
)
)
CDBFDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.4.61)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CDBFDR)
)
)
Step 6 : Listener file example Standby (Linux):
SID_LIST_LSNR_FDR =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = /oracle/app/orawork/product/19.0.0.0/db_1)
(PROGRAM = extproc)
)
)
LSNR_FDR =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.4.61)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LSNR_FLY =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = CDBFDR)
(ORACLE_HOME = D:\WINDOWS.X64_193000_db_home)
(SID_NAME = CDBFDR)
)
(SID_DESC =
(GLOBAL_DBNAME = CDBFLY)
(ORACLE_HOME = D:\WINDOWS.X64_193000_db_home)
(SID_NAME = CDBFLY)
)
)
Step 7 : Start with the standby startup (Nomount Linux) :
[oracle@dm01db01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 27 23:25:35 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1073738488 bytes
Fixed Size 9143032 bytes
Variable Size 276824064 bytes
Database Buffers 784334848 bytes
Redo Buffers 3436544 bytes
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
Step 8 : Start with the restore (connect with auxillary) :
[oracle@dm01db01 dbs]$ rman target sys/Welcome1#@CDBFLY auxiliary sys/Welcome1#@CDBFDR
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Oct 27 23:25:47 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.03.00.00 in TARGET database is not current
PL/SQL package SYS.DBMS_RCVMAN version 19.03.00.00 in TARGET database is not current
connected to target database: CDBFLY (DBID=832005645)
connected to auxiliary database: CDBFLY (not mounted)
RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=278 device type=DISK
allocated channel: prmy2
channel prmy2: SID=31 device type=DISK
allocated channel: prmy3
channel prmy3: SID=265 device type=DISK
allocated channel: stby1
channel stby1: SID=432 device type=DISK
Starting Duplicate Db at 27-OCT-22
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/oracle/app/orawork/product/19.0.0.0/db_1/dbs/orapwCDBFDR' ;
}
executing Memory Script
Starting backup at 27-OCT-22
Finished backup at 27-OCT-22
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '+DATA/CDBFDR/control01.ctl';
restore clone primary controlfile to '+RECO/CDBFDR/control02.ctl' from
'+DATA/CDBFDR/control01.ctl';
}
executing Memory Script
Starting backup at 27-OCT-22
channel prmy1: starting datafile copy
copying standby control file
output file name=D:\WINDOWS.X64_193000_DB_HOME\DATABASE\SNCFCDBFLY.ORA tag=TAG20221027T105607
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27-OCT-22
Starting restore at 27-OCT-22
channel stby1: copied control file copy
Finished restore at 27-OCT-22
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"+DATA/CDBFDR/DATAFILES/temp01.dbf";
set newname for tempfile 2 to
"+DATA/CDBFDR/DATAFILES/PDBSEED/temp012022-10-27_04-07-49-520-am.dbf";
set newname for tempfile 3 to
"+DATA/CDBFDR/DATAFILES/FLYDB/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"+DATA/CDBFDR/DATAFILES/system01.dbf";
set newname for datafile 3 to
"+DATA/CDBFDR/DATAFILES/sysaux01.dbf";
set newname for datafile 4 to
"+DATA/CDBFDR/DATAFILES/undotbs01.dbf";
set newname for datafile 5 to
"+DATA/CDBFDR/DATAFILES/PDBSEED/system01.dbf";
set newname for datafile 6 to
"+DATA/CDBFDR/DATAFILES/PDBSEED/sysaux01.dbf";
set newname for datafile 7 to
"+DATA/CDBFDR/DATAFILES/users01.dbf";
set newname for datafile 8 to
"+DATA/CDBFDR/DATAFILES/PDBSEED/undotbs01.dbf";
set newname for datafile 9 to
"+DATA/CDBFDR/DATAFILES/FLYDB/system01.dbf";
set newname for datafile 10 to
"+DATA/CDBFDR/DATAFILES/FLYDB/sysaux01.dbf";
set newname for datafile 11 to
"+DATA/CDBFDR/DATAFILES/FLYDB/undotbs01.dbf";
set newname for datafile 12 to
"+DATA/CDBFDR/DATAFILES/FLYDB/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"+DATA/CDBFDR/DATAFILES/system01.dbf" datafile
3 auxiliary format
"+DATA/CDBFDR/DATAFILES/sysaux01.dbf" datafile
4 auxiliary format
"+DATA/CDBFDR/DATAFILES/undotbs01.dbf" datafile
5 auxiliary format
"+DATA/CDBFDR/DATAFILES/PDBSEED/system01.dbf" datafile
6 auxiliary format
"+DATA/CDBFDR/DATAFILES/PDBSEED/sysaux01.dbf" datafile
7 auxiliary format
"+DATA/CDBFDR/DATAFILES/users01.dbf" datafile
8 auxiliary format
"+DATA/CDBFDR/DATAFILES/PDBSEED/undotbs01.dbf" datafile
9 auxiliary format
"+DATA/CDBFDR/DATAFILES/FLYDB/system01.dbf" datafile
10 auxiliary format
"+DATA/CDBFDR/DATAFILES/FLYDB/sysaux01.dbf" datafile
11 auxiliary format
"+DATA/CDBFDR/DATAFILES/FLYDB/undotbs01.dbf" datafile
12 auxiliary format
"+DATA/CDBFDR/DATAFILES/FLYDB/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +DATA/CDBFDR/DATAFILES/temp01.dbf in control file
renamed tempfile 2 to +DATA/CDBFDR/DATAFILES/PDBSEED/temp012022-10-27_04-07-49-520-am.dbf in control file
renamed tempfile 3 to +DATA/CDBFDR/DATAFILES/FLYDB/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
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 backup at 27-OCT-22
channel prmy1: starting datafile copy
input datafile file number=00001 name=D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\SYSTEM01.DBF
channel prmy2: starting datafile copy
input datafile file number=00003 name=D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\SYSAUX01.DBF
channel prmy3: starting datafile copy
input datafile file number=00010 name=D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB\SYSAUX01.DBF
output file name=+DATA/CDBFDR/DATAFILES/FLYDB/sysaux01.dbf tag=TAG20221027T105615
channel prmy3: datafile copy complete, elapsed time: 00:00:15
channel prmy3: starting datafile copy
input datafile file number=00006 name=D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\PDBSEED\SYSAUX01.DBF
output file name=+DATA/CDBFDR/DATAFILES/system01.dbf tag=TAG20221027T105615
channel prmy1: datafile copy complete, elapsed time: 00:00:31
channel prmy1: starting datafile copy
input datafile file number=00005 name=D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\PDBSEED\SYSTEM01.DBF
output file name=+DATA/CDBFDR/DATAFILES/sysaux01.dbf tag=TAG20221027T105615
channel prmy2: datafile copy complete, elapsed time: 00:00:31
channel prmy2: starting datafile copy
input datafile file number=00009 name=D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB\SYSTEM01.DBF
output file name=+DATA/CDBFDR/DATAFILES/PDBSEED/sysaux01.dbf tag=TAG20221027T105615
channel prmy3: datafile copy complete, elapsed time: 00:00:15
channel prmy3: starting datafile copy
input datafile file number=00008 name=D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\PDBSEED\UNDOTBS01.DBF
output file name=+DATA/CDBFDR/DATAFILES/PDBSEED/undotbs01.dbf tag=TAG20221027T105615
channel prmy3: datafile copy complete, elapsed time: 00:00:07
channel prmy3: starting datafile copy
input datafile file number=00011 name=D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB\UNDOTBS01.DBF
output file name=+DATA/CDBFDR/DATAFILES/PDBSEED/system01.dbf tag=TAG20221027T105615
channel prmy1: datafile copy complete, elapsed time: 00:00:09
channel prmy1: starting datafile copy
input datafile file number=00004 name=D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\UNDOTBS01.DBF
output file name=+DATA/CDBFDR/DATAFILES/FLYDB/system01.dbf tag=TAG20221027T105615
channel prmy2: datafile copy complete, elapsed time: 00:00:10
channel prmy2: starting datafile copy
input datafile file number=00007 name=D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\USERS01.DBF
output file name=+DATA/CDBFDR/DATAFILES/users01.dbf tag=TAG20221027T105615
channel prmy2: datafile copy complete, elapsed time: 00:00:01
channel prmy2: starting datafile copy
input datafile file number=00012 name=D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB\USERS01.DBF
output file name=+DATA/CDBFDR/DATAFILES/undotbs01.dbf tag=TAG20221027T105615
channel prmy1: datafile copy complete, elapsed time: 00:00:04
output file name=+DATA/CDBFDR/DATAFILES/FLYDB/users01.dbf tag=TAG20221027T105615
channel prmy2: datafile copy complete, elapsed time: 00:00:01
output file name=+DATA/CDBFDR/DATAFILES/FLYDB/undotbs01.dbf tag=TAG20221027T105615
channel prmy3: datafile copy complete, elapsed time: 00:00:05
Finished backup at 27-OCT-22
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1119223617 file name=+DATA/CDBFDR/DATAFILES/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1119223617 file name=+DATA/CDBFDR/DATAFILES/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1119223617 file name=+DATA/CDBFDR/DATAFILES/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1119223618 file name=+DATA/CDBFDR/DATAFILES/PDBSEED/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1119223618 file name=+DATA/CDBFDR/DATAFILES/PDBSEED/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1119223618 file name=+DATA/CDBFDR/DATAFILES/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=1119223618 file name=+DATA/CDBFDR/DATAFILES/PDBSEED/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1119223618 file name=+DATA/CDBFDR/DATAFILES/FLYDB/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=1119223618 file name=+DATA/CDBFDR/DATAFILES/FLYDB/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1119223618 file name=+DATA/CDBFDR/DATAFILES/FLYDB/undotbs01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=1119223618 file name=+DATA/CDBFDR/DATAFILES/FLYDB/users01.dbf
Finished Duplicate Db at 27-OCT-22
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: stby1
Restore completed :
Step 9 : connect Primary and do some log switches :
D:\WINDOWS.X64_193000_db_home\database>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 27 11:02:06 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> set pages 9999 lines 300
SQL> col OPEN_MODE for a10
SQL> col HOST_NAME for a30
SQL> select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------------
CDBFLY DM01WIN01 PRIMARY READ WRITE ARCHIVELOG 19.0.0.0.0 ALLOWED 27-OCT-2022 09:55:29
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log liist
SP2-0718: illegal ARCHIVE LOG option
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\Archive
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
Step 10 : Connect Standby start the MRP and check the sync :
[oracle@dm01db01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 27 23:29:27 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
MOUNTED CDBFDR PHYSICAL STANDBY
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
no rows selected
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------------
CDBFLY dm01db01.database.com PHYSICAL STANDBY MOUNTED ARCHIVELOG 19.0.0.0.0 ALLOWED 27-OCT-2022 23:25:40
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 11 3 8
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
SQL>
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 8
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CLOSING 9
ARCH CLOSING 10
ARCH CLOSING 11
ARCH CLOSING 7
RFS IDLE 0
RFS IDLE 12
RFS IDLE 0
MRP0 APPLYING_LOG 12
11 rows selected.
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 11 11 0
Step 11 : Datafiles and log files location created on restore :
SQL> col member for a40
SQL> set lines 200
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ---------------------------------------- --- ----------
3 ONLINE +DATA/CDBFDR/LOGFILES/redo03.log NO 0
2 ONLINE +DATA/CDBFDR/LOGFILES/redo02.log NO 0
1 ONLINE +DATA/CDBFDR/LOGFILES/redo01.log NO 0
4 STANDBY +DATA/CDBFDR/LOGFILES/redo04.log NO 0
5 STANDBY +DATA/CDBFDR/LOGFILES/redo05.log NO 0
6 STANDBY +DATA/CDBFDR/LOGFILES/redo06.log NO 0
6 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 0 209715200 512 1 YES UNUSED 2006928 27-OCT-22 9.2954E+18 0
3 1 0 209715200 512 1 YES UNUSED 2001714 27-OCT-22 2006928 27-OCT-22 0
2 1 0 209715200 512 1 YES UNUSED 1956904 27-OCT-22 2001714 27-OCT-22 0
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/CDBFDR/DATAFILES/system01.dbf
+DATA/CDBFDR/DATAFILES/sysaux01.dbf
+DATA/CDBFDR/DATAFILES/undotbs01.dbf
+DATA/CDBFDR/DATAFILES/PDBSEED/system01.dbf
+DATA/CDBFDR/DATAFILES/PDBSEED/sysaux01.dbf
+DATA/CDBFDR/DATAFILES/users01.dbf
+DATA/CDBFDR/DATAFILES/PDBSEED/undotbs01.dbf
+DATA/CDBFDR/DATAFILES/FLYDB/system01.dbf
+DATA/CDBFDR/DATAFILES/FLYDB/sysaux01.dbf
+DATA/CDBFDR/DATAFILES/FLYDB/undotbs01.dbf
+DATA/CDBFDR/DATAFILES/FLYDB/users01.dbf
11 rows selected.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 FLYDB MOUNTED
Step 12 : check new tablespace creation and movement on standby .
Primary (Windows)
SQL> select name from v$datafile;
NAME
-----------------------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB\SYSTEM01.DBF
D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB\SYSAUX01.DBF
D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB\UNDOTBS01.DBF
D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB\USERS01.DBF
SQL> create tablespace Newtest datafile 'D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB\Newtest01.DBF' size 1G;
Tablespace created.
SQL> select name from v$datafile;
NAME
------------------------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB\SYSTEM01.DBF
D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB\SYSAUX01.DBF
D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB\UNDOTBS01.DBF
D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB\USERS01.DBF
D:\APP\ADMINISTRATOR\ORADATA\CDBFLY\FLYDB\NEWTEST01.DBF
Standby (Linux)
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/CDBFDR/DATAFILES/FLYDB/system01.dbf
+DATA/CDBFDR/DATAFILES/FLYDB/sysaux01.dbf
+DATA/CDBFDR/DATAFILES/FLYDB/undotbs01.dbf
+DATA/CDBFDR/DATAFILES/FLYDB/users01.dbf
+DATA/CDBFDR/DATAFILES/FLYDB/newtest01.dbf
Alert log Standby :
MRP0 (PID:65984): Media Recovery Log +RECO/CDBFDR/ARCHIVELOG/2022_10_27/thread_1_seq_19.273.1119224533
MRP0 (PID:65984): Media Recovery Waiting for T-1.S-20 (in transit)
2022-10-27T23:42:13.384993+05:30
Recovery of Online Redo Log: Thread 1 Group 5 Seq 20 Reading mem 0
Mem# 0: +DATA/CDBFDR/LOGFILES/redo05.log
2022-10-30T20:10:15.408399+05:30
FLYDB(3):Recovery created file +DATA/CDBFDR/DATAFILES/FLYDB/newtest01.dbf
FLYDB(3):Successfully added datafile 13 to media recovery
FLYDB(3):Datafile #13: '+DATA/CDBFDR/DATAFILES/FLYDB/newtest01.dbf'
2022-10-30T20:10:41.126778+05:30
Resize operation completed for file# 3, fname +DATA/CDBFDR/DATAFILES/sysaux01.dbf, old size 522240K, new size 532480K
Configration completed , Hope This Helps .
Regards
Sultan Khan
ConversionConversion EmoticonEmoticon