Oracle 12c Create CDB with Database Configuration Assistant in Windows

In Oracle 12c, Container Databases were introduced which allowed for multiple separate database to be placed in a single oracle instance to reduce the amount of resource consumed.   This procedure covers the creation of a CDB using Oracle Database Configuration Assistant in a Windows environment.

 

  1. Logon your windows server as the administrator.

 

  1. Open Oracle Database Configuration Assistant.

  1. At the Database Operation Page select “Create Database”

  1. At the Creation Mode Page select “Advanced Mode”

  1. At the Database Template Page select the Desired type of database, in This example we select “General Purpose or Transaction Processing”

  1. At the Database Identification Page enter the Global Database Name and SID, then select “Create as Container Database” and “Create an Empty Container Database”

  1. At the Management Options Page, select Configure Enterprise Manger (EM) Database Express if desired.

 

  1. At the Database Credentials Page, select Use the Same Administrative Password for All Accounts and enter a valid password.

 

  1. Additionally enter a password the administrative account on the windows system.

  1. On the Network Configuration Page, select the Listener to be used by the new database.

  1. At the Storage Location Page, select the appropriate storage for your database.

  1. At the Database Options page select nothing and Press Next button.

 

  1. At the Initialization Parameters Page, select the defaults and Press the Next button.

 

  1. At the Creation Options Page, select Create Database and Generate Database Creation Scripts (giving a valid destination directory) and press Next button.

  1. At the Summary Page, press the Finish button.

  1. The Progress Page, a confirmation of script creation will be displayed, once complete press OK.

  1. The CDB will now be created in Windows.

 

  1. A confirmation screen will appear once the install is completed and press the Close button to terminate Database Configuration Assistant.

 

 

This completes creation of Oracle 12c CDB in Windows environment with Database Configuration Assistant.

 

 

Larry Catt

OCP

Oracle 12c – Troubleshoot database issues

Oracle uses the ADR (Automatic Diagnostic Repository) directory structure to store all log files concerning the health of the database.   This directory structure is stored under the $ORACLE_BASE/diag directory.    Each background process of the Oracle RDBMS maintains its own logs in addition to recording information to the central alert log.   Two versions of oracle logs are maintained by the ADR:  XML and plain text.  Background processes may place more detailed information into trace files referenced in the log file messages.  General steps for troubleshooting database issues.

 

 

  1. Logon to SQLPLUS with sysdba privileges.
  2. Determine the location of each log and trace file type by quering the view V$DIAG_INFO as shown below:

 

SQL> set linesize 200

SQL> column name format a30

SQL> column value format a150

SQL>  select name, value from v$diag_info;

 

NAME                           VALUE

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

Diag Enabled                   TRUE

ADR Base                       /opt/app/oracle/diag

ADR Home                       /opt/app/oracle/diag/diag/rdbms/orcl/ORCL

Diag Trace                     /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/trace

Diag Alert                     /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/alert

Diag Incident                  /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/incident

Diag Cdump                     /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/cdump

Health Monitor                 /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/hm

Default Trace File             /opt/app/oracle/diag/diag/rdbms/orcl/ORCL/trace/ORCL_ora_15788.trc

 

  1. Examine the log files starting with the alert log in directory: $ORACLE_BASE/diag/rdbms/<DB_NAME>/<SID>/trace directory.

 

This alert log will contain the following:

  • Internal errors (ORA-00600)
  • Block Corruption errors (ORA-01578)
  • Deadlock errors (ORA-00060)
  • All DDL statements: CREATE, ALTER, and DROP.
  • All SHUTDOWN, STARTUP, and ARCHIVELOG statements.
  • Errors with shared processes, server process, and dispatcher processes.
  • Errors from automatic refresh operations in materialized views.
  • Values of non-default initialization parameters.
  • Background and Archiving processes.

 

 

  1. Diagnostics is based on a problem or incident:

 

PROBLEM – critical error in the database, internal error such ORA- error.

