Data Guard Standby Automatic Password file Synchronization

It makes DBA life easy by using new feature of Data Guard Standby Automatic Password file Synchronization

For 12cR1 and earlier versions, when setup DataGuard( DG), if there is a change in password file of Primary database , then the password file needs to be copied from Primary database to standby database server. Otherwise the DG will get errors.

Subscribe to get access

Read more of this content when you subscribe today.

How to Rotate and Purge Listener Logs in 19c

A 19c new feature really makes DBA Life Easy.

Before 19c, there are very limited options to purge listener logs, which could be growing day by day up to a couple of GB size. DBAs have to write their own customised scripts to do this job.

This post demonstrates how to take advantage of 19c a new feature to rotate and purge listerer logs automatically, without a line of script necessary.

Subscribe to get access

Read more of this content when you subscribe today.

Upgrade Oracle Database by Using AutoUpgrade Utility

Contents

Introduction

This post demonstrates how to upgrade 18c database to 19c by using Oracle upgrade utility tool – AutoUpgrade.

Environment :

Primary Database Host / Database : oemnode1 / OEMREP

Standby Database Host / Database:   stbnode1 / STBOEMREP

Subscribe to get access

Read more of this content when you subscribe today.

Final Checks

DGMGRL> show configuration;

Configuration - dg_oemrep

  Protection Mode: MaxPerformance
  Members:
  oemrep    - Primary database
    stboemrep - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 49 seconds ago)

DGMGRL> show database stboemrep

Database - stboemrep

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 30.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    STBOEMREP

Database Status:
SUCCESS
[oracle@oemnode1 dbs]$ $ORACLE_HOME/OPatch/opatch lspatches

30894985;OCW RELEASE UPDATE 19.7.0.0.0 (30894985)
30869156;Database Release Update : 19.7.0.0.200414 (30869156)
30805684;OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)

OPatch succeeded.
SQL> set pagesize 200
SQL> set linesize 300
SQL>  select CON_ID,
        TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,
        PATCH_ID,
        PATCH_TYPE,
        ACTION,
        DESCRIPTION,
        SOURCE_VERSION,
        TARGET_VERSION
   from CDB_REGISTRY_SQLPATCH
  order by CON_ID, action_time, patch_id;  

    CON_ID ACTION_TIM   PATCH_ID PATCH_TYPE ACTION   DESCRIPTION                                           SOURCE_VERSION  TARGET_VERSION
---------- ---------- ---------- ---------- -------- ----------------------------------------------------- --------------- ---------------
         1 2020-05-31   30805684 INTERIM    APPLY    OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)       19.1.0.0.0      19.1.0.0.0
         1 2020-05-31   30869156 RU         APPLY    Database Release Update : 19.7.0.0.200414 (30869156)  19.1.0.0.0      19.7.0.0.0
         3 2020-05-31   30805684 INTERIM    APPLY    OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)       19.1.0.0.0      19.1.0.0.0
         3 2020-05-31   30869156 RU         APPLY    Database Release Update : 19.7.0.0.200414 (30869156)  19.1.0.0.0      19.7.0.0.0
 SQL> select comp_name, status,version,VERSION_FULL 
        from dba_registry

COMP_NAME                         STATUS     VERSION   VERSION_FULL
--------------------------------- ---------  --------- ------------
Oracle Database Catalog Views     VALID      19.0.0.0.0 19.7.0.0.0
Oracle Database Packages and TypesVALID      19.0.0.0.0 19.7.0.0.0
Oracle Real Application Clusters  OPTION OFF 19.0.0.0.0 19.7.0.0.0
JServer JAVA Virtual Machine      VALID      19.0.0.0.0 19.7.0.0.0
Oracle XDK                        VALID      19.0.0.0.0 19.7.0.0.0
Oracle Database Java Packages     VALID      19.0.0.0.0 19.7.0.0.0
OLAP Analytic Workspace           VALID      19.0.0.0.0 19.7.0.0.0
Oracle XML Database               VALID      19.0.0.0.0 19.7.0.0.0
Oracle Workspace Manager          VALID      19.0.0.0.0 19.7.0.0.0
Oracle Text                       VALID      19.0.0.0.0 19.7.0.0.0
Oracle Multimedia                 VALID      19.0.0.0.0 19.7.0.0.0
Spatial                           VALID      19.0.0.0.0 19.7.0.0.0
Oracle OLAP API                   VALID      19.0.0.0.0 19.7.0.0.0
Oracle Label Security             VALID      19.0.0.0.0 19.7.0.0.0
Oracle Database Vault             VALID      19.0.0.0.0 19.7.0.0.0

15 rows selected.
SQL> show con_name;

CON_NAME
----------------
EMPDBREPOS

SQL> select count(*) from dba_objects where status!='VALID';

  COUNT(*)
----------
         0

SQL> connect / as sysdba
Connected.
SQL>  select count(*) from dba_objects where status!='VALID';

  COUNT(*)
----------
         0

Check TimeZone version has been upgraded to version 32 from version 31 in 18c.

