Author Archives: SandeepSingh DBA

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 9 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Preupgrade tool before upgrade the PDB or CDB Example

Run Preupgrade tool before upgrade the PDb or CDB example

Syntax on 12c version of Preupgrade

$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home
/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir]

FILE|TERMINAL means File save output in file and in terminal it print on terminal.
TEXT|XML means Output type in text or xml file
DIR means output directory otherwise default location is ORACLE_HOME/cfgtoollogs/dbunique_name/preupgrade
-c include of PDBs databases inside CDB which you want to execute. in windows use double quote “PDB1 PDB2” or linux single quote ‘PDB1 PDB2’
-C Exclude the PDBs database with Capital C
-oh Oracle Home location
-sid oracle SID

Note:
1. Before run the command check that PDBs or CDB must be in OPEN State.
2. Before upgrade check CDB already have name same PDB otherwise rename first that pdb and then upgrade it.

Example for running PREUPGRADE utity from 12.1.0.1 to 12.2.0.1

1. Following are the home location

OLD HOME
SET ORACLE_HOME=C:\Oracle\12.1.0\dbhome_1
SET PATH=%ORACLE_HOME%\bin;%ORACLE_HOME%\jdk\bin;%PATH%
SET ORACLE_SID=ORCL

NEW HOME:
SET ORACLE_HOME=C:\oracle12c\product\12.2.0\dbhome_1
SET PATH=%ORACLE_HOME%\bin;%ORACLE_HOME%\jdk\bin;%PATH%
SET ORACLE_SID=ORCL

2. Set the OLD home environment variable and run the preupgrade command on windows:

java -jar C:\oracle12c\product\12.2.0\dbhome_1\rdbms\admin\preupgrade.jar TERMINAL TEXT -c "PDB2"

Example of Output:

C:\Windows\system32>java -jar C:\oracle12c\product\12.2.0\dbhome_1\rdbms\admin\p
reupgrade.jar TERMINAL TEXT -c "PDB2"

Output
Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0

Upgrade-To version: 12.2.0.1.0
=======================================
Status of the database prior to upgrade
=======================================

