Install 18c Autonomous Database

Autonomous Database is self-driving, self-securing, and self-repairing. An Autonomous Database eliminates complexity, human error, and manual management, helping to ensure higher reliability, security, and more operational efficiency at the lowest cost.

Self-Driving: Oracle Autonomous Database automates all database and infrastructure management, monitoring, and tuning. This reduces your full-stack admin costs, although admins will still be needed for tasks such as managing how applications connect to the data warehouse and how developers use the in-database features and functions without their application code.
Self-Securing: Oracle Autonomous Database protects you from both external attacks and malicious internal users, which means you can stop worrying about cyberattacks on unpatched or unencrypted databases.
Self-Repairing: Oracle Autonomous Database protects from all downtime, including unplanned maintenance, with fewer than 2.5 minutes of downtime a month, including patching.

There’s now full, end-to-end automation for Provisioning, Security, Updates, Availability, Performance, Change management, Errors.

 Oracle 18c preinstall RPM on RedHat RHEL
The Linux prerequisites for Oracle Database are all documented but using the pre-install rpm makes all things easier. Before 18c, this was easy on Oracle Enterprise Linux (OEL) but not so easy on RedHat (RHEL) where the .rpm had many dependencies on OEL and UEK.
Now that 18c is there to download, there’s also the 18c preinstall rpm and the good news is that it can be run also on RHEL without modification.

On the other hand, you may not have noticed that it no longer requires Oracle Linux specific RPMs. It can now be used on RHEL and all its derivatives.

Downloaded the RPM from the OEL7 repository:

[root@instance-20180803-1152 opc]# 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

% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 18244 100 18244 0 0 63849 0 --:--:-- --:--:-- --:--:-- 63790
then run the installation:

[root@instance-20180803-1152 opc]# yum -y localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_ 64.rpm
It installs automatically all dependencies:

Installed:
oracle-database-preinstall-18c.x86_64 0:1.0-1.el7
Dependency Installed:
compat-libcap1.x86_64 0:1.10-7.el7 compat-libstdc++-33.x86_64 0:3.2.3-72.el7 glibc-devel.x86_64 0:2.17-222.el7 glibc-headers.x86_64 0:2.17-222.el7
gssproxy.x86_64 0:0.7.0-17.el7 kernel-headers.x86_64 0:3.10.0-862.9.1.el7 keyutils.x86_64 0:1.5.8-3.el7 ksh.x86_64 0:20120801-137.el7
libICE.x86_64 0:1.0.9-9.el7 libSM.x86_64 0:1.2.2-2.el7 libXext.x86_64 0:1.3.3-3.el7 libXi.x86_64 0:1.7.9-1.el7
libXinerama.x86_64 0:1.1.3-2.1.el7 libXmu.x86_64 0:1.1.2-2.el7 libXrandr.x86_64 0:1.5.1-2.el7 libXrender.x86_64 0:0.9.10-1.el7
libXt.x86_64 0:1.1.5-3.el7 libXtst.x86_64 0:1.2.3-1.el7 libXv.x86_64 0:1.0.11-1.el7 libXxf86dga.x86_64 0:1.1.4-2.1.el7
libXxf86misc.x86_64 0:1.0.3-7.1.el7 libXxf86vm.x86_64 0:1.1.4-1.el7 libaio-devel.x86_64 0:0.3.109-13.el7 libbasicobjects.x86_64 0:0.1.1-29.el7
libcollection.x86_64 0:0.7.0-29.el7 libdmx.x86_64 0:1.1.3-3.el7 libevent.x86_64 0:2.0.21-4.el7 libini_config.x86_64 0:1.3.1-29.el7
libnfsidmap.x86_64 0:0.25-19.el7 libpath_utils.x86_64 0:0.2.1-29.el7 libref_array.x86_64 0:0.1.5-29.el7 libstdc++-devel.x86_64 0:4.8.5-28.el7_5.1
libverto-libevent.x86_64 0:0.2.5-4.el7 nfs-utils.x86_64 1:1.3.0-0.54.el7 psmisc.x86_64 0:22.20-15.el7 xorg-x11-utils.x86_64 0:7.5-22.el7
xorg-x11-xauth.x86_64 1:1.0.9-1.el7

Note that the limits are stored in limits.d which has priority over limits.conf:

[root@instance-20180803-1152 opc]# cat /etc/security/limits.d/oracle-database-preinstall-18c.conf

# oracle-database-preinstall-18c setting for nofile soft limit is 1024
oracle soft nofile 1024
# oracle-database-preinstall-18c setting for nofile hard limit is 65536
oracle hard nofile 65536
# oracle-database-preinstall-18c setting for nproc soft limit is 16384
# refer orabug15971421 for more info.
oracle soft nproc 16384
# oracle-database-preinstall-18c setting for nproc hard limit is 16384
oracle hard nproc 16384
# oracle-database-preinstall-18c setting for stack soft limit is 10240KB
oracle soft stack 10240
# oracle-database-preinstall-18c setting for stack hard limit is 32768KB
oracle hard stack 32768
# oracle-database-preinstall-18c setting for memlock hard limit is maximum of 128GB on x86_64 or 3GB on x86 OR 90 % of RAM
oracle hard memlock 134217728
# oracle-database-preinstall-18c setting for memlock soft limit is maximum of 128GB on x86_64 or 3GB on x86 OR 90% of RAM
oracle soft memlock 134217728

