Checking Tablespaces in Database
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP
DATA
Create New Tablespace
SQL> create tablespace india datafile
'/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf' size 30m;
Tablespace created.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP
DATA
INDIA
Check Datafile in whole Database
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/ORACLEAGENT/system.dbf
SYSAUX /u01/app/oracle/oradata/ORACLEAGENT/sysaux.dbf
UNDOTBS /u01/app/oracle/oradata/ORACLEAGENT/undo1.dbf
DATA /u01/app/oracle/oradata/ORACLEAGENT/data01.dbf
INDIA /u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
Check Datafile's in particular Tablespace.
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
CheckTablespace size in particular tablespace
SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='INDIA';
SUM(BYTES)/1024/1024
--------------------
2048
How toCheckTablespace size in whole Database
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024
-------------- --------------------
SYSTEM 2048
INDIA 30
SYSAUX 2048
UNDOTBS 2048
DATA 60
How toResize Datafiles
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
SQL>alter database datafile '/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf' resize60m;
Database altered.
SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='INDIA';
SUM(BYTES)/1024/1024
--------------------
60
Add New Datafile
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
SQL> alter tablespace india add datafile '/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf' size 30m;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf
Rename or Relocate datafilesafter the 12c method
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
FILE_NAME
-------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORACLEAGENT/india01.dbf
/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf
SQL> alter database move datafile '/u01/app/oracle/oradata/ORACLEAGENT/india02.dbf' to '/u02/india03.dbf';
Database altered.
SQL> select file_name from dba_data_files where tablespace_name='INDIA';
Undo – Written on Blue Keyboard Key. Male Hand Presses Button on Black PC Keyboard. Closeup View. Blurred Background. 3D Render.
Undo tablespace check undo tablespace:
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
Check to undo retention:
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
Check to undo management:
SQL> show parameter undo_management;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
SQL> alter system set undo_tablespace='undotbs2' scope=spfile;
System altered.
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string undotbs2
change undo_retention
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
SQL> alter system set undo_retention=920;
System altered.
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 920
check db block size
SQL> show parameter db_block_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
change undo management { auto | manual }
SQL> show parameter undo_management;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
SQL> alter system set undo_management='auto' scope=spfile;
System altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1610609200 bytes
Fixed Size 8897072 bytes
Variable Size 385875968 bytes
Database Buffers 1207959552 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> show parameter undo_management;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
Undo stages:
SQL> select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb,
count(*) counts from dba_undo_extents group by tablespace_name, status order by 1,2;
TABLESPACE STATUS SUM_IN_MB COUNTS
------------------------------ --------- ---------- ----------
UNDOTBS1 EXPIRED 1399.25 200
UNDOTBS1 UNEXPIRED 168 21
UNDOTBS2 EXPIRED .375 6
UNDOTBS2 UNEXPIRED 5.4375 42
Active, expired and unexpired transaction space usage in Undo Tablespace:-
ACTIVE: Status shows us the active transaction going in the database, utilizing the undo tablespace and cannot be truncated.
EXPIRED: Status shows us the transaction which is completed and complete the undo_retention time and now the first candidate for truncated from undo tablespace.
UNEXPIRED: Status shows us the transaction which is completed but not completed the undo retention time. It can be truncated if required.
Free blocks : At the time of creating new undo tablespace shows empty blocks
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:
Oracle Data Guard concept make sure high availability, disaster recovery and data protection of your enterprise database. In Case of disaster or data correction your database can easily run on data guard.
Data Guard can be of two types:
Physical Stand By Database-
Physical Stand by Database provides physical identical copy of your database. It has similar schema and schema. Physical Stand by synchronized by applying redo logs from primary database.
Logical Stand by Database-
Logical Stand by database provides logical information of the data files. It can be of different structure and physical org. Logical Stand by synchronizes by using sql statement and then execute sql statement in logical database.
Query to monitor the data guard status:
Run in Production to get the database role and thread and sequence of archived_log
a. select name, database_role from v$database;
b. select thread#,max(sequence#) from v$archived_log group by thread#;
Run in DR Database:
a. select thread#,max(sequence#) from v$log_history group by thread#;
b. select name,database_role from v$database;
Command to see MRP & RFS services are running or not
select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;
Take current sync status using below query:
SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
If there is any lag between the Production Database versus Standby Database then you have to check the following:
Space in DR using df -h
MRP and RFS status using below query
select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;
If MRP or RFS has issue, you may have to restart it
ALTER database RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database mount standby database;
alter database recover managed standby database disconnect;
You may have to manually copy the Archive from Production to stand by.
In this way you can monitor the DR and incase of issue you can resolve the issue related to DR.
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:
Oracle Data Guard concept make sure high availability, disaster recovery and data protection of your enterprise database. In Case of disaster or data correction your database can easily run on data guard.
Data Guard can be of two types:
Physical Stand By Database-
Physical Stand by Database provides physical identical copy of your database. It has similar schema and schema. Physical Stand by synchronized by applying redo logs from primary database.
Logical Stand by Database-
Logical Stand by database provides logical information of the data files. It can be of different structure and physical org. Logical Stand by synchronizes by using sql statement and then execute sql statement in logical database.
Query to monitor the data guard status:
Run in Production to get the database role and thread and sequence of archived_log
a. select name, database_role from v$database;
b. select thread#,max(sequence#) from v$archived_log group by thread#;
Run in DR Database:
a. select thread#,max(sequence#) from v$log_history group by thread#;
b. select name,database_role from v$database;
Command to see MRP & RFS services are running or not
select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;
Take current sync status using below query:
SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
If there is any lag between the Production Database versus Standby Database then you have to check the following:
Space in DR using df -h
MRP and RFS status using below query
select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;
If MRP or RFS has issue, you may have to restart it
ALTER database RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database mount standby database;
alter database recover managed standby database disconnect;
You may have to manually copy the Archive from Production to stand by.
In this way you can monitor the DR and incase of issue you can resolve the issue related to DR.
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:
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
It is reversal of role between a standby database and a primary database. The primary database assumes role of standby database and the standby database assumes role of primary database after the switchover. There is no data loss in a switchover. This is generally a planned activity.
Overview steps:
Step:1 Check the db name, open mode, database role of the primary and standby databases.
Step:2 select switchover status on primary & standby db.
Step:3 Check that there is no active users connected to the databases.
Step:4Switch the current online redo log file on primary database and verify that it has been applied in the standby database.
Step:5Connect with primary database and initiate the switchover.
Step:6 Bounce the primary db and check the switchover status.
Step:7 Then convert the physical standby into primary db.(stop the MRP process)
Step:8 Open the new standby db in read only mode.
Step:9 Apply the redo log files in newly created standby.(start the MRP process).Check whether the logs are applying in the new standby db.
Step:1 Check the db name, open mode, database role of the primary and standby databases.
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
CANADA READ WRITE PRIMARY
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
CANADA READ ONLY PHYSICAL STANDBY
Step:2 Check that there is no active users connected to the databases.
Step:4 Check the log sequence number in primary and standby db(before switchover).
Step:3 In this step primary db is converted into standby by giving the following command.
SQL>alter database commit to switchover to physical standby with session shutdown;
Database altered
SQL>alter database mount standby database;
Database altered
Step:5 Check name, open_mode, database_role of new standby database.
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
CANADA READ ONLY PHYSICAL STANDBY
Step:6 Then start to apply the redo log (MRP process) on primary(current standby).
