Monthly Archives: September 2015

12c, Cloud Control, EM, EM Express

EM Express 12c Storage Page

Leave a comment

Oracle EM Express Storage Page provides the ability to administrate the storage requirements of an Oracle database.  This article covers the general layout of EM Express Storage Page and its functional usage.

 

  1. Logon the EM Express as an administrative user.   In this case we will use the URL:  https://10.30.15.63:5500/em.   This indicates the EM Express is installed on server 10.30.15.63, listening on port 5500 (which is the default).  NOTE:  You may receive a certificate warning but select continue to site option.   Enter your sysdba credentials at the sign in page and mark the check box as sysdba, normally this is the user name and password you used to create the database.

  1. From the top level drop down memory you have four options: Configuration, Storage, Security and Performance.

  1. The Storage drop down give five database storage options: Tablespaces, Undo Management, Redo Log Groups, Archive Logs, and Control Files.

  1. The Tablespaces option allows you to view tablespace space, add/drop datafiles and modify storage parameters.

  1. The Undo Management option allows you to view undo statistics and switch undo tablespaces.

  1. The Redo Log Groups option allows you to view existing redo log groups and their statistics and add new groups.

  1. The Archive Logs option allows you to view archive log data.                       
  2. The Control Files option you to view current control files data and create backup copies of controlfiles.

 

This article summarized the feature and use of the Storage Page of Oracle 12c EM Express.

 

Larry Catt

OCP

12c, CDB, Multitenant, privileges, Users

Privileges of Common Users in Oracle Multitenant Database

Leave a comment

Privileges for Common Users in Oracle 12c multitenant database architecture have unique constraints allowing for privileges at the CDB level and the PDB levels.    Common users may or may not have privileges at all containers in the multitenant database or they may be restricted to only a few.   Additionally, privileges maybe restricted to objects in individual containers.   The following are general rules governing privileges of common users and we will examine each rule in the below procedures:

 

  1. A privilege granted commonly can be used in every container and future container.
  2. Only common users can grant common privileges.
  3. Granter of common privileges must connect to root and specify CONTAINER=ALL clause.
  4. Can include system and object privilege.
  5. Privileges should never be given to public.

 

 

  • A privilege granted commonly can be used in every container and future container.

 

  1. Using the common user C##DBA we will create the user at the CDB level and grant DBA privileges with the CONTAINER clause equal to all.

 

 SQL> show con_name

CON_NAME

——————————

CDB$ROOT

SQL> create user c##dba identified by password;

User created.

SQL> grant dba to c##dba container=all;

Grant succeeded.

SQL>  connect c##dba/password@cdb1;

Connected.

SQL> create user c##user identified by password container=all;

User created.

SQL>

 

  1. Now alter the session to container PDB1 as the same user and create a user there. NOTE:  Permissions are good both in CDB1 and PDB1 for Common users.   This is true because container clause was set to ALL.

 

SQL> show user

USER is “C##DBA”

SQL> alter session set container=PDB1;

Session altered.

SQL> show user

USER is “C##DBA”

SQL>  create user pdb1_user identified by password;

User created.

SQL>

 

 

  • Only common users can grant common privileges.

 

  1. As the common user c##dba grant resource to common user c##user.

 

SQL> connect c##dba/password@cdb1;

Connected.

SQL> grant resource to c##user;

Grant succeeded.

SQL>

 

  • Granter of common privileges must connect to root and specify CONTAINER=ALL clause for privileges to be granted in all containers.

 

  1. This one is a little tricky because it appears that you can do it, however the key is common privilege, you can have privileges in the CDB and a few PDB and it not be common. This procedure shows an example of this scenario.
  2. Connect to PDB1 as the common user c##dba and grant dba to the user c##user.

 

SQL> connect c##dba/password@pdb1

Connected.

SQL> grant dba to c##user;

Grant succeeded.

SQL>

 

  1. Now a user with common privilege of DBA should be able to connect to any PDB in the CDB, however when we try to connect to pdb2, it fails. Further more when we look at the view dba_role_privs, we note that user C##USER does have DBA role, but not commonly.

 