Note that memlock is set to 128GB here but can be higher on machines with huge RAM (up to 90% of RAM)
And for information, here is what is set in /etc/sysctl.conf:

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

Besides that, the preinstall rpm disables NUMA and transparent huge pages (as boot options in GRUB). It creates the oracle user (id 54321 and belonging to groups oinstall,dba,oper,backupdba,dgdba,kmdba,racdba)

Ref: https://docs.oracle.com/en/database/oracle/oracle-database/18/cwlin/about-the-oracle-preinstallation-rpm.html#GUID-C15A642B-534D-4E4A-BDE8-6DC7772AA9C8
18c runInstaller -silent

18c runInstaller -silent
You find two different ‘runInstaller’ under an Oracle Home. The old one, the Oracle Universal Installer, in $ORACLE_HOME/oui/bin. And the new one, in $ORACLE_HOME directly. They have the same name but are completely different. The old one was used to install an Oracle Home from the installation media. But in 18c you don’t use it. It has been used by Oracle to build the Oracle Home image. Then you download and unzip directly your Oracle Home. You have only to configure it and re-link the binaries. And this is done by the new runInstaller which is at the root of the Oracle Home. Actually, it is just a shell script that runs the Perl dbSetup.pl to setup the Oracle Database software. In my opinion, it would be better to have it called dbSetup.sh rather than rename it to runInstaller, especially given that the same thing for Grid Infrastructure is called GridSetup.sh since 12cR2. The Perl script finally runs the Java GUI. It can also be run in command line, aka silent mode, which is the goal of this post. The command line arguments are similar, but not the same as in the old runInstaller.
Prerequisites
You may want to run the prerequisites only to check if your system is ready for the installation. Here is how to do so in command line:
# $ORACLE_HOME/runInstaller -silent -executePrereqs -responseFile $ORACLE_HOME/inventory/response/db_install.rsp

Launching Oracle Database Setup Wizard...

[FATAL] [INS-13013] Target environment does not meet some mandatory requirements.
CAUSE: Some of the mandatory prerequisites are not met. See logs for details. /u00/app/oraInventory/logs/InstallActions2018-08-11_06-07-14PM/installActions2018-08-11_06-07-14PM.log
ACTION: Identify the list of failed prerequisite checks from the log: /u00/app/oraInventory/logs/InstallActions2018-08-11_06-07-14PM/installActions2018-08-11_06-07-14PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually

From there we can check the log about the tests that have failed, such as in the following example:
INFO: [Aug 11, 2018 6:08:21 PM] Physical Memory: This is a prerequisite condition to test whether the system has at least 8GB (8388608.0KB) of total physical memory.
INFO: [Aug 11, 2018 6:08:21 PM] Severity:IGNORABLE
INFO: [Aug 11, 2018 6:08:21 PM] OverallStatus:VERIFICATION_FAILED
INFO: [Aug 11, 2018 6:08:21 PM] *********************************************
INFO: [Aug 11, 2018 6:08:21 PM] Run Level: This is a prerequisite condition to test whether the system is running with proper run level.
INFO: [Aug 11, 2018 6:08:21 PM] Severity:CRITICAL
INFO: [Aug 11, 2018 6:08:21 PM] OverallStatus:VERIFICATION_FAILED
INFO: [Aug 11, 2018 6:08:21 PM] *********************************************
INFO: [Aug 11, 2018 6:08:21 PM] OS Kernel Version: This is a prerequisite condition to test whether the system kernel version is at least "2.6.39-400.211.1".
INFO: [Aug 11, 2018 6:08:21 PM] Severity:CRITICAL
INFO: [Aug 11, 2018 6:08:21 PM] OverallStatus:VERIFICATION_FAILED
Software Install
You can pass all parameters in command line (‘runInstaller -silent -help’ to see all possibilities), but in all cases you need a response file. Then I put everything I need in the response file. There’s no mention of the ORACLE_HOME because you already unzipped it at the right place. The most important is the edition which seems to accept [EE, SEONE, SE2, HP, XP, PE]. I didn’t try it but Standard Edition One is for versions <= 12.1.0.1 by the way.
cd $ORACLE_HOME

cat > db18EE.rsp <<END
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v18.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u00/app/oraInventory
ORACLE_BASE=/u00/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backupdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=dba
END
There is no need for the oracle.install.db.config variables because I’ll install the software only without creating a database.
Here is how to run the dbSetup. You can use ‘-ignorePrereqFailure’ to ignore the prerequisites if you want to install to a host where some prerequisites fail:

./runInstaller -silent -noconfig -ignorePrereqFailure -responseFile ./db18EE.rsp

