Category Archives: 12c

Configure tnsnames.ora file for PDB

June 6, 2016 Larry Catt Leave a comment

Oracle 12c multitenant database normally uses the same oracle networking files as other Oracle RDBMS implementations. This article covers the configuration of the tnsnames.ora file for connection to a PDB database cdb1.

Connect to your oracle database server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Apr 24 10:45:22 EDT 2016 on pts/1

[oracle@linux2 ~]$

You can use the following code to create the appropriate tnsnames.ora entries in the file $ORACLE_HOME/network/admin/tnsnames.ora or manually insert into the file knowing the machine IP, Port, SID, and service name.

NOTE: You have to edit the parameter SERVICE_NAME and Alias, in below example they are pdb1 and PDB1 respectively. PDBs do not have SID listings because they are Services which are contained within a CDB and only CDBs have SIDs in multitenant architecture.

echo -e “PDB1 =\n” \

” (DESCRIPTION =\n” \

” (ADDRESS_LIST =\n” \

” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

” )\n” \

” (CONNECT_DATA =\n” \

” (SERVER = DEDICATED)\n” \

” (SERVICE_NAME = pdb1)\n” \

” (SID = “$ORACLE_SID”)\n” \

” )\n” \

” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

Execution Example:

[oracle@linux2 admin]$ echo -e “PDB1 =\n” \

> ” (DESCRIPTION =\n” \

> ” (ADDRESS_LIST =\n” \

> ” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

> ” )\n” \

> ” (CONNECT_DATA =\n” \

> ” (SERVER = DEDICATED)\n” \

> ” (SERVICE_NAME = pdb1)\n” \

> ” (SID = “$ORACLE_SID”)\n” \

> ” )\n” \

> ” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

[oracle@linux2 admin]

After execution or editing, you can view the contains of the tnsnames.ora file with the cat command as shown below:

[oracle@linux2 admin]$ cat tnsnames.ora

CDB1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cdb1)

(SID = cdb1)

)

PDB1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb1)

(SID = cdb1)

)

[oracle@linux2 admin]$

As long as the listener processes is started, the container database is up and running, and the respective PDB is up; you can test the connection through the tnsping utility by suppling the reference alias name in the tnsnames.ora file. In this example the reference name is PDB1, as shown below.

[oracle@linux2 admin]$ tnsping PDB1

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 24-APR-2016 11:56:34

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1) (SID = cdb1)))

OK (0 msec)

[oracle@linux2 admin]$

This completes setup of tnsnames.ora file for PDB connection in Oracle 12c.

Larry Catt

OCP

12c, audit

Oracle 12c – Enabling Unified Audit Data Trail

June 3, 2016 Larry Catt Leave a comment

In 12c the Unified Audit Trail consolidates audit information from multiple sources and makes this information available in a standard format in the UNIFIED_AUDIT_TRAIL dictionary view. This article describes enabling the Unified Audit Data Trail.

Logon to your oracle server as the oracle software owner and logon to sqlplus with sysdba privileges.
To determine if Unified Auditing is enabled, execute the query: select value from v$option where parameter=’Unified Auditing’;

SQL> select value from v$option where parameter=’Unified Auditing’;

VALUE

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

FALSE

SQL>

NOTE: that Unified Auditing is not currently enabled.
Shutdown the database instance with command: shutdown immediate.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

Exit out of SQLPLUS and shutdown the listener utility with the OS command: lsnrctl stop

SQL> exit

Disconnected

[oracle@linux2 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-APR-2016 10:13:21

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.1.101)(PORT=1521)))

The command completed successfully

[oracle@linux2 ~]$

Change directory to $ORACLE_HOME/rdbms/lib and execute the make command to relink oracle with ‘uniaud_on’ option.

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk uniaud_on ioracle

Restart Oracle listener with command: lsnrctl start

[oracle@linux2 lib]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-APR-2016 10:17:53

Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 12.1.0.2.0 – Production

System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.1.101)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.1.101)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date 15-APR-2016 10:17:53

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.1.101)(PORT=1521)))

Services Summary…

Service “cdb1” has 1 instance(s).

Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

[oracle@linux2 lib]$

Logon to Oracle SQLPLUS with sysdba privileges and startup the database with command: startup.