INCIDENT – is a single occurrence of a problem and is tracked by a specific number.

 

Each INCIDENT records the following:

  • Entry is made to alert log.
  • Incident alert is sent to EM.
  • Diagnostic info is sent to dump file in the ADR subdirectory for that incident type.

 

  1. Take corrective action based on information received from INCIDENT referenced in logs and trace files.
  2. Re-execute process causing original issue and repeat troubleshooting if necessary.
  3. Additional steps of SQL TRACING and getting assistance from ORACLE SUPPORT may be required in some situations.

 

Larry Catt

OCP

Oracle 12c – Implement Space Management

Oracle provides a large amount of storage options to reduce the space required for data storage and increase the speed of retrieval.   This article provides a brief overview of space management in an Oracle 12c database with some general suggestions to increase flexibility and performance.

 

Local Managed Tablespaces:

You should always use locally managed tablespaces in Oracle, this is where bitmaps in the tablespace to manage all extents and not external in the data dictionary.  Any tablespace can be locally managed, including SYSTEM and SYSAUX.   Advantage of local managed tablespaces:

  1. Allocation and deallocation of space is local managed, resulting in higher performance.
  2. Local managed temporary tablespaces do not generate undo or redo.
  3. The AUTOALLOCATE clause allows for automatically extent sizing.
  4. Reduce contention on data dictionary resulting from no access by tablespace.
  5. No need to coalesce free extents.

DBMS_SPACE_ADMIN is a package containing procedures specific to locally managed tablespaces.

Use the ‘EXTENT MANAGEMENT LOCAL AUTOALLOCATE’ is the clause to specify local management with auto select of extent sizes which is the default.  To have uniformed extent sizes you must specify, ‘UNIFORM’

Example:

 

CREATE TABLESPACE data1

DATAFILE ‘/ u02/ oracle/ data/ orcl01. dbf’

SIZE 50M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

 

CREATE TABLESPACE data1 DATAFILE ‘/ u02/ oracle/ data/ orcl01. dbf’ SIZE 50M;

 

Two methods you can use for segment space in locally managed tablespace.  Manual and Automatic:  Manual uses a free lists to manage free space in segments.   Automatic is the default method and it uses bitmaps to manage free space and is more efficient.   You can explicitly specify clause:  ‘SEGMENT SPACE MANAGEMENT AUTO’

Example:

 

CREATE TABLESPACE data1

DATAFILE ‘/ u02/ oracle/ data/ orcl01. dbf’

SIZE 50M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

SEGMENT SPACE MANAGEMENT AUTO;

 

 

BASIC TABLE COMPRESSION

Is the lowest level compression and does not compress data in a table from INSERT/UPDATE.   Data is only compressed during bulk loads.   Basic compression is available in EE license and its format is the same as Advance Compression.  It is possible to convert from Basic to Advance, by simply changing the storage definition of the table or partition.

 

Advance Row Compression

Originally called OLTP Table compression in 11g, Advance Row compression compresses data regardless how it arrived.  It also minimizes overhead in write operations by eliminating duplicates in data blocks even across rows.  Advance Compression is an additional licensing option.

 

Hybrid Columnar Compression

Hybrid Columnar Compression (HCC) is a method of organizing data within a set of database blocks.   The underlying OS must support HCC and requires the use of Oracle Storage Exadata, Pillar Axiom or Sun ZFS Storage Appliance.  It stores multiple rows by columns, in what is call compression units.  Columns of like data are stored together similar data types, thus can achieve compression of x6 x15.

 

Tables created in tablespace with compression option will be compressed unless you specify no compression or another compression method in the CREATE TABLE clause.

 

Segment Shrink

Online segment shrink allows you to reclaim fragmented free space below high watermark in a segment.  With the following benefits:

  1. Compacting provides better cache use.
  2. Compact data has less data blocks to be scanned.