The log of the installation goes into the oraInventory/logs and, as usual, you have to run the root.sh
As a root user, execute the following script(s):
1. /u00/app/oracle/product/18SE/root.sh

Execute /u00/app/oracle/product/18SE/root.sh on the following nodes: ed-olraclin1
This new runInstaller can also apply one-off patches with -applyOneOffs mentioning the patch locations. You can also build an Oracle Home image that you customize, with -createGoldImage -destinationLocation and even mention some files or path to exclude to make it smaller: -exclFiles

If you have any queries regarding this article then email me and I will respond to you. samiappsdba@gmail.com

Migrate On-premises Database to Cloud using Data Pump

Below are step by step procedure to migrate an on-premises source database, tablespace, schema, or table to an Oracle Database Cloud Service database deployment using the conventional export and import functionality of the Data Pump feature of Oracle Database. This method can be used regardless of the endian format and database character set of the on-premises database.
To accomplish the migration, perform the following steps:

1. On the on-premises database host, invoke Data Pump Export (expdp) and export the on-premises database.
2. Create a new Oracle Database Cloud Service.
3. Connect to the Oracle Database Cloud Service compute node and then use a secure copy utility to transfer the dump file to the Oracle Database Cloud Service compute node.
4. On the Oracle Database Cloud Service compute node, invoke Data Pump Import (impdp) and import the data into the database.
5. After verifying that the data has been imported successfully, delete the dump file.

The following sections show an example of the entire process. The example illustrates a schema export and import. However, the same procedure applies for exporting and importing a full database, a tablespace, or a table. In this example, the on-premises database is on a Linux host.

Export the On-Premises Database

Perform the following steps on the on-premises database host to export the schemas:

1. On the on-premises database host, create an operating system directory to use for the on-premises database export files:
   
   

   [oracle@cloud ~]$ mkdir -p /u01/app/dbpump
   

2. On the on-premises database host, invoke SQL*Plus and log in to the on-premises database as the SYS user:
   
   

   [oracle@cloud ~]$ sqlplus sys@PDB_PREM as sysdba
   SQL*Plus: Release 12.1.0.2.0 Production on Sun May 21 16:05:26 2017
   Copyright (c) 1982, 2014, Oracle. All rights reserved.
   Enter Password:
   Connected to:
   Oracle Database12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   With the partitioning, OLAP, Advanced Analytics and Real Application Testing options
   

3. Create a directory object in the on-premises database to reference the operating system directory:
   
   

   SQL> create directory prem_to_cloud as '/u01/app/dpdump';
   Directory created
   

4. Exit from SQL*Plus.
5. On the on-premises database host, invoke the Data Pump Export utility as the SYSTEM user or as another user with the DATAPUMP_EXP_FULL_DATABASE role and export the on-premises schemas. Provide the password for the user when prompted.
   
   

   [oracle@cloud ]$ expdp system@PDB_PREM full=y directory=prem_to_cloud
   Export: Release 12.1.0.2.0 - Production on Sun May 21 16:05:26 2017
   Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
   Password:
   Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
   Production
   With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
   

Create a New Instance of Oracle Database Cloud Service

1. Log in to your Oracle Cloud services account, go to the Oracle Database Cloud Service page, and create a new service:
   - For Service Name enter Cloud-Mig.
   - From the Service Level list, select Oracle Database Cloud Service.
   - From the Metering Frequency list, select whatever frequency is appropriate for your environment.
   - From the Software Release list, select Oracle Database 12c Release 1.
   - From the Software Edition list, select Enterprise Edition.
   - From the Database Type list, select Single Instance.
     Then click Next to continue.

Figure1. Creating a new service (Cloud-Mig)

2. In the Service Details screen, do the following:

- For DB Name (SID), enter MIGORCL.
- Set an administrative password of your choice and confirm the password (this will be your sys password).
- For Usable Database Storage (GB), enter 25.
- From the Compute Shape list, select OC3 -1 OCPU, 7.5 GB RAM (this is the bare minimum required).
- For SSH Public Key, enter rsa-key-20170111.pub.

Then click Next to continue.

Figure 2. Specifying the service details


3. Finally, review the configuration and click Create to create your cloud database.

Figure 3. Creating the cloud database instance


  After a few minutes, the cloud database instance has been created successfully.

Figure 4. The cloud database has been created


4. Click the service name (Cloud-Mig) to open the main page of the database.
5. Before trying to connect to the database instance on the cloud machine, you have to enable the dblistener access rule. Do the following:

a. Open the database service and select Access Rules from the menu.

b. For the ora_p2_dblistener rule, select Enable from the Actions menu.

Connect to the Cloud Database, Transfer the Dump File, and Import the Data

1. Open an instance of the PuTTY executable and connect to the Oracle Database Cloud Service compute node using an SSH public key.
   

Figure shows Connecting to Database Cloud Service compute node using PuTTY
2. On the Oracle Database Cloud Service compute node, check the pluggable databases (PDBs) and the pmon process:

[oracle@Cloud-Mig ~]$ ps -ef|grep pmon
oracle    311 32724 0 10:31 pts/1    00:00:00 grep pmon
oracle   7695     1 0 07:57 ?        00:00:00 ora_pmon_MIGORCL
[oracle@Cloud-Mig ~]$ sqlplus sys as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun May 21 10:31:42 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter Password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics 
and Real Application Testing options
SQL> show pdbs
   CON_ID   CON_NAME     OPEN MODE      RESTRICTED
----------  ----------   -----------    ------------
     2      PDB$SEED     READ ONLY      NO
     3      PDB1         READ WRITE     NO

3. Use a secure copy utility to transfer the dump file to the Oracle Database Cloud Service compute node.
In this example, the dump file is copied to the /u01 directory. Choose an appropriate location based on the size of the file that will be transferred.

a. On the Oracle Database Cloud Service compute node, create a directory for the dump file:

[oracle@Cloud-Mig admin]$ mkdir -p /u01/app/dump

b. Before using the scp command to copy the exported dump file, make sure the SSH private key that provides access to the Oracle Database Cloud Service compute node is available on your on-premises host.

c. On the on-premises database host, use the scp command to transfer the dump file to the Oracle Database Cloud Service compute node:

[oracle@cloud dpdump]$ ls
expdat.dmp   export.log
[oracle@cloud dpdump]$ scp -i /home/oracle/rsa-key-20170111.ssh expdat.dmp oracle@129.157.129.107:/u01/app/dump
Enter passphrase for key '/home/oracle/rsa-key-20170111.ssh':
expdat.dmp               4%  2704KB 856.1KB/s   01:12 ETA

4. On the Oracle Database Cloud Service compute node, import the data into the database:
a. On the Oracle Database Cloud Service compute node, invoke SQL*Plus and log in to the database as the SYSTEM user.

[oracle@Cloud-Mig admin]$ sqlplus sys@pdprem2 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun May 21 11:56:53 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter Password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options

b. Create a directory object in the Oracle Database Cloud Service database.

SQL> create directory cloud_mig as '/u01/app/dpump';
Directory created.

c. If they do not exist, create the tablespace(s) for the objects that will be imported.
d. Exit from SQL*Plus.
e. On the Oracle Database Cloud Service compute node, invoke the Data Pump Import utility and connect to the database. Import the data into the database.

[oracle@Cloud-Mig admin] $ impdb system@pdbprem2 full=y directory=cloud_mig
Import: Release 12.1.0.1.0 - Production on Sun May 21 12:24:39 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
...

5. After verifying that the data has been imported successfully, you can delete the expdat.dmp file. With this step, the entire process has be completed.


References: Oracle Technology Network.


For any queries you can email me on samiappsdba@gmail.com

Convert physical standby to snapshot standby database

Snapshot standby:

Snapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database.  i. e we can convert the physical standby database to snapshot standby. On that, we can do all types of testing or can be used as a development database (which is an exact replication of production ). Once the testing is over we can again convert the snapshot database to physical standby. Once it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.
A snapshot standby database is a fully update-able standby database that is created by converting a physical standby database into a snapshot standby database.

A snapshot Standby is open in the read-write mode and hence it is possible to process transactions independently of the primary database. At the same time, it maintains protection by continuing to receive data from the production database, archiving it for later use.
Using a single command change made while the database is in read-write mode can throw away the changes made to the standby database only and re-synchronize the standby database with the production database.
1. Snapshot standby database receives and archives, but does not apply the redo data.

2. Redo data received from the primary database is applied automatically once it is converted back into a physical standby database.

3. Snapshot standby database cannot be the target of a switchover or failover. A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.


DEMONSTRATION:
Steps to convert Physical Standby Database to the Snapshot Standby Database


I have 2 node primary cluster database and 2 node standby cluster database with the ASM. It is running on 12c version

Note: In the snapshot standby database, there is no step to be performed in the primary database. all the steps are performed only in the physical standby database

STATUS OF THE STANDBY DATABASE

SQL> select name,open_mode from gv$database;

NAME      OPEN_MODE

mydb1       MOUNTED

mydb2       MOUNTED

CHECKING RECOVERY AREA AND ALLOCATE SIZE

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE

db_recovery_file_dest                string      +EXA_RECO

db_recovery_file_dest_size           big integer 400G

CHECKING WHETHER FLASHBACK IS ENABLED OR NOT. TO OPEN A STANDBY DATABASE IN THE READ/WRITE MODE, WE NEED TO HAVE THE FLASHBACK WITH ENOUGH SIZE.

SQL> select flashback_on from gV$database;

FLASHBACK_ON

YES

YES

CHECKING THE STATUS OF RECOVERY PROCESS, IF IT IS ENABLED THEN WE HAVE TO STOP THE RECOVERY.

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#

RFS       LGWR              2         74       6080

MRP0      N/A               2         74       6078

RFS       LGWR              1         78       7145

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

AFTER CANCELING THE RECOVERY PROCESS, CHECK THE STATUS

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or           client_process='LGWR';

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#

RFS       LGWR              2         74       6150

RFS       LGWR              1         78       7216