Database Name: ORCL
Container Name: PDB2
Container ID: 3
Version: 12.1.0.1.0
Compatible: 12.1.0.0.0
Blocksize: 8192
Platform: Microsoft Windows x86 64-bit
Timezone File: 18
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: EE

Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] OPTION OFF
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Oracle Application Express [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID

==============
BEFORE UPGRADE
==============
Run /preupgrade_fixups_PDB2.sql to complete all
of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.

REQUIRED ACTIONS
================
+ Adjust TABLESPACE SIZES as needed.
Auto 12.2.0.1.0
Tablespace Size Extend Min Size Action
---------- ---------- -------- ---------- ------

EXAMPLE 359 MB DISABLED 322 MB None
SYSAUX 640 MB ENABLED 1543 MB None
SYSTEM 270 MB ENABLED 773 MB None
TEMP 20 MB DISABLED 150 MB Extend

Note that 12.2.0.1.0 minimum sizes are estimates.
If you plan to upgrade multiple pluggable databases concurrently,
then you must ensure that the UNDO tablespace size is equal to at least
the number of pluggable databases that you upgrade concurrently,
multiplied by that minimum. Failing to allocate sufficient space can
cause the upgrade to fail

RECOMMENDED ACTIONS
===================
+ (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Dictionary statistics do not exist or are stale (not up-to-date).

Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.

For information on managing optimizer statistics, refer to the 12.1.0.1
Oracle Database SQL Tuning Guide.

+ Please make sure that all the MVs are refreshed and sys.sumdelta$
becomes empty before doing upgrade, unless you have strong business
reasons not to do so. You can use dbms_mview.refresh() to refresh the
MVs except those stale ones to be kept due to business need. If there
are any stale MVs depending on changes in sys.sumdelta$, do not truncate
it, because doing so will cause wrong results after refresh.

There is one or more non-fresh MV in the database or sumdelta$ is not
empty.

Oracle recommends that all materialized views (MV's) are refreshed
before upgrading the database because this will clear the MV logs and
the sumdelta$ table, and make the UPGRADE process faster. If you choose
to not refresh some MVs, the change data for those MV's will be carried
through the UPGRADE process. After UPGRADE, you can refresh the MV's and
MV incremental refresh should work in normal cases.

INFORMATION ONLY
================
+ Consider upgrading APEX manually, before the database upgrade.

The database contains APEX version 4.2.0.00.27 and will need to be
upgraded to at least version 5.0.4.00.12.

To reduce database upgrade time, you can upgrade APEX manually before
the database upgrade. Refer to My Oracle Support Note 1088970.1 for
information on APEX installation upgrades.

=============
AFTER UPGRADE
=============

Run /postupgrade_fixups_PDB2.sql to complete all
of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.

REQUIRED ACTIONS
================
None

RECOMMENDED ACTIONS
===================
+ If you use the -T option for the database upgrade, then run
$ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,
to VALIDATE and UPGRADE any user tables affected by changes to
Oracle-Maintained types.

There are user tables dependent on Oracle-Maintained object types.

If the -T option is used to set user tablespaces to READ ONLY during the
upgrade, user tables in those tablespaces, that are dependent on
Oracle-Maintained types, will not be automatically upgraded. If a type
is evolved during the upgrade, any dependent tables need to be
re-validated and upgraded to the latest type version AFTER the database
upgrade completes.

+ Upgrade the database time zone version using the DBMS_DST package.

The database is using timezone datafile version 18 and the target
12.2.0.1.0 database ships with timezone datafile version 26.

Oracle recommends using the most recent timezone data. For further
information, refer to My Oracle Support Note 1585343.1.

+ (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Oracle recommends gathering dictionary statistics after upgrade.

Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a
database upgrade, statistics need to be re-gathered as there can now be
tables that have significantly changed during the upgrade or new tables
that do not have statistics gathered yet.

+ Gather statistics on fixed objects two weeks after the upgrade using the
command:

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

This recommendation is given for all preupgrade runs.

Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.

Preupgrade generated files:
C:\Oracle\cfgtoollogs\orcl\preupgrade\preupgrade_fixups.sql
C:\Oracle\cfgtoollogs\orcl\preupgrade\postupgrade_fixups.sql

Run the Preupgrade fixups command on PDB2 database

SQL> alter session set container=PDB2;
Session altered.

SQL> @C:\Oracle\cfgtoollogs\orcl\preupgrade\preupgrade_fixups.sql;
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 1
Generated on: 2018-05-26 11:33:56

For Source Database: ORCL
Source Database Version: 12.1.0.1.0
For Upgrade to Version: 12.2.0.1.0

Executing in container: PDB2
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
dictionary_stats Passed None
mv_refresh Failed Manual fixup recommended.
apex_upgrade_msg Failed Manual fixup recommended.
PL/SQL procedure successfully completed.
Session altered.

Error with providing the wrong PDB or in Single quote in window

C:\Windows\system32>java -jar C:\oracle12c\product\12.2.0\dbhome_1\rdbms\admin\p
reupgrade.jar TERMINAL TEXT -c 'pdb1'
Error - Could not obtain the list of PDBS, failed query: select upper(name)||'@'
||OPEN_MODE from v$containers where upper(name) in (''PDB1'')

C:\Windows\system32>java -jar C:\oracle12c\product\12.2.0\dbhome_1\rdbms\admin\p
reupgrade.jar TERMINAL TEXT -c 'pdb2'
Error - Could not obtain the list of PDBS, failed query: select upper(name)||'@'
||OPEN_MODE from v$containers where upper(name) in (''PDB2'')

Advertisements

Pluggable Database Saved State in Oracle

Pluggable Database Saved State in Oracle

Saved State is a feature in 12.1.0.2 for saved the OPEN state of all PDBS when CDB is restart.
It will bring PDBs to same state if it open then it bring the PDB in Open State or if PDB is closed when CDB restart then it bring PDB in closed state.
open the database in READ-WRITE/READ-ONLY mode it save state.

Check the Save State for PDBS

select a.name,b.state from v$pdbs a , dba_pdb_saved_states b where a.con_id = b.con_id;

SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

Note: When you unplug and plug in the database, this saved state will be lost.

Save the existing State of PDB

alter pluggable database PDB1 save state;
Pluggable database altered.

Discard or unsave the existing state of PDB

alter pluggable database HYDB discard state;
Pluggable database altered.

Show the pdbs

Show pdbs

Rename the PDB database in Oracle

Rename the PDB database

Rename the PDB1 database to PDB2 in the following example. Following are the steps involved for PDB1 to PDB2 rename:

1. Check the PDB present in Container database.

select name from v$pdbs;

2. Open the database in Restricted Mode.

alter pluggable database PDB1 close;
alter pluggable database PDB1 open restricted;

3. Check database in restricted Mode.

select con_id, name, open_mode,restricted from v$containers;

CON_ID NAME OPEN_MODE RES
---------- ------------------------------ ---------- ---
1 CDB$ROOT READ WRITE NO
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE YES

show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE YES

4. Rename the PDB1 database by connecting it.

alter session set container=PDB1;
alter pluggable database rename global_name to PDB2;

Note: Database name is rename but for naming convention you can move all datafiles to PDB2 folder for better understanding.

5. Make a new directory with name PDB2 in Oradata folder and check datafiles path
Note: ORCL is our CDB database

mkdir C:\oracle\oradata\orcl\pdb2

SQL> show con_name
CON_NAME
---------
PDB2

select file_name from dba_data_files;
FILE_NAME
----------------------------------------
C:\ORACLE\ORADATA\ORCL\PDB1\EXAMPLE01.DBF
C:\ORACLE\ORADATA\ORCL\PDB1\SAMPLE_SCHEMA_USERS01.DBF
C:\ORACLE\ORADATA\ORCL\PDB1\SYSAUX01.DBF
C:\ORACLE\ORADATA\ORCL\PDB1\SYSTEM01.DBF

6. Move the datafile from PDB1 folder to PDB2 folder.

alter database move datafile 'C:\ORACLE\ORADATA\ORCL\PDB1\EXAMPLE01.DBF' to 'C:\ORACLE\ORADATA\ORCL\PDB2\EXAMPLE01.DBF';
alter database move datafile 'C:\ORACLE\ORADATA\ORCL\PDB1\SAMPLE_SCHEMA_USERS01.DBF' to 'C:\ORACLE\ORADATA\ORCL\PDB2\SAMPLE_SCHEMA_USERS01.DBF';
alter database move datafile 'C:\ORACLE\ORADATA\ORCL\PDB1\SYSAUX01.DBF' to 'C:\ORACLE\ORADATA\ORCL\PDB2\SYSAUX01.DBF';
alter database move datafile 'C:\ORACLE\ORADATA\ORCL\PDB1\SYSTEM01.DBF' to 'C:\ORACLE\ORADATA\ORCL\PDB2\SYSTEM01.DBF';

7. Check Create and Drop the temp file.

SQL> select file_name from dba_Temp_files;
FILE_NAME
---------------------------------------------
C:\ORACLE\ORADATA\ORCL\PDB1\PDB1_TEMP01.DBF

alter tablespace TEMP add tempfile 'C:\ORACLE\ORADATA\ORCL\PDB2\PDB2_TEMP01.DBF' size 20M reuse;

alter database tempfile 'C:\ORACLE\ORADATA\ORCL\PDB1\PDB1_TEMP01.DBF' drop including datafiles;

8. Check the PDB database.

select name, open_mode from v$pdbs;

NAME OPEN_MODE
------------------ ----------
PDB2 READ WRITE

9. Close the PDB2 database and open in normal mode.

alter pluggable database pdb2 close;
alter pluggable database pdb2 open;
-- to save the state in UPPER VERSION 12.1.0.2
alter pluggable database pdb2 save state;

Configure SSL and TSL in Oracle Database with example

Configure SSL and TSL in Oracle Database with example

SSL OR TSL is the method used for Oracle Advanced Security to secure the connection between Client or Server.

Note: SSL was developed by Netscape Communications Corporation, the Internet Engineering Task Force (IETF) took over SSL and renamed it Transport Layer Security (TLS).

Oracle Advanced Security feature

Authentication process with help of SSL or TSL as follows in Oracle:
1. On a client, the user makes a connection to the server by using SSL.
2. SSL verified the connection between the client and the server.
3. If connection is successful, the db server check the user has the authorization to access the database.
4. Then connection is established.

Install Oracle Advanced Security on both the client and server

Create and Configure the Wallet on Server

1. Create the Wallet directory on Operating system.

mkdir wallet_location

Example
c:\oracle>mkdir wallet

2. Go to the directory location.

cd wallet_location

Example
cd C:\oracle\wallet

3. Run orapki command to create the initial wallet.

orapki wallet create -wallet wallet_location -auto_login -pwd

Example
C:\Oracle\wallet>orapki wallet create -wallet C:\oracle\wallet -auto_login -pwd
sys123456
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.

4. It will create two files in the directory.

$dir
cwallet.sso
ewallet.p12

Example
C:\Oracle\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\Oracle\wallet
17-05-2018 07:47 .
17-05-2018 07:47 ..
17-05-2018 07:47 2,901 cwallet.sso
17-05-2018 07:47 0 cwallet.sso.lck
17-05-2018 07:47 2,856 ewallet.p12
17-05-2018 07:47 0 ewallet.p12.lck
4 File(s) 5,757 bytes
2 Dir(s) 27,854,073,856 bytes free

5. Create a self-signed certificate.
Note: Command will generate both a user certificate and the CA root certificate.

orapki wallet add -wallet wallet_location -dn "CN=server" -keysize 512 -self_signed -validity 365 -pwd

Example
C:\Oracle\wallet>orapki wallet add -wallet C:\Oracle\wallet -dn "CN=server" -key
size 512 -self_signed -validity 365 -pwd sys123456
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.

C:\Oracle\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\Oracle\wallet
17-05-2018 07:47 .
17-05-2018 07:47 ..
17-05-2018 07:48 4,405 cwallet.sso
17-05-2018 07:47 0 cwallet.sso.lck
17-05-2018 07:48 4,360 ewallet.p12
17-05-2018 07:47 0 ewallet.p12.lck
4 File(s) 8,765 bytes
2 Dir(s) 27,857,510,400 bytes free

6. Export the CA Certificate.

orapki wallet export -wallet wallet_location -dn "CN=server" -cert server_ca.cert

Example
C:\Oracle\wallet>orapki wallet export -wallet C:\Oracle\wallet -dn "CN=server" -cert server_ca.cert
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.

C:\Oracle\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\Oracle\wallet
17-05-2018 07:49 .
17-05-2018 07:49 ..
17-05-2018 07:48 4,405 cwallet.sso
17-05-2018 07:47 0 cwallet.sso.lck
17-05-2018 07:48 4,360 ewallet.p12
17-05-2018 07:47 0 ewallet.p12.lck
17-05-2018 07:49 427 server_ca.cert
5 File(s) 9,192 bytes
2 Dir(s) 27,857,936,384 bytes free

Create and Configure the wallet on Client

1. Create the Wallet directory on Operating system.

mkdir wallet_location

Example
C:\oraclient>mkdir wallet

2. Go to the directory location.

cd wallet_location

Example
C:\oraclient>cd wallet
C:\oraclient\wallet>

3. Run orapki command to create the initial wallet.

orapki wallet create -wallet wallet_location -auto_login -pwd

Example:
C:\oraclient\wallet>orapki wallet create -wallet C:\oraclient\wallet -auto_login -pwd sys123456
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.

4. It will create two files in the directory.

$dir
cwallet.sso
ewallet.p12

Example
C:\oraclient\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\oraclient\wallet
16-05-2018 21:42 .
16-05-2018 21:42 ..
16-05-2018 21:42 2,901 cwallet.sso
16-05-2018 21:42 0 cwallet.sso.lck
16-05-2018 21:42 2,856 ewallet.p12
16-05-2018 21:42 0 ewallet.p12.lck
4 File(s) 5,757 bytes
2 Dir(s) 27,907,039,232 bytes free

5. Create a self-signed certificate.
Note: Command will generate both a user certificate and the CA root certificate.

orapki wallet add -wallet wallet_location -dn "CN=client" -keysize 512 -self_signed -validity 365 -pwd

Example:
C:\oraclient\wallet>orapki wallet add -wallet C:\oraclient\wallet -dn "CN=client
" -keysize 512 -self_signed -validity 365 -pwd sys123456
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.

C:\oraclient\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\oraclient\wallet
16-05-2018 21:42 .
16-05-2018 21:42 ..
16-05-2018 21:45 4,397 cwallet.sso
16-05-2018 21:42 0 cwallet.sso.lck
16-05-2018 21:45 4,352 ewallet.p12
16-05-2018 21:42 0 ewallet.p12.lck
4 File(s) 8,749 bytes
2 Dir(s) 27,884,232,704 bytes free

6. Export the CA Certificate.

orapki wallet export -wallet wallet_location -dn "CN=client" -cert client_ca.cert

Example
C:\oraclient\wallet>orapki wallet export -wallet C:\oraclient\wallet -dn "CN=cli
ent" -cert client_ca.cert
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.

C:\oraclient\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\oraclient\wallet
16-05-2018 21:47 .
16-05-2018 21:47 ..
16-05-2018 21:47 427 client_ca.cert
16-05-2018 21:45 4,397 cwallet.sso
16-05-2018 21:42 0 cwallet.sso.lck
16-05-2018 21:45 4,352 ewallet.p12
16-05-2018 21:42 0 ewallet.p12.lck
16-05-2018 21:47 0 wallet_location.lck
6 File(s) 9,176 bytes
2 Dir(s) 27,882,811,392 bytes free

Final Steps to Exchange the certificate in between Client and Server

1. Transfer the exported Certificate from Server to client and Client to Server with FTP.

2. Import the Server Certificate to client system.

orapki wallet add -wallet wallet_location -trusted_cert -cert server_ca.cert -pwd

Example
C:\oraclient\wallet>orapki wallet add -wallet C:\oraclient\wallet -trusted_cert -cert server_ca.cert -pwd sys123456
Oracle PKI Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

C:\oraclient\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\oraclient\wallet
17-05-2018 07:50 .
17-05-2018 07:50 ..
16-05-2018 21:56 427 client_ca.cert
17-05-2018 07:51 5,445 cwallet.sso
17-05-2018 07:51 5,368 ewallet.p12
17-05-2018 07:49 427 server_ca.cert
4 File(s) 11,667 bytes
2 Dir(s) 28,018,552,832 bytes free

3. Import the Client Certificate to Server system.

orapki wallet add -wallet wallet_location -trusted_cert -cert client_ca.cert -pwd

C:\Oracle\wallet>orapki wallet add -wallet C:\oracle\wallet -trusted_cert -cert
client_ca.cert -pwd sys123456
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.

C:\Oracle\wallet>dir
Volume in drive C has no label.
Volume Serial Number is B808-E4B3
Directory of C:\Oracle\wallet
17-05-2018 07:50 .
17-05-2018 07:50 ..
16-05-2018 21:56 427 client_ca.cert
17-05-2018 07:51 4,765 cwallet.sso
17-05-2018 07:47 0 cwallet.sso.lck
17-05-2018 07:51 4,720 ewallet.p12
17-05-2018 07:47 0 ewallet.p12.lck
17-05-2018 07:49 427 server_ca.cert
6 File(s) 10,339 bytes
2 Dir(s) 28,019,503,104 bytes free

Configure the listener to TCPS protocol

1. Modify the Listener to work on TCPS protocol on Server side and restart it.
Add wallet location entry and address with TCPS protocol with port different then already used in Listener.ora file.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXX)(PORT = XXXX))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = XXXX)(PORT = XXXX))
)
)