Segment shrink is performed online without DML operations interference.    Concurrent DML operations are blocked at end of shrink for space deallocation.  Indexes are maintained and usable after shrink.   Unused disk space is reclaimed above and below high water mark.  No additional space is needed during shrink.  By default shrink operation compact data, move the high water mark and release unused space.

Segment shrink requires row movement and thus requires you to enable row movement and disable rowed triggers.  Shrink operations can only happen on segments using locally managed tablespace with ASSM Automatic segment space management.  In ASSM all segment types are shrinkable except:

  1. IOT mapping tables
  2. Tables with rowed based materialized views.
  3. Tables with function-based indexes.
  4. SECUREFILE LOBs
  5. Compressed tables.

 

Invoking Online Segment Shrink – SHRINK SPACE

You can shrink space in the following by use of SHRINK SPACE clause

  1. Table – with ALTER TABLE..
  2. Index-Organized Table – with ALTER TABLE
  3. Index – with ALTER INDEX
  4. Partition – with ALTER TABLE
  5. Sub-partition – with ALTER TABLE
  6. Materialized view – with ALTER MATERIALIZED VIEW
  7. Materialized view log – with ALTER MATERIALIZED VIEW LOG

 

Two operations with SHRINK SPACE clause.

  1. COMPACT – shrink operation divided into two sections. First phase:  defragments and compacts rows, does not reset high water mark or release space.  You can re-issue shrink operation without the COMPACT operation during off hours to complete deallocation of space and movement of high water mark.
  2. CASCADE – This extends shrink operation to all dependent objects.

 

Example shrink a table and all of its dependent segments:

 

ALTER TABLE employees SHRINK SPACE CASCADE;

 

Example shrink a single partition of a partitioned table:

 

ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE;

 

 

 

Larry Catt

OCP

12c New Features – Redaction Policies

Oracle 12c RDBMS Redaction Policies allow the RDBMS Developer or DBA the ability to obscure data to prevent viewing by none authorized users.  This procedure shows the use of 12c Redaction against a common table.

 

 

  1. Logon as the oracle DBA user.

 

mylinux# sqlplus system/password

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 1 13:58:21 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. Create test user, test table, and insert dummy data for execute this procedure against with the following SQL statements.

 

create user test identified by test default tablespace users

quota unlimited on users;

grant resource to test;

grant create session to test;

create table test.emp(f_name varchar2(30),

l_name varchar2(30),

start_date date,

salary number(8,2));

insert into test.emp values(‘Bob’, ‘Smith’, sysdate-30, 45000);

insert into test.emp values(‘Nancy’,’Jones’, sysdate-628,87000);

insert into test.emp values(‘Thomas’,’Harris’,sysdate-1245,79000);

 

  1. Now as the system user create a policy using the RDBMS_REDACTION package to redact the column SALARY in the table TEST.EMP.

 

BEGIN

DBMS_REDACT.ADD_POLICY( object_schema => ‘test’,

object_name => ’emp’,

column_name => ‘salary’,

policy_name => ‘redact_salary’,

function_type => DBMS_REDACT.FULL,

expression => ‘1=1’);

END;

/

 

  1. Now as the TEST user, select the values from table TEST.EMP and you will notice that the SALARY columns are all zero.

 

SQL> connect test/test

Connected.

SQL> set linesize 140

SQL> select * from test.emp;

 

F_NAME                         L_NAME                         START_DAT     SALARY

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

Bob                            Smith                          04-APR-16          0

Nancy                          Jones                          15-AUG-14          0

Thomas                         Harris                         06-DEC-12          0

 

SQL>

 

  1. Logon as the sys user and grant the user TEST the privilege to see the redacted column with the privilege EXEMPT REDACTION POLICY. By default even the owner of the table cannot see the redacted column without this privilege.

 

SQL> connect / as sysdba

Connected.

SQL> grant exempt redaction policy to test;

 

Grant succeeded.

 

SQL> connect test/test

Connected.

SQL> set linesize 140

