Migrate Oracle Database
Source Database
DB_NAME : ibmggn
DB-Unique_name = ibmggn
Database version : 12.2.0
OS : RHE 8.7
SQL> select name ,open_mode, log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
IBMGGN READ WRITE ARCHIVELOG
SQL>
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL>
SQL>
SQL>
SQL>
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL>
SQL>
SQL>
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL>
SQL>
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
SQL>
Add standby redolog : n+1 (where n is current group of redolog )
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ibmggn/redo03.log
/u01/app/oracle/oradata/ibmggn/redo02.log
/u01/app/oracle/oradata/ibmggn/redo01.log
SQL>
SQL> select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024,MEMBERS,STATUS from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- ---------- --------------- ---------- ----------------
1 1 1 200 1 CURRENT
2 1 0 200 1 UNUSED
3 1 0 200 1 UNUSED
SQL>
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL>
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>
SQL>
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>
alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo1.log') size 200m;
alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo2.log') size 200m;
alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo3.log') size 200m;
alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo4.log') size 200m;
SQL>
SQL>
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo1.log') size 200m;
Database altered.
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo2.log') size 200m;
Database altered.
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo3.log') size 200m;
Database altered.
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibmggn/stbyredo4.log') size 200m;
Database altered.
SQL>
column REDOLOG_FILE_NAME format a50
SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;
SQL> column REDOLOG_FILE_NAME format a50
SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;SQL> SQL> 2 3 4 5 6 7 8 9 10 11
GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB
---------- ---------- ---------- --- ---------------- -------------------------------------------------- ----------
1 1 4 NO CURRENT /u01/app/oracle/oradata/ibmggn/redo01.log 200
2 1 2 YES INACTIVE /u01/app/oracle/oradata/ibmggn/redo02.log 200
3 1 3 YES INACTIVE /u01/app/oracle/oradata/ibmggn/redo03.log 200
SQL>
select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'stdby_'||
regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||
''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
union all
select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'stdby_redo0'||(select max(group#)+1 from v$log)||'.rdo'||
''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
and rownum <=2
/
SQL> select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'stdby_'||
regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||
''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
2 3 4 5 6 7 union all
select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'stdby_redo0'||(select max(group#)+1 from v$log)||'.rdo'||
8 9 10 ''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
11 12 13 and rownum <=2
/
14
Create Standby redo
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database add standby logfile '/u01/app/oracle/oradata/ibmggn/stdby_redo03.log' size 209715200;
alter database add standby logfile '/u01/app/oracle/oradata/ibmggn/stdby_redo02.log' size 209715200;
alter database add standby logfile '/u01/app/oracle/oradata/ibmggn/stdby_redo01.log' size 209715200;
alter database add standby logfile '/u01/app/oracle/oradata/ibmggn/stdby_redo04.rdo' size 209715200;
alter database add standby logfile '/u01/app/oracle/oradata/ibmggn/stdby_redo04.rdo' size 209715200;
SQL>
select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
GROUP# Size in MB
---------- ----------
6 200
4 200
5 200
7 200
SQL>
select bytes/1024/1024"Size in MB" from v$standby_log
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------------------------------------- --- ----------
3 ONLINE /u01/app/oracle/oradata/ibmggn/redo03.log NO 0
2 ONLINE /u01/app/oracle/oradata/ibmggn/redo02.log NO 0
1 ONLINE /u01/app/oracle/oradata/ibmggn/redo01.log NO 0
4 STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo1.log NO 0
5 STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo2.log NO 0
6 STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo3.log NO 0
7 STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo4.log NO 0
7 rows selected.
SQL>
alter system set log_archive_config='dg_config=(ibmggn,ibmnoida)' scope=both;
alter system set log_archive_dest_2='service=ibmnoida noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=ibmnoida' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
ALTER SYSTEM SET FAL_SERVER=ibmnoida scope=both;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;
col value for a110
col name for a35
select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','fal_client','db_file_name_convert','log_file_name_convert', 'standby_file_management','control_files','db_recovery_file_dest');
SQL> col value for a110
col name for a35
SQL> SQL>
select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile','log_archive_format','log_arcSQL> hive_max_processes','fal_server','fal_client','db_file_name_convert','log_file_name_convert', 'standby_file_management','control_files','db_recovery_file_dest');
NAME VALUE
----------------------------------- --------------------------------------------------------------------------------------------------------------
control_files /u01/app/oracle/oradata/ibmggn/control01.ctl, /u01/app/oracle/fast_recovery_area/ibmggn/control02.ctl
db_file_name_convert
log_file_name_convert
log_archive_dest_1
log_archive_dest_2 service=ibmnoida noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=ibmnoida
log_archive_dest_state_1 enable
log_archive_dest_state_2 ENABLE
fal_client
fal_server IBMNOIDA
log_archive_config dg_config=(ibmggn,ibmnoida)
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 4
db_recovery_file_dest /u01/app/oracle/fast_recovery_area/ibmggn
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name ibmggn
db_unique_name ibmggn
17 rows selected.
SQL>
create test table and insert few records .
do some switch log
SQL>
SQL>
SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10
Next log sequence to archive 12
Current log sequence 12
SQL>
[oracle@ip-172-31-21-233 backup]$ pwd
/u01/app/oracle/backup
[oracle@ip-172-31-21-233 backup]$
run{
allocate channel d1 type disk MAXPIECESIZE 2G;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup format '/u01/app/oracle/backup/db_t%t_s%s_p%p' database;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup format '/u01/app/oracle/backup/ar_t%t_s%s_p%p' archivelog all;
release channel d1;
}
RMAN>
RMAN>
RMAN> run{
allocate channel d1 type disk MAXPIECESIZE 2G;
sql 'alter system archive log current';
sql 'alter system archive log current';
2> 3> 4> 5> backup format '/u01/app/oracle/backup/db_t%t_s%s_p%p' database;
sql 'alter system archive log current';
6> 7> sql 'alter system archive log current';
backup format '/u01/app/oracle/backup/ar_t%t_s%s_p%p' archivelog all;
release channel d1;
}8> 9> 10>
allocated channel: d1
channel d1: SID=33 device type=DISK
sql statement: alter system archive log current
sql statement: alter system archive log current
Starting backup at 14-DEC-22
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ibmggn/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ibmggn/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ibmggn/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ibmggn/users01.dbf
channel d1: starting piece 1 at 14-DEC-22
channel d1: finished piece 1 at 14-DEC-22
piece handle=/u01/app/oracle/backup/db_t1123401582_s2_p1 tag=TAG20221214T075941 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:15
Finished backup at 14-DEC-22
Starting Control File and SPFILE Autobackup at 14-DEC-22
piece handle=/u01/app/oracle/fast_recovery_area/ibmggn/IBMGGN/autobackup/2022_12_14/o1_mf_s_1123401597_ksm0mx79_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-DEC-22
sql statement: alter system archive log current
sql statement: alter system archive log current
Starting backup at 14-DEC-22
current log archived
channel d1: starting archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=1123399726
input archived log thread=1 sequence=2 RECID=2 STAMP=1123399727
input archived log thread=1 sequence=3 RECID=3 STAMP=1123399728
input archived log thread=1 sequence=4 RECID=4 STAMP=1123400453
input archived log thread=1 sequence=5 RECID=5 STAMP=1123400486
input archived log thread=1 sequence=6 RECID=6 STAMP=1123401190
input archived log thread=1 sequence=7 RECID=7 STAMP=1123401191
input archived log thread=1 sequence=8 RECID=8 STAMP=1123401193
input archived log thread=1 sequence=9 RECID=9 STAMP=1123401338
input archived log thread=1 sequence=10 RECID=10 STAMP=1123401339
input archived log thread=1 sequence=11 RECID=11 STAMP=1123401341
input archived log thread=1 sequence=12 RECID=12 STAMP=1123401580
input archived log thread=1 sequence=13 RECID=13 STAMP=1123401581
input archived log thread=1 sequence=14 RECID=14 STAMP=1123401598
input archived log thread=1 sequence=15 RECID=15 STAMP=1123401598
input archived log thread=1 sequence=16 RECID=16 STAMP=1123401601
channel d1: starting piece 1 at 14-DEC-22
channel d1: finished piece 1 at 14-DEC-22
piece handle=/u01/app/oracle/backup/ar_t1123401601_s4_p1 tag=TAG20221214T080001 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-DEC-22
Starting Control File and SPFILE Autobackup at 14-DEC-22
piece handle=/u01/app/oracle/fast_recovery_area/ibmggn/IBMGGN/autobackup/2022_12_14/o1_mf_s_1123401602_ksm0n2m8_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-DEC-22
released channel: d1
RMAN>
RMAN>
Backup controlfile for standby
RMAN>
run
{
allocate channel c1 type disk;
backup format '/u01/app/oracle/backup/standby_controlfile_t%t_s%s_p%p' current controlfile for standby;
}
RMAN> run
{
allocate channel c1 type disk;
backup format '/u01/app/oracle/backup/standby_controlfile_t%t_s%s_p%p' current controlfile for standby;
}2> 3> 4> 5>
allocated channel: c1
channel c1: SID=33 device type=DISK
Starting backup at 14-DEC-22
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 14-DEC-22
channel c1: finished piece 1 at 14-DEC-22
piece handle=/u01/app/oracle/backup/standby_controlfile_t1123401671_s6_p1 tag=TAG20221214T080111 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-DEC-22
Starting Control File and SPFILE Autobackup at 14-DEC-22
piece handle=/u01/app/oracle/fast_recovery_area/ibmggn/IBMGGN/autobackup/2022_12_14/o1_mf_s_1123401673_ksm0p9fr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-DEC-22
released channel: c1
RMAN>
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/app/oracle/backup/standby_controlfile.ctl';
Database altered.
CREATE PFILE ='/u01/app/oracle/backup/india.ora' FROM SPFILE;
Transfer backup files from source database and password file
===================================================================
[oracle@ip-172-31-21-233 oracle]$
[oracle@ip-172-31-21-233 oracle]$ scp -r backup/ oracle@172.31.19.40:/u01/app/oracle
oracle@172.31.19.40's password:
db_t1123401582_s2_p1 100% 1024MB 74.8MB/s 00:13
ar_t1123401601_s4_p1 100% 152MB 120.3MB/s 00:01
standby_controlfile_t1123401671_s6_p1 100% 10MB 98.6MB/s 00:00
standby_controlfile.ctl 100% 10MB 98.5MB/s 00:00
ibmnoida.ora_bkp_primary_file 100% 1431 1.2MB/s 00:00
ibmnoida.ora 100% 1012 750.5KB/s 00:00
[oracle@ip-172-31-21-233 oracle]
On Target DB
=====================
DB_NAME : ibmggn
DB-Unique_name = ibmnoida
Database version : 19.3.0
OS : RHE 8.7
mkdir -p /u01/app/oracle/admin/ibmggn/adump
mkdir -p /u01/app/oracle/oradata/ibmggn/
mkdir -p /u01/app/oracle/fast_recovery_area/ibmggn
[oracle@ip-172-31-19-40 ~]$
[oracle@ip-172-31-19-40 ~]$ mkdir -p /u01/app/oracle/admin/ibmggn/adump
[oracle@ip-172-31-19-40 ~]$ mkdir -p /u01/app/oracle/oradata/ibmggn/
[oracle@ip-172-31-19-40 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/ibmggn
[oracle@ip-172-31-19-40 ~]$
startup database in nomount mode
select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_2','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','fal_client','db_file_name_convert','log_file_name_convert', 'standby_file_management','control_files','db_recovery_file_dest');
SQL> /
NAME VALUE
----------------------------------- --------------------------------------------------------------------------------------------------------------
control_files /u01/app/oracle/oradata/ibmggn/control01.ctl, /u01/app/oracle/fast_recovery_area/ibmggn/control02.ctl
db_file_name_convert
log_file_name_convert
log_archive_dest_1
log_archive_dest_2 service=ibmggn noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=ibmggn
log_archive_dest_state_1 enable
log_archive_dest_state_2 ENABLE
fal_client
fal_server IBMGGN
log_archive_config dg_config=(ibmggn,ibmnoida)
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 4
db_recovery_file_dest /u01/app/oracle/fast_recovery_area/ibmggn
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name ibmggn
db_unique_name ibmnoida
17 rows selected.
SQL>
Source DB tnsnames.ora
IBMGGN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ibmggn)
)
)
IBMNOIDA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.0.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ibmnoida)
)
)
chnage listeener.ora on target server
-----------------------------------------
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.0.2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(SID_DESC =
(GLOBAL_DBNAME = ibmnoida)
(ORACLE_HOME = /u01/app/oracle/product/19.0/dbhome1 )
(SID_NAME = ibmnoida)
)
)
)
from source server
-------------------------
[oracle@ip-172-31-21-233 admin]$ tnsping ibmnoida
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 14-DEC-2022 09:53:11
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0/dbhome1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.0.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ibmnoida)))
OK (0 msec)
[oracle@ip-172-31-21-233 admin]$
Restore target database
--------------------------
Backup location :
/u01/app/oracle/backup
[oracle@ip-172-31-19-40 backup]$ ls -lrt
total 1225948
-rw-r----- 1 oracle oinstall 1074257920 Dec 14 08:12 db_t1123401582_s2_p1
-rw-r----- 1 oracle oinstall 159834624 Dec 14 08:12 ar_t1123401601_s4_p1
-rw-r----- 1 oracle oinstall 10665984 Dec 14 08:12 standby_controlfile_t1123401671_s6_p1
-rw-r----- 1 oracle oinstall 10600448 Dec 14 08:12 standby_controlfile.ctl
-rw-r--r-- 1 oracle oinstall 1431 Dec 14 08:12 ibmnoida.ora_bkp_primary_file
-rw-r--r-- 1 oracle oinstall 1012 Dec 14 08:12 ibmnoida.ora
[oracle@ip-172-31-19-40 backup]$
[oracle@ip-172-31-19-40 backup]$ pwd
restore controlfile from '/u01/app/oracle/backup/standby_controlfile_t1123401671_s6_p1';
[oracle@ip-172-31-19-40 backup]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Dec 14 10:05:05 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: IBMGGN (not mounted)
RMAN> restore controlfile from '/u01/app/oracle/backup/standby_controlfile_t1123401671_s6_p1';
Starting restore at 14-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=251 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ibmggn/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ibmggn/control02.ctl
Finished restore at 14-DEC-22
RMAN>
RMAN>
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
RMAN>
RMAN> report schema;
Starting implicit crosscheck backup at 14-DEC-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=251 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 14-DEC-22
Starting implicit crosscheck copy at 14-DEC-22
using channel ORA_DISK_1
Finished implicit crosscheck copy at 14-DEC-22
searching for all files in the recovery area
cataloging files...
no files cataloged
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name IBMNOIDA
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /u01/app/oracle/oradata/ibmggn/system01.dbf
3 0 SYSAUX *** /u01/app/oracle/oradata/ibmggn/sysaux01.dbf
4 0 UNDOTBS1 *** /u01/app/oracle/oradata/ibmggn/undotbs01.dbf
7 0 USERS *** /u01/app/oracle/oradata/ibmggn/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ibmggn/temp01.dbf
RMAN>
RMAN>
RMAN> catalog start with '/u01/app/oracle/backup';
searching for all files that match the pattern /u01/app/oracle/backup
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/backup/standby_controlfile_t1123401671_s6_p1
File Name: /u01/app/oracle/backup/standby_controlfile.ctl
File Name: /u01/app/oracle/backup/ibmnoida.ora_bkp_primary_file
File Name: /u01/app/oracle/backup/ibmnoida.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: /u01/app/oracle/backup/standby_controlfile_t1123401671_s6_p1
File Name: /u01/app/oracle/backup/standby_controlfile.ctl
List of Files Which Were Not Cataloged
=======================================
File Name: /u01/app/oracle/backup/ibmnoida.ora_bkp_primary_file
RMAN-07517: Reason: The file header is corrupted
File Name: /u01/app/oracle/backup/ibmnoida.ora
RMAN-07517: Reason: The file header is corrupted
RMAN>
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4 152.43M DISK 00:00:00 14-DEC-22
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20221214T080001
Piece Name: /u01/app/oracle/backup/ar_t1123401601_s4_p1
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 1408558 14-DEC-22 1421197 14-DEC-22
1 2 1421197 14-DEC-22 1421200 14-DEC-22
1 3 1421200 14-DEC-22 1421203 14-DEC-22
1 4 1421203 14-DEC-22 1421591 14-DEC-22
1 5 1421591 14-DEC-22 1421629 14-DEC-22
1 6 1421629 14-DEC-22 1423114 14-DEC-22
1 7 1423114 14-DEC-22 1423119 14-DEC-22
1 8 1423119 14-DEC-22 1423127 14-DEC-22
1 9 1423127 14-DEC-22 1424503 14-DEC-22
1 10 1424503 14-DEC-22 1424514 14-DEC-22
1 11 1424514 14-DEC-22 1424524 14-DEC-22
1 12 1424524 14-DEC-22 1425054 14-DEC-22
1 13 1425054 14-DEC-22 1425065 14-DEC-22
1 14 1425065 14-DEC-22 1425129 14-DEC-22
1 15 1425129 14-DEC-22 1425140 14-DEC-22
1 16 1425140 14-DEC-22 1425152 14-DEC-22
RMAN>
RMAN> restore database;
Starting restore at 14-DEC-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ibmggn/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ibmggn/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ibmggn/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ibmggn/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/db_t1123401582_s2_p1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/db_t1123401582_s2_p1 tag=TAG20221214T075941
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 14-DEC-22
RMAN>
RMAN> recover database;
Starting recover at 14-DEC-22
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=14
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=15
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=16
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/ar_t1123401601_s4_p1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/ar_t1123401601_s4_p1 tag=TAG20221214T080001
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/ibmggn/IBMNOIDA/archivelog/2022_12_14/o1_mf_1_14_ksm93891_.arc thread=1 sequence=14
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ibmggn/IBMNOIDA/archivelog/2022_12_14/o1_mf_1_14_ksm93891_.arc RECID=1 STAMP=1123410280
archived log file name=/u01/app/oracle/fast_recovery_area/ibmggn/IBMNOIDA/archivelog/2022_12_14/o1_mf_1_15_ksm938b9_.arc thread=1 sequence=15
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ibmggn/IBMNOIDA/archivelog/2022_12_14/o1_mf_1_15_ksm938b9_.arc RECID=3 STAMP=1123410280
archived log file name=/u01/app/oracle/fast_recovery_area/ibmggn/IBMNOIDA/archivelog/2022_12_14/o1_mf_1_16_ksm9389n_.arc thread=1 sequence=16
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ibmggn/IBMNOIDA/archivelog/2022_12_14/o1_mf_1_16_ksm9389n_.arc RECID=2 STAMP=1123410280
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-DEC-22
RMAN>
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ------------------------------------------------------------------------------------------ --- ----------
3 ONLINE /u01/app/oracle/oradata/ibmggn/redo03.log NO 0
2 ONLINE /u01/app/oracle/oradata/ibmggn/redo02.log NO 0
1 ONLINE /u01/app/oracle/oradata/ibmggn/redo01.log NO 0
4 STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo1.log NO 0
5 STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo2.log NO 0
6 STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo3.log NO 0
7 STANDBY /u01/app/oracle/oradata/ibmggn/stbyredo4.log NO 0
7 rows selected.
SQL>
SQL>
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
SQL>
SQL>
alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=10.1.0.2)(PORT=1521))' scope=both;
check passwordfile on target
SQL>
SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE
--------- -------------------- ---------------- ------------------------------ --------------------
IBMGGN MOUNTED PHYSICAL STANDBY ibmnoida MAXIMUM PERFORMANCE
SQL>
on source
-------------------
SQL>
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 8 1 4
DGRD ALLOCATED 0 0 0 0
ARCH OPENING 1 16 1 2
ARCH CLOSING 1 17 43008 67
ARCH CLOSING 1 11 1 10
DGRD ALLOCATED 0 0 0 0
LNS CONNECTED 0 0 0 0
LNS WRITING 1 18 81 1
DGRD ALLOCATED 0 0 0 0
10 rows selected.
SQL>
on target
---------------
SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
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 IDLE 1 0 0 0
RFS IDLE 1 18 76 1
8 rows selected.
SQL>
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
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 IDLE 1 0 0 0
RFS IDLE 1 18 76 1
8 rows selected.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
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 IDLE 1 0 0 0
RFS IDLE 1 18 233 6
MRP0 WAIT_FOR_LOG 1 18 0 0
9 rows selected.
SQL>
on source
=================
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- -------------------- -------------------- ---------
1 14-DEC-2022 07:12:52 14-DEC-2022 07:28:45 NO
2 14-DEC-2022 07:28:45 14-DEC-2022 07:28:47 NO
3 14-DEC-2022 07:28:47 14-DEC-2022 07:28:48 NO
4 14-DEC-2022 07:28:48 14-DEC-2022 07:40:53 NO
5 14-DEC-2022 07:40:53 14-DEC-2022 07:41:26 NO
6 14-DEC-2022 07:41:26 14-DEC-2022 07:53:10 NO
7 14-DEC-2022 07:53:10 14-DEC-2022 07:53:11 NO
8 14-DEC-2022 07:53:11 14-DEC-2022 07:53:13 NO
9 14-DEC-2022 07:53:13 14-DEC-2022 07:55:38 NO
10 14-DEC-2022 07:55:38 14-DEC-2022 07:55:39 NO
11 14-DEC-2022 07:55:39 14-DEC-2022 07:55:41 NO
12 14-DEC-2022 07:55:41 14-DEC-2022 07:59:40 NO
13 14-DEC-2022 07:59:40 14-DEC-2022 07:59:41 NO
14 14-DEC-2022 07:59:41 14-DEC-2022 07:59:58 NO
15 14-DEC-2022 07:59:58 14-DEC-2022 07:59:58 NO
16 14-DEC-2022 07:59:58 14-DEC-2022 08:00:01 NO
17 14-DEC-2022 08:00:01 14-DEC-2022 10:51:47 NO
17 14-DEC-2022 08:00:01 14-DEC-2022 10:51:47 YES
18 rows selected.
SQL>
on target
==============
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
14 14-DEC-22 14-DEC-22 YES
15 14-DEC-22 14-DEC-22 YES
16 14-DEC-22 14-DEC-22 YES
17 14-DEC-22 14-DEC-22 YES
SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE
--------- -------------------- ---------------- ------------------------------ --------------------
IBMGGN MOUNTED PHYSICAL STANDBY ibmnoida MAXIMUM PERFORMANCE
SQL>
on source
-----------------
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE
--------- -------------------- ---------------- ------------------------------ --------------------
IBMGGN READ WRITE PRIMARY ibmggn MAXIMUM PERFORMANCE
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- -------------------- -------------------- ---------
1 14-DEC-2022 07:12:52 14-DEC-2022 07:28:45 NO
2 14-DEC-2022 07:28:45 14-DEC-2022 07:28:47 NO
3 14-DEC-2022 07:28:47 14-DEC-2022 07:28:48 NO
4 14-DEC-2022 07:28:48 14-DEC-2022 07:40:53 NO
5 14-DEC-2022 07:40:53 14-DEC-2022 07:41:26 NO
6 14-DEC-2022 07:41:26 14-DEC-2022 07:53:10 NO
7 14-DEC-2022 07:53:10 14-DEC-2022 07:53:11 NO
8 14-DEC-2022 07:53:11 14-DEC-2022 07:53:13 NO
9 14-DEC-2022 07:53:13 14-DEC-2022 07:55:38 NO
10 14-DEC-2022 07:55:38 14-DEC-2022 07:55:39 NO
11 14-DEC-2022 07:55:39 14-DEC-2022 07:55:41 NO
12 14-DEC-2022 07:55:41 14-DEC-2022 07:59:40 NO
13 14-DEC-2022 07:59:40 14-DEC-2022 07:59:41 NO
14 14-DEC-2022 07:59:41 14-DEC-2022 07:59:58 NO
15 14-DEC-2022 07:59:58 14-DEC-2022 07:59:58 NO
16 14-DEC-2022 07:59:58 14-DEC-2022 08:00:01 NO
17 14-DEC-2022 08:00:01 14-DEC-2022 10:51:47 NO
17 14-DEC-2022 08:00:01 14-DEC-2022 10:51:47 YES
18 14-DEC-2022 10:51:47 14-DEC-2022 10:57:32 NO
18 14-DEC-2022 10:51:47 14-DEC-2022 10:57:32 YES
19 14-DEC-2022 10:57:32 14-DEC-2022 10:57:34 NO
19 14-DEC-2022 10:57:32 14-DEC-2022 10:57:34 YES
22 rows selected.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL>
On target
================
SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE
--------- -------------------- ---------------- ------------------------------ --------------------
IBMGGN MOUNTED PHYSICAL STANDBY ibmnoida MAXIMUM PERFORMANCE
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
BANNER_LEGACY CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
14 14-DEC-22 14-DEC-22 YES
15 14-DEC-22 14-DEC-22 YES
16 14-DEC-22 14-DEC-22 YES
17 14-DEC-22 14-DEC-22 YES
18 14-DEC-22 14-DEC-22 YES
19 14-DEC-22 14-DEC-22 YES
6 rows selected.
SQL>
######################################################################################################3
create a user and sample table on source
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 18
Next log sequence to archive 20
Current log sequence 20
SQL>
SQL>
SQL>
SQL> create user test_after_sync identified by test;
User created.
SQL> grant create session,dba to test_after_sync;
Grant succeeded.
SQL> conn test/test
Connected.
SQL>
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
-------------------------------------------------------------------------------------------------------------------------------- ------- ----------
TEST TABLE
SQL>
SQL>
SQL> create table anurag as select * from test;
Table created.
SQL>
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>
SQL>
SQL> archive log list
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
SQL>
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- -------------------- -------------------- ---------
1 14-DEC-2022 07:12:52 14-DEC-2022 07:28:45 NO
2 14-DEC-2022 07:28:45 14-DEC-2022 07:28:47 NO
3 14-DEC-2022 07:28:47 14-DEC-2022 07:28:48 NO
4 14-DEC-2022 07:28:48 14-DEC-2022 07:40:53 NO
5 14-DEC-2022 07:40:53 14-DEC-2022 07:41:26 NO
6 14-DEC-2022 07:41:26 14-DEC-2022 07:53:10 NO
7 14-DEC-2022 07:53:10 14-DEC-2022 07:53:11 NO
8 14-DEC-2022 07:53:11 14-DEC-2022 07:53:13 NO
9 14-DEC-2022 07:53:13 14-DEC-2022 07:55:38 NO
10 14-DEC-2022 07:55:38 14-DEC-2022 07:55:39 NO
11 14-DEC-2022 07:55:39 14-DEC-2022 07:55:41 NO
12 14-DEC-2022 07:55:41 14-DEC-2022 07:59:40 NO
13 14-DEC-2022 07:59:40 14-DEC-2022 07:59:41 NO
14 14-DEC-2022 07:59:41 14-DEC-2022 07:59:58 NO
15 14-DEC-2022 07:59:58 14-DEC-2022 07:59:58 NO
16 14-DEC-2022 07:59:58 14-DEC-2022 08:00:01 NO
17 14-DEC-2022 08:00:01 14-DEC-2022 10:51:47 NO
17 14-DEC-2022 08:00:01 14-DEC-2022 10:51:47 YES
18 14-DEC-2022 10:51:47 14-DEC-2022 10:57:32 NO
18 14-DEC-2022 10:51:47 14-DEC-2022 10:57:32 YES
19 14-DEC-2022 10:57:32 14-DEC-2022 10:57:34 NO
19 14-DEC-2022 10:57:32 14-DEC-2022 10:57:34 YES
20 14-DEC-2022 10:57:34 14-DEC-2022 11:03:55 NO
20 14-DEC-2022 10:57:34 14-DEC-2022 11:03:55 YES
21 14-DEC-2022 11:03:55 14-DEC-2022 11:03:56 NO
21 14-DEC-2022 11:03:55 14-DEC-2022 11:03:56 YES
22 14-DEC-2022 11:03:56 14-DEC-2022 11:03:59 NO
22 14-DEC-2022 11:03:56 14-DEC-2022 11:03:59 YES
23 14-DEC-2022 11:03:59 14-DEC-2022 11:04:02 NO
23 14-DEC-2022 11:03:59 14-DEC-2022 11:04:02 YES
24 14-DEC-2022 11:04:02 14-DEC-2022 11:04:03 NO
24 14-DEC-2022 11:04:02 14-DEC-2022 11:04:03 YES
32 rows selected.
SQL>
on target db
------------------
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL>
SQL>
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- -------------------- -------------------- ---------
14 14-DEC-2022 07:59:41 14-DEC-2022 07:59:58 YES
15 14-DEC-2022 07:59:58 14-DEC-2022 07:59:58 YES
16 14-DEC-2022 07:59:58 14-DEC-2022 08:00:01 YES
17 14-DEC-2022 08:00:01 14-DEC-2022 10:51:47 YES
18 14-DEC-2022 10:51:47 14-DEC-2022 10:57:32 YES
19 14-DEC-2022 10:57:32 14-DEC-2022 10:57:34 YES
20 14-DEC-2022 10:57:34 14-DEC-2022 11:03:55 YES
21 14-DEC-2022 11:03:55 14-DEC-2022 11:03:56 YES
22 14-DEC-2022 11:03:56 14-DEC-2022 11:03:59 YES
23 14-DEC-2022 11:03:59 14-DEC-2022 11:04:02 YES
24 14-DEC-2022 11:04:02 14-DEC-2022 11:04:03 YES
11 rows selected.
SQL>
Now activate standby
--------------------------
stop the mrp
alter database recover managed standby database cancel;
SQL>
SQL> alter database recover managed standby database cancel;
Database altered.
SQL>
shut immediate;
startup mount;
alter database activate standby database;
alter database flashback on;
create restore point BEFORE_UPGRADE_DB guarantee flashback database;
select * from v$restore_point;
select count(*) from dba_objects where status='INVALID';
startup upgrade;
[oracle@ip-172-31-19-40 dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 14 11:14:52 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1895822688 bytes
Fixed Size 8897888 bytes
Variable Size 436207616 bytes
Database Buffers 1442840576 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL>
SQL>
SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
PROTECTION_MODE
--------------------
IBMGGN MOUNTED PHYSICAL STANDBY ibmnoida
MAXIMUM PERFORMANCE
SQL>
SQL> set lines 200 pages 500
SQL>
SQL>
SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE
--------- -------------------- ---------------- ------------------------------ --------------------
IBMGGN MOUNTED PHYSICAL STANDBY ibmnoida MAXIMUM PERFORMANCE
SQL>
SQL>
SQL>
SQL> alter database activate standby database;
Database altered.
SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE
--------- -------------------- ---------------- ------------------------------ --------------------
IBMGGN MOUNTED PRIMARY ibmnoida MAXIMUM PERFORMANCE
SQL> select count(*) from dba_objects where status='INVALID';
select count(*) from dba_objects where status='INVALID'
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only
SQL>
SQL>
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ip-172-31-19-40 dbs]$
[oracle@ip-172-31-19-40 dbs]$
[oracle@ip-172-31-19-40 dbs]$
[oracle@ip-172-31-19-40 dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 14 11:25:20 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 1895822688 bytes
Fixed Size 8897888 bytes
Variable Size 436207616 bytes
Database Buffers 1442840576 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
PROTECTION_MODE
--------------------
IBMGGN READ WRITE PRIMARY ibmnoida
MAXIMUM PERFORMANCE
SQL> set lines 200 pages 500
SQL>
SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE
--------- -------------------- ---------------- ------------------------------ --------------------
IBMGGN READ WRITE PRIMARY ibmnoida MAXIMUM PERFORMANCE
SQL>
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
2319
SQL>
[oracle@ip-172-31-19-40 dbs]$
[oracle@ip-172-31-19-40 dbs]$
[oracle@ip-172-31-19-40 dbs]$ cd ..
[oracle@ip-172-31-19-40 dbhome1]$ cd bin/
[oracle@ip-172-31-19-40 bin]$
[oracle@ip-172-31-19-40 bin]$ pwd
/u01/app/oracle/product/19.0/dbhome1/bin
[oracle@ip-172-31-19-40 bin]$
[oracle@ip-172-31-19-40 bin]$ ls -lrt dbu
dbua dbupgrade
[oracle@ip-172-31-19-40 bin]$ ls -lrt dbupgrade
-rwxr-x--- 1 oracle oinstall 3136 Apr 17 2019 dbupgrade
[oracle@ip-172-31-19-40 bin]$
[oracle@ip-172-31-19-40 bin]$
time : 14/12/2015 16:58 PM
==================================
[oracle@ip-172-31-19-40 bin]$
[oracle@ip-172-31-19-40 bin]$ ./dbupgrade
Argument list for [/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]
/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0/dbhome1]
/u01/app/oracle/product/19.0/dbhome1/bin/orabasehome = [/u01/app/oracle/product/19.0/dbhome1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0/dbhome1]
Analyzing file /u01/app/oracle/product/19.0/dbhome1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20221214112732]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20221214112732/catupgrd_catcon_3105.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20221214112732/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20221214112732/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = ibmnoida
DataBase Version = 12.2.0.1.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/catupgrd_catcon_3105.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742]
Parallel SQL Process Count = 4
Components in [ibmnoida]
Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM RAC WK]
------------------------------------------------------
Phases [0-107] Start Time:[2022_12_14 11:27:54]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [ibmnoida] Files:1
upgrade log
-------------
[oracle@ip-172-31-19-40 bin]$
[oracle@ip-172-31-19-40 bin]$ ./dbupgrade
Argument list for [/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]
/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0/dbhome1]
/u01/app/oracle/product/19.0/dbhome1/bin/orabasehome = [/u01/app/oracle/product/19.0/dbhome1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0/dbhome1]
Analyzing file /u01/app/oracle/product/19.0/dbhome1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20221214112732]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20221214112732/catupgrd_catcon_3105.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20221214112732/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20221214112732/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = ibmnoida
DataBase Version = 12.2.0.1.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/catupgrd_catcon_3105.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742]
Parallel SQL Process Count = 4
Components in [ibmnoida]
Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM RAC WK]
------------------------------------------------------
Phases [0-107] Start Time:[2022_12_14 11:27:54]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [ibmnoida] Files:1
Time: 20s
*************** Catalog Core SQL ***************
Serial Phase #:1 [ibmnoida] Files:5
Time: 29s
Restart Phase #:2 [ibmnoida] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [ibmnoida] Files:19 Time: 14s
Restart Phase #:4 [ibmnoida] Files:1 Time: 3s
************* Catalog Final Scripts ************
Serial Phase #:5 [ibmnoida] Files:7 Time: 13s
***************** Catproc Start ****************
Serial Phase #:6 [ibmnoida] Files:1 Time: 10s
***************** Catproc Types ****************
Serial Phase #:7 [ibmnoida] Files:2 Time: 10s
Restart Phase #:8 [ibmnoida] Files:1 Time: 1s
**************** Catproc Tables ****************
Parallel Phase #:9 [ibmnoida] Files:67 Time: 20s
Restart Phase #:10 [ibmnoida] Files:1 Time: 4s
************* Catproc Package Specs ************
Serial Phase #:11 [ibmnoida] Files:1 Time: 54s
Restart Phase #:12 [ibmnoida] Files:1 Time: 2s
************** Catproc Procedures **************
Parallel Phase #:13 [ibmnoida] Files:94 Time: 7s
Restart Phase #:14 [ibmnoida] Files:1 Time: 4s
Parallel Phase #:15 [ibmnoida] Files:120 Time: 13s
Restart Phase #:16 [ibmnoida] Files:1 Time: 2s
Serial Phase #:17 [ibmnoida] Files:22
Time: 4s
Restart Phase #:18 [ibmnoida] Files:1 Time: 2s
***************** Catproc Views ****************
Parallel Phase #:19 [ibmnoida] Files:32 Time: 15s
Restart Phase #:20 [ibmnoida] Files:1 Time: 2s
Serial Phase #:21 [ibmnoida] Files:3 Time: 11s
Restart Phase #:22 [ibmnoida] Files:1 Time: 1s
Parallel Phase #:23 [ibmnoida] Files:25 Time: 108s
Restart Phase #:24 [ibmnoida] Files:1 Time: 2s
Parallel Phase #:25 [ibmnoida] Files:12
Time: 77s
Restart Phase #:26 [ibmnoida] Files:1 Time: 1s
Serial Phase #:27 [ibmnoida] Files:1 Time: 0s
Serial Phase #:28 [ibmnoida] Files:3 Time: 4s
Serial Phase #:29 [ibmnoida] Files:1 Time: 0s
Restart Phase #:30 [ibmnoida] Files:1 Time: 2s
*************** Catproc CDB Views **************
Serial Phase #:31 [ibmnoida] Files:1 Time: 2s
Restart Phase #:32 [ibmnoida] Files:1 Time: 1s
Serial Phase #:34 [ibmnoida] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [ibmnoida] Files:293 Time: 17s
Serial Phase #:36 [ibmnoida] Files:1 Time: 0s
Restart Phase #:37 [ibmnoida] Files:1 Time: 2s
Serial Phase #:38 [ibmnoida] Files:6 Time: 6s
Restart Phase #:39 [ibmnoida] Files:1 Time: 2s
*************** Catproc DataPump ***************
Serial Phase #:40 [ibmnoida] Files:3 Time: 39s
Restart Phase #:41 [ibmnoida] Files:1 Time: 3s
****************** Catproc SQL *****************
Parallel Phase #:42 [ibmnoida] Files:13 Time: 80s
Restart Phase #:43 [ibmnoida] Files:1 Time: 2s
Parallel Phase #:44 [ibmnoida] Files:11 Time: 7s
Restart Phase #:45 [ibmnoida] Files:1 Time: 3s
Parallel Phase #:46 [ibmnoida] Files:3 Time: 3s
Restart Phase #:47 [ibmnoida] Files:1 Time: 2s
************* Final Catproc scripts ************
Serial Phase #:48 [ibmnoida] Files:1 Time: 6s
Restart Phase #:49 [ibmnoida] Files:1 Time: 4s
************** Final RDBMS scripts *************
Serial Phase #:50 [ibmnoida] Files:1 Time: 4s
************ Upgrade Component Start ***********
Serial Phase #:51 [ibmnoida] Files:1 Time: 2s
Restart Phase #:52 [ibmnoida] Files:1 Time: 1s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [ibmnoida] Files:2
Time: 203s
***************** Upgrading XDB ****************
Restart Phase #:54 [ibmnoida] Files:1 Time: 3s
Serial Phase #:56 [ibmnoida] Files:3 Time: 8s
Serial Phase #:57 [ibmnoida] Files:3 Time: 5s
Parallel Phase #:58 [ibmnoida] Files:10 Time: 5s
Parallel Phase #:59 [ibmnoida] Files:25 Time: 7s
Serial Phase #:60 [ibmnoida] Files:4 Time: 8s
Serial Phase #:61 [ibmnoida] Files:1 Time: 0s
Serial Phase #:62 [ibmnoida] Files:32 Time: 6s
Serial Phase #:63 [ibmnoida] Files:1 Time: 0s
Parallel Phase #:64 [ibmnoida] Files:6 Time: 6s
Serial Phase #:65 [ibmnoida] Files:2 Time: 18s
Serial Phase #:66 [ibmnoida] Files:3 Time: 26s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [ibmnoida] Files:1 Time: 3s
Serial Phase #:69 [ibmnoida] Files:1 Time: 4s
Parallel Phase #:70 [ibmnoida] Files:2
Time: 26s
Restart Phase #:71 [ibmnoida] Files:1 Time: 3s
Parallel Phase #:72 [ibmnoida] Files:2 Time: 4s
Serial Phase #:73 [ibmnoida] Files:2 Time: 4s
***************** Upgrading SDO ****************
Restart Phase #:74 [ibmnoida] Files:1 Time: 3s
Serial Phase #:76 [ibmnoida] Files:1 Time: 33s
Serial Phase #:77 [ibmnoida] Files:2 Time: 5s
Restart Phase #:78 [ibmnoida] Files:1 Time: 3s
Serial Phase #:79 [ibmnoida] Files:1 Time: 44s
Restart Phase #:80 [ibmnoida] Files:1 Time: 3s
Parallel Phase #:81 [ibmnoida] Files:3 Time: 38s
Restart Phase #:82 [ibmnoida] Files:1 Time: 2s
Serial Phase #:83 [ibmnoida] Files:1 Time: 7s
Restart Phase #:84 [ibmnoida] Files:1 Time: 2s
Serial Phase #:85 [ibmnoida] Files:1 Time: 10s
Restart Phase #:86 [ibmnoida] Files:1 Time: 3s
Parallel Phase #:87 [ibmnoida] Files:4 Time: 58s
Restart Phase #:88 [ibmnoida] Files:1 Time: 2s
Serial Phase #:89 [ibmnoida] Files:1 Time: 4s
Restart Phase #:90 [ibmnoida] Files:1 Time: 2s
Serial Phase #:91 [ibmnoida] Files:2 Time: 10s
Restart Phase #:92 [ibmnoida] Files:1 Time: 3s
Serial Phase #:93 [ibmnoida] Files:1 Time: 2s
Restart Phase #:94 [ibmnoida] Files:1 Time: 2s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [ibmnoida] Files:1 Time: 10s
Restart Phase #:96 [ibmnoida] Files:1 Time: 4s
*********** Final Component scripts ***********
Serial Phase #:97 [ibmnoida] Files:1
Time: 3s
************* Final Upgrade scripts ************
Serial Phase #:98 [ibmnoida] Files:1
Time: 175s
******************* Migration ******************
Serial Phase #:99 [ibmnoida] Files:1 Time: 2s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [ibmnoida] Files:1 Time: 2s
Serial Phase #:101 [ibmnoida] Files:1 Time: 0s
Serial Phase #:102 [ibmnoida] Files:1 Time: 50s
***************** Post Upgrade *****************
Serial Phase #:103 [ibmnoida] Files:1 Time: 12s
**************** Summary report ****************
Serial Phase #:104 [ibmnoida] Files:1 Time: 2s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [ibmnoida] Files:1 Time: 2s
Serial Phase #:106 [ibmnoida] Files:1 Time: 0s
Serial Phase #:107 [ibmnoida] Files:1 Time: 56s
------------------------------------------------------
Phases [0-107] End Time:[2022_12_14 11:53:08]
------------------------------------------------------
Grand Total Time: 1515s
LOG FILES: (/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibmnoida/upgrade20221214112742/upg_summary.log
Grand Total Upgrade Time: [0d:0h:25m:15s]
[oracle@ip-172-31-19-40 bin]$
[oracle@ip-172-31-19-40 bin]$
[oracle@ip-172-31-19-40 bin]$
[oracle@ip-172-31-19-40 bin]$
[oracle@ip-172-31-19-40 bin]$
SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE
--------- -------------------- ---------------- ------------------------------ --------------------
IBMGGN READ WRITE PRIMARY ibmnoida MAXIMUM PERFORMANCE
SQL>
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
2547
SQL> @?/rdbms/admin/utlrp
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2022-12-14 11:56:12
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2022-12-14 11:58:57
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> SQL>
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
SQL>
=================================================================================================================================
########################################################################################################################3
[oracle@ip-172-31-19-40 admin]$
[oracle@ip-172-31-19-40 admin]$ sqlplus system/system@ibm12c
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 7 07:24:30 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@ip-172-31-19-40 admin]$
[oracle@ip-172-31-19-40 admin]$
On Source DB
=====================
DB_NAME : ibm12c
DB-Unique_name = ibm12c
Database version : 12.2.0
OS : RHE 8.7
On Target DB
=====================
DB_NAME : ibm12c
DB-Unique_name = ibm12c_dr
Database version : 19.3.0
OS : RHE 8.7
SQL> select name ,open_mode, log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
IBM12C READ WRITE ARCHIVELOG
SQL>
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL>
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL>
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
SQL>
alter system set log_archive_config='dg_config=(ibm12c,ibm12c_dr)' scope=both;
alter system set log_archive_dest_2='service=ibm12c_dr noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=ibm12c_dr' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
ALTER SYSTEM SET FAL_SERVER=ibm12c_dr scope=both;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;
SQL> show parameter remote
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
remote_recovery_file_dest string
result_cache_remote_expiration integer 0
SQL>
SQL>
alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo1.log') size 50m;
alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo2.log') size 50m;
alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo3.log') size 50m;
alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo4.log') size 50m;
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ibm12c/redo03.log
/u01/app/oracle/oradata/ibm12c/redo02.log
/u01/app/oracle/oradata/ibm12c/redo01.log
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo1.log') size 50m;
Database altered.
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo2.log') size 50m;
Database altered.
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo3.log') size 50m;
Database altered.
SQL> alter database add standby logfile ('/u01/app/oracle/oradata/ibm12c/stbyredo4.log') size 50m;
Database altered.
SQL> alter system set log_archive_config='dg_config=(ibm12c,ibm12c_dr)' scope=spfile;
System altered.
SQL> alter system set log_archive_config='dg_config=(ibm12c,ibm12c_dr)' scope=both;
System altered.
SQL> alter system set log_archive_dest_2='service=ibm12c_dr noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=ibm12c_dr' scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
System altered.
SQL> ALTER SYSTEM SET FAL_SERVER=ibm12c_dr scope=both;
System altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;
System altered.
SQL>
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ibm12c/redo03.log
/u01/app/oracle/oradata/ibm12c/redo02.log
/u01/app/oracle/oradata/ibm12c/redo01.log
/u01/app/oracle/oradata/ibm12c/stbyredo1.log
/u01/app/oracle/oradata/ibm12c/stbyredo2.log
/u01/app/oracle/oradata/ibm12c/stbyredo3.log
/u01/app/oracle/oradata/ibm12c/stbyredo4.log
7 rows selected.
SQL>
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ibm12c)
(ORACLE_HOME = /u01/app/sysadmin/product/19.2.0/db_home )
(SID_NAME = ibm12c)
)
(SID_DESC =
(GLOBAL_DBNAME = ibm12c_dr)
(ORACLE_HOME = /u01/app/sysadmin/product/19.2.0/db_home )
(SID_NAME = ibm12c_dr)
)
)
ibm12c =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ibm12c)
)
)
ibm12c_dr =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.0.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ibm12c_dr)
)
)
backup Source database
run{
allocate channel d1 type disk MAXPIECESIZE 1G;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup format '/u01/app/oracle/backup/db_t%t_s%s_p%p' database;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup format '/u01/app/oracle/backup/ar_t%t_s%s_p%p' archivelog all;
release channel d1;
}
Backup controlfile for standby
RMAN> run
{
allocate channel c1 type disk;
backup current controlfile for standby;
}
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/std_testdb.ctl';
Database altered.
CREATE PFILE ='/u01/app/oracle/stdby_backup/stdby.ora' FROM SPFILE;
=========================================================================
rman target sys/sys@ibm12c auxiliary sys/sys@ibm12c_dr
[oracle@ip-172-31-21-233 dbs]$
[oracle@ip-172-31-21-233 dbs]$ rman target sys/sys@ibm12c auxiliary sys/sys@ibm12c_dr
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Dec 7 13:44:27 2022
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: IBM12C (DBID=3136829478)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06429: AUXILIARY database is not compatible with this version of RMAN
RMAN-06618: RMAN client and database release mismatch; indicated database release is 19.3.0.0
[oracle@ip-172-31-21-233 dbs]$
[oracle@ip-172-31-21-233 dbs]$
[oracle@ip-172-31-19-40 ~]$ rman target sys/sys@ibm12c
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Dec 7 13:35:09 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06429: TARGET database is not compatible with this version of RMAN
RMAN-06618: RMAN client and database version mismatch; indicated database version is 12.2.0.1
[oracle@ip-172-31-19-40 ~]$
[oracle@ip-172-31-19-40 ~]$
BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT TAG 'FULL BACKUP PLUS ARCHIVELOG';
sql 'alter system archive log current';
sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";
RUN
{
configure controlfile autobackup on;
set command id to 'ORCLOnlineBackupFull';
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET full database tag ibm12c_FULL format '/tmp/ibm12c/%d_%T_%s_%p_FULL' ;
sql 'alter system archive log current';
backup tag ibm12c_ARCHIVE format '/tmp/ibm12c/%d_%T_%s_%p_ARCHIVE' archivelog all;
backup tag ibm12c_CONTROL current controlfile format '/tmp/ibm12c/%d_%T_%s_%p_CONTROL';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 10.19M DISK 00:00:00 07-DEC-22
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20221207T131608
Piece Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/c-3136829478-20221207-00
SPFILE Included: Modification time: 07-DEC-22
SPFILE db_unique_name: IBM12C
Control File Included: Ckp SCN: 1436459 Ckp time: 07-DEC-22
RMAN>
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name IBM12C
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 1 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_1_1122793448.dbf
2 1 2 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_2_1122793448.dbf
3 1 3 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_3_1122793448.dbf
RMAN>
RMAN>
[oracle@ip-172-31-19-40 tmp]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Dec 7 14:18:46 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: IBM12C (not mounted)
RMAN> restore spfile from '/tmp/c-3136829478-20221207-03';
Starting restore at 07-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /tmp/c-3136829478-20221207-03
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 07-DEC-22
RMAN>
RMAN> restore controlfile from '/tmp/ibm12c/IBM12C_20221207_13_1_CONTROL';
Starting restore at 07-DEC-22
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/product/19.0/dbhome1/dbs/cntrlibm12c.dbf
Finished restore at 07-DEC-22
RMAN>
RMAN> restore controlfile from '/tmp/ibm12c/IBM12C_20221207_13_1_CONTROL';
Starting restore at 07-DEC-22
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ibm12c/control01.ctl
output file name=/u01/app/oracle/oradata/ibm12c/control02.ctl
Finished restore at 07-DEC-22
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN>
RMAN> restore database;
Starting restore at 07-DEC-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ibm12c/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/ibm12c/IBM12C_20221207_6_1_FULL
channel ORA_DISK_1: piece handle=/tmp/ibm12c/IBM12C_20221207_6_1_FULL tag=IBM12C_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ibm12c/users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/ibm12c/IBM12C_20221207_7_1_FULL
channel ORA_DISK_1: piece handle=/tmp/ibm12c/IBM12C_20221207_7_1_FULL tag=IBM12C_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ibm12c/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/ibm12c/IBM12C_20221207_5_1_FULL
channel ORA_DISK_1: piece handle=/tmp/ibm12c/IBM12C_20221207_5_1_FULL tag=IBM12C_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ibm12c/system01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/ibm12c/IBM12C_20221207_4_1_FULL
channel ORA_DISK_1: piece handle=/tmp/ibm12c/IBM12C_20221207_4_1_FULL tag=IBM12C_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 07-DEC-22
RMAN>
recover database until sequence 4;
RMAN> recover database until sequence 8;
Starting recover at 07-DEC-22
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece /tmp/ibm12c/IBM12C_20221207_11_1_ARCHIVE
channel ORA_DISK_1: piece handle=/tmp/ibm12c/IBM12C_20221207_11_1_ARCHIVE tag=IBM12C_ARCHIVE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/19.0/dbhome1/dbs/arch1_6_1122793448.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/19.0/dbhome1/dbs/arch1_7_1122793448.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-DEC-22
RMAN>
on source12c
===================
[oracle@ip-172-31-21-233 tmp]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 7 14:33:31 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL>
SQL>
SQL> alter system switch logfile;
System altered.
SQL> create user abc20pm identified by abc;'
2
SQL>
SQL> create user abc20pm identified by abc;
User created.
SQL> grant dba to abc20pm;
Grant succeeded.
SQL> conn abc20pm/abc
Connected.
SQL> create table test (id number);
Table created.
SQL> insert into test values (1);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@ip-172-31-21-233 tmp]$
[oracle@ip-172-31-21-233 tmp]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 7 14:35:08 2022
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
SQL>
SQL>
BACKUP format '/tmp/ibm12c/%d_%T_%s_%p_ARCHIVE_7_12' ARCHIVELOG FROM SEQUENCE 7 UNTIL SEQUENCE 12;
RMAN> BACKUP format '/tmp/ibm12c/%d_%T_%s_%p_ARCHIVE_7_12' ARCHIVELOG FROM SEQUENCE 7 UNTIL SEQUENCE 12;
Starting backup at 07-DEC-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=267 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=7 STAMP=1122819070
input archived log thread=1 sequence=8 RECID=8 STAMP=1122820430
input archived log thread=1 sequence=9 RECID=9 STAMP=1122820519
input archived log thread=1 sequence=10 RECID=10 STAMP=1122820521
input archived log thread=1 sequence=11 RECID=11 STAMP=1122820524
input archived log thread=1 sequence=12 RECID=12 STAMP=1122820527
channel ORA_DISK_1: starting piece 1 at 07-DEC-22
channel ORA_DISK_1: finished piece 1 at 07-DEC-22
piece handle=/tmp/ibm12c/IBM12C_20221207_15_1_ARCHIVE_7_12 tag=TAG20221207T143852 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-DEC-22
Starting Control File and SPFILE Autobackup at 07-DEC-22
piece handle=/u01/app/oracle/product/12.2.0/dbhome1/dbs/c-3136829478-20221207-04 comment=NONE
Finished Control File and SPFILE Autobackup at 07-DEC-22
RMAN>
ship archive log to target
[oracle@ip-172-31-21-233 tmp]$ scp -r /u01/app/oracle/product/12.2.0/dbhome1/dbs/c-3136829478-20221207-03 oracle@172.31.19.40:/tmp
oracle@172.31.19.40's password:
c-3136829478-20221207-03 100% 10MB 93.5MB/s 00:00
[oracle@ip-172-31-21-233 tmp]$
[oracle@ip-172-31-21-233 ibm12c]$
[oracle@ip-172-31-21-233 ibm12c]$ scp IBM12C_20221207_15_1_ARCHIVE_7_12 oracle@172.31.19.40:/tmp/ibm12c
oracle@172.31.19.40's password:
IBM12C_20221207_15_1_ARCHIVE_7_12 100% 1145KB 101.1MB/s 00:00
[oracle@ip-172-31-21-233 ibm12c]$
on target
===============
RMAN> catalog start with '/tmp/ibm12c';
searching for all files that match the pattern /tmp/ibm12c
List of Files Unknown to the Database
=====================================
File Name: /tmp/ibm12c/IBM12C_20221207_15_1_ARCHIVE_7_12
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/ibm12c/IBM12C_20221207_15_1_ARCHIVE_7_12
RMAN>
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name IBM12C
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 1 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_1_1122793448.dbf
2 1 2 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_2_1122793448.dbf
3 1 3 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_3_1122793448.dbf
4 1 4 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_4_1122793448.dbf
5 1 5 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_5_1122793448.dbf
9 1 6 A 07-DEC-22
Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_6_1122793448.dbf
6 1 6 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_6_1122793448.dbf
8 1 7 A 07-DEC-22
Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_7_1122793448.dbf
7 1 7 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_7_1122793448.dbf
RMAN>
RMAN> recover database until sequence 13;
Starting recover at 07-DEC-22
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=11
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=12
channel ORA_DISK_1: reading from backup piece /tmp/ibm12c/IBM12C_20221207_15_1_ARCHIVE_7_12
channel ORA_DISK_1: piece handle=/tmp/ibm12c/IBM12C_20221207_15_1_ARCHIVE_7_12 tag=TAG20221207T143852
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/19.0/dbhome1/dbs/arch1_8_1122793448.dbf thread=1 sequence=8
archived log file name=/u01/app/oracle/product/19.0/dbhome1/dbs/arch1_9_1122793448.dbf thread=1 sequence=9
archived log file name=/u01/app/oracle/product/19.0/dbhome1/dbs/arch1_10_1122793448.dbf thread=1 sequence=10
archived log file name=/u01/app/oracle/product/19.0/dbhome1/dbs/arch1_11_1122793448.dbf thread=1 sequence=11
archived log file name=/u01/app/oracle/product/19.0/dbhome1/dbs/arch1_12_1122793448.dbf thread=1 sequence=12
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-DEC-22
RMAN>
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name IBM12C
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 1 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_1_1122793448.dbf
2 1 2 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_2_1122793448.dbf
3 1 3 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_3_1122793448.dbf
4 1 4 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_4_1122793448.dbf
5 1 5 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_5_1122793448.dbf
9 1 6 A 07-DEC-22
Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_6_1122793448.dbf
6 1 6 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_6_1122793448.dbf
8 1 7 A 07-DEC-22
Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_7_1122793448.dbf
7 1 7 A 07-DEC-22
Name: /u01/app/oracle/product/12.2.0/dbhome1/dbs/arch1_7_1122793448.dbf
14 1 8 A 07-DEC-22
Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_8_1122793448.dbf
10 1 9 A 07-DEC-22
Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_9_1122793448.dbf
12 1 10 A 07-DEC-22
Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_10_1122793448.dbf
11 1 11 A 07-DEC-22
Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_11_1122793448.dbf
13 1 12 A 07-DEC-22
Name: /u01/app/oracle/product/19.0/dbhome1/dbs/arch1_12_1122793448.dbf
RMAN>
Alter Database open migrate ;
alter database open resetlogs upgrade;
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=296460073482260&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=984511.1&_afrWindowMode=0&_adf.ctrl-state=3cn7j36nl_129
RMAN> alter database open resetlogs upgrade;
using target database control file instead of recovery catalog
PSDRPC returns significant error 3113.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 12/07/2022 15:25:41
ORA-04023: Object SYS.STANDARD could not be validated or authorized
RMAN>
[oracle@ip-172-31-19-40 ~]$
[oracle@ip-172-31-19-40 ~]$
[oracle@ip-172-31-19-40 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 7 15:30:00 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 2449472408 bytes
Fixed Size 8899480 bytes
Variable Size 536870912 bytes
Database Buffers 1895825408 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select status,active_state from v$instance;
STATUS ACTIVE_ST
------------ ---------
OPEN MIGRATE NORMAL
SQL>
/u01/app/oracle/product/19.0/dbhome1/rdbms/admin
upgrade start at 9:00PM 1/12/2022
=======================================
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ip-172-31-19-40 ~]$
[oracle@ip-172-31-19-40 ~]$
[oracle@ip-172-31-19-40 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 7 15:30:00 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 2449472408 bytes
Fixed Size 8899480 bytes
Variable Size 536870912 bytes
Database Buffers 1895825408 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select status,active_state from v$instance;
STATUS ACTIVE_ST
------------ ---------
OPEN MIGRATE NORMAL
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ip-172-31-19-40 ~]$
[oracle@ip-172-31-19-40 ~]$
[oracle@ip-172-31-19-40 ~]$
[oracle@ip-172-31-19-40 ~]$
[oracle@ip-172-31-19-40 ~]$ cd $ORACLE_HOME/
[oracle@ip-172-31-19-40 dbhome1]$ pwd
/u01/app/oracle/product/19.0/dbhome1
[oracle@ip-172-31-19-40 dbhome1]$
[oracle@ip-172-31-19-40 dbhome1]$ cd rdbms/admin/
[oracle@ip-172-31-19-40 admin]$
[oracle@ip-172-31-19-40 admin]$ pwd
/u01/app/oracle/product/19.0/dbhome1/rdbms/admin
[oracle@ip-172-31-19-40 admin]$
[oracle@ip-172-31-19-40 admin]$ cd ..
[oracle@ip-172-31-19-40 rdbms]$ cd ..
[oracle@ip-172-31-19-40 dbhome1]$ cd bin/
[oracle@ip-172-31-19-40 bin]$
[oracle@ip-172-31-19-40 bin]$ ls -lrt dbu
dbua dbupgrade
[oracle@ip-172-31-19-40 bin]$ ls -lrt dbu
dbua dbupgrade
[oracle@ip-172-31-19-40 bin]$ ./dbupgrade
Argument list for [/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]
/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0/dbhome1]
/u01/app/oracle/product/19.0/dbhome1/bin/orabasehome = [/u01/app/oracle/product/19.0/dbhome1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0/dbhome1]
Analyzing file /u01/app/oracle/product/19.0/dbhome1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20221207153254]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20221207153254/catupgrd_catcon_4973.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20221207153254/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20221207153254/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = ibm12c
DataBase Version = 12.2.0.1.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibm12c/upgrade20221207153304/catupgrd_catcon_4973.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibm12c/upgrade20221207153304/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibm12c/upgrade20221207153304/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/19.0/dbhome1/cfgtoollogs/ibm12c/upgrade20221207153304]
Parallel SQL Process Count = 4
Components in [ibm12c]
Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM RAC WK]
------------------------------------------------------
Phases [0-107] Start Time:[2022_12_07 15:33:16]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [ibm12c] Files:1 Time: 21s
*************** Catalog Core SQL ***************
Serial Phase #:1 [ibm12c] Files:5
Time: 31s
Restart Phase #:2 [ibm12c] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [ibm12c] Files:19 Time: 14s
Restart Phase #:4 [ibm12c] Files:1 Time: 1s
************* Catalog Final Scripts ************
Serial Phase #:5 [ibm12c] Files:7 Time: 13s
***************** Catproc Start ****************
Serial Phase #:6 [ibm12c] Files:1 Time: 10s
***************** Catproc Types ****************
Serial Phase #:7 [ibm12c] Files:2 Time: 10s
Restart Phase #:8 [ibm12c] Files:1 Time: 1s
**************** Catproc Tables ****************
Parallel Phase #:9 [ibm12c] Files:67 Time: 20s
Restart Phase #:10 [ibm12c] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [ibm12c] Files:1 Time: 55s
Restart Phase #:12 [ibm12c] Files:1 Time: 1s
************** Catproc Procedures **************
Parallel Phase #:13 [ibm12c] Files:94 Time: 8s
Restart Phase #:14 [ibm12c] Files:1 Time: 2s
Parallel Phase #:15 [ibm12c] Files:120 Time: 12s
Restart Phase #:16 [ibm12c] Files:1 Time: 3s
Serial Phase #:17 [ibm12c] Files:22 Time: 3s
Restart Phase #:18 [ibm12c] Files:1 Time: 3s
***************** Catproc Views ****************
Parallel Phase #:19 [ibm12c] Files:32 Time: 14s
Restart Phase #:20 [ibm12c] Files:1 Time: 3s
Serial Phase #:21 [ibm12c] Files:3 Time: 10s
Restart Phase #:22 [ibm12c] Files:1 Time: 2s
Parallel Phase #:23 [ibm12c] Files:25
upgrade timezone if required
===============================
Upgrade timezone data to version 18
[oracle@ip-172-31-19-40 bin]$
[oracle@ip-172-31-19-40 bin]$
[oracle@ip-172-31-19-40 bin]$
[oracle@ip-172-31-19-40 bin]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 7 16:03:27 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 2449472408 bytes
Fixed Size 8899480 bytes
Variable Size 637534208 bytes
Database Buffers 1795162112 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
[oracle@ip-172-31-19-40 bin]$
[oracle@ip-172-31-19-40 bin]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 7 16:03:27 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 2449472408 bytes
Fixed Size 8899480 bytes
Variable Size 637534208 bytes
Database Buffers 1795162112 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> select name ,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
IBM12C READ WRITE
SQL>
SQL> conn abc20pm/abc
Connected.
SQL> select * from test;
ID
----------
1
SQL>
Really good post, thanks for sharing..
ReplyDeleteOracle Dba Training
Amazing, this is what I was looking. So much effort put 🙌
ReplyDelete