Let us learn Oracle Database Administration skills: Perform Oracle DB upgrade from 12.2 to 19c using RMAN

Monday, September 4, 2023

Perform Oracle DB upgrade from 12.2 to 19c using RMAN

source server: vcentos79-oracle-ggsrc [GGSRC04T, 12.2.0.1]
target server: vcentos79-oracle-ggtgt [GGTGT05T, 19.3.0]

Step 1) Ensure the backups are available in the target

Copy from source:

[oracle@vcentos79-oracle-ggsrc SnapshotBackup]$ ls -altr
total 196856
drwxr-x---. 6 oracle oinstall 81 Sep 4 11:39 ..
-rw-r-----. 1 oracle oinstall 4341760 Sep 4 11:39 rman_bkp_GGSRC04T_3j25h5j3_1_1
-rw-r-----. 1 oracle oinstall 60055552 Sep 4 11:39 rman_bkp_GGSRC04T_3i25h5j2_1_1
-rw-r-----. 1 oracle oinstall 4341760 Sep 4 11:46 rman_bkp_GGSRC04T_3m25h60r_1_1
-rw-r-----. 1 oracle oinstall 62070784 Sep 4 11:46 rman_bkp_GGSRC04T_3k25h60r_1_1
-rw-r-----. 1 oracle oinstall 60055552 Sep 4 11:46 rman_bkp_GGSRC04T_3l25h60r_1_1
-rw-r--r--. 1 oracle oinstall 1856 Sep 4 12:49 pfileGGSRC04TSB1.ora
-rw-------. 1 oracle oinstall 3891 Sep 4 12:50 cwallet.sso
-rw-------. 1 oracle oinstall 3848 Sep 4 12:50 ewallet.p12
drwxr-xr-x. 2 oracle oinstall 4096 Sep 4 12:50 .
-rw-r-----. 1 oracle oinstall 10698752 Sep 4 12:50 o1_mf_s_1146655442_lhcff4yp_.bkp
[oracle@vcentos79-oracle-ggsrc SnapshotBackup]$ cd ..
[oracle@vcentos79-oracle-ggsrc GGSRC04TSB1]$ ls -altr
total 4
drwxr-xr-x. 6 oracle dba 73 Sep 3 21:20 ..
drwxr-x---. 4 oracle oinstall 42 Sep 4 08:00 archivelog
drwxr-x---. 2 oracle oinstall 60 Sep 4 11:05 flashback
drwxr-x---. 3 oracle oinstall 24 Sep 4 11:14 autobackup
drwxr-x---. 6 oracle oinstall 81 Sep 4 11:39 .
drwxr-xr-x. 2 oracle oinstall 4096 Sep 4 12:50 SnapshotBackup
[oracle@vcentos79-oracle-ggsrc GGSRC04TSB1]$ scp -pr SnapshotBackup oracle@192.168.194.11:/oraarch/GGSRC04TSB1/
oracle@192.168.194.11's password:
rman_bkp_GGSRC04T_3i25h5j2_1_1 100% 57MB 41.6MB/s 00:01
rman_bkp_GGSRC04T_3j25h5j3_1_1 100% 4240KB 41.0MB/s 00:00
rman_bkp_GGSRC04T_3k25h60r_1_1 100% 59MB 41.4MB/s 00:01
rman_bkp_GGSRC04T_3l25h60r_1_1 100% 57MB 23.3MB/s 00:02
rman_bkp_GGSRC04T_3m25h60r_1_1 100% 4240KB 29.8MB/s 00:00
pfileGGSRC04TSB1.ora 100% 1856 1.4MB/s 00:00
cwallet.sso 100% 3891 2.4MB/s 00:00
ewallet.p12 100% 3848 3.0MB/s 00:00
o1_mf_s_1146655442_lhcff4yp_.bkp 100% 10MB 41.8MB/s 00:00
[oracle@vcentos79-oracle-ggsrc GGSRC04TSB1]$

@ target:

