12c : Transportable Database

Oracle 12c introduces full transportable export/import,  an  exciting new feature that greatly simplifies the process of database migration. It  employs

  •  Oracle Data Pump export  to extract  all of the system, user, and application metadata needed for a database migration
  •   transportable tablespaces mechanism to move user and application data i.e. datafiles containing user and application data are physically copied to the target. This results in a migration that is very fast, even for very large volumes of data.

Thus, you can upgrade or migrate to Oracle Database 12c easily and quickly with a single import command. This requires that  your source database is at least Oracle Database 11g Release 2 (11.2.0.3).

This post focuses on the use of full transportable export/import  to migrate an 11.2.0.3 database to a pluggable database , although this feature can be used for migrations to a Oracle Database 12c non-CDB database as well. 

The steps for migrating from an 11.2.0.3 non-CDB  into a pluggable database are as follows:

  • Set the user and application tablespaces in the source database to be READ ONLY
  •  Export from the source database using expdp with the FULL=Y, VERSION=12.0 and TRANSPORTABLE=ALWAYS
  • Copy the  dump file and data files for tablespaces containing user/application data to the destination
  • Create a new PDB in the destination CDB using the create pluggable database command
  • Create a directory object in the destination PDB pointing to the folder containing dumpfile
  • Create an entry for the destination PDB in tnsnames.ora
  •   Using an account that has the DATAPUMP_IMP_FULL_DATABASE privilege, import into the target database using impdp with the FULL=Y and TRANSPORT_DATAFILES parameters
  • Restore the user tablespaces in source database to read/write mode

Current scenario:
Source
version : 11.2.0.3
Database
: orcl
Platform 32 bit RHEL 5.4
Tablespaces : system, sysaux, users, example, undotbs1, temp
Target  
container database : cdb1
pluggable database : pdb_orcl
version : 12.1.0.1
Platform 64 bit OEL 5.4
Tablespaces : system, sysaux

Objective :

- Upgrade orcl (11.2.0.3 32 bit linux) to pdb_orcl(12.1.0.1 64 bit linux) using expdp transportable database
- While import rename users tablespace to users_orcl in target db

– Implementation –

— Source Database (ORCL) —

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

– Issue export command 
. TRANSPORTABLE=ALWAYS and FULL=Y : These parameter values tell Data Pump to use full transportable rather than conventional export methods.
.  VERSION=12   : Since source database is  11g Release 2 (11.2.0.3), we  must also specify the Data Pump parameter VERSION=12 to denote the fact that the result of the export will be imported into an Oracle Database 12c Release 1 (12.1) or later database. This is the only time that a version number greater than the current version is allowed by the expdp command. If the source database is Oracle Database 12c, with COMPATIBLE=12.0 or higher, then the VERSION parameter is not required.

Note that command fails as user tablespaces have not been put in read only mode.

[oracle@node1 bin]$ expdp system/oracle full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log
Export: Release 11.2.0.3.0 - Production on Mon Mar 24 16:53:13 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y version=12.0 transportable=always
Estimate in progress using BLOCKS method...
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is

ORA-29335: tablespace 'EXAMPLE' is not read only
 ORA-29335: tablespace 'USERS' is not read only
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at 16:53:20

– Set the user and application tablespaces in the source database to be READ ONLY

SQL> alter tablespace example read only;

              alter tablespace users read only;

– Issue expdp command again
After the export command completes, the export log file shows a list of all of the tablespace data files that need to be moved to the target.

[oracle@node1 bin]$ expdp system/oracle full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log

Export: Release 11.2.0.3.0 - Production on Tue Mar 25 09:38:23 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** full=y transportable=always version=12.0 dumpfile=exporcl.dmp logfile=exporcl.log
Estimate in progress using BLOCKS method...

...

Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/u01/app/oracle/admin/orcl/dpdump/exporcl.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
 /u01/app/oracle/oradata/orcl/example01.dbf
 Datafiles required for transportable tablespace USERS:
 /u01/app/oracle/oradata/orcl/users01.dbf
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 09:51:17

— TARGET CONTAINER DATABASE (CDB1) –

– Find out names of  the datafiles of   seed  database in cdb1

SQL> select file_name from cdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/users01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf

– create pluggable database pdb_orcl

