Oracle 12c – Monitor performance

September 23, 2016 Larry Catt Leave a comment

There are several methods to monitor database performance, but EM is probably the most frequent used today. Oracle Enterprise Manager Cloud Control is designed to manage the entire Oracle environment: database, server, middleware, and more. OEM Cloud control requires additional licensing, however EM Express is provide free with Enterprise editions.

EM Express can be used to manage a single Oracle 12c database and it is actually built into the database. EM Express is a light weight management console and provides basic administrative tasks with minimal impact to database operations. EM Express has no background processes and it gathers information from already existing database stores. The Performance Hub of Express provides the following abilities:

ASH Analytics.
SQL Monitor
ADDM
Workload metrics.
Resource usage
See real-time and historic data.
In historic mode AWR (Automatic Workload Repository) data is used.
Performance Hub tabs are
1. Summary – real-time mode
2. Activity – ASH analytics both real-time and historic.
3. Workload – info about Top SQL
4. RAC – RAC specific metrics.
5. Monitor SQL – current and historic SQL executions.
6. ADDM – ADDM reports both real-time and historic
7. Current ADDM findings – performance analysis of last 5 minutes.

Larry Catt

OCP

12c, Administration, ILM

Use ILM feature: Heat Maps

September 22, 2016 Larry Catt Leave a comment

Oracle Information Lifecycle Management (ILM) is the processes by which we can manage data from creation to deletion. It allows us a method to increase speed of access and to acquire metadata about use. The two features which support ILM in Oracle 12c are Heat Maps and Automatic Data Optimization. This procedure will cover the use of Heat Maps within a 12c RDBMS. Heat Maps track use information about data at the row and segment level. NOTE: Heat Maps are not supported in CDB multitenant databases.

Logon to your Oracle server as the Oracle software owner and logon to SQLPLUS

[root@linux2 oracle]# su – oracle

Last login: Wed Sep 7 14:47:35 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 7 14:50:11 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>

Check to see if Heat Maps is enabled for your database and if not turn it on with the command: “alter system set heat_map=on;”

SQL> show parameter heat_map

NAME TYPE VALUE

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

heat_map string OFF

SQL> alter system set heat_map=on;

System altered.

SQL> show parameter heat_map

NAME TYPE VALUE

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

heat_map string ON

SQL>

Create user test with default tablespace of users.

create user test identified by test

default tablespace users quota unlimited on users;

grant dba to test;

Create table test_hm with column col_a and col_b.

create table test.test_hm(col_a number,

col_b varchar2(10))

partition by range (col_a)

(partition less_then_50 values less than (‘50000000’)

tablespace users);

Insert 20 million records to test_hm with the following pl/sql block.

Declare

val_a number:=1;

val_b varchar2(10):=’a’;

begin

while val_a<20000001

loop

insert into test.test_hm(col_a, col_b)

values(val_a, val_b);

val_a:=val_a+1;

if val_b=’z’

then

val_b:=’a’;

else

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

end if;

end loop;

commit;

end;

Check records for current heat maps in the view

column NAME format a10

column SUB_NAME format a15

column WRITE format a14

column READ format a14

select object_name Name,

SUBOBJECT_NAME SUB_NAME,

to_char(segment_write_time,’DD-MON HH:MI’) write,

to_char(segment_read_time,’DD-MON HH:MI’) read

from dba_heat_map_segment where object_name=’TEST_HM’;

SQL> SQL> column NAME format a10

SQL> column SUB_NAME format a15

SQL> column WRITE format a14

SQL> column READ format a14

SQL> select object_name Name,

2 SUBOBJECT_NAME SUB_NAME,

3 to_char(segment_write_time,’DD-MON HH:MI’) write,

4 to_char(segment_read_time,’DD-MON HH:MI’) read

5 from dba_heat_map_segment where object_name=’TEST_HM’;

NAME SUB_NAME WRITE READ

———- ————— ————– ————–

TEST_HM LESS_THEN_50 13-SEP 01:54

SQL>

Create sub_partitions for table test_hm for col_a values ( <5 million, < 10 million, < 15 million, and < 20 million)

ALTER TABLE test.test_hm split PARTITION less_then_50 at (5000000) into

(PARTITION less_than_5, PARTITION greater_than_5);

ALTER TABLE test.test_hm split PARTITION greater_than_5 at (10000000) into

(PARTITION less_than_10, PARTITION greater_than_10);

ALTER TABLE test.test_hm split PARTITION greater_than_10 at (15000000) into