SQL> connect c##user/password@pdb2

ERROR:

ORA-01045: user C##USER lacks CREATE SESSION privilege; logon denied

 

Warning: You are no longer connected to ORACLE.

SQL> connect c##dba/password@pdb1

Connected.

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE                        COM

—————————— —

C##USER                        NO

SQL>

 

  1. Now logon to the root container as the C##DBA user and grant the DBA role to C##USER. Then view the dba_role_privs table and attempt to connect to PDB2.

 

SQL> connect c##dba/password@cdb1

Connected.

SQL> grant dba to c##user container=all;

 

Grant succeeded.

 

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE                        COM

—————————— —

C##USER                        NO

C##USER                        NO

C##USER                        YES

 

SQL> connect c##user/password@pdb2

Connected.

SQL>

 

  • Can include system and object privilege.

 

  1. Revoke all rights from user C##USER. If you are following from the above example, the user C##USER has been granted the DBA role twice locally and once commonly. So revoke Common grant at ROOT container and local root grant.   Then connect to PDB1 to remove local grant at PDB1.

 

SQL> connect c##dba/password@cdb1

Connected.

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE                        COM

—————————— —

C##USER                        NO

C##USER                        NO

C##USER                        YES

 

SQL> revoke dba from c##user container=ALL;

 

Revoke succeeded.

 

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE                        COM

—————————— —

C##USER                        NO

C##USER                        NO

 

SQL>  revoke dba from c##user container=current;

 

Revoke succeeded.

 

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

GRANTEE                        COM

—————————— —

C##USER                        NO

 

SQL> connect c##dba/password@pdb1

Connected.

SQL> revoke dba from c##user;

 

Revoke succeeded.

 

SQL>

SQL> column grantee format a30

select grantee, common from dba_role_privs where grantee=’C##USER’;

SQL>

no rows selected

SQL>

 

  1. Now reconnect to C##DBA at the root level and grant connect and select on V$SESSIONS commonly the user C##USER. To test common privilege to objects.

 

SQL> connect c##dba/password@cdb1

Connected.

SQL> grant create session to c##user container=all;

Grant succeeded.

SQL> grant select on sys.v_$session to c##user container=all;

Grant succeeded.

SQL>

 

  1. Connect the C##USER and execute a select statement on v$session and v$sql objects to validate access from root container. NOTE:  We are able to access object v$session but not v$sql.

 

SQL> connect c##user/password@cdb1

Connected.

SQL> select count(*) from v$session;

 

  COUNT(*)

———-

        37

SQL> select count(*) from v$sql;

select count(*) from v$sql

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

 

  1. Connect to PDB1 and repeat the above queries to validate common access privileges to objects.

 

SQL> connect c##user/password@pdb1

Connected.

SQL> select count(*) from v$session;

  COUNT(*)

———-

        35

SQL> select count(*) from v$sql;

select count(*) from v$sql

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SQL>

 

  • Privileges should never be given to public from common account.

 

  1. Once a privilege is granted to public, all users have access to that privilege, which is a huge security risk.    However, there is no restrictions built into Oracle to prevent this from occurring.  We will demonstrate this by the creation of new user c##dba2, grant the new user no privileges, grant dba role to public, connect to both the CDB and PDB, and finally create a user.    This is extremely dangerous, because granting privileges to public grants access to system and object privileges to every user.    Finally we will revoke DBA role from public and note that the new user c##dba2 can no longer connect.

 

SQL> connect / as sysdba

Connected.

SQL>  alter session set container=cdb$root;

Session altered.

SQL> create user c##dba2 identified by password;

User created.

SQL> grant dba to public container=all;

Grant succeeded.

SQL> connect c##dba2/password@cdb1

Connected.

SQL> connect c##dba2/password@pdb1

Connected.

SQL> create user testing identified by password;

User created.

 

SQL> connect / as sysdba

Connected.

SQL> revoke dba from public container=all;

 

Revoke succeeded.

 

SQL> connect c##dba2/password@cdb1

ERROR:

ORA-01045: user C##DBA2 lacks CREATE SESSION privilege; logon denied

 

Warning: You are no longer connected to ORACLE.

