Export(exp) and import(imp) are Logical backup and Recovery. When exporting the database objects are dumped to a binary file which can then be imported into another Oracle database.
The Export and Import utilities provide a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.
When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is written to an export dump file. The Import utility reads the object definitions and table data from the dump file. First, let us see export in detail.
Difference between exp/imp and expdp/impdp
Exp/imp is a Traditional export import,expdp/impdp is a Datapump export
import.
Traditional export import utility is starting with exp/imp,Datapump utility
is starting with expdp/impdp.
Datapump access files in the server (using ORACLE directories). Traditional export can access files in client and server both (not using ORACLE directories).
exp/imp (Traditional) is using conventional path , expdp /impdb (Datapump) is using Direct path.
Exp (Traditional) is byte mode ,Datapump is block mode.
Data Pump will recreate the user, whereas the old imp utility required the DBA to create the user ID before importing.
Datapump utility we can stop and Restart the Jobs.
Features of Datapump utility
Job Estimation can be Done in Datapump.
Data Remapping can be done using REMAPDATA parameter.
EXCLUDE and INCLUDE parameter allows the fine-grained object selection.
Failed export/import Jobs can be Restarted.
Export and import can be taken over the network using database links even without Generating the dump file using NETWORK_LINK parameter.
CONTENT parameter gives the freedom for what to export with options METADATA ONLY, DATA, BOTH.
You don’t need to specify the BUFFER size in datapump
Job estimated completion time can be monitored from v$session_longops view.
Dump file can be compressed with COMPRESSION parameter. In conventional exp/imp you have to compress the dumps using OS utilities.
Data encryption can be done in datapump.
DATAPUMP has interactive options like ADD_FILE, START_JOB, KILL_JOB, STOP_JOB.
REUSE_DUMPFILES parameter asks the confirmation/rewrite the existing dumpfile.
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:
In this blog, we will see how to upgrade Oracle Database 12c to 19c using AutoUpgrade Tool
What is the AutoUpgrade?
The Oracle Database AutoUpgrade utility is a small command-line tool that allows you to upgrade your databases very easily with very little interaction
The new AutoUpgrade utility in Oracle 19¢ performs almost 99% of the task by itself, we just have to provide inputs during the initial phase
So it performs Prechecks against multiple databases, upgrades multiple databases in one go
Also, it does Post upgrade, object recompilation, and time zone up-gradation
The only thing you need to provide is a contig file in text format
Which database releases are supported?
As a source, the minimum version is Oracle Database 11.2.0.4. onwards
Download the latest auto-upgrade jar file
Auto upgrade utility autoupgrade.jar file exists by default under $ORACLE_HOME/rdbms/admin directory of Oracle 19c Home
Oracle strongly recommends downloading the latest AutoUpgrade version before doing the upgrade
You download the most recent version from MOS Note: 2485487.1 AutoUpgrade Tool
Once you download this jar file transfer it to the Server and create a new directory and place this file in that directory
prddb_config.cfg file should have following entry which specifies source and target database home location and DB name and log locations and other information’s:
Auto upgrade Analyze mode checks your database to see if it is ready for the upgrade. This will reads data from the database and does not perform any updates.
Execute AutoUpgrade in analyze mode with the below syntax:
Once the upgrade process is started consider monitoring the logs and database alert logs to see the progress of the upgrade. Auto upgrade logs are available under
/home/oracle/auto_upgrade_19c/upg_logs/
Once the upgrade finishes crosscheck the below.
SELECT VERSION FROM V$TIMEZONE_FILE;
select name, open_mode, version, status from v$database, v$instance;
Post-upgrade task
Once the upgrade is successful and all testing is done, drop the restore point.
Drop the Guaranteed restore point
select name from v$restore_point;
drop restore point restorepoint_name;
Change the compatible parameter
After the upgrade, the database has to be tested properly before updating the compatible parameter. Once the parameter is updated database cannot be downgraded.
show parameter compatible;
alter system set compatible=’19.0.0′ scope=spfile;
shutdown immediate:
startup;
show parameter compatible;
Hope this blog was useful…
Please find out all of our articles send us the Invitation or Follow us:
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
An another way to upgrade a database from lower version to higher version is we have to manually upgrade a database (ie) we have to verify whether it meets all the pre-requests manually instead of using dbua (GUI) / datapump.
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@orcldbs ~]$ mkdir -p /u01/backup
[oracle@orcldbs u01]$ cd backup/ [oracle@orcldbs backup]$ ll total 20 -rwxr-xr-x. 1 oracle oinstall 343 Jan 30 23:32 listener21013011PM3657.bak -rwxr-xr-x. 1 oracle oinstall 555 Jan 30 23:32 listener.ora -rwxr-xr-x. 1 oracle oinstall 191 Jan 30 23:32 sqlnet21013011PM3657.bak -rwxr-xr-x. 1 oracle oinstall 191 Jan 30 23:32 sqlnet.ora -rwxr-xr-x. 1 oracle oinstall 614 Jan 30 23:32 tnsnames.ora [oracle@orcldbs backup]$
[oracle@orcldbs ~]$ /u01/app/oracle/product/12.2.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /u01/upgrade
SQL> startup mount ORACLE instance started. Total System Global Area 2432696320 bytes Fixed Size 8623592 bytes Variable Size 654314008 bytes Database Buffers 1761607680 bytes Redo Buffers 8151040 bytes Database mounted.
SQL> alter database archivelog; Database altered.
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/12.2.0/db_1/dbs/arch Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 1
SQL> show parameter rec
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------
buffer_pool_recycle string control_file_record_keep_time integer 7 db_recovery_file_dest string db_recovery_file_dest_size big integer 0 db_recycle_cache_size big integer 0 db_unrecoverable_scn_tracking boolean TRUE ldap_directory_access string NONE ldap_directory_sysauth string no recovery_parallelism integer 0 recyclebin string on remote_recovery_file_dest string
In the Above snippet db_recovery_file_dest,db_recovery_file_dest_size is not set, here I am setting the db_recovery_file_dest_size to 10g and db_recovery_file_dest=’/u01/app/oracle/fra’
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fra' scope=both;
System altered.
SQL> show parameter rec
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------
buffer_pool_recycle string control_file_record_keep_time integer 7 db_recovery_file_dest string db_recovery_file_dest_size big integer 10 db_recycle_cache_size big integer 0 db_unrecoverable_scn_tracking boolean TRUE ldap_directory_access string NONE ldap_directory_sysauth string no recovery_parallelism integer 0 recyclebin string on remote_recovery_file_dest string
Step:7 Create Flashback Guaranteed Restore Point in the name pre_upgrade.
SQL> select flashback_on from v$database;
FLASHBACK_ON ------------------ NO
SQL> select name,open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE --------- -------------------- ------------ AUSTRALIA READ WRITE ARCHIVELOG
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size big integer 10G
SQL> create restore point pre_upgrade guarantee flashback database;
Restore point created.
Step:8 Copy init and password files from 12c home to 19c home
[oracle@orcldbs ~]$ cd $ORACLE_HOME/dbs [oracle@orcldbs dbs]$ cp spfileaustralia.ora orapwaustralia /u01/app/oracle/product/19.0.0/dbhome_1/dbs oracle@orcldbs dbs]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/dbs [oracle@orcldbs dbs]$ ls hc_oraprod.dat lkORAPROD orapworaprod spfileaustralia.ora init.ora orapwaustralia snapcf_oraprod.f spfileoraprod.ora
Step:9 Startup DB in Upgrade mode from 19c home
[oracle@orcldbs ~]$ !sq sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 01:24:40 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade ORACLE instance started. Total System Global Area 2432695144 bytes Fixed Size 8899432 bytes Variable Size 536870912 bytes Database Buffers 1879048192 bytes Redo Buffers 7876608 bytes Database mounted. Database opened.
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
NAME OPEN_MODE CDB VERSION STATUS --------- -------------------- --- ----------------- ------------ AUSTRALIA READ WRITE NO 19.0.0.0.0 OPEN MIGRATE
Step:10 Run dbupgrade, using $ORACLE_HOME/bin/dbupgrade file (or) by catupgrade(catupgrd.sql). Here starts the manual upgrade process.
Database upgrade completed.
Step:11 Startup DB from 19c home
[oracle@orcldbs whileupgrade]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 02:26:12 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2432695144 bytes Fixed Size 8899432 bytes Variable Size 637534208 bytes Database Buffers 1778384896 bytes Redo Buffers 7876608 bytes Database mounted Database opened
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
NAME OPEN_MODE CDB VERSION STATUS --------- -------------------- --- ----------------- ------------ AUSTRALIA READ WRITE NO 19.0.0.0.0 OPEN
Step:-12. 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> select count(*) from dba_objects where status='INVALID';
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>#
DOC> The following query reports the number of invalid objects. DOC> DOC> If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ------------------- 0
DOC> The following query reports the number of exceptions caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC> Note: Typical compilation errors (due to coding errors) are not DOC> logged into this table: they go into DBA_ERRORS instead. DOC>#
ERRORS DURING RECOMPILATION --------------------------- 0
Function created. PL/SQL procedure successfully completed. Function dropped. PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*) ---------- 0
Step:13 Upgrade the time zone by running the following scripts,
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: 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.
INFO: If an ERROR occurs, the script will EXIT SQL*Plus. INFO: The database RDBMS DST version will be updated to DSTv32 . WARNING: This script will restart the database 2 times WARNING: WITHOUT asking ANY confirmation. WARNING: Hit control-c NOW if this is not intended. INFO: Restarting the database in UPGRADE mode to start the DST upgrade. Database closed. Database dismounted. ORACLE instance shut down. ORACLE instance started. Total System Global Area 2432695144 bytes Fixed Size 8899432 bytes Variable Size 637534208 bytes Database Buffers 1778384896 bytes Redo Buffers 7876608 bytes Database mounted. Database opened.
INFO: Starting the RDBMS DST upgrade. INFO: Upgrading all SYS owned TSTZ data. INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started. INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data. Database closed. Database dismounted. ORACLE instance shut down. ORACLE instance started. Total System Global Area 2432695144 bytes Fixed Size 8899432 bytes Variable Size 637534208 bytes Database Buffers 1778384896 bytes Redo Buffers 7876608 bytes Database mounted. Database opened.
INFO: Upgrading all non-SYS TSTZ data. INFO: It might take time before any further output is seen ... INFO: Do NOT start any application yet that uses TSTZ data! INFO: Next is a list of all upgraded tables: Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L" Number of failures: 0 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S" Number of failures: 0 Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE" Number of failures: 0 Table list: "DVSYS"."AUDIT_TRAIL$" Number of failures: 0 Table list: "DVSYS"."SIMULATION_LOG$" Number of failures: 0 INFO: Total failures during update of TSTZ data: 0 . An upgrade window has been successfully ended. INFO: Your new Server RDBMS DST version is DSTv32 . INFO: The RDBMS DST update is successfully finished. INFO: Make sure to exit this SQL*Plus session. INFO: Do not use it for timezone related selects. Session altered.
SQL> SELECT version FROM v$timezone_file;
VERSION ---------- 32
1 row selected.
Step:14 Run utlusts.sql ,this script reads the view dba_registry_log and displays the upgrade results for the database components.
SQL> @?/rdbms/admin/utlusts.sql text Oracle Database Release 19 Post-Upgrade Status Tool 01-31-2021 02:43:1 Database Name: AUSTRALIA Component Current Full Elapsed Time Name Status Version HH:MM:SS Oracle Server VALID 19.3.0.0.0 00:18:34 JServer JAVA Virtual Machine VALID 19.3.0.0.0 00:01:34 Oracle XDK VALID 19.3.0.0.0 00:00:47 Oracle Database Java Packages VALID 19.3.0.0.0 00:00:13 OLAP Analytic Workspace VALID 19.3.0.0.0 00:00:12 Oracle Label Security VALID 19.3.0.0.0 00:00:06 Oracle Database Vault VALID 19.3.0.0.0 00:00:20 Oracle Text VALID 19.3.0.0.0 00:00:31 Oracle Workspace Manager VALID 19.3.0.0.0 00:00:27 Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:00 Oracle XML Database VALID 19.3.0.0.0 00:01:31 Oracle Multimedia VALID 19.3.0.0.0 00:00:47 Spatial VALID 19.3.0.0.0 00:05:33 Oracle OLAP API VALID 19.3.0.0.0 00:00:10 Datapatch 00:03:27
Final Actions 00:03:35 Post Upgrade 00:00:17 Post Compile 00:03:53
Total Upgrade Time: 00:39:14
Database time zone version is 32. It meets current release needs.
Step:15 Drop Restore point as we have successfully completed the manual upgradation.
SQL> drop restore point PRE_UPGRADE;
Restore point dropped.
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
no rows selected
Step:16 Set COMPATIBALE parameter value to 19.0.0 from 12.2.0
SQL> show parameter COMPATIBLE
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------
SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;
System altered.
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started Total System Global Area 2432695144 bytes Fixed Size 8899432 bytes Variable Size 637534208 bytes Database Buffers 1778384896 bytes Redo Buffers 7876608 bytes Database mounted. Database opened.
SQL> show parameter COMPATIBLE
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 19.0.0 noncdb_compatible boolean FALSE
Step:17 Verify DBA_REGISTRY
SQL> col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 set lines 180 set pages 999 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; SQL>
[oracle@orcldbs admin]$ lsnrctl start LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02:56:33 Copyright (c) 1991, 2019, Oracle. All rights reserved. TNS-01106: Listener using listener name LISTENER has already been starte [oracle@orcldbs admin]$ lsnrctl stop LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02:56:38 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521))) The command completed successfully [oracle@orcldbs admin]$ lsnrctl start LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-JAN-2021 02: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 31-JAN-2021 02:56:41 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF
Service "australia" has 1 instance(s). Instance "australia", 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 31-JAN-2021 02: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 31-JAN-2021 02: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 "australia" has 1 instance(s). Instance "australia", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
Step:19 Edit oratab,Put an entry of current database in the vi /etc/oratab file.
[oracle@orcldbs admin]$ cat /etc/oratab | grep -i australia
australia:/u01/app/oracle/product/12.2.0/db_1:N
[oracle@orcldbs admin]$ cat /etc/oratab | grep -i australia
australia:/u01/app/oracle/product/19.0.0/dbhome_1:Y
australia:/u01/app/oracle/product/12.2.0/db_1:N
The SSH (Secure Shell) protocol is a method for secure remote login from one computer to another. SSH enables secure system administration and file transfers over insecure networks using encryption to secure the connections between endpoints. SSH keys are an important part of securely accessing Oracle Cloud Infrastructure compute instances in the cloud.
Oracle always recommend you use the Oracle Cloud Shell to interface with the OCI compute instance you will create. Oracle Cloud Shell is browser-based, does not require installation or configuration of software on your laptop, and works independently of your network setup.
To use the Cloud Shell machine, your tenancy administrator must grant the required IAM (Identity and Access Management) policy.
To start the Oracle Cloud shell, go to your Cloud console and click the cloud shell icon at the top right of the page.
Once the cloud shell has started, Choose the key name, This will be the keyname you will use to connect to any compute instances you create. Press Enter twice for no passphrase.
mkdir .ssh
cd .ssh
ssh-keygen -b 2048 -t rsa -f <<sshkeyname>>
Please find the below notes which I has done.
osamaobama@cloudshell:~ (us-phoenix-1)$ mkdir .ssh
osamaobama@cloudshell:~ (us-phoenix-1)$ cd .ssh
osamaobama@cloudshell:.ssh (us-phoenix-1)$ ssh-keygen -b 2048 -t rsa -f cloudshellkey
Generating public/private rsa key pair.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in cloudshellkey.
Your public key has been saved in cloudshellkey.pub.
The key fingerprint is:
SHA256:VJLFZbU6v2PHzCJtWKHNEgV8UH7RGpFXny63PvXDatM osamaobama@5e3feabe028c
The key's randomart image is:
+---[RSA 2048]----+
| .+oo==+++|
| .o....=.*|
| . oo=o|
| . ..+. |
| S o* + |
| oo* o|
| =+=o|
| ooBE*|
| .=+=o|
+----[SHA256]-----+
osamaobama@cloudshell:.ssh (us-phoenix-1)$ ls
cloudshellkey cloudshellkey.pub
osamaobama@cloudshell:.ssh (us-phoenix-1)$ cat cloudshellkey.pub
ssh-rsa AAAAB3NzaC1yc**********************************************
osamaobama@cloudshell:.ssh (us-phoenix-1)$
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
Step:1 Check the database size in source. Step:2 check which tablespace holds the schema objects. Step:3 compile invalid objects in source. step:4 check the count of invalid dba_objects in the source. Step:5 create a directory for export purpose both in OS level and database level. Step:6 Estimate the size of dumpfile, so that we can know when will the export get completed. Step:7 export the database(TANSTAL). Step:8 create a fresh database for import. Step:9 Now create a directory in both OS level and database level for import purpose in the newly created database. Step:10 import the database.(ZHIGOMA) Step:11 Post upgrade steps in target database. 11.1 compile invalid objects in target database. 11.2 check whether any invalid objects present. 11.3 Run the query to check for currently installed database components.
Step:12 Verify the timezone of the upgraded database.
Step:13 Check the CONSTRAINTS count in both source & target.it is used to display the constraints that are defined in the database.
Step:1 Check the database size in source .
SQL> col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
Database Size Used space Free space
-------------------- -------------------- --------------------
2 GB 2 GB 0 GB
SQL>
Step:2 execute the following query to check which tablespace holds the schema objects.
set pagesize 130
break on Tablespace on Owner
column Objects format A20
select Tablespace_Name,Owner,COUNT(*)||’ tables’ Objects
from DBA_TABLES
group by Tablespace_Name,Owner
union
select Tablespace_Name, Owner, COUNT(*)||’ indexes’ Objects
from DBA_INDEXES
group by Tablespace_Name, Owner;
Step:3 compile invalid objects in source to reduce dependencies, so that we can find the best way to recompile it.
SQL> @?/rdbms/admin/utlrp.sql
COMP_TIMESTAMP UTLRP_BGN 2021-01-31 20:19:36
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.
COMP_TIMESTAMP UTLRP_END 2021-01-31 20:19:39
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>#
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>#
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
step:4 check the count of invalid dba_objects in the source.
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
----------
0
Step:5 create a directory for export purpose both in OS level and database level.
mkdir /u01/export
create directory export as '/u01/export';
Directory created
[oracle@orcldbs ~]$ mkdir export
[oracle@orcldbs ~]$ ls
12c.env Documents export oraprod.env Templates
19c.env Downloads Music Pictures utlrp.out
Desktop em13400_linux64-3.zip oradiag_oracle Public Videos
Create a table employee in the user : rahul.
Step:6 Estimate the size of dumpfile by this we are able to know the completion time of export activity.
Once the import is over, you can also review the log file.
13.1 compile invalid objects in target database.
SQL> @?/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*) --------
0
SQL> SELECT version FROM v$timezone_file;
VERSION ----------------- 32
13.3 Run the query to check for currently installed database components.
SQL> col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 set lines 180 set pages 999 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
13.4 Check the CONSTRAINTS count,it is used to display the constraints that are defined in the database.
SQL> SELECT constraint_type, count(*) AS num_constraints
FROM dba_constraints
GROUP BY constraint_type;
C NUM_CONSTRAINTS
C 5784
F 12
O 181
R 327
P 864
V 11
U 250
7 rows selected.
SQL>
yes now we can login to the zhigoma database and we can verify our user(rahul) and the table(employee) in that user is imported into this database or not.
We have successfully upgraded our database from 12 c to 19c using datapump !!!!
