Monthly Archives: November 2016

12c, listener, network, tnsnames

ORACLE 12c – Configure server and client network for database

Leave a comment

Oracle Net Services are the elements that provides connection for distributed services. This article covers the methods of: Oracle Net; Local Naming Method; Easy Connect Naming Method; Directory Naming Method; and External Naming Methods

 

 

Oracle Net Method:

Oracle Net acts as the data courier, it establishes and maintains the connection between client and server.   In Oracle Net configuration a client is setup to connect to a listener process on the server via an alias name in the configuration file.   The Listener process on the server performs connection to database and client.  Oracle Net Services can handle non Oracle data source connections such as SQL Server, DB2 and external code libraries through EXTPROC.  Database server receives initial connection through the Listener and then hands off connection from client to a server process, once handoff has occurred the client communicates directly with database server.   Listener failure will not stop currently connected sessions, only new ones.  Client must configure a descriptor to give the database location, service name and port.  In order to connect, the tnsnames.ora file descriptor must have a matching entry in listener.ora of the listener process on server.

 

There are several other types of network connections besides Oracle Net:

  1. Java Application Connections
  2. Web Client connections by Application Web Server.
  3. OCI

 

 

Client TNSNAMES.ORA file:

 

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)

(HOST = mylinux.localdomain.com)

(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ORCL)

      (SID = ORCL)

    )

  )

 

Address Section:

Protocol – defines protocal being used by listener device, TCP is for TCP/IP

Host – can use name or IP, identifies IP of Listener

Port – Defines port listener is listening on, default is 1521

Connect data Section:

SID – Name of SID for database you wish to connect to.

SERVICE_NAME – Name given in the SERVICE_NAMES initialization parameter is the global database name.

INSTANCE_NAME – Id the database instance, it is optional and default is the SID.

DEDICATED SERVER – listener will start a dedicated server process for each incoming connection.  Each client will have its own server process which will terminate upon exit of the session.

 

Steps for listener connection:

  1. Listener receives client request.
  2. Listener starts dedicated server process and passes connection request to process.
  3. Client now connected directly to server process.
  4. Server process checks client’s credentials for authentication.
  5. If credentials are good, session is created for client.

 

 

Local Naming Method:

Local naming method uses TNSNAMES.ORA file to provide client with connection address, file is normally located in $ORACLE_HOME/network/admin

 

Easy Connect Naming Method:

Bypasses lookup in TNSNAMES.ORA file and uses simple string to connect to database server.  You can use Easy Connect if the following conditions are met:

  1. Oracle Net Services software installed
  2. TCP/IP supported on server and client.
  3. No add-ons like external procedure calls or heterogeneous services exist.
  4. EZCONNECT is listed in NAMES.DIRECTORY_PATH of SQLNET.ORA file.

 

CONNECT username/password@host/ORCL

 

Directory Naming Method:

Uses LDAP compliant technology such as Oracle Internet Directory, connection are mapped to connection descriptors in directory server.   LDAP server provides central management of network connection services.  Directory service is created during database creation and use EM Cloud or Oracle Net Manager to configure client for directory method.

 

External Naming Methods:

Third part method such as Network Information Services (NIS), allows client to resolve service to network service name and prevents local configuration of database connections.

 

 

Larry Catt

OCP

12c, CDB, Multitenant, PDB, privileges, Users

Users accounts in Multitenant Database

Leave a comment

Oracle 12c multitenant database consist of a CDB which houses one or more PDB which house application data.   Now this design has direct implications on user accounts and how they are implemented.    This article attempts to briefly cover the implementation of user accounts in the Oracle 12c multitenant architecture.

 

