HOW TO CHECK WHETHER PHYSICAL STANDBY IS IN SYNC WITH THE PRIMARY OR NOT AND RESOLVED GAP ?

HOW TO CHECK WHETHER PHYSICAL STANDBY IS IN SYNC WITH THE PRIMARY OR NOT AND RESOLVED GAP ?

Step by Step Process to Resolved gap on Standby database.

Summary
1. Check the name and status of database.
2. Check for GAP on standby
3. Check redo received on standby
4. Check redo applied on standby
5. Identify missing archive log files
6. Copy archive log files
7. Register archive log files with standby
8. Restart the managed recovery operations

******************************************************************************************************************

Step 1 : Check the status of database on both server.
On Primary Server.
SQL> select name, open_mode, database_role from v$database;
NAME      OPEN_MODE  DATABASE_ROLE
——— ———- —————-
MYDB   READ WRITE PRIMARY

SQL> set sqlprompt “PRIMARY’@’_connect_identifier>”
PRIMARY@MYDB>

On Standby Server.

SQL> select name, open_mode, database_role from v$database;
NAME      OPEN_MODE  DATABASE_ROLE
——— ———- —————-
MYDB   MOUNTED    PHYSICAL STANDBY

SQL> set sqlprompt “SECONDARY’@’_connect_identifier>”
STANDBY@MYDB>

Step 2 : Check for GAP on Standby
PRIMARY@MYDB>select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
————–
         76921
STANDBY@MYDB>select max(sequence#) from v$log_history;
MAX(SEQUENCE#)
————–
         76921

STANDBY@MYDB>SELECT THREAD# “Thread”,SEQUENCE# “Last Sequence Generated”
 FROM V$ARCHIVED_LOG
          WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
          ORDER BY 1;
    Thread Last Sequence Generated
———- ———————–
         1                   76921



Step 3 & 4: Check redo received and applied on standby.

STANDBY@MYDB> 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;
    Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
         1                  76922                 20931      55991

Step 5: Identify the missing archive log file.

 STANDBY@MYDB>SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected

—-If found gap

Step 6: Copy missing archive log file
After identifying a gap (as shown above), the DBA will need to query the primary database to locate the archived redo logs on the primary database. The following query assumes the local archive destination on the primary database is LOG_ARCHIVE_DEST_1:
PRIMARY@MYDB>  SELECT name
 FROM v$archived_log
 WHERE thread# = 1
 AND dest_id = 1
 AND sequence# BETWEEN 20931 and 76922;
Output:
/oracle/bases/MYDB/archives/MYDB_0001_0716381751_0000076922.arc
56027 rows selected.

Step 7: Register archive logfile with standby.
Copy the above redo log files to the physical standby database and register them using the ALTER DATABASE REGISTER LOGFILE … SQL statement on the physical standby database.
For example:
STANDBY@MYDB> ALTER DATABASE REGISTER LOGFILE ‘/oracle/bases/MYDB/archives/MYDB_0001_0716381751_0000076922.arc’;

Step 8: Restart the managed recovery operations.
— After the redo logs have been registered on the physical standby database, the DBA can restart the managed recovery operations.
  For example, to put the physical standby database into automatic recovery managed mode:
STANDBY@MYDB> alter database recover managed standby database disconnect from session;

WHAT IS SCAN IN RAC ?

WHAT IS SCAN IN RAC ?

Single Client Access Name (SCAN) is a feature used in Oracle RAC environments that provides a single name for clients to access any Oracle Database running in a cluster.The main benefit is that the client’s connect information does not need to change if you add or remove nodes or databases in the cluster.

In versions prior to Oracle 11.2, an entry in the tnsnames.ora file for a n-node RAC database always referenced all
nodes in the ADDRESS_LIST section, as in the listing that follows:

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = tcp)(HOST = rac1-vip.localdomain)(PORT =1521))
(ADDRESS = (PROTOCOL = tcp)(HOST = rac2-vip.localdomain)(PORT =1521))
(ADDRESS = (PROTOCOL = tcp)(HOST = rac3-vip.localdomain)(PORT =1521))
(ADDRESS = (PROTOCOL = tcp)(HOST = rac4-vip.localdomain)(PORT =1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testing)
)
)

The use of a SCAN address removes this problem, instead of addressing every single node as before, the SCAN virtual IP addresses refer to the cluster. Using the SCAN, the preceding connection entry is greatly simplified. Instead of listing each node’s virtual IP address, all we need to do is enter the SCAN rac-scan.localdomain.com. Here’s the simplified version:

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = rac-scan.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testing)
)
)

SCAN

Now in the following post we’ll understand how this SCAN concept manages the connections internally, for that we first need to understand the set-up of SCAN.

SCAN configuration using DNS (Domain Name Service)

For this you need to have a DNS entry with one single name that resolves to 3 IP addresses using a round robin algorithm. The IP addresses must be on the same subnet as your default public network in the cluster (In 12c R1 SCAN would support multiple subnets).

SCAN configuration using GNS (Grid Name Service)

In this case Oracle assumes you use some form of dynamic IP assignment on your public network, so you just need to mention the SCAN name and 3 IPs will be acquired from either a DHCP service or SLAAC when using IPv6 based IP address.

Round Robin Algorithm

Round-robin on DNS level allows for a connection request load balancing across SCAN listeners floating in the cluster.  If your DNS is set up to provide round-robin access to the IPs resolved by the SCAN entry, then run the “nslookup” command at least twice to see the round-robin algorithm work. The result should be that each time, the “nslookup” would return a set of three IPs in a different order.