WALLET_LOCATION =
(SOURCE=
(METHOD=File)
(METHOD_DATA=
(DIRECTORY=wallet_location)))

2. Modify the SQLNET.ORA file in Server side
Note: If you want to support both SSL_VERSION(TSL version) then use SQLNET entry with OR operator as shown: SSL_VERSION=1.2 or 1.1 otherwise use separate mention one version which you want to use SSL_VERSION=1.2

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SSL_VERSION = 1.0
SSL_CLIENT_AUTHENTICATION = TRUE
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = wallet_location)
)
)

3. Modify the SQLNET.ORA file in CLIENT Side.
Note: If you want to support both SSL_VERSION(TSL version) then use SQLNET entry with OR operator as shown: SSL_VERSION=1.2 or 1.1 otherwise use separate mention one version which you want to use SSL_VERSION=1.2

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)
SSL_VERSION = 1.0
SSL_CLIENT_AUTHENTICATION = TRUE

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = wallet_location)
)
)

4. Modify the TNSNAMES.ORA file and make entry using TCPS protocol for connectivity at CLIENT side.

ORCLTCPS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCPS)(HOST = XXXX)(PORT = XXXX))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)

Configuring the database
1. Database parameter OS_AUTHENT_PREFIX must be null and REMOTE_OS_AUTHENT must be FALSE.

