A system perform well, but we have to update it then and now due to this evolving era
Why upgrade to 19c
Long term release <-> Premier support <-> end of support coming soon for old versions
[oracle@rac7 ~]$ mkdir upgrade_standy
[oracle@rac7 ~]$ cd upgrade_standy
[oracle@rac7 upgrade_standy]$ pwd
/home/oracle/upgrade_standy
[oracle@rac7 upgrade_standy]$ /u01/app/oracle/product/12.1.0.2/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.3.0.0/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/upgrade_standy
==================
PREUPGRADE SUMMARY
==================
/home/oracle/upgrade_standy/preupgrade.log
/home/oracle/upgrade_standy/preupgrade_fixups.sql
/home/oracle/upgrade_standy/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/home/oracle/upgrade_standy/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/home/oracle/upgrade_standy/postupgrade_fixups.sql
Preupgrade complete: 2022-02-04T19:30:36
[oracle@rac7 upgrade_standy]$ ls -lrt
total 676
drwxr-xr-x 3 oracle oinstall 4096 Feb 4 19:30 oracle
-rw-r--r-- 1 oracle oinstall 15085 Feb 4 19:30 dbms_registry_extended.sql
-rw-r--r-- 1 oracle oinstall 7884 Feb 4 19:30 preupgrade_driver.sql
-rw-r--r-- 1 oracle oinstall 455876 Feb 4 19:30 preupgrade_package.sql
-rw-r--r-- 1 oracle oinstall 14016 Feb 4 19:30 parameters.properties
-rw-r--r-- 1 oracle oinstall 100166 Feb 4 19:30 preupgrade_messages.properties
-rw-r--r-- 1 oracle oinstall 41134 Feb 4 19:30 components.properties
drwxr-xr-x 3 oracle oinstall 4096 Feb 4 19:30 upgrade
-rw-r--r-- 1 oracle oinstall 2 Feb 4 19:30 checksBuffer.tmp
-rw-r--r-- 1 oracle asmadmin 10467 Feb 4 19:30 preupgrade_fixups.sql
-rw-r--r-- 1 oracle asmadmin 9897 Feb 4 19:30 postupgrade_fixups.sql
-rw-r--r-- 1 oracle asmadmin 8094 Feb 4 19:30 preupgrade.log
[oracle@rac7 pre_upgrade]$ cat preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2022-02-05T13:22:39
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: AMIT
Container Name: AMIT
Container ID: 0
Version: 12.1.0.2.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.1.0.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 18
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 12.1.0.2
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
2. Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
2181 MB of archived logs. Check alert log during the upgrade that there
is no write error to the destination due to lack of disk space.
Archiving cannot proceed if the archive log destination is full during
upgrade.
Archive Log Destination:
Parameter : LOG_ARCHIVE_DEST_1
Destination : /data1/AMIT/arch
The database has archiving enabled. The upgrade process will need free
disk space in the archive log destination(s) to generate archived logs to.
3. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database AMIT
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/home/oracle/pre_upgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
4. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 18 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
5. To identify directory objects with symbolic links in the path name, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
Recreate any directory objects listed, using path names that contain no
symbolic links.
Some directory object path names may currently contain symbolic links.
Starting in Release 18c, symbolic links are not allowed in directory
object path names used with BFILE data types, the UTL_FILE package, or
external tables.
6. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
7. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 12.1.0.2
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database AMIT
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/home/oracle/pre_upgrade/postupgrade_fixups.sql
============================================================
[oracle@rac7 upgrade_standy]$ sqlplus '/as sysdba'
SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 4 22:24:58 2022
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2022-02-04 19:30:33
For Source Database: AMIT
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. purge_recyclebin YES None.
2. tempts_notempfile YES None.
3. tablespaces NO Manual fixup recommended.
4. invalid_objects_exist NO Manual fixup recommended.
5. dictionary_stats YES None.
6. min_archive_dest_size NO Informational only.
Further action is optional.
7. rman_recovery_version NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
===========================================================================
SQL> select file_name from dba_Data_files;
FILE_NAME
--------------------------------------------------------------------------
/data1/amit/system.dbf
/data1/amit/sysaux.dbf
/data1/amit/undo1.dbf
/data1/amit/users.dbf
SQL> alter database datafile '/data1/amit/sysaux.dbf' resize 750m;
Database altered.
SQL> alter database datafile '/data1/amit/undo1.dbf' resize 500m;
Database altered.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2022-02-04 22:35:55
SQL> select count(*), object_name from dba_objects where status='INVALID' group by object_name;
no rows selected
SQL> exec dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
SQL> select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from
obj$ o, sum$ s where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8; 2
no rows selected
SQL> select * from v$recover_file;
no rows selected
SQL> select * from v$backup WHERE status != 'NOT ACTIVE';
no rows selected
SQL> Select * from dba_2pc_pending;
no rows selected
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> set lines 200
SQL> set pages 200
SQL> select job_name,state from dba_scheduler_jobs;
JOB_NAME STATE
---------------------------------------- ---------------
XMLDB_NFS_CLEANUP_JOB DISABLED
LOAD_OPATCH_INVENTORY DISABLED
SM$CLEAN_AUTO_SPLIT_MERGE SCHEDULED
RSE$CLEAN_RECOVERABLE_SCRIPT SCHEDULED
FGR$AUTOPURGE_JOB DISABLED
BSLN_MAINTAIN_STATS_JOB SCHEDULED
DRA_REEVALUATE_OPEN_FAILURES SCHEDULED
HM_CREATE_OFFLINE_DICTIONARY DISABLED
ORA$AUTOTASK_CLEAN SCHEDULED
FILE_SIZE_UPD SCHEDULED
CLEANUP_ONLINE_PMO SCHEDULED
CLEANUP_TRANSIENT_PKG SCHEDULED
CLEANUP_TRANSIENT_TYPE SCHEDULED
CLEANUP_TAB_IOT_PMO SCHEDULED
CLEANUP_ONLINE_IND_BUILD SCHEDULED
CLEANUP_NON_EXIST_OBJ SCHEDULED
PMO_DEFERRED_GIDX_MAINT_JOB SCHEDULED
FILE_WATCHER DISABLED
PURGE_LOG SCHEDULED
19 rows selected.
SQL> select job_name,state,enabled from dba_scheduler_jobs;
JOB_NAME STATE ENABL
------------------------------ --------------- -----
PURGE_LOG SCHEDULED TRUE
ORA$AUTOTASK_CLEAN SCHEDULED TRUE
HM_CREATE_OFFLINE_DICTIONARY DISABLED FALSE
DRA_REEVALUATE_OPEN_FAILURES SCHEDULED TRUE
BSLN_MAINTAIN_STATS_JOB SCHEDULED TRUE
FGR$AUTOPURGE_JOB DISABLED FALSE
RSE$CLEAN_RECOVERABLE_SCRIPT SCHEDULED TRUE
SM$CLEAN_AUTO_SPLIT_MERGE SCHEDULED TRUE
LOAD_OPATCH_INVENTORY DISABLED FALSE
FILE_WATCHER DISABLED FALSE
PMO_DEFERRED_GIDX_MAINT_JOB SCHEDULED TRUE
CLEANUP_NON_EXIST_OBJ SCHEDULED TRUE
CLEANUP_ONLINE_IND_BUILD SCHEDULED TRUE
CLEANUP_TAB_IOT_PMO SCHEDULED TRUE
CLEANUP_TRANSIENT_TYPE SCHEDULED TRUE
CLEANUP_TRANSIENT_PKG SCHEDULED TRUE
CLEANUP_ONLINE_PMO SCHEDULED TRUE
FILE_SIZE_UPD SCHEDULED TRUE
XMLDB_NFS_CLEANUP_JOB DISABLED FALSE
19 rows selected.
SQL> select 'execute dbms_scheduler.disable('||''''||owner||'.'||job_name||''''||');' from dba_scheduler_jobs where enabled='TRUE';
'EXECUTEDBMS_SCHEDULER.DISABLE('||''''||OWNER||'.'||JOB_NAME||''''||');'
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
execute dbms_scheduler.disable('SYS.SM$CLEAN_AUTO_SPLIT_MERGE');
execute dbms_scheduler.disable('SYS.RSE$CLEAN_RECOVERABLE_SCRIPT');
execute dbms_scheduler.disable('SYS.BSLN_MAINTAIN_STATS_JOB');
execute dbms_scheduler.disable('SYS.DRA_REEVALUATE_OPEN_FAILURES');
execute dbms_scheduler.disable('SYS.ORA$AUTOTASK_CLEAN');
execute dbms_scheduler.disable('SYS.FILE_SIZE_UPD');
execute dbms_scheduler.disable('SYS.CLEANUP_ONLINE_PMO');
execute dbms_scheduler.disable('SYS.CLEANUP_TRANSIENT_PKG');
execute dbms_scheduler.disable('SYS.CLEANUP_TRANSIENT_TYPE');
execute dbms_scheduler.disable('SYS.CLEANUP_TAB_IOT_PMO');
execute dbms_scheduler.disable('SYS.CLEANUP_ONLINE_IND_BUILD');
execute dbms_scheduler.disable('SYS.CLEANUP_NON_EXIST_OBJ');
execute dbms_scheduler.disable('SYS.PMO_DEFERRED_GIDX_MAINT_JOB');
execute dbms_scheduler.disable('SYS.PURGE_LOG');
14 rows selected.
SQL> execute dbms_scheduler.disable('SYS.SM$CLEAN_AUTO_SPLIT_MERGE');
execute dbms_scheduler.disable('SYS.RSE$CLEAN_RECOVERABLE_SCRIPT');
execute dbms_scheduler.disable('SYS.BSLN_MAINTAIN_STATS_JOB');
execute dbms_scheduler.disable('SYS.DRA_REEVALUATE_OPEN_FAILURES');
execute dbms_scheduler.disable('SYS.ORA$AUTOTASK_CLEAN');
execute dbms_scheduler.disable('SYS.FILE_SIZE_UPD');
execute dbms_scheduler.disable('SYS.CLEANUP_ONLINE_PMO');
execute dbms_scheduler.disable('SYS.CLEANUP_TRANSIENT_PKG');
execute dbms_scheduler.disable('SYS.CLEANUP_TRANSIENT_TYPE');
execute dbms_scheduler.disable('SYS.CLEANUP_TAB_IOT_PMO');
execute dbms_scheduler.disable('SYS.CLEANUP_ONLINE_IND_BUILD');
execute dbms_scheduler.disable('SYS.CLEANUP_NON_EXIST_OBJ');
execute dbms_scheduler.disable('SYS.PMO_DEFERRED_GIDX_MAINT_JOB');
execute dbms_scheduler.disable('SYS.PURGE_LOG');
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> SELECT client_name, status FROM dba_autotask_client;
CLIENT_NAME STATUS
------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
SQL>
SQL>
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> SELECT client_name, status FROM dba_autotask_client;
CLIENT_NAME STATUS
-------------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor DISABLED
sql tuning advisor DISABLED
SQL> set lines 200
col OWNER for a40
select TABLE_NAME,OWNER,TABLESPACE_NAME from dba_tables where TABLE_NAME='AUD$';SQL> SQL>
TABLE_NAME OWNER TABLESPACE_NAME
---------------------- ---------- -------------------
AUD$ SYS SYSTEM
SQL> col username for a15
select USERNAME,DEFAULT_TABLESPACE from dba_users where username in ('SYS','SYSTEM');SQL>
USERNAME DEFAULT_TABLESPACE
--------------- ------------------------------
SYS SYSTEM
SYSTEM SYSTEM
SQL> set lines 200
set pages 200
COLUMN comp_id FORMAT A10
COLUMN comp_name FORMAT A35
COLUMN version FORMAT A14
SELECT comp_id,comp_name,version FROM dba_registry;SQL> SQL> SQL> SQL> SQL> SQL>
COMP_ID COMP_NAME VERSION
---------- ----------------------------------- --------------
XDB Oracle XML Database 12.1.0.2.0
CATALOG Oracle Database Catalog Views 12.1.0.2.0
CATPROC Oracle Database Packages and Types 12.1.0.2.0
SQL>
SQL>
SQL>
================================================================================
3. Disable Dataguard broker (If you have configured)
If you are not using the broker then use the manual method to disable the dataguard log shipping from primary to standby. Also disable the broker configuration.
Now Stop Data Guard: On the primary database, defer the redo log transport to the standby database. This is not necessary, but can be done. Be sure to verify that log_archive_dest_state_2 is the actual archive destination for your standby database
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=SAMIT LGWR VALID_FOR=(
ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=SAMIT
SQL> alter system set log_archive_dest_state_2='defer' scope=both;
System altered.
Next, you cancel redo apply on the standby database:
SQL> alter database recover managed standby database cancel;
Database altered.
Finally, you shut down the database:
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
If you are using Grid Infrastructure (GI) to manage the database, you should stop and disable the database. Disabling the database is not must but can be done.
Upgrade the primary db to 19c:
Now you can upgrade the primary database using the method you prefer.
Shutdown Database
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Copy init and password files from 12c to 19c dbs home
[oracle@rac7 upgrad1]$ cd /u01/app/oracle/product/12.1.0.2/db_1/dbs
[oracle@rac7 dbs]$ ls -lrt
total 8008
-rw-r--r-- 1 oracle asmadmin 635 Feb 4 18:20 initPAMIT.ora
-rw-r----- 1 oracle oinstall 7680 Feb 4 18:28 orapwPAMIT
-rw-r----- 1 oracle asmadmin 3584 Feb 4 23:00 spfilePAMIT.ora
[oracle@rac7 dbs]$ cp spfilePAMIT.ora orapwPAMIT /u01/app/oracle/product/19.3.0.0/dbs
[oracle@rac7 dbs]$
[oracle@rac7 dbs]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0.0
[oracle@rac7 dbs]$
[oracle@rac7 dbs]$ echo $ORACLE_SID
PAMIT
[oracle@rac7 dbs]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0.0
[oracle@rac7 dbs]$
[oracle@rac7 dbs]$
[oracle@rac7 dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 5 13:33:49 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 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 503316480 bytes
Database Buffers 1627389952 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select name, open_mode,cdb,i.con_id, version from V$database,v$instance i;
NAME OPEN_MODE CDB CON_ID VERSION
--------- -------------------- --- ---------- -----------------
AMIT READ WRITE NO 0 19.0.0.0.0
SQL> exit
[oracle@rac7 dbs]$ pwd
/u01/app/oracle/product/19.3.0.0/dbs
[oracle@rac7 dbs]$ cd ../bin
[oracle@rac7 bin]$ ls -lrt dbupgrade
-rwxr-x--- 1 oracle oinstall 3136 Apr 17 2019 dbupgrade
[oracle@rac7 bin]$ ./dbupgrade -n 2 -l /home/oracle/actual_upgrade
Argument list for [/u01/app/oracle/product/19.3.0.0/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 = /home/oracle/actual_upgrade
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 2
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.3.0.0/rdbms/admin/orahome = [/u01/app/oracle/product/19.3.0.0]
/u01/app/oracle/product/19.3.0.0/bin/orabasehome = [/u01/app/oracle/product/19.3.0.0]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.3.0.0]
Analyzing file /u01/app/oracle/product/19.3.0.0/rdbms/admin/catupgrd.sql
Log file directory = [/home/oracle/actual_upgrade]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/actual_upgrade/catupgrd_catcon_43173.lst]
catcon::set_log_file_base_path: catcon: See [/home/oracle/actual_upgrade/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/home/oracle/actual_upgrade/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = PAMIT
DataBase Version = 12.1.0.2.0
Parallel SQL Process Count = 2
Components in [PAMIT]
Installed [CATALOG CATPROC XDB]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM OWM RAC SDO WK XML XOQ]
------------------------------------------------------
Phases [0-107] Start Time:[2022_02_05 13:37:03]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [PAMIT] Files:1 Time: 35s
*************** Catalog Core SQL ***************
Serial Phase #:1 [PAMIT] Files:5 Time: 30s
Restart Phase #:2 [PAMIT] Files:1 Time: 2s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [PAMIT] Files:19 Time: 20s
Restart Phase #:4 [PAMIT] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [PAMIT] Files:7 Time: 11s
***************** Catproc Start ****************
Serial Phase #:6 [PAMIT] Files:1 Time: 12s
***************** Catproc Types ****************
Serial Phase #:7 [PAMIT] Files:2 Time: 10s
Restart Phase #:8 [PAMIT] Files:1 Time: 2s
**************** Catproc Tables ****************
Parallel Phase #:9 [PAMIT] Files:67 Time: 31s
Restart Phase #:10 [PAMIT] Files:1 Time: 2s
************* Catproc Package Specs ************
Serial Phase #:11 [PAMIT] Files:1 Time: 54s
Restart Phase #:12 [PAMIT] Files:1 Time: 1s
************** Catproc Procedures **************
Parallel Phase #:13 [PAMIT] Files:94 Time: 17s
Restart Phase #:14 [PAMIT] Files:1 Time: 1s
Parallel Phase #:15 [PAMIT] Files:120 Time: 32s
Restart Phase #:16 [PAMIT] Files:1 Time: 1s
Serial Phase #:17 [PAMIT] Files:22 Time: 4s
Restart Phase #:18 [PAMIT] Files:1 Time: 2s
***************** Catproc Views ****************
Parallel Phase #:19 [PAMIT] Files:32 Time: 25s
Restart Phase #:20 [PAMIT] Files:1 Time: 1s
Serial Phase #:21 [PAMIT] Files:3 Time: 9s
Restart Phase #:22 [PAMIT] Files:1 Time: 2s
Parallel Phase #:23 [PAMIT] Files:25 Time: 145s
Restart Phase #:24 [PAMIT] Files:1 Time: 2s
Parallel Phase #:25 [PAMIT] Files:12 Time: 87s
Restart Phase #:26 [PAMIT] Files:1 Time: 2s
Serial Phase #:27 [PAMIT] Files:1 Time: 0s
Serial Phase #:28 [PAMIT] Files:3 Time: 5s
Serial Phase #:29 [PAMIT] Files:1 Time: 0s
Restart Phase #:30 [PAMIT] Files:1 Time: 2s
*************** Catproc CDB Views **************
Serial Phase #:31 [PAMIT] Files:1 Time: 2s
Restart Phase #:32 [PAMIT] Files:1 Time: 2s
Serial Phase #:34 [PAMIT] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [PAMIT] Files:293 Time: 22s
Serial Phase #:36 [PAMIT] Files:1 Time: 0s
Restart Phase #:37 [PAMIT] Files:1 Time: 2s
Serial Phase #:38 [PAMIT] Files:6 Time: 8s
Restart Phase #:39 [PAMIT] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [PAMIT] Files:3 Time: 44s
Restart Phase #:41 [PAMIT] Files:1 Time: 1s
****************** Catproc SQL *****************
Parallel Phase #:42 [PAMIT] Files:13 Time: 103s
Restart Phase #:43 [PAMIT] Files:1 Time: 2s
Parallel Phase #:44 [PAMIT] Files:11 Time: 13s
Restart Phase #:45 [PAMIT] Files:1 Time: 1s
Parallel Phase #:46 [PAMIT] Files:3 Time: 2s
Restart Phase #:47 [PAMIT] Files:1 Time: 1s
************* Final Catproc scripts ************
Serial Phase #:48 [PAMIT] Files:1 Time: 7s
Restart Phase #:49 [PAMIT] Files:1 Time: 1s
************** Final RDBMS scripts *************
Serial Phase #:50 [PAMIT] Files:1 Time: 18s
************ Upgrade Component Start ***********
Serial Phase #:51 [PAMIT] Files:1 Time: 2s
Restart Phase #:52 [PAMIT] Files:1 Time: 2s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [PAMIT] Files:2 Time: 2s
***************** Upgrading XDB ****************
Restart Phase #:54 [PAMIT] Files:1 Time: 2s
Serial Phase #:56 [PAMIT] Files:3 Time: 11s
Serial Phase #:57 [PAMIT] Files:3 Time: 5s
Parallel Phase #:58 [PAMIT] Files:10 Time: 6s
Parallel Phase #:59 [PAMIT] Files:25 Time: 8s
Serial Phase #:60 [PAMIT] Files:4 Time: 8s
Serial Phase #:61 [PAMIT] Files:1 Time: 0s
Serial Phase #:62 [PAMIT] Files:32 Time: 6s
Serial Phase #:63 [PAMIT] Files:1 Time: 0s
Parallel Phase #:64 [PAMIT] Files:6 Time: 9s
Serial Phase #:65 [PAMIT] Files:2 Time: 16s
Serial Phase #:66 [PAMIT] Files:3 Time: 28s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [PAMIT] Files:1 Time: 2s
Serial Phase #:69 [PAMIT] Files:1 Time: 4s
Parallel Phase #:70 [PAMIT] Files:2 Time: 5s
Restart Phase #:71 [PAMIT] Files:1 Time: 1s
Parallel Phase #:72 [PAMIT] Files:2 Time: 4s
Serial Phase #:73 [PAMIT] Files:2 Time: 5s
***************** Upgrading SDO ****************
Restart Phase #:74 [PAMIT] Files:1 Time: 1s
Serial Phase #:76 [PAMIT] Files:1 Time: 5s
Serial Phase #:77 [PAMIT] Files:2 Time: 4s
Restart Phase #:78 [PAMIT] Files:1 Time: 1s
Serial Phase #:79 [PAMIT] Files:1 Time: 5s
Restart Phase #:80 [PAMIT] Files:1 Time: 0s
Parallel Phase #:81 [PAMIT] Files:3 Time: 5s
Restart Phase #:82 [PAMIT] Files:1 Time: 1s
Serial Phase #:83 [PAMIT] Files:1 Time: 5s
Restart Phase #:84 [PAMIT] Files:1 Time: 2s
Serial Phase #:85 [PAMIT] Files:1 Time: 4s
Restart Phase #:86 [PAMIT] Files:1 Time: 2s
Parallel Phase #:87 [PAMIT] Files:4 Time: 4s
Restart Phase #:88 [PAMIT] Files:1 Time: 1s
Serial Phase #:89 [PAMIT] Files:1 Time: 4s
Restart Phase #:90 [PAMIT] Files:1 Time: 1s
Serial Phase #:91 [PAMIT] Files:2 Time: 4s
Restart Phase #:92 [PAMIT] Files:1 Time: 1s
Serial Phase #:93 [PAMIT] Files:1 Time: 2s
Restart Phase #:94 [PAMIT] Files:1 Time: 2s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [PAMIT] Files:1 Time: 2s
Restart Phase #:96 [PAMIT] Files:1 Time: 2s
*********** Final Component scripts ***********
Serial Phase #:97 [PAMIT] Files:1 Time: 2s
************* Final Upgrade scripts ************
Serial Phase #:98 [PAMIT] Files:1 Time: 58s
******************* Migration ******************
Serial Phase #:99 [PAMIT] Files:1 Time: 43s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [PAMIT] Files:1 Time: 2s
Serial Phase #:101 [PAMIT] Files:1 Time: 0s
Serial Phase #:102 [PAMIT] Files:1 Time: 43s
***************** Post Upgrade *****************
Serial Phase #:103 [PAMIT] Files:1 Time: 24s
**************** Summary report ****************
Serial Phase #:104 [PAMIT] Files:1 Time: 2s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [PAMIT] Files:1 Time: 2s
Serial Phase #:106 [PAMIT] Files:1 Time: 0s
Serial Phase #:107 [PAMIT] Files:1 Time: 31s
------------------------------------------------------
Phases [0-107] End Time:[2022_02_05 13:56:30]
------------------------------------------------------
Grand Total Time: 1168s
LOG FILES: (/home/oracle/actual_upgrade/catupgrd*.log)
Upgrade Summary Report Located in:
/home/oracle/actual_upgrade/upg_summary.log
Grand Total Upgrade Time: [0d:0h:19m:28s]
[oracle@rac7 bin]$
[oracle@rac7 bin]$
[oracle@rac7 bin]$ ps -ef| grep smon
grid 24360 1 0 10:27 ? 00:00:00 asm_smon_+ASM
oracle 50052 38209 0 14:30 pts/1 00:00:00 grep --color=auto smon
[oracle@rac7 bin]$
[oracle@rac7 bin]$
[oracle@rac7 bin]$ sqlplus '/as sysdba'
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 5 15:11:04 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 503316480 bytes
Database Buffers 1627389952 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
SQL> select count(1) from dba_objects where status='INVALID';
COUNT(1)
----------
2209
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2022-02-05 15:13:29
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>#
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2022-02-05 15:19:29
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
SQL> SQL> SQL> SQL> SQL>
SQL>
SQL>
[oracle@rac7 pre_upgrade]$ sqlplus '/as sysdba'
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 5 15:21:45 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> @postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
PL/SQL procedure successfully completed.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2022-02-05 13:22:39
For Source Database: AMIT
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
4. old_time_zones_exist NO Manual fixup recommended.
5. dir_symlinks YES None.
6. post_dictionary YES None.
7. post_fixed_objects NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
Session altered.
SQL>
The script utlusts.sql generates a comprehensive report about the upgrade, including invalid objects, deprecated features, and status details.
TEXT
When you pass TEXT as an argument, the output is displayed in a text format, making it easy to read directly in the terminal or redirect to a log file.
SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT
Oracle Database Release 19 Post-Upgrade Status Tool 02-05-2022 15:29:5
Database Name: AMIT
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.3.0.0.0 00:12:54
Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:00
Oracle XML Database VALID 19.3.0.0.0 00:01:32
Datapatch 00:00:53
Final Actions 00:01:40
Post Upgrade 00:00:20
Post Compile 00:06:00
Total Upgrade Time: 00:23:59
Database time zone version is 18. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.
Purpose of catuppst.sql
This script is part of the database upgrade process and is used to:
- Perform post-upgrade actions to finalize the upgrade.
- Ensure all required database components and features are fully upgraded to the current version.
- Validate and register components that might require adjustments after the initial upgrade process.
When to Run
- After successfully running the main upgrade script (
catctl.plordbupgrade). - As part of the steps listed in Oracle's upgrade documentation for the specific version you're upgrading to.
SQL> @?/rdbms/admin/catuppst.sql
Session altered.
Session altered.
Session altered.
PL/SQL procedure successfully completed.
Session altered.
System altered.
PL/SQL procedure successfully completed.
Session altered.
TIMESTAMP
---------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART 2022-02-05 15:38:21
DBUA_TIMESTAMP DBRESTART FINISHED 2022-02-05 15:38:21
DBUA_TIMESTAMP DBRESTART NONE 2022-02-05 15:38:21
TIMESTAMP
---------------------------------------------------------------------
DBUA_TIMESTAMP CATUPPST STARTED 2022-02-05 15:38:21
TIMESTAMP
---------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2022-02-05 15:38:21
DBUA_TIMESTAMP POSTUP_BGN FINISHED 2022-02-05 15:38:21
DBUA_TIMESTAMP POSTUP_BGN NONE 2022-02-05 15:38:21
TIMESTAMP
---------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN 2022-02-05 15:38:21
DBUA_TIMESTAMP CATREQ_BGN FINISHED 2022-02-05 15:38:21
DBUA_TIMESTAMP CATREQ_BGN NONE 2022-02-05 15:38:21
PL/SQL procedure successfully completed.
catrequtlmg: b_StatEvt = TRUE
catrequtlmg: b_SelProps = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig = FALSE
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END 2022-02-05 15:38:21
DBUA_TIMESTAMP CATREQ_END FINISHED 2022-02-05 15:38:21
DBUA_TIMESTAMP CATREQ_END NONE 2022-02-05 15:38:21
catuppst: Dropping library DBMS_DDL_INTERNAL_LIB
PL/SQL procedure successfully completed.
catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping view DBA_PART_KEY_COLUMNS_V$_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V$_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping table OBJ$MIG
catuppst: Dropping table USER$MIG
catuppst: Dropping table COL$MIG
catuppst: Dropping table CLU$MIG
catuppst: Dropping table CON$MIG
catuppst: Dropping table BOOTSTRAP$MIG
catuppst: Dropping table TAB$MIG
catuppst: Dropping table TS$MIG
catuppst: Dropping table IND$MIG
catuppst: Dropping table ICOL$MIG
catuppst: Dropping table LOB$MIG
catuppst: Dropping table COLTYPE$MIG
catuppst: Dropping table SUBCOLTYPE$MIG
catuppst: Dropping table NTAB$MIG
catuppst: Dropping table REFCON$MIG
catuppst: Dropping table OPQTYPE$MIG
catuppst: Dropping table ICOLDEP$MIG
catuppst: Dropping table VIEWTRCOL$MIG
catuppst: Dropping table ATTRCOL$MIG
catuppst: Dropping table TYPE_MISC$MIG
catuppst: Dropping table LIBRARY$MIG
catuppst: Dropping table ASSEMBLY$MIG
catuppst: Dropping table TSQ$MIG
catuppst: Dropping table FET$MIG
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
TIMESTAMP
---------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2022-02-05 15:38:21
DBUA_TIMESTAMP POSTUP_END FINISHED 2022-02-05 15:38:21
DBUA_TIMESTAMP POSTUP_END NONE 2022-02-05 15:38:21
TIMESTAMP
---------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST 2022-02-05 15:38:21
DBUA_TIMESTAMP CATUPPST FINISHED 2022-02-05 15:38:21
DBUA_TIMESTAMP CATUPPST NONE 2022-02-05 15:38:21
Session altered.
SQL>
SQL>
Change the DST time zone 19c
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
18
SQL>
SQL>
Time Zone File Updates in Oracle
Oracle databases often store timestamp information with time zones. Time zone definitions (rules for handling daylight saving time, offsets, etc.) can change over time due to geopolitical or legislative changes. Oracle provides updated time zone files in new database versions to reflect these changes.
2. Purpose of Each Script :-
a. utltz_countstats.sql
- Purpose: Generates statistics about how many time zone data-dependent objects exist in the database.
- Why Run It: To get a summary of objects (tables, columns, etc.) using time zone-sensitive data before deciding whether a time zone file upgrade is necessary.
b. utltz_countstar.sql
- Purpose: Similar to
utltz_countstats.sql, this script identifies and counts the specific rows in tables with time zone-dependent data. - Why Run It: Helps understand the scope and size of data affected by a potential time zone file upgrade. This is particularly useful for planning downtime and impact assessment.
c. utltz_upg_check.sql
- Purpose: Checks for inconsistencies or issues that could arise when upgrading the database's time zone file.
- Why Run It: Ensures that there are no blockers before applying the actual time zone file upgrade. For example, it checks for unsupported time zone versions or incorrect settings.
d. utltz_upg_apply.sql
- Purpose: Applies the time zone file upgrade to the database.
- Why Run It: After successfully upgrading the database, you need to ensure it uses the latest time zone definitions. This script upgrades time zone data-dependent objects and ensures compatibility with the new time zone file version.
SQL> @utltz_countstats.sql
Session altered.
.
Amount of TSTZ data using num_rows stats info in DBA_TABLES.
.
For SYS tables first ...
Note: empty tables are not listed.
Stat date - Owner.TableName.ColumnName - num_rows
05/02/2022 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 4
05/02/2022 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 4
05/02/2022 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 4
05/02/2022 - SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
05/02/2022 - SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
05/02/2022 - SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
05/02/2022 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
05/02/2022 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
05/02/2022 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
05/02/2022 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
05/02/2022 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
05/02/2022 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
05/02/2022 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1
05/02/2022 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1
05/02/2022 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1
05/02/2022 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
05/02/2022 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
05/02/2022 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
05/02/2022 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
05/02/2022 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
05/02/2022 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
05/02/2022 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
05/02/2022 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
05/02/2022 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
05/02/2022 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
05/02/2022 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
05/02/2022 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
05/02/2022 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
05/02/2022 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
05/02/2022 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
05/02/2022 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
05/02/2022 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
05/02/2022 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
05/02/2022 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
05/02/2022 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
05/02/2022 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
05/02/2022 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
05/02/2022 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45
05/02/2022 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45
05/02/2022 - SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 434
05/02/2022 - SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72
05/02/2022 - SYS.RADM_FPTM$.TSWTZ_COL - 1
05/02/2022 - SYS.REG$.NTFN_GROUPING_START_TIME - 2
05/02/2022 - SYS.REG$.REG_TIME - 2
05/02/2022 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 43
05/02/2022 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
05/02/2022 - SYS.SCHEDULER$_JOB.END_DATE - 21
05/02/2022 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 21
05/02/2022 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 21
05/02/2022 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 21
05/02/2022 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 21
05/02/2022 - SYS.SCHEDULER$_JOB.START_DATE - 21
05/02/2022 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
05/02/2022 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
05/02/2022 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
05/02/2022 - SYS.SCHEDULER$_WINDOW.END_DATE - 9
05/02/2022 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
05/02/2022 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
05/02/2022 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
05/02/2022 - SYS.SCHEDULER$_WINDOW.START_DATE - 9
05/02/2022 - SYS.TAB_STATS$.SPARE6 - 1025
05/02/2022 - SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 16
05/02/2022 - SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 16
05/02/2022 - SYS.WRI$_OPTSTAT_AUX_HISTORY.SAVTIME - 18
05/02/2022 - SYS.WRI$_OPTSTAT_AUX_HISTORY.SPARE6 - 18
05/02/2022 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - 29613
05/02/2022 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - 29613
05/02/2022 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 49191
05/02/2022 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 49191
05/02/2022 - SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 2684
05/02/2022 - SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 2684
05/02/2022 - SYS.WRI$_OPTSTAT_OPR.END_TIME - 238
05/02/2022 - SYS.WRI$_OPTSTAT_OPR.SPARE6 - 238
05/02/2022 - SYS.WRI$_OPTSTAT_OPR.START_TIME - 238
05/02/2022 - SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 7972
05/02/2022 - SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 7972
05/02/2022 - SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 7972
05/02/2022 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 3770
05/02/2022 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 3770
05/02/2022 - SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ - 6
05/02/2022 - SYS.WRM$_PDB_INSTANCE.OPEN_TIME_TZ - 1
05/02/2022 - SYS.WRM$_PDB_INSTANCE.STARTUP_TIME_TZ - 1
05/02/2022 - SYS.WRM$_PDB_IN_SNAP.OPEN_TIME_TZ - 1
05/02/2022 - SYS.WRM$_SNAPSHOT.BEGIN_INTERVAL_TIME_TZ - 2
05/02/2022 - SYS.WRM$_SNAPSHOT.END_INTERVAL_TIME_TZ - 2
05/02/2022 - SYS.XS$PRIN.END_DATE - 14
05/02/2022 - SYS.XS$PRIN.START_DATE - 14
Total numrows of SYS TSTZ columns is : 197202
There are in total 162 SYS TSTZ columns.
.
For non-SYS tables ...
Note: empty tables are not listed.
Stat date - Owner.Tablename.Columnname - num_rows
05/02/2022 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
05/02/2022 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
05/02/2022 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME -
1
Total numrows of non-SYS TSTZ columns is : 3
There are in total 5 non-SYS TSTZ columns.
Total Minutes elapsed : 0
Session altered.
SQL>
SQL>
SQL> @utltz_countstar.sql
Session altered.
.
Estimating amount of TSTZ data using COUNT(*).
This might take some time ...
.
For SYS tables first ...
Note: empty tables are not listed.
Owner.TableName.ColumnName - COUNT(*) of that column
SYS.AQ$_ALERT_QT_S.CREATION_TIME - 4
SYS.AQ$_ALERT_QT_S.DELETION_TIME - 4
SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 4
SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1
SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45
SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45
SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 824
SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72
SYS.RADM_FPTM$.TSWTZ_COL - 1
SYS.REG$.NTFN_GROUPING_START_TIME - 2
SYS.REG$.REG_TIME - 2
SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 43
SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
SYS.SCHEDULER$_JOB.END_DATE - 21
SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 21
SYS.SCHEDULER$_JOB.LAST_END_DATE - 21
SYS.SCHEDULER$_JOB.LAST_START_DATE - 21
SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 21
SYS.SCHEDULER$_JOB.START_DATE - 21
SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
SYS.SCHEDULER$_WINDOW.END_DATE - 9
SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
SYS.SCHEDULER$_WINDOW.START_DATE - 9
SYS.TAB_STATS$.SPARE6 - 1025
SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 16
SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 16
SYS.WRI$_OPTSTAT_AUX_HISTORY.SAVTIME - 18
SYS.WRI$_OPTSTAT_AUX_HISTORY.SPARE6 - 18
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - 30502
SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - 30502
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 49336
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 49336
SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 2695
SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 2695
SYS.WRI$_OPTSTAT_OPR.END_TIME - 238
SYS.WRI$_OPTSTAT_OPR.SPARE6 - 238
SYS.WRI$_OPTSTAT_OPR.START_TIME - 238
SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 7981
SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 7981
SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 7981
SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 3771
SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 3771
SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ - 6
SYS.WRM$_PDB_INSTANCE.OPEN_TIME_TZ - 1
SYS.WRM$_PDB_INSTANCE.STARTUP_TIME_TZ - 1
SYS.WRM$_PDB_IN_SNAP.OPEN_TIME_TZ - 1
SYS.WRM$_SNAPSHOT.BEGIN_INTERVAL_TIME_TZ - 2
SYS.WRM$_SNAPSHOT.END_INTERVAL_TIME_TZ - 2
SYS.XS$PRIN.END_DATE - 15
SYS.XS$PRIN.START_DATE - 15
Total count * of SYS TSTZ columns is : 199713
There are in total 162 SYS TSTZ columns.
.
For non-SYS tables ...
Note: empty tables are not listed.
Owner.TableName.ColumnName - COUNT(*) of that column
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME - 1
Total count * of non-SYS TSTZ columns is : 3
There are in total 5 non-SYS TSTZ columns.
Total Minutes elapsed : 0
Session altered.
SQL>
SQL>
SQL> @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 DSTv18 .
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>
SQL> @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 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 503316480 bytes
Database Buffers 1627389952 bytes
Redo Buffers 7876608 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 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 503316480 bytes
Database Buffers 1627389952 bytes
Redo Buffers 7876608 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
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>
SQL>
SQL>
SQL>
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
32
1 row selected.
SQL>
Set compatible parameter to 19.0.0(need a bounce)
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- --------------
compatible string 12.1.0.2.0
noncdb_compatible boolean FALSE
SQL>
SQL>
SQL>
SQL> alter system set compatible='19.0.0' scope=spfile;
System altered.
SQL>
SQL>
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup;
ORACLE instance started.
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 503316480 bytes
Database Buffers 1627389952 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ----------------
compatible string 19.0.0
noncdb_compatible boolean FALSE
SQL>
SQL> show parameter LOG_ARCHIVE_DEST_STATE_2
NAME TYPE VALUE
------------------------------------ ----------- --------------------
log_archive_dest_state_2 string defer
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
SQL>
SQL>
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
System altered.
SQL> show parameter LOG_ARCHIVE_DEST_STATE_2
NAME TYPE VALUE
------------------------------------ ----------- --------------------
log_archive_dest_state_2 string ENABLE
SQL> SET LINES 180
COL DEST_NAME FOR A30
COL ERROR FOR A60
select DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest where dest_id < 3;
DEST_ID DEST_NAME STATUS ERROR
---------- ------------------------------ --------- -----------------
1 LOG_ARCHIVE_DEST_1 VALID
2 LOG_ARCHIVE_DEST_2 ERROR ORA-16484: compatibility setting is too low
2 rows selected.
================================
Standby :-
[oracle@rac8 dbs]$
[oracle@rac8 dbs]$ scp orapwSAMIT spfileSAMIT.ora /u01/app/oracle/product/19.3.0.0/dbs
[oracle@rac8 dbs]$
[oracle@rac8 dbs]$
[oracle@rac8 dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 5 16:00:53 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> exit
Disconnected
[oracle@rac8 dbs]$
[oracle@rac8 dbs]$
[oracle@rac8 dbs]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0.0
[oracle@rac8 dbs]$
[oracle@rac8 dbs]$ echo $ORACLE_SID
SAMIT
[oracle@rac8 dbs]$
[oracle@rac8 dbs]$
[oracle@rac8 dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 5 16:01:46 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 520093696 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7876608 bytes
SQL>
SQL>
SQL>
SQL> alter database mount standby database;
Database altered.
SQL>
SQL>
Enable MRP on Standby
SQL> select name,open_mode,version from v$database,v$instance;
NAME OPEN_MODE VERSION
--------- -------------------- -----------------
AMIT MOUNTED 19.0.0.0.0
SQL>
SQL> show parameter COMPATIBLE
NAME TYPE VALUE
------------------------------------ ----------- --------------------
compatible string 12.1.0.2.0
noncdb_compatible boolean FALSE
SQL>
SQL>
SQL>
SQL> SELECT ARCH.THREAD# "Thread",
ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#; 2 3 4 5 6 7 8
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 220 120 100
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 220 128 92
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 220 220 0
SQL> show parameter COMPATIBLE
NAME TYPE VALUE
------------------------------------ ----------- --------------------
compatible string 12.1.0.2.0
noncdb_compatible boolean FALSE
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
DGRD ALLOCATED 0 0
DGRD ALLOCATED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
MRP0 WAIT_FOR_GAP 221 1
RFS IDLE 0 0
RFS IDLE 309 1
SQL>
SQL>
SQL> alter system set compatible='19.0.0' scope=spfile;
System altered.
SQL>
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2147481656 bytes
Fixed Size 8898616 bytes
Variable Size 520093696 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7876608 bytes
SQL>
SQL>
SQL>
SQL> alter database mount standby database;
Database altered.
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- --------------------compatible string 19.0.0
noncdb_compatible boolean FALSE
SQL>
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
SQL>
SQL> SELECT ARCH.THREAD# "Thread",
ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#; 2 3 4 5 6 7 8
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 309 309 0
SQL>
SQL>
No comments:
Post a Comment