Tag Archives: PDB

Downgrading the Oracle Database Non-CDB or CDB

Downgrade the Oracle Database

In Oracle You have option to downgrade the Oracle database. Suppose you upgraded the Oracle database from 11g to 12c database now you want to downgrade.
but if you change the compatibility parameter of your oracle database from 11.2.0 to 12.1.0 then you cannot able to downgrade. only method is logical backup expdp or impdp.

If your database compatibility is same lower than 12.1.0 then you can follows the downgrade steps of the database.

Note:
1. If you are downgrading to Oracle Database 11g Release 2 (11.2.0.2),(11.2.0.3) or (11.2.0.4), then the COMPATIBLE initialization parameter must be set to 11.2.0 or lower.
2. Downgrading to release 11.2.0.1 is not supported.
3. Downgrading to Oracle Database 11g Release 1 (11.1.0.7), then the COMPATIBLE initialization parameter must be set to 11.1.0, its supported.

Following are the steps in three phases
Pre-Downgrade Steps
DOWNGRADE Steps
POSTDOWNGRADE STEPS

Following are the Pre-Downgrade Steps

1. Take a full backup of Oracle Database before downgrading.

Pre-Downgrade Steps for Oracle Database

2. If you are downgrade the CDB or any PDB in oracle 12.1.0.2, you need to apply PSU4 patch. (MOS Note 756671.1)

3. If you are downgrading to Oracle 11.1, took backup of the password of database link.
Note: its better to take the database link in all cases

-- Export database link
expdp system/manager FULL=Y directory=dpump_dir dumpfile=saved_dblinks.dmp INCLUDE=DB_LINK;

-- Import database link
impdp system/manager directory=dpump_dir dumpfile=saved_dblinks.dmp;

4. Disable the Oracle Database vault service if its enabled.

Disable the Oracle Database vault
--Connect with DB owner
sqlplus dv_owner
-- Disable the database vault
EXEC DBMS_MACADM.DISABLE_DV;
--restart the database
shutdown immediate
startup

Enable the database vault
CONNECT dv_owner
EXEC DBMS_MACADM.ENABLE_DV;
--Label security must be enabled of database vault.
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';
--Enable label secuirty
EXEC LBACSYS.CONFIGURE_OLS;
EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;
-- Restart the database
shutdown immediate;
startup

5. If your database uses Oracle Label Security

If downgraded to 11.2 or older then run the downgrade script

-- Check its label security is enabled
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';
-- if yes then run the following script in case of downgrade to 11.2 or older version.
@ORACLE_HOME/rdbms/admin/olspredowngrade.sql

Note: if you are downgrading release 12.1.0.2 to release 12.1.0.1. then donot do anything

6. If you already enabled unified auditing.

--backup the unified table
CREATE TABLE UA_DATA AS (SELECT * FROM V$UNIFIED_AUDIT_TRAIL);

--clean the table
EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE);

7. Check the timezone

-- Check the timezone of both the databases. if it matched then fine otherwise you need to copy the time zone from upper to lower version.
select * from V$TIMEZONE_FILE;
-- Copy the time zone files from the current Oracle home to the Orache home to which you are downgrading.
%ORACLE_HOME%\oracore\zoneinfo\timezlrg_18.dat
%ORACLE_HOME%\oracore\zoneinfo\timezone_18.dat

8. If Oracle Enterprise manager is configured. Drop the user SYSMAN

DROP USER sysman CASCADE;

Following are the Downgrading a CDB or Non-CDB Oracle Database Steps

Suppose downgrade the oracle 12c latest version to 12.1.0.1

1. Log in to the system with new oracle home.

2. Go to the directory ORACLE_HOME/rdbms/admin

cd %ORACLE_HOME%\rdbms\admin
sqlplus / as sysdba

3. Start the instance in downgrade mode.

-- For Non CDB
startup downgrade pfile=pfile_name
-- For CDB
startup downgrade pfile=pfile_name
alter pluggable database all open downgrade;

4. Run catdwgrd.sql script for downgrade for CDB or Non CDB database.