[oracle@linux2 lib]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu APR 15 10:18:25 2016

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 549453824 bytes

Fixed Size 2926616 bytes

Variable Size 268437480 bytes

Database Buffers 272629760 bytes

Redo Buffers 5459968 bytes

Database mounted.

Database opened.

SQL>

Check the status of Unified Auditing with command: select value from v$option where parameter=’Unified Auditing’;

SQL> select value from v$option where parameter=’Unified Auditing’;

VALUE

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

TRUE

SQL>

This completes the enablement of Unified Audit Data in 12c

Larry Catt

OCP

12c, Performance, storage, tablespaces

Oracle 12c – Manage VLDB

May 29, 2016 Larry Catt Leave a comment

Very Large Databases use partitioning to improve the ability to support large data sets. Breaking large tables into smaller manageable chunks. This allows for partition pruning, which prevents access to partitions which will not have data queried. Partitioning and indexing work together to provide parallel processing to search multiple partitions at same time as in data ware housing.

Partitioning Methods:

You can partition data into a single level or two level for a composite partition. How the data is accessed determines the best implementation method. Regardless of partitioning method, you have three ways to control distributing of partitions:

Range
Hash
List

Single Level Partitioning

One or more of the columns acts as the partition key spitting the table into multiple sections.

Range Partitioning – maps data to a range of values in the partition key. Each row is identified with a mapping to partition key. This is the most common partitioning method and is commonly used with dates. Partition is created with a VALUES LESS THEN clause which show the upper bound of a date column. The highest partition can use a MAXVALUE to provide an upper limit.
Hash Partitioning – This method uses a hashing algorithm to map data to partitioning key. Used to evenly spread data among partitions. It is easier to implement then range. Good to use if no date column exists and no obvious partitioning key exists.
List Partitioning – Allows you to provide a list of values for partitioning key column and divides data base on list. You control exactly how rows are mapped to partitions. You list a set of values to be placed in separate partitions and use the DEFAULT clause to specify a partition for any value not in the list.

Composite Partitions

This combines the basic partitioning methods into multiple sub-partitions of the same method or different methods. Provides for a finer level of sub-division of data. Possible composite partitions are:

Range-Range
Range-Hash
Range-List
List-Range
List-Hash
List-List
Hash-Hash

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Startup CDB

May 27, 2016 Larry Catt Leave a comment

In Oracle 12c multitenant databases consist of a container database CDB which contain one or more pluggable databases PDB. Startup of the CDB can be done with none of the PDBs it contains being started. This procedures covers the startup of a CDB.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Apr 24 14:55:04 EDT 2016 on pts/1

[oracle@linux2 ~]$

No CDB in non-RAC system acts the same as any Oracle single instance database with three startup modes: NOMOUNT, MOUNT, and OPEN. Please see related article about modes. You can check for running CDB instances from the OS layer by searching for the SMON process. This does not show what startup mode the instance is in.

[oracle@linux2 ~]$ ps -ef|grep smon

oracle 2660 2395 0 09:34 pts/1 00:00:00 grep –color=auto smon

oracle 3849 1 0 Apr24 ? 00:00:02 ora_smon_cdb1

[oracle@linux2 ~]$

This shows that there is one instance started in some mode called cdb1. In this example our CDB database is cdb1. NOTE: You cannot validate any PDB in this manner. PDBs are services within a CDB and cannot be viewed via OS layer.

Since this CDB is up and running, we will first demonstrate the shutdown process which is identical to non-CDB databases in single instance mode with three options: IMMEDIATE, ABORT, NORMAL. Please see related articles about modes. You have to connect to CDB as the SYSDBA privileged user to perform shutdown as shown below.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 25 09:39:29 2016

Connected to:

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

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

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

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

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

[oracle@linux2 ~]$

Now to startup the CDB, again you have to connect with SYSDBA privileged user, as shown below. Open is the default mode.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 25 09:41:06 2016

Connected to an idle instance.

SQL> startup open

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 2928728 bytes

Variable Size 524292008 bytes

Database Buffers 201326592 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

SQL>

Note you have to startup the listener process to enable oracle networking, but as far as the CDB is concerned, the database is ready for use.