[oracle@vcentos79-oracle-ggtgt GGSRC04TSB1]$ ls -altr SnapshotBackup/
total 196856
-rw-r-----. 1 oracle oinstall 4341760 Sep 4 11:39 rman_bkp_GGSRC04T_3j25h5j3_1_1
-rw-r-----. 1 oracle oinstall 60055552 Sep 4 11:39 rman_bkp_GGSRC04T_3i25h5j2_1_1
-rw-r-----. 1 oracle oinstall 4341760 Sep 4 11:46 rman_bkp_GGSRC04T_3m25h60r_1_1
-rw-r-----. 1 oracle oinstall 62070784 Sep 4 11:46 rman_bkp_GGSRC04T_3k25h60r_1_1
-rw-r-----. 1 oracle oinstall 60055552 Sep 4 11:46 rman_bkp_GGSRC04T_3l25h60r_1_1
-rw-r--r--. 1 oracle oinstall 1856 Sep 4 12:49 pfileGGSRC04TSB1.ora
-rw-------. 1 oracle oinstall 3848 Sep 4 12:50 ewallet.p12
-rw-------. 1 oracle oinstall 3891 Sep 4 12:50 cwallet.sso
-rw-r-----. 1 oracle oinstall 10698752 Sep 4 12:50 o1_mf_s_1146655442_lhcff4yp_.bkp
drwxr-xr-x. 2 oracle oinstall 4096 Sep 4 12:50 .
drwxr-xr-x. 3 oracle oinstall 28 Sep 4 12:53 ..
[oracle@vcentos79-oracle-ggtgt GGSRC04TSB1]$

Step 2) Ensure to copy TDE wallet if needed, create audit file dest

mkdir -p /u01/app/oracle/admin/GGTGT05T/wallet/
ls -ld /u01/app/oracle/admin/GGTGT05T/wallet/

mkdir -p /u01/app/oracle/admin/GGTGT05T/adump
ls -ld /u01/app/oracle/admin/GGTGT05T/adump

Actual output:

[oracle@vcentos79-oracle-ggtgt GGSRC04TSB1]$ mkdir -p /u01/app/oracle/admin/GGTGT05T/wallet/

[oracle@vcentos79-oracle-ggtgt GGSRC04TSB1]$ ls -ld /u01/app/oracle/admin/GGTGT05T/wallet/
drwxr-xr-x. 2 oracle oinstall 6 Sep 4 12:55 /u01/app/oracle/admin/GGTGT05T/wallet/

