Steps to Restore a TDE Database backup file of Source on Destination Server.
step 1) Create a new Master Key or Alter it using below if it already exists.
CREATE MASTER KEY ENCRYPTION BY PASSWORD=’OracleAgent@DBA$123′; — This can be from Source Server/New one.
–ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD =’OracleAgent@DBA$123′
step 2)Restore the Master DB Certificate of Source Server on Destination Server:
USE master;
GO
CREATE CERTIFICATE TDE_Cert_New
fROM FILE = ‘D:\OracleAgent\TDE\TDE_Cert_New.cer’
WITH PRIVATE KEY(
FILE = ‘D:\OracleAgent\TDE\TDE_Cert_New_PrivateKey.pvk’,
DECRYPTION BY PASSWORD = ‘OracleAgent@DBA$123’
)
step 3)Finally Restore the TDE Enabled DB Backup File on Destination.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
• Create Master Key on MASTER DB
• Backup Master Key of Master DB
• Create Certificate on MASTER DB
• Backup Certificate on MASTER DB
• Create Encryption Key on User DB.
• Finally Enable TDE on User DB
At Source Server: Step 1:Create Database Master Key on Master DB.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD=’OracleAgent@DBA$123′;
GO
–Use this if Master key already exists and to add a new Master Key.
–ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD =’OracleAgent@DBA$123′;
Step-2: Backup Master Key of MASTER DB:
USE MASTER
BACKUP MASTER KEY
TO FILE = ‘D:\OracleAgent\TDE\MasterKey.key’
ENCRYPTION BY PASSWORD = ‘OracleAgent@DBA$123’
Step 3:Create a Certificate on Master DB to support TDE
USE master;
GO
CREATE CERTIFICATE TDE_Cert_New
WITH SUBJECT=’Database_Encryption’;
GO
Step-4:Backup Master DB Certificate & Private Key;
This step is not required to encrypt a database using TDE. But to make sure you can recover your encrypted data from a database backup, should your instance database become corrupted, or you want to move an encrypted database to another server, you should backup the certificate.
Run the following code:
USE master;
GO
BACKUP CERTIFICATE TDE_Cert_New
TO FILE = ‘D:\OracleAgent\TDE\TDE_Cert_New.cer’
WITH PRIVATE KEY(
FILE = ‘D:\OracleAgent\TDE\TDE_Cert_New_PrivateKey.pvk’,
ENCRYPTION BY PASSWORD = ‘OracleAgent@DBA$123’
)
Note: Store the PASSWORD in a safe place.
Step 5: Create Database Encryption Key on required User DB.
USE Advworks
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_New;
Step 6: Enable TDE on Database
ALTER DATABASE Advworks SET ENCRYPTION ON;
In below document i mentioned how to restore in destination server.
How to find pending concurrent requests in 11i/R12
Below document describes how to find total pending concurrent requests in the system.
Some times our concurrent requests queue fill up. We find lot of requests in pending status. In that scenarios we need to find the total number of pending requests.
Please use below query to find pending concurrent requests details.
SELECT DECODE(phase_Code,’P’,’Pending’,’R’,’Running’)
phase
, meaning status
, count(*) numreqs
FROM apps.fnd_concurrent_requests, apps.fnd_lookups
WHERE LOOKUP_TYPE = ‘CP_STATUS_CODE’ AND lookup_code = status_code AND phase_code in ( ‘P’,’R’) and status_code!=’D’
and requested_Start_Date < sysdate
group by DECODE(phase_Code,’P’,’Pending’,’R’,’Running’) , meaning
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Server side Load Balance- Make sure remote_listener is set and pointing to correct entry in server side tnsnames.ora ( SCAN NAME – not the local listener)
If the desire is to get instance based LB make sure CLB_GOAL set to LONG and in cases set prefer_least_loaded_<listener_name>=OFF
If desire is to get LB on node load make sure CLB_GOAL is unset or set to NONE. Check prefer_least_loaded_<listener_name>=ON
If desire to get LB on service goodness then set CLB_GOAL=SHORT and also set Service Goal
Java application not receiving FAN events-check ONS, ICC
.NET and OCI application not receiving FAN events-check if aq_ha_notifications set for service, check system queue.
Java application not doing FCF-check if connection pool has FCF set/enabled
Check ONS, MMON, MMNL on server
Check PMON-enable trace, truss, strace, etc.
Oracle bugs
Notes:
REMOTE_LISTENER: A remote listener is a listener residing on one computer that redirects connections to a database instance on another computer. In a dedicated server RAC environment, we must enable the PMON background process to register with a remote listener. You do this by configuring the REMOTE_LISTENER parameter.
SCAN – Single Client Access Name This simply means client will just have the SCAN Name in the tnsnames.ora in the Client side and no need to know the Name of the nodes involved in the cluster to load balance . The SCAN Name is associated with one or more SCAN VIP’s (max of 3 VIP’s) . Client will get the SCAN Name and the request will be routed to the DNS and GNS and will get resolved by a single SCAN VIP which is associated to the SCAN Name . PMON will be frequently sending the load status on each node to the SCAN Listener and hence the SCAN listener will have a count on the load status on each node . Each Instance will be registered with the SCAN Listener and SCAN Listener will be aware of the load on each instance and will load balance the new requests accordingly .
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Restrictions and Usage Notes for the SET DBID Command:
You should only run the SET DBID command in the following specialized circumstances:
You are not connected to a recovery catalog and want to restore the control file or server parameter file.
You are connected to a recovery catalog and want to restore the control file, but the database name is not unique in the recovery catalog.
When you restore the control file, you must use the SET DBID command to identify the target database. The DBID is used to determine the location of control file auto backups.
We can get the DBID from below query
Or
when we run the rman to connect the target database:
If you are using RMAN catalog, and you have a clone of your database with the same DBID, how the RMAN will differentiate between them?. Here you need to change the DBID of the cloned database using DBNEWID utility to avoid confusion.
In this below blog i showed you how to change DATABASE Name with DBNEWID utility.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
In this article we are going to see about how to refresh the standby database using RMAN Incremental Backups.
My scenario is , I have faced the log difference between the primary and standby databases and then I sync the primary and standby databases by taking the incremental backup. Let us see in brief about here.
Overview steps:
Step:1 I have verified the log sequence on both the primary and standby databases then i found that there is a log difference between them.
Step:2 we can resolve this issue by taking the incremental backup on primary database and then restoring it in the standby database.
Step:3First of all stop the redo transfer from primary to standby database by setting the log_archive_dest_2=DEFER.
Step:4 Find out the current_scn from v$database view in standby database.
Step:5 Then on primary, connect to the RMAN and take the incremental backup from the scn (which we noted in the standby database)
Step:6 Create the standby control file in primary database.
Step:7 By using server copy transfer the backup files, standby control file to the standby database.
Step:8In standby database, replace the control file from backup.
Step:9 Stop the MRP(Managed Recovery Process) in standby database and shut down the database.
Step:10Mount the standby database and connect to the RMAN, then catalog the backup pieces.
Step:11 Recover the standby database by using the incremental backup taken from the primary database.
Step:12 Enable log_archive_dest_state_2 parameter on primary and enable the MRP process on standby database.
Step:13 Then verify the log sequence sync between primary and standby databases.
Step:14 Finally we have refreshed/sync the standby database by using the incremental backup.
Step:1 Verify the log sequence on both primary and secondary database and found that there is a log difference between them.
on primary:
on standby:
Step:2 set the log_archive_dest_2=defer in primary database.
Step:3 Look for the current scn from the standby database.
Step:4 Take the incremental backup starting from that scn because after this scn only our primary and standby databases are not in sync.
Step:5 Create the standby controlfile in primary database.
Step:6 Move the incremental backup, standby controlfile from primary database to standby database through scp(server copy) and in standby database replace the controlfile from backup .
Step:7 Stop the MRP process in standby database.
Step:8 Recover the standby database by using the incremental backup taken from the primary database.
Step:9 Set the log_archive_dest_2=enable on primary database and start the MRP process in standby database.
Step:10 Start the MRP process on standby database.
Step:11 Verify the log sequence on both primary database and standby database, now both are in sync.
On primary:
On standby:
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
In this article we are going to see how to upgrade a oracle 12c container database from 12c to 19c using manual upgrade method.
Here we are going to proceed with the same steps as we followed while upgrading a non container database from oracle 12c to 19c.
In my environment I am having one container database and two pluggable database in it. Overview Steps:
Step:1 Create a new directory called backup, Take, Take backup of listener.ora, tnsnames.ora files ,then move all the files to the newly created directory called backup.
Step:2 Create a new directories called upgrade, while upgrade, Run the preupgrade.jar file which is in the location. Move the summary of the preupgrade script to the upgrade directory.
Step:3 Three files named preupgrade.log, preupgrade_fixups.sql, postupgrade_fixups.sql files will be created after running the preupgrade script.
Step:4 We should execute the below scripts,
Before upgrade – @/u01/preupgrade/preupgrade_fixups.sql
After upgrade – @/u01/preupgrade/postupgrade_fixups.sql
Step:5 Then check the preupgrade.log whether it is completed without any errors.
Step:6 Verify the tablespace sizes for upgrade.(enable the AUTO EXTENT ON and set the maximum size for tablespaces)
Step:7 Gather the dictionary statistics query to get the statistical data of fixed objects. we should gather dictionary statistics before and after the upgrade to maintain better database performance. Because dictionary tables are modified and created during the upgrade.
Step:8 Purge the recyclebin
Step:9 Refresh all the materialized views before upgrade.
Step:10 Run preupgrade_fixups.sql (/u01/upgrade/preupgrade_fixups.sql) before the upgrade.
Step:11 You should enable the archive log list, it is mandatory to perform an upgrade.
Step:12 Create the flashback restore point before performing upgrade, it is important suppose if dbupgrade fails you can recover the database from this restore point.
Step:13 Set the db_recovery_file_dest_size=10GB
UPGRADE STEPS:
After we have completed all our pre-upgrade steps,
Step:14 shut down the database, copy the spfile, password files from 12c home to 19c home.
Step:15 startup the database in upgrade mode from 19c home.
Step:16 Run the upgrade, $ORACLE_HOME/bin/dbupgradefile. Here starts the manual upgrade process.
POST UPGRADE STEPS:
Step:17Run catcon.pl to start utlrp.sql to recompile the invalid objects.
Step:18 Then check for any invalid objects in the database after upgrade.
Step:19 Verify the timezone version of the upgraded database.(it should be changed to 32),for that run utlusts.sql script.
Step:20 We have to verify theDBA_REGISTRYview, it displays the information about the components loaded in the database.
Step:21 Now we can drop the restore point that we have created already before the upgrade.
Step:22 Update the compatible parameter value, it will be set in the base database version we have to update the upgraded database version in the compatible parameter value.
Step:23 Finally check the listener.ora and tnsnames.ora files it should be updated to a upgraded database version.
PRE-UPGRADE TASK:
Step: 1 Create directory backup, upgrade for taking backup of listener.ora , tnsnames.ora and running the preupgrade.jar files respectively.
Backup TNS Files listener spfile and passwordfile.
[oracle@localhost ~]$ mkdir -p /u01/backup
[oracle@localhost u01]$ cd backup/
[oracle@orcldbs backup]$ ll
total 20
-rwxr-xr-x. 1 oracle oinstall 343 Mar 09 03:32 listener21013011PM3657.bak
-rwxr-xr-x. 1 oracle oinstall 555 Mar 09 03:32 listener.ora
-rwxr-xr-x. 1 oracle oinstall 191 Mar 09 03:32 sqlnet21013011PM3657.bak
-rwxr-xr-x. 1 oracle oinstall 191 Mar 09 03:32 sqlnet.ora
-rwxr-xr-x. 1 oracle oinstall 614 Mar 09 03:32 tnsnames.ora
[oracle@orcldbs backup]$
Step:2 Login to the database & Run the preupgrade_fixups.sql
Step:3 Gather the dictionary statistics it should be gathered before and after performing the upgrade.
Step:4 Purge the Recyclebin, it gains some storage space and on other hand we have chosen the recovery options.
Step:5 Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.
Step:6 Verify archive log mode and enable it ,if not
Step:7 Create Flashback Guaranteed Restore Point in the name pre_upgrade.
Step:8 Copy init and password files from 12c home to 19c home.
preupgrade_CDB_ROOT.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version 19.0.0.0.0 Build: 1 on 2021-03-09T05:41:20
Upgrade-To version: 19.0.0.0.0
======================================= Status of the database prior to upgrade ======================================= Database Name: EUROPE Container Name: CDB$ROOT Container ID: 1 Version: 12.2.0.1.0 DB Patch Level: No Patch Bundle applied Compatible: 12.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 26 Database log mode: NOARCHIVELOG Readonly: FALSE Edition: EE
Report generated by Oracle Database Pre-Upgrade Information Tool Version 19.0.0.0.0 Build: 1 on 2021-03-09T05:41:43
Upgrade-To version: 19.0.0.0.0
======================================= Status of the database prior to upgrade ======================================= Database Name: EUROPE Container Name: PDB$SEED Container ID: 2 Version: 12.2.0.1.0 DB Patch Level: No Patch Bundle applied Compatible: 12.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 26 Database log mode: NOARCHIVELOG Readonly: TRUE Edition: EE
Report generated by Oracle Database Pre-Upgrade Information Tool Version 19.0.0.0.0 Build: 1 on 2021-03-09T05:41:43
Upgrade-To version: 19.0.0.0.0
======================================= Status of the database prior to upgrade ======================================= Database Name: EUROPE Container Name: ARGENTINA Container ID: 3 Version: 12.2.0.1.0 DB Patch Level: No Patch Bundle applied Compatible: 12.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 26 Database log mode: NOARCHIVELOG Readonly: FALSE Edition: EE
Report generated by Oracle Database Pre-Upgrade Information Tool Version 19.0.0.0.0 Build: 1 on 2021-03-09T05:42:09
Upgrade-To version: 19.0.0.0.0
======================================= Status of the database prior to upgrade ======================================= Database Name: EUROPE Container Name: BRAZIL Container ID: 4 Version: 12.2.0.1.0 DB Patch Level: No Patch Bundle applied Compatible: 12.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 26 Database log mode: NOARCHIVELOG Readonly: FALSE Edition: EE
For detailed information click here to view the preupgrade.log
UPGRADE TASK:
Step:9 Startup the database in Upgrade mode from 19c home, Run dbupgrade, $ORACLE_HOME/bin/dbupgrade file. Here starts the manual upgrade process.
To view the complete database upgrade process, click here.
[oracle@localhost ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@localhost admin]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin
[oracle@localhost admin]$ mkdir -p /u01/whileupgrade
[oracle@localhost admin]$ nohup /u01/app/oracle/product/19.0.0/dbhome_1/perl/bin/perl catctl.pl -l /u01/whileupgrade -n 4 catupgrd.sql &
[1] 24608
[oracle@orcldbs admin]$ nohup: ignoring input and appending output to ‘nohup.out’
Start of Summary Report
------------------------------------------------------
Oracle Database Release 19 Post-Upgrade Status Tool 03-09-2021 06:06:0
Database Name: EUROPE
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.3.0.0.0 00:18:34
JServer JAVA Virtual Machine UPGRADED 19.3.0.0.0 00:01:34
Oracle XDK UPGRADED 19.3.0.0.0 00:00:47
Oracle Database Java Packages UPGRADED 19.3.0.0.0 00:00:13
OLAP Analytic Workspace UPGRADED 19.3.0.0.0 00:00:12
Oracle Label Security UPGRADED 19.3.0.0.0 00:00:06
Oracle Database Vault UPGRADED 19.3.0.0.0 00:00:20
Oracle Text UPGRADED 19.3.0.0.0 00:00:31
Oracle Workspace Manager UPGRADED 19.3.0.0.0 00:00:27
Oracle Real Application Clusters UPGRADED 19.3.0.0.0 00:00:00
Oracle XML Database UPGRADED 19.3.0.0.0 00:01:31
Oracle Multimedia UPGRADED 19.3.0.0.0 00:00:47
Spatial UPGRADED 19.3.0.0.0 00:05:33
Oracle OLAP API UPGRADED 19.3.0.0.0 00:00:10
Datapatch 00:03:27
Final Actions 00:03:35
Post Upgrade 00:00:17
Total Upgrade Time: 01:55:38
Database upgrade completed.
Step:10 Startup DB from 19c home.
POST-UPGRADE TASKS:
Step:-11 Run catcon.pl(it is used used to execute the command in all PDBs and CDB database) to start utlrp.sql to recompile invalid objects.
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2021-03-09 06:30:35
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 2021-03-09 06:34: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
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
Step:12 Upgrade the time zone by running the following scripts,
First Script $ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
Second Script $ORACLE_HOME/rdbms/admin/ utltz_upg_apply.sql
SQL> @?/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: This database is a Multitenant database.
INFO: Current container is CDB$ROOT .
INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database
INFO: will NOT update the RDBMS DST version of PDB databases in this CDB.
INFO: There are no open PDBs .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen …
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
Session altered.
SQL>@?/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 .
INFO: This database is a Multitenant database.
INFO: Current container is CDB$ROOT .
INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database
INFO: will NOT update the RDBMS DST version of PDB databases in this CDB.
INFO: There are no open PDBs .
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 2432695832 bytes
Fixed Size 9137688 bytes
Variable Size 654311424 bytes
Database Buffers 1761607680 bytes
Redo Buffers 7639040 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 2432695832 bytes
Fixed Size 9137688 bytes
Variable Size 654311424 bytes
Database Buffers 1761607680 bytes
Redo Buffers 7639040 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
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.
Step:13 Run utlusts.sql ,this script reads the view dba_registry_log and displays the upgrade results for the database components.
Step:14 Drop Restore point as we have successfully completed the manual upgradation.
SQL> drop restore point PRE_UPGRADE;
Restore point dropped.
Step:15 Set COMPATIBALE parameter value to 19.0.0 from 12.2.0
Step:16 Verify DBA_REGISTRY
Step:17 Add TNS Entries in 19c TNS home
[oracle@localhost admin]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-MAR-2021 07:56:41
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 09-MAR-2021 07:56:41
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Services Summary...
Service "europe" has 1 instance(s).
Instance "europe", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@orcldbs admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-MAR-2021 07:56:50
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 09-MAR-2021 07:56:41
Uptime 0 days 0 hr. 0 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
Services Summary...
Service "europe" has 1 instance(s).
Instance "europe", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Step:18 Edit oratab,Put an entry of current database in the vi /etc/oratab file.
[oracle@localhost admin]$ cat /etc/oratab | grep -i europe
europe:/home/oracle/u03/app/oracle/product/12.2.0/db_1:N
[oracle@localhost admin]$ cat /etc/oratab | grep -i europe
europe:/u01/app/oracle/product/19.0.0/dbhome_1:Y
europe:/home/oracle/u03/app/oracle/product/12.2.0/db_1:N
In this document, We have seen how to upgrade an oracle 12c container database to 19c using Manual upgrade technique.
Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
[dbname] [hongkong]
==========================================
[container] [hongkong]
==========================================
[checkname] POST_DICTIONARY
[stage] POSTCHECKS
[fixup_available] YES
[runfix] YES
[severity] RECOMMEND
[action] Gather dictionary statistics after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
[broken rule] Oracle recommends gathering dictionary statistics after upgrade.
[rule] 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.
----------------------------------------------------
[checkname] POST_FIXED_OBJECTS
[stage] POSTCHECKS
[fixup_available] NO
[runfix] N/A
[severity] RECOMMEND
[action] 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;
[broken rule] Oracle recommends gathering fixed object statistics after upgrade. This recommendation is given for all preupgrade runs.
[rule] 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.2.0.1 Oracle Database SQL Tuning Guide.
----------------------------------------------------
[checkname] OLD_TIME_ZONES_EXIST
[stage] POSTCHECKS
[fixup_available] YES
[runfix] YES
[severity] WARNING
[action] Upgrade the database time zone file using the DBMS_DST package.
[broken rule] The database is using time zone file version 26 and the target 19 release ships with time zone file version 32.
[rule] 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.
----------------------------------------------------
[checkname] RMAN_RECOVERY_VERSION
[stage] PRECHECKS
[fixup_available] NO
[runfix] N/A
[severity] INFO
[action] Check the Oracle Backup and Recovery User's Guide for information on how to manage an RMAN recovery catalog schema.
[broken rule] 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.
[rule] It is good practice to have the catalog schema the same or higher version than the RMAN client version you are using.
----------------------------------------------------
[checkname] TABLESPACES_INFO
[stage] PRECHECKS
[fixup_available] NO
[runfix] N/A
[severity] INFO
[action] To help you keep track of your tablespace allocations, the following AUTOEXTEND tablespaces are expected to successfully EXTEND during the upgrade process.
[broken rule] [Tablespace Name|Allocated|Minimum Required for Upgrade]
|SYSTEM|820 MB|960 MB|
|TEMP|32 MB|150 MB|
|UNDOTBS1|65 MB|400 MB|
|SYSAUX|470 MB|598 MB|
[rule] Minimum tablespace sizes for upgrade are estimates.
----------------------------------------------------
[checkname] INFORM_DROP_GRP
[stage] POSTCHECKS
[fixup_available] NO
[runfix] N/A
[severity] INFO
[action] Drop GRP at your convenience once you consider it is no longer needed. drop restore point AUTOUPGRADE_9212_HONGKONG122010;
[broken rule] None
[rule] The AutoUpgrade tool will not automatically drop a GRP unless you specify in the config file the parameter drop_grp_after_upgrade=YES.
----------------------------------------------------
[checkname] ORDIM_INFO_DESUPPORT
[stage] PRECHECKS
[fixup_available] NO
[runfix] N/A
[severity] INFO
[action] Follow the instructions in the Oracle Multimedia README.txt file in <19 ORACLE_HOME>/ord/im/admin/README.txt, or MOS note 2555923.1 to determine if Oracle Multimedia is being used. If Oracle Multimedia is being used, refer to MOS note 2347372.1 for suggestions on replacing Oracle Multimedia.
[broken rule] Oracle Multimedia component (ORDIM) is installed.
[rule] Starting in release 19c, Oracle Multimedia is desupported. Object types still exist, but methods and procedures will raise an exception. Refer to 19 Oracle Database Upgrade Guide, the Oracle Multimedia README.txt file in <19 ORACLE_HOME>/ord/im/admin/README.txt, or MOS note 2555923.1 for more information.
----------------------------------------------------
[checkname] TIMESTAMP_MISMATCH
[stage] POSTCHECKS
[fixup_available] YES
[runfix] YES
[severity] WARNING
[action] Recompile the objects with timestamp mismatch. Please refer to MOS note 781959.1 for more details.
[broken rule] There are objects whose timestamp are mismatched with its parent objects.
[rule] Timestamp of dependent objects must coincide with the timestamp of parent objects.
----------------------------------------------------
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