SQL> SELECT * FROM v$timezone_file;

FILENAME             VERSION     CON_ID
-------------------- ------- ----------
timezlrg_32.dat           32          0


SQL> SELECT tz_version FROM registry$database;

TZ_VERSION
----------
        32

SQL> SELECT property_name, property_value
     FROM   database_properties
     WHERE  property_name LIKE 'DST_%'
  ORDER BY property_name

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         32
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL> SELECT DBMS_DST.get_latest_timezone_version  from dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
                         32

Drop GRP ( Guarantee Restore Point )

SQL> select name from v$restore_point;

NAME
--------------------------------------------
AUTOUPGRADE_221145114461854_OEMREP

SQL> drop restore point AUTOUPGRADE_221145114461854_OEMREP;

Restore point dropped.

Install Oracle Database 18c Using RPM Packages

From Oracle Database 18c, we can install below Oracle software using RPM packages.

  • a single-instance Oracle Database.
  • an Oracle Database Instant Client software.

After software is installed, Oracle database can be created by:

  • Database Configuration Assistant (Oracle DBCA).
  • /etc/init.d/oracledb_ORCLCDB-18c service configuration script.

Restrictions and Guidelines for RPM-Based Installations.

  • Patching is still to use the OPatch utility.
  • Standard Edition 2 has no RPM-based Oracle Database installation available for.
  • Upgrades using rpm -Uvh is not supported. For Oracle Database upgrades, follow the regular upgrade process.
  • An RPM-based installation supports the installation of multiple Oracle Database software versions into different Oracle homes on the same machine.

Installing Oracle Database RPM using YUM

1)Log in as root.
2)If you are using Oracle Linux and have subscribed to Unbreakable Linux Network (ULN), you can install Oracle Database via a single yum command.

# yum -y install oracle-database-ee-18c

This command downloads and installs the Oracle Preinstallation RPM and the Oracle Database RPM packages.

Installing Oracle Database RPM Manually

1)Log in as root.
2)Download and install the Oracle Preinstallation RPM:

  1. On Oracle Linux.
    # yum -y install oracle-database-preinstall-18c 
    [root@oemnode1 ~]# rpm -qa | grep oracle-database-preinstall-18 oracle-database-preinstall-18c-1.0-1.el7.x86_64
  2. On Red Hat Enterprise Linux.
    # curl -o oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm 

    # yum -y localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

    After successful installation, you can delete the downloaded RPM file:

    # rm oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

3)Download oracle-database-ee-18c-1.0-1.x86_64.rpm to the /tmp directory.

4) Install the database software using the yum localinstall command.

#chown oracle:oinstll /opt/oracle 
#cd /tmp
[root@oemnode1 tmp]# yum -y localinstall oracle-database-ee-18c-1.0-1.x86_64.rpm Loaded plugins: ulninfo Examining oracle-database-ee-18c-1.0-1.x86_64.rpm: oracle-database-ee-18c-1.0-1.x86_64 Marking oracle-database-ee-18c-1.0-1.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package oracle-database-ee-18c.x86_64 0:1.0-1 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================================================================================================================================================= Package Arch Version Repository Size ======================================================================================================== Installing: oracle-database-ee-18c x86_64 1.0-1 /oracle-database-ee-18c-1.0-1.x86_64 7.8 G Transaction Summary ======================================================================================================== Install 1 Package Total size: 7.8 G Installed size: 7.8 G Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : oracle-database-ee-18c-1.0-1.x86_64 1/1 [INFO] Executing post installation scripts... [INFO] Oracle home installed successfully and ready to be configured. To configure a sample Oracle Database you can execute the following service configuration script as root: /etc/init.d/oracledb_ORCLCDB-18c configure Verifying : oracle-database-ee-18c-1.0-1.x86_64 1/1 Installed: oracle-database-ee-18c.x86_64 0:1.0-1 Complete!

Creating and Configuring an Oracle Database

  1. Tried to change ORACLE_SID, and PDB_NAME into whatever you  want in  configuration  file  “/etc/init.d/oracledb_ORCLCDB-18c”. But it did not work when creating database in the next step, so we have to use the default ones.

2. To create and configure a sample Oracle Database instance, run the following service configuration script:

[root@oemnode1 init.d]# /etc/init.d/oracledb_ORCLCDB-18c configure
Configuring Oracle Database ORCLCDB.
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/ORCLCDB.
Database Information:
Global Database Name:ORCLCDB
System Identifier(SID):ORCLCDB
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" 
for further details.

Database configuration completed successfully. The passwords were auto 
generated, you must change them by connecting to the database using 
'sqlplus / as sysdba' as the oracle user.
[root@oemnode1 init.d]#

Removing RPM-based Oracle Database Installation

1)Drop instance, and listener configurations.

2)Run following command to remove the database installation by RPM.

