Rename the PDB database

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;

show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------ ---------- ----------
2 PDB$SEED READ ONLY YES
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;

5. Make a new directory with name PDB2 in Oradata folder

mkdir E:\oracle\oradata\cdb\pdb2

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

alter database move datafile 'E:\oracle\oradata\cdb\pdb1\undotbs01.dbf' to 'E:\oracle\oradata\cdb\pdb2\undotbs01.dbf';
alter database move datafile 'E:\oracle\oradata\cdb\pdb1\sysaux01.dbf' to 'E:\oracle\oradata\cdb\pdb2\sysaux01.dbf';
alter database move datafile 'E:\oracle\oradata\cdb\pdb1\system01.dbf' to 'E:\oracle\oradata\cdb\pdb2\system01.dbf';
alter database move datafile 'E:\oracle\oradata\cdb\pdb1\users01.dbf' to 'E:\oracle\oradata\cdb\pdb2\users01.dbf';

7. Create or Drop the temp file.

alter tablespace TEMP add tempfile 'E:\oracle\oradata\cdb\pdb2\temp01.dbf' size 20M reuse;
alter database tempfile 'E:\oracle\oradata\cdb\pdb1\temp1.dbf' drop including datafiles;

8. Check the PDB database.

select name, open_mode from v$pdbs;

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

alter pluggable database pdb2 close;
alter pluggable database pdb2 open;
-- to save the state
alter pluggable database pdb2 save state;

Advertisements

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"

Regular Expressions function in SQL

Regular Expressions function in SQL

Regular Expression belongs to search pattern, used in SQL query for helping in search of a specific pattern in column data. Oracle SQL supports regular expressions with the pattern-matching condition and functions

Example:
Search pattern may be difficult. It helps to find.
This regular expression matches any string that begins with either s or ss, followed by qq, optionally followed by s, followed by the colon (:).
(s|ss)qqs?:
Explain:
pipe symbol(|) indicates a choice between the data from either side of it. ( s | aa) choose either s or aa.
question mark (?) indicates that the preceding element, s, is optional

Following are the function present in Oracle SQL:

REGEXP_LIKE Condition that can appear in the WHERE clause of a query, causing the query to return rows that match the given pattern.
Example:
This WHERE clause identifies employees with the first name of Steven or Stephen:
WHERE REGEXP_LIKE((hr.employees.first_name, '^Ste(v|ph)en$')

REGEXP_COUNT Function that returns the number of times the given pattern appears in the given string.
Example:
Function returns the number of times that e (but not E) appears in the string ‘Albert Einstein’, starting at character position 7. last c column specify case sensitive
REGEXP_COUNT('Albert Einstein', 'e', 7, 'c')

REGEXP_INSTR Function that returns an integer that indicates the starting position of the given pattern in the given string.
Example:
This function invocation returns the starting position of the first valid email address in the column hr.employees.email
REGEXP_INSTR(hr.employees.email, '\w+@\w+(\.\w+)+')
If the returned value is greater than zero, then the column contains a valid email address.

REGEXP_REPLACE Function that returns the string that results from replacing occurrences of the given pattern in the given string with a replacement string.
Example:
This function invocation puts a space after each character in the column hr.countries.country_name:
REGEXP_REPLACE(hr.countries.country_name, '(.)', '\1 ')

REGEXP_SUBSTR Function returns the matching substring itself.
Example:
This function invocation returns ‘Oracle’ because the x option ignores the spaces in the pattern:
REGEXP_SUBSTR('Oracle 2010', 'O r a c l e', 1, 1, 'x')

Pattern Matching Option

i Case insensitive Matching
c case sensitive matching
n allow the dot(.) operator for matching new line character. E.g. REGEXP_SUBSTR(‘a’||CHR(10)||’d’, ‘a.d’, 1, 1, ‘n’)
Note: CHR(10) is for newline char
m allow multiline mode, string contain multiple lines. E.g. REGEXP_SUBSTR(‘ab’||CHR(10)||’ac’, ‘^a.’, 1, 2,’m’)
x Ignores whitespace characters in the string. REGEXP_SUBSTR(‘Oracle’, ‘O r a c l e’, 1, 1, ‘x’)

Example: you can use both option as show below:
Note: i for case insenstive and x for white space removal.

SQL> select REGEXP_SUBSTR('Oracle', 'o r a cle', 1, 1, 'ix') from dual;
REGEXP
------
Oracle
-- Remove the case insensitive i then it return blank.
SQL> select REGEXP_SUBSTR('The Oracle', 'o ra cle', 1, 1, 'x') from dual;
R
-

Operator meaning
. – means matching any character in database including newline like a.b find abc, adb, afb etc but not bcb or bdb.
+ – means matching one or more occurrence of preceding expression. like a+ find a, aa, aaaa etc but not ba or ab.
* – means matching zero or more occurrence of preceding expression. like ab*c find ac,abc,abbbc etc but not abd or abk.
? – means matching zero or one occurrence of preceding expression. like ab?c find ac and abc but not abbc or adc.
{m} – means matching exact occurrence of preceding expression. like a{3} find aaa but not aa.
{m,} – means matching at least m occurrence of preceding expression. like a{3,} find aaa and aaaa but not aa.
{m,n} – means matching m occurrence but not more than n. like a{3,4} find aaa and aaaa but not aaaaa or aa.
[char…] – means matching any single character in string like [abc] first character find all, bill, cold but not doll.
[^char…] – means not matching any single character in string for selected position. like [^ab] first character should but not be a or b.
\ – means treat operator character as a literal. like abc\+def with slash(\) you can use + sign as data.
^ – means matches begin of line with the string. like ^def matches the starting string defghi but not abcdef.
$ – means matches one of line with string. like def$ matches the ending string abcdef but not defghi.
\d – means matching the digit character like ^\(\d{3}\) is (550) but not 550.
\D – means match with non digit character like \d\D is 4b or 3_ but not 22.
\w – means matches with a world character like \w+@\w+(\.\w+)+ is sunny@gmail.com but not sunny@gmail.
\W – means matches a non world character like \w+\W\s\w+ matches the string to: bill but does not match to bill
\s – means matches a white space like \(\w\s\w\s\) is ( a b ) but not match (ab) or (a,b.)
\S – means matches a non white space character like \(\w\S\w\S\) is (abde) and (a,b.) but not match (a b d e)

Example of using the Regular Expression operator:

Check the phone number in particular format:

(REGEXP_LIKE (p_number, '^\(\d{3}\) \d{3}-\d{4}$'))

Sharding Methods in Oracle

Sharding Methods in Oracle

Sharding is a data tier architecture in which data is horizontally partitioned across independent databases. Each database in such a configuration is called a shard. All of the shards together make up a single logical database, which is referred to as a sharded database (SDB). Oracle Sharding is implemented based on the Oracle Database partitioning feature.

Sharding has following methods for implementation sharding in Oracle Database:

System Managed Sharding

User does not require specific mapping of data to shards. In this data is automatically distributed on basis of consistent hash.
The partitioning method automatically distributed data across shards.
It automatically maintain the balanced distribution of chunks when shards are added or removed from environment.

Example of Employee table created on Shard database. This table used partitioned by consistent hash method.
PARTITION AUTO in the statements means that number of partitions is automatically set in number of tablespace in tablespace set ts; ts is name of tablespace already created in Shard database.


CREATE TABLESPACE SET ts;

CREATE SHARDED TABLE employees
( emp_id NUMBER NOT NULL
, name VARCHAR2(50)
, address VARCHAR2(250)
, location_id VARCHAR2(20)
, class VARCHAR2(3)
, signup DATE
, CONSTRAINT cust_pk PRIMARY KEY(cust_id)
)
PARTITION BY CONSISTENT HASH (cust_id)
PARTITIONS AUTO
TABLESPACE SET ts;

Note: Each tablespace in tablespace set belong to distinct chunk.
Same tablespace is used for multiple table belong to same table family.

User Defined Sharding
In this you can define the mapping of data to individual shards. In this DBA manages & monitor the data across shards.

Example, In this user defined sharding , a sharded table has partitioned of data by range or list.
Example show us that tablespace is defined on each shared server separately. We have divided the shard database in 4 region : esst ,west, south and north.
A shardspace is set of shards that store data that corresponds to a range or list of key values. In this we need to create shardspace for define range in shard database wiht different shard(standalon database server)
First create shardspace cooresponding with shared server location

-- We have 4 shard db which we create sharedspace
ADD SHARDSPACE –SHARDSPACE west, north, east, south;
ADD SHARD –CONNECT shard-1 –SHARDSPACE west;
ADD SHARD –CONNECT shard-2 –SHARDSPACE norht;
ADD SHARD –CONNECT shard-3 –SHARDSPACE east;
ADD SHARD –CONNECT shard-4 –SHARDSPACE south;

Second, Create the shard tablespace as region on different shard server. All tablespace is different in all shard Server.

CREATE TABLESPACE tbs1 IN SHARDSPACE west;
CREATE TABLESPACE tbs2 IN SHARDSPACE North;
CREATE TABLESPACE tbs3 IN SHARDSPACE east;
CREATE TABLESPACE tbs4 IN SHARDSPACE south;

Third, Manually define the table and its partition value as range or list partition.

CREATE SHARDED TABLE accounts
( id NUMBER
, account_number NUMBER
, customer_id NUMBER
, branch_id NUMBER
, state VARCHAR(2) NOT NULL
, status VARCHAR2(1)
)
PARTITION BY LIST (state)
( PARTITION p_west VALUES ('OR', 'WA') TABLESPACE ts1
, PARTITION p_north VALUES ('SD', 'WI') TABLESPACE ts2
, PARTITION p_east VALUES ('NY', 'VM', 'NJ') TABLESPACE ts3
, PARTITION p_south VALUES ('FL', 'GA') TABLESPACE ts4)
;

Note: MOVE CHUNK command for each chunk that needs to be migrated. No chunk migration is automatically started when a shard is added to the SDB.
SPLIT CHUNK command, which is used to split a chunk in the middle of the hash range for system-managed sharding,

Composite Sharding

Composite Sharding is combination of user defined and system managed sharding. provides benefits of both methods.
Data is first partitioned by list or range across multiple shardspaces, and then further partitioned by consistent hash across multiple shards in each shardspace.
The two levels of sharding make it possible to automatically maintain balanced distribution of data across shards in each shardspace, and, at the same time, partition data across shardspaces.

Example: you can allocate the three shards to the gold class and two shards to the silver class customer.
In this case you can create two shardsspace for each catagory and assign tablespace to them and define the table according to you need with composite sharding.

First create the two shardspace and assign the server according to gold or silver catagory:

ADD SHARDSPACE –SHARDSPACE shspace1;
ADD SHARDSPACE –SHARDSPACE shspace2;
-- gold customer
ADD SHARD –CONNECT shard1 –SHARDSPACE shspace1;
ADD SHARD –CONNECT shard2 –SHARDSPACE shspace1;
ADD SHARD –CONNECT shard3 –SHARDSPACE shspace1;
-- Silver customer
ADD SHARD –CONNECT shard4 –SHARDSPACE shspace2;
ADD SHARD –CONNECT shard5 –SHARDSPACE shspace2;

Second, create two separate tablespace for them.

CREATE TABLESPACE SET tbs1 IN SHARDSPACE shspace1;
CREATE TABLESPACE SET tbs2 IN SHARDSPACE shspace2;

Third, Create table that use composite sharding

CREATE SHARDED TABLE customers
( cust_id NUMBER NOT NULL
, name VARCHAR2(50)
, address VARCHAR2(250)
, location_id VARCHAR2(20)
, class VARCHAR2(3)
, signup_date DATE
, CONSTRAINT cust_pk PRIMARY KEY(cust_id, class)
)
PARTITIONSET BY LIST (class)
PARTITION BY CONSISTENT HASH (cust_id)
PARTITIONS AUTO
(PARTITIONSET gold VALUES ('gld') TABLESPACE SET tbs1,
PARTITIONSET silver VALUES ('slv') TABLESPACE SET tbs2)
;

Subpartitions with Sharding
Subpartitioning splits each partition into smaller parts and may be beneficial for efficient parallel execution within a shard, especially in the case of sharding by range or list when the number of partitions per shard may be small.