1) nslookup rac-scan

Server: 192.168.46.1
Address: 192.168.46.1#53

Non-authoritative answer:
Name: rac-scan.localdomain
Address: 192.168.46.30
Name: rac-scan.localdomain
Address: 192.168.46.31
Name: rac-scan.localdomain
Address: 192.168.46.32

2) nslookup rac-scan

Server: 192.168.46.1
Address: 192.168.46.1#53

Non-authoritative answer:
Name: rac-scan.localdomain
Address: 192.168.46.31
Name: rac-scan.localdomain
Address: 192.168.46.32
Name: rac-scan.localdomain
Address: 192.168.46.30

Three IP addresses are recommended considering load balancing and high availability requirements regardless of the number of servers in the cluster.

SCAN (Single Client Access Name)

The CRSd process starts orarootagent & oraagent processes,  orarootagent is responsible for starting Node VIP, SCAN VIP and oraagent process is responsible for starting SCAN Listener and Node Listener.

For each of the 3 IP addresses that the SCAN resolves to, a SCAN VIP resource is created and a SCAN Listener is created. The SCAN Listener is dependent on the SCAN VIP and the 3 SCAN VIPs (along with their associated listeners) will be dispersed across the cluster.REMOTE_LISTENER parameter is set to the SCAN by default this allows the instances to register with the SCAN Listeners as remote listeners to provide information on what services are being provided by the instance, the current load and a recommendation on how many incoming connections should be directed to the instance.

The LOCAL_LISTENER parameter is set to the node-VIP,  the node listener run out of the Oracle Grid Infrastructure home and listens on the node-VIP using the specified port (default port is 1521).

So up till now we understood, SCAN configuration, SCAN IPs, SCAN Listener & Node Listener and how the Instances provide relevant information to SCAN Listeners, now we’ll understand the Client Side Load balancing in RAC using SCAN which would further explain another major benefit of using SCAN.

Connection Load Balancing

loadbalancing

Client Side Load Balancing – For clients connecting using Oracle SQL*Net 11g Release 2 and above, 3 IP addresses will be received by the client by resolving the SCAN name through DNS. The client will then go through the list it receives from the DNS and try connecting through one of the IPs received. If the client receives an error, it will try the other addresses before returning an error to the user or application. This is similar to how client connection failover works in previous releases when an address list is provided in the client connection string.

Server Side Load Balancing – When a SCAN Listener receives a connection request, the SCAN Listener will check for the least loaded instance providing the requested service. It will then re-direct the connection request to the local listener on the node where the least loaded instance is running. Subsequently, the client will be given the address of the local listener. The local listener will finally create the connection to the database instance.

I hope the above information helps you in understanding the SCAN Concept, how it is configured and how it is used for client/server side connect time load balancing.

Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 12c Release 2 (12.2) (Doc ID 2173141.1)