(PARTITION less_than_15, PARTITION greater_than_15);

ALTER TABLE test.test_hm split PARTITION greater_than_15 at (20000000) into

(PARTITION less_than_20, PARTITION greater_than_20);

No perform a select on data where a sort operation would be require, we used the below statement.

select * from test.test_hm where col_a between 12000000 and 19000000 order by col_b;

Now execute the statement from step 6 to see heat map information about the scans which are occurring in our table.

SQL> column NAME format a10

SQL> column SUB_NAME format a15

SQL> column WRITE format a14

SQL> column READ format a14

SQL> select object_name Name,

2 SUBOBJECT_NAME SUB_NAME,

to_char(segment_write_time,’DD-MON HH:MI’) write,

to_char(segment_read_time,’DD-MON HH:MI’) read

3 4 5 from dba_heat_map_segment where object_name=’TEST_HM’;

NAME SUB_NAME WRITE READ

———- ————— ————– ————–

TEST_HM LESS_THAN_20

TEST_HM LESS_THAN_15

TEST_HM LESS_THAN_5 13-SEP 02:02

SQL>

This completes our coverage of Heat Maps in Oracle 12c

Larry Catt

OCP

12c, audit

Oracle 12c – Configure and manage auditing

September 21, 2016 Larry Catt Leave a comment

Auditing monitors and records selected actions by database users and processes. It can be based on specific SQL, object, system or object privilege and monitor both successful and/or failed attempts. Auditing must be enabled to perform auditing. Audit records are either stored in data dictionary or OS file. Auditing provides internal controls and typical implementations are:

Enable accountability for actions.
Deter user or intruders form inappropriate actions.
Investigate suspicious activity.
Notify an auditor of unauthorized actions.
Monitor and gather data about specific database activities.
Detect problems with an authorization or access control.
Address auditing requirements for regulatory compliance.

The system initialization parameter AUDIT_TRAIL controls database auditing process and has the following options.

DB – audit trail is sent to data dictionary. All mandatory and SYS audit trails are always sent to OS audit trail. DB is the default setting form AUDIT_TRAIL.
DB, EXTENDED – Same as DB, but gives SQL, Bind variables, and CLOB columns to SYS.AUD$
OS – Puts all audit trails on OS directory defined in AUDIT_FILE_DEST init parameter.
XML – Puts all audit trails in XML file on OS directory defined in XML_AUDIT_TRAIL value. Does not affect syslog.audit file, which will always be in text.
XML, EXTENDED – same as XML but includes SQL, Bind variables, etc.
NONE – Disables auditing.

The SYS.AUD$ table holds standard audit records within the database and the SYS.FGA_LOG$ table holds records for fine grain auditing. Audit records can only be deleted by administrator. IF parameter 07_DIRCTIONARY_ACCESSIBILITY is set to FALSE the default value, only SYSDBA can perform DML on SYS.AUD$ or SYS.FGA_LOG$. Oracle Database Vault and Oracle Label Security can add additional protection to audit trail but requires additional licensing.

Some actions by SYSDBA and SYSOPER are always audited and place in OS audit trail, this are referred to as mandatory auditing and happens even if audit trail is disabled. These audit records are located in $ORACLE_BASE/admin/$ORACLE_SID/adump directory by default. Mandatory actions that are audited:

Database Startup
SYSDBA and SYSOPER logins
Database shutdown.

FGA – Fine-grained auditing extends the capabilities of standard auditing and allows for conditions to be set to trigger auditing to occur.

Larry Catt

OCP

12c, SQL

Oracle 12c – Use Row limit clause

September 11, 2016 Larry Catt Leave a comment

Oracle 12c now supports top-N SQL queries (those that return limited number of rows after sort operation). In previous releases this was performed by limiting return by the pseudo column ROWNUM before the ORDER BY clause but was not a true top-N query. This has been resolved with two new clauses: FETCH FIRST and OFFSET.

Examples:

FETCH FIRST – the example below use FETCH FIRST to return the 3 highest salaried persons from scott.emp.

Logon to SQLPLUS as SCOTT.

[oracle@linux2 ~]$ sqlplus scott/password

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 4 07:45:52 2016

Last Successful login time: Tue Jan 03 2016 12:44:03 -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>

Execute the query below:

select ename, sal from scott.emp order by sal desc

fetch first 3 rows only;

SQL> select ename, sal from scott.emp order by sal desc

fetch first 3 rows only;

ENAME SAL