[root@oemnode1 ContentsXML]# rpm -qa | grep 18c
oracle-database-preinstall-18c-1.0-1.el7.x86_64
oracle-database-ee-18c-1.0-1.x86_64
[root@oemnode1 ~]#  /etc/rc.d/init.d/oracledb_ORCLCDB-18c  -help
Usage: /etc/rc.d/init.d/oracledb_ORCLCDB-18c {start|stop|restart|configure}

[root@oemnode1 ~]#  /etc/rc.d/init.d/oracledb_ORCLCDB-18c stop
Shutting down Oracle Database instance ORCLCDB.
Oracle Database instance ORCLCDB shut down.
Stopping Oracle Net Listener.
Oracle Net Listener stopped.

[root@oemnode1 oracle]# yum -y remove oracle-database-ee-18c
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-ee-18c.x86_64 0:1.0-1 will be erased
--> Finished Dependency Resolution
Dependencies Resolved
=========================================================================
 Package                                                       Arch                                          Version                                      Repository                                                                    Size
=========================================================================
 oracle-database-ee-18c                                        x86_64                                        1.0-1                                        @/oracle-database-ee-18c-1.0-1.x86_64                                        7.8 G

Transaction Summary
==========================================================================
Remove  1 Package

Installed size: 7.8 G
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
[SEVERE] Listeners or databases are present in the system, remove them before proceeding with the deinstallation
error: %preun(oracle-database-ee-18c-1.0-1.x86_64) scriptlet failed, exit status 1
Error in PREUN scriptlet in rpm package oracle-database-ee-18c-1.0-1.x86_64
  Verifying  : oracle-database-ee-18c-1.0-1.x86_64                                                                                                                                                                                     1/1

Failed:
  oracle-database-ee-18c.x86_64 0:1.0-1

Complete!
[root@oemnode1 oracle]

If get above errors, please check :

a) There is no ORACLE_HOME entry in /etc/oratab.

b) There is no files under $ORACLE_HOME/dbs.

c) There is no files under $ORACLE_HOME/network/admin.

[root@oemnode1 ORCLCDB]# yum -y remove oracle-database-ee-18c
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-ee-18c.x86_64 0:1.0-1 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================================================================================
Removing:
oracle-database-ee-18c x86_64 1.0-1 @/oracle-database-ee-18c-1.0-1.x86_64 7.8 G

Transaction Summary
=============================================================================================================================================================================================================================================
Remove 1 Package

Installed size: 7.8 G
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Erasing : oracle-database-ee-18c-1.0-1.x86_64 1/1
warning: file /opt/oracle/product/18c/dbhome_1/network/admin/shrept.lst: remove failed: No such file or directory
warning: file /opt/oracle/product/18c/dbhome_1/network/admin/samples/tnsnames.ora: remove failed: No such file or directory
warning: file /opt/oracle/product/18c/dbhome_1/network/admin/samples/sqlnet.ora: remove failed: No such file or directory
warning: file /opt/oracle/product/18c/dbhome_1/network/admin/samples/listener.ora: remove failed: No such file or directory
warning: file /opt/oracle/product/18c/dbhome_1/network/admin/samples: remove failed: No such file or directory
warning: file /opt/oracle/product/18c/dbhome_1/dbs/init.ora: remove failed: No such file or directory
warning: file /etc/init.d/oracledb_ORCLCDB-18c: remove failed: No such file or directory
Verifying : oracle-database-ee-18c-1.0-1.x86_64 1/1

Removed:
oracle-database-ee-18c.x86_64 0:1.0-1

Complete!

The Master Node of RAC Cluster

The ways to find master node in Oracle RAC environment.

1) Check from ocssd.log
$ cat $GRID_HOME/log/racnode1/cssd/ocssd.l* |grep 'master node' |tail -1 2017-07-18 10:09:21.431: [ CSSD][1102125408]clssgmCMReconfig: reconfiguration successful, incarnation 217002855 with 2 nodes, local node number 2, master node number 2
2) Check from crsd.log
$ cat $GRID_HOME/log/racnode1/crsd/crsd.l* |grep 'OCR MASTER' | tail -1
2017-07-18 10:09:10.414: [ OCRMAS][1101056352]th_master:13: I AM THE NEW OCR MASTER at incar 18. Node Number 2
3) Check who ( MASTER ) does OCR backup
grid@racnode1:$ ocrconfig -showbackup

racnode2 2017/07/17 13:51:21 /u01/app/11.2.0.4/grid/cdata/RACNODE-CLUSTER/backup00.ocr
racnode2 2017/07/17 09:51:18 /u01/app/11.2.0.4/grid/cdata/RACNODE-CLUSTER/backup01.ocr
racnode2 2017/07/17 05:51:16 /u01/app/11.2.0.4/grid/cdata/RACNODE-CLUSTER/backup02.ocr
racnode2 2017/07/16 05:51:00 /u01/app/11.2.0.4/grid/cdata/RACNODE-CLUSTER/day.ocr
racnode2 2017/07/06 01:48:12 /u01/app/11.2.0.4/grid/cdata/RACNODE-CLUSTER/week.ocr
PROT-25: Manual backups for the Oracle Cluster Registry are not available