Category Archives: 12c

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

12c, backup/recovery

Oracle 12c – Block Change Tracking

August 20, 2016 Larry Catt Leave a comment

Oracle 12c provides block change tracking to increase performance of incremental backups by recording change blocks for each datafile, thus stopping RMAN from scanning each datafile to determine changes. This feature must be enabled by default it is disabled in the database. Tracking information on each changed block is stored in a file located in the directory defined by parameter DB_CREATE_FILE_DEST and is only used by level 1 backups. Additionally, the tracking must be enabled before the level 0 backup is performed in order for level 1 backups to use it. Use ALTER DATABASE command to turn on Block Change Tracking:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

You can also specify the location of the tracking file:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/linux1/rman_change_tracking.file’ REUSE;

Disable Block tracking with:

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Larry Catt

OCP

12c, Performance

Oracle 12c – Explain Index enhancements for partitioned tables

August 18, 2016 Larry Catt Leave a comment

Asynchronous Global Index Maintenance

In Oracle 12c the commands DROP PARTITION and TRUNCATE PARTITION only affects metadata. The index maintenance is asynchronous now, thus it does not invalidate indexes on the partitions. You then use UPDATE INDEXES commands to synch indexes during maintenance hours. Limitations of asynchronous global index maintenance are:

Only for heap tables.
Object types in table not supported.
Domain indexes not supported.
Cannot be user SYS

SYS.PMO_DEFERRED_GIDX_MAINT_JOB –

Oracle 12c provides this Automatic scheduled job to execute at 0200 and it updates any indexes impacted by DROP PARTITION and TRUNCATE PARTITION commands. This job can be executed anytime by DBMS_SCHEDULER.RUN_JOB.

Partial Indexes –

In Oracle 12c you can now create local and global indexes on subsets of partitions. Partial indexes do not supported unique indexes and cannot enforce unique constraints. By default, indexes are created as FULL indexes. You create partial indexing using the INDEXING clause at the partition and sub-partition level.

ONLINE Move Partition –

In Oracle 12c, the ALTER TABLE … MOVE PARTITION allows for DML operations to take place while executing. During MOVE operation, global indexes are maintained and rebuilt later by scheduler job.

Larry Catt

OCP

12c, Administration

Oracle 12c – Implement real-time database operation monitoring

August 17, 2016 Larry Catt Leave a comment

This procedure outlines the steps of performing real-time database operation monitoring via the sqlplus console using the package DBMS_SQL_MONITOR.

Start SQLPLUS and connect as a user with the appropriate privileges.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 20 08:00:16 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 user test and table test_tab, than insert 10 miliion records into test.test_tab for monitoring test.

create user test identified by test

default tablespace users quota unlimited on users;

grant dba to test;

create table test.test_tab(col_a number,

col_b varchar2(10));

Jullare

val_a number:=1;

val_b varchar2(10):=’a’;

begin

while val_a<20000001

loop

