Monthly Archives: July 2016

12c, CDB, Multitenant, PDB

Oracle 12c – Use Resource Manager for a CDB and PDB

Leave a comment

Resource manager determines how resources are shared amongst multiple competing tasks within oracle.   Resource Management in a CDB environment has two components:  CDB level – Manage resources amongst multiple PDBs and has the ability to allocate/limit resources to specific PDB.  PDB level – Manages resources within a single PDB.   Through using the package DBMS_RESOURCE MANGER, the administrator is able to create Plans, Groups, and Allocations of resources.

Resource Allocation:

  1. Create Resource Plans.
  2. Create Consumer Groups.
  3. Assign PDBs to Groups.
  4. Each PDB is allocated a portion of the total system resources in the CDB.
  5. Inside a PDB resources are allocated to individual sessions in the PDB.
  6. Specify which PDBs shares of resources based on their importance.
  7. Limit CPU usage of individual PDB.
  8. Limit number of parallel servers a PDB can use.
  9. Limit resource usage of different sessions in a single PDB.
  10. Monitor resource usage of PDBs.

 

Larry Catt

OCP

12c, audit

Oracle 12c – Create and enable audit policies

Leave a comment

Oracle 12c has implemented the unified auditing which is based off of system and user level policies.   This article describes the use of audit policies.

 

 

Audit Policy General Aspects.

  1. A unified audit policy is a named group of audit setting that track a particular aspect of user behavior in DB.
  2. The CREATE AUDIT POLICY is used to create unified audit policy.
  3. Multiple audit policy can be active at any given time.
  4. AUDIT and NOAUDIT statements enable and disable.
  5. The AUDIT statement can include or exclude specific users.
  6. AUDIT and NOAUDIT can enable audit application context values.
  7. Activities that can be audited:
    1. USER ACCOUNTS, ROLES, and PRIVILEGEs
    2. OBJECT ACTION: drop table; running a procedure.
    3. APPLICATION CONTEXT VALUES.

 

  1. You should group auditing into related policies and attempt to minimize the number of policies defined.
  2. Many policies increase overhead in the UGA.
  3. The CREATE AUDIT POLICY statement creates a first class object in the SYS schema not in the schema of the creator.
  4. Once policy is created, it must be enabled with the AUDIT statement with POLICY clause.
  5. CREATE AUDIT POLICY and AUDIT statement do not affect current sessions, user must disconnect and reconnect for auditing to start.
  6. CLAUSES of the AUDIT statement.

BY — Used to apply the unified audit policy to one or more users.

AUDIT POLICY change_table_pol BY ocpuser;

 

EXCEPT — Used to exclude users from the unified audit policy.

AUDIT POLICY change_table_pol EXCEPT jtkirk, jlpicard;

 

WHENEVER SUCCESSFUL — Records only successful executions of the audited activity.

AUDIT change_table_pol WHENEVER SUCCESSFUL;

 

WHENEVER NOT SUCCESSFUL — Records only failed executions of the audited activity.

AUDIT change_table_pol WHENEVER NOT SUCCESSFUL;

 

Note the following:

WHENEVER — If the WHENEVER clause is omitted, then both failed and successful user activities are written to the audit trail.

 

BY/ EXCEPT — A unified audit policy can be enabled with either the BY clause or the EXCEPT clause, but not both simultaneously.

 

AUDIT… BY — If multiple AUDIT statements are executed on the same unified audit policy with different BY users, all of the specified users are audited.

 

AUDIT… EXCEPT — If multiple AUDIT statements are executed on the same unified audit policy with different EXCEPT users, only the last exception user list is used.

 

COMMON policies — Common unified audit policies can only be enabled from the root and only for common users.

 

LOCAL policies — Local audit policies can only be enabled from the PDB to which it applies.

 

  1. No audit policy is necessary to record RMAN activities. RMAN activities are recorded automatically.

RMAN_SESSION_RECID — Recovery Manager session identifier. Together with the RMAN_SESSION_STAMP column, this column uniquely identifies the Recovery Manager job. RMAN_SESSION_STAMP — Timestamp for the session.

RMAN_OPERATION — The Recovery Manager operation executed by the job.

RMAN_OBJECT_TYPE — Type of objects involved in a Recovery Manager session.

RMAN_DEVICE_TYPE — Device associated with a Recovery Manager session.

 

 

 

Larry Catt

OCP

12c, 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, ADDM

Oracle 12c – Generate ADDM

Leave a comment

In Oracle 12c, one needs to generate ADDM reports for comparison of Normal database operations and Periods performance is thought to be sub-par.   This article covers the generation of ADDM for normal operation.   There are three methods to generate ADDM reports:  EM Cloud control, ADDMRPT.SQL script, and DBMS_ADVISOR package.

 

EM Cloud Control:

  1. Logon to EM.
  2. Click the Advisor Central Link.
  3. Select ADDM link.
  4. From here you can start/stop snapshots; create ADDM tasks and display results.
  5. You can view ADDM analysis from the Performance Analysis section of the Home page.

 

