Generate REST API from mysql database in less than 5 minutes

Representation State Transfer is the new industry standard of building technology neutral API’s. REST API are essentially web services to read or transfer data. REST API’s can be build on top of most relational databases. Mysql being the most popular database around. If you have nodejs and mysql already installed, you can build a REST API for your mysql db in less than 2 minutes 🙂

Before we begin we must have nodejs and mysql running on the server. If you don’t have nodejs, you can check my other article on how to install node.js on Cent OS Linux : https://easyoradba.com/2018/05/08/install-nodejs-on-centos-6-7/

And to quickly install mysql refer to my another of my blog posts 🙂 https://easyoradba.com/2018/05/18/install-mysql-database-along-with-phpadmin-on-centos-6-7/

Once you have mysql and nodejs setup. You can install and spin up  REST API’s doing the below steps

1.Install xmysql using node package manager

$ npm install -g xmysql

2. Create a new user, a new test database and a new table with few records

mysql -u root -p

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘test’@’localhost’ IDENTIFIED BY ‘test123’;

mysql> CREATE DATABASE easyoradba;

mysql> USE easyoradba;

mysql> CREATE TABLE emp ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );

mysql> INSERT INTO emp ( id, name ) VALUES ( null, ‘SHADAB’ );

mysql> select * from emp;
+—-+——–+
| id | name |
+—-+——–+
| 1 | SHADAB |
+—-+——–+
1 row in set (0.00 sec)

mysql> exit;

3. Generate the REST API for db ‘easyoradba’ using xmysql

$ xmysql -h localhost -u test -p test123 -d easyoradba -n 8899 -r 192.168.1.46

Generating REST APIs at the speed of your thought..

– – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

Database : easyoradba
Number of Tables : 1

REST APIs Generated : 19

Xmysql took : 0.3 seconds

– – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

-h hostname of your mysql DB server

-u username of mysql

-p password for mysql user

-d DB name for which API needs to be generated

-n port number to create the REST service

-r your server IP, if left blank it will create with default value localhost

4. Check the REST API service and Call it from Postman

Go to browser and check the ip with defined port number : http://192.168.1.46:8899/