alter system set remote_os_authent=FALSE scope=spfile;
alter system set os_authent_prefix='' scope=spfile;

2. Restart the database

shutdown immediate;
startup;

3. User within the database has to be created specifying the distiguished name (DN) on their certificate and have create session grant.

create user client identified externally as 'CN=client';
grant create session to client;

Testing the SSL or TSL connection is working
1. Tnsping the ORCL entry make in CLIENT Side.
tnsping ORCLTCPS

2. Connect with SQLPLUS with tns entry and checked user or PROTOCOL.

sqlplus client@ORCLTCPS
SQL> select user from dual;
USER
------------------------------
CLIENT

select sys_context('userenv','network_protocol') from dual;
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps

Error during configure wallet
Error
C:\Oracle\wallet>orapki wallet add -wallet wallet_location -dn “CN=server” -keysize 512 -self_signed -validity 365 -pwd sys123456
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.
PKI-02003: Unable to load the wallet at: wallet_location

Solution
Specify the correct wallet location which exist physically in Operating system.

Error
C:\oraclient\wallet>orapki wallet create -wallet C:\oraclient\wallet -auto_login -pwd sys123
Oracle PKI Tool : Version 12.1.0.1
Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved.
PKI-01002: Invalid password:Passwords must have a minimum length of eight characters and contain alphabetic characters combined with numbers or special characters.

