Monthly Archives: June 2016

12c, CDB, Multitenant, PDB

Startup Single PDB

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 of a single PDB.

 

  1. Logon to your server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

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

[oracle@linux2 ~]$

 

  1. 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 Jun 25 09:41:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

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>

 

 

  1. 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

 

SQL>

 

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

 

  1. 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 <PDB_NAME> <OPTION>;    In this step we will move PDB1 from mounted to open read write.   NOTE: read write is optional.

 

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

 

SQL> select name, open_mode from v$pdbs;

 

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

 

SQL>

 

  1. You can close the PDB with close option.

 

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

 

SQL> select name, open_mode from v$pdbs;

 

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           MOUNTED

 

SQL>

 

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

 

SQL> alter pluggable database pdb1 open read write;

Pluggable database altered.

 

SQL>  select name, open_mode from v$pdbs;

 

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

 

SQL>

 

 

  1. You cannot move from read write to simply read option, you have to shut down the PDB first.

 

SQL> alter pluggable database pdb1 open read only;

alter pluggable database pdb1 open read only

*

ERROR at line 1:

ORA-65019: pluggable database PDB1 already open

 

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

 

SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

 

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ ONLY

 

SQL>

 

  1. The other options are self-evident. This completes startup of single PDB within its CDB

Larry Catt

OCP

12c, Performance

Oracle 12c – Enhanced statistics gathering features

Leave a comment

Oracle 12c has several enhanced features for statistics gathering which add in optimization of PL/SQL execution.    This article describes these enhancements.

 

 

The Optimizer uses statistics to create the best execution plans.  Re-optimization uses the execution of previous statements results to determine if the executed plan was optimal and adjusts plans for future execution in an iterative process.

Three types of re-optimization:

  1. Statistics Feedback – also known as cardinality feedback, it compares cardinality of estimated and actual executions. Storing the correct cardinality statistics and creates SQL plane directive for future use.
  2. Performance Feedback – Used to improve degree of parallelism for future SQL statements based on previous executions when Automatic Degree of Parallelism (AutoDOP) is enabled in adaptive mode.
  3. SQL plan directives – Is additional information used by optimizer to determine correct execution plans and are created on query expressions not SQL statements thus can be applied to multiple SQL statements. Multiple directives can be used for single SQL statement.  SQL directives are purged if not used in 53 days.  You can see directives in views:   DBA_SQL_PLAN_DIRECTIVES and DBA_SQL_PLAN_DIR_OBJECTS.

 

 

 

Partition table statistics – Two sets of statistics are gathered at the partition level and table level, in 12c the table level statistics are aggregated by partition level avoiding full table scan.

 

INCREMENTAL_STALENESS – Preference setting in DBMS_STATS.SET_TABLE_PREFS allows you to determine when statistics on tables are stale by percentage of change with  USE_STALE_PERCENT or locking statistics forcing their use regardless of how much change has occurred with USE_LOCKED_STATS.

 

Incremental statistics improved by EXCHANGE_PARTITION command allows you to exchange statistics from non-partitioned table with those in a partition.  Prior to 12c you had to gather data on all partitions before using incremental global statistics.   Use the DBMS_STATS package preference of INCREMENTAL_LEVEL set to TABLE (the default is PARTITION) to use this EXCHANGE_PARTITION feature.

 

Concurrent Statistics – when global statistics gathering preference CONCURRENT is set, the Job Scheduler and Advance queuing gather states on table/partitions concurrently.  12c has optimized automatic statistics gathering by batching smaller jobs to run together.  When preference CONCURRENT is set to ALL or AUTOMATIC stats a gathered nightly.

 

 

Automatic column group detection – Gathering of statistics on columns used in filter predicates by estimating cardinality.   Determining columns to gather increased stats is determined by workload history.  Three step process determines Auto Column Group detection:

  1. Seed column usage – workload history via the DBMS_STATS.SEED_COL_USAGE procedure.
  2. Create the column groups – Use the DBMS_STATS.CREATE_EXTENDED_STATS function to id columns groups based on usage information gathered. Once identified extended statistics will be automatically generated.
  3. Regather statistics – Regather statistics for all tables identified for column stats extended.

 

 