ht (c) 2018, Oracle. All rights reserved.

Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 12c Release 2 (12.2) (Doc ID 2173141.1) To Bottom In this Document Purpose Scope Details Step 1: Upgrade Path for 12.2 Oracle database Minimum version of the database that can be directly upgraded to Oracle 12c Release 1 (12.2) Intermediate upgrades needs to be carried for following releases Step 2: Requirements and recommendations for source database Step3: Requirements and recommendations for target ORACLE_HOME Step 4: Check the health of the source database Step 5: Pre-upgrade checks Clean up database Check materialized views Performance Checking Time zone settings Back up the database Ensure no files are in Back up mode before starting the upgrade Purge Recycle bin Save Oracle EM DB Control Configuration and Data Steps to save data Manually remove DB control with emremove.sql Drop JSON-Enabled Context search Indexes Check the accounts use Case-Insensitive password version Remove Unified Auditing Schema and Roles Put Schema based tablespaces offline during upgrade Preserve Downgrade capability Audit table preupgrade requirements Cluster DB Requirements Other Checks Step 6: Preupgrade step Dependencies on Network Utility Packages Check Time zone version Step7: Upgrade Database to 12.2 Step 8: Post-upgrade Setting Environment variables on Linux and Unix Update oratab entries Post-upgrade fixup script Upgrading Tables Dependent on Oracle-Maintained Types Enabling the New Extended Data Type Capability Recovery Catalog Upgrade Upgrade the Time Zone File Version After Upgrading Oracle Database Upgrading Statistics Tables Upgrade Externally Authenticated SSL Users Install Oracle Text Supplied Knowledge Bases After Upgrading Oracle Database Update Oracle Application Express Configuration After Upgrading Oracle Database Configure Access Control Lists (ACLs) to External Network Services Enabling Oracle Database Vault After Upgrading Oracle Database Check for the SQLNET.ALLOWED_LOGON_VERSION Parameter Behavior Known issues References APPLIES TO: Oracle Database - Enterprise Edition - Version 10.2.0.5.0 to 12.2.0.1 [Release 10.2 to 12.2] Oracle Database - Standard Edition - Version 10.2.0.5.0 to 12.2.0.1 [Release 10.2 to 12.2] Information in this document applies to any platform. PURPOSE  This document is created for use as a guideline and checklist when manually upgrading from Oracle 10gR2 (10.2) , Oracle 11gR1 (11.1) or Oracle 11gR2 (11.2) or Oracle 12c Release 1 (12.1) to Oracle 12c Release 2 (12.2) SCOPE  Database Administrators, Support DETAILS Step 1: Upgrade Path for 12.2 Oracle database Minimum version of the database that can be directly upgraded to Oracle 12c Release 1 (12.2) Source Database Target Database 11.2.0.3 / 11.2.0.4 12.2.x 12.1.0.1 / 12.1.0.2 12.2.x Intermediate upgrades needs to be carried for following releases Source Database Intermediate upgrade path Target database 11.2.0.1 / 11.2.0.2 --> 11.2.0.4 --> 12.2.x 11.1.0.6 / 11.1.0.7 --> 11.2.0.4 --> 12.2.x 10.2.0.2/10.2.0.3/10.2.0.4/10.2.0.5 --> 11.2.0.4 / 12.1.0.2 --> 12.2.x 10.1.0.5 --> 11.2.0.4 / 12.1.0.2 --> 12.2.x 9.2.0.8 --> 11.2.0.3 / 11.2.0.4 --> 12.2.x For example If you are upgrading from release 11.2.0.2 or 11.1.0.7, then you must first upgrade to Oracle Database 11g release 2 (11.2.0.4). If you are upgrading from release 10.2.0.2, 10.2.0.3, 10.2.0.4,10.2.0.5 or 10.1.0.5, then you must first upgrade to release 11.2.0.4 or 12.1.0.2. If you are upgrading from release 9.2.0.8, then you must first upgrade to a sequence of intermediate Oracle Database releases: Upgrade from release 9.2.0.8 to release 11.2.0.3 or 11.2.0.4. Then upgrade from release 11.2.0.4 to 12.2 Step 2: Requirements and recommendations for source database Take a cold or hot back up of the source database. Disable any custom triggers that would get executed before / after DDL. You can enable it once upgrade is completed. Data security roles defined in a release 11g Oracle Database instance are not automatically converted to ORAS. Before you upgrade an 11g database to Oracle Database 12c, you must delete any data security roles that are defined in the 11g database. After the upgrade, you may use Analytic Workspace Manager 12c to define the data security roles again. If you upgrade an 11g database to Oracle Database 12c without deleting the 11g data security roles, then any data security policies that include a data security role are invalid in the Oracle Database 12c database. Timezone should less than or equal to target database timezone version. IF APEX is installed then it is recommended to upgrade APEX in the source DB first before upgrading DB Step3: Requirements and recommendations for target ORACLE_HOME Verify the whether your operating system is certified for 12.2.  Click here to launch certification portal Install 12.2.0.1, verify there are no installation related issues. Download and install latest PSU if any Copy spfile or pfile from source ORACLE_HOME (under $ORACLE_HOME/dbs) to target home Remove any _parameter, obsolete and deprecated parameters in pfile Note min value of COMPATIBLE parameter to upgrade 12.2 is “11.2.0”, make sure you have COMPATIBLE parameter is set to 11.2.0 or greater Review patch recommendations as given in the article "Patches to apply before upgrading Oracle GI and DB to 12.2.0.1 (Doc ID 2180188.1)" Step 4: Check the health of the source database Execute dbupgdiag.sql (refer note 556610.1 to download this script) and verify whether there are any INVALID components or objects owned by SYS/SYSTEM. If any, fix them before proceeding to upgrade the database. You can execute utlrp.sql multiple times to VALIDate them, if still objects are INVALID, create a service request with Oracle support. Execute utlrp.sql multiple times and verify there are no INVALID objects. Step 5: Pre-upgrade checks Clean up database Empty the recycle bin Check for INVALID objects in SYS and SYSTEM Check for duplicate objects in SYS and SYSTEM Check for INVALID, mandatory, obsolete components Check materialized views Check the status of all materialized views (MV), and refresh any materialized views that are not fresh. Check the size of your materialized view logs. If any materialized view logs have non-zero rows, then refresh the base table materialized views. Check the size of direct loader logs and PMOP logs (partition maintenance operation logs). If any direct loader logs or PMOP logs have non-zero rows, then refresh the MVs indicated by the logs Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing. 1. Run the following SQL query: SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;   Performance Preserve performance statistics Check network performance Gather Optimizer statistics To decrease the amount of downtime, gather statistics. Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATSprocedure to gather these statistics. For example, enter the following SQL statement: SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; Checking Time zone settings Default time zone for Oracle database 12.2 is V26 Time zone should less than or equal to target database time zone version. If source is having higher time zone, then apply time zone patch on target ORACLE_HOME to match the source. Back up the database Back up the database, create a guaranteed flashback restore point, or both. Test your fallback strategies at least once before your upgrade window. Ensure that you have fallback strategies for issues both during upgrade, and after upgrade. Connect to RMAN rman "target / nocatalog" Execute RMAN command to backup RUN { ALLOCATE CHANNEL chan_name TYPE DISK; BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade; BACKUP CURRENT CONTROLFILE FORMAT 'controlfile location and name'; } Ensure no files are in Back up mode before starting the upgrade Run the following statement: SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';   Purge Recycle bin To empty the database recycle bin, run the following command: SQL> PURGE DBA_RECYCLEBIN Note: The database recycle bin must be empty during the upgrade process to avoid possible ORA-00600 errors, and to minimize the upgrade time. Save Oracle EM DB Control Configuration and Data If you plan to downgrade database after upgrading to 12.2, then before starting the upgrade save the DB Control configuration and data using emdwgrd utility, so that you can restore the files after downgrading. Steps to save data 1. Install the software for the new Oracle Database 12c release. 2. Set ORACLE_HOME to your old Oracle home. 3. Set ORACLE_SID to the SID of the database being upgraded. 4. Set PATH, LD_LIBRARY_PATH, and SHLIB_PATH to point to the upgraded Oracle Database Oracle home. 5. Change directory to the new Oracle Database release Oracle home. 6. Run emdwgrd Single-instance databases: emdwgrd[sh|bat] -save -sid old_SID -path save_directory Oracle Real Application Clusters (Oracle RAC) databases: Remote copy must be enabled across all cluster member nodes. Use EM_REMCP environment variable, to indicate which remote copy is configured, for example: export EM_REMCP /usr/bin/scp emdwgrd -save -cluster -sid old_SID -path save_directory 7. Enter the SYS password for the database that you want to upgrade. Manually remove DB control with emremove.sql Stop/shutdown DB control emctl stop dbconsole    Login as sysdba SQL>SET ECHO ON SQL>SET SERVEROUTPUT ON SQL>@emremove.sql >> Script located in new 12c ORACLE_HOME/rdbms/admin Manually remove ORACLE_HOME/HOSTNAME_SID/ and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID directory from the system On windows delete DB Console service OracleDBConsoleSID Drop JSON-Enabled Context search Indexes If you created a JSON search index using Oracle Database 12c Release 1 (12.1.0.2) then Oracle recommends that you drop that index and create a new search index for use with later releases. Check the accounts use Case-Insensitive password version Log in to SQL*Plus as an administrative user, and enter the following SQL query SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS; If there are any 10g versions, suggest you to refer Oracle documentation to fix 10g versions, failing to do so, user accounts with LOCKED after upgrade is completed. Remove Unified Auditing Schema and Roles Note: If you have created AUDIT_ADMIN and AUDIT_VIEWER users or roles with Oracle Database 12c release 1 (12.1) or if the database was created in 12.1, then you do not drop these roles and AUDSYS user. For more information refer to URL https://docs.oracle.com/database/122/UPGRD/unified-auditing-audit_admin-audit_viewer-changes.htm#UPGRD60010 Log into SQL*Plus as SYS with SYSDBA privilege.  Drop AUDSYS schema if exists. Start DB in migrate mode and drop AUDSYS user SQL> startup migrate pfile=$T_WORK/t_init1.ora ORACLE instance started. SQL> drop user audsys cascade; Drop AUDIT_ADMIN and AUDIT_VIEWER roles DROP ROLE AUDIT_ADMIN; DROP ROLE AUDIT_VIEWER; Put Schema based tablespaces offline during upgrade Note down the table space names which needs to be offline during upgrade.  Use -T option followed by table space name dbupgrade –T  Starting with Oracle Database 12c release 2, you can run the Parallel Upgrade Utility with the -T option to have schema-based tablespaces (user tablespaces) taken offline during the upgrade. Taking these tablespaces offline can reduce the necessity of backing up before upgrades. The Parallel Upgrade Utility (catctl.pl) analyzes tablespaces, and automatically selects the right set of tablespaces to set to read only.  The utility does not set to READ ONLY any tablespaces that contain Oracle-Maintained objects Preserve Downgrade capability If you have any plans to downgrade the database to its previous version, then you must have patch 20898997 installed in source ORACLE_HOME, else downgrade will not be possible. On source ORACLE_HOME $ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed |grep –i "20898997"    If patch is not installed, download patch 20898997 from MOS and install Audit table preupgrade requirements If upgrading from pre 12.1 release and using Oracle database Vault, Oracle Label Security, then you must first run olspreupgrade.sql.  Copy $ORACLE_HOME/rdbms/admin/olspreupgrade.sql from target home (12.2).  Connect as DVOWNER to the source db SQL> GRANT DV_PATCH_ADMIN to SYS; Grant DV_PATCH_ADMIN to SYS.  Connect SYS as SYSDBA CONNECT SYS AS SYSDBA Execute olspreupgrade.sql SQL>ORACLE_HOME/rdbms/admin/olspreupgrade.sql After completion, connect as DVOWNER and revoke DV_PATCH_ADMIN to SYS SQL> REVOKE DV_PATCH_ADMIN from SYS; Cluster DB Requirements Upgrade GI first before upgrading the database.  If RAC, then database set CLUSTER_DATABASE to false in the init parameter file. Other Checks Ensure you have enough space in ARCHIVE_LOG and FLASHBACK location. Step 6: Preupgrade step Execute Preupgrade script from source home $Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar FILE TEXT DIR output_dir FILE - Use this option to direct output to a file TEXT - Use this option to specify log should be in Text format (other option is to have XML output) DIR - Logs will be created under <output_dir> It is recommended to execute pre-upgrade fixup script, if any, which are AUTO FIXABLE Dependencies on Network Utility Packages Execute the following query SQL> SELECT * FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS'); To ensure that the new access controls are part of your upgrade testing, prepare a post-upgrade script to make the scripts available in your database environment.  After the upgrade, grant specific required privileges. Access is based on the usage in the original database. Check Time zone version Check if target database's time zone version is lower than the source database time zone version.  If yes, before starting upgrade time zone should be upgrade without fail.  RDBMS DST patches are available in Note 412160.1 Step7: Upgrade Database to 12.2 Set the environment variables to point to target ORACLE_HOME export ORACLE_HOME=<path to Oracle 12.2> export PATH=$ORACLE_HOME/bin:$PATH export ORACLE_BASE=<path to Oracle_Base set during installation>    Start DB in upgrade mode from target ORACLE_HOME CONNECT / AS SYSDBA SQL> startup upgrade; SQL> exit On Linux/Unix cd $ORACLE_HOME/bin ./dbupgrade   On Windows cd %ORACLE_HOME%\bin dbupgrade Execute Post-Upgrade Status Tool, utlu122s.sql and review the upgrade spool log file.  You run the Post-Upgrade Status Tool in the environment of the new release. $ sqlplus "/as sysdba" SQL> STARTUP SQL> @utlu122s.sql Verify the upgrade log whether catuppst.sql has been executed or not.  If not, execute it manually from new ORACLE_HOME, located at $ORACLE_HOME/rdbms/admin directory SQL> @catuppst.sql   Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session. SQL> @utlrp.sql Check the state of the Oracle Data Dictionary for diagnosing upgrades and migrations.  The dbupgdiag.sql script collects upgrade and migration diagnostic information about the current state of the data dictionary.  You can run the script in SQL*Plus on the upgraded database as the SYS user.  Refer Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.  After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine. If you are using Oracle Clusterware, then you must upgrade the Oracle Clusterware keys for the database.  Run srvctl for Oracle Database 12c to upgrade the database. For example: ORACLE_HOME/bin/srvctl upgrade database -db name -o ORACLE_HOME Step 8: Post-upgrade  Setting Environment variables on Linux and Unix Confirm that the following environment variables point to the directories of the new Oracle home: ORACLE_HOME PATH Update oratab entries Modify the corresponding entry in the /etc/oratab file to point to the new ORACLE_HOME location. Post-upgrade fixup script Execute post-upgrade fixup scripts generated by the pre-upgrade script. Upgrading Tables Dependent on Oracle-Maintained Types Starting with Oracle Database 12c release 2 (12.2), you must manually upgrade user tables that depend on Oracle-Maintained types. To identify tables that need to be upgraded after the database upgrade, connect AS SYSDBA and run the following query: COLUMN owner FORMAT A30 COLUMN table_name FORMAT A30 SELECT DISTINCT owner, table_name FROM dba_tab_cols WHERE data_upgraded = 'NO' ORDER BY 1,2; Execute utluptabdata.sql script either with a user account with the privileges to ALTER all of the tables dependent on Oracle-Maintained types, or with a user granted the SYSDBA system privileges that is logged in AS SYSDBA. SET SERVEROUTPUT ON @utluptabdata.sql Enabling the New Extended Data Type Capability Enabling a system to take advantage of the new extended data types requires specific upgrade actions. Oracle Database 12c introduces MAX_STRING_SIZE to control the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL. Setting MAX_STRING_SIZE = EXTENDED enables the 32767 byte limit introduced in Oracle Database 12c. You must set the COMPATIBLE initialization parameter to 12.0.0.0 or higher to be able to set MAX_STRING_SIZE = EXTENDED. Recovery Catalog Upgrade If you use a version of the recovery catalog schema that is older than that required by the RMAN client, then you must upgrade it. You can upgrade the Recovery catalog by executing the UPGRADE CATALOG command Please refer to Oracle documentation under "Upgrading the Recovery Catalog" for complete information and steps Upgrade the Time Zone File Version After Upgrading Oracle Database If the Pre-Upgrade Information Tool instructed you to upgrade the time zone files after completing the database upgrade, then use the DBMS_DST PL/SQL package to update the RDBMS DST (timezone) version. Follow the procedure in Oracle documentation under "Steps to Upgrade Time Zone File and Timestamp with Time Zone Data" and Note 1509653.1"Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST" Upgrading Statistics Tables If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by running DBMS_STATS.UPGRADE_STAT_TABLE.  In the following example, SYS is the owner of the statistics table and 'dictstattab' is the name of the statistics table. EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS', 'dictstattab'); Perform this procedure for each statistics table. Upgrade Externally Authenticated SSL Users If you are upgrading from Oracle9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), and you are using externally authenticated SSL users, then you must run the SSL external users conversion (extusrupgrade) script to upgrade those users. ORACLE_HOME/rdbms/bin/extusrupgrade --dbconnectstring hostname:port_no:sid --dbuser db_admin --dbuserpassword password -a Install Oracle Text Supplied Knowledge Bases After Upgrading Oracle Database The Oracle Text-supplied knowledge bases are part of the companion products for Oracle Database 12c and are not immediately available after an upgrade to Oracle Database 12c. Any Oracle Text features dependent on the supplied knowledge bases which were available before the upgrade do not function after the upgrade. To re-enable such features, you must install the Oracle Text supplied knowledge bases from the installation media. Update Oracle Application Express Configuration After Upgrading Oracle Database If the Oracle Database release that you upgrade includes Oracle Application Express release 3.2 or later, then you do not need to carry out additional configuration after upgrading to Oracle Database 12c. However, if Oracle Application Express is in the registry, so that Oracle Application Express is included in the upgrade, then set the open_cursors parameter to a minimum of 200. Configure Access Control Lists (ACLs) to External Network Services if you have applications that use UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages, then after upgrading Oracle Database you must configure network access control lists (ACLs) in the database before these packages can work as they did in earlier releases. Without the ACLs, your applications may fail with the error "ORA-24247: network access denied by access control list (ACL)." Enabling Oracle Database Vault After Upgrading Oracle Database Refer to the following documents for enabling Oracle Database Vault: Note 453903.1 - Enabling and Disabling Oracle Database Vault in UNIX Note 453902.1 - Enabling and Disabling Oracle Database Vault in WINDOWS Check for the SQLNET.ALLOWED_LOGON_VERSION Parameter Behavior Connections to Oracle Database from clients earlier than release 10g fail with the error ORA-28040: No matching authentication protocol. Refer to Oracle documentation for fixing the ORA-28040: No matching authentication protocol. Known issues For known issues refer to Oracle Database Upgrade Known issues - 12.2 (Doc ID 2243613.1)