[{“resource”:”emp”,”routes”:[{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/describe”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/count”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/groupby”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/distinct”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/ugroupby”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/chart”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/aggregate”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/findOne”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/autoChart”},{“httpType”:”post”,”routeUrl”:”http://192.168.1.46:8899/api/emp”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp”},{“httpType”:”post”,”routeUrl”:”http://192.168.1.46:8899/api/emp/bulk”},{“httpType”:”delete”,”routeUrl”:”http://192.168.1.46:8899/api/emp/bulk”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/bulk”},{“httpType”:”put”,”routeUrl”:”http://192.168.1.46:8899/api/emp”},{“httpType”:”patch”,”routeUrl”:”http://192.168.1.46:8899/api/emp/:id”},{“httpType”:”delete”,”routeUrl”:”http://192.168.1.46:8899/api/emp/:id”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/:id”},{“httpType”:”get”,”routeUrl”:”http://192.168.1.46:8899/api/emp/:id/exists”}]},”http://192.168.1.46:8899/api/tables”,”http://192.168.1.46:8899/api/xjoin”,”http://192.168.1.46:8899/api/dynamic”,”/upload”,”/uploads”,”/download”]

Open Postman and call the URL with GET method to fetch the data

Screen Shot 2018-05-18 at 2.17.30 PM

 

And voila ! There you have it an REST API running on top of a mysql database in less than 5 minutes :)))

 

 

 

Install mysql database along with phpadmin on CENTOS 6 7

Mysql is the most popular open database and is the M in the LAMP stack (others being Linux-Apache-PHP)

If your on CentOS and your linux server is connected to the internet installing mysql is just a few terminal commands

1. Install the LAMP stack via yum

$ yum install httpd mysql-server php php-mysql

2. Configure the LAMP Stack

$ service httpd start

Now you should have a webserver up and running on http://localhost/

3. Start and secure your mysql database by executing below script

$ service mysqld start

$ mysql_secure_installation

Securing your database is very important, specially if your deploying a productions database

4. Install phpmyadmin

Install the EPEL Repo, if your on CentOS 6

$ wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
$ rpm -ivh epel-release-6-8.noarch.rpm

if your on CentOS 7

$ wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
$ rpm -ivh epel-release-latest-7.noarch.rpm

Now install phpmyadmin

$ yum install phpmyadmin

If you want access to phpmyadmin from other computers edit below file and add the host ip’s

$ vi /etc/httpd/conf.d/phpMyAdmin.conf

Require ip <other workstation ip>

Allow from <other workstation ip>

Restart httpd services

$ service httpd restart

You can now access using below URL

http://localhost/phpmyadmin

Enter your root username and password and you now have a nice GUI to manage your mysql instance

Install Oracle Apex 5.1.x on ORDS 3,17.4, 18.1

Oracle Apex is a modern framework to quickly develop and deploy web apps sitting on top of Oracle Database. Apex can be run directly from the database engine itself using a component called Mod PL/SQL. It is one of the easiest way to run Apex, but it is not a production ready setup. In a production scenario your database server should not be internet facing. The ideal design is a reverse proxy server facing the internet and a webserver sitting behind it and the DB server behind a firewall isolated from any public network.

Oracle has a modern gateway to do this, it is called ORDS( previously called Apex Listener). It stands for Oracle Rest Data Services. Not only it can help you host your apex instance in a Java web container like Tomcat, Glassfish, Weblogic but also it gives your developer access to build enterprise scale data access API’s. It can help you build your front-end in the modern JavaScript instead of the traditional programming languages. You can read about it more here : https://blogs.oracle.com/newgendbaccess/why-use-rest-and-ords-to-transform-your-oracle-database-into-a-restful-api-service

architecture big

Now to setup ORDS to host your Apex instance first we need to make sure you have 3 components

1. Oracle APEX itself, though from ORDS 17.1 it is not compulsory to have Apex installed, but i always prefer having it. Download the latest version from here : http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html

2. A Servlet web container like Tomcat, Glassfish or Weblogic. I prefer to use Tomcat since it is the easiest to setup, is open source and is quite lightweight. Apache tomcat can be downloaded from here  : https://tomcat.apache.org/download-90.cgi

3. ORDS, this is the most important component in this setup. Oracle has recently changed it’s numbering scheme. So it jumped from ORDS 3 to ORDS 17.4 and ORDS 18.1. It is now done using YY.MM naming convention. It can be downloaded from here : http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html

Let’s  Start with Installing Apache tomcat first on you server

                             1.  INSTALL APACHE TOMCAT

1. Create a user tomcat on the linux server which will be your webserver

useradd tomcat

2. Untar the file in the home directory or create a separate directory. To have a well defined structure i always prefer to have a directory structure like /u01/tomcat

tar xvf apache-tomcat-9.0.8.tar.gz

This will create the directory ‘apache-tomcat-9.0.8’ inside

go to the .bash_profile of tomcat user and add below for the home directory

PATH=$PATH:$HOME/bin:$CATALINA_HOME/bin
CATALINA_HOME=/u01/tomcat/apache-tomcat-9.0.8
export CATALINA_HOME
export PATH

. .bash_profile

3. Start Stop Apache Tomcat 9 snd enable Gui Access

sh $CATALINA_HOME/bin/shutdown.sh

sh $CATALINA_HOME/bin/startup.sh

You can now access the console of tomcat using http://localhost:8080/

To enable access to manager app and manager app gui. Add below to lines to file tomcat-users.xml

vi $CATALINA_HOME/conf/tomcat-users.xml

<role rolename=”manager-gui”/>
<user username=”tomcat” password=”Abc1234$#” roles=”manager-gui”/>
<role rolename=”admin-gui”/>
<user username=”tomcat” password=”Abc1234$#” roles=”admin-gui”/>

Save and restart Tomcat

                        2.   INSTALL ORACLE APEX 5.1.x

This installation has to be done on the DB Server. Copy your Apex zip file to Oracle Home Directory. Remove the existing Apex directory and unzip the fil

1.Install Apex Binary and Create Tablespace and Schema

cd $ORACLE_HOME

rm -rf apex/

unzip apex_5_0_1_en.zip

cd $ORACLE_HOME/apex

sqlplus “/as sysdba”

SQL> CREATE TABLESPACE APEX  DATAFILE ‘/u01/oradata/orcl/datafiles/apex01.dbf’ SIZE 1G AUTOEXTEND ON NEXT 10M;

SQL> @apexins.sql APEX APEX TEMP /i/

APEX – Tablespace to hold Apex Schema and its associated files

TEMP –  Temporary Tablespace

/i/ – It is  your image directory

Change your Admin password

SQL> @apxchpwd.sql

Create APEX_LISTENER and  APEX_REST_PUBLIC_USER

SQL> @apex_rest_config.sql

If you want to run the PL/SQL gateway continue to the next step else you can skip this and go to Step 3. directly

SQL> @apex_epg_config.sql /u01/app/oracle/product/12.2.0/dbhome_1

‘/u01/app/oracle/product/12.2.0/dbhome_1’ is the home directory of Oracle Database

SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

Check the HTTP Port,

SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

If this is 0 and you need to to install Apex with ORDS then goto Step 3. else continue and get the port

SQL> EXEC DBMS_XDB.sethttpport(8080);

SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

—

8080

The URL for Apex application now should be accessible from Web Browser http://localhost:8080/apex/apex_admin

              3. ORACLE REST DATA SERVICES (ORDS) CONFIGURATION & INSTALLATION

After downloading the ORDS to the server, go to the server where you installed Tomcat earlier

1.Unzip the ords zip file

cd /u01

mkdir -p /u01/ords

mkdir -p /u01/ords/conf

unzip ords.18.1.1.95.1251.zip

2. Login to DB Server SQL*PLUS and unlock the Apex  listener usernames and password to ensure ORDS installation goes smoothly

SQL> ALTER USER SYS IDENTIFIED BY abc123 ACCOUNT UNLOCK;

SQL> ALTER USER APEX_LISTENER IDENTIFIED BY abc123 ACCOUNT UNLOCK;
SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY abc123 ACCOUNT UNLOCK;
SQL> ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY abc123 ACCOUNT UNLOCK;

SQL>ALTER USER ORDS_PUBLIC_USER IDENTIFIED BY abc123 ACCOUNT UNLOCK;

If last SQL command fails, it is because there is no ORDS schema yet.

3. Configure ORDS for Database connectivity. Go back to WebServer and there will be a ords.war file in /u01/ords

Edit the /u01/ords/params/ords_params.properties and set the parameters of your DB server and instance. Make sure there is network connectivity between DB Server and Web Server

cd /u01/ords

java -jar ords.war configdir /u01/ords/conf

java -jar ords.war

Don’t select Standalone option in the last, just press 2 and exit. Standlone server is a quick Jetty webserver build in ORDS. But its not the best production setup, hosting on Apache Tomcat is better.

mkdir $CATALINA_HOME/webapps/i/

Copy the image directory from the Apex installation in DB Server to Tomcat image directory.

cp -R $ORACLE_HOME/apex/images/* $CATALINA_HOME/webapps/i/

Copy the ords.war file from /u01/ords to Webapps directory of tomcat

cd /u01/ords
cp ords.war $CATALINA_HOME/webapps/

ORDS should now be accessible on URL http://localhost:8080/ords

Remember to remove the port 8080 configuration from Embedded PL/SQL gateway, if you did Step 2. and are moving to ORDS from PL/SQL Gateway

SQL> EXEC DBMS_XDB.sethttpport(0);

SQL> SELECT DBMS_XDB.gethttpport FROM DUAL;

—

0

And try URL again : http://localhost:8080/ords

Error 1 :

Oracle Rest Data Services 404 Not Found When Running APEX – PL/SQL Gateway Configured

Check APEX_PUBLIC_USER account and unlock it, stop and start Apache tomcat and try again. Or maybe an incorrect APEX_PUBLIC_USER password was entered during ORDS configuration. reconfigure the ords.war using below command:

java -jar ords.war setup

And copy new war file to webapps directory again and access URL again

http://localhost:8080/ords

Reference : Oracle Rest Data Services 404 Not Found When Running APEX – PL/SQL Gateway Configured (Doc ID 2048493.1)

Error 2:

Server isn’t redirecting oracle apex ords on Firefox

This page isn’t working
<hostname> redirected you too many times.

Try clearing the cookies and try again. Most likely close the browser windows and open and again and the error will go away

http://localhost:8080/ords

Reference: Problem Accessing APEX Application Using ORDS 3.0.9.348.07.16 (Doc ID 2280694.1)

Summary :

1.Steps to do when installing only PL/SQL gateway

1 > 2

2. Steps when upgrading from PL/SQL Gateway to ORDS

1 > 3

3. Steps when doing fresh new install of ORDS

1 > 2 > 3

Install NodeJS on Mac OS

To install NodeJS on a Mac the first thing you have to do is download Brew package manager for mac. Though you can install NodeJS via other methods but doing it with Brew is the easiest and least error prone method

1. Install Brew on your mac if you dont have it already. Open the terminal app and paste the below command (make sure you have the password for the user)

ruby -e “$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)”

2. Now the Brew package manager is installed. Open terminal and type below command

brew install node

3. Check Versions of Node and NPM

node -v

npm -v

 

4. Create a Demo Web server and Check. Open terminal and type below

vim demo_server.js

— Paste below Code in the file —

var http = require(‘http’);
http.createServer(function (req, res) {
res.writeHead(200, {‘Content-Type’: ‘text/plain’});
res.end(‘Welcome To EasyOraDBA’);
}).listen(3005, “127.0.0.1”);
console.log(‘Server is running at http://127.0.0.1:3005/’);

 

node demo_server.js

— Now open a Web browser and paste the URL http://127.0.0.1:3005/ And voila you have a NodeJS Web Server running —

 

Install NodeJS on CentOS 6 – 7

Install Latest NodeJS on CentOS 6 or 7
————————————–

1. Add Repo and Install NodeJS

For Stable Release:-

$ yum install -y gcc-c++ make
$ curl -sL https://rpm.nodesource.com/setup_8.x | sudo -E bash –

$ yum install nodejs

 

2. Check the version for Node and NPM

$ node -v

v8.11.1

$ npm -v

5.6.0

 

3. Create a Demo Server

$ vim demo_server.js

add below lines

var http = require(‘http’);
http.createServer(function (req, res) {
res.writeHead(200, {‘Content-Type’: ‘text/plain’});
res.end(‘Welcome To EasyOraDBA’);
}).listen(3005, “127.0.0.1”);
console.log(‘Server is running at http://127.0.0.1:3005/’);

4. Start the Node Server

[root@easyoradba CordovaApps]# node demo_server.js
Server is running at http://127.0.0.1:3005/

Go to your Browser and Paste the URL, your WebServer has been started on port 3005

Quick and Easy Integration with Twitter with Apex 18.1

Quick Integration with Twitter in Oracle Apex 18.1

https://blogs.oracle.com/apex/quick-and-easy-twitter-api-with-apex-181

 

ORA-01103: database name ‘MW’ in control file is not ‘MWTST’

This error most likely occurred when you duplicated a database and the DB_NAME parameter in spfile is not matching the one in the control file. Sometimes it so happened that after duplicating the database name in SPFILE with which you started the instance with is not matching the one in the control file.

Explanation:
=====================

The database name in the controlfile is not consistent with the db_name
parameter in the init.ora file.

Solution Step 1:

Change the DB_NAME parameter in spfile and bounce the database

STARTUP NOMOUNT

ALTER SYSTEM SET DB_NAME=ORIGINAL_DBNAME SCOPE=spfile;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE OPEN RESETLOGS;

If you get like below error

ORA-19838: Cannot use this control file to open database then have to recreate the control file from trace . Go to Solution 2:

See Oracle support Doc ID 1906009.1

 

Solution Step 2:

 

Now the only way to bring up the cloned DB is to re-create controlfile from trace. Login to the source database and run below commands.

SQL> alter database backup controlfile to trace;

Check location of controlfile which is created to trace; checking in ALERT LOG

ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Backup controlfile written to trace file /u02/app/oracle/diag/rdbms/mw/mw1/trace/mw1_ora_146366.trc

Then Copy/Edit the Details of ControlFile from the block like below.

[code language=”sql”]

CREATE CONTROLFILE SET DATABASE “MW” RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 9344
LOGFILE
GROUP 1 (
‘+DATAC1/mwtst1/onlinelog/group_1.366.854619221’,
‘+RECOC1/mwtst1/onlinelog/group_1.504.854619221’
) SIZE 100M BLOCKSIZE 512,
GROUP 2 (
‘+DATAC1/mwtst1/onlinelog/group_2.367.854619389’,
‘+RECOC1/mwtst1/onlinelog/group_2.505.854619389’
) SIZE 100M BLOCKSIZE 512,
GROUP 3 (
‘+DATAC1/mwtst1/onlinelog/group_3.368.854619411’,
‘+RECOC1/mwtst1/onlinelog/group_3.506.854619411’
) SIZE 100M BLOCKSIZE 512,
GROUP 4 (
‘+DATAC1/mwtst1/onlinelog/group_4.365.854619175’,
‘+RECOC1/mwtst1/onlinelog/group_4.507.854619175’
) SIZE 100M BLOCKSIZE 512
DATAFILE
‘+DATAC1/mwtst1/datafile/SYSTEM.467.971185463’,
‘+DATAC1/mwtst1/datafile/SYSAUX.439.971266529’,
‘+DATAC1/mwtst1/datafile/USERS.473.971266529’,
‘+DATAC1/mwtst1/datafile/UNDOTBS1.468.971185463’,
‘+DATAC1/mwtst1/datafile/UNDOTBS2.466.971185463’,
‘+DATAC1/mwtst1/datafile/MQ.357.971266521’,
‘+RECOC1/mwtst1/datafile/AUDIT_TBS.1787.971185463′
CHARACTER SET AL32UTF8
;

[/code]

Save it as control_file.sql

Ensure you check the Datafile location and name, it should be same as in what resides on your filesystem. This is specially true when you using ASM and OMF together.

 

Now start the instance

 

SQL> startup nomount

SQL> @control_file.sql

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

SQL> select open_mode,name from v$database;

OPEN_MODE NAME
——————– ———
READ WRITE MW

SQL> select * from v$controlfile;

STATUS
——-
NAME
——————————————————————————–
IS_ BLOCK_SIZE FILE_SIZE_BLKS
— ———- ————–

+RECOC1/mw/controlfile/current.2156.971360621
YES 16384 1878

Now change the database name in control file using nid utility

nid TARGET=system/****** DBNAME=MWTST SETNAME=Y

 

DBNEWID: Release 11.2.0.4.0 – Production on Wed Mar 21 14:27:18 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to database MW (DBID=1312370394)

Connected to server version 11.2.0

Control Files in database:
+RECOC1/mw/controlfile/current.2156.971360621

Change database name of database MW to MWTST? (Y/[N]) => Y

Proceeding with operation
Changing database name from MW to MWTST
Control File +RECOC1/mw/controlfile/current.2156.971360621 – modified
Datafile +DATAC1/mwtst1/datafile/system.467.97118546 – wrote new name
Datafile +DATAC1/mwtst1/datafile/sysaux.439.97126652 – wrote new name
Datafile +DATAC1/mwtst1/datafile/undotbs1.468.97118546 – wrote new name
Datafile +DATAC1/mwtst1/datafile/users.473.97126652 – wrote new name
Datafile +DATAC1/mwtst1/datafile/undotbs2.466.97118546 – wrote new name
Datafile +DATAC1/mwtst1/datafile/mq.357.97126652 – wrote new name
Datafile +RECOC1/mwtst1/datafile/audit_tbs.1787.97118546 – wrote new name
Control File +RECOC1/mw/controlfile/current.2156.971360621 – wrote new name
Instance shut down

Database name changed to MWTST.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID – Completed succesfully.

 

Change PARAMETER in PFILE or in Memory

SQL> alter system set db_name=’MWTST’ scope=spfile;

System altered.

SQL> show parameter cluster

NAME TYPE VALUE
———————————— ———– ——————————
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string

 

SQL> alter system set db_unique_name=’MWTST’ scope=spfile;

SQL> shutdown immediate;

SQL> startup mount

SQL> alter database open;

Database altered.

SQL> select open_mode,name from v$database;

OPEN_MODE NAME
——————– ———
READ WRITE MWTST

 

 

 

 

 

Restore and Upgrade Oracle Database from 11gR2 to 12c from Exadata X4-2 to X7-2

Upgrade from Exadata X4-2 11g (11.2.0.4) to Exadata X7-2 12CR1 (12.1.0.2.0) : ASM to ASM

Oracle Database 11.2.0.3and 11.2.0.4 to Oracle 12.2.0.1 Direct upgrade is supported by Oracle. If you have Database lower than those version than you have to upgrade to 11.2.0.4 first and you can upgrade to 12cR2

In our Scenario We will Migrate and Upgrade from One Exadata machine (X4-2) to another Exadata machine (X7-2) from 11.2.0.4 to 12.1.0.2.0

Source Exadata Setup

———————-

RAC 2-Nodes

Hardware : X4-2

Image version: 12.1.2.3.5.170418

DB NAME : mob

DATABASE HOME VERSION: 11.2.0.4

GRID HOME VERSION : 12.1.0.2.0

ASM Diskgroups : +DATAC1, +RECOC1

Target Exadata Setup

—————–

RAC 2-Nodes

Hardware : X7-2

Image version: 18.1.4.0.0.180125.3

DB NAME: SWX

DATABASE HOME VERSION : 12.1.0.2.0

GRID HOME VERSION : 12.2.0.1.0

ASM Diskgroups : +DATAC1, +RECOC1

 

Presumptions

————

* Oracle Home (12.1.0.2.0) is installed on the Target Server with latest patches

* Both Exadata Servers are reachable from the Network to Copy the backup files

1. Take RMAN Backup of Source Database on X4-2 hardware

rman target / nocatalog

run

{

allocate channel swx1 DEVICE TYPE DISK ;

allocate channel swx2 DEVICE TYPE DISK ;

allocate channel swx3 DEVICE TYPE DISK ;

allocate channel swx4 DEVICE TYPE DISK ;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/exabackups/rmanbkp/swx/autobackup_control_file_%F’;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

BACKUP DATABASE FORMAT ‘/exabackups/rmanbkp/swx/SWX_FULL_%s_%D_%M_%Y’ tag ‘SWX_DAILY_FULL_DB_BKP’;

BACKUP ARCHIVELOG ALL FORMAT ‘/exabackups/rmanbkp/swx/ARCH_%d_ALL_%s_%t’ tag ‘SWX_ARCH_LOG_BKP’;

}

SQL> create pfile=’/exabackups/rmanbkp/swx/swx_source.ora’ from spfile ;

2. Copy Backup from Source Server to Target Server

cd /exabackups/rmanbkp/swx

scp -r * oracle@qiibtstdbadm01.qiibonline.com:/u03/swx_today

3. Edit the Source pfile : swx_source.ora and change below parameters

Old pfile

———

swx1.__db_cache_size=9395240960

swx2.__db_cache_size=9395240960

swx1.__java_pool_size=33554432

swx2.__java_pool_size=33554432

swx1.__large_pool_size=67108864

swx2.__large_pool_size=67108864

swx2.__oracle_base=’/u02/app/oracle’#ORACLE_BASE set from environment

swx1.__oracle_base=’/u02/app/oracle’#ORACLE_BASE set from environment

swx1.__pga_aggregate_target=6006243328

swx2.__pga_aggregate_target=6006243328

swx1.__sga_target=11173625856

swx2.__sga_target=11173625856

swx1.__shared_io_pool_size=0

swx2.__shared_io_pool_size=0

swx1.__shared_pool_size=1577058304

swx2.__shared_pool_size=1577058304

swx1.__streams_pool_size=0

swx2.__streams_pool_size=0

*._client_enable_auto_unregister=TRUE

*.archive_lag_target=0

*.audit_file_dest=’/u02/app/oracle/admin/swx/adump’ ####### Change to Target Directory and Create teh Directory on OS ###########

*.audit_trail=’db’

*.cluster_database=TRUE ######## Set to FALSE ############

*.compatible=’11.2.0.4.0′ ######## Change to 12.1.0.2.0 ##########

*.control_files=’+datac1/swx/controlfile/current.346.853755085′,’+RECOC1/swx/controlfile/current.4208.853579009′ ########## Hash this #########

*.cpu_count=2

*.db_block_size=8192

*.db_create_file_dest=’+DATAC1′

*.db_create_online_log_dest_1=’+RECOC1′

*.db_domain=”

*.db_name=’swx’

*.db_recovery_file_dest=’+RECOC1′

*.db_recovery_file_dest_size=858993459200

*.diagnostic_dest=’/u02/app/oracle’ ########## Change to new target Oracle Base directory ############

*.dispatchers='(PROTOCOL=TCP) (SERVICE=swxXDB)’

swx2.instance_number=2

swx1.instance_number=1

*.java_jit_enabled=TRUE

*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SWX’

*.log_archive_format=’%t_%s_%r.arc’

swx1.log_archive_format=’%t_%s_%r.arc’

swx2.log_archive_format=’%t_%s_%r.arc’

*.log_archive_max_processes=8

*.log_archive_min_succeed_dest=1

swx1.log_archive_trace=0

swx2.log_archive_trace=0

*.sga_max_size=17179869184

*.sga_target=17179869184

*.open_cursors=300

*.processes=1500

*.remote_listener=’exaprod-scan:1521′ ########## Change to SCAN listener of Target DB #########

*.remote_login_passwordfile=’exclusive’

*.resource_manager_cpu_allocation=48

*.resource_manager_plan=’DEFAULT_PLAN’

*.sessions=1655

*.standby_file_management=’AUTO’

swx2.thread=2

swx1.thread=1

swx1.undo_tablespace=’UNDOTBS1′

swx2.undo_tablespace=’UNDOTBS2′

*.instance_number=2

Save the parameters and save pfile as swx_target.ora

New pfile : swx_target.ora

———

swx1.__db_cache_size=9395240960

swx2.__db_cache_size=9395240960

swx1.__java_pool_size=33554432

swx2.__java_pool_size=33554432

swx1.__large_pool_size=67108864

swx2.__large_pool_size=67108864

swx2.__oracle_base=’/u02/app/oracle’#ORACLE_BASE set from environment

swx1.__oracle_base=’/u02/app/oracle’#ORACLE_BASE set from environment

swx1.__pga_aggregate_target=6006243328

swx2.__pga_aggregate_target=6006243328

swx1.__sga_target=11173625856

swx2.__sga_target=11173625856

swx1.__shared_io_pool_size=0

swx2.__shared_io_pool_size=0

swx1.__shared_pool_size=1577058304

swx2.__shared_pool_size=1577058304

swx1.__streams_pool_size=0

swx2.__streams_pool_size=0

*._client_enable_auto_unregister=TRUE

*.archive_lag_target=0

*.audit_file_dest=’/u03/app/oracle/admin/swx/adump’ ####### Change to Target Directory and Create teh Directory on OS ###########

*.audit_trail=’db’

*.cluster_database=FALSE ######## Set to FALSE ############

*.compatible=’12.1.0.2.0′ ######## Change to 12.1.0.2.0 ##########

#*.control_files=’+datac1/swx/controlfile/current.346.853755085′,’+RECOC1/swx/controlfile/current.4208.853579009′ ########## Hash this #########

*.cpu_count=2

*.db_block_size=8192

*.db_create_file_dest=’+DATAC1′

*.db_create_online_log_dest_1=’+RECOC1′

*.db_domain=”

*.db_name=’swx’

*.db_recovery_file_dest=’+RECOC1′

*.db_recovery_file_dest_size=858993459200

*.diagnostic_dest=’/u03/app/oracle’ ########## Change to new target Oracle Base directory ############

*.dispatchers='(PROTOCOL=TCP) (SERVICE=swxXDB)’

swx2.instance_number=2

swx1.instance_number=1

*.java_jit_enabled=TRUE

*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SWX’

*.log_archive_format=’%t_%s_%r.arc’

swx1.log_archive_format=’%t_%s_%r.arc’

swx2.log_archive_format=’%t_%s_%r.arc’

*.log_archive_max_processes=8

*.log_archive_min_succeed_dest=1

swx1.log_archive_trace=0

swx2.log_archive_trace=0

*.sga_max_size=17179869184

*.sga_target=17179869184

*.open_cursors=300

*.processes=1500

*.remote_listener=’qiibtst-scan:1521′ ########## Change to SCAN listener of Target DB #########

*.remote_login_passwordfile=’exclusive’

*.resource_manager_cpu_allocation=48

*.resource_manager_plan=’DEFAULT_PLAN’

*.sessions=1655

*.standby_file_management=’AUTO’

swx2.thread=2

swx1.thread=1

swx1.undo_tablespace=’UNDOTBS1′

swx2.undo_tablespace=’UNDOTBS2′

*.instance_number=2

4. Start the Instance on Target Database using swx_target.ora pfile

Make sure you export ORACLE_HOME and ORACLE_SID environment variables properly before beginning

SQL> startup nomount pfile=’/u03/swx_today/swx_source.ora’;

rman target /

RMAN> restore controlfile from ‘/u03/swx_today/control_file_SWX_853579011_20180215_4985_1’;

Starting restore at 18-MAR-18

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1140 instance=swx device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

output file name=+RECOC1/SWX/CONTROLFILE/current.654.971100501

Finished restore at 18-MAR-18

RMAN> alter database mount;

RMAN> catalog start with ‘/u03/swx_today’;

Starting implicit crosscheck backup at 18-MAR-18

allocated channel: ORA_DISK_1

allocated channel: ORA_DISK_2

allocated channel: ORA_DISK_3

allocated channel: ORA_DISK_4

Crosschecked 158 objects

Finished implicit crosscheck backup at 18-MAR-18

Starting implicit crosscheck copy at 18-MAR-18

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

Crosschecked 4 objects

Finished implicit crosscheck copy at 18-MAR-18

searching for all files in the recovery area

cataloging files…

no files cataloged

searching for all files that match the pattern /u03/swx_today

List of Files Unknown to the Database

=====================================

File Name: /u03/swx_today/ARCH_SWX_ALL_4979_968178229

File Name: /u03/swx_today/control_file_SWX_853579011_20180215_4985_1

File Name: /u03/swx_today/SWX_FULL_4975_15_02_2018

File Name: /u03/swx_today/ARCH_SWX_ALL_4981_968178230

File Name: /u03/swx_today/ARCH_SWX_ALL_4978_968178229

File Name: /u03/swx_today/ARCH_SWX_ALL_4982_968178255

File Name: /u03/swx_today/autobackup_control_file_c-133836929-20180215-02

File Name: /u03/swx_today/autobackup_control_file_c-133836929-20180215-00

File Name: /u03/swx_today/SWX_FULL_4974_15_02_2018

File Name: /u03/swx_today/autobackup_control_file_c-133836929-20180215-01

File Name: /u03/swx_today/swx_source.ora

File Name: /u03/swx_today/SWX_FULL_4976_15_02_2018

File Name: /u03/swx_today/ARCH_SWX_ALL_4980_968178230

File Name: /u03/swx_today/ARCH_SWX_ALL_4983_968178258

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files…

cataloging done

List of Cataloged Files

=======================

File Name: /u03/swx_today/ARCH_SWX_ALL_4979_968178229

File Name: /u03/swx_today/control_file_SWX_853579011_20180215_4985_1

File Name: /u03/swx_today/SWX_FULL_4975_15_02_2018

File Name: /u03/swx_today/ARCH_SWX_ALL_4981_968178230

File Name: /u03/swx_today/ARCH_SWX_ALL_4978_968178229

File Name: /u03/swx_today/ARCH_SWX_ALL_4982_968178255

File Name: /u03/swx_today/autobackup_control_file_c-133836929-20180215-02

File Name: /u03/swx_today/autobackup_control_file_c-133836929-20180215-00

File Name: /u03/swx_today/SWX_FULL_4974_15_02_2018

File Name: /u03/swx_today/autobackup_control_file_c-133836929-20180215-01

File Name: /u03/swx_today/SWX_FULL_4976_15_02_2018

File Name: /u03/swx_today/ARCH_SWX_ALL_4980_968178230

File Name: /u03/swx_today/ARCH_SWX_ALL_4983_968178258

List of Files Which Were Not Cataloged

=======================================

File Name: /u03/swx_today/swx_source.ora

RMAN-07517: Reason: The file header is corrupted

RMAN> restore database;

Starting restore at 18-MAR-18

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: SID=26 instance=swx device type=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 7.1.0.0

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00003 to +DATAC1/swx/datafile/undotbs1.281.853578943

channel ORA_DISK_1: restoring datafile 00004 to +RECOC1/swx/datafile/audit_tbs.2213.871200459

channel ORA_DISK_1: reading from backup piece /u03/swx_today/SWX_FULL_4975_15_02_2018

channel ORA_DISK_2: starting datafile backup set restore

channel ORA_DISK_2: specifying datafile(s) to restore from backup set

channel ORA_DISK_2: restoring datafile 00001 to +DATAC1/swx/datafile/system.285.853578943

channel ORA_DISK_2: restoring datafile 00002 to +DATAC1/swx/datafile/sysaux.278.853578943

channel ORA_DISK_2: reading from backup piece /u03/swx_today/SWX_FULL_4976_15_02_2018

channel ORA_DISK_3: starting datafile backup set restore

channel ORA_DISK_3: specifying datafile(s) to restore from backup set

channel ORA_DISK_3: restoring datafile 00005 to +DATAC1/swx/datafile/undotbs2.351.853579027

channel ORA_DISK_3: restoring datafile 00006 to +DATAC1/swx/datafile/swx.345.853583217

channel ORA_DISK_3: reading from backup piece /u03/swx_today/SWX_FULL_4974_15_02_2018

channel ORA_DISK_1: piece handle=/u03/swx_today/SWX_FULL_4975_15_02_2018 tag=SWX_DAILY_FULL_DB_BKP

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

channel ORA_DISK_2: piece handle=/u03/swx_today/SWX_FULL_4976_15_02_2018 tag=SWX_DAILY_FULL_DB_BKP

channel ORA_DISK_2: restored backup piece 1

channel ORA_DISK_2: restore complete, elapsed time: 00:00:15

channel ORA_DISK_3: piece handle=/u03/swx_today/SWX_FULL_4974_15_02_2018 tag=SWX_DAILY_FULL_DB_BKP

channel ORA_DISK_3: restored backup piece 1

channel ORA_DISK_3: restore complete, elapsed time: 00:04:45

Finished restore at 18-MAR-18

RMAN> recover database;

Starting recover at 18-MAR-18

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_SBT_TAPE_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=2 sequence=48891

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=43454

channel ORA_DISK_1: restoring archived log

archived log thread=2 sequence=48892

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=43455

channel ORA_DISK_1: reading from backup piece /u03/swx_today/ARCH_SWX_ALL_4983_968178258

channel ORA_DISK_1: piece handle=/u03/swx_today/ARCH_SWX_ALL_4983_968178258 tag=SWX_ARCH_LOG_BKP

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=+RECOC1/SWX/ARCHIVELOG/2018_03_18/thread_1_seq_43454.645.971100923 thread=1 sequence=43454

archived log file name=+RECOC1/SWX/ARCHIVELOG/2018_03_18/thread_2_seq_48891.642.971100923 thread=2 sequence=48891

channel default: deleting archived log(s)

archived log file name=+RECOC1/SWX/ARCHIVELOG/2018_03_18/thread_2_seq_48891.642.971100923 RECID=238985 STAMP=971100923

archived log file name=+RECOC1/SWX/ARCHIVELOG/2018_03_18/thread_2_seq_48892.639.971100923 thread=2 sequence=48892

channel default: deleting archived log(s)

archived log file name=+RECOC1/SWX/ARCHIVELOG/2018_03_18/thread_1_seq_43454.645.971100923 RECID=238987 STAMP=971100923

archived log file name=+RECOC1/SWX/ARCHIVELOG/2018_03_18/thread_1_seq_43455.648.971100923 thread=1 sequence=43455

channel default: deleting archived log(s)

archived log file name=+RECOC1/SWX/ARCHIVELOG/2018_03_18/thread_1_seq_43455.648.971100923 RECID=238988 STAMP=971100923

unable to find archived log

archived log thread=1 sequence=43456

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 03/18/2018 14:15:25

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 43456 and starting SCN of 21582106266

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00601: fatal error in recovery manager

RMAN-03004: fatal error during execution of command

ORA-03114: not connected to ORACLE

RMAN-03002: failure of sql statement command at 03/18/2018 14:15:42

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 2

ORA-00904: “I”.”UNUSABLEBEGINNING#”: invalid identifier

Process ID: 136136

Session ID: 1137 Serial number: 14577

sqlplus “/as sysdba”

Change control file information with new location after restore in pfile and start database again +RECOC1/SWX/CONTROLFILE/current.654.971100501

Also change CLUSTER_DATABASE to false in pfile

If you get any error like below error then copy the file to the dbs folder as init.ora

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file ‘/u03/app/oracle/product/12.1.0.2/dbhome_1/dbs/initswx.ora’

$ cp -p /u03/swx_today/swx_source.ora /u03/app/oracle/product/12.1.0.2/dbhome_1/dbs/initswx.ora

sqlplus “/as sysdba”

SQL> startup upgrade;

$ cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql

Argument list for [catctl.pl]

SQL Process Count n = 4

SQL PDB Process Count N = 0

Input Directory d = 0

Phase Logging Table t = 0

Log Dir l = 0

Script s = 0

Serial Run S = 0

Upgrade Mode active M = 0

Start Phase p = 0

End Phase P = 0

Log Id i = 0

Run in c = 0

Do not run in C = 0

Echo OFF e = 1

No Post Upgrade x = 0

Reverse Order r = 0

Open Mode Normal o = 0

Debug catcon.pm z = 0

Debug catctl.pl Z = 0

Display Phases y = 0

Child Process I = 0

catctl.pl version: 12.1.0.2.0

Oracle Base = /u03/app/oracle

Analyzing file catupgrd.sql

Log files in /u03/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin

catcon: ALL catcon-related output will be written to catupgrd_catcon_227787.lst

catcon: See catupgrd*.log files for output generated by scripts

catcon: See catupgrd_*.lst files for spool files, if any

Number of Cpus = 2

SQL Process Count = 4

——————————————————

Phases [0-73] Start Time:[2018_03_18 14:39:13]

——————————————————

Serial Phase #: 0 Files: 1 Time: 105s

Serial Phase #: 1 Files: 5 Time: 22s

Restart Phase #: 2 Files: 1 Time: 0s

Parallel Phase #: 3 Files: 18 Time: 4s

Restart Phase #: 4 Files: 1 Time: 0s

Serial Phase #: 5 Files: 5 Time: 10s

Serial Phase #: 6 Files: 1 Time: 9s

Serial Phase #: 7 Files: 4 Time: 4s

Restart Phase #: 8 Files: 1 Time: 0s

Parallel Phase #: 9 Files: 62 Time: 12s

Restart Phase #:10 Files: 1 Time: 0s

Serial Phase #:11 Files: 1 Time: 8s

Restart Phase #:12 Files: 1 Time: 0s

Parallel Phase #:13 Files: 91 Time: 4s

Restart Phase #:14 Files: 1 Time: 0s

Parallel Phase #:15 Files: 111 Time: 8s

Restart Phase #:16 Files: 1 Time: 0s

Serial Phase #:17 Files: 3 Time: 0s

Restart Phase #:18 Files: 1 Time: 0s

Parallel Phase #:19 Files: 32 Time: 8s

Restart Phase #:20 Files: 1 Time: 0s

Serial Phase #:21 Files: 3 Time: 4s

Restart Phase #:22 Files: 1 Time: 0s

Parallel Phase #:23 Files: 23 Time: 43s

Restart Phase #:24 Files: 1 Time: 0s

Parallel Phase #:25 Files: 11 Time: 17s

Restart Phase #:26 Files: 1 Time: 0s

Serial Phase #:27 Files: 1 Time: 0s

Restart Phase #:28 Files: 1 Time: 0s

Serial Phase #:30 Files: 1 Time: 0s

Serial Phase #:31 Files: 257 Time: 12s

Serial Phase #:32 Files: 1 Time: 0s

Restart Phase #:33 Files: 1 Time: 0s

Serial Phase #:34 Files: 1 Time: 3s

Restart Phase #:35 Files: 1 Time: 0s

Restart Phase #:36 Files: 1 Time: 0s

Serial Phase #:37 Files: 4 Time: 31s

Restart Phase #:38 Files: 1 Time: 0s

Parallel Phase #:39 Files: 13 Time: 27s

Restart Phase #:40 Files: 1 Time: 0s

Parallel Phase #:41 Files: 10 Time: 3s

Restart Phase #:42 Files: 1 Time: 0s

Serial Phase #:43 Files: 1 Time: 4s

Restart Phase #:44 Files: 1 Time: 0s

Serial Phase #:45 Files: 1 Time: 3s

Serial Phase #:46 Files: 1 Time: 1s

Restart Phase #:47 Files: 1 Time: 0s

Serial Phase #:48 Files: 1 Time: 82s

Restart Phase #:49 Files: 1 Time: 0s

Serial Phase #:50 Files: 1 Time: 22s

Restart Phase #:51 Files: 1 Time: 0s

Serial Phase #:52 Files: 1 Time: 16s

Restart Phase #:53 Files: 1 Time: 0s

Serial Phase #:54 Files: 1 Time: 192s

Restart Phase #:55 Files: 1 Time: 0s

Serial Phase #:56 Files: 1 Time: 37s

Restart Phase #:57 Files: 1 Time: 0s

Serial Phase #:58 Files: 1 Time: 69s

Restart Phase #:59 Files: 1 Time: 0s

Serial Phase #:60 Files: 1 Time: 295s

Restart Phase #:61 Files: 1 Time: 0s

Serial Phase #:62 Files: 1 Time: 24s

Restart Phase #:63 Files: 1 Time: 0s

Serial Phase #:64 Files: 1 Time: 0s

Serial Phase #:65 Files: 1 Time: 11s

Serial Phase #:66 Files: 1 Time: 25s

Serial Phase #:67 Files: 1 Time: 1s

Serial Phase #:68 Files: 1 Time: 0s

Serial Phase #:69 Files: 1 Time: 27s

——————————————————

Phases [0-73] End Time:[2018_03_18 14:58:16]

——————————————————

Grand Total Time: 1145s

*** WARNING: ERRORS FOUND DURING UPGRADE ***

Due to errors found during the upgrade process, the post

upgrade actions in catuppst.sql have not been automatically run.

*** THEREFORE THE DATABASE UPGRADE IS NOT YET COMPLETE ***

1. Evaluate the errors found in the upgrade logs

and determine the proper action.

2. Execute the post upgrade script as described in Chapter 3

of the Database Upgrade Guide.

REASON:

catuppst.sql unable to run in Database: swx Id: 0

ERRORS FOUND: during upgrade CATCTL ERROR COUNT=5

——————————————————

Identifier XDB 18-03-18 02:47:25 Script = /u03/app/oracle/product/12.1.0.2/dbhome_

ERROR = [ORA-01917: user or role ‘ANONYMOUS’ does not exist ORA-06512: at line 7

ORA-06512: at line 15

]

STATEMENT = [declare

already_revoked exception;

pragma exception_init(already_revoked,-01927);

procedure revoke_inherit_privileges(user in varchar2) as

begin

execute immediate ‘revoke inherit privileges on user ‘||

dbms_assert.enquote_name(user)||’ from public’;

exception

when already_revoked then null;

end;

begin

revoke_inherit_privileges(‘xdb’);

revoke_inherit_privileges(‘anonymous’);

end;]

——————————————————

——————————————————

Identifier XDB 18-03-18 02:47:25 Script = /u03/app/oracle/product/12.1.0.2/dbhome_

ERROR = [ORA-06512: at line 7 ORA-06512: at line 15

]

STATEMENT = [as above]

——————————————————

——————————————————

Identifier XDB 18-03-18 02:47:25 Script = /u03/app/oracle/product/12.1.0.2/dbhome_

ERROR = [ORA-06512: at line 15]

STATEMENT = [as above]

——————————————————

——————————————————

Identifier ORDIM 18-03-18 02:50:44 Script = /u03/app/oracle/product/12.1.0.2/dbhome_

ERROR = [ORA-20000: Oracle XML Database component not valid. Oracle XML Database must be installed and valid prior to Oracle Multimedia install, upgrade, downgrade, or patch.

ORA-06512: at line 3

]

STATEMENT = [begin

IF dbms_registry.is_valid(‘XDB’,dbms_registry.release_version) != 1 THEN

raise_application_error(-20000,

‘Oracle XML Database component not valid. ‘||

‘Oracle XML Database must be installed and valid prior to Oracle Multimedia install, upgrade, downgrade, or patch.’);

END IF;

end;]

——————————————————

——————————————————

Identifier ORDIM 18-03-18 02:50:44 Script = /u03/app/oracle/product/12.1.0.2/dbhome_

ERROR = [installed and valid prior to Oracle Multimedia install, upgrade, downgrade, or patch.

ORA-06512: at line 3

]

STATEMENT = [as above]

——————————————————

——————————————————

Identifier ORDIM 18-03-18 02:50:44 Script = /u03/app/oracle/product/12.1.0.2/dbhome_

ERROR = [patch. ORA-06512: at line 3

]

STATEMENT = [as above]

——————————————————

——————————————————

Identifier ORDIM 18-03-18 02:50:44 Script = /u03/app/oracle/product/12.1.0.2/dbhome_

ERROR = [ORA-06512: at line 3]

STATEMENT = [as above]

——————————————————

LOG FILES: (catupgrd*.log)

Upgrade Summary Report Located in:

/u03/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/swx/upgrade/upg_summary.log

Grand Total Upgrade Time: [0d:0h:19m:5s]

sqlplus “/as sysdba”

SQL> @utlrp.sql

SQL> select * from dba_registry;

Verify all components are valid

OWB OWB 11.2.0.4.0 VALID 18-MAR-2018 15:02:31 SERVER SYS OWBSYS DBMS_OWB.VALIDATE

EM Oracle Enterprise Manager 11.2.0.4.0 VALID 24-AUG-2013 11:58:27 SERVER SYS SYSMAN

AMD OLAP Catalog 11.2.0.4.0 OPTION OFF 18-MAR-2018 14:46:55 SERVER SYS OLAPSYS CWM2_OLAP_INSTALLER.VALIDATE_CWM2_INSTALL

SDO Spatial 12.1.0.2.0 VALID 18-MAR-2018 15:02:30 SERVER SYS MDSYS VALIDATE_SDO MDDATA,MDSYS,SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN_USR

ORDIM Oracle Multimedia 12.1.0.2.0 VALID 18-MAR-2018 15:02:29 SERVER SYS ORDSYS VALIDATE_ORDIM ORDDATA,ORDPLUGINS,SI_INFORMTN_SCHEMA

XDB Oracle XML Database 12.1.0.2.0 VALID 18-MAR-2018 15:02:28 SERVER SYS XDB DBMS_REGXDB.VALIDATEXDB XS$NULL

CONTEXT Oracle Text 12.1.0.2.0 VALID 18-MAR-2018 15:02:27 SERVER SYS CTXSYS VALIDATE_CONTEXT

OWM Oracle Workspace Manager 12.1.0.2.0 VALID 18-MAR-2018 15:02:25 SERVER SYS WMSYS VALIDATE_OWM

CATALOG Oracle Database Catalog Views 12.1.0.2.0 VALID 18-MAR-2018 15:02:23 SERVER SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATALOG

CATPROC Oracle Database Packages and Types 12.1.0.2.0 VALID 18-MAR-2018 15:02:23 SERVER SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATPROC APPQOSSYS,DBSNMP,DIP,GSMADMIN_INTERNAL,ORACLE_OCM,OUTLN,SYSTEM

JAVAVM JServer JAVA Virtual Machine 12.1.0.2.0 VALID 18-MAR-2018 15:02:26 SERVER SYS SYS INITJVMAUX.VALIDATE_JAVAVM

COMP_ID COMP_NAME VERSION STATUS MODIFIED NAMESPACE CONTROL SCHEMA PROCEDURE STARTUP PARENT_ID OTHER_SCHEMAS

—————————— ————————————————————————————————————————————————————————————————————————————————————— —————————— ———– ——————– —————————— ——————————————————————————————————————————– ——————————————————————————————————————————– ————————————————————- ——– —————————— —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-

XML Oracle XDK 12.1.0.2.0 VALID 18-MAR-2018 15:02:27 SERVER SYS SYS XMLVALIDATE

CATJAVA Oracle Database Java Packages 12.1.0.2.0 VALID 18-MAR-2018 15:02:27 SERVER SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATJAVA

APS OLAP Analytic Workspace 12.1.0.2.0 VALID 18-MAR-2018 15:02:29 SERVER SYS SYS APS_VALIDATE

XOQ Oracle OLAP API 12.1.0.2.0 VALID 18-MAR-2018 15:02:30 SERVER SYS SYS XOQ_VALIDATE

RAC Oracle Real Application Clusters 12.1.0.2.0 VALID 18-MAR-2018 15:02:31 SERVER SYS SYS DBMS_CLUSTDB.VALIDATE

16 rows selected.

SQL>