Nothing but my learning and experience with Oracle..!

Wednesday, March 8, 2017

Sql performance analyzer after a major database change

There are some wonderful feature that were introduced in 11g R1 and I was too late to test this amazing feature. I am talking about SQL Performance Analyzer.Here is the link that I followed/tested -

https://oracle-base.com/articles/11g/sql-performance-analyzer-11gr1

1) Create the Sql Tuning Set using DBMS_SQLTUNE.
2) Create a analysis task to run Sql Performance Analyzer using DBMS_SQLPA.
3) Execute Before Change TEST EXECUTE (Pre-Change SQL Trial).
---> Make a change that needs to tested.
4) Execute After Change TEST EXECUTE (Post-Change SQL Trial).
5) Comparing SQL Trials.
6) Generate Compare report.

The concept of SQL tuning sets, along with the DBMS_SQLTUNE package to manipulate them, was introduced in Oracle 10g as part of the Automatic SQL Tuning functionality. Oracle 11g makes further use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database change can be as major or minor as you like, such as:

Database, operating system, or hardware upgrades.
Database, operating system, or hardware configuration changes.
Database initialization parameter changes.
Schema changes, such as adding indexes or materialized views.
Refreshing optimizer statistics.
Creating or changing SQL profiles.

The SQL Performance Analyzer can be run manually using the DBMS_SQLPA package or using Enterprise Manager.

The SQL performance analyzer requires SQL tuning sets, and SQL tuning sets are pointless unless they contain SQL.

SQL> CREATE USER spa_test_user IDENTIFIED BY spa_test_user QUOTA UNLIMITED ON users;

User created.

SQL> GRANT CONNECT, CREATE TABLE TO spa_test_user;

Grant succeeded.

SQL> CONN spa_test_user/spa_test_user

Connected.

SQL> CREATE TABLE my_objects AS SELECT * FROM all_objects;

Table created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'MY_OBJECTS', cascade => TRUE);

PL/SQL procedure successfully completed.

-->This schema represents our "before" state. Still logged in as the test user, issue the following statements.

SQL> SELECT COUNT(*) FROM my_objects WHERE object_id <= 100;

COUNT(*)

----------

SQL> SELECT object_name FROM my_objects WHERE object_id = 100;

OBJECT_NAME

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

ORA$BASE

SQL> SELECT COUNT(*) FROM my_objects WHERE object_id <= 1000;

COUNT(*)

----------

SQL> SELECT object_name FROM my_objects WHERE object_id = 1000;

no rows selected

SQL> SELECT COUNT(*) FROM my_objects WHERE object_id BETWEEN 100 AND 1000;

COUNT(*)

----------

SQL>

Notice, all statements make reference to the currently unindexed OBJECT_ID column. Later we will be indexing this column to create our changed "after" state.

The select statements are now in the shared pool, so we can start creating a SQL tuning set.

Creating SQL Tuning Sets using the DBMS_SQLTUNE Package

The DBMS_SQLTUNE package contains procedures and functions that allow us to create, manipulate and drop SQL tuning sets. The first step is to create a SQL tuning set called spa_test_sqlset using the CREATE_SQLSET procedure.

SQL> CONN / AS SYSDBA

Connected.

SQL> EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'spa_test_sqlset');--- > created STS

PL/SQL procedure successfully completed.

SQL> DECLARE

l_cursor DBMS_SQLTUNE.sqlset_cursor;

BEGIN

OPEN l_cursor FOR

SELECT VALUE(a)

FROM TABLE(

DBMS_SQLTUNE.select_cursor_cache(

basic_filter => 'sql_text LIKE ''%my_objects%'' and parsing_schema_name = ''SPA_TEST_USER''',

attribute_list => 'ALL')

) a;

DBMS_SQLTUNE.load_sqlset(sqlset_name => 'spa_test_sqlset',populate_cursor => l_cursor);

END;

PL/SQL procedure successfully completed.

SQL> SELECT sql_text FROM dba_sqlset_statements WHERE sqlset_name = 'spa_test_sqlset';

SQL_TEXT

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

SELECT object_name FROM my_objects WHERE object_id = 100

CREATE TABLE my_objects AS SELECT * FROM all_objects

SELECT COUNT(*) FROM my_objects WHERE object_id <= 100