Oracle 12c multitenant database have two class of user accounts:  Common and Local.    Common users belong to the root container (CDB) and have full access to PDBs within the CDB.   Local users belong a particular PDB and do not have access to the root container (CDB) which houses them.   They following rules govern User accounts in the multitenant database architecture:

 

 

 

  1. Common user have the same identity in the root and every PDB.
  2. Common users can connect to root and PDB.
  3. Common users connected to root can perform ALTER PLUGGABLE DATABASE, CREATE USER/ROLE that affect multiple PDBs.
  4. Local users apply to only one PDB.
  5. Local users cannot connect to CDB their PDB is contained in.
  6. Local users cannot cross PDBs but same username can exist in two different PDB in same CDB.
  7. Most privileges only apply in current container. IE user must first connect to container to query tables in that container and they cannot do it from the root.

 

Larry Catt

OCP

12c, ADDM

Oracle 12c – Perform Emergency Monitoring and Real-time ADDM

Leave a comment

Emergency Monitoring feature of EM Cloud allows for DBA to connect to non-responding database by way of proprietary resources for diagnosis of problem.  The EM agent connects directly to SGA, bypassing the SQL retrieval layer.   The page displays ASH data and Hang Analysis table, displaying blocking sessions and allows for killing this blocks.  Emergency Monitoring page is accessible from Performance Menu of EM Cloud.  EM Express has no diagnostic connection.

 

Real-Time ADDM – introduced in EM Cloud 12c, much of functionality is available in EM Express.  ADDM is to help with analysis of problem which cause unresponsive behavior without restarting DB.  ADDM checks for problems based on set of predefined criteria, then suggests solutions.

Two Ways ADDM connects:

  1. Normal Connection – normal JDBC connection, used for extensive performance analysis.
  2. Diagnostic Connection – latchless connection for hung situations, where JDBC connection is not available.

 

Differences between REAL-Time ADDM and ADDM

  1. Real-Time ADDM performs similar analysis to ADDM by does not use AWR snapshots, rather it uses ASH data from SGA.
  2. Real-Time ADDM using in-memory real time performance data.
  3. Real-Time ADDM analysis is automatic when problem is detected.
  4. RT ADDM scans every 3 seconds using MMON process.
  5. RT ADDM does not use lock or latch.
  6. Things that would trigger RT ADDM analysis:
    1. High load – active session > 3x number of CPU
    2. I/O bound – I/O of active sessions concentrated on a single block read.
    3. CPU bound – Active sessions > 10% of all sessions and CPU > 50% utilization.
    4. Over-allocated memory – memory allocated > 95% of physical memory.
    5. Interconnect bound – Single block continually used.
    6. Session limit – near 100%
    7. Process limit – near 100%
    8. Hung session – hung session > 10% of all sessions.
    9. Deadlock detected – Any deadlock.
  7. MMON slave process will store ADDM reports created by AWR, which is accessible by DBA_HIST_REPORTS view.
  8. RT ADDM controls level analysis resource usage by:
    1. Duration between reports – RT ADDM will not produce new report if it has been less than 5 min since last report.
    2. Oracle RAC control – Only one RAC instance can create ADDM report at any given time.
    3. Repeated Triggers – A unique issue can only trigger an analysis if the impact is 100% or greater previous impact within 45 min.
    4. Newly identified issues – If new issue is identified, an analysis will be performed regardless of active session load.

 

 

Larry Catt

OCP

12c, dbca

Create non-CDB database in Oracle 12c

Leave a comment

The multitenant database in Oracle 12c is an option that provides for a single instance supporting multiple database all sharing the same Oracle resources.  The default database creation is still a standard single instance and single database, outside of a RAC configuration.    This procedure demonstrates creation of a non-CDB database from command prompt of a Linux platform.   In this procedure, you must already have the Oracle binary software installed.

 

 

  1. Logon to your oracle server as the oracle software owner.

 

[root@linux2 ~]# su – oracle

Last login: Wed Nov  7 08:45:50 EST 2016 on pts/0

[oracle@linux2 ~]$

 

  1. View the system parameters for oracle binaries to include: ORACLE_HOME and PATH.

 

[oracle@linux2 ~]$ echo $ORACLE_HOME

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

[oracle@linux2 ~]$ 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 ~]$

 

  1. Set a new ORACLE_SID value for creation of new database.

 

[oracle@linux2 ~]$ export ORACLE_SID=orcl

