Oracle database 12c/13c/18/19 new features
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.
Read more of this content when you subscribe today.
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.
Read more of this content when you subscribe today.
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
Read more of this content when you subscribe today.
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.
From Oracle Database 18c, we can install below Oracle software using RPM packages.
After software is installed, Oracle database can be created by:
Restrictions and Guidelines for RPM-Based Installations.
rpm -Uvh is not supported. For Oracle Database upgrades, follow the regular upgrade process.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.
1)Log in as root.
2)Download and install the Oracle Preinstallation RPM:
# 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# 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.rpmAfter successful installation, you can delete the downloaded RPM file:
# rm oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm3)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!
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]#
[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 ways to find master node in Oracle RAC environment.
$ 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
$ 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
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
Make DBA Life Easy 