insert into test.test_tab(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;

Execution:

SQL> create user test identified by test

default tablespace users quota unlimited on users;

User created.

SQL> grant dba to test;

Grant succeeded.

SQL> create table test.test_tab(col_a number,

col_b varchar2(10));

Table created.

SQL> Jullare

val_a number:=1;

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

4 begin

5 while val_a<20000001

6 loop

7 insert into test.test_tab(col_a, col_b)

8 values(val_a, val_b);

9 val_a:=val_a+1;

10 if val_b=’z’

11 then

12 val_b:=’a’;

13 else

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

15 end if;

16 end loop;

17 commit;

18 end;

19 /

PL/SQL procedure successfully completed.

SQL>

Define a variable to hold the execution ID.

var opid NUMBER

Begin the database operation.

EXEC :opid := DBMS_SQL_MONITOR.BEGIN_OPERATION(‘op_test’);

Run the queries in the operation.

SELECT col_a FROM test.test_tab order by col_a;

SELECT col_b FROM test.test_tab order by col_a;

End the database operation.

EXEC DBMS_SQL_MONITOR.END_OPERATION(‘op_test’, :opid);

Confirm that the database operation completed.

SQL> SELECT dbop_name, status FROM v$sql_monitor WHERE dbop_name = ‘op_test’;

DBOP_NAME STATUS

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

rtsm_op DONE

SQL>

This completes manual execution of real_time database operation monitoring with DBMS_SQL_MONITOR.

Larry Catt

OCP

12c, Administration, Performance

Oracle 12c – Use Resource Manager to manage resources

August 13, 2016 Larry Catt Leave a comment

Oracle Database Resource Manager is designed to optimize resource allocation among concurrent database sessions. It prevents from the OS making resource decisions during high overhead periods without awareness of database needs. Resource Manager gives the database more control over resource allocation and gives the ability to place sessions into groups and allocated resources to those groups, through resource plans. Oracle 12c comes with the following Resource Plans by default:

DEFAULT_MAINTENANCE_PLAN – default plan for maintenance windows.
DEFAULT_PLAN – Default gives priority to SYS_GROUP and gives minimal resources to maintenance and diagnostics operations.
DSS_PLAN – for data warehouse gives priority to DDS queries and less to non-DDS and ETL operations.
ETL_CRITICAL_PLAN – for data warehouse gives priority to ETL over DDS.
INTERNAL_QUIESCE – quiescing the database, must be done manually with QUIESCE command.
MIXED_WORKLOAD_PLAN – prioritized interactive processes over batch.

Three elements of Resource Management are:

Resource Consumer Group – group of sessions based on resource needs.
Resource Plan – directives that detail how resources are allocated to group.
Resource Plan Directive – Associates resource consumer group with a particular plan and how resources are allocated to consumer group.

Resource plan directives can limit CPU and I/O for sessions in the group. This is done by a processes call switching, which specifies an action to take when a call exceeds the limit. Resource plan directive attribute SWITCH_GROUP determines which action to take. If the attribute is a consumer group name action 1 will be taken, if the attribute is KILL_SESSION action 2, and if attribute is CANCEL_SQL action 3.

The possible actions are:

Session is switched to a consumer group with lower resource allocation.
Session is killed.
Sessions current SQL is aborted.

Attributes which control I/O and CPU are as follow: NOTE: unlimited means no limit.

SWITCH_TIME – CPU time in seconds before switch
SWITCH_IO_MEGABYTES – amount of I/O read and writes before switch.
SWITCH_IO_REQS – number of I/O requests before switch.

Two attributes that can modify behavior of resource plan switching:

SWITCH_ESTIMATE – If TRUE – database estimate execution time of each call. if it exceeds SWITCH_TIME attribute, the session is moved to Default is FALSE.
SWITCH_FOR_CALL – If TRUE – session switched because of exceeding resource is returned to original group once completed. Default is NULL.

Larry Catt

OCP

12c, alert / logs, Performance

Oracle 12c – Monitor database alerts

August 11, 2016 Larry Catt Leave a comment

Alert Logs and Trace Files:

All Oracle background process have their own trace files where information about errors are dumped to when detected. Alert logs also contain information about database errors, with less detail and normally point towards specific trace file. Message in alert log include:

Init parameter with non-default values or other notations.
all errors: internal ORA-600, block corruption ORA-1578 and deadlock ORA-60
Admin DDL CREATE, ALTER, and DROP and startup and shutdown.

Alert logs are maintained in both XML and plain text. The ADRCI utility is used to view XML alert logs. Both Alert and trace files are stored under the Automatic Diagnostic Repository directory structure. The init parameter MAX_DUMP_FILE_SIZE limits the size of trace files to a set number of OS blocks. You cannot limit size of alert logs, but it is a good practice to periodically rename the alert file to reduce file size oracle is using. The database instance will automatically create a new alert log with appropriate name and this renaming process can be done with the database online.

Statistics:

Statistics are generated by the Oracle 12c database to help increase level of performance and to all the optimizer to decide on the best execution plans of SQL statements. If the SQL_TRACE init parameter is set to TRUE, performance statistics will be generated for SQL statements. SQL tracing can be set at session level by ‘ALTER SESSION SET SQL_TRACE’ command. Trace files are written to Automatic Diagnostic Repository directory structure. The DBMS_SESSION and DBMS_MONITOR packages can be used to control SQL tracing for a session.

Adaptive Thresholds:

You can monitor database continuously with adaptive thresholds. By setting warning and critical alerts for system metrics. This can be done using moving window metrics. Two types of adaptive thresholds:

Percentage of maximum – Defined as the max of data/resource in moving window. like 80% of observed.

Significance level – Defined to a unusual value of threshold, set to one of the following values

High (.95)
Very High (.99)
Severe (.999)
Extreme (.9999)

Larry Catt

OCP

12c, PDB

Connection to PDB

August 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. PDBs are services within a CDB and thus are connected to through the CDB they are contained inside of or through oracle networking. This procedure demonstrates the connection through both processes.

Connection via CDB

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

Once you are connected to the CDB, you can now change your session to connect to the PDB of interest. In this example we will connect to pdb1. You do this with the command alter session set container=<PDB_name>l

SQL> show con_name;

CON_NAME

——————————

CDB$ROOT

SQL> alter session set container = pdb1;

Session altered.

SQL> show con_name

CON_NAME

——————————

PDB1

SQL>

This completes connecting the PDB from CDB.

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

View the database connection available in the listener process with command lsnrctl status.

[oracle@linux2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 24-JUN-2016 14:03:44

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

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date 24-JUN-2016 14:02:17

Uptime 0 days 0 hr. 1 min. 26 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.15.75)(PORT=1521)))