SELECT COUNT(*) FROM my_objects WHERE object_id BETWEEN 100 AND 1000

SELECT COUNT(*) FROM my_objects WHERE object_id <= 1000

SELECT object_name FROM my_objects WHERE object_id = 1000

6 rows selected.

SQL>

Now we have a SQL tuning set, we can start using the SQL performance analyzer.

Running the SQL Performance Analyzer using the DBMS_SQLPA Package by providing the STS that we created.

The DBMS_SQLPA package is the PL/SQL API used to manage the SQL performance ananlyzer. The first step is to create an analysis task using the CREATE_ANALYSIS_TASK function, passing in the SQL tuning set name and making a note of the resulting task name

SQL> VARIABLE v_task VARCHAR2(64);

SQL> EXEC :v_task := DBMS_SQLPA.create_analysis_task(sqlset_name => 'spa_test_sqlset');

PL/SQL procedure successfully completed.

SQL> PRINT :v_task

V_TASK

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

TASK_17292

When executing the task you have to decide, with execution_type parameter, which kind of execution you want to perform. A standard SPA task is made of following steps:

Execute the task in TEST EXECUTE mode and generate a before change task report.
Change what you want on your database (upgrade, optimizer parameters, statistics, …), execute the task in TEST EXECUTE mode and generate an after change task report.
Execute the task in COMPARE PERFORMANCE mode and generate a compare performance task report.

SQL> BEGIN

DBMS_SQLPA.execute_analysis_task(

task_name => :v_task,

execution_type => 'test execute',

execution_name => 'before_change');

END;

/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

Now we have the "before" performance information, we need to make a change so we can test the "after" performance. For this example we will simply add an index to the test table on the OBJECT_ID column. In a new SQL*Plus session create the index using the following statements.

SQL> CONN spa_test_user/spa_test_user

Connected.

SQL> CREATE INDEX my_objects_index_01 ON my_objects(object_id);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'MY_OBJECTS', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> sho user

USER is "SPA_TEST_USER"

SQL> conn / as sysdba

Connected.

SQL> BEGIN

DBMS_SQLPA.execute_analysis_task(

task_name => :v_task,

execution_type => 'test execute',

execution_name => 'after_change');

END;

/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> BEGIN

2 DBMS_SQLPA.execute_analysis_task(

3 task_name => :v_task,

4 execution_type => 'compare performance',

execution_params => dbms_advisor.arglist(

5 6 'execution_name1',

'before_change',

7 8 'execution_name2',

9 'after_change')

);

END;

10 11 12 /

PL/SQL procedure successfully completed.

SQL> SET PAGESIZE 0

SQL> SET LINESIZE 1000

SQL> SET LONG 1000000

SQL> SET LONGCHUNKSIZE 1000000

SQL> SET TRIMSPOOL ON

SQL> SET TRIM ON

SQL> SPOOL /tmp/execute_comparison_report.txt

SQL> SELECT DBMS_SQLPA.report_analysis_task(:v_task, 'TEXT', 'ALL','ALL') FROM dual;

General Information

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

Task Information: Workload Information:

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

Task Name : TASK_17292 SQL Tuning Set Name : spa_test_sqlset

Task Owner : SYS SQL Tuning Set Owner : SYS

Description : Total SQL Statement Count : 6

Execution Information:

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

Execution Name : EXEC_18111 Started : 03/08/2017 02:43:12

Execution Type : COMPARE PERFORMANCE Last Updated : 03/08/2017 02:43:12

Description : Global Time Limit : UNLIMITED

Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED

Status : COMPLETED Number of Errors : 0

Number of Unsupported SQL : 1

Analysis Information:

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

Before Change Execution: After Change Execution:

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

Execution Name : before_change Execution Name : after_change

Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE

Scope : COMPREHENSIVE Scope : COMPREHENSIVE

Status : COMPLETED Status : COMPLETED

Started : 03/08/2017 02:41:50 Started : 03/08/2017 02:43:04

Last Updated : 03/08/2017 02:41:50 Last Updated : 03/08/2017 02:43:04

Global Time Limit : UNLIMITED Global Time Limit : UNLIMITED

Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED

Number of Errors : 0 Number of Errors : 0

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

Comparison Metric: ELAPSED_TIME

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

Workload Impact Threshold: 1%

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