ASM doesn’t start due to CRS-1714 error

Startup and check CRS and verify your disk groups
# $GRID_HOME/bin/crsctl  start crs
CRS-4123: Oracle High Availability Services has been started.

# $GRID_HOME/bin/crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4530: Communications failure contacting Cluster Synchronization Services daemon
CRS-4534: Cannot communicate with Event Manager

$ asmcmd lsdg
Connected to an idle instance.
ASMCMD-8102: no connection to Oracle ASM; command requires Oracle ASM to run

--> From GRID alert.log
[cssd(10157)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds;
Details at (:CSSNM00070:) in /u01/app/11204/grid/log/grac42/cssd/ocssd.log

$  /etc/init.d/oracleasm listdisks
--> No ASM disks configured - Installation uses udev devices!

Get your disk discovery string and verify your /dev/asm directory
$GRID_HOME/bin/gpnptool get
DiscoveryString="/dev/asm*
SPFile="+DATA/grac4/asmparameterfile/registry.253.825944079"

$  ls -l /dev/asm*
brw-rw----. 1 grid asmadmin 252, 5 Mar 18 13:42 /dev/asmdisk_OF-disk1
brw-rw----. 1 grid asmadmin 252, 6 Mar 18 13:42 /dev/asmdisk_OF-disk2
brw-rw----. 1 grid asmadmin 252, 7 Mar 18 13:42 /dev/asmdisk_OF-disk3

$ kfed read /dev/asmdisk_OF-disk1 | egrep 'dskname|grpname'
kfdhdb.dskname:       OPENFILER_DG_0000 ; 0x028: length=17
kfdhdb.grpname:            OPENFILER_DG ; 0x048: length=12
[grid@grac42 grac42]$  kfed read /dev/asmdisk_OF-disk2 | egrep 'dskname|grpname'
kfdhdb.dskname:       OPENFILER_DG_0001 ; 0x028: length=17
kfdhdb.grpname:            OPENFILER_DG ; 0x048: length=12
[grid@grac42 grac42]$ kfed read /dev/asmdisk_OF-disk3  | egrep 'dskname|grpname'
kfdhdb.dskname:       OPENFILER_DG_0002 ; 0x028: length=17
kfdhdb.grpname:            OPENFILER_DG ; 0x048: length=12

Verify  voting file location locally
# more /etc/oracle/ocr.loc
ocrconfig_loc=+OCR
local_only=false

Verify voting file location on a working instance
$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   b0e94e5d83054fe9bf58b6b98bfacd65 (/dev/asmdisk5_udev_sdf1) [OCR]
 2. ONLINE   88c2a08b4c8c4f85bf0109e0990388e4 (/dev/asmdisk6_udev_sdg1) [OCR]
 3. ONLINE   1108f9a41e814fb2bfed879ff0039dd0 (/dev/asmdisk7_udev_sdh1) [OCR]

Check ASM devices
ON Working Node
[root@grac41 rules.d]# ls /dev/asm*
/dev/asmdisk10_ssd3      /dev/asmdisk2_test       /dev/asmdisk4_udev_sde1  /dev/asmdisk8_ssd1     /dev/asmdisk_OF-disk3
/dev/asmdisk1_fra        /dev/asmdisk2_udev_sdc1  /dev/asmdisk5_udev_sdf1  /dev/asmdisk9_ssd2
/dev/asmdisk1_test       /dev/asmdisk3_test       /dev/asmdisk6_udev_sdg1  /dev/asmdisk_OF-disk1
/dev/asmdisk1_udev_sdb1  /dev/asmdisk3_udev_sdd1  /dev/asmdisk7_udev_sdh1  /dev/asmdisk_OF-disk2

Failing Node
grid@grac42 grac42]$  ls -l /dev/asm*
brw-rw----. 1 grid asmadmin 252, 5 Mar 18 13:42 /dev/asmdisk_OF-disk1
brw-rw----. 1 grid asmadmin 252, 6 Mar 18 13:42 /dev/asmdisk_OF-disk2
brw-rw----. 1 grid asmadmin 252, 7 Mar 18 13:42 /dev/asmdisk_OF-disk3