Solution
Use the password with eight characters at least.

Downgrade unplug and plug the Pluggable Oracle Database

Downgrade unplug and plug the Pluggable Oracle Database in lower version

You can downgrade the PDB database from upper version to lower version by unplug the PDB from higher version to plug in a lower version CDB by downgrade process

Note: Database must be upgraded from PDB smaller version otherwise it will throw following error during executing catdwgd.sql

SELECT version_script AS file_name FROM DUAL
*
ERROR at line 1:
ORA-20000: Downgrade not supported - database has not been upgraded
ORA-06512: at "SYS.VERSION_SCRIPT", line 13

Steps as follows:
1. Downgrade the PDB database
2. Unplug from upper version
3. Plug it in lower version

Example
You are downgrade the PDB from 12.1.0.2 to 12.1.0.1 Version of Oracle.

Downgrade the PDB

1. Start up the PDB database(12.1.0.2) in DOWNGRADE mode. CDB can be in normal mode:

alter pluggable database PDB1 open downgrade;

2. Run catdwgrd script on PDB1 database as follows:

%ORACLE_HOME%/perl/bin/perl %ORACLE_HOME%/rdbms/admin/catcon.pl -d %ORACLE_HOME%/rdbms/admin -l C:\script -e -b catdwgrd -c 'PDB1' catdwgrd.sql