SQL> select * from test.emp;

 

F_NAME                         L_NAME                         START_DAT     SALARY

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

Bob                            Smith                          04-APR-16      45000

Nancy                          Jones                          15-AUG-14      87000

Thomas                         Harris                         06-DEC-12      79000

 

SQL>

 

  1. Now the column is protected from all users except those with exempt redaction policy privileges.

 

Larry Catt, OCP

 

 

 

Privileges of Local Users in Oracle Multitenant Database

In Oracle Multitenant Databases, local users only have access and privileges to a single PDB and not the CDB.    You can have the same user name across multiple PDBs housed in the same CDB, but the users share no privileges and only shares a common name.    This procedure give a brief overview of local user privileges in Oracle 12c Multitenant Databases.  Listed below are the stated restriction on local user privileges.

 

  1. A privilege granted locally can only be used in that container, even if granted by common user.
  2. Both common and local users can grant privileges locally.
  3. Both common and local users can grant privileges to other common or local users.
  4. Grantor must connect to container and specify CONTAINER=CURRENT clause.
  5. Common and Local user can grant privileges locally if they have appropriate privileges.

 

 

  • A privilege granted locally can only be used in that container, even if granted by common user.

 

  1. As the common user c##dba logon to pdb1 and pdb2 creating users user_local.

 

SQL> connect c##dba/password@pdb1

Connected.

SQL> create user user_local identified by password;

User created.

SQL> connect c##dba/password@pdb2

Connected.

SQL>  create user user_local identified by password;

User created.

 

SQL>

 

  1. Now connect to pdb1 as c##dba and grant the user user_local the DBA role. Connect as user_local and  create an additional local user user_test.    NOTE:   The user user_local can successfully connect to pdb1 and create an additional local user in this PDB.

 

SQL> connect c##dba/password@pdb1

Connected.

SQL> grant dba to user_local;

Grant succeeded.

 

SQL> connect user_local/password@pdb1

Connected.

 

SQL> create user user_test identified by password;

User created.

 

SQL>

 

  1. Now attempt to connect to pdb2 with local user user_local. NOTE:   The user user_local does not even have permission to connect to pdb2, even though the user exists in pdb2.

 

SQL> connect user_local/password@pdb2

ERROR:

ORA-01045: user USER_LOCAL lacks CREATE SESSION privilege; logon denied

 

Warning: You are no longer connected to ORACLE.

SQL>

 

  • Both common and local users can grant privileges locally.

 

  1. Using local_user created above from pdb1, you can grant privileges to the newly created user user_test logon as local_user.

 

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

 

SQL>

 

 

  • Both common and local users can grant privileges to other common or local users.

 

  • Using local_user created above from pdb1, you can grant privileges to the newly created user user_test logon as local_user.

 

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

 

SQL>

 

 

  • Grantor must connect to container and specify CONTAINER=CURRENT clause or specify no CONTAINER clause. CONTAINER=ALL is not available in PDB.

 

  1. As the user_local grant resource to user_test without a container clause and CONTAINER=ALL clause.

 

SQL> connect user_local/password@pdb1

Connected.

SQL> grant resource to user_test;

Grant succeeded.

 

SQL> grant resource to user_test container=all;

grant resource to user_test container=all

*

ERROR at line 1:

ORA-65029: a Local User may not grant or revoke a Common Privilege or Role

 

SQL>

 

  • Common and Local user can grant privileges locally if they have appropriate privileges.

 

 

  1. Local user_local can create additional users in PDB pdb1 once the user has been granted the DBA role.

 

SQL> connect c##dba/password@pdb1

Connected.

SQL> grant dba to user_local;

Grant succeeded.

 

SQL> connect user_local/password@pdb1

Connected.

 

SQL> create user user_test identified by password;

User created.

 

SQL>

 

 

This completes the overview of privileges for local users in Oracle 12c Multitenant Database.

 

 

Larry Catt

OCP