--> All disks for  Diskgroup OCR are missing from /dev/asm*

Solution : Fix udev file and verify that our OCR diskgroup can be mounted
$ udevadm control --reload-rules
$ start_udev

Verify ASM disks:
[root@grac43 rules.d]#  $GRID_HOME/bin/kfed read  /dev/asmdisk1_udev_sdf1| egrep 'dskname|grpname'
kfdhdb.dskname:                OCR_0000 ; 0x028: length=8
kfdhdb.grpname:                     OCR ; 0x048: length=3
[root@grac43 rules.d]# $GRID_HOME/bin/kfed read  /dev/asmdisk1_udev_sdg1| egrep 'dskname|grpname'
kfdhdb.dskname:                OCR_0001 ; 0x028: length=8
kfdhdb.grpname:                     OCR ; 0x048: length=3
[root@grac43 rules.d]#  $GRID_HOME/bin/kfed read  /dev/asmdisk1_udev_sdh1| egrep 'dskname|grpname'
kfdhdb.dskname:                OCR_0002 ; 0x028: length=8
kfdhdb.grpname:                     OCR ; 0x048: length=3

Restart CRS ( as root ) and verify ASM status
# $GRID_HOME/bin/crsctl  stop crs -f
# $GRID_HOME/bin/crsctl start crs