-- For Non CDB
SPOOL downgrade.log
SQL> @catdwgrd.sql
spool off

-- For a CDB:
ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d $ORACLE_HOME/rdbms/admin -e -b catdwgrd -l -r catdwgrd.sql

In CDB example catcon.pl is run with catdwgrd.
-d parameter tells catcon.pl where to find catdwgrd.
-l parameter specifies the output directory for log files
-r parameter to run the two scripts together.

5. Shutdown the database after executing catdwgrd.sql script

Shutdown immediate

6. Change the environment variable to old oracle home which you downgraded the database.

ORACLE_HOME
PATH

7. In case of windows system you need to drop the exisiting service and create new from downgraded home of database

-- Stop the ORCL database service
NET STOP OracleServiceORCL
-- Drop the ORCL service
ORADIM -DELETE -SID ORCL
-- Create new ORCL Service
ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS -STARTMODE MANUAL -PFILE ORACLE_HOME\DATABASE\INITSID.ORA

8. Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges.

-- For Non CDB
CONNECT / AS SYSDBA
STARTUP UPGRADE

--For CDB
connect / as sysdba
startup database mount;
alter database open upgrade;
alter pluggable database all open upgrade;

9. Go to ORACLE_HOME/rdbms/admin and connect with database

--For a non-CDB:
SPOOL reload.log
@catrelod.sql
SPOOL off

-- For CDB
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b catrelod -d '''.''' catrelod.sql

10. If you downgrading to Oracle Database 11g Release 1 (11.1.0.7) then you run the @apxrelod.sql script.

@apxrelod.sql

Note: Script used for reload Oracle Application Express.

11. Shut down and restart the instance for normal operation:

SHUTDOWN IMMEDIATE
STARTUP

12. IF Oracle Label Security is configured then copy the script olstrig.sql from upper version oracle home to new downgraded oracle home and run it.

@olstrig.sql

Note: Run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies.

13. Run the utlrp.sql script

-- For a non-CDB:
@utlrp.sql;

-- For CDB
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

POSTDOWNGRADE STEPS

1. Recreate the listener from downgraded Oracle home.
Note: Copy paste the network folder from old home to downgrade home.

NETCA

2. If enabled then Re-enabling Oracle Database Vault after Downgrading Oracle Database.

Enable the database vault
CONNECT dv_owner
EXEC DBMS_MACADM.ENABLE_DV;
--Label security must be enabled of database vault.
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';
--Enable label secuirty
EXEC LBACSYS.CONFIGURE_OLS;
EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;
-- Restart the database
shutdown immediate;
startup

3. If configured then Restoring Oracle Enterprise Manager after Downgrading Oracle Database.

ORACLE_HOME/bin/emca -restore db

Advertisements

Starting and Stopping commands for RAC instances

Starting and Stopping commands for RAC instances

Starting and Stopping of RAC instance is managed from OS with srvctl command.
It provide different option at database , instance and service level to stop and start the database or instances present in RAC Environment.

Following are the command for Sart or Stop the database/instances:

Start or Stop the Instance of database:
command start/stop the Particular|all instances with services.

srvctl start/stop instance -db ORCL -node NODE1 -instance ORCL_1 -STARTOPTION|STOPOPTION
[OPEN | MOUNT | NOMOUNT | NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]

Start or stop all instance of database with database
It start/stop the cluster database with enable all instances and services

srvctl start|stop database -db ORCL -startoption|stopoption
[OPEN | MOUNT | NOMOUNT | NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]

Starting or stopping with SQLPLUS:
Note: you cannot start/stop multiple instance in SQLPLUS. Only current instance you can start or shutdown with sqlplus prompt:

sqlplus sys@orcl_1 as sysdba
shutdown immediate;
startup;

sqlplus sys@orcl_2 as sysdba
shutdown immediate;
startup;

Examples of start or stop with srvctl commands:

Check the status of instances in the database:

srvctl status database -db ORCL
Instance ORCL_1 is running on node NODE1
Instance ORCL_2 is running on node NODE2
Instance ORCL_3 is running on node NODE3

Stop the instance ORCL_1 with srvctl command

srvctl stop instance -db ORCL -node NODE1 -stopoption abort
OR
srvctl stop instance -db ORCL -instance ORCL_1 -stopption abort

Check the status of one instance is stopped in ORCL RAC:

srvctl status database -db ORCL
Instance ORCL_1 is not running on node NODE1
Instance ORCL_2 is running on node NODE2
Instance ORCL_3 is running on node NODE3

Stop all the instance then we used database option to stop:

srvctl stop database -db ORCL -stopoption transactional

Check the database status all instances must be stopped.

srvctl status database -db ORCL
Instance ORCL_1 is not running on node NODE1
Instance ORCL_2 is not running on node NODE2
Instance ORCL_3 is not running on node NODE3

Start the one instance of ORCL database:

srvctl start database -db ORCL -node NODE1
or
srvctl start database -instance ORCL_1

Check status node1 is started:

srvctl status database -db ORCL
Instance ORCL_1 is running on node NODE1
Instance ORCL_2 is not running on node NODE2
Instance ORCL_3 is not running on node NODE3

Start all the instances with database command:

srvctl start database -ORCL

Check status of all instances:

srvctl status database -db ORCL
Instance ORCL_1 is running on node NODE1
Instance ORCL_2 is running on node NODE2
Instance ORCL_3 is running on node NODE3

Manage the RAC pluggable database start/stop by services
Note: Need to assign the service to pluggable database for manage start or stop of PDB database.

1. Assign a service to pdb1 pluggable database :

srvctl add service -db raccdb -pdb pdb1 -service pdb1srv -serverpool sp1

2. For start or stop the pluggable database:

srvctl start service -db raccdb -service pdb1srv

srvctl stop service -db raccdb -service pdb1srv

3. Check the status for pluggable database:

srvctl status service -db raccdb -service pdb1arv

Manages Services in RAC Environment

Managed Services in RAC Oracle 12c

Services is used for managed the workload in Oracle database and simplified the way of connection with application and group of application.
Divide the workload with help of services like OLTP , Batch , Reporting to simplified the bottleneck when issued occurred.
srvctl command is used to managed the services.Services integrated with resource manager
Services also control parallel operation in rac , by default all instance is used for parallel operations in RAC.

Characteristics for service
Service Name
Service Edition
Service management policy – Manual when service not start when database is start , Automatic when database start service automatic start
Database role
Instance preference – Specify the instance preference
Server pool Assignment
Load balancing advisory – Run time connection load balancing to choose the
Connection load balancing goal –

Note: Default database name service identified by db_unique_name if not set then db_name or pdb_name is as default service.

Creating and Managing the Services in RAC Oracle 12c

Create a new Service for ORCL database.

srvctl add service -db orcl -service GL -preferred NODE1,NODE2,NODE3

Check the Configuration of the Service created.

srvclt config service -db orcl -service GL

Create a service with available and preferred options

srvctl add service -db orcl -service AL -preferred NODE1,NODE2 -available NODE3

Create a service “ETL” with server pool with singleton option so that it will work only on one instance. Policy is manual means it doesnot start or stop automatically adminstrator do it manually to stop and start service

srvctl add service -db orcl -service ETL -serverpool SP1 -cardinality SINGLTON -policy MANUAL

Create a service “TRAN” with uniform cardinality so that it run all the node present in serverpool. Choose policy to automatic so it automatic start or stop with database

srvctl add service -db orcl -service TRAN -serverpool SP2 -cardinality UNIFORM -policy AUTOMATIC

Start the service in RAC

srvctl start service -db orcl -service GL
srvctl start service -db orcl -service AL

Check the status of the service in RAC

srvctl status service -db orcl -service AL
Service AL is running on instance(s) node1

Stop the Service in RAC

srvctl stop service -db orcl -service AL
-- Stop service on particular instance only
srvctl stop service -db orcl -service GL -instance NODE1

