Category Archives: 12c

Oracle 12c – In-Database archiving

October 16, 2016 Larry Catt Leave a comment

Oracle 12c supports in-database archiving to compress data that is no longer readily needed but is still kept online. This article covers the rules of in-Database archiving with a brief example.

Use In-Database archiving rules–

Allows rows to be kept in the database but invisible from applications.
Idea is the data is available for compliance but minimize impact to the performance.
Archive data can be compressed to help improve backup performance.
The column clause ROW ARCHIVAL of the table must be enabled and the ORA_ARCHIVE_STATE must be set to non-zero value.
The session parameter ROW ARCHIVAL VISIBILITY is set to ACTIVE, only rows where ORA_ARCHIVE_STATE equals zero will be displayed.
The session parameter ROW_ARCHIVAL VISIBILITY is set to ALL, all rows will be displayed.

In-Database archiving Eample.

Logon to sqlplus as sysdba.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 15 09:10:34 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>

Alter session to set row archival visibility to active.

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

Session altered.

SQL>

Create table test.archival_test with following statement including ROW ARCHIVAL clause. This creates a hidden column call ORA_ARCHIVE_STATE which determines the archival status of the row.

CREATE TABLE test.archival_test (col1 NUMBER, col2 VARCHAR2( 20)) ROW ARCHIVAL;

SQL> CREATE TABLE test.archival_test (col1 NUMBER, col2 VARCHAR2( 20)) ROW ARCHIVAL;

Table created.

SQL>

Insert records into test.archival_test table.

INSERT INTO test.archival_test (col1, col2) VALUES (1, ‘Record One’);

INSERT INTO test.archival_test (col1, col2) VALUES (2, ‘Record Two’);

INSERT INTO test.archival_test (col1, col2) VALUES (3, ‘Record Three’);

INSERT INTO test.archival_test (col1, col2) VALUES (4, ‘Record Four’);

Select values in test.archival_test table to determine ora_archive_state column value.

column col1 format 9999

column col2 format a12

column ora_archive_state format a15

SQL> column col1 format 9999

column col2 format a12

column ora_archive_state format a15

SELECT col1, col2, ora_archive_state FROM test.archival_test;

SQL> SQL> SQL>

COL1 COL2 ORA_ARCHIVE_STA

—– ———— —————

1 Record One 0

2 Record Two 0

3 Record Three 0

4 Record Four 0

SQL>

Update the column ora_archive_state from 0 to 5 for record 3.

UPDATE test.archival_test SET ora_archive_state = ‘5’ WHERE col1 = 3;

SQL> UPDATE test.archival_test SET ora_archive_state = ‘5’ WHERE col1 = 3;

1 row updated.

SQL>

Perform another select of the table ora_archive_state and note that record three does not appear in result set.

SQL> SELECT col1, col2, ora_archive_state FROM test.archival_test;

COL1 COL2 ORA_ARCHIVE_STA

—– ———— —————

1 Record One 0

2 Record Two 0

4 Record Four 0

SQL>

Set the ROW ARCHIVAL VISIBILITY to all.

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

SQL> ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

Session altered.

SQL>

Now slect all values in archival_test and not that all values are displayed.

SELECT * FROM test.archival_test;

SQL> SELECT * FROM test.archival_test;

COL1 COL2

—– ————

1 Record One

2 Record Two

3 Record Three

4 Record Four

SQL>

This completes the example of In-Database archiving in Oracle 12c.

Larry Catt

OCP

12c, data pump

Oracle 12c – Network-based Data Pump operations

October 15, 2016 Larry Catt Leave a comment

NETWORK_LINK parameter is used with impdp as part of import operation. Data is moved directly using SQL. Select statement moves data from remote database over the link and INSERT clause inserts into local database. No dump files are involved. Current user database links are not supported. Only PUBLIC, FIXED user or connected user Database Links can be used.

Restrictions of Network Link:

Tables with LONG or LONG RAW that reside in admin tablespaces are not supported.
Auditing cannot be enabled on admin tablespaces when performing full transportable export.
Source and Target database cannot be more than two versions off.