This completes startup of CDB in Oracle 12c.

Larry Catt

OCP

12c, backup/recovery, flashback, RMAN

Oracle 12c – Enabling Flashback Database

May 26, 2016 Larry Catt Leave a comment

In Oracle 12c Flashback Database must be configured which allow for tracking of changes for the database to revert to. This article details the steps in enabling flashback database feature.

Logon to server as the oracle software owner.

[root@orclc-db1 devadmin]# su – oracle

Last login: Mon Feb 19 18:31:43 UTC 2016 on pts/0

mylinux#

Open or mount the oracle database.

mylinux# sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 20 12:41:43 2016

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 5.1540E+10 bytes

Fixed Size 2938792 bytes

Variable Size 3.4628E+10 bytes

Database Buffers 1.6777E+10 bytes

Redo Buffers 131276800 bytes

Database mounted.

SQL>

Set the DB_FLASHBACK_RETENTION_TARGET initialization parameter to time period of recovery, default is 1440 minutes or 1 day.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=2880 scope=both;

System altered.

SQL>

Set the initialization parameter DB_RECOVERY_FILE_DEST_SIZE to a suitable size. NOTE: This is simple a test system so we area setting the recovery size to 4GB, a real system will require a larger size.

SQL> alter system set db_recovery_file_dest_size = 4g scope=both;

System altered.

Set the initialization parameter DB_RECOVERY_FILE_DEST to a suitable large enough space one disk.

SQL> alter system set db_recovery_file_dest=’ /u04/oradata/ORCL/flash_recovery_area’ scope=both;

System altered.

SQL>

Ensure that archivelog mode is enabled and enable if not.

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /opt/app/oracle/orcl_db/dbs/arch

Oldest online log sequence 1613

Current log sequence 1633

Archiving log is not currently enabled, restart the database in mount mode and enable archiving.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 549453824 bytes

Fixed Size 2926616 bytes

Variable Size 268437480 bytes

Database Buffers 272629760 bytes

Redo Buffers 5459968 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL>

Enable flashback for database with ALTER DATABASE command, before opening database.

SQL> alter database flashback on;

Database altered.

SQL>

Alter the database to open.

SQL> alter database open;

Database altered.

SQL>

This completes enabling flashback for your Oracle RDBMS.

Larry Catt

OCP

12c, CDB, network

Configure tnsnames.ora file for CDB

May 6, 2016 Larry Catt Leave a comment

Connect to your oracle database server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Apr 24 10:45:22 EDT 2016 on pts/1

[oracle@linux2 ~]$

You can use the following code to create the appropriate tnsnames.ora entries in the file $ORACLE_HOME/network/admin/tnsnames.ora or manually insert into the file knowing the machine IP, Port, SID, and service name.

echo -e “CDB1 =\n” \

” (DESCRIPTION =\n” \

” (ADDRESS_LIST =\n” \

” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

” )\n” \

” (CONNECT_DATA =\n” \

” (SERVER = DEDICATED)\n” \

” (SERVICE_NAME = “$ORACLE_SID”)\n” \

” (SID = “$ORACLE_SID”)\n” \

” )\n” \

” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

Execution Example:

[oracle@linux2 admin]$ echo -e “CDB1 =\n” \

> ” (DESCRIPTION =\n” \

> ” (ADDRESS_LIST =\n” \

> ” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

> ” )\n” \

> ” (CONNECT_DATA =\n” \

> ” (SERVER = DEDICATED)\n” \

> ” (SERVICE_NAME = “$ORACLE_SID”)\n” \

> ” (SID = “$ORACLE_SID”)\n” \

> ” )\n” \

> ” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

[oracle@linux2 admin]

After execution or editing, you can view the contains of the tnsnames.ora file with the cat command as shown below:

oracle@linux2 admin]$ cat tnsnames.ora

CDB1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cdb1)

(SID = cdb1)

)

As long as the listener process and database is up and running, you can test the connection through the tnsping utility by suppling the reference name in the tnsnames.ora file. In this example the reference name is CDB1, as shown below.

[oracle@linux2 ~]$ tnsping CDB1

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 24-APR-2016 11:49:44

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb1) (SID = cdb1)))

OK (0 msec)

[oracle@linux2 ~]$