Larry Catt

OCP

12c, Administration

Oracle 12c – Use Oracle Database Migration Assistant for Unicode

Leave a comment

Oracle 12c now comes with the Database Migration Assistant for Unicode (DMU) for migration of Unicode character sets.  This replaces DB Character Set Scanner (CSSCAN) and CASALTER utility which are both de-supported.  DMU guides DBA through entire migration and contains a verification mode for existing Unicode databases.  Validation method identifies problems with Unicode existing and gives solutions.

 

Database must meet the following to support DMU.

  1. DB version must be 10.2.0.4, 10.2.0.5, 11.1.0.7, 11.2.0.1 or later.
  2. Character set must be ASCII based.
  3. DBMS_DUMA_INTERNAL must be installed.
  4. Oracle Database Vault must be disabled to migrate.
  5. Cannot be 12c PDB.
  6. DB must be in read/write mode.

 

Feature of DMU:

  1. Selective Conversion – DMU has the ability to only convert data needed at table and column levels.
  2. Monitoring – GUI used to track progress.
  3. Inline Conversion – Support inline conversion of database content.
  4. Scheduling – Removal of old data can be scheduled for future maintenance period.

 

Before conversion process, DMU preforms an analysis of varchar2, char, long, and CLOB to determine if anything will corrupt the data during Unicode conversion, it checks for the following:

  1. If results are changed from original value.
  2. Conversion fits in length limit of column.
  3. Conversion results fit in data type of column.
  4. Each character if valid in the source character code.

 

Larry Catt

OCP

 

12c, audit

Oracle 12c – Migrate to unified auditing

Leave a comment

When upgrading to 12c the unified auditing is not enabled.  The audit processes of the upgraded database will be used.   Now newly installed databases will use the mixed use unified auditing by default.  In upgrade, you must migrate the database to unified auditing to disable traditional auditing.   The following procedure does this.

 

  1. Logon as SYSDBA
  2. Determine if database is already in unified auditing with the following statement. TRUE indicates no migration is necessary.

select VALUE from V$OPTION where PARAMETER = ‘Unified Auditing’;

  1. Shutdown the database
  2. Stop the listener.
  3. Change directory to $ORACLE_HOME/rdbms/lib
  4. Enable unified auditing executable with unix command:

make –f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORALE_HOME

In Windows:

Rename file %ORACLE_HJOME%/bin/oauniaud12dll.dbl to %ORACLE_HJOME%/bin/oauniaud12dll

  1. Restart listener.
  2. Restart the database.

 

Larry Catt

OCP

12c, flashback

Oracle 12c – Flashback Table

Leave a comment

By default when a table is dropped oracle does not immediately remove the space, but renames it and places the table and associated objects in recycle bin which allows it to be recovered at a later time.  The recycle bin is a data dictionary table that contains info needed to recover dropped tables.  Original table stays in place and occupies same space until purged or database needs space for storage.  Tablespaces are not placed in recycle bin and once dropped are gone.   All tables in that tablespace are removed from recycle bin if they have previously been dropped.   Recycle bin can be disabled with recyclebin initialization parameter.   To recover a dropped table from the recyclebin the command ‘FLASHBACK TABLE ….  TO BEFORE DROP; ‘is used.  You must use the system generated recyclebin name.  You can rename the table with RENAME clause during recovery.  The USER_RECYCLEBIN view can be used to find the system generated recyclebin name.  This article demonstrates the use of flashback table.

 

  1. Logon to SQLPLUS as the user scott.

 

[oracle@linux2 ~]$ scott/password

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 6 08:25:35 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

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>

 

 

  1. Create table fb_test.

 

create table fb_test(fname varchar2(30), amt number);

SQL> create table fb_test(fname varchar2(30), amt number);

Table created.

SQL>

 

 

  1. Perform insert into test table fb_test and commit;

 

insert into scott.fb_test(fname,amt) values(‘Larry’,111);

commit;

SQL> insert into scott.fb_test(fname,amt) values(‘Larry’,111);

commit;

1 row created.

SQL>

Commit complete.

SQL>

 

 

  1. Select the values from test table fb_test.

 

