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

Catt Solutions

Monthly Archives: October 2016

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