This article will give a basic demonstration of Network-based Data Pump operations by extracting the scott.emp table from a database ORCL and importing it into a pluggable database PDB1.

Logon to your Pluggable database server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Jan 11 14:15:49 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 ~]$

Verify that you can reach the remote database ORCL via the network with tnsping command.

[oracle@linux2 ~]$ tnsping orcl

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 11-JAN-2016 14:43:20

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.98)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

OK (10 msec)

[oracle@linux2 ~]$

Logon local PDB1 database as sysdba.

[oracle@linux2 ~]$ sqlplus / as sysdba@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 15:12:43 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>

Change container to pdb1.

SQL> alter session set container=pdb1;

Session altered.

SQL>

Create user pdb1_user with dba and create session privileges.

SQL> create user pdb1_user identified by password;

User created.

SQL> grant create session to pdb1_user;

Grant succeeded.

SQL> grant dba to pdb1_user;

Grant succeeded.

SQL>

Connect to pdb1 as pdb1_user.

SQL> connect pdb1_user/password@pdb1;

Connected.

SQL>

Create a public database link to ORCL

create public database link orcl_db

connect to scott identified by tiger

using ‘orcl’;

SQL> create public database link orcl_db

connect to scott identified by tiger

using ‘orcl’;

SQL> 2 3

Database link created.

SQL>

Create a directory object dmpdir for any necessary data pump files. In this example the log file will be placed in this directory object.

SQL> create directory dmpdir as ‘/home/oracle’;

Directory created.

SQL>

Test the database link to the database ORCL by querying the table scott.emp.

SQL> select * from scott.emp@orcl_db fetch first 2 rows only;

EMPNO ENAME JOB MGR

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

DEPTNO HIREDATE SAL COMM

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

1111 DOE SALES 1

10 01-JAN-17 1000 1000

7369 SMITH CLERK 7902

20 17-DEC-80 800

SQL>

Now exit out of pdb1.

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

Due to new security restriction you may have to raise the privileges of the user scott in extraction database. Logon to database orcl as the sys and grant scott dba privilege.

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

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 12 07:58:33 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> grant dba to scott;

Grant succeeded.

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

Now from the command prompt, execute the impdp utility

impdp pdb1_user/password@pdb1 directory=dmpdir LOGFILE=scott_emp_transfer.log network_link=ORCL_DB remap_table=scott.emp:emp

[oracle@linux2 ~]$ impdp pdb1_user/password@pdb1 directory=dmpdir LOGFILE=scott_emp_transfer.log network_link=ORCL_DB remap_table=scott.emp:emp