This completes setup of tnsnames.ora file for CDB connection in Oracle 12c.

Larry Catt

OCP

12c, OPatch

Oracle 12c – Apply and review patches

April 14, 2016 Larry Catt Leave a comment

In Oracle 12c, Patching is performed to correct discovered bugs and close security vulnerabilities. Oracle provides four types of Patches:

Interim Patches – single/collection of bug fixes or one-offs.

Diagnostic Patches – Diagnose or verify a fix.

Patch Set Updates (PSU) – Collection of high impact, low risk, proven fixes for products and components

Critical Patch Updates (CPU) – Collection of patches for security vulnerabilities.

Patching of the RDBMS can be done with two methods: Manually or EM Cloud Control Patch Wizard. Additionally, the patching processes have added: Online Patching and Queryable Inventories. This article will give a brief overview of these Features.

Manual Patching

Step in manually patching are:

Logon to oracle support https://support.oracle.com
Click Patches & Updates tab.
Enter the patch number you are looking for on Patches % Updates page.
Click download on patch for your OS
Click download Patch Metadata.
Review the documentation for specific installation instructions.
Use the OPatch utility to install all patches.

EM Cloud Control Patch Wizard

The EM Cloud Control Patch Wizard has the following benefits over Manual Patching:

Integrate Patch work with Oracle support and provides single view to determine patch needs.
End to End workflow of patching, resolving patch conflicts.
Easy determination of Patch needs for single system.
Patch plans can be saved as templates.
Out of place patching is possible for support of RAC and GRID infrastructure.
EM Cloud allows you create a patch plan to apply patches to one or more targets. A patch can be added to plan only if it’s for same release and OS.

EM Cloud can apply patches with two methods:

Online Mode – EM connects to Oracle Support, searches for necessary patches, resolves any conflicts and adds them to patch plan.
Offline Mode – Used when EM cannot connect to Oracle Support. EM searches for patches manually uploaded to software library and adds them to patch plan.

Online Patches

Before 11g all patches require relink of RDBMS libraries and a shutdown of system. Now a large number of Patches can be done online, they contain .so files and do not require relink of RDBMS.

Benefit of Online Patches:

No downtime.
persistent across shutdown
Allow rolling patches in RAC
Fast install.

Downside of Online Patches:

Require more memory
Online patches not available for all OS
Not all patches are hot patches.

Queryable Patch Inventory

Queryable Patch inventory is new 12c, implemented with DBMS_QOPATCH package and provides view of patches installed just as OPATCH lsinventory command. DBMS_QOPATCH queries the OUI Oracle Universal Installer inventory to gather this metadata.

It provides the following:

Query patches installed from SQL prompt.
Creates reports and provides validation across many installs.
Checks patches on RAC from single node.

DBMS_QOPATCH components:

GET_OPTACH_BUGS – Provides list of bug fixes per patch number.

GET_OPATCH_COUNT – Provides total number of patches installed.

GET_OPATCH_DATA – Provides top level patch info – number, creation date.

GET_OPATCH_FILES – Provides list of files updated by patch number.

GET_OPATCH_INSTALL_INFO – Provides info such as ORACLE_HOME, patch, inventory_location

GET_OPATCH_LIST – List of Patches installed.

GET_OPATCH_LSINVENTORY – Whole patch inventory

GET_OPATCH_OLAYS –Overlay for specific patch

GET_OPATCH_PREQS – Provides prerequisites for a patch

GET_OPATCH_XSLT – Provides style sheet for opatch XML inventory

GET_PENDING_ACTIVITY – Provides SQL patches applied to single inventory from binaries.

GET_SQLPATCH_STATUS – Patch status from SQL patch registry.

IS_PATCH_INSTALLED – Patches applied info from XML inventory

PATCH_CONFLICT_DETETION – Patch in conflict for give file.

SET_CURRENT_OPINST – inventory information for specific node in RAC.

Larry Catt

OCP

12c, audit

Oracle 12c – Enable and configure Unified Audit Data Trail

April 13, 2016 Larry Catt Leave a comment

