Cause:
You try to ssh to EC2 compute instance using a public key authentication with doesnot match the key value pair which you have generated
Solution:
Use puttygen to generate a .ppk file from the .pem file to generate ssh key value pair
Import the private key which you have generated into putty configuration
Works like charm!
Hope this post might save someones time !
Cause:
When you specify to_date() function,nls_date_format parameter should support the format which you give
Most probably ‘MM-DD-YYYY‘ format works well
SQL> !oerr ORA 14120 14120, 00000, "incompletely specified partition bound for a DATE column" // *Cause: An attempt was made to use a date expression whose format // does not fully (i.e. day, month, and year (including century)) // specify a date as a partition bound for a DATE column. // The format may have been specified explicitly (using // TO_DATE() function) or implicitly (NLS_DATE_FORMAT).
Solution:
// *Action: Ensure that date format used in a partition bound for a
// DATE column supports complete specification of a date
// (i.e. day, month, and year (including century)).
// If NLS_DATE_FORMAT does not support complete
// (i.e. including the century) specification of the year,
// use TO_DATE() (e.g. TO_DATE('01-01-1999', 'MM-DD-YYYY')
// to fully express the desired date.
Check nls format for date
SQL> alter session set nls_date_format='DD-MM-YYYY'; Session altered. SQL> show parameter nls_date_format NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ nls_date_format string DD-MM-YYYY
Specify the below format when using to_date()
PARTITION BY RANGE (ORDER_DATE)
(PARTITION P_S1 VALUES LESS THAN (TO_DATE('01-01-2012','DD-MM-YYYY')),
PARTITION P_S2 VALUES LESS THAN (TO_DATE('01-01-2015','DD-MM-YYYY')),
PARTITION P_S3 VALUES LESS THAN (TO_DATE('01-01-2018','DD-MM-YYYY')),
PARTITION P_S4 VALUES LESS THAN (TO_DATE('01-01-2021','DD-MM-YYYY')),
PARTITION P_S5 VALUES LESS THAN (TO_DATE('01-01-2024','DD-MM-YYYY')),
PARTITION P_S6 VALUES LESS THAN (TO_DATE('01-01-2027','DD-MM-YYYY')),
PARTITION P_S7 VALUES LESS THAN (TO_DATE('01-01-2030','DD-MM-YYYY')),
PARTITION P_S8 VALUES LESS THAN (TO_DATE('01-01-2033','DD-MM-YYYY')),
PARTITION P_S9 VALUES LESS THAN (maxvalue));
Cause:
Silly ! Syntax issue like Commas or braces missing ~Dont scratch your head and waste your time figuring out
PARTITION BY RANGE (ORDER_DATE)
(PARTITION P_S1 VALUES LESS THAN (TO_DATE('01/01/2012','DD/MM/YYYY'))
(PARTITION P_S2 VALUES LESS THAN (TO_DATE('01/01/2015','DD/MM/YYYY')) <----------
(PARTITION P_S3 VALUES LESS THAN (TO_DATE(MAXVALUE)); 5 6 7 8 9 10 11 12 13 14 15 16 17 18
(PARTITION P_S2 VALUES LESS THAN (TO_DATE('01/01/2015','DD/MM/YYYY')) <----------
*
ERROR at line 17:
ORA-14020: this physical attribute may not be specified for a table partition
Solution:
Give the correct syntax with correct quotations and braces on the query
PARTITION BY RANGE (ORDER_DATE)
(PARTITION P_S1 VALUES LESS THAN (TO_DATE('01-01-2012','DD-MM-YYYY')),
PARTITION P_S2 VALUES LESS THAN (TO_DATE('01-01-2015','DD-MM-YYYY')),
PARTITION P_S3 VALUES LESS THAN (TO_DATE('01-01-2018','DD-MM-YYYY')),
PARTITION P_S4 VALUES LESS THAN (TO_DATE('01-01-2021','DD-MM-YYYY')),
PARTITION P_S5 VALUES LESS THAN (TO_DATE('01-01-2024','DD-MM-YYYY')),
PARTITION P_S6 VALUES LESS THAN (TO_DATE('01-01-2027','DD-MM-YYYY')),
PARTITION P_S7 VALUES LESS THAN (TO_DATE('01-01-2030','DD-MM-YYYY')),
PARTITION P_S8 VALUES LESS THAN (TO_DATE('01-01-2033','DD-MM-YYYY')),
PARTITION P_S9 VALUES LESS THAN (maxvalue));
Error:
User from which you try to gather stats doesnot have the necessary privilege!
SQL> !oerr ora 20000 20000, 00000, "%s" // *Cause: The stored procedure 'raise_application_error' // was called which causes this error to be generated. // *Action: Correct the problem as described in the error message or contact // the application administrator or DBA for more information.
SQL> exec dbms_stats.gather_index_stats('TEST1','fn_order_date');
BEGIN dbms_stats.gather_index_stats('TEST1','fn_order_date'); END;
*
ERROR at line 1:
ORA-20000: Unable to analyze INDEX "TEST1"."FN_ORDER_DATE", insufficient
privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 30110
ORA-06512: at "SYS.DBMS_STATS", line 30024
ORA-06512: at "SYS.DBMS_STATS", line 29963
ORA-06512: at "SYS.DBMS_STATS", line 30097
ORA-06512: at line 1
Solution:
Grant execute privilege on dbms stats !
SQL> grant execute on dbms_stats to test1; Grant succeeded.
SQL> exec dbms_stats.gather_index_stats('TEST1','fn_order_date');
PL/SQL procedure successfully completed.
Cause:
When duplicate a database from target to auxiliary,there is a version mismatch in rman client version of auxiliary database
Solution:
Upgrade your catalog on your target database
RMAN> upgrade catalog;
Indexes in a database are not visible to our bare eyes but we have a logical idea that index is a brief record that contain the copy of the specific rowid of tables.
If god come in front of you and offer a wish to make you invisible ! what would it be like ?
will you use it for positive or negative purpose ? 😉
So lets get into invisible index
Invisible index is very useful in case of testing the efficiency of an index which you are not sure if that index performs well in a production or screw up the performance
Take a scenario,where we have to run a select query with join on another table
In this case,my select query takes 5.25 seconds complete the query without any index
SQL> set timing on
set autot traceonly
select a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a
inner join sales1 b on a.order_id = b.order_id
where trunc(a.order_date) > trunc(sysdate) - 1
order by a.total_profit desc;SQL> SQL> 2 3 4
452432 rows selected.
Elapsed: 00:00:05.25
Execution Plan
----------------------------------------------------------
Plan hash value: 2839474500
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 615K| 38M| | 34681 (1)| 00:00:02 |
| 1 | SORT ORDER BY | | 615K| 38M| 42M| 34681 (1)| 00:00:02 |
|* 2 | HASH JOIN | | 615K| 38M| 7640K| 25133 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| SALES | 111K| 6328K| | 3393 (2)| 00:00:01 |
| 4 | TABLE ACCESS FULL| SALES1 | 5242K| 35M| | 16635 (1)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ORDER_ID"="B"."ORDER_ID")
3 - filter(TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"))>TRUNC(SYSDATE@!)-1)
I create a functional index on column trunc(order_date) on sales table to check if performance is good but im not sure if functional index improves the performance
SQL> create index ind_func_date on sales(trunc(order_id)); Index created.
Lets make the index visible first to test query ,already visible here
SQL> select index_name,visibility from dba_indexes where index_name='IND_FUNC_DATE'; INDEX_NAME VISIBILITY ---------------------------------------- -------------------- IDX_FUNC_DATE VISIBLE
Note: we can create an index as invisible SQL> create index ind_func_date on sales(trunc(order_id)) invisible;
Gather stats on table
SQL> exec dbms_stats.gather_table_stats(ownname=>'test1',tabname=>'sales',cascade=>true,degree=>4,method_opt=>'for all columns size auto'); PL/SQL procedure successfully completed.
Hint is used to force the optimizer to use the index
The query perform worse than full table scan after adding an index
SQL> set timing on
set autot traceonly
select /*+ index(a ind_sales_order_id) */ a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a
inner join sales1 b on a.order_id = b.order_id
where trunc(a.order_date) > trunc(sysdate) - 1
order by a.total_profit desc;SQL> SQL> 2 3 4
452432 rows selected.
Elapsed: 00:00:08.53
Execution Plan
----------------------------------------------------------
Plan hash value: 4235492740
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 615K| 42M| | 1083K (1)| 00:00:43 |
| 1 | SORT ORDER BY | | 615K| 42M| 47M| 1083K (1)| 00:00:43 |
|* 2 | HASH JOIN | | 615K| 42M| 8408K| 1073K (1)| 00:00:42 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 111K| 7092K| | 1051K (1)| 00:00:42 |
| 4 | INDEX FULL SCAN | IND_SALES_ORDER_ID | 1048K| | | 2637 (1)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SALES1 | 5242K| 35M| | 16635 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ORDER_ID"="B"."ORDER_ID" AND TRUNC("ORDER_ID")=TRUNC("B"."ORDER_ID"))
3 - filter(TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"))>TRUNC(SYSDATE@!)-1)
Lets add another index on order id column on sales table
SQL> create index ind_sales_order_id on sales(order_id); Index created.
Gather stats for table for our satisfaction
SQL> exec dbms_stats.gather_table_stats(tabname=>'sales',ownname=>'test1',cascade=>true); PL/SQL procedure successfully completed.
Even after forcing the second index,still full table scan is better than using index here
We know that full table scans are not always evil !
SQL> set timing on
set autot traceonly
select /*+ index(a ind_sales_order_id) */ a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a
inner join sales1 b on a.order_id = b.order_id
where trunc(a.order_date) > trunc(sysdate) - 1
order by a.total_profit desc;SQL> SQL> 2 3 4
452432 rows selected.
Elapsed: 00:00:08.53
Execution Plan
----------------------------------------------------------
Plan hash value: 4235492740
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 615K| 42M| | 1083K (1)| 00:00:43 |
| 1 | SORT ORDER BY | | 615K| 42M| 47M| 1083K (1)| 00:00:43 |
|* 2 | HASH JOIN | | 615K| 42M| 8408K| 1073K (1)| 00:00:42 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 111K| 7092K| | 1051K (1)| 00:00:42 |
| 4 | INDEX FULL SCAN | IND_SALES_ORDER_ID | 1048K| | | 2637 (1)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SALES1 | 5242K| 35M| | 16635 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ORDER_ID"="B"."ORDER_ID" AND TRUNC("ORDER_ID")=TRUNC("B"."ORDER_ID"))
3 - filter(TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"))>TRUNC(SYSDATE@!)-1)
So here our testing says that both indexes are useless for this particular query
Better to make indexes invisible to optimizer,but not us as we already know there are indexes hiding behind the wall
SQL> alter index IND_SALES_ORDER_ID invisible; Index altered. SQL> alter index IND_FUNC_DATE invisible; Index altered.
After forcing the optimizer to use index,still optimizer would not use index as they are unusable
In the below execution plan,the outline information shows that the index is not used eventhough we forced the index with hints
SQL> set timing on
set autot traceonly
select /*+ index(a ind_func_date) */ a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a
inner join sales1 b on a.order_id = b.order_id
where trunc(a.order_date) > trunc(sysdate) - 1
ordSQL> er by a.total_profit desc;SQL> 2 3 4
452432 rows selected.
Elapsed: 00:00:05.79
Execution Plan
----------------------------------------------------------
Plan hash value: 2839474500
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 615K| 38M| | 34681 (1)| 00:00:02 |
| 1 | SORT ORDER BY | | 615K| 38M| 42M| 34681 (1)| 00:00:02 |
|* 2 | HASH JOIN | | 615K| 38M| 7640K| 25133 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| SALES | 111K| 6328K| | 3393 (2)| 00:00:01 |
| 4 | TABLE ACCESS FULL| SALES1 | 5242K| 35M| | 16635 (1)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ORDER_ID"="B"."ORDER_ID")
3 - filter(TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"))>TRUNC(SYSDATE@!)-1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
3 - SEL$58A6D7F6 / A@SEL$1
U - index(a ind_func_date)
Again i want to test the index for some other query
SQL> alter index ind_sales_order_id visible; Index altered.
In this case,i need only single row to be retrieved from million rows
Hence,full table scan is not the correct access path here
The better way here is to use the index which we had created earlier for order_id column
SQL> set autot traceonly SQL> select a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a where a.order_id =1120; no rows selected Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 562010242 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 50 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES | 1 | 50 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_SALES_ORDER_ID | 1 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------
This is one of the smart way to reduce the application downtime
Monitoring the index usage
This is one way to monitor the index usage efficiently rather than investigating the execution plans
SQL> alter index IND_SALES_ORDER_ID monitoring usage; Index altered. SQL> alter index IND_FUNC_DATE monitoring usage; Index altered. SQL> select index_name,monitoring,used from v$object_usage; INDEX_NAME MONITORING USED ---------------------------------------- -------------------- ---------------------------------------- IND_SALES_ORDER_ID YES NO IND_FUNC_DATE YES NO
Once the index is tested,we can turn off the auto monitor
SQL> alter index IND_FUNC_DATE nomonitoring usage; Index altered. SQL> alter index IND_SALES_ORDER_ID nomonitoring usage; Index altered. SQL> select index_name,monitoring,used from v$object_usage; INDEX_NAME MONITORING USED ---------------------------------------- -------------------- ---------------------------------------- IND_SALES_ORDER_ID NO NO IND_FUNC_DATE NO NO
we can enable this parameter to make index visible to optimizer at session or system level using “OPTIMIZER_USE_INVISIBLE_INDEXES”
Now we can force the invisible index to optimizer using hints
SQL> alter system set OPTIMIZER_USE_INVISIBLE_INDEXES=true; System altered. SQL> show parameter invisible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_use_invisible_indexes boolean TRUE
Sometimes rebuilding an index on a mammoth table takes huge time and are overhead to a production database.There are always better ways to deal with these kind of hurdles where one of them is here in front of you
Cause:
There are multiple oracle home location A,B, and C running different oracle database version in host and you actually need rman to be launched for A but you launched rman for B from A
[oracle@orcl19x admin]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Sat Dec 19 21:07:39 2020 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-03000: recovery manager compiler component initialization failed RMAN-06001: error parsing job step library RMAN-01009: syntax error: found "identifier": expecting one of: "" RMAN-01008: the bad identifier was: raschemaversion RMAN-01007: at line 17 column 1 file: ?/rdbms/admin/recover.bsq
[oracle@orcl19x apex]$ cat /etc/oratab |grep orcl19x orcl19x:/apps01/product/12.1.0/dbhome_1:Y orcl19x1:/data01/product/19.0.0:Y
Path or oracle home variable is not properly set for the database from which rman try to execute
Solution:
Set the correct variables
[oracle@orcl19x apex]$ export PATH=/data01/product/19.0.0/bin:/usr/sbin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin export ORACLE_HOME=/data01/product/19.0.0
Relink binaries from the correct oracle home
[oracle@orcl19x apex]$ /data01/product/19.0.0/bin/relink all writing relink log to: /data01/product/19.0.0/install/relinkActions2020-12-20_07-21-44PM.log
[oracle@orcl19x apex]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jan 5 01:27:23 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN>
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
current DB version : 12.1.0.2.0 (single instance non PDB)
upgraded DB version : 19.0.0.0.0
12C oracle home : /apps01/product/12.1.0/dbhome
19C oracle home : /data01/product/19.0.0
Database name : orcl19x1
Direct upgrade paths recommendation :
Other database versions apart from below doesnot support direct upgrade
for example (12.1.0.1) has to be upgraded to 19c ,once it is upgraded to the below intermediate versions
From the above paths,we have 12.1.0.2 version which can be directly upgraded to 19c
Download the 19c software from the link and stage the file in oracle home directory
https://www.oracle.com/downloads/software-license-agreement.html#license-lightbox
PREREQUISITES:
Unzip the 19c file:
[root@orcl19x 19.0.0]# unzip LINUX.X64_193000_db_home
Install the 19C software on the host
Take a tar ball of 12c oracle home
[root@orcl19x 12.1.0]# tar -cvf dbhome12c.tar /apps01/ >/dev/null 2>&1 [root@orcl19x 12.1.0]# [root@orcl19x 12.1.0]# ls dbhome_1 dbhome12c.tar
Notify application team to refrain access the database
Start the database in restricted mode to stop incoming connections to the database
Take consistent cold backup if downtime can be afford or take hot incremental backup of database (the database should be in archivelog mode for hot backup)
[oracle@orcl19x ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Nov 23 18:47:45 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL19X (DBID=4045524769)
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> allocate channel c2 device type disk;
5> backup as backupset database plus archivelog delete input;
6> backup current controlfile;
7> release channel c1;
8> release channel c2;
9> }
Starting backup at 23-NOV-20
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
RECID=6 STAMP=1057258215
Finished backup at 23-NOV-20
piece handle=/data01/FRA/ORCL19X/backupset/2020_11_23/o1_mf_ncnnf_TAG20201123T185354_hvqfzf5d_.bkp tag=TAG20201123T185354 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-NOV-20
List the backups:
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1.27G DISK 00:01:34 23-NOV-20
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20201123T184835
Piece Name: /data01/FRA/ORCL19X/backupset/2020_11_23/o1_mf_nnndf_TAG20201123T184835_hvqfod5s_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1924263 23-NOV-20 /apps01/oradata/orcl19x/system01.dbf
3 Full 1924263 23-NOV-20 /apps01/oradata/orcl19x/sysaux01.dbf
4 Full 1924263 23-NOV-20 /apps01/oradata/orcl19x/undotbs01.dbf
5 Full 1924263 23-NOV-20 /apps01/oradata/orcl19x/example01.dbf
6 Full 1924263 23-NOV-20 /apps01/oradata/orcl19x/users01.dbf
Preview the restore of controlfile and database from rman
RMAN> restore controlfile from '/data01/FRA/ORCL19X/backupset/2020_11_23/o1_mf_ncnnf_TAG20201123T185354_hvqfzf5d_.bkp' preview summary; Starting restore at 23-NOV-20 using channel ORA_DISK_1 Finished restore at 23-NOV-20 ======================================================================= RMAN> restore database preview summary; Starting restore at 23-NOV-20 using channel ORA_DISK_1 List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 2 B F A DISK 23-NOV-20 1 1 NO TAG20201123T184835 List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 4 B A A DISK 23-NOV-20 1 1 NO TAG20201123T185015 RMAN-05119: recovery can not be done to a consistent state. Media recovery start SCN is 1924263 Recovery must be done beyond SCN 1924263 to clear datafile fuzziness Finished restore at 23-NOV-20
Create a new directory for 19c and give permissions to 19c oracle home
[root@orcl19x product]# mkdir 19.0.0 [root@orcl19x product]# pwd /apps01/product [root@orcl19x product]# chmod -R 777 19.0.0/ [root@orcl19x product]# chown -R oracle:oinstall 19.0.0/
Check the space on disk and clear space if the new oracle home location has no sufficient space
[root@orcl19x product]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 20G 15G 3.2G 83% / tmpfs 1.9G 72K 1.9G 1% /dev/shm /dev/sda1 29G 15G 13G 55% /apps01 /dev/sda5 29G 1.5G 26G 6% /data01
Copy the pfile,spfile,passwordfile,listener file,tnsnames.ora and sqlnet.ora files to 19c oracle directory from 12c
[oracle@orcl19x dbs]$ cp * /apps01/product/19.0.0/dbhome/dbs/ [oracle@orcl19x dbs]$ cd ../network/admin/ [oracle@orcl19x admin]$ cp * /apps01/product/19.0.0/dbhome/network/admin/ cp: omitting directory `samples' [oracle@orcl19x admin]$
Check for invalid objects on database.If there are invalid objects then recompile objects with UTLRP or UTL_RECOMP.recomp_parallel(4)
SQL> select count(*) from dba_objects where status = 'INVALID';
COUNT(*)
----------
0
If you find invalid objects,then recompile the objects
Create a temporary directory to store the upgrade scripts
[oracle@orcl19x admin]$ mkdir /tmp/preupgrade [oracle@orcl19x admin]$ echo $ORACLE_SID orcl19x [oracle@orcl19x admin]$ echo $ORACLE_HOME /apps01/product/12.1.0/dbhome_1
Invoke the preupgrade.jar script in 19c OH from 12c java package to generate the pre and post upgrade scripts
[oracle@orcl19x admin]$ /apps01/product/12.1.0/dbhome_1/jdk/bin/java -jar /apps0 1/product/19.0.0/dbhome/rdbms/admin/preupgrade.jar FILE DIR /tmp/preupgrade ================== PREUPGRADE SUMMARY ================== /tmp/preupgrade/preupgrade.log /tmp/preupgrade/preupgrade_fixups.sql /tmp/preupgrade/postupgrade_fixups.sql Execute fixup scripts as indicated below: Before upgrade: Log into the database and execute the preupgrade fixups @/tmp/preupgrade/preupgrade_fixups.sql After the upgrade: Log into the database and execute the postupgrade fixups @/tmp/preupgrade/postupgrade_fixups.sql Preupgrade complete: 2020-11-24T18:39:58
Check the preupgrade.log file and follow the steps
Note: If there are errors in any step,dont skip the errors until you resolve them.Otherwise you might get into trouble in the middle of the upgrade process and waste time
[oracle@orcl19x admin]$ more /tmp/preupgrade/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-11-24T18:39:57
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORCL19X1
Container Name: orcl19x1
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
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] OPTION OFF
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
Keep track of the tablespace size because after upgrade they increase in size
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSAUX 700 MB 808 MB
SYSTEM 790 MB 1136 MB
UNDOTBS1 155 MB 449 MB
It is good practice to have the catalog schema the same or higher version than the RMAN client version you are using.After upgrade ,catalog also should be upgraded
SQL> conn rcat/password Connected. SQL> select * from rcver; VERSION ------------ 12.01.00.02
Run the preupgrade fixups script
SQL> @/tmp/preupgrade/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: 2020-11-24 18:39:52
For Source Database: ORCL19X1
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. apex_manual_upgrade NO Manual fixup recommended.
2. mv_refresh NO Informational only.
Further action is optional.
3. dictionary_stats YES None.
4. mv_refresh NO Informational only.
Further action is optional.
5. pre_fixed_objects YES None.
6. tablespaces_info 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.
UPGRADE APEX:
Check Your Version of APEX
SQL> Col Comp_name Format a22 Col Status Format a12 Select Comp_name, status, Version From Dba_Registry Order by Comp_name;SQL> SQL> 2 3 COMP_NAME STATUS VERSION ---------------------- ------------ ------------------------------ JServer JAVA Virtual M VALID 12.1.0.2.0 achine OLAP Analytic Workspac VALID 12.1.0.2.0 e Oracle Application Exp VALID 4.2.5.00.08 < --------- ress Oracle Database Catalo VALID 12.1.0.2.0 g Views COMP_NAME STATUS VERSION ---------------------- ------------ ------------------------------ Oracle Database Java P VALID 12.1.0.2.0 ackages Oracle Database Packag VALID 12.1.0.2.0 es and Types Oracle Database Vault VALID 12.1.0.2.0 Oracle Label Security VALID 12.1.0.2.0 Oracle Multimedia VALID 12.1.0.2.0 Oracle OLAP API VALID 12.1.0.2.0 COMP_NAME STATUS VERSION ---------------------- ------------ ------------------------------ Oracle Real Applicatio OPTION OFF 12.1.0.2.0 n Clusters Oracle Text VALID 12.1.0.2.0 Oracle Workspace Manag VALID 12.1.0.2.0 er Oracle XDK VALID 12.1.0.2.0 Oracle XML Database VALID 12.1.0.2.0 Spatial VALID 12.1.0.2.0 16 rows selected.
Determine the Installation Type
Check the count for schema APEX with the version on wwv_flows table
If the query returns 0, it is a runtime only installation, and apxrtins.sql should be used for the upgrade.
If the query returns 1, this is a development install and apexins.sql should be used.
APEX component should be manually upgraded,so it can be done before database upgrade to save downtime
Download the latest version from https://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html
[oracle@orcl19x apex]$ cd /tmp/apex/apex/ [oracle@orcl19x apex]$ ls apex_epg_config_cdb.sql apxappcon.sql apxremov1.sql catapx.sql apex_epg_config_core.sql apxchpwd.sql apxremov2.sql core apex_epg_config_nocdb.sql apxconf.sql apxremov_cdb.sql coreins2.sql apex_epg_config.sql apxdevrm_cdb.sql apxremov_nocdb.sql coreins3.sql apexins1.sql apxdevrm_nocdb.sql apxremov.sql coreins4.sql apexins2.sql apxdevrm.sql apxrtins1.sql coreins5.sql apexins3.sql apxdvins_cdb.sql apxrtins2.sql coreins.sql apexins_cdb.sql apxdvins_nocdb.sql apxrtins3.sql dbcsconf.sql apexins_cdb_upg.sql apxdvins.sql apxrtins_cdb.sql dbcsins.sql apexins_nocdb.sql apxdwngrd.sql apxrtins_cdb_upg.sql devins.sql apexins.sql apxexit.sql apxrtins_nocdb.sql images apex_rest_config_cdb.sql apxldimg_core.sql apxrtins.sql install2020-12-19_22-37-28.log apex_rest_config_core.sql apxldimg.sql apxsdoins.sql load_trans.sql apex_rest_config_nocdb.sql apxpatch_cdb.sql apxsilentins.sql utilities apex_rest_config.sql apxpatch_nocdb.sql apxupgrd.sql appins.sql apxpatch.sql builder
Run apexins script to perform development install
# Actions in Phase 1:
#
ok 1 - BEGIN | 0.00
ok 2 - Creating FLOWS_FILES | 0.00
ok 3 - Creating APEX_PUBLIC_USER | 0.00
ok 4 - Creating APEX_GRANTS_FOR_NEW_USERS_ROLE | 0.03
ok 5 - Creating SYS Objects | 0.00
ok 6 - Creating APEX Tables | 0.47
ok 7 - Installing Package Specs (Runtime) | 1.03
ok 8 - Installing Package Bodies (Runtime) | 0.62
ok 9 - Dev Grants | 0.02
ok 10 - Creating Dev-Only Tables and Triggers | 0.00
ok 11 - Installing Package Specs (Dev) | 0.47
ok 12 - Installing Views (Dev) | 0.05
ok 13 - Installing Package Bodies (Dev) | 0.18
ok 14 - Recompiling APEX_190200 | 1.78
ok 15 - Creating APEX$ objects in APEX_190200 | 0.03
ok 16 - Creating Spatial Support | 0.00
ok 17 - Creating Instance Parameter Defaults | 0.00
ok 18 - Installing Page Designer Metadata | 0.08
ok 19 - Inherit Privileges | 0.00
ok 20 - Creating APEX_INSTANCE_ADMIN_USER | 0.00
ok 1 - 20 actions passed, 0 actions failed | 4.77
Thank you for installing Oracle Application Express 19.2.0.00.18
Oracle Application Express is installed in the APEX_190200 schema.
The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex/apex_admin (Oracle REST Data Services)
The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex (Oracle REST Data Services)
timing for: Phase 3 (Switch)
Elapsed: 00:00:14.65
timing for: Complete Installation
Elapsed: 00:13:58.73
PL/SQL procedure successfully completed.
load the images files into the apex home directory where the installation files were unzipped
SQL> @apex_epg_config.sql /tmp/apex/ PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. . Loading images directory: /tmp/apex//apex/images Directory created. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Commit complete. Directory dropped. timing for: Load Images Elapsed: 00:03:30.85 PL/SQL procedure successfully completed. Commit complete.
Check for the materialized views in the database currently
Refresh the materialized views which are there on the database
Please make sure that all the MVs are refreshed and sys.sumdelta$ becomes
empty before doing upgrade, unless you have strong business reasons not
to do so. You can use dbms_mview.refresh() to refresh the MVs except
those stale ones to be kept due to business need. If there are any stale
MVs depending on changes in sys.sumdelta$, do not truncate it, because
doing so will cause wrong results after refresh.
Before upgrade,the sumdelta sys view should be empty
Gather stale data dictionary statistics prior to database upgrade in off-peak time.This will boost SQL execution performance
Dictionary statistics help the Oracle optimizer find efficient SQL execution plans and are essential for proper upgrade timing. Oracle recommends gathering dictionary statistics in the last 24 hours before database upgrade.
Check for triggers which doesnot have ‘ADMINISTER DATABASE PRIVILEGE‘.
If not grant the right privileges.In my case,all triggers has correct privileges in place
Find the tablespaces for which autoextend is enabled
Tablespace grow after upgrade.Keep monitor them and check the estimates
Purge the recyclebin table
After the fixup,verify the actions column again if we need to take any action
We can ignore optional messages
Check the FRA and archive log destinations on the server for available space
Database should be in archivelog mode
Stop the listener
Enable guaranteed flashback on database for safer side in case of failure,we can rollback the database to previous state
Create a restore point with GF
Verify the restore point
Compatible parameter should be same as database version
START UPGRADE ACTIVITY
Verify if pfile,spfile,password file and network files are there in place in 19c dbs location
export the env variables like oracle sid and home
Connect to 19c home sqlplus and start the instance in upgrade mode
Verify the version and status of instance.Note that from this step onwards, i have renamed the database name from orcl19x to orcl19x1
Call the catctl and catupgrd scripts from 19c home and based on the CPU cores ,give the degree of parallelism for faster upgrade process or use dbupgrade script
Monitor the CPU consumption for peak utilization on the host using TOP during the upgrade process
[oracle@orcl19x upgrdlog]$ /data01/product/19.0.0/perl/bin/perl /data01/product/19.0.0/rdbms/admin/catctl.pl -l /tmp/upgrdlog -n 2 /data01/product/19.0.0/rdbms/admin/catupgrd.sql
Argument list for [/data01/product/19.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 = /tmp/upgrdlog
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]
/data01/product/19.0.0/rdbms/admin/orahome = [/data01/product/19.0.0]
/data01/product/19.0.0/bin/orabasehome = [/data01/product/19.0.0]
catctlGetOraBaseLogDir = [/data01/product/19.0.0]
Analyzing file /data01/product/19.0.0/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/upgrdlog]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/upgrdlog/catupgrd_catcon_11652.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/upgrdlog/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/upgrdlog/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = orcl19x1
DataBase Version = 19.0.0.0.0
Parallel SQL Process Count = 2
Components in [orcl19x1]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [EM MGW ODM RAC WK]
------------------------------------------------------
Phases [0-107] Start Time:[2020_12_19 17:20:00]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [orcl19x1] Files:1 Time: 221s
*************** Catalog Core SQL ***************
Serial Phase #:1 [orcl19x1] Files:5 Time: 110s
Restart Phase #:2 [orcl19x1] Files:1 Time: 2s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [orcl19x1] Files:19 Time: 34s
Restart Phase #:4 [orcl19x1] Files:1 Time: 1s
************* Catalog Final Scripts ************
Serial Phase #:5 [orcl19x1] Files:7 Time: 27s
***************** Catproc Start ****************
Serial Phase #:6 [orcl19x1] Files:1 Time: 34s
***************** Catproc Types ****************
Serial Phase #:7 [orcl19x1] Files:2 Time: 30s
Restart Phase #:8 [orcl19x1] Files:1 Time: 4s
**************** Catproc Tables ****************
Parallel Phase #:9 [orcl19x1] Files:67 Time: 278s
Restart Phase #:10 [orcl19x1] Files:1 Time: 4s
************* Catproc Package Specs ************
Serial Phase #:11 [orcl19x1] Files:1 Time: 133s
Restart Phase #:12 [orcl19x1] Files:1 Time: 5s
************** Catproc Procedures **************
Parallel Phase #:13 [orcl19x1] Files:94 Time: 26s
Restart Phase #:14 [orcl19x1] Files:1 Time: 1s
Parallel Phase #:15 [orcl19x1] Files:120 Time: 63s
Restart Phase #:16 [orcl19x1] Files:1 Time: 3s
Serial Phase #:17 [orcl19x1] Files:22 Time: 18s
Restart Phase #:18 [orcl19x1] Files:1 Time: 8s
***************** Catproc Views ****************
Parallel Phase #:19 [orcl19x1] Files:32 Time: 60s
Restart Phase #:20 [orcl19x1] Files:1 Time: 2s
Serial Phase #:21 [orcl19x1] Files:3 Time: 20s
Restart Phase #:22 [orcl19x1] Files:1 Time: 2s
Parallel Phase #:23 [orcl19x1] Files:25 Time: 219s
Restart Phase #:24 [orcl19x1] Files:1 Time: 2s
Parallel Phase #:25 [orcl19x1] Files:12 Time: 149s
Restart Phase #:26 [orcl19x1] Files:1 Time: 2s
Serial Phase #:27 [orcl19x1] Files:1 Time: 0s
Serial Phase #:28 [orcl19x1] Files:3 Time: 11s
Serial Phase #:29 [orcl19x1] Files:1 Time: 0s
Restart Phase #:30 [orcl19x1] Files:1 Time: 13s
*************** Catproc CDB Views **************
Serial Phase #:31 [orcl19x1] Files:1 Time: 9s
Restart Phase #:32 [orcl19x1] Files:1 Time: 5s
Serial Phase #:34 [orcl19x1] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [orcl19x1] Files:293 Time: 50s
Serial Phase #:36 [orcl19x1] Files:1 Time: 0s
Restart Phase #:37 [orcl19x1] Files:1 Time: 8s
Serial Phase #:38 [orcl19x1] Files:6 Time: 12s
Restart Phase #:39 [orcl19x1] Files:1 Time: 4s
*************** Catproc DataPump ***************
Serial Phase #:40 [orcl19x1] Files:3 Time: 122s
Restart Phase #:41 [orcl19x1] Files:1 Time: 2s
****************** Catproc SQL *****************
Parallel Phase #:42 [orcl19x1] Files:13 Time: 296s
Restart Phase #:43 [orcl19x1] Files:1 Time: 2s
Parallel Phase #:44 [orcl19x1] Files:11 Time: 32s
Restart Phase #:45 [orcl19x1] Files:1 Time: 13s
Parallel Phase #:46 [orcl19x1] Files:3 Time: 12s
Restart Phase #:47 [orcl19x1] Files:1 Time: 2s
************* Final Catproc scripts ************
Serial Phase #:48 [orcl19x1] Files:1 Time: 25s
Restart Phase #:49 [orcl19x1] Files:1 Time: 6s
************** Final RDBMS scripts *************
Serial Phase #:50 [orcl19x1] Files:1 Time: 26s
************ Upgrade Component Start ***********
Serial Phase #:51 [orcl19x1] Files:1 Time: 4s
Restart Phase #:52 [orcl19x1] Files:1 Time: 9s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [orcl19x1] Files:2 Time: 14s
***************** Upgrading XDB ****************
Restart Phase #:54 [orcl19x1] Files:1 Time: 4s
Serial Phase #:56 [orcl19x1] Files:3 Time: 9s
Serial Phase #:57 [orcl19x1] Files:3 Time: 6s
Parallel Phase #:58 [orcl19x1] Files:10 Time: 24s
Parallel Phase #:59 [orcl19x1] Files:25 Time: 7s
Serial Phase #:60 [orcl19x1] Files:4 Time: 18s
Serial Phase #:61 [orcl19x1] Files:1 Time: 0s
Serial Phase #:62 [orcl19x1] Files:32 Time: 12s
Serial Phase #:63 [orcl19x1] Files:1 Time: 0s
Parallel Phase #:64 [orcl19x1] Files:6 Time: 18s
Serial Phase #:65 [orcl19x1] Files:2 Time: 24s
Serial Phase #:66 [orcl19x1] Files:3 Time: 26s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [orcl19x1] Files:1 Time: 5s
Serial Phase #:69 [orcl19x1] Files:1 Time: 34s
Parallel Phase #:70 [orcl19x1] Files:2 Time: 28s
Restart Phase #:71 [orcl19x1] Files:1 Time: 2s
Parallel Phase #:72 [orcl19x1] Files:2 Time: 26s
Serial Phase #:73 [orcl19x1] Files:2 Time: 5s
***************** Upgrading SDO ****************
Restart Phase #:74 [orcl19x1] Files:1 Time: 4s
Serial Phase #:76 [orcl19x1] Files:1 Time: 178s
Serial Phase #:77 [orcl19x1] Files:2 Time: 23s
Restart Phase #:78 [orcl19x1] Files:1 Time: 4s
Serial Phase #:79 [orcl19x1] Files:1 Time: 103s
Restart Phase #:80 [orcl19x1] Files:1 Time: 9s
Parallel Phase #:81 [orcl19x1] Files:3 Time: 152s
Restart Phase #:82 [orcl19x1] Files:1 Time: 4s
Serial Phase #:83 [orcl19x1] Files:1 Time: 25s
Restart Phase #:84 [orcl19x1] Files:1 Time: 3s
Serial Phase #:85 [orcl19x1] Files:1 Time: 18s
Restart Phase #:86 [orcl19x1] Files:1 Time: 2s
Parallel Phase #:87 [orcl19x1] Files:4 Time: 255s
Restart Phase #:88 [orcl19x1] Files:1 Time: 2s
Serial Phase #:89 [orcl19x1] Files:1 Time: 8s
Restart Phase #:90 [orcl19x1] Files:1 Time: 2s
Serial Phase #:91 [orcl19x1] Files:2 Time: 38s
Restart Phase #:92 [orcl19x1] Files:1 Time: 6s
Serial Phase #:93 [orcl19x1] Files:1 Time: 7s
Restart Phase #:94 [orcl19x1] Files:1 Time: 3s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [orcl19x1] Files:1 Time: 26s
Restart Phase #:96 [orcl19x1] Files:1 Time: 2s
*********** Final Component scripts ***********
Serial Phase #:97 [orcl19x1] Files:1 Time: 12s
************* Final Upgrade scripts ************
Serial Phase #:98 [orcl19x1] Files:1 Time: 371s
******************* Migration ******************
Serial Phase #:99 [orcl19x1] Files:1 Time: 133s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [orcl19x1] Files:1 Time: 5s
Serial Phase #:101 [orcl19x1] Files:1 Time: 0s
Serial Phase #:102 [orcl19x1] Files:1 Time: 121s
***************** Post Upgrade *****************
Serial Phase #:103 [orcl19x1] Files:1 Time: 63s
**************** Summary report ****************
Serial Phase #:104 [orcl19x1] Files:1 Time: 7s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [orcl19x1] Files:1 Time: 7s
Serial Phase #:106 [orcl19x1] Files:1 Time: 0s
Serial Phase #:107 [orcl19x1] Files:1 Time: 46s
------------------------------------------------------
Phases [90-107] End Time:[2020_12_19 18:34:05]
------------------------------------------------------
Grand Total Time: 4455s
LOG FILES: (/tmp/upgrdlog/catupgrd*.log)
Upgrade Summary Report Located in:
/tmp/upgrdlog/upg_summary.log
Grand Total Upgrade Time: [0d:1h:14m:5s]
[4]+ Killed /data01/product/19.0.0/perl/bin/perl /data01/product/19.0.0/rdbms/admin/catctl.pl -l /tmp/upgrdlog -n 2 /data01/product/19.0.0/rdbms/admin/catupgrd.sql
Check the upgrade summary logs for validation
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. Grand Total Upgrade Time: [0d:1h:14m:5s]
Export the env and start the instance from 19c
[oracle@orcl19x upgrdlog]$ export ORACLE_HOME=/data01/product/19.0.0 [oracle@orcl19x upgrdlog]$ /data01/product/19.0.0/bin/sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 19 19:19:48 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup; ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1572861600 bytes Fixed Size 8910496 bytes Variable Size 1052770304 bytes Database Buffers 503316480 bytes Redo Buffers 7864320 bytes Database mounted. Database opened.
Check the instance version
SQL> select name,open_mode,status,version from v$database,v$instance; NAME OPEN_MODE STATUS VERSION --------- -------------------- ------------ ----------------- ORCL19X1 READ WRITE OPEN 19.0.0.0.0
Check the dba registry for upgraded components
Compile all the invalid objects
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2020-12-19 19:34:13
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2020-12-19 19:46:44
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.
Verify the objects
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
POSTUPGRADE TASKS
run the postupgrade scripts which were generated in the beginning phase
SQL> @/tmp/preupgrade/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: 2020-12-18 21:50:23
For Source Database: ORCL19X1
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
------ ------------------------ ---------- --------------------------------
9. depend_usr_tables YES None.
10. old_time_zones_exist NO Manual fixup recommended.
11. dir_symlinks YES None.
12. post_dictionary YES None.
13. 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.
Its always required to fix and upgrade the timezone post upgrade
step 1:
SQL> @/data01/product/19.0.0/rdbms/admin/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 07/07/2014 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 3 07/07/2014 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 3 07/07/2014 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 3 07/07/2014 - SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3 07/07/2014 - SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3 07/07/2014 - SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3 07/07/2014 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1 07/07/2014 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1 07/07/2014 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1 19/12/2020 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1 19/12/2020 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1 19/12/2020 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1 19/12/2020 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1 19/12/2020 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1 19/12/2020 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1 07/07/2014 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3 07/07/2014 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3 07/07/2014 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3 07/07/2014 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1 07/07/2014 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1 07/07/2014 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1 05/12/2020 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1 05/12/2020 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1 05/12/2020 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1 19/12/2020 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1 19/12/2020 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1 19/12/2020 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1 19/12/2020 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4 19/12/2020 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4 19/12/2020 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4 19/12/2020 - SYS.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ - 1 18/12/2020 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1 18/12/2020 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1 18/12/2020 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1 18/12/2020 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1 18/12/2020 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1 19/12/2020 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7 19/12/2020 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7 18/12/2020 - SYS.KET$_CLIENT_TASKS.CURR_WIN_START - 3 18/12/2020 - SYS.KET$_CLIENT_TASKS.LG_DATE - 3 18/12/2020 - SYS.KET$_CLIENT_TASKS.LT_DATE - 3 19/12/2020 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45 19/12/2020 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45 19/12/2020 - SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 250 19/12/2020 - SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72 05/12/2020 - SYS.RADM_FPTM$.TSWTZ_COL - 1 19/12/2020 - SYS.REG$.NTFN_GROUPING_START_TIME - 2 19/12/2020 - SYS.REG$.REG_TIME - 2 19/12/2020 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 1116 19/12/2020 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11 19/12/2020 - SYS.SCHEDULER$_JOB.END_DATE - 25 19/12/2020 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 25 19/12/2020 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 25 19/12/2020 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 25 19/12/2020 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 25 19/12/2020 - SYS.SCHEDULER$_JOB.START_DATE - 25 18/12/2020 - SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 919 18/12/2020 - SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 919 18/12/2020 - SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 919 05/12/2020 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 4 05/12/2020 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4 19/12/2020 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9 19/12/2020 - SYS.SCHEDULER$_WINDOW.END_DATE - 9 19/12/2020 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9 19/12/2020 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9 19/12/2020 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9 19/12/2020 - SYS.SCHEDULER$_WINDOW.START_DATE - 9 13/12/2020 - SYS.SCHEDULER$_WINDOW_DETAILS.LOG_DATE - 3 13/12/2020 - SYS.SCHEDULER$_WINDOW_DETAILS.REQ_START_DATE - 3 13/12/2020 - SYS.SCHEDULER$_WINDOW_DETAILS.START_DATE - 3 18/12/2020 - SYS.STATS_TARGET$.END_TIME - 703 18/12/2020 - SYS.STATS_TARGET$.START_TIME - 703 05/12/2020 - SYS.TAB_STATS$.SPARE6 - 1028 19/12/2020 - SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 28 19/12/2020 - SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 28 19/12/2020 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - 197447 19/12/2020 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - 197447 19/12/2020 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 68176 19/12/2020 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 68176 19/12/2020 - SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 4274 19/12/2020 - SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 4274 19/12/2020 - SYS.WRI$_OPTSTAT_OPR.END_TIME - 1094 19/12/2020 - SYS.WRI$_OPTSTAT_OPR.SPARE6 - 1094 19/12/2020 - SYS.WRI$_OPTSTAT_OPR.START_TIME - 1094 19/12/2020 - SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 9206 19/12/2020 - SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 9206 19/12/2020 - SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 9206 19/12/2020 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 4919 19/12/2020 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 4919 19/12/2020 - SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ - 11 19/12/2020 - SYS.WRM$_PDB_INSTANCE.OPEN_TIME_TZ - 1 19/12/2020 - SYS.WRM$_PDB_INSTANCE.STARTUP_TIME_TZ - 1 19/12/2020 - SYS.WRM$_PDB_IN_SNAP.OPEN_TIME_TZ - 1 19/12/2020 - SYS.WRM$_SNAPSHOT.BEGIN_INTERVAL_TIME_TZ - 30 19/12/2020 - SYS.WRM$_SNAPSHOT.END_INTERVAL_TIME_TZ - 30 07/07/2014 - SYS.XS$PRIN.END_DATE - 14 07/07/2014 - SYS.XS$PRIN.START_DATE - 14 Total numrows of SYS TSTZ columns is : 587732 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 07/07/2014 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1 07/07/2014 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1 07/07/2014 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME - 1 05/12/2020 - IX.AQ$_ORDERS_QUEUETABLE_S.CREATION_TIME - 4 05/12/2020 - IX.AQ$_ORDERS_QUEUETABLE_S.DELETION_TIME - 4 05/12/2020 - IX.AQ$_ORDERS_QUEUETABLE_S.MODIFICATION_TIME - 4 05/12/2020 - IX.AQ$_STREAMS_QUEUE_TABLE_S.CREATION_TIME - 1 05/12/2020 - IX.AQ$_STREAMS_QUEUE_TABLE_S.DELETION_TIME - 1 05/12/2020 - IX.AQ$_STREAMS_QUEUE_TABLE_S.MODIFICATION_TIME - 1 07/07/2014 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1 07/07/2014 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1 07/07/2014 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1 07/07/2014 - WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1 07/07/2014 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1 Total numrows of non-SYS TSTZ columns is : 23 There are in total 58 non-SYS TSTZ columns. Total Minutes elapsed : 0 Session altered.
step 2:
SQL> @/data01/product/19.0.0/rdbms/admin/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.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ - 1 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.KET$_CLIENT_TASKS.CURR_WIN_START - 3 SYS.KET$_CLIENT_TASKS.LG_DATE - 3 SYS.KET$_CLIENT_TASKS.LT_DATE - 3 SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45 SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45 SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 631 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 - 1117 SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11 SYS.SCHEDULER$_JOB.END_DATE - 25 SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 25 SYS.SCHEDULER$_JOB.LAST_END_DATE - 25 SYS.SCHEDULER$_JOB.LAST_START_DATE - 25 SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 25 SYS.SCHEDULER$_JOB.START_DATE - 25 SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 954 SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 954 SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 954 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.SCHEDULER$_WINDOW_DETAILS.LOG_DATE - 3 SYS.SCHEDULER$_WINDOW_DETAILS.REQ_START_DATE - 3 SYS.SCHEDULER$_WINDOW_DETAILS.START_DATE - 3 SYS.STATS_TARGET$.END_TIME - 703 SYS.STATS_TARGET$.START_TIME - 703 SYS.TAB_STATS$.SPARE6 - 1025 SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 28 SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 28 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - 200931 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - 200931 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 68384 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 68384 SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 4285 SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 4285 SYS.WRI$_OPTSTAT_OPR.END_TIME - 1094 SYS.WRI$_OPTSTAT_OPR.SPARE6 - 1094 SYS.WRI$_OPTSTAT_OPR.START_TIME - 1094 SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 9221 SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 9221 SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 9221 SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 4923 SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 4923 SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ - 11 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 - 30 SYS.WRM$_SNAPSHOT.END_INTERVAL_TIME_TZ - 30 SYS.XS$PRIN.END_DATE - 15 SYS.XS$PRIN.START_DATE - 15 Total count * of SYS TSTZ columns is : 595680 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 IX.AQ$_ORDERS_QUEUETABLE_S.CREATION_TIME - 4 IX.AQ$_ORDERS_QUEUETABLE_S.DELETION_TIME - 4 IX.AQ$_ORDERS_QUEUETABLE_S.MODIFICATION_TIME - 4 IX.AQ$_STREAMS_QUEUE_TABLE_S.CREATION_TIME - 1 IX.AQ$_STREAMS_QUEUE_TABLE_S.DELETION_TIME - 1 IX.AQ$_STREAMS_QUEUE_TABLE_S.MODIFICATION_TIME - 1 WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1 WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1 WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1 WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1 WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1 Total count * of non-SYS TSTZ columns is : 23 There are in total 58 non-SYS TSTZ columns. Total Minutes elapsed : 0 Session altered.
step 3:
SQL> @/data01/product/19.0.0/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 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> @/data01/product/19.0.0/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. ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1572861600 bytes Fixed Size 8910496 bytes Variable Size 1136656384 bytes Database Buffers 419430400 bytes Redo Buffers 7864320 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. ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1572861600 bytes Fixed Size 8910496 bytes Variable Size 1136656384 bytes Database Buffers 419430400 bytes Redo Buffers 7864320 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_S" Number of failures: 0 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L" Number of failures: 0 Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE" Number of failures: 0 Table list: "DVSYS"."SIMULATION_LOG$" Number of failures: 0 Table list: "DVSYS"."AUDIT_TRAIL$" Number of failures: 0 Table list: "APEX_040200"."WWV_FLOW_FEEDBACK" Number of failures: 0 Table list: "APEX_040200"."WWV_FLOW_FEEDBACK_FOLLOWUP" Number of failures: 0 Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES" Number of failures: 0 Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES2" Number of failures: 0 Table list: "APEX_040200"."WWV_FLOW_WORKSHEET_NOTIFY" Number of failures: 0 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S" Number of failures: 0 Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S" Number of failures: 0 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L" Number of failures: 0 Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L" Number of failures: 0 Table list: "SQLTXPLAIN"."SQLT$_SQL_STATEMENT" Number of failures: 0 Table list: "SQLTXPLAIN"."SQLT$_STGTAB_DIRECTIVE" Number of failures: 0 Table list: "SQLTXPLAIN"."SQLT$_DBA_COL_STATS_VERSIONS" Number of failures: 0 Table list: "SQLTXPLAIN"."SQLT$_STATTAB" Number of failures: 0 Table list: "SQLTXPLAIN"."SQLI$_STATTAB_TEMP" Number of failures: 0 Table list: "SQLTXPLAIN"."SQLT$_DBA_TAB_STATS_VERSIONS" Number of failures: 0 Table list: "SQLTXPLAIN"."SQLT$_DBA_IND_STATS_VERSIONS" Number of failures: 0 Table list: "SQLTXPLAIN"."SQLT$_DBA_HISTGRM_STATS_VERSN" Number of failures: 0 Table list: "SQLTXPLAIN"."SQLT$_DBA_OPTSTAT_OPERATIONS" Number of failures: 0 Table list: "SQLTXPLAIN"."SQLT$_DBA_SCHEDULER_JOBS" Number of failures: 0 Table list: "SQLTXPLAIN"."SQLT$_DBA_AUTOTASK_CLIENT_HST" Number of failures: 0 Table list: "SQLTXPLAIN"."SQLT$_OPTSTAT_USER_PREFS$" Number of failures: 0 Table list: "SQLTXPLAIN"."SQLT$_WRI$_OPTSTAT_AUX_HISTORY" 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.
Timezone has been upgraded
Check the post upgrade status of dba registry
SQL> @/data01/product/19.0.0/rdbms/admin/utlusts Enter value for 1: TEXT Oracle Database Release 19 Post-Upgrade Status Tool 12-19-2020 20:37:4 Database Name: ORCL19X1 Component Current Full Elapsed Time Name Status Version HH:MM:SS Oracle Server VALID 19.3.0.0.0 00:35:06 JServer JAVA Virtual Machine VALID 19.3.0.0.0 00:00:00 Oracle XDK VALID 19.3.0.0.0 00:00:00 Oracle Database Java Packages VALID 19.3.0.0.0 00:00:00 OLAP Analytic Workspace VALID 19.3.0.0.0 00:00:00 Oracle Label Security VALID 19.3.0.0.0 00:00:00 Oracle Database Vault VALID 19.3.0.0.0 00:00:00 Oracle Text VALID 19.3.0.0.0 00:00:00 Oracle Workspace Manager VALID 19.3.0.0.0 00:00:00 Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:00 Oracle XML Database VALID 19.3.0.0.0 00:00:00 Oracle Multimedia VALID 19.3.0.0.0 00:00:00 Spatial VALID 19.3.0.0.0 00:20:37 Oracle OLAP API VALID 19.3.0.0.0 00:00:22 Datapatch 00:05:51 Final Actions 00:08:26 Post Upgrade 00:00:41 Post Compile 00:12:30 Total Upgrade Time: 01:23:03 Database time zone version is 32. It meets current release needs.
Run the catuppst.sql script to perform remaining upgrade actions to release the instance from upgrade state and drop the temporary view and tables created by upgrade process
SQL> @/data01/product/19.0.0/rdbms/admin/catuppst TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP DBRESTART 2020-12-19 20:39:42 DBUA_TIMESTAMP DBRESTART FINISHED 2020-12-19 20:39:42 DBUA_TIMESTAMP DBRESTART NONE 2020-12-19 20:39:42 TIMESTAMP -------------------------------------------------------------------------------- DBUA_TIMESTAMP CATUPPST STARTED 2020-12-19 20:39:42 TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP POSTUP_BGN 2020-12-19 20:39:42 DBUA_TIMESTAMP POSTUP_BGN FINISHED 2020-12-19 20:39:42 DBUA_TIMESTAMP POSTUP_BGN NONE 2020-12-19 20:39:42 TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP CATREQ_BGN 2020-12-19 20:39:42 DBUA_TIMESTAMP CATREQ_BGN FINISHED 2020-12-19 20:39:42 DBUA_TIMESTAMP CATREQ_BGN NONE 2020-12-19 20:39:42 catrequtlmg: b_StatEvt = TRUE catrequtlmg: b_SelProps = FALSE catrequtlmg: b_UpgradeMode = FALSE catrequtlmg: b_InUtlMig = FALSE TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP CATREQ_END 2020-12-19 20:39:42 DBUA_TIMESTAMP CATREQ_END FINISHED 2020-12-19 20:39:42 DBUA_TIMESTAMP CATREQ_END NONE 2020-12-19 20:39:42 catuppst: Dropping library DBMS_DDL_INTERNAL_LIB catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG catuppst: Dropping view DBA_PART_KEY_COLUMNS_V$_MIG catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V$_MIG 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 TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP POSTUP_END 2020-12-19 20:39:42 DBUA_TIMESTAMP POSTUP_END FINISHED 2020-12-19 20:39:42 DBUA_TIMESTAMP POSTUP_END NONE 2020-12-19 20:39:42 TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP CATUPPST 2020-12-19 20:39:42 DBUA_TIMESTAMP CATUPPST FINISHED 2020-12-19 20:39:42 DBUA_TIMESTAMP CATUPPST NONE 2020-12-19 20:39:42
Run the postupgrade script again to revalidate the fixup recommendations
SQL> @/tmp/preupgrade/postupgrade_fixups.sql
No errors.
No errors.
No errors.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2020-12-18 21:50:23
For Source Database: ORCL19X1
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
------ ------------------------ ---------- --------------------------------
9. depend_usr_tables YES None.
10. old_time_zones_exist YES None.
11. dir_symlinks YES None.
12. post_dictionary YES None.
13. post_fixed_objects NO Informational only.
Further action is optional.
Again check invalid objects
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
Drop the restore point
SQL> drop restore point GUA; Restore point dropped. SQL> select * from v$restore_point; no rows selected
Set compatible parameter to 19.0.0(need a bounce) and turn off the flashback if not required
SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 12.1.0.2.0 noncdb_compatible boolean FALSE SQL> alter system set compatible='19.0.0' scope=spfile; System altered. SQL> shu immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1572861600 bytes Fixed Size 8910496 bytes Variable Size 1136656384 bytes Database Buffers 419430400 bytes Redo Buffers 7864320 bytes Database mounted. ORA-38760: This database instance failed to turn on flashback database SQL> alter database flashback off; Database altered. SQL> alter database open; Database altered. SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string > 19.0.0 < noncdb_compatible boolean FALSE
Check password file status
SQL> show parameter password NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string EXCLUSIVE
Copy network files from 12c to 19c home -> /network/admin directory
[oracle@orcl19x admin]$ cp /apps01/product/12.1.0/dbhome_1/network/admin/* /data01/product/19.0.0/network/admin/ cp: omitting directory ‘/apps01/product/12.1.0/dbhome_1/network/admin/samples’
Modify the oracle home location inside listener.ora file
[oracle@orcl19x admin]$ cat listener.ora
# listener.ora Network Configuration File: /apps01/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl19x1)
(ORACLE_HOME = /data01/product/19.0.0)
(SID_NAME = orcl19x1)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl19x1)(PORT = 1521))
)
ADR_BASE_LISTENER = /data01/base
Relink all the binaries from 19c home
[oracle@orcl19x bin]$ ./relink all writing relink log to: /data01/product/19.0.0/install/relinkActions2020-12-19_09-09-43PM.log
Take incremental level 0 backup of database immediately
RMAN> run
2> {
3> allocate channel ch1 device type disk;
4> allocate channel ch2 device type disk;
5> crosscheck backup;
6> crosscheck archivelog all;
7> delete expired archivelog all;
8> backup as compressed backupset incremental level 0 database plus archivelog delete input;
9> backup current controlfile;
10> release channel ch1;
11> release channel ch2;
12> }
allocated channel: ch1
channel ch1: SID=36 device type=DISK
allocated channel: ch2
channel ch2: SID=21 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/data01/FRA/ORCL19X1/autobackup/2020_12_19/o1_mf_s_1059599126_hxw7fzhg_.bkp RECID=1 STAMP=1059599127
Crosschecked 1 objects
....................................................................................................................................................
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-DEC-20
Starting Control File and SPFILE Autobackup at 19-DEC-20
piece handle=/data01/FRA/ORCL19X1/autobackup/2020_12_19/o1_mf_s_1059602828_hxwc1obp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-DEC-20
released channel: ch1
released channel: ch2
Test the application connectivity and behaviour with 19c oracle for any performance issues but obviously 19c has more performance benefits !
Delete the flashback logs from mountpoint to clear unwanted flashback logfile generated from guarantee flashback after application connections are validated
Just decrease the flashback retention target to delete files automatically
Here i given 1 sec to instantly delete the log files to clear space
SQL> alter system set db_flashback_retention_target=1; System altered. [oracle@orcl19x flashback]$ ls -lrt /data01/FRA/ORCL19X1/flashback/ total 0
Clean up the old version of apex
VERSION ------------------------------ COMP_NAME ------------------------------ 4.2.5.00.08 Oracle Application Express
Run the script to remove the old apex from 12c home or create a new tablespace to hold latest apex version
SQL> @/apps01/product/12.1.0/dbhome_1/apex/apxremov.sql
SQL> drop public synonym apex_spatial; SQL> drop public synonym apex_pkg_app_install_log;
Validate the apex component version
Create an apex user and validate his password
SQL> @apxchpwd.sql ...set_appun.sql ================================================================================ This script can be used to change the password of an Application Express instance administrator. If the user does not yet exist, a user record will be created. ================================================================================ Enter the administrator's username [ADMIN] apex User "apex" does not yet exist and will be created. Enter apex's email [apex] Enter apex's password [] Created instance administrator APEX.
Upgrade rman catalog version
RMAN> connect target / connected to target database: ORCL19X1 (DBID=1992703147) RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> RMAN> upgrade catalog; recovery catalog owner is RCAT enter UPGRADE CATALOG command again to confirm catalog upgrade RMAN> upgrade catalog; recovery catalog upgraded to version 19.03.00.00.00 DBMS_RCVMAN package upgraded to version 19.03.00.00 DBMS_RCVCAT package upgraded to version 19.03.00.00.
Edit the bash profile to new oracle home for permanent environment variables change
[oracle@orcl19x dbs]$ cat /home/oracle/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
ifcfg up
export PATH
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/data01/base <--
export DB_HOME=$ORACLE_BASE/product/19.0.0/ <--
export ORACLE_HOME=$DB_HOME
export ORACLE_SID=orcl19x1
export ORACLE_TERM=xterm
export BASE_PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$BASE_PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
alias db=’. /home/oracle/db.env’
Edit the oratab for 19c home location
[oracle@orcl19x dbs]$ cat /etc/oratab |grep orcl19x #orcl19x1:/apps01/product/12.1.0/dbhome_1:Y orcl19x1:/data01/product/19.0.0:Y
Hope there are lot of new advanced features to explore in 19c
Thanks for the visit!
To switch on flashback on the database if FRA space is not sufficient to accomodate the flashback log files then,
SQL> alter database flashback on; alter database flashback on * ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38708: not enough space for first flashback database log file SQL> !oerr ora 38708 38708, 00000, "not enough space for first flashback database log file" // *Cause: An ALTER DATABASE FLASHBACK ON command failed because there // was not enough space in the Recovery Area for the first // flashback database log file. // *Action: Make more space in the Recovery Area. For example, this // can be done by increasing the value of // DB_RECOVERY_FILE_DEST_SIZE.
Increase FRA!
SQL> alter system set db_recovery_file_dest_size=10000M; System altered. SQL> alter database flashback on; Database altered.
I try to drop a tablespace with dependent objects but get below error
SQL> !oerr ora 02449 02449, 00000, "unique/primary keys in table referenced by foreign keys" // *Cause: An attempt was made to drop a table with unique or // primary keys referenced by foreign keys in another table. // *Action: Before performing the above operations the table, drop the // foreign key constraints in other tables. You can see what // constraints are referencing a table by issuing the following // command: // SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
Solution:
include cascade constraints option to drop the dependent objects!