[oracle@vcentos79-oracle-ggtgt GGSRC04TSB1]$ cp SnapshotBackup/*wallet* /u01/app/oracle/admin/GGTGT05T/wallet/

[oracle@vcentos79-oracle-ggtgt GGSRC04TSB1]$ ls -altr /u01/app/oracle/admin/GGTGT05T/wallet/
total 8
drwxr-xr-x. 3 oracle oinstall 20 Sep 4 12:55 ..
-rw-------. 1 oracle oinstall 3848 Sep 4 12:56 ewallet.p12
-rw-------. 1 oracle oinstall 3891 Sep 4 12:56 cwallet.sso
drwxr-xr-x. 2 oracle oinstall 44 Sep 4 12:56 .

[oracle@vcentos79-oracle-ggtgt GGSRC04TSB1]$
[oracle@vcentos79-oracle-ggtgt GGSRC04TSB1]$ mkdir -p /u01/app/oracle/admin/GGTGT05T/adump

[oracle@vcentos79-oracle-ggtgt GGSRC04TSB1]$ ls -ld /u01/app/oracle/admin/GGTGT05T/adump
drwxr-xr-x. 2 oracle oinstall 6 Sep 4 12:56 /u01/app/oracle/admin/GGTGT05T/adump
[oracle@vcentos79-oracle-ggtgt GGSRC04TSB1]$

Step 3) Ensure to copy the pfile and update it as per the target requirement

Global search and modify:
GGSRC04TSB1 -> GGTGT05T

Removed param:
*.db_file_name_convert='/oradata/GGSRC04T','/oradata/GGTGT05T'
*.fal_server='GGSRC04T'
*.log_archive_config='DG_CONFIG=(GGSRC04T,GGTGT05T)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=GGTGT05T'
*.log_archive_dest_2='service=GGSRC04T ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=GGSRC04T'
*.log_file_name_convert='/oradata/GGSRC04T','/oradata/GGTGT05T'

Added param:
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

Modified param:
*.streams_pool_size=100M
*.db_recovery_file_dest_size=4G
Step 4) Make /etc/oratab entry for the DB
[oracle@vcentos79-oracle-ggtgt GGSRC04TSB1]$ tail -2 /etc/oratab
GGTGT04T:/u01/app/oracle/product/19.0.0/db_1:N
GGTGT05T:/u01/app/oracle/product/19.0.0/db_1:N <<<<<<<< entry is made
[oracle@vcentos79-oracle-ggtgt GGSRC04TSB1]$

Step 5) Nomount the db using the pfile, create spfile

startup nomount pfile='/oraarch/GGSRC04TSB1/SnapshotBackup/pfileGGSRC04TSB1.ora';
create spfile from pfile='/oraarch/GGSRC04TSB1/SnapshotBackup/pfileGGSRC04TSB1.ora';

Actual output:

SQL> startup nomount pfile='/oraarch/GGSRC04TSB1/SnapshotBackup/pfileGGSRC04TSB1.ora';

ORACLE instance started.
Total System Global Area 3221222464 bytes
Fixed Size 8901696 bytes
Variable Size 754974720 bytes
Database Buffers 2449473536 bytes
Redo Buffers 7872512 bytes

SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
GGTGT05T STARTED

SQL> create spfile from pfile='/oraarch/GGSRC04TSB1/SnapshotBackup/pfileGGSRC04TSB1.ora';
File created.

SQL> !ls -altr $ORACLE_HOME/dbs/spfileGGTGT05*
-rw-r-----. 1 oracle oinstall 2560 Sep 4 13:45 /u01/app/oracle/product/19.0.0/db_1/dbs/spfileGGTGT05T.ora

SQL>

Step 6) Restart the db in nomount again to let it pickup spfile

startup nomount;

Actual output:

SQL> shu immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 3221222464 bytes
Fixed Size 8901696 bytes
Variable Size 754974720 bytes
Database Buffers 2449473536 bytes
Redo Buffers 7872512 bytes

SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
GGTGT05T STARTED

SQL> sho parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
/db_1/dbs/spfileGGTGT05T.ora
SQL>

Step 7) check wallet status

select * from v$encryption_wallet;

Actual output:

SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC
------------------------------ -------------------- --------- -------- ---------
CON_ID
----------
FILE
/u01/app/oracle/admin/GGTGT05T/wallet/
OPEN AUTOLOGIN SINGLE NONE NO
0

Step 8) Restore the controlfile

rman target /
run
{
set dbid 4198404018;
restore controlfile from '/oraarch/GGSRC04TSB1/SnapshotBackup/o1_mf_s_1146655442_lhcff4yp_.bkp';
}
alter database mount;
select name,open_mode from v$database;

Actual output:

[oracle@vcentos79-oracle-ggtgt ~]$ rman target /
..
connected to target database: GGSRC04T (not mounted)
RMAN> run
{
set dbid 4198404018;
restore controlfile from '/oraarch/GGSRC04TSB1/SnapshotBackup/o1_mf_s_1146655442_lhcff4yp_.bkp';
}2> 3> 4> 5>

executing command: SET DBID
Starting restore at 04-SEP-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/GGTGT05T/control01.ctl
output file name=/oradata/GGTGT05T/control02.ctl
Finished restore at 04-SEP-23

RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed

RMAN> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
GGSRC04T MOUNTED
RMAN> exit

Recovery Manager complete.

ls -[oracle@vcentos79-oracle-ggtgt ~]$ ls -altr /oradata/GGTGT05T/
total 20704
drwxr-xr-x. 7 oracle dba 86 Sep 4 13:48 ..
drwxr-x---. 2 oracle oinstall 48 Sep 4 13:48 .
-rw-r-----. 1 oracle oinstall 10600448 Sep 4 13:48 control01.ctl
-rw-r-----. 1 oracle oinstall 10600448 Sep 4 13:48 control02.ctl
[oracle@vcentos79-oracle-ggtgt ~]$

Step 9) Catalog the backup pieces

catalog start with '/oraarch/GGSRC04TSB1/SnapshotBackup/';

Actual output:

RMAN> catalog start with '/oraarch/GGSRC04TSB1/SnapshotBackup/';

Starting implicit crosscheck backup at 04-SEP-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
Crosschecked 18 objects
Finished implicit crosscheck backup at 04-SEP-23
Starting implicit crosscheck copy at 04-SEP-23
using channel ORA_DISK_1
Finished implicit crosscheck copy at 04-SEP-23
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /oraarch/GGSRC04TSB1/SnapshotBackup/
List of Files Unknown to the Database
=====================================
File Name: /oraarch/GGSRC04TSB1/SnapshotBackup/cwallet.sso
File Name: /oraarch/GGSRC04TSB1/SnapshotBackup/ewallet.p12
File Name: /oraarch/GGSRC04TSB1/SnapshotBackup/o1_mf_s_1146655442_lhcff4yp_.bkp
File Name: /oraarch/GGSRC04TSB1/SnapshotBackup/pfileGGSRC04TSB1.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: /oraarch/GGSRC04TSB1/SnapshotBackup/o1_mf_s_1146655442_lhcff4yp_.bkp
List of Files Which Were Not Cataloged
=======================================
File Name: /oraarch/GGSRC04TSB1/SnapshotBackup/cwallet.sso
RMAN-07517: Reason: The file header is corrupted
File Name: /oraarch/GGSRC04TSB1/SnapshotBackup/ewallet.p12
RMAN-07517: Reason: The file header is corrupted
File Name: /oraarch/GGSRC04TSB1/SnapshotBackup/pfileGGSRC04TSB1.ora
RMAN-07517: Reason: The file header is corrupted
RMAN>

Step 10) Restore the db & no recovery needed, since this is a cold backup
run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
restore database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
Missed: set newname for database to new;

Actual output:

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Sep 4 13:49:43 2023
Version 19.3.0.0.0
..

connected to target database: GGSRC04T (DBID=4198404018, not open)

RMAN> run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
restore database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>

using target database control file instead of recovery catalog

allocated channel: c1
channel c1: SID=42 device type=DISK
allocated channel: c2
channel c2: SID=46 device type=DISK
allocated channel: c3
channel c3: SID=47 device type=DISK
allocated channel: c4
channel c4: SID=45 device type=DISK

Starting restore at 04-SEP-23

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00003 to /oradata/GGSRC04TSB1/undotbs01.dbf
channel c1: restoring datafile 00006 to /oradata/GGSRC04TSB1/gg_data_01.dbf
channel c1: reading from backup piece /oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3m25h60r_1_1
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00001 to /oradata/GGSRC04TSB1/system01.dbf
channel c2: restoring datafile 00005 to /oradata/GGSRC04TSB1/encrypt_ts1_01.dbf
channel c2: reading from backup piece /oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3k25h60r_1_1
channel c3: starting datafile backup set restore
channel c3: specifying datafile(s) to restore from backup set
channel c3: restoring datafile 00002 to /oradata/GGSRC04TSB1/sysaux01.dbf
channel c3: restoring datafile 00004 to /oradata/GGSRC04TSB1/users01.dbf
channel c3: reading from backup piece /oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3l25h60r_1_1
channel c1: piece handle=/oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3m25h60r_1_1 tag=TAG20230904T114634
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:15
channel c2: piece handle=/oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3k25h60r_1_1 tag=TAG20230904T114634
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:35
channel c3: piece handle=/oraarch/GGSRC04TSB1/SnapshotBackup/rman_bkp_GGSRC04T_3l25h60r_1_1 tag=TAG20230904T114634
channel c3: restored backup piece 1
channel c3: restore complete, elapsed time: 00:00:35

Finished restore at 04-SEP-23

released channel: c1
released channel: c2
released channel: c3
released channel: c4
RMAN> exit

Recovery Manager complete.

Step 11) Verify the db restore finished without any issues

Actual output:

Restore log - OK

SQL> select distincT(status) from v$datafile;

STATUS
-------
ONLINE
SYSTEM

SQL> select distincT(status) from v$datafile_header;

STATUS
-------
ONLINE

SQL> select checkpoint_Change#,controlfile_Change#,name,open_mode from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# NAME OPEN_MODE
------------------ ------------------- --------- --------------------
604912 604912 GGSRC04T MOUNTED

SQL> select distinct(checkpoint_change#) from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
604912
SQL>

Step 12) Rename the redo and standby redolog files

select * from V$log;
select member from v$logfile;
alter database rename file '/oradata/GGSRC04TSB1/redo01.log' to '/oradata/GGTGT05T/redo01.log';
alter database rename file '/oradata/GGSRC04TSB1/redo02.log' to '/oradata/GGTGT05T/redo02.log';
alter database rename file '/oradata/GGSRC04TSB1/redo03.log' to '/oradata/GGTGT05T/redo03.log';
alter database rename file '/oradata/GGSRC04TSB1/sbyredo04.log' to '/oradata/GGSRC05T/sbyredo04.log';
alter database rename file '/oradata/GGSRC04TSB1/sbyredo05.log' to '/oradata/GGSRC05T/sbyredo05.log';
alter database rename file '/oradata/GGSRC04TSB1/sbyredo06.log' to '/oradata/GGSRC05T/sbyredo06.log';
alter database rename file '/oradata/GGSRC04TSB1/sbyredo07.log' to '/oradata/GGSRC05T/sbyredo07.log';
alter database clear logfile group 5;
alter database drop logfile group 5;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oradata/GGTGT05T/sbyredo05.log') SIZE 200M;
Fixup more:
alter database rename file '/oradata/GGSRC05T/redo01.log' to '/oradata/GGTGT05T/redo01.log';
alter database rename file '/oradata/GGSRC05T/redo02.log' to '/oradata/GGTGT05T/redo02.log';
alter database rename file '/oradata/GGSRC05T/redo03.log' to '/oradata/GGTGT05T/redo03.log';
alter database rename file '/oradata/GGSRC05T/sbyredo04.log' to '/oradata/GGTGT05T/sbyredo04.log';
alter database rename file '/oradata/GGSRC05T/sbyredo06.log' to '/oradata/GGTGT05T/sbyredo06.log';
alter database rename file '/oradata/GGSRC05T/sbyredo07.log' to '/oradata/GGTGT05T/sbyredo07.log';
select * from V$log;
select member from v$logfile;

Actual output:

SQL> set lines 300
SQL> set pages 3000

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 1 209715200 512 1 NO CURRENT 600558 04-SEP-23 1.8447E+19 0
3 1 0 209715200 512 1 YES UNUSED 0 0 0
2 1 0 209715200 512 1 YES UNUSED 0 0 0

SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/oradata/GGSRC04TSB1/redo01.log
/oradata/GGSRC04TSB1/redo02.log
/oradata/GGSRC04TSB1/redo03.log
/oradata/GGSRC04TSB1/sbyredo04.log
/oradata/GGSRC04TSB1/sbyredo05.log
/oradata/GGSRC04TSB1/sbyredo06.log
/oradata/GGSRC04TSB1/sbyredo07.log
7 rows selected.

SQL> alter database rename file '/oradata/GGSRC04TSB1/redo01.log' to '/oradata/GGSRC05T/redo01.log';
Database altered.

SQL> alter database rename file '/oradata/GGSRC04TSB1/redo02.log' to '/oradata/GGSRC05T/redo02.log';
alter database rename file '/oradata/GGSRC04TSB1/redo03.log' to '/oradata/GGSRC05T/redo03.log';
alter database rename file '/oradata/GGSRC04TSB1/sbyredo04.log' to '/oradata/GGSRC05T/sbyredo04.log';
alter database rename file '/oradata/GGSRC04TSB1/sbyredo05.log' to '/oradata/GGSRC05T/sbyredo05.log';
alter database rename file '/oradata/GGSRC04TSB1/sbyredo06.log' to '/oradata/GGSRC05T/sbyredo06.log';
alter database rename file '/oradata/GGSRC04TSB1/sbyredo07.log' to '/oradata/GGSRC05T/sbyredo07.log';
Database altered.

SQL>
Database altered.
SQL>
Database altered.

SQL> alter database rename file '/oradata/GGSRC04TSB1/sbyredo05.log' to '/oradata/GGSRC05T/sbyredo05.log'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-00261: log 5 of thread 1 is being archived or modified
ORA-00312: online log 5 thread 1: '/oradata/GGSRC04TSB1/sbyredo05.log'

SQL>
Database altered.
SQL>
Database altered.
SQL>
SQL> select group#,member from v$logfile;
GROUP#
----------
MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1
/oradata/GGSRC05T/redo01.log
2
/oradata/GGSRC05T/redo02.log
3
/oradata/GGSRC05T/redo03.log
4
/oradata/GGSRC05T/sbyredo04.log
5
/oradata/GGSRC04TSB1/sbyredo05.log
6
/oradata/GGSRC05T/sbyredo06.log
7
/oradata/GGSRC05T/sbyredo07.log

7 rows selected.

SQL> col member for a40
SQL> 1
1* select group#,member from v$logfile
SQL> /
GROUP# MEMBER
---------- ----------------------------------------
1 /oradata/GGSRC05T/redo01.log
2 /oradata/GGSRC05T/redo02.log
3 /oradata/GGSRC05T/redo03.log
4 /oradata/GGSRC05T/sbyredo04.log
5 /oradata/GGSRC04TSB1/sbyredo05.log
6 /oradata/GGSRC05T/sbyredo06.log
7 /oradata/GGSRC05T/sbyredo07.log
7 rows selected.

SQL> alter database clear logfile group 5;
Database altered.

SQL> alter database drop logfile group 5;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oradata/GGSRC05T/sbyredo05.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oradata/GGSRC05T/sbyredo05.log') SIZE 200M
*
ERROR at line 1:
ORA-00301: error in adding log file '/oradata/GGSRC05T/sbyredo05.log' - file cannot be created
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1

SQL> !ls -altr /oradata/GGSRC05T/sbyredo05.log
ls: cannot access /oradata/GGSRC05T/sbyredo05.log: No such file or directory

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/oradata/GGTGT05T/sbyredo05.log') SIZE 200M;
Database altered.

SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------
1 /oradata/GGSRC05T/redo01.log
2 /oradata/GGSRC05T/redo02.log
3 /oradata/GGSRC05T/redo03.log
4 /oradata/GGSRC05T/sbyredo04.log
5 /oradata/GGTGT05T/sbyredo05.log
6 /oradata/GGSRC05T/sbyredo06.log
7 /oradata/GGSRC05T/sbyredo07.log
7 rows selected.

SQL> alter database rename file '/oradata/GGSRC05T/redo01.log' to '/oradata/GGTGT05T/redo01.log';
Database altered.

SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------
1 /oradata/GGTGT05T/redo01.log
2 /oradata/GGSRC05T/redo02.log
3 /oradata/GGSRC05T/redo03.log
4 /oradata/GGSRC05T/sbyredo04.log
5 /oradata/GGTGT05T/sbyredo05.log
6 /oradata/GGSRC05T/sbyredo06.log
7 /oradata/GGSRC05T/sbyredo07.log
7 rows selected.

SQL> !ls -ld /oradata/GGTGT05T/
drwxr-x---. 3 oracle oinstall 86 Sep 4 14:03 /oradata/GGTGT05T/
SQL> alter database rename file '/oradata/GGSRC05T/redo02.log' to '/oradata/GGTGT05T/redo02.log';
alter database rename file '/oradata/GGSRC05T/redo03.log' to '/oradata/GGTGT05T/redo03.log';
alter database rename file '/oradata/GGSRC05T/sbyredo04.log' to '/oradata/GGTGT05T/sbyredo04.log';
alter database rename file '/oradata/GGSRC05T/sbyredo06.log' to '/oradata/GGTGT05T/sbyredo06.log';
alter database rename file '/oradata/GGSRC05T/sbyredo07.log' to '/oradata/GGTGT05T/sbyredo07.log';
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL>
SQL>
SQL>

SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------
1 /oradata/GGTGT05T/redo01.log
2 /oradata/GGTGT05T/redo02.log
3 /oradata/GGTGT05T/redo03.log
4 /oradata/GGTGT05T/sbyredo04.log
5 /oradata/GGTGT05T/sbyredo05.log
6 /oradata/GGTGT05T/sbyredo06.log
7 /oradata/GGTGT05T/sbyredo07.log
7 rows selected.

SQL> select distinct(substr(member,1,17)) from v$logfile;
(SUBSTR(MEMBER,1,17))
--------------------------------------------------------------------
/oradata/GGTGT05T

Step 12) Try to open the db with resetlogs in upgrade mode

set lines 300
set pages 3000

select name,open_mode,log_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN,CURRENT_SCN from v$database; -- this is snapshot standby coldbackup scn

select instance_name,status from v$instance;

alter database open resetlogs upgrade;

select name,open_mode,log_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN,CURRENT_SCN from v$database;

select instance_name,status from v$instance;

Actual output:

SQL> select name,open_mode,log_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN,CURRENT_SCN from v$database;

NAME OPEN_MODE LOG_MODE FORCE_LOGGING SUPPLEME CURRENT_SCN
--------- -------------------- ------------ --------------------------------------- -------- -----------
GGSRC04T MOUNTED ARCHIVELOG YES YES 0

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
GGTGT05T MOUNTED

SQL> alter database open resetlogs upgrade;
Database altered.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
GGTGT05T OPEN MIGRATE

SQL> select name,open_mode,log_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN,CURRENT_SCN from v$database;

select distincT(status) from v$datafile;
create restore point PRE_UPGRADE_GGTGT05T guarantee flashback database;
select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;
select parameter,value from v$option where upper(parameter) like '%VAULT%';

Actual output:

SQL> select distincT(status) from v$datafile;

STATUS

-------
SYSTEM
ONLINE

SQL> create restore point PRE_UPGRADE_GGTGT05T guarantee flashback database;

Restore point created.

SQL> select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;

SCN GUA NAME
---------- --- --------------------------------------------------------------------------------------------------------------------------------
609484 YES PRE_UPGRADE_GGTGT05T

SQL> select parameter,value from v$option where upper(parameter) like '%VAULT%';

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle Database Vault FALSE

SQL> sho parameter db_REco

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oraarch
db_recovery_file_dest_size big integer 4G

Step 13) Perform the db upgrade

mkdir -p /home/oracle/dba/DBUpgrade_GGTGT05T/log

cd /home/oracle/dba/DBUpgrade_GGTGT05T/

which dbupgrade -- to verify if we are picking 19c bin

nohup dbupgrade -n 2 -l /home/oracle/dba/DBUpgrade_GGTGT05T/log > /home/oracle/dba/DBUpgrade_GGTGT05T/templog &

Actual output:

[oracle@vcentos79-oracle-ggtgt DBUpgrade_GGTGT05T]$ nohup dbupgrade -n 2 -l /home/oracle/dba/DBUpgrade_GGTGT05T/log > /home/oracle/dba/DBUpgrade_GGTGT05T/templog &
[1] 15771

[oracle@vcentos79-oracle-ggtgt DBUpgrade_GGTGT05T]$ nohup: ignoring input and redirecting stderr to stdout

[oracle@vcentos79-oracle-ggtgt DBUpgrade_GGTGT05T]$ jobs -l
[1]+ 15771 Running nohup dbupgrade -n 2 -l /home/oracle/dba/DBUpgrade_GGTGT05T/log > /home/oracle/dba/DBUpgrade_GGTGT05T/templog &
[oracle@vcentos79-oracle-ggtgt DBUpgrade_GGTGT05T]$

[oracle@vcentos79-oracle-ggtgt DBUpgrade_GGTGT05T]$

[oracle@vcentos79-oracle-ggtgt log]$ jobs -l
[1]+ 15771 Done nohup dbupgrade -n 2 -l /home/oracle/dba/DBUpgrade_GGTGT05T/log > /home/oracle/dba/DBUpgrade_GGTGT05T/templog (wd: ~/dba/DBUpgrade_GGTGT05T)

Oracle Database Release 19 Post-Upgrade Status Tool 09-04-2023 14:47:3
Database Name: GGSRC04T
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.3.0.0.0 00:22:33
Oracle Label Security UPGRADED 19.3.0.0.0 00:00:16
Oracle Database Vault UPGRADED 19.3.0.0.0 00:00:27
Oracle Workspace Manager UPGRADED 19.3.0.0.0 00:00:33
Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:00
Oracle XML Database UPGRADED 19.3.0.0.0 00:02:00
Datapatch 00:01:46
Final Actions 00:02:02
Post Upgrade 00:00:17
Total Upgrade Time: 00:29:36
Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.
Grand Total Upgrade Time: [0d:0h:31m:54s]

>>> DB will be shutdown at the end of upgrade

Step 14) Post DB upgrade validation

startup
select name,open_mode from v$database;
select instance_name,status from v$instance;
@?/rdbms/admin/utlrp.sql
@$ORACLE_HOME/rdbms/admin/utlusts.sql

Actual output:

SQL> startup nomount

ORACLE instance started.
Total System Global Area 3221222464 bytes
Fixed Size 8901696 bytes
Variable Size 754974720 bytes
Database Buffers 2449473536 bytes
Redo Buffers 7872512 bytes

SQL> sho parameter pf
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
/db_1/dbs/spfileGGTGT05T.ora
SQL> alter database mount;

Database altered.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
GGSRC04T MOUNTED

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
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
CON_ID
----------

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
GGSRC04T READ WRITE

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
GGTGT05T OPEN

SQL> @?/rdbms/admin/utlrp.sql

Session altered.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2023-09-04 14:56:55
...

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2023-09-04 15:02:49
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> @$ORACLE_HOME/rdbms/admin/utlusts.sql

Oracle Database Release 19 Post-Upgrade Status Tool 09-04-2023 15:05:3
Database Name: GGSRC04T
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.3.0.0.0 00:22:33
Oracle Label Security VALID 19.3.0.0.0 00:00:16
Oracle Database Vault VALID 19.3.0.0.0 00:00:27
Oracle Workspace Manager VALID 19.3.0.0.0 00:00:33
Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:00
Oracle XML Database VALID 19.3.0.0.0 00:02:00
Datapatch 00:01:46
Final Actions 00:02:02
Post Upgrade 00:00:17
Post Compile 00:05:54
Total Upgrade Time: 00:35:31
Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.

SQL> select distinct(status) from dba_registry;

STATUS
--------------------------------------------
VALID
OPTION OFF

SQL> select distinct(status) from v$datafile;

STATUS
-------
SYSTEM
ONLINE

SQL>

Step 15) Timezone upgrade

@$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
@$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql

Actual output:

SQL> @$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
Session altered.
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql
Session altered.
INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 3221222464 bytes
Fixed Size 8901696 bytes
Variable Size 754974720 bytes
Database Buffers 2449473536 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 3221222464 bytes
Fixed Size 8901696 bytes
Variable Size 754974720 bytes
Database Buffers 2449473536 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "GGADMIN"."AQ$_OGG$Q_TAB_RXT3_L"
Number of failures: 0
Table list: "GGADMIN"."AQ$_OGG$Q_TAB_RXT3_S"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.
Session altered.
SQL> exit

Step 16) Post timezone upgrade step

@$ORACLE_HOME/rdbms/admin/utlusts.sql
alter system set compatible='19.0.0' scope=spfile;
drop restore point PRE_UPGRADE_GGTGT05T;
shu immediate;
startup;

Actual output:

SQL> sho parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
/db_1/dbs/spfileGGTGT05T.ora
SQL> create pfile='/home/oracle/dba/DBUpgrade_GGTGT05T/pfileGGTGT05T.ora' from spfile;
File created.

SQL> alter system set compatible='19.0.0' scope=spfile;
System altered.

SQL> select scn,GUARANTEE_FLASHBACK_DATABASE,NAME from V$RESTORE_POINT;
SCN GUA
---------- ---
NAME
--------------------------------------------------------------------------------
609484 YES
PRE_UPGRADE_GGTGT05T

SQL> drop restore point PRE_UPGRADE_GGTGT05T;
Restore point dropped.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> startup;

ORACLE instance started.
Total System Global Area 3221222464 bytes
Fixed Size 8901696 bytes
Variable Size 754974720 bytes
Database Buffers 2449473536 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.

Step 17) Post upgrade validations

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
select name,open_mode from v$database;
select distinct(status) from v$datafile;
select distinct(status) from dba_Registry;
select distinct(status) from dba_indexes where owner in ('SYS','SYSTEM');

Actual output:

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
GGSRC04T READ WRITE

SQL> select distinct(status) from v$datafile;

STATUS
-------
SYSTEM
ONLINE

SQL> select distinct(status) from dba_Registry;

STATUS
--------------------------------------------
VALID
OPTION OFF

SQL> select distinct(status) from dba_indexes where owner in ('SYS','SYSTEM');

STATUS
--------
VALID
N/A

SQL> select distinct(version) from dba_Registry;

VERSION
------------------------------
19.0.0.0.0

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

SQL>

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

SQL>

YouTube Video:

Thanks

Let us learn Oracle Database Administration skills

Monday, September 4, 2023

Perform Oracle DB upgrade from 12.2 to 19c using RMAN

Perform Oracle DB upgrade from 12.2 to 19c using RMAN

No comments:

Post a Comment

Flashback data archive steps