ADDMRPT.SQL

  1. This script is located under ORACLE_HOME/rdbms/admin directory and can be executed at any time.
  2. Logon to SQLPLUS with sysdba privileges.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 29 09:04:21 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. Execute the script and select snapshots to generate reports.

 

SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

 

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance

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

 1457738340 ORCL                1 orcl

 

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host

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

* 1457738340        1 ORCL         orcl         linux2.local

                                                domain

Using 1457738340 for database Id

Using          1 for instance number

 

 

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

 

Listing the last 3 days of Completed Snapshots

 

                                                        Snap

Instance     DB Name        Snap Id    Snap Started    Level

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

orcl         ORCL               388 27 Jun 2016 00:00      1

                                389 27 Jun 2016 01:00      1

                                390 27 Jun 2016 02:00      1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap:

 

 

  1. Enter a starting snapshot id:

 

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 389

Begin Snapshot Id specified: 389

 

Enter value for end_snap:

 

 

  1. Enter a Ending snapshot id:

 

Enter value for end_snap: 390

End   Snapshot Id specified: 390

 

 

  1. Enter a report name or use default with enter.

 

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is addmrpt_1_389_390.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

 

Enter value for report_name:

 

  1. The report will be generated and placed in current directory.

 

End of Report

Report written to addmrpt_1_389_390.txt

SQL>

 

 

DBMS_ADVISOR package

 

  1. Logon to SQLPLUS with sysdba privileges.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 29 09:04:21 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 a ADDM task.

 

Begin

  DBMS_ADVISOR.create_task (

    advisor_name      => ‘ADDM’,

    task_name         => ‘389_390_AWR_SNAPSHOT’,

    task_desc         => ‘Advisor for snapshots 389 to 390.’);

End;

/

 

  1. Set start and end parameters for task.

 

Begin

  — Set the start and end snapshots.

  DBMS_ADVISOR.set_task_parameter (

    task_name => ‘389_390_AWR_SNAPSHOT’,

    parameter => ‘START_SNAPSHOT’,

    value     => 389);

 

  DBMS_ADVISOR.set_task_parameter (

    task_name => ‘389_390_AWR_SNAPSHOT’,

    parameter => ‘END_SNAPSHOT’,

    value     => 390);

End;

/

 

  1. Execute task

 

Begin

  — Execute the task.

  DBMS_ADVISOR.execute_task(task_name => ‘389_390_AWR_SNAPSHOT’);

End;

/

 

  1. View report.

 

— Display the report.

SET LONG 1000000 LONGCHUNKSIZE 1000000

SET LINESIZE 1000 PAGESIZE 0

SET TRIM ON TRIMSPOOL ON

SET ECHO OFF FEEDBACK OFF

 

SELECT DBMS_ADVISOR.get_task_report(‘389_390_AWR_SNAPSHOT’) AS report

FROM   dual;

 

  1. This completes generation for ADDM reports.

 

 

 

 

 

Larry Catt

OCP

12c, data pump

Oracle 12c – Monitor a Data Pump Job

Leave a comment

You can use expdp and impdp utilities to connect to a currently running job and displays the real time processes.  In interactive command mode you can request job status.   You can also see status and information through Data Pump views: DBA_DATAPUMP_JOBS which shows all active DP jobs and master tables associated with active jobs and DATA_DATAPUMP_SESSIONS which IDs user sessions attached to DP jobs, this is helpful in determining why DP job is stopped.    Additionally, V$SESSION_LONGOPS shows DP jobs transferring data and maintains an entry to give current progress.  V$SESSION _LONGOPS will gives estimated transfer size and size transferred.

 

The following commands are applicable when using impdp or expdp in Interactive mode:

ADD_FILE – Add additional dump files.

CONTINUE_CLIENT – Exit interactive mode and enter logging mode.

EXIT_CLIENT – Stop the import or export client session, but leave the job running.

KILL_JOB – Detach all currently attached client sessions and terminate the current job.

PARALLEL – Increase or decrease the number of active worker processes for the current job.

START_JOB – Restart a stopped job to which you are attached.

STATUS – Display detailed status for the current job and/ or set status interval.

STOP_JOB – Stop the current job for later restart.

 

This article will give an example of using expdp utility in interactive mode to attach to a current running job to view progress.

 

 

  1. Create two Logons to your server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Jan 11 08:17:10 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$

 

  1. Logon to your database as the user sysdba and create the directory dmpdir.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 14:20:29 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> create directory dmpdir as ‘/home/oracle’;

Directory created.

SQL>

 

 

  1. Log out of SQLPLUS and change directory to oracle software owners home directory.
 
   

SQL> exit

Disconnected from 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

[oracle@linux2 ~]$ cd

[oracle@linux2 ~]$

 

  1. Execute the command:

 

expdp \”/ as sysdba\”  dumpfile=orcl.dmp directory=dmpdir full=y job_name=orcl_full

 