CONVERTING THE STANDBY DATABASE AS A SNAPSHOT STANDBY DATABASE

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

CHECKING THE STATUS OF THE DATABASE

SQL> select open_mode,database_role from gv$database;

OPEN_MODE            DATABASE_ROLE

MOUNTED              SNAPSHOT STANDBY

MOUNTED              SNAPSHOT STANDBY

NOW WE ARE STOPPING & STARTING THE DATABASE TO OPEN MODE

$ srvctl stop database -d mydb

$ srvctl start database -d mydb -o open

CHECKING THE STATUS OF THE DATABASE

SQL> select open_mode,database_role from gv$database;

OPEN_MODE            DATABASE_ROLE

READ WRITE           SNAPSHOT STANDBY

READ WRITE           SNAPSHOT STANDBY

CHECKING THE SYSTEM CREATED THE RESTORE POINT

SQL> select NAME,SCN,TIME from v$restore_point;

NAME              SCN                 TIME

SNAPSHOT_STANDBY_REQUIRED_12/25/2018 10:30:00         20539509

25-DEC-18 10.30.00.000000000 AM

Now CREATE SOME TABLES & PERFORMING SOME DML OPERATIONS IN THE SNAPSHOT DATABASE

SQL> create table bhuvan as select * from dba_objects;

Table created.

SQL> select count(1) from test;

COUNT(1)

100786

SQL> delete test where owner='SAMI MALIK';

100786 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(1) from TEST;

COUNT(1)

100111

NOW WE ARE STOPPING & CONVERTING SNAPSHOT DATABASE IN TO PHYSICAL STANDBY DATABASE

$ srvctl stop database -d MYDB

# I am using single instance to perform the conversion from the snapshot standby database to the physical standby database

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.4.0 Production on Mon Dec 25 10:40:11 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 1.4431E+10 bytes

Fixed Size                  2240272 bytes

Variable Size            3892314352 bytes

Database Buffers         1.0503E+10 bytes

Redo Buffers               34148352 bytes

Database mounted.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

Once we convert from the snapshot standby database to the physical standby database, database will go to the no mount stage.

SQL> select open_mode,database_role from v$database;

select open_mode,database_role from v$database

                 *

ERROR at line 1:

ORA-01507: database not mounted

NOW WE ARE STOPPING & STARTING THE DATABASE TO MOUNT STAGE AND CHECKING THE RECOVERY PROCESS

$ srvctl stop database -d mydb

$ srvctl start database -d mydb

CHECKING THE RESTORE POINT, SYSTEM WILL REMOVE IT AUTOMATICALLY ONCE WE ARE CONVERTED TO PHYSICAL STANDBY DATABASE

SQL> select NAME,SCN,TIME from v$restore_point;

no rows selected

CHECKING THE RECOVEY PROCESS

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';

PROCESS   CLIENT_P    THREAD#  SEQUENCE#     BLOCK#

--------- -------- ---------- ---------- ----------

RFS       LGWR              2         83       3495

RFS       LGWR              1         87       4407

MRP0      N/A               2         83       3495

Protecting Oracle OHS/Apache using Oracle Clusterware 11g/12c

This month's article is about protecting Apache Application service using Oracle RAC Clusterware 12c. To do this we should create an application VIP, an Action Script and a Resource on the Clusterware.
Here we will see the step by step configuration to protect Apache application and similar configuration can be plagiarized to protect Oracle OHS or any other Application service.
The below steps will work for both 11g and 12c RAC Clusterware software,

Step1. As the root user, verify that the Apache RPMs, httpd, httpd-devel and httpd-manual are installed on the two nodes on which Oracle clusterware is installed and configured.
grid@host01# su -
root@host01# rpm -qa | grep httpd
httpd-2.4.6-40.0.1.el7.x86_64
httpd-manual-2.4.6-40.0.1.el7.noarch
httpd-tools-2.4.6-40.0.1.el7.x86_64
Repeat on second node
root@host02# rpm -qa | grep httpd
httpd-2.4.6-40.0.1.el7.x86_64
httpd-manual-2.4.6-40.0.1.el7.noarch
httpd-tools-2.4.6-40.0.1.el7.x86_64

Step2. As the root user, start the Apache application on first node,
# apachectl start
Now access the Apache home page and verify it is working,
http://host01.samiora.blogspot.com:7777

OHS is managed by OPMN, the command line interface to OPMN is opmnctl.
Start OPMN and all managed processed, if not already started,
# ./opmnctl startall
# ./opmnctl status -l
# ./opmnctl stopproc process-type=OHS
[appldev@host01 scripts]$ sh adopmnctl.sh status -l
You are running adopmnctl.sh version 120.0.12020000.2
Checking status of OPMN managed processes...
Processes in Instance: EBS_web_dev_OHS1
---------------------------------+--------------------+---------+----------+------------+----------+-----------+------
ias-component                    | process-type       |     pid | status   |        uid |  memused |    uptime | ports
---------------------------------+--------------------+---------+----------+------------+----------+-----------+------
EBS_web_dev                   | OHS                |    8356 | Alive    |  778125435 |  2125916 |   2:03:55 | https:4447,https:10004,http:7777
adopmnctl.sh: exiting with status 0
adopmnctl.sh: check the logfile /u01/dev/fs1/inst/apps/dev_host01/logs/appl/admin/log/adopmnctl.txt for more information ...