$ $GRID_HOME/bin/asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  1048576     40944    20431            10236            5097              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576    204797   173814                0          173814              0             N  FRA/
MOUNTED  NORMAL  N         512   4096  1048576      6141     5091             2047            1522              0             Y  OCR/
MOUNTED  EXTERN  N         512   4096  1048576      5739     4565                0            4565              0             N  OPENFILER_DG/
MOUNTED  NORMAL  N         512   4096  1048576      3057     1995             1019             488              0             N  SSD/

Manually add an ASM instance after Oracle Restart installation failed

Manually add an ASM instance after Oracle Restart installation  failed
Check resources - you may need to drop the ASM resource if already available
[root@orars4 Desktop]# crs
*****  Local Resources: *****
Rescource NAME                 TARGET     STATE           SERVER       STATE_DETAILS                     
-------------------------      ---------- ----------      ------------ ------------------                 
ora.LISTENER.lsnr              ONLINE     ONLINE          orars4         
ora.ons                        OFFLINE    OFFLINE         orars4         
*****  Cluster Resources: *****
Resource NAME               INST   TARGET       STATE        SERVER          STATE_DETAILS
--------------------------- ----   ------------ ------------ --------------- -----------------------------------------
ora.cssd                       1   OFFLINE      OFFLINE                       
ora.diskmon                    1   OFFLINE      OFFLINE                       
ora.evmd                       1   ONLINE       ONLINE       orars4 