In 12c the Unified Audit Trail consolidates audit information form multiple sources and makes this information available in a standard format in the UNIFIED_AUDIT_TRAIL dictionary view.
The Unified Audit Trail is stored in a read-only table in the AUDSYS schema in SYSAUX tablespace
Data is available to SYS and users with AUDIT_ADMIN and AUDIT_VIEWER roles.
AUDIT_ADMIN can view data and create audit policies.
AUDIT_VIEWER can only view data.
The Unified Audit Data Trail combines the following:
Unified audit policies and AUDIT settings

Fine-grained audit records from DBMS_FGA
Oracle Database Real Application Security
Oracle Recovery Manager
Oracle Database Vault
Oracle Label Security
Oracle Data Mining
Oracle Data Pump
Oracle SQL* Loader Direct Load

Once configured the unified auditing is always enabled and does not depend on initialization parameters.
If the database is open in READ-ONLY mode, audit records are written to new OS files in $ORACLE_BASE/audit/$ORACLE_SID directory.
The V$OPTION view can be queried to determine if DB is using unified auditing.

Select value from v$option where parameter=’Unified Auditing’;

Parameter Value

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

Unified Auditing TRUE

New 12c database mixed mode auditing is enabled by default by predefined policy
Mixed-mode auditing enables both Traditional (pre-12c auditing and unified auditing.
Traditional auditing is controlled by AUDIT_TRAIL init parameters.
When AUDIT_TRAIL init parameter is parameter other than NONE, the traditional audit trail will be populated as well as the Unified Audit trail.
Auditing can be set to individual PDBs or CDB as a whole.
In Multi-tenant DB each PDB and root CDB has its own audit trail.
When upgrading to 12c, you have to manually migrate to unified auditing to enable.
Once unified auditing is enabled, traditional is disabled.
To start using Unified, at least one unified policy must be enabled.
To stop using Unified, disable all unified policies.
Predefined policy ORA_SECURECONFIG is initially enabled on all new 12c databases.

Larry Catt

OCP

12c, SQL

Oracle 12c – Adaptive Execution Plan

April 10, 2016 Larry Catt Leave a comment

In Oracle 12c Adaptive Execution Plans allows optimizer to make Janision on SQL at runtime and in mid execution. It will actually make sub-plans and collect statistics to make final Janision on execution. Currently release only supports adaptive execution plans for join and parallel operations.

This article will demonstrate the use of Adaptive Execution Plan against a set test tables.

Logon server as the oracle software owner and logon to SQLPLUS as the user scott.

[root@linux2 ~]# su – oracle

Last login: Thu Jan 15 13:05:52 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 schema]$ sqlplus scott/password

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 23 08:12:04 2016

Last Successful login time: Fri Jan 23 2016 08:10:57 -05:00

Connected to:

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

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

Create the user test with dba privileges.

SQL> create user test identified by test

default tablespace users quota unlimited on users; 2

User created.

SQL> grant dba to test;

Grant succeeded.

Create a test table test_tab1 and 200 insert values.

SQL> create table test.test_tab1(

col_a number,

col_b varchar2(10),

constraint tab1_pk primary key(col_a)); 2 3 4

Table created.

SQL> Janlare

2 val_a number:=1;

3 val_b varchar2(10):=’a’;

5 begin

7 while val_a<200

8 loop

9 insert into test.test_tab1(col_a, col_b)

10 values(val_a, val_b);

12 val_a:=val_a+1;

13 if val_b=’z’

14 then

15 val_b:=’a’;

16 else

17 val_b:=chr(ascii(val_b) + 1);

18 end if;

19 end loop;

20 commit;

21 end;

22 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

Create test table test_tab2 with foreign key back to test_tab1. NOTE: Adaptive Execution only work with join operations.

SQL> create table test.test_tab2(

tab1_col_a number,

col_a number,

col_b varchar2(10),

constraint tab2_tab1_fk foreign key (tab1_col_a) references test.test_tab1(col_a)); 2 3 4 5

Table created.

SQL>

Populate test table test_tab2.

SQL> insert into test.test_tab2(tab1_col_a, col_a, col_b)

select col_a, col_a+1, ‘testing’

from test.test_tab1 where 1=mod(col_a,2); 2 3

100 rows created.

SQL> commit;

Commit complete.

SQL>

Perform an explain for a select join operation on the two tables.