Note: Catcon command explain in link: Catcon.pl

3. Shutdown the PDB

alter pluggable database PDB1 close;

Unplug the PDB from the CDB

1. Connect to the 12.1.0.2 CDB.

Sqlplus / as sysdba

2. Close the PDB for unplug.

alter pluggable database PDB1 close;

3. Unplug the downgraded 12.1.0.1 PDB

alter pluggable database PDB1 unplug into 'path/PDB1.xml';

Plug the PDB1 database to version 12.1.0.1

1. Connect to the 12.1.0.1 CDB.

sqlplus / as sysdba

2. Plug in the 12.1.0.1 PDB.

create pluggable database PDB1 using 'path/PDB1.xml';

3. Open the PDB1 in upgrade mode.

alter pluggable database PDB1 open upgrade;

4. Connect to the PDB.

alter session set container=PDB1;

5. Run catrelod in the PDB1.
Note: catrelod.sql script reloads the appropriate version for each of the database components

@$ORACLE_HOME/rdbms/admin/catrelod.sql

6. Recompile the invalid object during downgrade operation.

@$ORACLE_HOME/rdbms/admin/utlrp.sql;

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

Execute SQL script (catcon.pl) in all PDBs database Oracle

Execute SQL script in all PDBs database Oracle 12c

In a CDB Environment, the catcon.pl utility is used to run SQL scripts and SQL statements in all PDBS database provide by Oracle.