Import: Release 12.1.0.2.0 – Production on Thu Jan 12 07:59:02 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 “PDB1_USER”.”SYS_IMPORT_SCHEMA_01″: pdb1_user/********@pdb1 directory=dmpdir LOGFILE=scott_emp_transfer.log network_link=ORCL_DB remap_table=scott.emp:emp

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 448 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . imported “SCOTT”.”BONUS” 14 rows

. . imported “SCOTT”.”DEPT” 4 rows

. . imported “SCOTT”.”EMP” 15 rows

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

. . imported “SCOTT”.”FB_TEST” 1 rows

. . imported “SCOTT”.”SALGRADE” 5 rows

. . imported “SCOTT”.”TEST” 3 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

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

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX

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

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Job “PDB1_USER”.”SYS_IMPORT_SCHEMA_01″ successfully completed at Thu Jan 12 08:16:20 2016 elapsed 0 00:17:15

[oracle@linux2 ~]$

Now logon to pdb1 as the user pdb1_user and query the new table emp.

sqlplus pdb1_user/password@pdb1

select * from pdb1_user.emp fetch first 2 rows only;

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

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 12 08:49:13 2016

Last Successful login time: Thu Jan 12 2016 07:59:02 -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> select * from scott.emp fetch first 2 rows only;

EMPNO ENAME JOB MGR DEPTNO HIREDATE SAL

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

COMM

———-

1111 DOE SALES 1 10 01-JAN-17 1000

1000

7369 SMITH CLERK 7902 20 17-DEC-80 800

SQL>

This completes using Network based data pump.

Larry Catt

OCP

12c, Administration

Oracle 12c – Use Secure File LOBs

October 14, 2016 Larry Catt Leave a comment

SecureFile LOBs were added to Oracle 11g to provide better performance over the legacy LOB format of BasicFile LOBs.

New features of SecureFile LOBs:

Intelligent LOB compression allows user to compress file.
Intelligent LOB encryption in place and allows for random reads and writes.
Deduplication option allows for duplicate data to only be stored once.
LOB data path optimization allows for logical cache above storage level, read prefetching, new caching modes, vectored IO

In Oracle 12c the initialization parameter of DB_SECUREFILE determines use of LOB type with the following options:

ALWAYS – Attempts to use SecureFile LOBS but uses BasicFile LOB if ASSM is not in use.

PERMITTED – Allows SecureFile LOBs to be created.

PREFERRED – All LOBs are SecureFile unless BasicFile is specified. When PREFERRED is sent inherited type from partition and columns are ignored.

NEVER – Disallows use of SecureFile LOBs, attempts to create SecureFile LOBs will be created as BasicFile.

IGNORE – SECUREFILE keyword and all options are ignored.

Online redefinition can be performed at table/partition level and does not require the table or partition to be taken offline. However redefinition will require storage equal to double current LOB and global indexes will have to be rebuilt. Redefined SecureFiles inherit LOB column settings for deduplication, encryption, and compression made at original BasicFile LOB.

DBMS_LOB package has several options for redefine:

DBMS_LOB.GETOPTIONS – give current settings of SecureFile LOB.

DBMS_LOB.SETOPTIONS – allows to change current settings.

DBMS_LOB.IISECUREFILE – Determines if it is SECUREFILE LOB or not.

DBMS_SPECE.SPACE_USAGE – Procedure returns amount of space used in blocks by all LOBs and can only be used if auto segment space management is in use.

Larry Catt

OCP

12c, CDB, dbca, script

Create CDB in silent mode

October 10, 2016 Larry Catt Leave a comment

In Oracle 12c RDBMS you can use the Database Configuration Assistant to create a CDB from shell scripts or command line. You can provide all of the necessary parameters for the CDB creation through a response file or at the command prompt. In this example we will provide all required command line option to create new CDB called cdb1.

Logon to your Oracle server as the Oracle software owner.

[root@linux2 ~]# su – oracle

Last login: Wed Oct 5 15:10:16 EDT 2016 on pts/2

[oracle@linux2 ~]$

Define the following parameter with values:
1. -gdbName = the name of the database you wish to create.
2. -sid = the SID you wish to use for the database.
3. -SysPassword = password for the SYS user.
4. -SystemPassword = password for the SYSTEM user.
5. -datafileDestination = location of the data file storage.
6. -storageType = type of storage used for files.
7. -characterSet = character set of database.

You will have to also supply the tags for –silent option, -createDatabase, -templateName, and –createAsContainerDatabase.

Update the below DBCA command with the values defined in step 2.

dbca -silent \

-createDatabase -templateName General_Purpose.dbc \

-gdbName <db name> -sid <SID name> \

-createAsContainerDatabase true \

-SysPassword <SYS Password> -SystemPassword <SYSTEM Password> \

-datafileDestination <Directory Name> \

-storageType <Type of storage> -characterSet <Character Set>

Example execution.

[oracle@linux2 database]$ dbca -silent \

> -createDatabase -templateName General_Purpose.dbc \

> -gdbName cdb1 -sid cdb1 \

> -createAsContainerDatabase true \

> -SysPassword password12 -SystemPassword password12 \

> -datafileDestination /u01/oradata \

> -storageType FS -characterSet AL32UTF8

Copying database files

1% complete

3% complete

11% complete

18% complete

26% complete

33% complete

37% complete

Creating and starting Oracle instance

40% complete

45% complete

46% complete

47% complete

52% complete

57% complete

58% complete

59% complete

62% complete

Completing Database Creation

66% complete

70% complete

74% complete

85% complete

96% complete

100% complete

Look at the log file “/opt/app/oracle/cfgtoollogs/dbca/cdb1/cdb1.log” for further details.

[oracle@linux2 database]$

NOTE: The following step should only be used if your system only has one SID and ORACLE_HOME. Update profile with the ORACLE_HOME and ORACLE_SID environmental variables. Add the ORACLE_HOME/bin path to your PATH variable. You can use the following two statements update the .bash_profile file located in your oracle software owner’s home file.

egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print “export ORACLE_HOME=”$2″”}’ >> ~/.bash_profile

egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print “export ORACLE_SID=”$1″”}’ >> ~/.bash_profile

echo “export PATH=$PATH:$ORACLE_HOME/bin” >> ~/.bash_profile

[oracle@linux2 app]$ egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print “export ORACLE_SID=”$1″”}’ >> ~/.bash_profile

[oracle@linux2 app]$

[oracle@linux2 app]$ echo “export PATH=$PATH:$ORACLE_HOME/bin” >> ~/.bash_profile

[oracle@linux2 app]$

[oracle@linux2 app]$ cat ~/.bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export ORACLE_HOME=/opt/app/oracle/product/12.1.0.2/db_1

export ORACLE_SID=cdb1

export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/opt/app/oracle/product/12.1.0.2/db_1/bin:/opt/app/oracle/product/12.1.0.2/db_1/bin

[oracle@linux2 app]$

Now re-execute the .bash_profile file to update environmental variables with the command: . ~/.bash_profile

[oracle@linux2 app]$ . ~/.bash_profile

[oracle@linux2 app]$

Verify that the variables have been updating by echo the environmental variables of $ORACLE_HOME, $ORACLE_SID, $PATH.

[oracle@linux2 app]$ echo $ORACLE_HOME

/opt/app/oracle/product/12.1.0.2/db_1

[oracle@linux2 app]$ echo $ORACLE_SID

cdb1

[oracle@linux2 app]$ echo $PATH

/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/opt/app/oracle/product/12.1.0.2/db_1/bin:/opt/app/oracle/product/12.1.0.2/db_1/bin

[oracle@linux2 app]$

Create the file tnsnames.ora with the following statement:

echo -e “CDB1 =\n” \

” (DESCRIPTION =\n” \

” (ADDRESS_LIST =\n” \

” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

” )\n” \

” (CONNECT_DATA =\n” \

” (SERVER = DEDICATED)\n” \

” (SERVICE_NAME = “$ORACLE_SID”)\n” \

” (SID = “$ORACLE_SID”)\n” \

” )\n” \

” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

Execution Example:

[oracle@linux2 admin]$ echo -e “CDB1 =\n” \

> ” (DESCRIPTION =\n” \

> ” (ADDRESS_LIST =\n” \

> ” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

> ” )\n” \

> ” (CONNECT_DATA =\n” \

> ” (SERVER = DEDICATED)\n” \

> ” (SERVICE_NAME = “$ORACLE_SID”)\n” \

> ” (SID = “$ORACLE_SID”)\n” \

> ” )\n” \

> ” )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

[oracle@linux2 admin]$ cat tnsnames.ora

CDB1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cdb1)

(SID = cdb1)

)

[oracle@linux2 admin]$

Create the file listener.ora with the following statement:

echo -e “SID_LIST_LISTENER =\n” \

” (SID_LIST =\n” \

” (SID_DESC =\n” \

” (SID_NAME = “$ORACLE_SID”)\n” \

” (SERVICE_NAME = “$ORACLE_SID”)\n” \

” (GLOBAL_DBNAME = “$ORACLE_SID”)\n” \

” (ORACLE_HOME = “$ORACLE_HOME”)\n” \

” )\n” \

“\nLISTENER =\n” \

” (DESCRIPTION_LIST =\n” \

” (DESCRIPTION =\n” \

” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

” ))\n” \

“\nADR_BASE_LISTENER = /opt/app/oracle\n” \

“\nLOGGING_LISTENER=ON\n” \

“\nTRACE_LEVEL_SERVER=SUPPORT\n” \

“\nSQLNET.INBOUND_CONNECT_TIMEOUT=240\n” >> $ORACLE_HOME/network/admin/listener.ora

Execution example:

[oracle@linux2 admin]$ echo -e “SID_LIST_LISTENER =\n” \

> ” (SID_LIST =\n” \

> ” (SID_DESC =\n” \

> ” (SID_NAME = “$ORACLE_SID”)\n” \

> ” (SERVICE_NAME = “$ORACLE_SID”)\n” \

> ” (GLOBAL_DBNAME = “$ORACLE_SID”)\n” \

> ” (ORACLE_HOME = “$ORACLE_HOME”)\n” \

> ” )\n” \

> “\nLISTENER =\n” \

> ” (DESCRIPTION_LIST =\n” \

> ” (DESCRIPTION =\n” \

> ” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

> ” ))\n” \

> “\nADR_BASE_LISTENER = /opt/app/oracle\n” \

> “\nLOGGING_LISTENER=ON\n” \

> “\nTRACE_LEVEL_SERVER=SUPPORT\n” \

> “\nSQLNET.INBOUND_CONNECT_TIMEOUT=240\n” >> $ORACLE_HOME/network/admin/listener.ora

[oracle@linux2 admin]$ cat listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = cdb1)

(SERVICE_NAME = cdb1)

(GLOBAL_DBNAME = cdb1)

(ORACLE_HOME = /opt/app/oracle/product/12.1.0.2/db_1)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))

))

ADR_BASE_LISTENER = /opt/app/oracle

LOGGING_LISTENER=ON

TRACE_LEVEL_SERVER=SUPPORT

SQLNET.INBOUND_CONNECT_TIMEOUT=240

[oracle@linux2 admin]$

Stat the listener process with the command: lsnrctl start

[oracle@linux2 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 19-OCT-2016 14:06:54

Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 12.1.0.2.0 – Production

System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))

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 19-OCT-2016 14:06:54

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))

Services Summary…

Service “cdb1” has 1 instance(s).

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

The command completed successfully

[oracle@linux2 admin]$

Test connect to the new database with command: tnsping cdb1

[oracle@linux2 admin]$ tnsping cdb1

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 19-OCT-2016 14:15:55

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb1) (SID = cdb1)))

OK (0 msec)

[oracle@linux2 admin]$

This completes the creation of CDB in silent mode.

Larry Catt

OCP

12c

Oracle 12c – Extended data types

October 8, 2016 Larry Catt Leave a comment

In Oracle 12c the maximum size of VARCHAR2, NVARCHAR2, and RAW has been increased to 32767 bytes from 4000 bytes for VARCHAR2 and NVARCHAR2 and 2000 bytes for RAW. The init parameter MAX_STRING_SIZE must be set to the value of EXTENDED to use this increase size, if it is set to STANDARD, the old limits apply. If VARCHAR2 and NVARCHAR2 are greater than 4000bytes and RAW greater than 2000bytes, they are considered EXTENDED data types and Oracle LOB technology is used for storage. If tablespace is using Automatic Segment Space Management (ASSM), SecureFiles LOBs are used for storage, otherwise they use BasicFile LOBs. The EXTENDED columns do not appear to be LOBs in the user operations and columns cannot be used with DBMS_LOB package.

Larry Catt

OCP

12c, ADDM, ADR, ASH, Performance

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

Catt Solutions

Category Archives: 12c

Oracle 12c – In-Database archiving

Oracle 12c – Network-based Data Pump operations

Oracle 12c – Use Secure File LOBs

Create CDB in silent mode

Oracle 12c – Extended data types

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 tips and tricks.