———- ———-

KING 5000

SCOTT 3000

FORD 3000

SQL>

Use ASC clause to get the reverse order, as below

select ename, sal from scott.emp order by sal asc

fetch first 3 rows only;

SQL> select ename, sal from scott.emp order by sal asc

fetch first 3 rows only;

ENAME SAL

———- ———-

SMITH 800

JAMES 950

DOE 1000

SQL>

FETCH LAST clause does not exist must use ASC FETCH FIRST clause.

OFFSET – Skips a number of rows in the ordered list and with FETCH NEXT gives a range of rows.

Logon to SQLPLUS as SCOTT.

[oracle@linux2 ~]$ sqlplus scott/password

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 4 07:45:52 2016

Last Successful login time: Tue Jan 03 2016 12:44:03 -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>

Execute the query below:

select ename, sal from scott.emp order by sal desc offset 3 rows fetch next 3 rows only;

SQL> select ename, sal from scott.emp order by sal desc offset 3 rows fetch next 3 rows only;

ENAME SAL

———- ———-

JONES 2975

BLAKE 2850

CLARK 2450

SQL>

FETCH NEXT can also give a percentage say you want to know the top 10% of all salaries.

Logon to SQLPLUS as SCOTT.

[oracle@linux2 ~]$ sqlplus scott/password

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 4 07:45:52 2016

Last Successful login time: Tue Jan 03 2016 12:44:03 -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>

Execute the query below:

select ename, sal from scott.emp order by sal desc fetch next 10 percent rows only;

SQL> select ename, sal from scott.emp order by sal desc fetch next 10 percent rows only;

ENAME SAL

———- ———-

KING 5000

FORD 3000

SQL>

This completes using row limit clauses in Oracle 12c.

Larry Catt

OCP

12c, CDB, Multitenant, PDB, Users

Container Clause for Common Users in Oracle Multitenant Databases

September 8, 2016 Larry Catt Leave a comment

In Oracle 12c multitenant databases, a user defined in the CDB is considered a Common user. Common users with correct privileges can access all PDBs in the CDB. The restriction of which database a user can and cannot access in the multitenant database architecture is governed by the CONATINER clause. This article looks at the creation of Common users and the use of the container clause to allow access to multiple PDBs.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Jul 26 10:07:02 EDT 2016 on pts/2

[oracle@linux2 ~]$

Logon to oracle as the sys user.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 26 14:30:43 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>

Create the user c##cdb_dba. NOTE: users names in the root container must begin with c##… or C##…

SQL> create user c##cdb_dba identified by password;

User created.

Just as in a traditional Oracle database a user has no privileges until they are granted and what a user can do is limited by these privileges. But an additional clause exists in CDB and PDB which determines where a common user can use their privileges, this clause is CONTAINER.

In this first example, we will use the clause CONTAINER=CURRENT, meaning that the privilege is available in the root container only.

SQL> grant all privileges to c##cdb_dba container=CURRENT;

Grant succeeded.

Now logon to the user c##cdb_dba and attempt to create another user account and see that you can connect to the root containe.

SQL> connect c##cdb_dba/password

Connected.

SQL>

In this database we have two PDBs: PDB1 and PDB2, attempt to connect to both PDBs and not that the operation fails. But you can reconnect to root CDB CDB$ROOT.

SQL> alter session set container=PDB1;

ERROR:

ORA-01031: insufficient privileges

SQL> alter session set container=PDB2;

ERROR:

ORA-01031: insufficient privileges

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL>

Reconnect as the sys user and grant “all privileges” with a container = ALL. Meaning that you can connect to both root, pdb1 and pdb2. At the root level you can specify ALL and CURRENT for the clause CONTAINER. Now you can connect to all containers in PDB

SQL> grant all privileges to c##cdb_dba container=all;

Grant succeeded.

SQL> connect c##cdb_dba/password

Connected.

SQL> alter session set container=pdb1;

Session altered.

SQL> alter session set container=pdb2;

Session altered.

SQL> alter session set container=cdb$root;

Session altered.

SQL>

Larry Catt

OCP

12c, backup/recovery, data pump

Oracle 12c – Import data pump

September 6, 2016 Larry Catt Leave a comment

IMPDP utility is used to read in the contents of an EXPDP dump file. It can be run in three modes:

Command-line – Enables you to specify most of the export parameters
Parameter File – Allows you to specify command line parameters in a PARFILE
Interactive-Command – Displays an import prompt and you enter various commands.