Disable the service in RAC environment

srvctl disable service -db orcl -service AL -instance NODE1

Changing the preferred and available instance

srvctl modify service -db orcl -service AL -modifyconfig -perferred NODE1, NODE2 -available NODE3

Relocate the service from NOde1 to Node3

srvctl relocate service -db orcl -service AL -oldinst NODE1 -newinst Node3

Manage Service for PDB database in RAC Environment
It is dynamic service we are created, default service is the service which is created when pdb database is created, it name as same pdb name or pdb name plus domain name.

Create a Service for PDB database
Note: you create it with policy automatic then you donot need to start the pdb database everytime when instance started

srvctl add service -db contdb -pdb pdb1 -service pdb1srv -serverpool sp1 -cardinality singleton -policy manual

serctl add service -db contdb -pdb pdb2 -service pdb2srv -serverpool sp1 -cardinality uniform -policy automatic
automatic is default

Start the service of PDB database:

srvctl start service -db contdb -service pdb1srv

Check the Status of the Service

srvctl status service -db contdb
Service pdb1srv is running on nodes: NODE1
Service pdb2srv is running on nodes: NODE1,NODE2

Check Service from sqlplus

select name, con_id from cdb_services;

Remove the service

srvctl remove service -db contdb -service pdb1srv

Restricted Service Registration
Restricted Service Registration is only for local ip address/ network. (subnet address is same). valid node checking is enabled by default.

Check the default setting for SCAN Listener when configured

srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:

Restricted Service to registered nodes on scan listener:

srvctl modify scan_listener -update -invitednodes host1,host2,host3

Restricted Service with Registered subnet on Scan Listener:

srvclt modify scan_listener -update -invitedsubnets 192.168.0.0/24,10.10.10.0/24

Check again the Scan listener setting:

#srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
Registration invited nodes: host1,host2,host3
Registration invited subnets: 192.168.0.0/24,10.10.10.0/24
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
Registration invited nodes: host1,host2,host3
Registration invited subnets: 192.168.0.0/24,10.10.10.0/24
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
Registration invited nodes: host1,host2,host3
Registration invited subnets: 192.168.0.0/24,10.10.10.0/24
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:

Note: when you add register nodes and subnet then listener.ora file is updated with parameters REGISTRATION_INVITED_NODES_LISTENER_SCAN

Application Containers in Oracle 12c Database

Application Containers in Oracle Database 12c Release 2 (12.2)

An Application Containers is used for simplify the maintenance of the different application version. It easy to handle the different version of application changes in SQL with application container at database level.

Suppose one PDB1 database is working on Application version 10 and other PDB2 is want to work with latest version 12 of application. You can easily managed with application container feature of Oracle to sync the PDB2 with latest version 12 and donot sync the PDB1 database so it will work on old version of application.

An Application Container is composed by One Application Root having application metadata, link with zero or more Application Pluggable Databases,
zero or one Application Seed and zero or more Applications.

An Application Root is a Pluggable Database where the “Applications” are installed and maintained the application master data which is minimum required to run the application particular version.
On new version released of application has new master data which is maintained in new version. Later they can sync the PDB as customer wants to move to new version of application.
There may be only one Application Root per Application Container.
 
Creating an Application Root Container

1. For create an Application Root you have to be connected with sysdba privileges user:

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "SYS"
-- Create the application root container
SQL> create pluggable database ROOTAPP as application container admin user sysappadmin identified by sysappadmin123;
Pluggable database created.
-- Open the application root container
SQL> alter pluggable database ROOTAPP open;
Pluggable database altered.
-- Check the application root container is created
SQL> select con_id, name , open_mode, application_root from v$pdbs where application_root='YES';
CON_ID NAME OPEN_MODE APP
------- -------- ---------- ---
5 ROOTAPP READ WRITE YES

Creating an Application Pluggable Database
Application pluggable database is created when you are connected with application root container. then created PDB in this is called application pluggable database.