SQL Impact Threshold: 1%

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

Report Summary

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

Projected Workload Change Impact:

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

Overall Impact : 98.82%

Improvement Impact : 98.82%

Regression Impact : 0%

SQL Statement Count

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

SQL Category SQL Count Plan Change Count

Overall 6 5

Improved 5 5

Unsupported 1 0

Top 5 SQL Sorted by Absolute Value of Change Impact on the Workload

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

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

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

| 19 | 7m2wr4twzmrkm | 20.57% | 1 | 2171 | 28 | 98.71% | y |

| 18 | 7k24y5pdhurxx | 20.44% | 1 | 2155 | 26 | 98.79% | y |

| 20 | 7vbu3057r64r0 | 20.39% | 1 | 2151 | 27 | 98.74% | y |

| 21 | fbsgyfwt1q29p | 18.79% | 1 | 1976 | 19 | 99.04% | y |

| 16 | 2j3dk2hcs8jq5 | 18.63% | 1 | 1963 | 23 | 98.83% | y |

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

Note: time statistics are displayed in microseconds

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

Report Details

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

SQL Details:

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

Object ID : 19

Schema Name : SPA_TEST_USER

Container Name : Unknown (con_dbid: 3041856058)

SQL ID : 7m2wr4twzmrkm

Execution Frequency : 1

SQL Text : SELECT COUNT(*) FROM my_objects WHERE object_id BETWEEN

100 AND 1000

Execution Statistics:

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

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

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

| elapsed_time | 20.57% | .002171 | .000028 | 98.71% |

| parse_time | 8.43% | .000315 | .000211 | 33.02% |

| cpu_time | 19.78% | .002111 | .000111 | 94.74% |

| user_io_time | 0% | 0 | 0 | 0% |

| buffer_gets | 19.97% | 1176 | 2 | 99.83% |

| cost | 19.88% | 327 | 2 | 99.39% |

| reads | 0% | 0 | 0 | 0% |

| writes | 0% | 0 | 0 | 0% |

| io_interconnect_bytes | 0% | 0 | 0 | 0% |

| rows | | 1 | 1 | |

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

Note: time statistics are displayed in seconds

Notes:

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

Before Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

After Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

Findings (2):

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

1. The performance of this SQL has improved.

2. The structure of the SQL execution plan has changed.

Execution Plan Before Change:

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

Plan Id : 27852

Plan Hash Value : 3102251570

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 327 | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| * 2 | TABLE ACCESS FULL | MY_OBJECTS | 384 | 1920 | 327 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - filter("OBJECT_ID"<=1000 AND "OBJECT_ID">=100)

Execution Plan After Change:

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

Plan Id : 27858

Plan Hash Value : 4165478064

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 2 | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| * 2 | INDEX RANGE SCAN | MY_OBJECTS_INDEX_01 | 169 | 845 | 2 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=1000)

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

SQL Details:

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

Object ID : 18

Schema Name : SPA_TEST_USER

Container Name : Unknown (con_dbid: 3041856058)

SQL ID : 7k24y5pdhurxx

Execution Frequency : 1

SQL Text : SELECT COUNT(*) FROM my_objects WHERE object_id <= 100

Execution Statistics:

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

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

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

| elapsed_time | 20.44% | .002155 | .000026 | 98.79% |

| parse_time | -.24% | .00018 | .000183 | -1.67% |

| cpu_time | 20.88% | .002111 | 0 | 100% |

| user_io_time | 0% | 0 | 0 | 0% |

| buffer_gets | 19.97% | 1176 | 2 | 99.83% |

| cost | 19.88% | 327 | 2 | 99.39% |

| reads | 0% | 0 | 0 | 0% |

| writes | 0% | 0 | 0 | 0% |

| io_interconnect_bytes | 0% | 0 | 0 | 0% |

| rows | | 1 | 1 | |

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

Note: time statistics are displayed in seconds

Notes:

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

Before Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

After Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

Findings (2):

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

1. The performance of this SQL has improved.

2. The structure of the SQL execution plan has changed.

Execution Plan Before Change:

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

Plan Id : 27851

Plan Hash Value : 3102251570

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 327 | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| * 2 | TABLE ACCESS FULL | MY_OBJECTS | 1 | 5 | 327 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - filter("OBJECT_ID"<=100)