[oracle@linux2 ~]$ echo $ORACLE_SID

orcl

[oracle@linux2 ~]$

 

  1. Create a database named for ORACLE_SID defined above using the dbca utility. You must define the following:

-silent – prevents opening of DBCA GUI and performs.

–createDatabase – create a database

-ignorePreReqs – ignore prerequisite checks

-templateName General_Purpose.dbc  – General Purpose database

-gdbName orcl – Global DB Name

-sid orcl – Instance Name

-sysPassword password – Password for SYS user

-systemPassword password – Password for SYSTEM user

-emConfiguration NONE – Configure EM Express

-datafileDestination /u01/oradata/orcl – Location of datafiles, does not need to exist

-redoLogFileSize 10 – Size of redo logs in megabytes

-recoveryAreaDestination NONE – Configure recovery area.

-storageType FS – Type of storage

-memoryPercentage 40 – Amount of memory for instance.

 

 

 

dbca -silent -createDatabase -ignorePreReqs -templateName General_Purpose.dbc -gdbName orcl -sid orcl -sysPassword password -systemPassword password \

-emConfiguration NONE -datafileDestination /u01/oradata/orcl -redoLogFileSize 10 \

-recoveryAreaDestination NONE -storageType FS -memoryPercentage 40

 

[oracle@linux2 db_1]$ dbca -silent -createDatabase -ignorePreReqs -templateName                           General_Purpose.dbc -gdbName orcl -sid orcl -sysPassword password -systemPasswor                          d password \

> -emConfiguration NONE -datafileDestination /u01/oradata/orcl -redoLogFileSize                           10 \

> -recoveryAreaDestination NONE -storageType FS -memoryPercentage 40

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

50% complete

55% complete

56% complete

60% complete

62% complete

Completing Database Creation

66% complete

70% complete

73% complete

85% complete

96% complete

100% complete

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

[oracle@linux2 db_1]$

 

  1. Network configuration should have been complete by the DBCA tool.
  2. Now from the command prompt, use the tnsping command to check connection with command: tnsping orcl.

 

[oracle@linux2 admin]$ tnsping orcl

 

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 07-NOV-2016 10:07:56

 

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

 

Used parameter files:

 

 

Used TNSNAMES adapter to resolve the alias

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

OK (0 msec)

[oracle@linux2 admin]$

 

  1. Connect to the newly created database as the sysdba user.

 

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

 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 7 10:08:40 2016

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

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>

 

 

  1. This completes creation of a non-CDB in Oracle 12c

 

Larry Catt

12c, Performance

Oracle 12c – Explain Partitioning enhancements

Leave a comment

Interval-Reference Partitioning –

The top partitioning strategy in 12c is interval-reference.   Partitions in the referenced partitioned table link back to interval partition in the parent table.  Any operation that transform interval partitions in conventional partition of the parent table will construct the corresponding transformation in child table and create partition in child table as necessary.     This type of partitioning gives better performance.   COMPATIBLE parameter must be set to 12.0.0.0 or higher to use.

 

Cascade Functionality –

The commands TRUNCATE Partition and EXCHANGE Partition in reference or interval-reference partitions cascade to references in child table.  Thus child table inherit changes to parent table, by default cascade options are disabled for compatibility purposes.   CASCADE option is provided for the ALTER TABLE EXCHANGE PARTITION and ALTET TABLE EXCHANGE SUBPARTITION SQL commands.  All foreign keys must be defined with ON DELETE CASCADE option.

 

Maintenance Operations on Multiple Partitions –

Oracle 12c allows you to do multiple tasks with single command, where partition names are now separated by commas.

  1. Add – multiple partitions and sub-partitions can be added with single add command.
  2. Drop – multiple partitions and sub-partitions can be dropped with single drop command.
  3. Truncate – ditto.
  4. Merge – multiple partitions specified with single INTO PARTITION clause.
  5. SPLIT – Single partition specified and INTO PARTITION clause can specify multiple partitions for division.

 

Online Move Partition –