SQL> alter session set container=ROOTAPP;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
ROOTAPP
-- Create a pluggable database
SQL> create pluggable database PDB1APP admin user pdb1appadmin identified by pdb1appadmin123;
Pluggable database created.
--Opening the Application PDB
SQL> alter pluggable database PDB1APP open;
Pluggable database altered.
--Check the Application PDBs were created
SQL> select con_id, name , open_mode, application_root, application_pdb from v$pdbs;
CON_ID NAME OPEN_MODE APPLICATION_ROOT APPLICATION_PDB
-------- -------- ---------- ---------------- ---------------
5 ROOTAPP READ WRITE YES NO
6 PDB1APP READ WRITE NO YES

Create an application in Root Container

-- Check the current container
SQL> show con_name
CON_NAME
---------------
APPROOT

--Create a sales application on app container
alter pluggable database application SalesApp begin install '1.0';
Pluggable database altered.

-- Create a separate tablespace for application sales
CREATE TABLESPACE sales_app_ts DATAFILE 'location' SIZE 100M AUTOEXTEND ON NEXT 10M;
Tablespace created.

-- create user for sales application
create user sales identified by password1 default tablespace sales_app_ts;

-- Grant permission
GRANT CREATE SESSION, CREATE TABLE TO sales;
-- Create table for version 1.0
create table sales.tran SHARING=DATA (
id NUMBER,
name varchar2(100),
description VARCHAR2(100),
CONSTRAINT id_pk PRIMARY KEY (id)
);

-- insert data into the tran table
Insert into sales.tran values (1, 'shoes', ' Black color');

-- End the salesapp process
alter pluggable database application SalesApp end install;

-- Check the application is created
COLUMN app_name FORMAT A10
COLUMN app_version FORMAT A10
SELECT app_name,
app_version,
app_status
FROM dba_applications
WHERE app_name = 'SALESAPP';
APP_NAME APP_VERSIO APP_STATUS
---------- ---------- ------------
SALESAPP 1.0 NORMAL

SYNC the PDB database with application created

-- Switch to the application pdb database
ALTER SESSION SET container = PDB1APP;
SHOW CON_NAME
CON_NAME
----------------
PDB1APP

-- try to check the salesapp object
SQL> select * from sales.tran;
select * from sales.tran
*
ERROR at line 1:
ORA-00942: table or view does not exist

-- Sync the pdb with application root container
SQL> alter pluggable database application SALESAPP sync;
Pluggable database altered.

-- Again select the table
SQL> select name from sales.tran;
NAME
-------
SHOES

-- you can check the status from DBA_APP_PDB_STATUS view
ALTER SESSION SET container = PDB1APP;

COLUMN name FORMAT A20
COLUMN app_name FORMAT A20
COLUMN app_version FORMAT A10
SELECT con_uid,
app_name,
app_version,
app_status
FROM dba_app_pdb_status WHERE app_name = 'SALESAPP';

CON_UID    APP_NAME    APP_VERSIO APP_STATUS
---------- ----------- ---------- ------------
4291055883 SALESAPP    1.0        NORMAL

Upgrade the application present in application root container

-- connect with root container for modify the application
ALTER SESSION SET container = APPROOT;
-- Fire the command for upgrade the application
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP BEGIN UPGRADE '1.0' TO '2.0';
-- Suppose create one more table
create table sales.region ( name varchar(100));
-- insert data into region table
insert into sales.region ('NORTH');
-- end the upgrade process
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP END UPGRADE;

For use the upgraded application in Pluggable database need to sync the PDB database again

-- Connect with pdb of app container
alter session set container=PDB1APP;

-- sync with upgraded version
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP SYNC;

Uninstall the application from root application container

-- first start the uninstall command
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP BEGIN UNINSTALL;
-- Drop the created user
drop user sales cascade;
-- Drop the tablespace assoicated with user
Drop tablespace INCLUDING CONTENTS AND DATAFILES;
-- End the uninstall command:
ALTER PLUGGABLE DATABASE APPLICATION SALESAPP END UNINSTALL;

Note: for removed from application pdb you need to again sync the pdb database