[oracle@linux2 ~]$ expdp \”/ as sysdba\”  dumpfile=orcl.dmp directory=dmpdir full=y job_name=orcl_full

Export: Release 12.1.0.2.0 – Production on Wed Jan 11 14:34:49 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  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

FLASHBACK automatically enabled to preserve database integrity.

Starting “SYS”.”ORCL_FULL”:  “/******** AS SYSDBA” dumpfile=orcl.dmp directory=dmpdir full=y job_name=orcl_full

Estimate in progress using BLOCKS method…

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

 

  1. Switch to your secondary screen

 

expdp \”/ as sysdba\”   attach=orcl_full status=1

 

[oracle@linux2 ~]$ expdp \”/ as sysdba\”   attach=orcl_full status=1

Export: Release 12.1.0.2.0 – Production on Wed Jan 11 14:35:18 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  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

 

Job: ORCL_FULL

  Owner: SYS

  Operation: EXPORT

  Creator Privs: TRUE

  GUID: 45D7B1AF11C77B0FE053620F1E0A3C9A

  Start Time: Wednesday, 11 January, 2016 14:34:52

  Mode: FULL

  Instance: orcl

  Max Parallelism: 1

  Timezone: +00:00

  Timezone version: 18

  Endianness: LITTLE

  NLS character set: WE8MSWIN1252

  NLS NCHAR character set: AL16UTF16

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        “/******** AS SYSDBA” dumpfile=orcl.dmp directory=dmpdir full=y job_name=orcl_full

  State: EXECUTING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: /home/oracle/orcl.dmp

    bytes written: 4,096

 

Worker 1 Status:

  Instance ID: 1

  Instance name: orcl

  Host name: linux2.localdomain

  Process Name: DW00

  State: EXECUTING

  Object Schema: SYS

  Object Type: DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT

  Completed Objects: 3

  Worker Parallelism: 1

 

Export>

 

  1. Once in interactive mode you can continue to ask for updates on job. with status execution.
  2. This completes monitoring of data pump job from EXPDP or IMPDP utility.

 

 

Larry Catt

OCP

12c, ADDM, ADR, ASH, Performance

Oracle 12c – Explain ADR Enhancements

Leave a comment

Oracle 12c Automatic Diagnostic Repository (ADR) provides files to store diagnostic information about the health of the database.   This release has added two new log files to add in administration of the RDBMS suite:  DDL Log File and Debug Log File.

 

NEW DDL Log file – The new DDL log file has the same format and behavior as the alert log.  When the ENABLE_DDL_LOGGING parameter is set to TRUE, DDL statements are written out to the DDL log file.  Two log files are maintained:  XML and plain text.  Log files are stored in the /log/ddl directory of the ADR home and are included in IPS incident packages.

 

Debug Log – the debug log records unusual incidents that do not impact normal operations.  They are occurrences which do not warrant an entry in alert log or incident report.  The debug log has the same format as alert log.

 

 

Larry Catt

OCP

12c, CDB

Connection to CDB

Leave a comment

 

In Oracle 12c multitenant databases CDBs are containers databases which hold oracle internals, supplied accounts and processes and client PDBs pluggable databases for applications.   Connections to CDBs can be from the OS layer by supplying the SID via environmental variables or directly and through oracle networking.   This procedure demonstrates the connection through both processes.

 

Connection via OS layer

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

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Jun 24 11:43:05 EDT 2016 on pts/1

[oracle@linux2 ~]$

 

  1. Verify that you have environment has the appropriate SID set with command echo $ORACLE_SID. If it is not the appropriate SID change which the export ORACLE_SID= statement.

 

[oracle@linux2 ~]$ echo $ORACLE_SID

cdb1

[oracle@linux2 ~]$ export ORACLE_SID=cdb1

[oracle@linux2 ~]$ echo $ORACLE_SID

cdb1

[oracle@linux2 ~]$

 

  1. Now you can connect directly into the CDB referenced by the environmental parameter ORACLE_SID with the command sqlplus / as sysdba. NOTE:  You are connecting with the administrative account

 

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 24 12:26:00 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 and Real Application Testing options

 

SQL>

 

  1. This completes connecting the CDB from OS layer

 

 

 

Connection via Oracle networking

 

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

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Jun 24 11:43:05 EDT 2016 on pts/1

[oracle@linux2 ~]$

 

  1. List the values in your local tnsnames.ora file to find the appropriate Alias for the CDB you which to connect to with the command: cat $ORACLE_HOME/network/admin/tnsnames.ora.

 

[oracle@linux2 ~]$ cat $ORACLE_HOME/network/admin/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 ~]$

 

  1. In this example, we want to connect to cdb1 referenced by CDB1. Supply the sqlplus connection string with the tag:  @CDB1 to complete your connection as shown below.

[oracle@linux2 ~]$ sqlplus sys/password@CDB1 as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 24 12:41:24 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 and Real Application Testing options

 

SQL>

  1. This completes connection to CDB via oracle networking.

 

Larry Catt

OCP