Category Archives: 12c

Oracle 12c – Generate ADDM

July 11, 2016 Larry Catt 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:

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

ADDMRPT.SQL

This script is located under ORACLE_HOME/rdbms/admin directory and can be executed at any time.
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

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>

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:

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:

Enter a Ending snapshot id:

Enter value for end_snap: 390

End Snapshot Id specified: 390

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:

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

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

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 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;

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;

Execute task

Begin

— Execute the task.

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

End;

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;

This completes generation for ADDM reports.

Larry Catt

OCP

12c, data pump

Oracle 12c – Monitor a Data Pump Job

July 11, 2016 Larry Catt 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.

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 >

[oracle@linux2 ~]$

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

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>

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

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

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

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

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>

Once in interactive mode you can continue to ask for updates on job. with status execution.
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

July 8, 2016 Larry Catt 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

July 6, 2016 Larry Catt 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

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

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

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

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>

This completes connecting the CDB from OS layer

Connection via Oracle networking

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

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

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

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>

This completes connection to CDB via oracle networking.

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Startup Single PDB

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

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

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

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

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

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>

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>

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>

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

June 24, 2016 Larry Catt 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:

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

Seed column usage – workload history via the DBMS_STATS.SEED_COL_USAGE procedure.
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.
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

June 21, 2016 Larry Catt 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.

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

Feature of DMU:

Selective Conversion – DMU has the ability to only convert data needed at table and column levels.
Monitoring – GUI used to track progress.
Inline Conversion – Support inline conversion of database content.
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:

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

Larry Catt

OCP

12c, audit

Oracle 12c – Migrate to unified auditing

June 11, 2016 Larry Catt 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.

Logon as SYSDBA
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’;

Shutdown the database
Stop the listener.
Change directory to $ORACLE_HOME/rdbms/lib
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

Restart listener.
Restart the database.

Larry Catt

OCP

12c, flashback

Oracle 12c – Flashback Table

June 11, 2016 Larry Catt 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.

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

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

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>

Select the values from test table fb_test.

SQL> select * from fb_test;

FNAME AMT

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

Larry 111

SQL>

Drop the test table fb_test.

SQL> drop table fb_test;

Table dropped.

SQL>

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>

Flashback the dropped table.

SQL> flashback table fb_test to before drop;

Flashback complete.

SQL>

Perform select operation on table fb_test.

SQL> select * from fb_test;

FNAME AMT

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

Larry 111

SQL>

This completes flashback to table.

Larry Catt

OCP

12c, ADDM, Performance

Oracle 12c – Analyze and identify performance issues

June 10, 2016 Larry Catt 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:

Locates the root cause of performance issue.
Provides recommendation to correct.
Quantifies the expected benefits.
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:

Hardware changes – add CPU, memory, change I/O subsystem.
Database configuration – change init parameters.
Schema changes – could include hash partitioning table or index or using ASSM automatic segment space management.
Application changes – cache options or use of bind variables.
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

Catt Solutions

Category Archives: 12c

Oracle 12c – Generate ADDM

Oracle 12c – Monitor a Data Pump Job

Oracle 12c – Explain ADR Enhancements

Connection to CDB

Startup Single PDB

Oracle 12c – Enhanced statistics gathering features

Oracle 12c – Use Oracle Database Migration Assistant for Unicode

Oracle 12c – Migrate to unified auditing

Oracle 12c – Flashback Table

Oracle 12c – Analyze and identify performance issues

Oracle tips and tricks.