SQL> create pluggable database pdb_orcl admin user pdbadmin identified by oracle file_name_convert=('pdbseed','pdb_orcl');

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB_ORCL MOUNTED

-- check that pdb_orcl has  datafiles for system/sysaux tablespaces only

SQL> alter pluggable database pdb_orcl open;

     select file_name from cdb_data_files where con_id=4;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb_orcl/system01.dbf
 /u01/app/oracle/oradata/cdb1/pdb_orcl/sysaux01.dbf

– Add  tnsnames.ora entry for pdb_orcl

PDB_ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = em12.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb_orcl)
)
)

– Copy the  export dump file and the data files for example and users tablespaces   from source to target

– copy example01.dbf and users01.dbf belonging to orcl to folder /u01/app/oracle/oradata/cdb1/pdb_orcl  on target machine

– copy exporcl.dmp containing  metadata  to the folder /u01/app/oracle/admin/cdb1 on target machine

– Create a directory object on the destination PDB (PDB_ORCL)  pointing to location of exporcl.dmp i.e. /u01/app/oracle/admin/cdb1

SQL> alter session set container=pdb_orcl;

sho con_name

CON_NAME
------------------------------
PDB_ORCL

SQL> create directory dump_dir as '/u01/app/oracle/admin/cdb1';

– Invoke full transportable import on the destination PDB

Specify following parameters :
- the dumpfile name
– full = Y and
– TRANSPORT_DATAFILES : to secify  the list of user tablespace data files to be transported

[oracle@em12 admin]$ impdp system/oracle@pdb_orcl full=y directory=dump_dir dumpfile=exporcl.dmp transport_datafiles='/u01/app/oracle/oradata/cdb1/pdb_orcl/example01.dbf', '/u01/app/oracle/oradata/cdb1/pdb_orcl/users01.dbf' remap_tablespace=users:users_orcl logfile=import_orcl.log