Execution Plan After Change:

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

Plan Id : 27857

Plan Hash Value : 4165478064

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 2 | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| * 2 | INDEX RANGE SCAN | MY_OBJECTS_INDEX_01 | 14 | 70 | 2 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - access("OBJECT_ID"<=100)

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

SQL Details:

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

Object ID : 20

Schema Name : SPA_TEST_USER

Container Name : Unknown (con_dbid: 3041856058)

SQL ID : 7vbu3057r64r0

Execution Frequency : 1

SQL Text : SELECT COUNT(*) FROM my_objects WHERE object_id <= 1000

Execution Statistics:

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

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

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

| elapsed_time | 20.39% | .002151 | .000027 | 98.74% |

| parse_time | -.16% | .000191 | .000193 | -1.05% |

| cpu_time | 20.88% | .002111 | 0 | 100% |

| user_io_time | 0% | 0 | 0 | 0% |

| buffer_gets | 19.97% | 1176 | 2 | 99.83% |

| cost | 19.88% | 327 | 2 | 99.39% |

| reads | 0% | 0 | 0 | 0% |

| writes | 0% | 0 | 0 | 0% |

| io_interconnect_bytes | 0% | 0 | 0 | 0% |

| rows | | 1 | 1 | |

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

Note: time statistics are displayed in seconds

Notes:

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

Before Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

After Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

Findings (2):

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

1. The performance of this SQL has improved.

2. The structure of the SQL execution plan has changed.

Execution Plan Before Change:

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

Plan Id : 27853

Plan Hash Value : 3102251570

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 327 | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| * 2 | TABLE ACCESS FULL | MY_OBJECTS | 384 | 1920 | 327 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - filter("OBJECT_ID"<=1000)

Execution Plan After Change:

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

Plan Id : 27859

Plan Hash Value : 4165478064

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

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

| 0 | SELECT STATEMENT | | 1 | 5 | 2 | 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

| * 2 | INDEX RANGE SCAN | MY_OBJECTS_INDEX_01 | 169 | 845 | 2 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - access("OBJECT_ID"<=1000)

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

SQL Details:

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

Object ID : 21

Schema Name : SPA_TEST_USER

Container Name : Unknown (con_dbid: 3041856058)

SQL ID : fbsgyfwt1q29p

Execution Frequency : 1

SQL Text : SELECT object_name FROM my_objects WHERE object_id =

1000

Execution Statistics:

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

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

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

| elapsed_time | 18.79% | .001976 | .000019 | 99.04% |

| parse_time | .32% | .000199 | .000195 | 2.01% |

| cpu_time | 18.68% | .001888 | 0 | 100% |

| user_io_time | 0% | 0 | 0 | 0% |

| buffer_gets | 19.97% | 1176 | 2 | 99.83% |

| cost | 19.88% | 327 | 2 | 99.39% |

| reads | 0% | 0 | 0 | 0% |

| writes | 0% | 0 | 0 | 0% |

| io_interconnect_bytes | 0% | 0 | 0 | 0% |

| rows | | 0 | 0 | |

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

Note: time statistics are displayed in seconds

Notes:

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

Before Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

After Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

Findings (3):

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

1. The performance of this SQL has improved.

2. The structure of the SQL execution plan has changed.

3. This SQL statement returned zero rows.

Execution Plan Before Change:

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

Plan Id : 27854

Plan Hash Value : 880823944

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

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

| 0 | SELECT STATEMENT | | 1 | 31 | 327 | 00:00:01 |

| * 1 | TABLE ACCESS FULL | MY_OBJECTS | 1 | 31 | 327 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 1 - filter("OBJECT_ID"=1000)

Execution Plan After Change:

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

Plan Id : 27860

Plan Hash Value : 712466900

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

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

| 0 | SELECT STATEMENT | | 1 | 31 | 2 | 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | MY_OBJECTS | 1 | 31 | 2 | 00:00:01 |

| * 2 | INDEX RANGE SCAN | MY_OBJECTS_INDEX_01 | 1 | | 1 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - access("OBJECT_ID"=1000)

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

SQL Details:

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

Object ID : 16

Schema Name : SPA_TEST_USER

Container Name : Unknown (con_dbid: 3041856058)

SQL ID : 2j3dk2hcs8jq5

Execution Frequency : 1