--> Run GUI tool asmca and create DG

Check resourses again asm resoruce is add and up and running
[root@orars4 Desktop]# crs
*****  Local Resources: *****
Rescource NAME                 TARGET     STATE           SERVER       STATE_DETAILS                     
-------------------------      ---------- ----------      ------------ ------------------                 
ora.DATA.dg                    ONLINE     ONLINE          orars4         
ora.LISTENER.lsnr              ONLINE     ONLINE          orars4         
ora.asm                        ONLINE     ONLINE          orars4       Started 
ora.ons                        OFFLINE    OFFLINE         orars4         
*****  Cluster Resources: *****
Resource NAME               INST   TARGET       STATE        SERVER          STATE_DETAILS
--------------------------- ----   ------------ ------------ --------------- -----------------------------------------
ora.cssd                       1   ONLINE       ONLINE       orars4           
ora.diskmon                    1   OFFLINE      OFFLINE                       
ora.evmd                       1   ONLINE       ONLINE       orars4

Asm commands

Check diskgroup including the dismounted DGs
$  asmcmd lsdg -g  --discovery
Inst_ID  State       Type  Rebal  Sector  Block  AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
      3  DISMOUNTED        N           0   4096   0         0        0                0               0              0             N  ACFS/
      2  DISMOUNTED        N           0   4096   0         0        0                0               0              0             N  ACFS/
      1  DISMOUNTED        N           0   4096   0         0        0                0               0              0             N  ACFS/

Check ASM disk status
$ asmcmd lsdsk -k -g
Inst_ID  Total_MB  Free_MB  OS_MB  Name       Failgroup  Failgroup_Type  Library  Label  UDID  Product  Redund   Path
      3     10236     4316  10236  DATA_0000  DATA_0000  REGULAR         System                         UNKNOWN  /dev/asm_data_10g_disk1
      1     10236     4316  10236  DATA_0000  DATA_0000  REGULAR         System                         UNKNOWN  /dev/asm_data_10g_disk1
      2     10236     4316  10236  DATA_0000  DATA_0000  REGULAR         System                         UNKNOWN  /dev/asm_data_10g_disk1
..

Check ASM disk status for candidate disk
$  asmcmd lsdsk -k -g --candidate
Inst_ID  Total_MB  Free_MB  OS_MB  Name       Failgroup  Failgroup_Type  Library  Label  UDID  Product  Redund   Path
      3         0        0   1019                        REGULAR         System                         UNKNOWN  /dev/asm_test_1G_disk1
      2         0        0   1019                        REGULAR         System                         UNKNOWN  /dev/asm_test_1G_disk1
      1         0        0   1019                        REGULAR         System                         UNKNOWN  /dev/asm_test_1G_disk1
      3         0        0   1019                        REGULAR         System                         UNKNOWN  /dev/asm_test_1G_disk2
      2         0        0   1019                        REGULAR         System                         UNKNOWN  /dev/asm_test_1G_disk2
      1         0        0   1019                        REGULAR         System                         UNKNOWN  /dev/asm_test_1G_disk2

Clients accessing ASM instance:
$ asmcmd lsct
DB_Name  Status     Software_Version  Compatible_version  Instance_Name  Disk_Group
+ASM     CONNECTED        11.2.0.3.0          11.2.0.3.0  +ASM1          OCR
GRACE2   CONNECTED        11.2.0.3.0          11.2.0.0.0  GRACE21        DATA