Oracle 12c allows for DDL operation to move or redefine while DML operations continue. MOVE PARTITION clause of ALTER TABLE changes existing data through the creating new segments and dropping old.   However, MODIFY PARTITION clause of ALTER TABLE only changes future data.

MOVE PARTITION clause of ALTER TABLE can do the following:

  1. Re-cluster data and reduce fragmentation.
  2. Move a partition to another tablespace.
  3. Modify create-time attribute
  4. Compress data using table compression.

 

Larry Catt

OCP

12c, SQLLoader

Oracle 12c – Use SQL*Loader and External table enhancements

Leave a comment

New SQL*loader express mode you can load data from file by specifying only a table name with the following requirements:

  1. Delimited data only
  2. Column data types only character, numbers, and datetime.
  3. Order is same as table column definition.
  4. Express mode does not use a control file.

 

SQL*Loader express mode assumes the following

  1. Data file – when non is specified, looks for file table_name.dat in current directory
  2. Load Method – default is external tables.
  3. Fields – use name, data type, and order of table definition. Records are delimited by comma, separated by new line, have no enclosures, and use left-right trim.
  4. DOP – Degree of Parallelism is set to Auto.
  5. Date Format – Uses NLS settings used.
  6. Character Set – Uses NLS settings
  7. Append mode – Appended to table if it already exists.
  8. File Names – where %p is process ID of database slave process:
    1. Data File – table_name.dat
    2. SQL*Loader Log File – table_name.log
    3. Oracle DB log file – table_name_%p.log_xt
    4. Bad Files – table_name_%p.bad

 

 

EXAMPLE:

 

  1. Logon to SQLPLUS as sysdba and grant the user scott create any directory and drop any directory

 

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 3 12:40:37 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

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 create any directory to scott;

Grant succeeded.

SQL> grant drop any directory to scott;

Grant succeeded.

SQL>

 

  1. Reconnect to SQLPLUS as the user scott and create the table test.

 

SQL> connect scott/password

Connected.

SQL> create table test(a char(5), b char(5));

Table created.

SQL>

 

  1. Exit out of SQLPLUS and create a file test.dat with the following text.

 

a,a

b,b

c,c

 

  1. Execute the command: sqlldr scott/password table=test

 

 

[oracle@linux2 ~]$ sqlldr scott/password table=test

SQL*Loader: Release 12.1.0.2.0 – Production on Tue Oct 3 12:36:18 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: TEST

Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table TEST:

  3 Rows successfully loaded.

 

Check the log files:

  test.log

  test_%p.log_xt

for more information about the load.

[oracle@linux2 ~]$

 

  1. Logon to SQLPLUS as the user scott and perform select on table TEST to see the results of SQLLDR utility with Express Mode.

 

[oracle@linux2 ~]$ sqlplus scott/password

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 3 12:44:03 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Oct 03 2016 12:41:50 -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 test;

A     B

—– —–

a     a

b     b

c     c

 

SQL>

 

 

  1. This completes example of SQLLDR Express feature.

 

Larry Catt

OCP

12c, flashback

Oracle 12c – New Feature in Flashback Data Archive

Leave a comment

User-context tracking – allows you to determine which user has made a change to a table through the use of procedure SET_CONTEXT_LEVEL in the DBMS_FLASHBACK_ARCHIVE package.  The information can be accessed via the GET_SYS_CONTEXT procedure. When set to TYPICAL changes are recorded with userID, global userID, and hostname.

 

begin

DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL(TYPICAL);

end;

/

 

Database hardening – You can now associate a subset of tables with a single application and enable flashback just those subset of tables.  You can perform the following on the subset of tables:

  1. Enable flashback on just subset.
  2. Lock DML operation on subset.

This enables you to protect and track sensitive set of tables.   This procedure REGISTER_APPLICATION in package DBMS_FLASHBACK_ARCHIVE is used for this hardening process.

 

Import and export of history – You can create a temporary history table for a table. This history can be extract showing exact changes to a table over period of time.

 

Larry Catt

OCP