SQL>

This completes overview of Privileges for Common Users in Oracle Multitenant Databases.

 

Larry Catt

OCP

12c, security

Oracle 12c – Implement column and tablespace encryption

Leave a comment

Transparent Data Encryption (TDE) is used for both column and tablespace encryption.    It uses a software key store which is a container that stores the TDE master encryption key.   There exist one TDE master encryption key per database and the location is defined in the SQLNET.ora file.    In previous releases this key store was referred to as an Oracle Wallet.   Software key store must be open before you can create an encrypted column or tablespace and before you can store or retrieve encrypted data.  Once you open, it is open to all sessions until it is explicitly close it or database shutdown.  Transparent Data Encryption is designed to protect data stored on a disk or other media and protects data from access by other means outside of the database.  It protects backups on tape and from accessing data via the OS directly to datafiles.  TDE supports the encryption types:  3DES168, AES128, AES192, AES256.  You specify the encryption algorithm when creating encrypted column or tablespace.  By default TDE used AES192.   TDE adds a salt to plaintext before encrypt to increase security.   TDE also uses Message Authentication Code (MAC) to data for integrity checking and uses SHA-1 by default.

 

Column Encryption

Columns are encrypted and decrypted at the SQL layer and any service that bypasses the SQL layer cannot use TDE column encryption.  TDE column encryption cannot be used with the following features:

  1. Index other than B-TREE
  2. Range scan through indexes.
  3. Synchronous change data capture.
  4. Transportable tablespaces.
  5. Columns used in foreign key constraints.

BUT you can encrypt these columns using DBMS_CRYPTO package.  You can encrypt the following datatypes: Note some size restrictions exist.

  1. BINARY_DOUBLE
  2. BINARY_FLOAT
  3. CHAR
  4. DATA
  5. INTERVAL DAY TO SECOND
  6. INTERVAL YEAR TO MONTH
  7. NCHAR
  8. NUMBER
  9. NVARCHAR2
  10. RAW
  11. TIMESTAMP
  12. VARCHAR2

Example of encrypted column with AES192:

 

CREATE TABLE emp (

ssn number,

fname varchar(100),

lname varchar(100),

salary  number encrypt);

 

Never salt a column that is an index and being encrypted.  Example:

 

CREATE TABLE emp (

ssn number encrypt no salt,

fname varchar(100),

lname varchar(100),

salary  number encrypt);

 

Change encrypt type by the USING clause. Example

 

CREATE TABLE emp (

ssn number encrypt no salt,

fname varchar(100),

lname varchar(100),

salary  number encrypt USING ‘3DES168’);

 

You can use the ALTER TABLE ADD to add an encrypted column to existing table.  You can use the ALTER TABLE MODIFY to make an existing column encrypted.

 

 

Tablespace Encryption

You can encrypt a permanent tablespace to protect the data.  This encrypts all data blocks of any segment type.  Data of an encrypted tablespace is also encrypted in undo, redo and all temp space.   There is no overhead in encrypting tablespace.  Restricting of encrypting tablespaces:

  1. You cannot encrypt an existing tablespace.
  2. Encrypted tablespace cannot be moved to another database.
  3. You must open key store after mount and before open to recover an encrypted tablespace.

 

Creation of encrypted tablespace example

 

CREATE TABLESPACE data_secret

DATAFILE ‘/u01/oradata/data_secret01.dbf’ size 1g

ENCRYPTION

DEFAULT STORAGE(ENCRYPT);

 

 

Larry Catt

OCP

12c, CDB, dbca, PDB

Oracle 12c Create CDB with PDB

Leave a comment

In Oracle 12c, Container Databases (CDB) were introduced which allowed for multiple Pluggable database (PDB) to be placed in a single oracle instance to reduce the amount of resource consumed.   This procedure covers the creation of a CDB with a PDB 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”, “Create a Container Database with one or more PDBs”, enter Number of PDBs = 1, and Name your PDB.

  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. 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 with PDB will now be created in Windows.

  1. A confirmation screen will appear once the install is completed with information about EM configuration, press the Exit button.

  1. Back at the Progress Page, Press the Close button to exit.

 

 

 

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

 

 