Files opened by ASM instance:
$ asmcmd lsof
DB_Name  Instance_Name  Path
+ASM     +ASM1          +ocr.255.4294967295
GRACE2   GRACE21        +data/grace2/controlfile/current.260.822058257
GRACE2   GRACE21        +data/grace2/datafile/example.264.822058263
GRACE2   GRACE21        +data/grace2/datafile/sysaux.257.822058183
GRACE2   GRACE21        +data/grace2/datafile/system.256.822058183
GRACE2   GRACE21        +data/grace2/datafile/undotbs1.258.822058183
GRACE2   GRACE21        +data/grace2/datafile/undotbs2.265.822058387
GRACE2   GRACE21        +data/grace2/datafile/users.259.822058183
GRACE2   GRACE21        +data/grace2/onlinelog/group_1.261.822058257
GRACE2   GRACE21        +data/grace2/onlinelog/group_2.262.822058259
GRACE2   GRACE21        +data/grace2/onlinelog/group_3.266.822058427
GRACE2   GRACE21        +data/grace2/onlinelog/group_4.267.822058427
GRACE2   GRACE21        +data/grace2/tempfile/temp.263.822058261


$ asmcmd lsdg -g
Inst_ID  State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
      1  MOUNTED  NORMAL  N         512   4096  1048576      6141     5089             2047            1521              0             Y  OCR/
      3  MOUNTED  NORMAL  N         512   4096  1048576      6141     5089             2047            1521              0             Y  OCR/
      2  MOUNTED  NORMAL  N         512   4096  1048576      6141     5089             2047            1521              0             Y  OCR/
      3  MOUNTED  EXTERN  N         512   4096  1048576      5739     4565                0            4565              0             N  OPENFILER_DG/
      2  MOUNTED  EXTERN  N         512   4096  1048576      5739     4565                0            4565              0             N  OPENFILER_DG/
      1  MOUNTED  NORMAL  N         512   4096  1048576      3057     1995             1019             488              0             N  SSD/
      3  MOUNTED  NORMAL  N         512   4096  1048576      3057     1995             1019             488              0             N  SSD/
      2  MOUNTED  NORMAL  N         512   4096  1048576      3057     1995             1019             488              0             N  SSD/
--> OPENFILER diskgroup not mount on instance 1

# asmcmd lsdsk -k -g
Inst_ID  Total_MB  Free_MB   OS_MB  Name               Failgroup          Failgroup_Type  Library  Label  UDID  Product  Redund   Path
      1    204797   173589  204797  FRA_0000           FRA_0000           REGULAR         System                         UNKNOWN  /dev/asmdisk1_fra
      2    204797   173589  204797  FRA_0000           FRA_0000           REGULAR         System                         UNKNOWN  /dev/asmdisk1_fra
      3    204797   173589  204797  FRA_0000           FRA_0000           REGULAR         System                         UNKNOWN  /dev/asmdisk1_fra
...
      2      1913     1521    1913  OPENFILER_DG_0000  OPENFILER_DG_0000  REGULAR         System                         UNKNOWN  /dev/asmdisk_OF-disk1
      3      1913     1521    1913  OPENFILER_DG_0000  OPENFILER_DG_0000  REGULAR         System                         UNKNOWN  /dev/asmdisk_OF-disk1
      2      1913     1522    1913  OPENFILER_DG_0001  OPENFILER_DG_0001  REGULAR         System                         UNKNOWN  /dev/asmdisk_OF-disk2
      3      1913     1522    1913  OPENFILER_DG_0001  OPENFILER_DG_0001  REGULAR         System                         UNKNOWN  /dev/asmdisk_OF-disk2
      2      1913     1522    1913  OPENFILER_DG_0002  OPENFILER_DG_0002  REGULAR         System                         UNKNOWN  /dev/asmdisk_OF-disk3
      3      1913     1522    1913  OPENFILER_DG_0002  OPENFILER_DG_0002  REGULAR         System                         UNKNOWN  /dev/asmdisk_OF-disk3
-->  Disks OPENFILER_DG_0000, OPENFILER_DG_0001, OPENFILER_DG_0002 not visible on Instance 1
     Action: check UDEV rules on instance 1 and verify diskheader with kfed



Dismount a diskgroup on a local instance
ASMCMD> lsdg -g OPENFILER_DG
Inst_ID  State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
      1  MOUNTED  EXTERN  N         512   4096  1048576      5739     4565                0            4565              0             N  OPENFILER_DG/
      3  MOUNTED  EXTERN  N         512   4096  1048576      5739     4565                0            4565              0             N  OPENFILER_DG/
      2  MOUNTED  EXTERN  N         512   4096  1048576      5739     4565                0            4565              0             N  OPENFILER_DG/
--> Diskgroup mounted on all instances

ASMCMD> umount  OPENFILER_DG
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "OPENFILER_DG" precludes its dismount (DBD ERROR: OCIStmtExecute)

ASMCMD> umount -f OPENFILER_DG
ASMCMD>  lsdg -g OPENFILER_DG
Inst_ID  State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
      3  MOUNTED  EXTERN  N         512   4096  1048576      5739     4565                0            4565              0             N  OPENFILER_DG/
      2  MOUNTED  EXTERN  N         512   4096  1048576      5739     4565                0            4565              0             N  OPENFILER_DG/
--> Diskgroup OPENFILER dismounted on instance 1