SQL Text : SELECT object_name FROM my_objects WHERE object_id =

100

Execution Statistics:

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

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

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

| elapsed_time | 18.63% | .001963 | .000023 | 98.83% |

| parse_time | 9.64% | .000349 | .00023 | 34.1% |

| cpu_time | 18.68% | .001888 | 0 | 100% |

| user_io_time | 0% | 0 | 0 | 0% |

| buffer_gets | 19.95% | 1176 | 3 | 99.74% |

| cost | 19.88% | 327 | 2 | 99.39% |

| reads | 0% | 0 | 0 | 0% |

| writes | 0% | 0 | 0 | 0% |

| io_interconnect_bytes | 0% | 0 | 0 | 0% |

| rows | | 1 | 1 | |

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

Note: time statistics are displayed in seconds

Notes:

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

Before Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

After Change:

1. The statement was first executed to warm the buffer cache.

2. Statistics shown were averaged over next 9 executions.

Findings (2):

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

1. The performance of this SQL has improved.

2. The structure of the SQL execution plan has changed.

Execution Plan Before Change:

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

Plan Id : 27849

Plan Hash Value : 880823944

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

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

| 0 | SELECT STATEMENT | | 1 | 31 | 327 | 00:00:01 |

| * 1 | TABLE ACCESS FULL | MY_OBJECTS | 1 | 31 | 327 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 1 - filter("OBJECT_ID"=100)

Execution Plan After Change:

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

Plan Id : 27855

Plan Hash Value : 712466900

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

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

| 0 | SELECT STATEMENT | | 14 | 434 | 2 | 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | MY_OBJECTS | 14 | 434 | 2 | 00:00:01 |

| * 2 | INDEX RANGE SCAN | MY_OBJECTS_INDEX_01 | 14 | | 1 | 00:00:01 |

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

Predicate Information (identified by operation id):

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

* 2 - access("OBJECT_ID"=100)

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

SQL>

Sunday, October 23, 2016

How to plug a PDB into another CDB within the same host.

In 12c container database we can unplug a PDB from its root container and can plug it back into the same/different CDB according to the requirement.In case if you are pluging into a differnet CDB on a remote machine you may use SOURCE_FILE_NAME_CONVERT parameter while creating the PDB. This time I’ll show how to plug a Pluggable Database (PDB) into another Container Database (CDB) within the same host.

In this demonstration PDB1 is my source PDB within the CDB – TDCDB, I will plug this PDB into a different CDB named MYCDB1 within the same machine.

Login to source PDB and create some ojects

[oracle@mydb01]:[TDCDB] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 18 15:55:46 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

4 PDB2 READ WRITE NO

5 TAPDBCLONE READ WRITE NO

SQL> alter session set container=PDB1;

Session altered.

SQL> show con_name

CON_NAME

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

PDB1

SQL> create user mahi identified by mahi;

User created.

SQL> grant dba to mahi;

Grant succeeded.

SQL> !hostname

mydb01

SQL> conn mahi/mahi@mydb01:1521/PDB1

Connected.

SQL> show user;

USER is "MAHI"

SQL> create table t1 as select * from dba_users;

Table created.

SQL> create table t2 as select * from dba_tables;

Table created.

SQL> select count(1) from t1;

COUNT(1)

----------

SQL> select count(1) from t2;

COUNT(1)

----------

2339

SQL> select NAME,CON_ID from v$datafile where CON_ID=3;

NAME CON_ID

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

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf 3

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf 3

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options

Close and unplug the source PDB

Before attempting to unplug a PDB, you must make sure it is closed. To unplug the database use the ALTER PLUGGABLE DATABASE command with the UNPLUG INTO clause to specify the location of the XML metadata file.

[oracle@mydb01]:[TDCDB] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 18 15:55:46 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL>shopdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 MOUNTED

4 PDB2 READ WRITE NO

5 TAPDBCLONE READ WRITE NO

SQL>alter pluggable database pdb1 unplug into '/tmp/pdb1.xml';

Pluggable database altered.

SQL> selectpdb_name, status from cdb_pdbs;

PDB_NAME STATUS

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

PDB$SEED NORMAL

PDB1 UNPLUGGED

PDB2 NORMAL

TAPDBCLONE NORMAL

You could see the location of datafiles , tablespace name, configured database component etc from the xml file.