catcon.pl script can run from the root container and in specified PDBs and it also have option for log files that you can view to confirm that
the SQL script or SQL statement did run successfully.

Syntax for Catcon for excute or run the SQL Script on all PDBS in once


$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl
[-u username[/password]] [-U username[/password]] [-d directory]
[-l directory] [{-c|-C} container] [-p parallelism] [-e] [-s]
[-E { ON | errorlogging-table-other-than-SPERRORLOG } ] [-I] [-g] [-f]
-b log_file_name_base -- { SQL_script [arguments] | --x'SQL_statement' }

Options
-u for username and password connect with PDB’s or CDB for execute SQL queries or scripts.
-U for username and password with special privileges to perform internal tasks like modified metadata.
-d Directory containing SQL script
-l directory on which log file written.
-c list the containers in which SQL script is run.
-C list the containers in which SQL script is not run.
-p degree of parallelism.
-e means echo on when script running.
-s spool on
-E When ON, errors are written to the table SPERRORLOG in the current schema.
-I Identifier for Error logging option
-g generating debugging information.
-f ignore if PDB’s database is closed.
-b base name of log file (mandatory)

Example of running the Script info.sql with catcon.pl script.

— Execute the script on all database included CDB or PDBs.

%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -d D:\script -b info_output info.sql

— Execute the script on HRPDB or SALESPDB
Note: ‘c’ small c is used for execute

%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -U SYS -d D:\scripts -l 'D:\script\output' -c 'HRPDB SALESPDB' -b info_output info.sql

–Execute the script except HRPDB or SALESPDB
Note: ‘C’ capital C is used for except

%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -U SYS -d D:\scripts -l 'D:\script\output' -C 'HRPDB SALESPDB' -b info_output info.sql

–Execute the SQL Query on all database

%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -e -b info_output -- --x"SELECT * FROM DUAL"