SQL> select * from fb_test;

FNAME                                 AMT

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

Larry                                 111

SQL>

 

  1. Drop the test table fb_test.

 

SQL> drop table fb_test;

Table dropped.

SQL>

 

 

  1. Attempt a select from table fb_test and see the table does not exist.

 

SQL>  select * from fb_test;

 select * from fb_test

               *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

 

 

 

  1. Flashback the dropped table.

 

SQL> flashback table fb_test to before drop;

Flashback complete.

SQL>

 

  1. Perform select operation on table fb_test.

 

SQL> select * from fb_test;

FNAME                                 AMT

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

Larry                                 111

SQL>

 

  1. This completes flashback to table.

 

 

Larry Catt

OCP

12c, ADDM, Performance

Oracle 12c – Analyze and identify performance issues

Leave a comment

The Oracle Automatic Database Diagnostic Monitor (ADDM) is a utility which automatically detects and reports performance issues.  ADDM is based on snapshots take by AWR (Automatic Workload Repository). This data is analyzed and displayed as ADDM findings on Database Home Page of EM.   The DBMS_ADDM package can execute ADDM reports outside of EM but the user executing DBMS_ADDM must have ADVISOR privilege.  ADDM allows administrators to quickly identify performance problems which may not be readily noticeable.  Every ADDM finding will have one or more recommendations to reduce the impact.

 

AWR takes snapshots are taken once an hour and stored for 8 days by default.  It is recommended that snapshots are stored for 30 days.  A new snapshot will result in an ADDM analysis being performed with the following steps:

 

  1. Locates the root cause of performance issue.
  2. Provides recommendation to correct.
  3. Quantifies the expected benefits.
  4. Identifies areas where no action is needed.

 

ADDM analysis is done top down through DB time statistics this is an iterative process and once one problem is solved another will become the bottleneck.  ADDM recommendation may include:

 

  1. Hardware changes – add CPU, memory, change I/O subsystem.
  2. Database configuration – change init parameters.
  3. Schema changes – could include hash partitioning table or index or using ASSM automatic segment space management.
  4. Application changes – cache options or use of bind variables.
  5. Using other advisors – Running SQL Tuning Advisor or Segment Advisor.

 

ADDM is enabled in database by default and controlled by init parameters CONTROL_MANAGEMENT_PACK_ACCESS.    ADDM is enabled when CONTROL_MANAGEMENT_PACK_ACCESS parameter is set to DIAGNOSTIC+TUNING (default) or DIAGNOSTIC.    ADDM is disabled when CONTROL_MANAGEMENT_PACK_ACCESS is setting to NONE.

Additionally the initialization parameter of STATISTICS_LEVEL must be set to TYPICAL (default) or ALL to enable ADDM, but if it is set to BASIC ADDM will be disable.

 

The parameter DBIO_EXPECTED of the DBMS_ADVISOR package is required to perform analysis of I/O performance. It is set to 7000 microseconds with the following package call:

 

EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(‘ ADDM’, ‘DBIO_EXPECTED’, 7000);

 

An ADDM analysis results come in one of three classes:

  • PROBLEM – root cause of database performance issues.
  • SYMPTOM – finding that’s info leads to one or more problem findings.
  • INFORMATION – Findings that are used to report areas of system that do not have performance issues.

 

If one problem has multiple findings, ADDM may report them separately.   Problem will be shown largest impact first.

 

Larry Catt

OCP

12c, network, PDB, tnsnames

Configure tnsnames.ora file for PDB

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.

 

  1. 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 ~]$

 

  1. 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]

 

 

  1. 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]$

  1. 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

 

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

 

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]$

 

  1. 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

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.

 

  1. Logon to your oracle server as the oracle software owner and logon to sqlplus with sysdba privileges.
  2. 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>

 

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

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

 

  1. 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

 

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

 

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

The command completed successfully

[oracle@linux2 ~]$

 

  1. 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

 

 

  1. 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

 

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

 

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]$

 

  1. 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

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

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>

 

 

 

  1. 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>

 

 

  1. This completes the enablement of Unified Audit Data in 12c

 

Larry Catt

OCP