[oracle@mydb01]:[TDCDB] $ grep dbf /tmp/pdb1.xml

<path>/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf</path>

<path>/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf</path>

<path>/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_temp_cz9kdysy_.dbf</path>

<path>/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf</path>

[oracle@mydb01]:[TDCDB] $ grep option /tmp/pdb1.xml

<option>CATALOG=12.1.0.2.0</option>

<option>CATJAVA=12.1.0.2.0</option>

<option>CATPROC=12.1.0.2.0</option>

<option>CONTEXT=12.1.0.2.0</option>

<option>JAVAVM=12.1.0.2.0</option>

<option>ORDIM=12.1.0.2.0</option>

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

[oracle@mydb01]:[TDCDB] $

Checking the Compatibility of the Unplugged PDB with the Host CDB

First check the PBD is compatible with the target CDB by calling the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function, passing in the XML metadata file and the name of the PDB you want to create using it.

[oracle@mydb01]:[TDCDB] $ .oraenv

ORACLE_SID = [TDCDB] ? MYCDB1

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@mydb01]:[MYCDB1] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 17 18:47:15 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> SET SERVEROUTPUT ON

DECLARE

compatible CONSTANT VARCHAR2(3) :=

CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

pdb_descr_file =>'/tmp/pdb1.xml')

WHEN TRUE THEN 'YES'

ELSE 'NO'

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(compatible);

END;

/SQL> 2 3 4 5 6 7 8 9 10 11

YES

PL/SQL procedure successfully completed.

SQL>

If you get an output ‘NO’, then PDB is not compatible and check the view PDB_PLUG_IN_VIOLATIONS for Errors.

Pre-requisites for plugin to a remote CDB

· The Target CDB must have the same endianness as source CDB.

· The CDB must have the same set of options installed.

· The source CDB and the target CDB must have compatible character sets and national character sets.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 TAPDB READ WRITE NO

SQL>

Here I am using OMF, so I don’t have to use file_name_convert clause while creating new PDB. If you want to configure a new OMF path for PDBs while creating PDBs use the create_file_dest parameter,other wise it will inherit the value(of OMF) from the root container.

SQL> show parameter create

NAME TYPE VALUE

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

create_bitmap_area_size integer 8388608

create_stored_outlines string

db_create_file_dest string /data1

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

SQL>

SQL>create pluggable database pdb1_tdcdb using '/tmp/pdb1.xml' copy ;

Pluggable database created.

SQL>

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 TAPDB READ WRITE NO

4 PDB1_TDCDB MOUNTED

SQL> alter pluggable database PDB1_TDCDB open;

Pluggable database altered.

SQL> alter pluggable database PDB1_TDCDB save state;

Pluggable database altered.

SQL> showpdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 TAPDB READ WRITE NO

4 PDB1_TDCDB READ WRITE NO

SQL>

SQL> alter session set container=PDB1_TDCDB;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

4 PDB1_TDCDB READ WRITE NO

SQL>

Verify the data that we created on the source PDB

SQL> !hostname

mydb01

SQL> conn mahi/mahi@mydb01:1521/PDB1_TDCDB

Connected.

SQL>sho user;

USER is "MAHI"

SQL> select count(1) from t1;

COUNT(1)

----------

SQL> select count(1) from t2;

COUNT(1)

----------

2339

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,

Advanced Analytics and Real Application Testing options

We can use different methods while cloning-

COPY: Specify COPY if you want the files listed in the XML file to be copied to the new location and used for the new PDB. This is the default and should be useful if you are cloning the new PDB in the same host.

NOCOPY: If you do not want Oracle to copy the files and would like to have the current location of data files of source PDB as the location for the new PDB, then make use of this option. Normally we may use this option after droping a pdb while keeping its datafiles like,

SQL>drop pluggable database pdb1 keep datafiles;

MOVE: Specify MOVE if you want the files listed in the XML file to be moved, rather than copied, to the new location and used for the new PDB.

FILE_NAME_CONVERT=When using a copy clause, you need to specify the source and destination file location and this can be done using the FILE_NAME_CONVERT clause.

AS CLONE:Specify this clause only if the target CDB already contains a PDB that was created using the same set of data files. The source files remain as an unplugged PDB and can be used again. Specifying AS CLONE also ensures that Oracle Database generates new identifiers, such as DBID and GUID, for the new PDB.