Larry Catt

OCP

 

12c, backup/recovery, RMAN

Oracle 12c – Detect and repair data failures with Data Recovery Advisor

Leave a comment

In Oracle 12c the Data Recovery Advisor is a part of Support Workbench which provides data corruption repair, database health checks, and RMAN functions.  It can perform the following:

  • Display data corruption problems.
  • Assess extent and impact.
  • Recommend repair options.
  • Automate repair processes.

Health checks are diagnostic procedures that assess the health of the database, they are executed reactively by an error occurring or manually.   The Data Recovery Advisor can diagnosis the following failures:

  • Datafiles or control files not accessible to database.
  • Physical corruption of datafiles.
  • Inconsistent datafile timestamps.
  • I/O failures such as hardware, OS drivers, exceeding OS resource limits.
  • Logical corruptions will require support interaction in general.

Failures are a persistent data corruption detected by a health check, normally found reactively when a problem is encountered and recorded in ADR.  The Data Recovery Advisor only after it occurs and assigns a priority of:

  • Critical – immediate action required to avoid database failure and corrupt.
  • High – importance may make some of database unavailable or corrupt.
  • Low – Failure can be ignored and will not cause database failure.

DRA Repairs are options which may include block media recovery, datafile media recovery, or Oracle Flashback Database.   DRA normally will provide both automated and manual recovery operations.   In automated repair DRA performance fix, verifies and clears error.

 

RMAN utility provides the ability to LIST, ADVISE and REPAIR failures.  RMAN is extracting this information from the ADR activities running in the database.  Commands used in RMAN for displaysing, advising, repairing, and changing failure check variables.

 

 

  • LIST FAILURE

 

RMAN> list failure;

using target database control file instead of recovery catalog

Database Role: PRIMARY

no failures found that match specification

RMAN>

 

  • ADVISE FAILURE – Displays repair options for failures.

 

RMAN> advise failure;

Database Role: PRIMARY

no failures found that match specification

RMAN>

 

  • REPAIR FAILURE – Allows you to repair failure with options, if there are automatic repairs from advise failure.

 

  • CHANGE FAILURE – allows you to set different priority to failure

 

RMAN> Change Failure 3 priority low;

 

 

Larry Catt

OCP

12c, backup/recovery, flashback, RMAN

Oracle 12c – Steps to perform a Flashback Database Operation

Leave a comment

The flashback database feature allows you to reset the database to earlier point in time to resolve logical corruption.    It is faster than point in time recovery and does not require backup and restore operations.  Flashback database operations is available from RMAN or SQL with command: FLASHBACK DATABASE.   To enable flashback database the FAST RECOVERY AREA must be configured and FLASHBACK RETENTION TARGET set.   The DB_FLASHBACK_RETENTION_TARGET states how far in the past the database can be recovered to and Flashback must be setup in advance of need. (See article Enabling Flashback Database)  The database periodically copies blocks from every datafile into recovery area and maintains logs of changes to those blocks.  Additionally flashback logs, redo logs must be available for entire span of retention target.   FLASHBACK DATABASE window is the time that is covered by flashback logs.  FLASHBACK will delete oldest logs first if it runs low on space.

 

FLASHBACK has these limits:

  1. It can only undo changes to datafile made by Oracle Database, not media failure or deletion of file.
  2. It cannot undo shrink operations.
  3. If control file is recovered or recreated all flashback data is invalid.
  4. IF NOLOGGING is used, block corruption is likely if flashback is used.

 

Restore points can be used instead of time or SCN, Restore points are alias of scn or time.

Commands that support restore point are RECOVER DATABASE and FLASHBACK DATABASE in RMAN   and FLASHBACK TABLE in SQL.  Guaranteed restore points never age out of control file and must be explicitly dropped, it ensures that you can use flashback database to a single SCN even if flashback logging is not enabled.  When enabled it enforces retention of flashback logs all the way to guaranteed SCN.  This article will demonstrate database flashback in RMAN.

 

  1. Logon to server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Thu Jan  5 10:45:29 EST 2015 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$

 

  1. Start RMAN and connect to your database.

 

