NID is an utility which is used to change the database internal name and dbname of a database. The utility is placed in the $ORACLE_HOME/bin directory. Prior to the nid utility we need to manually create a database and give it a new database name by recreating a control file.
Here I am going to change the db name from standby to Canada.
Overview steps:
Step:1Mount the database with old db name(standby)
Step:2 Run the nid utility (syntax: nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME)
Step:3Once you run the nid utility the name will be changed to new db name.(standby to Canada)
Step:4 Then you have to change the db_name in the parameter file.
Step:5 change the spfile to a new db name.
Step:6 Now open the database with reset logs option.
Step:1 mount the database with old db name.
Step:2 Run the nid utility
Syntax: nid sys/password@CURRENT_DBNAME DBNAME=NEW_DBNAME
Step:3 change the db_name in the parameter file.
Step:4 Rename the spfile to a new db name.
cp spfileSTANDBY.ora spfileCANADA.ora
Step:5 Open the database with resetlogs option.
Step:6 Register the database information to the listener using the command “alter system register;”
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
A compartment is a collection of related resources (such as cloud networks, compute instances, or block volumes) that can be accessed only by those groups that have been given permission by an administrator in your organization.
Steps to create compartment in OCI :
Go to the Navigation menu.
Under Governance and Administration, select Identity, then Compartments.A list of the compartments in your tenancy is displayed.
Select the compartment in which you want to create your instance or create a new compartment. To create a new compartment:
Click Create Compartment to create the compartment to use for creating an instance.
Enter the following:
Name: Enter a name that is unique across all compartments in your tenancy (maximum 100 characters, including letters, numbers, periods, hyphens, and underscores). For example, enter a name such as OICCompartment.
Description: Enter a description for this compartment.
Tags: Enter tags to organize and list resources based on your business needs.
Click Create Compartment.
Return to the navigation pane.
Login to Oracle Cloud Infrastructure to create compartment. Select Compartment Menu by Clicking on Navigation pane, hover over Identity option and click on Compartments.
Click on the Create Compartment button. Once inside Compartments menu, you can see Create Compartment button at top. Click on it to create new compartment.
Fill in the compartment details (Enter the compartment name and description. If you want to make it a child of an existing compartment, select the relevant parent compartment, otherwise accept the default root container) and click on Create Compartment button at left bottom of the screen.
Once you should be able to see your newly created compartment in the list of compartments. when you click in create compartment button.
We can see root compartment contains one sub compartment as we named as Galaxy.By clicking the compartment we get details of compartment like OCID.
If we need to create a instance under the compartment which we created we need to select compartment as I highlighted in left corner. Then we need to create instance by clicking create instance button.
In this document we saw about how we can create compartment.
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
We can do upgrade Oracle Grid Infrastructure below ways:
Rolling Upgrade which involves upgrading individual nodes without stopping Oracle Grid Infrastructure on other nodes in the cluster.
Non-rolling Upgrade which involves bringing down all the nodes except one. A complete cluster outage occurs while the root script stops the old Oracle Clusterware stack and starts the new Oracle Clusterware stack on the node where you initiate the upgrade. After upgrade is completed, the new Oracle Clusterware is started on all the nodes.
Note that some services are disabled when one or more nodes are in the process of being upgraded. All upgrades are out-of-place upgrades, meaning that the software binaries are placed in a different Grid home from the Grid home used for the prior release.
When you upgrade to Oracle Grid Infrastructure 19c, you upgrade to an Oracle Standalone Cluster configuration.
You can use either Oracle ASM or a shared file system to store OCR and voting files on Oracle Standalone Cluster deployments. If storage for OCR and voting files is other than Oracle ASM on other cluster types, then you need to migrate OCR and voting files to Oracle ASM before upgrading to Oracle Grid Infrastructure 19c.
Restrictions for Oracle Grid Infrastructure Upgrades:
Oracle Grid Infrastructure upgrades are always out-of-place upgrades. You cannot perform an in-place upgrade of Oracle Grid Infrastructure to existing homes.
The same user that owned the earlier release Oracle Grid Infrastructure software must perform the Oracle Grid Infrastructure 19c upgrade.
Oracle ASM and Oracle Clusterware both run in the Oracle Grid Infrastructure home.
When you upgrade Oracle Grid Infrastructure, you upgrade to an Oracle Flex Cluster configuration.
Do not delete directories in the Grid home. For example, do not delete the directory Grid_home/OPatch. If you delete the directory, then the Grid infrastructure installation owner cannot use OPatch utility to patch the grid home, and OPatch displays the error message “‘checkdir’ error: cannot create Grid_home/OPatch”.
To upgrade existing Oracle Grid Infrastructure installations to Oracle Grid Infrastructure 19c, you must first verify if you need to apply any mandatory patches for upgrade to succeed.Oracle recommends that you use the Cluster Verification Utility tool (CVU) to check if there are any patches required for upgrading your existing Oracle Grid Infrastructure or Oracle RAC database installations. See Using CVU to Validate Readiness for Oracle Clusterware Upgrades for steps to check readiness.
The software in the 19c Oracle Grid Infrastructure home is not fully functional until the upgrade is completed. Running srvctl, crsctl, and other commands from the new Grid homes are not supported until the final rootupgrade.sh script is run and the upgrade is complete across all nodes.To manage databases in existing earlier release database homes during the Oracle Grid Infrastructure upgrade, use the srvctl from the existing database homes.
To upgrade existing Oracle Clusterware installations to Oracle Grid Infrastructure 19c cluster, your release must be greater than or equal to Oracle Grid Infrastructure 11g Release 2 (11.2.0.4).
Upgrading Shared Grid Homes
If the existing Oracle Clusterware home is a shared home, then you can use a non-shared home for the Oracle Grid Infrastructure for a cluster home for Oracle Clusterware and Oracle ASM 19c.
You can perform upgrades on a shared Oracle Clusterware home.
Check current Oracle Cluster ware installation readiness for upgrades using Cluster
Verification Utility (CVU)
From the 19c Grid Infrastructure home execute:
[oracle@rac01 grid]$ ./runcluvfy.sh stage -pre hacfg
Verifying Physical Memory ...PASSED
Verifying Available Physical Memory ...PASSED
Verifying Swap Size ...PASSED
Verifying Free Space: rac01:/usr,rac01:/etc,rac01:/sbin ...PASSED
Verifying Free Space: rac01:/var ...PASSED
Verifying Free Space: rac01:/tmp ...PASSED
Verifying User Existence: oracle ...
Verifying Users With Same UID: 1001 ...PASSED
Verifying User Existence: oracle ...PASSED
Verifying Group Existence: dba ...PASSED
Verifying Group Existence: oinstall ...PASSED
Verifying Group Membership: oinstall(Primary) ...PASSED
Verifying Group Membership: dba ...PASSED
Verifying Run Level ...PASSED
Verifying Architecture ...PASSED
Verifying OS Kernel Version ...PASSED
Verifying OS Kernel Parameter: semmsl ...PASSED
Verifying OS Kernel Parameter: semmns ...PASSED
Verifying OS Kernel Parameter: semopm ...PASSED
Verifying OS Kernel Parameter: semmni ...PASSED
Verifying OS Kernel Parameter: shmmax ...PASSED
Verifying OS Kernel Parameter: shmmni ...PASSED
Verifying OS Kernel Parameter: shmall ...PASSED
Verifying OS Kernel Parameter: file-max ...PASSED
Verifying OS Kernel Parameter: ip_local_port_range ...PASSED
Verifying OS Kernel Parameter: rmem_default ...PASSED
Verifying OS Kernel Parameter: rmem_max ...PASSED
Verifying OS Kernel Parameter: wmem_default ...PASSED
Verifying OS Kernel Parameter: wmem_max ...PASSED
Verifying OS Kernel Parameter: aio-max-nr ...PASSED
Verifying Package: kmod-20-21 (x86_64) ...PASSED
Verifying Package: kmod-libs-20-21 (x86_64) ...PASSED
Verifying Package: binutils-2.23.52.0.1 ...PASSED
Verifying Package: compat-libcap1-1.10 ...PASSED
Verifying Package: libgcc-4.8.2 (x86_64) ...PASSED
Verifying Package: libstdc++-4.8.2 (x86_64) ...PASSED
Verifying Package: libstdc++-devel-4.8.2 (x86_64) ...PASSED
Verifying Package: sysstat-10.1.5 ...PASSED
Verifying Package: gcc-c++-4.8.2 ...PASSED
Verifying Package: ksh ...PASSED
Verifying Package: make-3.82 ...PASSED
Verifying Package: glibc-2.17 (x86_64) ...PASSED
Verifying Package: glibc-devel-2.17 (x86_64) ...PASSED
Verifying Package: libaio-0.3.109 (x86_64) ...PASSED
Verifying Package: libaio-devel-0.3.109 (x86_64) ...PASSED
Verifying Package: nfs-utils-1.2.3-15 ...PASSED
Verifying Package: smartmontools-6.2-4 ...PASSED
Verifying Package: net-tools-2.0-0.17 ...PASSED
Verifying Package: compat-libstdc++-33-3.2.3 (x86_64) ...PASSED
Verifying Package: libxcb-1.11 (x86_64) ...PASSED
Verifying Package: libX11-1.6.3 (x86_64) ...PASSED
Verifying Package: libXau-1.0.8 (x86_64) ...PASSED
Verifying Package: libXi-1.7.4 (x86_64) ...PASSED
Verifying Package: libXtst-1.2.2 (x86_64) ...PASSED
Verifying Users With Same UID: 0 ...PASSED
Verifying Current Group ID ...PASSED
Verifying Root user consistency ...PASSED
Pre-check for Oracle Restart configuration was successful.
CVU operation performed: stage -pre hacfg
Date: Feb 20, 2021 09:41:39 AM
CVU home: /u02/app/oracle/product/19.3.0.0/grid/
User: oracle
[oracle@rac01 grid]$
Start the 19c Grid Infrastructure upgrade:
[oracle@rac01 grid]$ ./gridSetup.sh
In Pre-req we are facing this issue. We need to apply these two patches then only we can go ahead for 19c grid upgrade.
Update opatch version and apply patches 28553832 and 27006180.
Now, we have grid setup in directory /u02/app/grid/product/19.3.0/grid , before proceed with the installation. let’s check current Grid Infrastructure version.
[oracle@rac01 ~]$ crsctl query has releaseversion
Oracle High Availability Services release version on the local node is [12.2.0.1.0]
[oracle@rac01 ~]$ crsctl query has softwareversion
Oracle High Availability Services version on the local node is [12.2.0.1.0]
As we can see, the current version of Grid Infrastructure is 12.2.0.1.0 that is 12cR2.
We can initiate installation wizard by running below command.
Make sure you bring down your all database instance(s) before initiating upgrade. If you are trying to upgrade grid without bringing down database instance
Select Upgrade Oracle Gird Infrastructure, and click NEXT
Select if you’ve configured EM(Cloud Control) agent on you server, else without selecting any option click NEXT.
As we can see, our Grid Infrastructure Home is by default the location where we’ve copied grid setup, verify Oracle base for Grid Infrastructure and click NEXT.
Here you can provide your root credentials which will automatically run root script during installation, optionally you can use sudo as well.
Here If we get warning for Physical Memory in prerequisite check, as Oracle recommends 8 GB of minimum physical memory for Grid Infrastructure. Here we have sufficient memory so let’s click NEXT.
Summary screen, verify your selections and modify if needed, then click NEXT.
Installation has began now.
Here we get the prompt to run root script , If we provided earlier using the credentials It will ask yes or no prompt but in our case we didn’t provided the credentials so we need to run manually here.
Once you run root.sh script below logs will run in our putty session.
[oracle@rac01:+ASM] sudo /u02/app/oracle/product/19.3.0.0/grid/rootupgrade.sh
[sudo] password for oracle:
Performing root user operation.
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u02/app/oracle/product/19.3.0.0/grid
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file:
/u02/app/oracle/product/19.3.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
/u02/app/oracle/crsdata/rac01/crsconfig/roothas_2021-02-20_10-02-12AM.log
2021/02/20 10:02:15 CLSRSC-595: Executing upgrade step 1 of 12: 'UpgPrechecks'.
2021/02/20 10:02:19 CLSRSC-595: Executing upgrade step 2 of 12: 'GetOldConfig'.
2021/02/20 10:02:23 CLSRSC-595: Executing upgrade step 3 of 12: 'GenSiteGUIDs'.
2021/02/20 10:02:23 CLSRSC-595: Executing upgrade step 4 of 12: 'SetupOSD'.
2021/02/20 10:02:23 CLSRSC-595: Executing upgrade step 5 of 12: 'PreUpgrade'.
ASM has been upgraded and started successfully.
2021/02/20 10:03:30 CLSRSC-595: Executing upgrade step 6 of 12: 'UpgradeAFD'.
2021/02/20 10:03:31 CLSRSC-595: Executing upgrade step 7 of 12: 'UpgradeOLR'.
clscfg: EXISTING configuration version 0 detected.
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
2021/02/20 10:03:35 CLSRSC-595: Executing upgrade step 8 of 12: 'UpgradeOCR'.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.Creating OCR keys for user 'root', privgrp 'root'..Operation successful.
CRS-4664: Node rac01 successfully pinned.
2021/02/20 10:03:37 CLSRSC-595: Executing upgrade step 9 of 12: 'CreateOHASD'.
2021/02/20 10:03:39 CLSRSC-595: Executing upgrade step 10 of 12: 'ConfigOHASD'.
2021/02/20 10:03:39 CLSRSC-329: Replacing Clusterware entries in file
'oracle-ohasd.service'
2021/02/20 10:03:57 CLSRSC-595: Executing upgrade step 11 of 12: 'UpgradeSIHA'.
rac01 2021/02/20 10:04:39 /u02/app/oracle/crsdata/rac01/olr/backup_20210220_100439.olr
724960844
rac01 2020/08/24 00:18:44
/u01/app/oracle/product/12.2.0.1/grid/cdata/rac01/backup_20200824_001844.olr 1977687601
2021/02/20 10:04:39 CLSRSC-595: Executing upgrade step 12 of 12: 'InstallACFS'.
2021/02/20 10:07:49 CLSRSC-327:
Successfully configured Oracle Restart for a standalone server.
And here installation finished.
We have successfully upgraded Oracle Grid Infrastructure 12cR2 to Oracle Grid Infrastructure 19c on our database.
Post checks:
Change ORACLE_HOME pointing to new 19c binaries in bash profile.
If your listener entry is in old ORACLE_HOME change that to new ORACLE_HOME.
Let’s now invoke SQL*Plus and check if we can login to Oracle Grid Infrastructure 19c.
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
Oracle Dataguard physical Standby configuration in 19c:
Overview steps:
Step:1 we have to enable the archive log mode ,flashback on primary databases.
Step:2 Then enable the force logging option.By enabling the force logging option it helps us to capture all the changes made in the database and available for recovery in the redo logs.
Step:3 Add the redolog files for standby database,it is required when you are configuring the standby for maximum protection.
Step:4 Add the listener.ora and tnsnames.ora entries in both primary and standby sides.
Step:5 Change the some set of parameters in primary side,you can change these parameters either by sql or directly make changes in the pfile.
Step:6 Move the password file,pfile from primary to standby using scp.(we can also create the pfile in standby side also by having a single parameter db_name in it)
Step:7 create the directory structure in standby similar to the primary side.
Step:8 Start the standby side database in nomount using the pfile.
Step:9 Connect to the RMAN and duplicate the primary database using the command
duplicate target database for standby from active database dorecover nofilenamecheck;
Step:10 Now our standby database is ready for read only purpose.
Step:11 Then connect to the standby database and start the MRP process using alter database recover managed standby database using current logfile disconnect; This command is used to apply the log files from primary to standby.
Step:12 Verify the current log sequence in both primary and standby sides.
Do the following set of changes in primary side:
Oracle Active Data Guard:
Active Data Guard is a licensed option for Oracle Database Enterprise Edition. … Data Guard automatically synchronizes the primary database and all standby databases by transmitting primary database redo – the information used by every Oracle Database to protect transactions – and applying it to the standby database.
Difference between Oracle Data Guard and Active Data Guard?
Oracle Active Data Guard provides the best data protection and availability for Oracle Database. Active Data Guard enables the offloading of read-only operations, backups, and so on, to an up-to-date physical standby database while also providing disaster protection.
Step:1 Enable the archive log mode in the primary database.
SQL> alter database archivelog;
Database altered
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
Step:2 Enable the force logging.
SQL>alter database force logging;
Database altered
Step:3 Flashback should be enabled in primary db.
ALTER DATABASE FLASHBACK ON;
Database altered
Check the archive log,force_logging,flashback is enabled.
SQL> select name,force_logging,flashback_on,log_mode from v$database;
NAME FORCE_LOGGING FLASHBACK_ON LOG_MODE
--------- --------------------------- ------------ -------------
CANADA YES YES ARCHIVELOG
Step:4 Add the Standby redo log files for standby side.
SQL> alter database add standby logfile thread 1
group 4('/u01/app/oracle/oradata/redo04.log')size 50m;
Database altered.
SQL> alter database add standby logfile thread 1
group 5('/u01/app/oracle/oradata/redo05.log')size 50m;
Database altered.
SQL> alter database add standby logfile thread 1
group 6('/u01/app/oracle/oradata/redo06.log')size 50m;
Database altered.
SQL> alter database add standby logfile thread 1
group 7('/u01/app/oracle/oradata/redo07.log')size 50m;
Database altered.
Step:5 set the log archive config parameter.
SQL> alter system set log_archive_config='DG_config=(canada,standby)';
System altered.
SQL> show parameter log_archive_config
NAME TYPE VALUE
--------------------------------- ----------- ------------------------------
log_archive_config string DG_config=(canada,standby)
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------- ----------- ---------------------------
log_archive_dest_2 string SERVICE=standby NOAFFIRM ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standby
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
NAME TYPE VALUE
-------------------------------- ----------- -----------------------
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
Step:6 Set the log archive format = ‘%t_%s_%r.arc ,it determines the name of the archive log file it comes into role when archive log is enabled.
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
System altered.
Step:7 configure the log_archive_process to 30 ,it prevents to avoid any runtime overhead of invoking additional ARCn process,you can set the LOG_ARCHIVE_MAX_PROCESSES
SQL> alter system set log_archive_max_processes=30;
System altered.
Step:8 register the fal_server and fal_client as standby(standby) and physical(primary) databases respectively.
SQL> alter system set fal_server=standby;
System altered.
SQL> alter system set fal_client=physical;
System altered.
Step:9 The standby_file_management initialization parameter plays an important role in the recovery process.
SQL> alter system set standby_file_management=auto;
System altered.
Step:10 change the remote_login_password file as exclusive.
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
remote_login_passwordfile string EXCLUSIVE
Step:11 db_file_name_convert and log_file_name_convert, converts the filename of a new datafile and logfile respectively on the primary database to a filename on the standby database.
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/canada/'
,'/u01/app/oracle/oradata/standby/' scope=spfile;
System altered.
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/canada/'
,'/u01/app/oracle/oradata/standby/' scope=spfile;
System altered.
Step:12 Configure the tns and listener files on both primary and standby databases.
listener entry in the primary database.
tnsnames entry in the primary database.
listener entry in the standby database.
tnsnames entry in the standby database.
Step:13 ping your primary and standby databases from one to another.
Step:14 create the similar directory structures in standby side as of in primary.
Step:15 Transfer the password files,pfiles to the standby side through scp.
Step:16 create the initcanada.ora file containing the single parameter db_name=canada in the standby side it is useful to start our database in no mount stage.
Step:17 start the db in nomount stage and connect to the rman to duplicate the target database.
Step:18 Duplicate the target database using the following command,
duplicate target database for standby from active database
dorecover nofilenamecheck;
[oracle@localhost admin]$
rman target sys/Pass#1234@canada auxiliary sys/Pass#1234@standby
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Feb 17 07:28:58 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CANADA (DBID=215384084)
connected to auxiliary database: CANADA (not mounted)
RMAN>
duplicate target database for standby from active database dorecover nofilenamecheck;
Starting Duplicate Db at 17-FEB-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format
'/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwcanada';
}
executing Memory Script
Starting backup at 17-FEB-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=112 device type=DISK
Finished backup at 17-FEB-21
contents of Memory Script:
{
restore clone from service 'canada' standby controlfile;
}
executing Memory Script
Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
output file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/cntrlcanada.dbf
Finished restore at 17-FEB-21
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05158: WARNING: auxiliary (datafile) file name
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp
conflicts with a file used by the target database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf";
restore
from nonsparse from service
'canada' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:09
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:37
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-FEB-21
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service 'canada'
archivelog from scn 2091702;
switch clone datafile all;
}
executing Memory Script
Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=11
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-FEB-21
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1064734612 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf
contents of Memory Script:
{
set until scn 2092999;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 17-FEB-21
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 10 is already on disk as file
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1064697431.dbf
archived log for thread 1 with sequence 11 is already on disk as file
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_11_1064697431.dbf
archived log file name=
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1064697431.dbf
thread=1 sequence=10
archived log file name=
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_11_1064697431.dbf
thread=1 sequence=11
media recovery complete, elapsed time: 00:00:03
Finished recover at 17-FEB-21
contents of Memory Script:
{
delete clone force archivelog all;
}
executing Memory Script
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=112 device type=DISK
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/
dbs/arch1_10_1064697431.dbf RECID=1 STAMP=1064734609
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/
dbs/arch1_11_1064697431.dbf RECID=2 STAMP=1064734611
Deleted 2 objects
Finished duplicate at 17-FEB-21
Yes,we have created the physical standby database successfully.
Step:19 Then mount the database and open the database in the read only mode.check the name,open_mode,database_role in the standby database,in the role it should have the value as physical_standby.
Step:20 Check for the max(sequence#) from the v$archived_log view in both primary and standby databases.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
11
Step:21 Apply the log files using MRP process.
SQL> alter database recover managed standby database using current
logfile disconnect;
Database altered.
Step:22 check for the sequence no which is applied in standby.
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log
ORDER BY sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
10 17-FEB-21 17-FEB-21 YES
11 17-FEB-21 17-FEB-21 YES
Step:23 We can check the difference in sequence using the following query.
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
SQL>
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 11 11 0
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
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
Let us see how to create and configure ACLs in Oracle database.
ORA-24247: network access denied by access control list (ACL)
When I was trying to send mail from my Oracle Database and I got below mentioned error :-
ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at “CINP01314”, line 255 ORA-06512: at line 21
We need to create and configure ACL(access control list). In order to use PL/SQL network utility packages like UTL_SMTP, UTL_MAIL, we have to configure an ACL file that provides fine grained access to external network services.
Below Steps need to follow configure :-
1. Find out your SMTP outgoing mail server and configure the database parameter smtp_out_server.
###### only for UTL_MAIL package this parameter needs to be set#######
SQL> alter system set smtp_out_server= '10.10.10.10' ;
system altered
2. Create an access control list file :-
begin
DBMS_NETWORK_ACL_ADMIN
.create_acl (
acl => 'utl_smtp.xml',
description => 'Enables mail to be sent',
principal => 'INDIA', -- USERNAME to which access has to be granted
is_grant => true,
privilege => 'connect');
commit;
end;
/
PL/SQL procedure successfully completed.
5. Check that for INDIA user permission has been set properly :-
SQL> col HOST for a45
SQL> SELECT host, lower_port, upper_port, privilege, status FROM
user_network_acl_privileges;
HOST LOWER_PORT UPPER_PORT PRIVILEGE STATUS
------------------- ----------------- --------------- ---------- ------------
10.0.06.01 25 25 connect GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED
–Status column must have value granted.
SQL> SELECT DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege
('utl_smtp.xml','INDIA','connect'),1,'GRANTED',0,'DENIED', NULL) privilege FROM dual;
PRIVILEGE
---------------------
GRANTED
SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,principal,privilege,is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;.
ACL PRINCIPAL PRIVILEGE IS_GRANT START_DATE END_DATE
---------------------- ----------- -------------- ------------ ----------- --------
/sys/acls/utl_smtp.xml INDIA connect true
Once you got output like above queries. You can test you procedure to send mail, it will work.
As we have create ACL for utl_smtp.xml we can create for utl_mail.xml and utl_tcp.xml after creation of both these ACL’s output of above query will be like below :-
ACL PRINCIPAL PRIVILEGE IS_GRANT START_DATE END_DATE
--------------------- ----------- ----------- -------- -------- -------
/sys/acls/utl_mail.xml INDIA connect true
/sys/acls/utl_smtp.xml INDIA connect true
/sys/acls/utl_tcp.xml INDIA connect true
TO enable this ACL permission for a different USER other than above user(INDIA) :-
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
Let us see how to create and configure ACLs in Oracle database.
ORA-24247: network access denied by access control list (ACL)
When I was trying to send mail from my Oracle Database and I got below mentioned error :-
ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at “CINP01314”, line 255 ORA-06512: at line 21
We need to create and configure ACL(access control list). In order to use PL/SQL network utility packages like UTL_SMTP, UTL_MAIL, we have to configure an ACL file that provides fine grained access to external network services.
Below Steps need to follow configure :-
1. Find out your SMTP outgoing mail server and configure the database parameter smtp_out_server.
###### only for UTL_MAIL package this parameter needs to be set#######
SQL> alter system set smtp_out_server= '10.10.10.10' ;
system altered
2. Create an access control list file :-
begin
DBMS_NETWORK_ACL_ADMIN
.create_acl (
acl => 'utl_smtp.xml',
description => 'Enables mail to be sent',
principal => 'INDIA', -- USERNAME to which access has to be granted
is_grant => true,
privilege => 'connect');
commit;
end;
/
PL/SQL procedure successfully completed.
5. Check that for INDIA user permission has been set properly :-
SQL> col HOST for a45
SQL> SELECT host, lower_port, upper_port, privilege, status FROM
user_network_acl_privileges;
HOST LOWER_PORT UPPER_PORT PRIVILEGE STATUS
------------------- ----------------- --------------- ---------- ------------
10.0.06.01 25 25 connect GRANTED
<your smtp server host name or address> connect GRANTED
black connect GRANTED
–Status column must have value granted.
SQL> SELECT DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege
('utl_smtp.xml','INDIA','connect'),1,'GRANTED',0,'DENIED', NULL) privilege FROM dual;
PRIVILEGE
---------------------
GRANTED
SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30
SQL> SELECT acl,principal,privilege,is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;.
ACL PRINCIPAL PRIVILEGE IS_GRANT START_DATE END_DATE
---------------------- ----------- -------------- ------------ ----------- --------
/sys/acls/utl_smtp.xml INDIA connect true
Once you got output like above queries. You can test you procedure to send mail, it will work.
As we have create ACL for utl_smtp.xml we can create for utl_mail.xml and utl_tcp.xml after creation of both these ACL’s output of above query will be like below :-
ACL PRINCIPAL PRIVILEGE IS_GRANT START_DATE END_DATE
--------------------- ----------- ----------- -------- -------- -------
/sys/acls/utl_mail.xml INDIA connect true
/sys/acls/utl_smtp.xml INDIA connect true
/sys/acls/utl_tcp.xml INDIA connect true
TO enable this ACL permission for a different USER other than above user(INDIA) :-
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
UTL_SMTP is a Oracle PL/SQL package which is used to send e-mails from Oracle Database over SMTP(simple mail transfer protocol).
We can send two types of mails using UTL_SMTP:-
Mails without attachment.
Mails with attachment.
Mails without attachment :-
SQL> DECLARE c UTL_SMTP.CONNECTION;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;
BEGIN
c := UTL_SMTP.OPEN_CONNECTION('10.10.1.10');
UTL_SMTP.HELO(c, '10.10.1.10');
UTL_SMTP.MAIL(c, '[email protected]');
UTL_SMTP.RCPT(c, '[email protected]');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <[email protected]>');
send_header('To', '"Recipient" <[email protected]>');
send_header('Subject', 'Hello');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL;
-- When the SMTP server is down or unavailable, we don't have-- a connection to the server. The QUIT call will raise an-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
/
DECLARE
*
ERROR at line 1: ORA-20000: Failed to send mail: Error code -24247: ORA-24247: network access denied by access control list (ACL) ORA-06512: at line 25
If you get above mentioned error then its because your user is not added in Oracle Access control list. In Oracle Database we have to configure ACL in order to use PL/SQL packages.
Once we configure ACL then we are able to successfully send mails using above mentioned code.
Please follow below steps :-
SQL> DECLARE
c UTL_SMTP.CONNECTION;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;
BEGIN
c := UTL_SMTP.OPEN_CONNECTION('10.10.1.10');
UTL_SMTP.HELO(c, '10.10.1.10');
UTL_SMTP.MAIL(c, '[email protected]');
UTL_SMTP.RCPT(c, '[email protected]');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <[email protected]>');
send_header('To', '"Recipient" <[email protected]>');
send_header('Subject', 'Hello');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL;
-- When the SMTP server is down or unavailable, we don't have-- a connection to the server. The QUIT call will raise an-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
/
PL/SQL procedure successfully completed.
Mail has been send now.
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