IMPDP utility can accept the following parameters during execution:

ABORT_STEP – Stop the job after it is initialized or at the indicated object.Valid values are -1 or N where N is zero or greater.N corresponds to the object’s process order number in the master table.
ACCESS_METHOD – Instructs Import to use a particular method to load data.Valid keyword values are: [AUTOMATIC], CONVENTIONAL, DIRECT_PATHand EXTERNAL_TABLE.
ATTACH – Attach to an existing job. For example, ATTACH=job_name.
CLUSTER – Utilize cluster resources and distribute workers across the Oracle RAC [YES].
CONTENT – Specifies data to load. Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.
DATA_OPTIONS – Data layer option flags. Valid keywords are: DISABLE_APPEND_HINT and SKIP_CONSTRAINT_ERRORS.
DIRECTORY – Directory object to be used for dump, log and SQL files.
DUMPFILE – List of dump files to import from [expdat.dmp]. For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD – Password key for accessing encrypted data within a dump file. Not valid for network import jobs.
ENCRYPTION_PWD_PROMPT – Specifies whether to prompt for the encryption password [NO]. Terminal echo will be suppressed while standard input is read.
ESTIMATE – Calculate network job estimates. Valid keywords are: [BLOCKS] and STATISTICS.
EXCLUDE – Exclude specific object types. For example, EXCLUDE=SCHEMA:”=’HR'”.
FLASHBACK_SCN – SCN used to reset session snapshot.
FLASHBACK_TIME – Time used to find the closest corresponding SCN value.
FULL – Import everything from source [YES].
HELP – Display help messages [NO].
INCLUDE – Include specific object types. For example, INCLUDE=TABLE_DATA.
JOB_NAME – Name of import job to create.
KEEP_MASTER – Retain the master table after an import job that completes successfully [NO].
LOGFILE – Log file name [import.log].
LOGTIME – Specifies that messages displayed during import operations be timestamped. Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.
MASTER_ONLY – Import just the master table and then stop the job [NO].
METRICS – Report additional job information to the import log file [NO].
NETWORK_LINK – Name of remote database link to the source system.
NOLOGFILE – Do not write log file [NO].
PARALLEL – Change the number of active workers for current job.
PARFILE – Specify parameter file.
PARTITION_OPTIONS – Specify how partitions should be transformed. Valid keywords are: DEPARTITION, MERGE and [NONE].
QUERY – Predicate clause used to import a subset of a table. For example, QUERY=employees:”WHERE department_id > 10″.
REMAP_DATA – Specify a data conversion function. For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.
REMAP_DATAFILE – Redefine data file references in all DDL statements.
REMAP_SCHEMA – Objects from one schema are loaded into another schema.
REMAP_TABLE – Table names are remapped to another table. For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.
REMAP_TABLESPACE – Tablespace objects are remapped to another tablespace.
REUSE_DATAFILES – Tablespace will be initialized if it already exists [NO].
SCHEMAS – List of schemas to import.
SERVICE_NAME – Name of an active Service and associated resource group to constrain Oracle RAC resources.
SKIP_UNUSABLE_INDEXES – Skip indexes that were set to the Index Unusable state.
SOURCE_EDITION – Edition to be used for extracting metadata.
SQLFILE – Write all the SQL DDL to a specified file.
STATUS – Frequency (secs) job status is to be monitored where the default [0] will show new status when available.
STREAMS_CONFIGURATION – Enable the loading of Streams metadata [YES].
TABLE_EXISTS_ACTION – Action to take if imported object already exists. Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
TABLES – Identifies a list of tables to import. For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.
TABLESPACES – Identifies a list of tablespaces to import.
TARGET_EDITION – Edition to be used for loading metadata.
TRANSFORM – Metadata transform to apply to applicable objects. Valid keywords are: DISABLE_ARCHIVE_LOGGING, INMEMORY, INMEMORY_CLAUSE, LOB_STORAGE, OID, PCTSPACE, SEGMENT_ATTRIBUTES, STORAGE, and TABLE_COMPRESSION_CLAUSE.
TRANSPORTABLE – Options for choosing transportable data movement. Valid keywords are: ALWAYS and [NEVER]. Only valid in NETWORK_LINK mode import operations.
TRANSPORT_DATAFILES – List of data files to be imported by transportable mode.
TRANSPORT_FULL_CHECK – Verify storage segments of all tables [NO]. Only valid in NETWORK_LINK mode import operations.
TRANSPORT_TABLESPACES – List of tablespaces from which metadata will be loaded. Only valid in NETWORK_LINK mode import operations.
VERSION – Version of objects to import. Valid keywords are: [COMPATIBLE], LATEST, or any valid database version. Only valid for NETWORK_LINK and SQLFILE.
VIEWS_AS_TABLES – Identifies one or more views to be imported as tables. For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW. Note that in network import mode, a table name may be appended to the view name.

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