Verify OHS and Apache pages are appearing or not on host01 where the service is started,

 

 

Step3. Now create an action script to control the application. This script must be accessible by all nodes on which the application resource can be located.

A) As the root user, create a script on the first node called 'apache.scr' in /usr/local/bin that will start, stop, check status and clean up if the application does not exit cleanly. Make sure that the host specified in the WEBPAGECHECK variable is your first node.
root@host01# vi /usr/local/bin/apache.scr
#!/bin/bash
HTTPDCONFLOCATION=/etc/httpd/conf/httpd.conf
WEBPAGECHECK=http://host01.samiora.blogspot.com:80/icons/apache_pb.gif
case $1 in
'start')
/usr/sbin/apachectl -k start -f $HTTPDCONFLOCATION
RET=$?
;;
'stop')
/usr/sbin/apachectl -k stop
RET=$?
;;
'clean')
/usr/sbin/apachectl -k stop
RET=$?
;;
'check')
/usr/bin/wget -q --delete-after $WEBPAGECHECK
RET=$?
;;
*)
RET=0
;;
esac
# 0: success; 1 : error
if [ $RET -eq 0 ]; then
exit 0
else
exit 1
fi
root@host01# chmod 755 /usr/local/bin/apache.scr
root@host01# apache.scr start
Verify the web page and it should be working.
root@host01# apache.scr stop
Verify the web page and it will not be working.

B) As root, create a script on the second node called 'apache.scr' in /usr/bin/local that will start, stop, check status and clean up if the application does not exit cleanly. Make sure that the host specified in WEBPAGECHECK variable is your second node.
root@host02# vi /usr/local/bin/apache.scr
#!/bin/bash
HTTPDCONFLOCATION=/etc/httpd/conf/httpd.conf
WEBPAGECHECK=http://host02.samiora.blogspot.com:80/icons/apache_pb.gif
case $1 in
'start')
/usr/sbin/apachectl -k start -f $HTTPDCONFLOCATION
RET=$?
;;
'stop')
/usr/sbin/apachectl -k stop
RET=$?
;;
'clean')
/usr/sbin/apachectl -k stop
RET=$?
;;
'check')
/usr/bin/wget -q --delete-after $WEBPAGECHECK
RET=$?
;;
*)
RET=0
;;
esac
# 0: success; 1 : error
if [ $RET -eq 0 ]; then
exit 0
else
exit 1
fi
root@host02# chmod 755 /usr/local/bin/apache.scr
root@host02# apache.scr start
Verify the web page and it should be working.
root@host02# apache.scr stop
Verify the web page and it will not be working.

Step4. Next, you must validate the return code of a check failure using the new script. The Apache server should NOT be running on either node. Run 'apache.scr check' and immediately test the return code by issuing an 'echo $?' command. This must be run immediately after the 'apache.scr check' command because the shell variable $? holds the exit code of the previous command run from the shell. An unsuccessful check should return an exit code of 1. You should do this on both nodes.
root@host01# apache.scr check
root@host01# echo $?
1
root@host02# apache.scr check
root@host02# echo $?
1

Step5. As the grid user, create a server pool for the resource called myApache_sp. This pool contains your first two hosts of the cluster and is a child of the Generic pool.
grid@host01# id
uid=502(grid) gid=54321(oinstall) groups=504(asmadmin),505(asmdba),506(asmoper),54321(oinstall)
grid@host01# . oraenv
ORACLE_SID = [grid] ? +ASM1
The Oracle base has been set to /u01/app/grid
grid@host01# /u01/app/12.2.0/grid/bin/crsctl add serverpool myApache_sp -attr "PARENT_POOLS=Generic,SERVER_NAMES=host01 host02"

Step6. Check the status of the new pool of your cluster.
grid@host01# /u01/app/12.2.0/grid/bin/crsctl status server -f
NAME=host01
STATE=ONLINE
ACTIVE_POOLS=myApache_sp Generic
STATE_DETAILS=

NAME=host02
STATE=ONLINE
ACTIVE_POOLS=myApache_sp Generic
STATE_DETAILS=....

Step7. Add the Apache Resource, which can be called myApache, to the myApache_sp subpool that has Generic as a parent. It must be performed as root because the resource requires root authority because of listening on the default privileged port 80. set CHECK_INTERVAL to 30, RESTART_ATTEMPTS to 2 and PLACEMENT to restricted.
root@host01# su -
root@host01# id
uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel)
root@host01# /u01/app/12.2.0/grid/bin/crsctl add resource myApache -type cluster_resource -attr "ACTION_SCRIPT=/usr/local/bin/apache.scr, PLACEMENT='restricted', SERVER_POOLS=myApache_sp, CHECK_INTERVAL='30', RESTART_ATTEMPTS='2'"