Services Summary…

Service “cdb1” has 2 instance(s).

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

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

Service “cdb1XDB” has 1 instance(s).

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

Service “pdb1” has 1 instance(s).

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

The command completed successfully

[oracle@linux2 ~]$

See the PDB pdb1 in the container cdb1. Connect directly to the PDB via oracle listener by referencing this listener server.

[oracle@linux2 ~]$ sqlplus system/password@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 24 14:57: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>SQL> show con_name

CON_NAME

——————————

PDB1

SQL>

This completes connection to PDB via oracle networking.

Larry Catt

OCP

12c, Administration, ILM

Use ILM feature: Automatic Database Optimization (ADO)

August 2, 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 Automatic Database Optimization (ADO) within a 12c RDBMS. ADO allows us to create policies for compression and data movement within database by use of metadata at the row and segment level.

Logon to your Oracle server as the Oracle software owner.

[root@linux2 ~]# su – oracle

Last login: Tue Jul 13 09:01:45 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 ~]$

Logon to SQLPLUS with sysdba permissions.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 14 06:55:06 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>

Using the table test.test_hm, add a compression policy for table if data is not modified in 1 day.

SQL> ALTER TABLE test.test_hm ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 1 DAYS OF NO MODIFICATION;

Table altered.

SQL>

You must delete a policy if you already have a policy on an object. Example

SQL> ALTER TABLE test.test_hm ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 1 DAYS OF NO ACCESS;

ALTER TABLE test.test_hm ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 1 DAYS OF NO ACCESS

ERROR at line 1:

ORA-38323: policy conflicts with policy 1

SQL> alter table test.test_hm ilm delete policy p1;

Table altered.

SQL> ALTER TABLE test.test_hm ILM ADD POLICY COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 1 DAYS OF NO ACCESS;

Table altered.

SQL>

Compression options for ADO policies are:

COMPRESS ADVANCED – on a heap table maps to standard compression for indexes and LOW for LOB segments.
COMPRESS FOR QUERY LOW/ QUERY HIGH – on a heap table maps to standard compression for indexes and MEDIUM for LOB segments.
COMPRESS FOR ARCHIVE LOW/ ARCHIVE HIGH – on a heap table maps to standard compression for indexes and HIGH for LOB segments.

This completes our coverage of ADO in Oracle 12c

Larry Catt

OCP

12c, CDB, Multitenant, PDB

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

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

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

Larry Catt

OCP

Catt Solutions

Category Archives: 12c

Oracle 12c – Import data pump

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

Oracle 12c – Block Change Tracking

Oracle 12c – Explain Index enhancements for partitioned tables

Oracle 12c – Implement real-time database operation monitoring

Oracle 12c – Use Resource Manager to manage resources

Oracle 12c – Monitor database alerts

Connection to PDB

Use ILM feature: Automatic Database Optimization (ADO)

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

Oracle tips and tricks.