SQL> explain plan for select * from test.test_tab2 b, test.test_tab1 a

where a.col_a=tab1_col_a 2

3 ;

Explained.

SQL>

Now examine the execution plan and not the adaptive remark at the bottom.

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1368181317

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

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

| 0 | SELECT STATEMENT | | 100 | 5300 | 6 (0)| 00:00:01 |

|* 1 | HASH JOIN | | 100 | 5300 | 6 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| TEST_TAB2 | 82 | 2706 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| TEST_TAB1 | 82 | 1640 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 – access(“A”.”COL_A”=”TAB1_COL_A”)

Note

—–

– dynamic statistics used: dynamic sampling (level=AUTO)

– this is an adaptive plan

20 rows selected.

SQL>

Now use the format => adaptive reference in the dbms_xplan.display function to get the adaptive plan.

SQL> SET LINESIZE 200 PAGESIZE 100

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => ‘adaptive’));SQL>

PLAN_TABLE_OUTPUT

SQL_ID ff5spha9pjuyu, child number 0

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

select * from table(dbms_xplan.display)

Plan hash value: 2137789089

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

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

| 0 | SELECT STATEMENT | | | | 29 (100)| |

| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |

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

13 rows selected.

SQL>

This completes testing of adaptive execution plan.

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Startup all PDB in a CDB

April 8, 2016 Larry Catt Leave a comment

In Oracle 12c multitenant databases consist of a container database CDB which contain one or more pluggable databases PDB. Startup of the PDB must be done after the CDB it is contained in has been started. This procedures covers the startup and shutdown of all PDBs in a CDB.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Mar 24 14:55:04 EDT 2016 on pts/1

[oracle@linux2 ~]$

Logon to your CDB where the PDB you wish to start is stored.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 25 09:41:06 2016

Connected to an idle instance.

SQL> startup open

ORACLE instance started.

Total System Global Area 734003200 bytes

Fixed Size 2928728 bytes

Variable Size 524292008 bytes

Database Buffers 201326592 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

SQL>

View the available PDBs within the CDB with SQL statement: select name, open_mode from v$pdbs.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 MOUNTED

PDB2 MOUNTED

SQL>

NOTE: MOUNTED is the closed state for a PDB while it CDB is open.

You have four options for PDBs to choose from:
1. MOUNTED – PDB closed but CDB open.
2. OPEN MIGRATE – PDB is open for upgrade and patching.
3. OPEN READ ONLY – PDB is open for reads but not writes.
4. OPEN READ WRITE – PDB is open for all operations.

All of this modes can be moved to and from with the SQL statement: ‘ALTER PLUGGABLE DATABASE all <OPTION>; In this step we will move all the PDBs from mounted to open read write. NOTE: read write is optional.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ WRITE

PDB2 READ WRITE

SQL>

You can close all the PDB with close option.

SQL> alter pluggable database all close;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 MOUNTED

PDB2 MOUNTED

SQL>

You can actually specify the read write option and it will be accepted.

SQL> alter pluggable database all open read write;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 READ WRITE

PDB2 READ WRITE

SQL>

You cannot move from read write to simply read option with all PDB and you cannot move all PDB into read only mode. You can only open all PDBs in read write mode and close all PDBs with all option.

SQL> alter pluggable database all open read;

alter pluggable database all open read

ERROR at line 1:

ORA-00922: missing or invalid option

SQL> alter pluggable database all close;

Pluggable database altered.

SQL> alter pluggable database all open read;

alter pluggable database all open read

ERROR at line 1:

ORA-00922: missing or invalid option

SQL>

There are no other options to move all PDBs open mode. This completes startup and shutdown of all PDB within its CDB.

Larry Catt

OCP

Catt Solutions

Category Archives: 12c

Configure tnsnames.ora file for PDB

Oracle 12c – Enabling Unified Audit Data Trail

Oracle 12c – Manage VLDB

Startup CDB

Oracle 12c – Enabling Flashback Database

Configure tnsnames.ora file for CDB

Oracle 12c – Apply and review patches

Oracle 12c – Enable and configure Unified Audit Data Trail

Oracle 12c – Adaptive Execution Plan

Startup all PDB in a CDB

Oracle tips and tricks.