SOURCE_FILE_NAME_CONVERT: If the location of the datafiles in the XML are different from that what you have for the source files, then this caluse is to be used. Let’s say that you have the source PDB on host1 and it’sdatafiles are at location “/u01/sourcePDB/” and the same is recorded in the XML file. On the target server, you copy these files manually to say “/u03/targetPDB”. But when you use the XML file to create the PDB, the location of the files in the XML do not match or are not accurate as the files still point to location “/u01/sourcePDB/”. In such cases, you can use the SOURCE_FILE_NAME_CONVERT clause which is used mainly for the source PDB files if the XML file does not describe the exact current location of the source PDB files.For example,

SQL> create pluggable database CLONEPDB using '/tmp/pdb1.xml’

source_file_name_convert=('/u01/sourcePDB’,'/u03/targetPDB') move;

AS CLONE Clause

Specify this clause only if the target CDB already contains a PDB that was created using the same set of data files. The source files remain as an unplugged PDB and can be used again. Specifying AS CLONE also ensures that Oracle Database generates new identifiers, such as DBID and GUID, for the new PDB. Let me demonstrate this with an example.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 TAPDB READ WRITE NO

4 PDB1_TDCDB READ WRITE NO

SQL>

SQL> create pluggable database pdb2_tdcdb using '/tmp/pdb1.xml' copy ;

create pluggable database pdb2_tdcdb using '/tmp/pdb1.xml' copy

ERROR at line 1:

ORA-65122: Pluggable database GUID conflicts with the GUID of an existing

container.

SQL> create pluggable database pdb2_tdcdb as clone using '/tmp/pdb1.xml' copy ;

Pluggable database created.

SQL> showpdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 TAPDB READ WRITE NO

4 PDB1_TDCDB READ WRITE NO

5 PDB2_TDCDB MOUNTED

SQL> alter pluggable database PDB2_TDCDB open;

Pluggable database altered.

SQL> alter pluggable database PDB2_TDCDB save state;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 TAPDB READ WRITE NO

4 PDB1_TDCDB READ WRITE NO

5 PDB2_TDCDB READ WRITE NO

SQL>

Reference:-https://shivanandarao-oracle.com/2015/10/02/creating-pluggable-databases-part-2/#CreatingaPDBbyplugginginaanunpluggedPDB

Friday, October 14, 2016

Automatic Startup of Pluggable Database (PDB) in 12.1.0.2

Prior to 12.1.0.2, when the CDB is started, all PDBs remain in mounted mode. There was no default mechanism to automatically start them when the CDB is started. The way to achieve this is to use a system trigger on the CDB to start some or all of the PDBs.

The 12.1.0.2 patchset has introduced the ability to preserve the startup state of PDBs, We don’t need start up triggers anymore because this version comes with the SAVE STATE option for pluggable databases.

Here is the demonstration,

[oracle@mydb01]:[MYCDB1] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 14 22:06:49 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT name, open_mode FROM v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

TAPDB READ WRITE

SQL> shut immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 4194304000 bytes

Fixed Size 4505200 bytes

Variable Size 2768241040 bytes

Database Buffers 1409286144 bytes

Redo Buffers 12271616 bytes

Database mounted.

Database opened.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

TAPDB MOUNTED

Could see that by default all user created PDBs remain in MOUNTED mode. Lets open and save its state.

SQL> ALTER PLUGGABLE DATABASE TAPDB open;

Pluggable database altered.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

TAPDB READ WRITE

SQL> ALTER PLUGGABLE DATABASE TAPDB SAVE STATE;

Pluggable database altered.

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> shut immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 4194304000 bytes

Fixed Size 4505200 bytes

Variable Size 2768241040 bytes

Database Buffers 1409286144 bytes

Redo Buffers 12271616 bytes

Database mounted.

Database opened.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

TAPDB READ WRITE

SQL>

Nothing but my learning and experience with Oracle..!

Pages

Wednesday, March 8, 2017

Sql performance analyzer after a major database change

Sunday, October 23, 2016

How to plug a PDB into another CDB within the same host.

Friday, October 14, 2016

Automatic Startup of Pluggable Database (PDB) in 12.1.0.2

Popular Posts