The following commands are valid while in interactive mode.

Note: abbreviations are allowed.

CONTINUE_CLIENT – Return to logging mode. Job will be restarted if idle.
EXIT_CLIENT – Quit client session and leave job running.
HELP – Summarize interactive commands.
KILL_JOB – Detach and delete job.
PARALLEL – Change the number of active workers for current job.
START_JOB – Start or resume current job. Valid keywords are: SKIP_CURRENT.
STATUS – Frequency (secs) job status is to be monitored where the default [0] will show new status when available.
STOP_JOB – Orderly shutdown of job execution and exits the client. Valid keywords are: IMMEDIATE.

This article will give a brief example of IMPDP utility by importing an export of table scott.emp into a new table scott.emp2.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Tue Jan 10 09:26:15 EST 2016 on pts/2

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 ~]$

Logon to your database as the user scott default password tiger.

[oracle@linux2 ~]$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 08:56:36 2016

Last Successful login time: Fri Jan 06 2016 11:01:31 -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 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 below to export the table scott.emp to file scott_emp.dmp. NOTE: you may have some errors with name of constraints from original table, but the data will be imported.

impdp scott/tiger DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir REMAP_TABLE=scott.emp:emp2

[oracle@linux2 ~]$ impdp scott/tiger DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir REMAP_TABLE=scott.emp:emp2

Import: Release 12.1.0.2.0 – Production on Wed Jan 11 10:03:13 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

Master table “SCOTT”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded

Starting “SCOTT”.”SYS_IMPORT_FULL_01″: scott/******** DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir REMAP_TABLE=scott.emp:emp2

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported “SCOTT”.”EMP2″ 8.820 KB 15 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

ORA-31684: Object type INDEX:”SCOTT”.”BT_SCOTT_EMP_JOB” already exists

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

ORA-31684: Object type CONSTRAINT:”SCOTT”.”PK_EMP” already exists

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

ORA-31684: Object type REF_CONSTRAINT:”SCOTT”.”FK_DEPTNO” already exists

Processing object type TABLE_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX

ORA-31684: Object type INDEX:”SCOTT”.”BM_SCOTT_EMP_JOB” already exists

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job “SCOTT”.”SYS_IMPORT_FULL_01″ completed with 4 error(s) at Wed Jan 11 10:04:08 2016 elapsed 0 00:00:53

[oracle@linux2 ~]$

Now logon to SQLPLUS as the user scott and describe your new table emp2.

[oracle@linux2 ~]$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 10:06:05 2016

Last Successful login time: Wed Jan 11 2016 10:03:13 -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> desc emp2

Name Null? Type

—————————————– ——– —————————-

EMPNO NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

DEPTNO NUMBER(2)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

SQL> select count(*) from emp2;

COUNT(*)

———-

SQL>

This completes a simple example of IMPDP utility in Oracle 12c.

Larry Catt

OCP

12c, Performance

Oracle 12c – Explain Multi-process Multi-threaded Oracle architecture

September 1, 2016 Larry Catt Leave a comment

Using 12c multithread mode, one Operating System process can support multiple Oracle processes running in DB.

Multithreaded mode is not enabled with a new 12c database by default, to enable multi-threaded mode, the parameter THREADED_EXECUTION must be set to TRUE and the database restarted. Some OS processes will still support only one oracle thread, while other OS processes may support multiple Oracle processes. This can be viewed in V$PROCESS view by identifying OS process ID for each oracle process.

When running in multi-threaded mode, you must have an administration account controlled by a password file. If a password file is not in use starting the system in multi-threaded mode will result in the error ORA-01017 invalid username/password; logon denied.

Larry Catt

OCP

Catt Solutions

Monthly Archives: September 2016

Oracle 12c – Monitor performance

Use ILM feature: Heat Maps

Oracle 12c – Configure and manage auditing

Oracle 12c – Use Row limit clause

Container Clause for Common Users in Oracle Multitenant Databases

Oracle 12c – Import data pump

Oracle 12c – Explain Multi-process Multi-threaded Oracle architecture

Oracle tips and tricks.