Step8. View the static attributes of the myApache resource with the crsctl status resource myApache -p -f command.
root@host01# /u01/app/12.2.0/grid/bin/crsctl status resource myApache -f
NAME=myApache
TYPE=cluster_resource
STATE=OFFLINE
TARGET=ONLINE
ACL=owner:root:rwx,pgrp:root:r-x,other::r--
ACTION_FAILURE_TEMPLATE=
ACTION_SCRIPT=/usr/local/bin/apache.scr
ACTIVE_PLACEMENT=0
AGENT_FILENAME=%CRS_HOME%/bin/scriptagent
AUTO_START=restore
CARDINALITY=1
CARDINALITY_ID=0
CHECK_INTERVAL=30
CREATION_SEED=30
DEFAULT_TEMPLATE=
DEGREE=1
DESCRIPTION=
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
HOSTING_MEMBERS=
ID=myApache
INSTANCE_FAILOVER=0
LOAD=1
LOGGING_LEVEL=1
NOT_RESTARTING_TEMPLATE=
OFFLINE_CHECK_INTERVAL=0
PLACEMENT=restricted
PROFILE_CHANGE_TEMPLATE=
RESTART_ATTEMPTS=2
SCRIPT_TIMEOUT=60
SERVER_POOLS=myApache_sp
START_DEPENDENCIES=
START_TIMEOUT=0
STATE_CHANGE_TEMPLATE=
STOP_DEPENDENCIES=
STOP_TIMEOUT=0
UPTIME_THRESHOLD=1h

Step9. Use the 'crsctl start resource myApache' command to start the new resource. Use the 'crsctl status resource myApache' command to confirm that the resource is online on the first node. If you like, open a browser and verify the apache home page as shown in step 2 above.
root@host01# /u01/app/12.2.0/grid/bin/crsctl start resource myApache
root@host01# /u01/app/12.2.0/grid/bin/crsctl status resource myApache
resource myApache
NAME=myApache
TYPE=cluster_resource
TARGET=ONLINE
STATE=ONLINE on host01

Step10. Confirm that Apache is NOT running on your second node. The easiest way to do this is to check for the running '/usr/sbin/httpd -k start -f /etc/httpd/conf/httpd.confd' processes with the ps command.
root@host02# ps -ef | grep -i "httpd -k"

Step11. Next, simulate a node failure on your first node using the init command as root. Before issuing the reboot on the first node, open a VNC session on the second node and as the root user execute below script so that you can monitor the failover.
monitor.sh
while true
do
ps -ef | grep -i "httpd -k"
  sleep 1
done
root@host01# reboot ==>To initiate a reboot, simulating a node failure.
At the same time on second node run the below script as root user,
root@host02# sh monitor.sh ==> you will see that after sometime the httpd service will be started on host02.

Step12. Verify the failover from the host01 to host02 with the 'crsctl status resource myApache -t' command.
root@host02# /u01/app/12.2.0/grid/bin/crsctl status resource myApache -t
NAME      TARGET  STATE  SERVER  STATE_DETAILS 
Cluster Resources
myApache
1               ONLINE  ONLINE  host02
Now access Apache page on host02 and it should display while on host01 it will not.
http://host02.samiora.blogspot.com

Step13. Use the 'crsctl relocate resource' command to move the myApache resource back to host01.
root@host01# /u01/app/12.2.0/grid/bin/crsctl relocate resource myApache
CRS-2673: Attempting to stop 'myApache' on 'host02'
CRS-2677: Stop of 'myApache' on 'host02' succeeded
CRS-2672: Attempting to start 'myApache' on 'host01'
CRS-2676: Start of 'myApache' on 'host01' succeeded
Now access Apache page on host01 and it should display while on host02 it will not.
http://host01.samiora.blogspot.com

For any queries on Oracle RAC 11g or RAC 12c you can email me on samiappsdba@gmail.com

Upgrade 11g to 12c 12.1.0.2 + Bundle Patch 12.1.0.2.1

With every release of the Oracle database there is always an upgrade path that should be followed. The upgrade path for going to Oracle Database 12c (12.1.0.2) is pretty straightforward.

Supported DB Direct Upgrade Paths to 12c are:

-Oracle Database 10g (10.2.0.5)
-Oracle Database 11g (11.1.0.7)
-Oracle Database 11g (11.2.0.2 or later)

There are three supported upgrade paths/tools. The upgrade options that are supported with Oracle Database 12c are:

-Database Upgrade Assistant (DBUA)
-Manual Upgrade (script based)
-Export/Import

For the purpose of this article, let’s focus on using the Database Upgrade Assistant (DBUA).

1. Run the PREUPGRD.SQL

In order to run the preupgrd.sql file, we first need to install the new binaries into an Oracle home for 12c. Once the binaries are in place, we need to setup our environment to connect to the database we want to upgrade.

Oracle Database 11g settings that will be upgraded are:

ORACLE_SID=ora11g
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/11.2.0.3.0/db_1

Next we need to go to the directory where the preupgrd.sql file is located:

# cd /opt/oracle/product/12.1.0.2/dbhome_1/rdbms/admin

Finally, we need to connect to the 11g database with SQL*Plus and run the preupgrd.sql file:

# sqlplus / as sysdba
SQL> @preupgrd.sql

When the preupgrd.sql script is done, we will be given the locations of the files that we need to reference for verifying and correcting any issues with our environment.

Results of the checks are located at:

/opt/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade.log

Pre-Upgrade Fixup Script (run in source database environment):

/opt/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade_fixups.sql

Post-Upgrade Fixup Script (run shortly after upgrade):

/opt/oracle/cfgtoollogs/ora11g/preupgrade/postupgrade_fixups.sql

Review these scripts and correct anything that needs to be fixes. Once these corrections are made, running the DBUA will be simpler. If there are any errors listed in the preupgrade.log, these need to be corrected before proceeding.

2. Running DBUA

Once everything has been corrected after reviewing the preupgrade.log, we can start the Database Upgrade Assistant (DBUA).

To start the DBUA, we need to go to the Oracle Database 12c home and run dbua:

# cd /opt/oracle/product/12.1.0.2/dbhome_1/bin
# ./dbua &
This will start the GUI to begin the upgrade. You will notice that I did not change anything in my environment. I’m still pointing to the 11g environment.

ORACLE_SID=ora11g
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/11.2.0.3.0/db_1
Once the DBUA starts, you will notice that we are at step 1 of 11.  The number of steps will change depending on the options that are selected. Step 1, we have two options:

•Upgrade an Oracle Database
•Move an existing 12c database to a new 12c oracle home

For the purpose of the update, we can just click next and move on.
Below are the step by step screenshots.

At this point, the upgrade is done and the DBUA can be closed. Click the ‘Close’ button to exit the GUI.

Verify the Upgrade
There are multiple ways that the upgrade can be verified. The easiest way is the check the /etc/oratab file. Once the upgrade is done, this oratab file should have changed the Oracle home to match the 12c binary location.

Another way to verify is to check the environment variables from the command line:

# env | grep ORA
ORACLE_SID=ora11g
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/12.1.0.2/dbhome_1

Lastly, we can use SQL*Plus to check the version of the database:

# sqlplus / as sysdba
 
SQL> select banner from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                               0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                         0
NLSRTL Version 12.1.0.2.0 - Production                                                   0


Upgrade database from 12.1.0.2.0 to 12.1.0.2.1 using Bundle patch (19720843) 

Bundle Patch (BP) patches are cumulative. That is, the content of all previous BPs is included in the latest BP patch.

A. If on windows OS, Stop all services.

Distributed Transaction coordinator
OracleServiceora12c
OracleListener

B. export PATH=$ORACLE_HOME/perl/bin:$ORACLE_HOME/OPatch:$PATH

C. unzip p19720843_12102_<platform>.zip
cd 19720843
opatch apply

D. Start services
Distributed Transaction coordinator
OracleServiceora12c
OracleListener

E. For each database instance running on the oracle home being patched, run the 'datapatch' utility
# sqlplus / as sysdba
SQL> STARTUP;
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
SQL> EXIT;
# cd $ORACLE_HOME/OPatch
# datapatch -verbose

The 'datapatch' utility will then run the necessary apply scripts to load the modified SQL files into the database. An entry will be added to the DBA_REGISTRY_SQLPATCH and DBA_REGISTRY views reflecting the patch application.

SQL> select patch_id,version,status,bundle_series,description from dba_registry_sqlpatch;

PATCH_ID VERSION  STATUS    BUNDLE_SERIES    DESCRIPTION
---------------------------------------------------------------------------
19720843    12.1.0.2     SUCCESS    PSU       WINDOWS DB BUNDLE PATCH 12.1.0.2.1(64bit):19720843

F. Check the following log files in $ORACLE_HOME/sqlpatch/19648840/ for errors:

19648840_apply_<database SID>_<CDB name>_<timestamp>.log
where database SID is the database SID, CDB name is the name of the multitenant container database, and timestamp is of the form YYYYMMMDD_HH_MM_SS.
In addition, you can check the log files for catbundle in $ORACLE_HOME/cfgtoollogs/catbundle or $ORACLE_BASE/cfgtoollogs/catbundle for any errors:

catbundle_PSU_<database SID>_APPLY_<TIMESTAMP>.log
catbundle_PSU_<database SID>_GENERATE_<TIMESTAMP>.log
where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS
 G. If you are using the Oracle Recovery Manager, the catalog needs to be upgraded. Enter the following command to upgrade it:

$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;





Patch deinstallation

Shutdown the database and listener and then run the below command to deinstall the patch.

cd 19720843
opatch rollback -id 19720843


I hope this article has shown you how easy it is to upgrade an Oracle Database 11g up to the latest release of the Oracle Database 12c and to apply the bundle patch to upgrade your database to higher patch level i.e from 12.1.0.2 to 12.1.0.2.1. 


For any further assistance please don't hesitate to contact me on samiora@gmail.com