Import: Release 12.1.0.1.0 - Production on Tue Mar 25 11:41:44 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Source timezone version is +00:00 and target timezone version is -07:00.
Starting "SYSTEM"."SYS_IMPORT_FULL_02": system/********@pdb_orcl full=y directory=dump_dir dumpfile=exporcl.dmp transport_datafiles=/u01/app/oracle/oradata/cdb1/pdb_orcl/example01.dbf, /u01/app/oracle/oradata/cdb1/pdb_orcl/users01.dbf remap_tablespace=users:users_orcl logfile=import_orcl.log

....
Job "SYSTEM"."SYS_IMPORT_FULL_02" completed with 3395 error(s) at Tue Mar 25 12:07:27 2014 elapsed 0 00:25:40

– Check that target PDB’s metadata is updated with datafiles for example and users tablespaces  

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/pdb_orcl/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb_orcl/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb_orcl/example01.dbf
 /u01/app/oracle/oradata/cdb1/pdb_orcl/users01.dbf

–  Check that the application tablespaces are automatically placed in read/write mode on the destination database and users tablespace has been renamed to users_orcl

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
EXAMPLE ONLINE
USERS_ORCL ONLINE

 – Check that user HR has been created on the target PDB also 

SQL> conn hr/hr@pdb_orcl
    select tname from tab;

TNAME
--------------------------------------------------------------------------------
COUNTRIES
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
EMP_DETAILS_VIEW

– Restore user tablespaces to read-write mode on the source database

SQL> alter tablespace USERS read write;

alter tablespace example read write;
              select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE

References: 

http://docs.oracle.com/cd/E16655_01/server.121/e17636/transport.htm#ADMIN13727

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

Related Links:

Home

Database 12c

 

 

 

 

 

ORA-65023: active transaction exists in container CDB$ROOT

I received this error message in the following scenario:

– Connect to CDB

SQL> conn / as sysdba
sho con_name

CON_NAME
------------------------------
CDB$ROOT

– Start a transaction

SQL> create table test(c number);
     insert into test values(1) ;

– Change the container to PDB without completing the transaction

SQL> alter session set container=pdb2;

– Try to shutdown the pluggable database

– It causes ORA-65023 because  on switching the container (not   making a connection to  PDB using connect), pending transactions in the original container are still in a pending state

SQL> shu immediate;
ORA-65023: active transaction exists in container CDB$ROOT

That’s what oracle documentation says for this error:

Cause

A statement attempted to create a new transaction in the current container while there was an active transaction in another container.

Action

Switch to the container with the active transaction and commit, rollback or detach the active transaction before attempting to issue any statement that will attempt to create a new transaction in another container.

– Switch back to original container (cdb$root) and commit the transaction

SQL> alter session set container=cdb$root;
commit;

– Switch the container to PDB and close the PDB

– The attempt succeeds as there is no pending transaction in the parent container.

SQL> alter session set container=pdb2;
shu immediate;
Pluggable Database closed.

SQL> sho pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB2                           MOUNTED

References:

Oracle Documentation

——————————————————————————————–

Related Links:

Home

Database 12c

 

 

12c : DDL log does not identify the source PDB

In 12c, the location of DDL logs has changed from alert log to DDL log.

Oracle Database 11g

If instance parameter ENABLE_DDL_LOGGING = TRUE

DDL statements are logged into the alert log.

Oracle Database 12c

If instance parameter ENABLE_DDL_LOGGING = TRUE

DDL statement logging has been moved from alert log to DDL log
i.e. $ADR_HOME/log/ddl/log.xml
This file  contains only the DDL statements and dates.
– Let’s demonstrate

–Check that by default DDL logging is disabled

SQL> sho parameter ddl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE

– Enable DDL logging

SQL> alter system set enable_ddl_logging=true;

sho parameter ddl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     TRUE

– Remove all the trace files including alert log

[oracle@em12 ~]$ cd /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/

rm -rf *

ls

– check that there is no DDL log file in /u01/app/oracle/diag/rdbms/cdb1/cdb1/log/ddl

[oracle@em12 ddl]$ cd /u01/app/oracle/diag/rdbms/cdb1/cdb1/log/ddl
ls

– Issue a DDL while connected to CDB1

SQL> sho con_name

CON_NAME
------------------------------
CDB$ROOT

SQL>  create table test(x number);

– check that alert log has not been created as DDL’s are no longer logged to alert log

[oracle@em12 log]$ ls /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/

– check that ddl log file log.xml has been created in $ORACLE_BASE/diag/rdbms/cdb1/cdb1/log/ddl($ADR_HOME/log/ddl)

[oracle@em12 ddl]$ ls /u01/app/oracle/diag/rdbms/cdb1/cdb1/log/ddl/

log.xml

– check that the contents of the file give info about the DDL that was issued
Note that name of the database issuing the DDL (CDB$ROOT) is not there in the log

[oracle@em12 ddl]$ cat log.xml

<msg time='2014-03-05T13:08:33.207+05:30' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='em12.example.com' host_addr='192.9.201.150'
version='1'>
<txt>create table test(x number)
</txt>
</msg>

– Issue another DDL from PDB1

SQL> alter session set container=pdb1;
create table test(x number);

– check that the contents of the file give info about this DDL also
   Again note that name of the database issuing the DDL(PDB1) is not there in the log

[oracle@em12 ddl]$ cat log.xml

<msg time='2014-03-05T13:08:33.207+05:30' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='em12.example.com' host_addr='192.9.201.150'
version='1'>
<txt>create table test(x number)
</txt>
</msg>
<msg time='2014-03-05T13:09:11.104+05:30' org_id='oracle' comp_id='rdbms'
msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='em12.example.com' host_addr='192.9.201.150'>
<txt> create table test(x number)
</txt>
</msg>

It can be seen that it is not possible to identify  from this log whether the  DDL was issued from CDB$ROOT or which PDB (in case CDB has multiple PDB’s)

In 11g or 12c non-CDB, this issue is not there since the alert log belongs to one database only.

References:

Oracle Documentation

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

Related Links:

Home

Database 12c

12C : SQL MANAGEMENT BASE STORES PLAN ROWS TOO

The SQL management base (SMB) resides in the SYSAUX tablespace and stores SQL plan baselines, statement logs, plan histories, and SQL profiles.

  • In Oracle Database 11g, when DBMS_XPLAN.DISPLAY_PLAN_BASELINE is invoked to view a plan in SMB, the  execution plan is displayed by compiling the statement with the baseline, mainly using outlines. The plan rows are not stored in the SMB and hence the actual execution plan may not be reproduced.
  •  In Oracle Database12c, when a new plan is added to the plan history of a SQL statement, plan rows are also stored in the SMB . when DBMS_XPLAN.DISPLAY_PLAN_BASELINE is invoked to view a plan in SMB, the plan rows are displayed from the SMB. This facilitates easier diagnosability when plan cannot be reproduced.

Let’s demonstrate :

Overview:

11g Database

– Create table hr.emp which is a copy of hr.employees with primary key constraint and hence index on employee_id
– Issue a select statement which accesses hr.emp and load the execution plan into SMB
– verify that the plan stored in SMB is same as that was used when the statement was executed (Index access)
– Drop the primary key constraint and hence the index which was used during execution
– verify that the plan  that was used when the statement was executed cannot be reproduced as the index has been dropped

12c Database

– Create table hr.emp which is a copy of hr.employees with primary key constraint and hence index on employee_id
– Issue a select statement which accesses hr.emp and load the execution plan into SMB
– verify that the plan stored in SMB is same as that was used when the statement was executed (Index access)
– Drop the primary key constraint and hence the index which was used during execution
– verify that the plan  that was used when the statement was executed (index access) has been  reproduced even though the index has been dropped as the plan rows are stored in SMB

Implementation :

11g Database

– Create table hr.emp which is a copy of hr.employees with primary key constraint and hence index on employee_id

SQL>set line 500
    set pagesize 80

    drop table hr.emp purge;
    create table hr.emp as 
           select * from      hr.employees;
    alter table hr.emp 
    add constraint emp_pk primary key (employee_id);

 – Issue a select statement which accesses hr.emp using index

SQL>select employee_id, last_name
from hr.emp
where employee_id = 101;

– check that the statement is executed using the index

SQL>select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID  2t38scsy1hct2, child number 0
-------------------------------------
select employee_id, last_name from hr.emp where employee_id = 101

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|         |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    27 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|         |
--------------------------------------------------------------------------------------

– Load the execution plan into SMB

SQL> DECLARE
l_plans_loaded  PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id=>'2t38scsy1hct2');
END;
/

– Check that the plan has been loaded into SMB

SQL> col SQL_HANDLE for a25
col plan_name for a30
select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
from dba_sql_plan_baselines
WHERE sql_text like '%select employee_id, last_name%';

SQL_HANDLE                PLAN_NAME                      ENA ACC FIX
------------------------- ------------------------------ --- --- ---
SYS_SQL_90c5335bf23b296d  SQL_PLAN_91j9mbgt3qabd447b331a YES YES NO

– verify that the plan stored in SMB is same as that was used when the statement was executed

SQL> set line 999
set pagesize 999

select *
from table(dbms_xplan.display_sql_plan_baseline
(plan_name=>'SQL_PLAN_91j9mbgt3qabd447b331a'));

SQL handle: SYS_SQL_90c5335bf23b296d
SQL text: select employee_id, last_name from hr.emp where employee_id = 101
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_91j9mbgt3qabd447b331a         Plan id: 1148924698
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    27 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    27 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

– Drop the primary key constraint and hence the index which was used during execution

SQL>alter table hr.emp drop primary key;

-- verify that the plan  that was used when the statement was executed cannot be reproduced as the index has been dropped

SQL>select *
from  table(dbms_xplan.display_sql_plan_baseline
(plan_name=>'SQL_PLAN_91j9mbgt3qabd447b331a'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_90c5335bf23b296d
SQL text: select employee_id, last_name from hr.emp where employee_id = 101
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_91j9mbgt3qabd447b331a         Plan id: 1148924698
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |   108 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     4 |   108 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

12c Database

– Create table hr.emp which is a copy of hr.employees with primary key constraint and hence index on employee_id

SQL>set line 500
    set pagesize 80

    drop table hr.emp purge;
    create table hr.emp as 
           select * from      hr.employees;
    alter table hr.emp 
    add constraint emp_pk primary key (employee_id);

 – Issue a select statement which accesses hr.emp using index

SQL>select employee_id, last_name
from hr.emp
where employee_id = 101;

– check that the statement is executed using the index

SQL>select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  2t38scsy1hct2, child number 0
-------------------------------------
select employee_id, last_name from hr.emp where employee_id = 101

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

– Load the execution plan into SMB

SQL> DECLARE
l_plans_loaded  PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id=>'2t38scsy1hct2');
END;
/

– Check that the plan has been loaded into SMB

SQL>col SQL_HANDLE for a25
col plan_name for a30
 select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
 from dba_sql_plan_baselines
 WHERE sql_text like '%select employee_id, last_name%';

SQL_HANDLE                PLAN_NAME                      ENA ACC FIX
 ------------------------- ------------------------------ --- --- ---
 SQL_90c5335bf23b296d      SQL_PLAN_91j9mbgt3qabd447b331a YES YES NO

– verify that the plan stored in SMB is same as that was used when the statement was executed

SQL> set line 999
set pagesize 999

select *
from table(dbms_xplan.display_sql_plan_baseline
(plan_name=>'SQL_PLAN_91j9mbgt3qabd447b331a'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL handle: SQL_90c5335bf23b296d
SQL text: select employee_id, last_name from hr.emp where employee_id = 101
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_91j9mbgt3qabd447b331a         Plan id: 1148924698
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

– Drop the primary key constraint and hence the index which was used during execution

SQL>alter table hr.emp drop primary key;

-- verify that the plan  that was used when the statement was executed has been  reproduced even thoughthe index has been dropped as the plan rows are stored in SMB

SQL>select *
from table(dbms_xplan.display_sql_plan_baseline
(plan_name=>'SQL_PLAN_91j9mbgt3qabd447b331a'));

PLAN_TABLE_OUTPUT
----------------------------------
--------------
SQL handle: SQL_90c5335bf23b296d
SQL text: select employee_id, last_name from hr.emp where employee_id = 101
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_91j9mbgt3qabd447b331a         Plan id: 1148924698
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
Plan rows: From dictionary
--------------------------------------------------------------------------------

Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    12 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

I hope this post was useful.

Your comments and suggestions are always welcome!

—————————————————————-

Related Links:

Home

Database Index
Oracle 12c Index
Parameters Deprecated  in 12c
Undocumented Parameters in 12c

———————————————————————————–

SERVICE CREATED USING EM DOES NOT UPDATE DATA DICTIONARY OR OCR

Recently, I discovered a bug in EM. If we create a database service using EM, it updates TAF settings (Failover type, failover method, retries and delay) only in tnsnames.ora on all the nodes . Data dictionary and OCR are not updated.

Current scenario:

Name of RAC database : orcl
No. of Nodes  : 2
Names of nodes : host01, host02

— Create database service orcl_em using EM --

Name of service orcl_em

Attributes :

Start service after creation : Yes
Update local naming parameter (tnsnames.ora) file  : Yes
preferred instance : orcl1
Available instance : orcl2
TAF policy         : Basic

– After the service has been created, check that service is running on preferred instance orcl1

[oracle@host01 ~]$ srvctl status service -s orcl_em -d orcl

Service orcl_em is running on instance(s) orcl1

– check that the tnsnames.ora entry contains TAF settings as specified :

orcl_em =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = cluster01-scan.cluster01.example.com)
(PORT = 1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl_em)
  (FAILOVER_MODE =
         (TYPE = SELECT)
         (METHOD = BASIC)
         (RETRIES = 180)
         (DELAY = 5))))

– check that data dictionary view is not aware of TAF settings

SQL> col name for a15
col failover_type for a15
col failover_method for a15

select name, FAILOVER_METHOD, FAILOVER_TYPE, FAILOVER_RETRIES, FAILOVER_DELAY
from dba_services
where upper(name) = 'ORCL_EM';

NAME            FAILOVER_TYPE   FAILOVER_METHOD FAILOVER_RETRIES FAILOVER_DELAY
--------------- --------------- --------------- ---------------- --------------
orcl_em

– check that the resource profile as stored in OCR also does not contain any information about TAF settings

[oracle@host01 admin]$ srvctl config service -s orcl_em -d orcl
Service name: orcl_em
Service is enabled
Server pool: orcl_orcl_em
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Preferred instances: orcl1
Available instances: orcl2

It can be clearly seen that TAF attributes are updated only in tnsnames.ora . They have not been updated in data dictionary or OCR.

– Now let us create and start another database service orcl_manual using srvctl

[oracle@host01 admin]$ srvctl add service -s orcl_manual -r orcl1 -a orcl2 -m BASIC -e SELECT -w 100 -z 5

[oracle@host01 admin]$ srvctl start service -s orcl_manual -d orcl

– Check that TAF attributes have been updated in OCR

[oracle@host01 admin]$ srvctl config service -s orcl_manual -d orclService name: orcl_manual
Service is enabled
Server pool: orcl_orcl_manual
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 5
TAF failover delay: 100
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Preferred instances: orcl1
Available instances: orcl2

– Check that TAF settings have been updated in data dictionary too

SQL> select name, failover_type, failover_method, failover_retries, failover_delay
from dba_services
where upper(name) = 'ORCL_MANUAL';

NAME            FAILOVER_TYPE   FAILOVER_METHOD FAILOVER_RETRIES FAILOVER_DELAY
--------------- --------------- --------------- ---------------- --------------
orcl_manual     SELECT          BASIC                          5            100

Hence, database services for RAC database should always be created using srvctl and tnsnames.ora entry should be  added manually.

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

Related Links:

Home

11g R2 RAC Index

11g R2 RAC :Services
Tracing Services in RAC

 

 

 

 

VIP FAILOVER DEMONSTRATION

Recently, a student of mine wanted me to demonstrate failover of VIP.

Here is the demonstration:

Current scenario:

2 node 11.2.0.1 RAC cluster with SCAN and GNS

nodes : host01, host02

Overview:

- check that VIP’s of both the nodes are running on respective nodes
– Stop clusterware on host01
– check that VIP of host01  has failed over to host02
– Trace route of request to connect  to VIP of host01 and verify that connection is not made as listener on host02 accepts requests    to its own VIP only
– Trace route of request to connect  to VIP of host02 and verify  that connection is made  as listener on host02 accepts requests    to host02′s VIP
– start clusterware on host01
– check that host01 vip is running on host01 again
– Trace route of request to connect  to VIP of host01 and verify that connection is made  as listener on host01 accepts requests   to its own  VIP

Implementation :

- check that VIP’s of both the nodes are running on respective nodes

[root@host02 ~]# srvctl status nodeapps -n host01

VIP 192.9.201.247 is enabled
VIP 192.9.201.247 is running on node: host01
Network is enabled
Network is running on node: host01

[root@host02 ~]# srvctl status nodeapps -n host02

VIP 192.9.201.235 is enabled
VIP 192.9.201.235 is running on node: host02
Network is enabled
Network is running on node: host02

- Stop clusterware on host01

[root@host01 ~]# crsctl stop crs

- check that VIP of host01 (192.9.201.247) has failed over to host02

[root@host02 ~]# srvctl status nodeapps -n host01

VIP 192.9.201.247 is enabled
VIP 192.9.201.247 is running on node: host02
Network is enabled
Network is not running on node: host01

– Trace route of request to connect  to VIP of host01 i.e. 192.9.201.147
– It can be seen there is no listener as listener on host02 accepts requests    to host02′s VIP (192.9.201.235) only

[root@host02 ~]# trcroute 192.9.201.247

Route of TrcRoute:
------------------

Node: Client            Time and address of entry into node:
-------------------------------------------------------------
08-JAN-2014 14:14:39 ADDRESS= PROTOCOL=TCP  HOST=192.9.201.247  PORT=1521

TNS-12224: TNS:no listener
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-03601: Failed in route information collection

– Trace route of request to connect  to VIP of host02 i.e. 192.9.201.235
– It can be seen that connection is made  as listener on host02 accepts requests    to host02′s VIP

[root@host02 ~]# trcroute 192.9.201.235

Route of TrcRoute:
------------------

Node: Client            Time and address of entry into node:
-------------------------------------------------------------
08-JAN-2014 14:14:51 ADDRESS= PROTOCOL=TCP  HOST=192.9.201.235  PORT=1521

Node: Server            Time and address of entry into node:
-------------------------------------------------------------
08-JAN-2014 14:14:52 ADDRESS= PROTOCOL=TCP  HOST=192.9.201.235  PORT=1521

– start clusterware on host01

[root@host01 ~]# crsctl start crs

– check that host01 vip (192.9.201.147) is running on host01 again

[root@host02 ~]# srvctl status nodeapps -n host01

VIP 192.9.201.247 is enabled
VIP 192.9.201.247 is running on node: host01
Network is enabled
Network is running on node: host01

– Trace route of request to connect  to VIP of host01 i.e. 192.9.201.247
– It can be seen that connection is made  as listener on host01 accepts requests   to its own  VIP

[root@host02 ~]# trcroute 192.9.201.247

Route of TrcRoute:
------------------

Node: Client            Time and address of entry into node:
-------------------------------------------------------------
08-JAN-2014 14:23:00 ADDRESS= PROTOCOL=TCP  HOST=192.9.201.247  PORT=1521

Node: Server            Time and address of entry into node:
-------------------------------------------------------------
08-JAN-2014 14:23:00 ADDRESS= PROTOCOL=TCP  HOST=192.9.201.247  PORT=1521

I hope this post was useful.

Your comments and suggestions are always welcome.
—————————————————————————————-

Related LInks:

Home

11g R2 RAC INDEX

Need For VIP In RAC

————————————————————————————–

 

RECOVER STANDBY DATAFILE FROM PRIMARY

In this post, I will demonstrate how we can recover a lost/corrupted/inaccessible datafile on standby from primary.

Overview:

– Simulate loss of a datafile on standby database by renaming it.
– Restart standby database – Stops at mount stage as datafile is inaccessible.
– Check that redo apply to standby is stopped.
– Connect to primary database as target and standby as auxiliary.
– Take backup of the affected datafile from primary  so that backup file is created on standby.
– Recover standby database.
– Open standby database.
– check that redo apply has been restrated on standby and configuration is successful again.

– Implementation –

– Check that dataguard configuration is running properly

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
dg02 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

– Find out names of datafiles on standby

SBY>select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dg02/system01.dbf
/u01/app/oracle/oradata/dg02/sysaux01.dbf
/u01/app/oracle/oradata/dg02/undotbs01.dbf
/u01/app/oracle/oradata/dg02/users01.dbf
/u01/app/oracle/oradata/dg02/example01.dbf

– To simulate loss of the datafile of example tablespace , rename it

SBY>ho mv /u01/app/oracle/oradata/dg02/example01.dbf /u01/app/oracle/oradata/dg02/example.dbf

– Restart standby database
— Stops at mount stage due to missing datafile

SBY>startup force;
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
Database mounted.
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/dg02/system01.dbf'

-- Switch logs on primary and verify that redo apply has stopped on standby

PRI>alter system switch logfile;

DGMGRL>  show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
dg02 - Physical standby database
 Error: ORA-16766: Redo Apply is stopped

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

– Using RMAN, connect to primary as target and standby as auxiliary

[oracle@node1 ~]$ . oraenv
ORACLE_SID = [dg01] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle

[oracle@node1 ~]$ rman target / auxiliary sys/oracle@dg02

connected to target database: DG01 (DBID=434142737)
connected to auxiliary database: DG01 (DBID=434142737, not open)

RMAN>

– Try to take backupset type of backup of example tablespace on primary so that backup file is created on standby
— Fails as only image copies can be transported over network using RMAN

RMAN> backup tablespace example auxiliary format
2> '/u01/app/oracle/oradata/dg02/example01.dbf';

Starting backup at 26-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/26/2013 14:37:04
RMAN-06955: Network copies are only supported for image copies.

– Take image copy backup of example tablespace on primary so that backup file is created on standby

RMAN> backup as copy  tablespace example auxiliary format
2> '/u01/app/oracle/oradata/dg02/example01.dbf';

Starting backup at 26-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/dg01/example01.dbf
output file name=/u01/app/oracle/oradata/dg02/example01.dbf tag=TAG20131226T143727
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 26-DEC-13

– check that image copy copy has been created on standby

SBY>ho ls /u01/app/oracle/oradata/dg02/example01.dbf
/u01/app/oracle/oradata/dg02/example01.dbf

– Recover standby database and open it

SBY>recover managed standby database disconnect;
alter database open;

– Check that redo apply is resumed again and configuration is successful

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
dg02 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

—————————————————————————————————–

Related links:

Home
11g Dataguard Index

11g  DataGuard: Flashback Standby After Resetlogs On Primary11g  DataGuard Setup Using Active DataGuard on RHEL/OEL- 5 
Flashback Through Role Transition For Physical Standby

 

—————