[oracle@linux2 ~]$ rman

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Jan 10 09:18:41 2015

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

RMAN> connect target /

connected to target database: ORCL (DBID=1457738340)

RMAN>

 

  1. Use timestamp_to_scn function to get SCN for 48 hours ago.

 

RMAN> select timestamp_to_scn(systimestamp-2) from dual;

TIMESTAMP_TO_SCN(SYSTIMESTAMP-2)

——————————–

                         4236799

RMAN>

 

  1. Shutdown the database with immediate option if open.

 

RMAN> shutdown immediate

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

RMAN>

 

  1. Startup the database with mount.

 

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     549453824 bytes

Fixed Size                     2926616 bytes

Variable Size                272631784 bytes

Database Buffers             268435456 bytes

Redo Buffers                   5459968 bytes

RMAN>

 

  1. Execute the FLASHBACK DATABASE command with SCN retrieved above.

 

flashback database to scn 4236799;

RMAN> flashback database to scn 4236799;

Starting flashback at 10-JAN-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=22 device type=DISK

 

starting media recovery

 

archived log for thread 1 with sequence 2761 is already on disk as file /u04/oradata/ORCL/flash_recovery_area/ORCL/archivelog/2015_01_08/o1_mf_1_2761_d74og7tj_.arc

media recovery complete, elapsed time: 00:00:02

Finished flashback at 10-JAN-17

 

RMAN>

 

  1. Open the database with command ‘ALTER DATABASE OPEN READ ONLY;’

 

RMAN> alter database open read only;

Statement processed

RMAN>

 

  1. If not errors, shutdown the database with immediate option.

 

RMAN> shutdown immediate

database closed

database dismounted

Oracle instance shut down

RMAN>

 

  1. Startup the database with mount option.

 

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

 

Total System Global Area     549453824 bytes

Fixed Size                     2926616 bytes

Variable Size                272631784 bytes

Database Buffers             268435456 bytes

Redo Buffers                   5459968 bytes

 

RMAN>

 

  1. Open the database with resetlogs option: ‘ALTER DATABASE OPEN RESETLOGS;’

 

RMAN> ALTER DATABASE OPEN RESETLOGS;

Statement processed

RMAN>

 

  1. You should take a full backup at the end of any recovery.
  2. This completes flashing back a database in RMAN.

 

Larry Catt

OCP

12c, redaction

12c New Features – Redaction Policies by username

Leave a comment

Oracle 12c RDBMS Redaction Policies allow the RDBMS Developer or DBA the ability to obscure data.  This prevention of viewing data can be defined for all users, specific users, environmental variables, or roles.   This procedure shows the use of 12c Redaction against a common table and restricts access to a specific user TEST1.

 

  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);

 commit;

 

  1. Now create a user VIEW1 with the following sql statements.

 

create user test1 identified by test default tablespace users

quota unlimited on users;

 grant resource to test1;

grant create session to test1;

 

  1. Now as the system user create a policy using the RDBMS_REDACTION package to redact the column SALARY in the table TEST.EMP. The key to restricting the viewing of SALARY column is the EXPRESSION clause.     If the EXPRESSION clause is TRUE, the redaction policy will be applied.   In this example, when the logon user equals TEST1, the redaction policy will be applied.

 

BEGIN

DBMS_REDACT.ADD_POLICY( object_schema => ‘test’,

object_name => ’emp’,

column_name => ‘salary’,

policy_name => ‘redact_salary’,

function_type => DBMS_REDACT.FULL,

expression => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”) = ”TEST1”’);

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      45000

Nancy                          Jones                          15-AUG-14      87000

Thomas                         Harris                         06-DEC-12      79000

 

SQL>

 

  1. Now logon as the user TEST1 and not that the select returns 0 values for the SALARY column. This will only be true for a user id referenced by the EXPRESSION clause unless those users have the EXAMPT REDACTION POLICY privilege.

 

SQL> connect test1/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. Now the column is protected from all users defined in the EXPRESSION clause unless they have exempt redaction policy privileges.

 

Larry Catt, OCP