ContractOracle

Subscribe to ContractOracle feed
Contract Oracle Limited is not affiliated to Oracle Corporation.
Updated: 49 min 59 sec ago

Oracle 12c - need to start container AND pluggable databases.

Thu, 2013-06-27 01:56
In Oracle 12c we start the Container Database (CDB) the same as we started previous database versions.

[oracle@rac1 lib]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 14:47:35 2013

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2289016 bytes
Variable Size             989856392 bytes
Database Buffers          654311424 bytes
Redo Buffers                7061504 bytes
Database mounted.
Database opened.

But then we find that the Pluggable Databases (PDBs) are still in "MOUNTED" state, so we will need to open them before we can login.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED
PDB2                           MOUNTED
PDB3                           MOUNTED

From CDB$ROOT we can manage any PDB.  

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

To open one PDB :-

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

To open ALL PDBS :-

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
PDB3                           READ WRITE

Or we can move down to a PDB container to stop and start them individually.

SQL> alter session set container=PDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> shutdown;
Pluggable Database closed.
SQL> startup;
Pluggable Database opened.


Startup of PDBs can be automated using a startup trigger.

SQL> create or replace trigger Sys.After_Startup
                          after startup on database
begin
   execute immediate 'alter pluggable database all open';
end;
/  

Trigger created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1636814848 bytes
Fixed Size                  2288968 bytes
Variable Size             973079224 bytes
Database Buffers          654311424 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
PDB3                           READ WRITE

Categories: DBA Blogs

Oracle 12c Limitations to RESOURCE, SELECT ANY DICTIONARY

Thu, 2013-06-27 01:38
Oracle 12c has implemented a few improvements to the existing system privileges.  

Dictionary tables containing password hashes (DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$XS$VERIFIERSare no longer included in the SELECT ANY DICTIONARY system privilege.  This makes it safer to give developers access to dictionary tables for tuning and debugging, without giving them the chance to run brute force attacks ...

Unlimited Tablespace is no longer included in the RESOURCE role. This should reduce the number of times developers create segments in SYSTEM tablespace ....

SQL> grant select any dictionary to god;

Grant succeeded.

SQL> grant resource to god;

Grant succeeded.

SQL> connect god/god@T12P1
Connected.
SQL> select password from user$;
select password from user$
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select default_tablespace from dba_users where username = 'GOD';

DEFAULT_TABLESPACE
------------------------------
USERS

SQL> create table test(id integer) tablespace system;

Table created.

SQL> insert into test values (1);
insert into test values (1)
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'

Categories: DBA Blogs

Oracle 12c New Features - Data Redaction via DBMS_REDACT

Wed, 2013-06-26 21:55
Oracle 12c has added functionality to redact data in specific columns depending on a policy configured by the DBMS_REDACT package.  

First create table EMPLOYEE in schema TEST and grant select to GOD and NOBODY.

SQL> connect test/test@T12P1
Connected.
SQL> create table employee (emp_id integer primary key, emp_name varchar2(10), salary number);

Table created.

SQL> grant select on employee to god;

Grant succeeded.

SQL> grant select on employee to nobody;

Grant succeeded.

SQL> insert into employee (emp_id, emp_name, salary) values (1,'John',100000);

1 row created.

SQL> insert into employee (emp_id, emp_name, salary) values (2,'Ben',80000);

1 row created.

SQL> commit;

Commit complete.

Now we create the data redaction policy using DBMS_REDACT.ADD_POLICY.  Only someone logged in as GOD should be allowed to see the values stored in TEST.EMPLOYEE.SALARY.  In this case we are using function type FULL which obscures all data, but other options include PARTIAL, RANDOM, REGEXP.

SQL> connect system/password@T12P1
Connected.
SQL> BEGIN
  2  DBMS_REDACT.add_policy(object_schema => 'TEST'
  3  ,object_name => 'EMPLOYEE'
  4  ,policy_name => 'Salary Redaction'
  5  ,expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''GOD'' OR SYS_CONTEXT(''USERENV'',''SESSION_USER'') IS NULL'
  6  ,column_name => 'SALARY'
  7  ,function_type => dbms_redact.FULL
  8  );
  9  END;
 10  /

PL/SQL procedure successfully completed.

When we are logged in as nobody the salary column is redacted.

SQL> connect nobody/nobody@T12P1
Connected.
SQL> select * from test.employee;

              EMP_ID EMP_NAME                 SALARY
-------------------- ---------- --------------------
                   1 John                          0
                   2 Ben                           0

But when we are logged in as god we can see the data.

SQL> connect god/god@T12P1
Connected.
SQL> select * from test.employee;

              EMP_ID EMP_NAME                 SALARY
-------------------- ---------- --------------------
                   1 John                     100000
                   2 Ben                       80000

Note that redaction policies do NOT apply to users with the EXEMPT REDACTION POLICY system privilege, which by default is granted via EXP_FULL_DATABASE and DBA roles.  This means that by default DBAs will still have access to redacted data.

SQL> connect system/password@T12P1
Connected.
SQL> select * from test.employee;

              EMP_ID EMP_NAME                 SALARY
-------------------- ---------- --------------------
                   1 John                     100000
                   2 Ben                       80000

SQL> select grantee, privilege from dba_sys_privs where privilege = 'EXEMPT REDACTION POLICY';

GRANTEE
-----------------------------------------------------------------
EXP_FULL_DATABASE

SQL> select grantee from dba_role_privs where granted_role = 'EXP_FULL_DATABASE';

GRANTEE
-----------------------------------------------------------------
SYS
DATAPUMP_EXP_FULL_DATABASE
DBA
DATAPUMP_IMP_FULL_DATABASE







Categories: DBA Blogs

Oracle 12c New Features - In Database Row Archiving

Wed, 2013-06-26 20:57
Oracle 12c has a new feature called In Database Row Archiving.  Instead of deleting old records they can now be marked as archived, be invisible to running applications, but remain in the original table in case they need to be restored at a later date.  This should reduce the need for DBAs to restore old backups, and should keep auditors happy.

First create a test table.

SQL> create table employee (emp_id integer primary key, emp_name varchar2(10), archive_date date);

Table created.

SQL> insert into employee (emp_id, emp_name) values (1,'John');

1 row created.

SQL> insert into employee (emp_id, emp_name) values (2,'Ben');

1 row created.

SQL> commit;

Commit complete.

Now enable archival for the table.

SQL> alter table employee row archival;

Table altered.

We can see from hidden attribute ORA_ARCHIVE_STATE=0 that the records are not archived.

SQL> select * from employee;

EMP_ID EMP_NAME   ARCHIVE_D
------ ---------- ---------
     1 John
     2 Ben

SQL> select emp_id, emp_name, ora_archive_state from employee;

EMP_ID EMP_NAME   ORA_ARCHIVE_STATE
------ ---------- -----------------------------------------------
     1 John       0
     2 Ben        0

Now we want to archive the record for employee Ben (he resigned).

SQL> update employee
set ora_archive_state=dbms_ilm.archivestatename(1), archive_date=sysdate
where emp_id=2; 

1 row updated.

SQL> commit;

Commit complete.

The record record for Ben is no longer visible to normal select operations.

SQL> select * from employee;

EMP_ID EMP_NAME   ARCHIVE_D
------ ---------- ---------
     1 John

But if we set row archive visibility=all then we can see that the record still exists in the table.  

SQL> alter session set row archival visibility = all;

Session altered.

SQL> select emp_id, emp_name, ora_archive_state from employee;

EMP_ID EMP_NAME   ORA_ARCHIVE_STATE
------ ---------- -----------------------------------------------
     1 John       0
     2 Ben        1


SQL> alter session set row archival visibility = active;

Session altered.

And while the record is invisible to the application, it is still considered by constraints.

SQL> select * from employee;

EMP_ID EMP_NAME   ARCHIVE_D
------ ---------- ---------
     1 John

SQL> insert into employee (emp_id, emp_name) values (2,'Ben');
insert into employee (emp_id, emp_name) values (2,'Ben')
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.SYS_C009859) violated

And if we re-hire Ben, the record can be brought back from archive.

SQL> alter session set row archival visibility = all;

Session altered.

SQL> update employee
set ora_archive_state=dbms_ilm.archivestatename(0), archive_date=null
where emp_id=2; 

1 row updated.

SQL> commit;

Commit complete.

SQL> alter session set row archival visibility = active;

Session altered.

SQL> select * from employee;

EMP_ID EMP_NAME   ARCHIVE_D
------ ---------- ---------
     1 John
     2 Ben


Categories: DBA Blogs

Oracle 12c New Features - Partial Indexing on Partitioned Tables

Wed, 2013-06-26 03:42
Oracle 12c allows DBAs to set specific table partitions "INDEXING OFF" and create indexes with "INDEXING PARTIAL".  This means that index partitions won't be created for the specified table partitions.  This allows DBAs to have multiple indexing strategies for different partitions, or to rapidly create indexes on specific partitions.

Example :- create a partitioned table :-

SQL> CREATE TABLE test_range
(id  NUMBER(5),
att1 char(1),
att2 char(1),
att3 char(1))
PARTITION BY RANGE(id)
(
PARTITION id_10 VALUES LESS THAN(10),
PARTITION id_20 VALUES LESS THAN(20)
);

Table created.

Create a local index.  By default it will create index partitions for every table partition.

SQL> create index test_index1 on test_range(att1) local;

Index created.

Now set indexing off for one partition, and create a second index with partial indexing enabled.

SQL> alter table test_range modify partition id_20 indexing off;

Table altered.

SQL> create index test_index2 on test_range(att2) local indexing partial;

Index created.

Insert to create the segments.

SQL> insert into test_range values (1,'a','a','a');

1 row created.

SQL> insert into test_range values (11,'b','b','b');

1 row created.

SQL> commit;

Commit complete.

And we can see that for index TEST_INDEX2 with PARTIAL INDEXING set, no segment was created for table partition ID_20 with INDEXING OFF.

SQL> select index_name, partition_name, segment_created from dba_ind_partitions where index_owner = 'TEST';

INDEX_NAME
--------------------------------------------------------------------------------
PARTITION_NAME
--------------------------------------------------------------------------------
SEGMENT_CREATED
-------------------------
TEST_INDEX1
ID_10
YES

TEST_INDEX1
ID_20
YES

TEST_INDEX2
ID_10
YES

TEST_INDEX2
ID_20
NO

SQL> select segment_name, segment_type, count(*) from dba_segments where owner = 'TEST' group by segment_name, segment_type;

SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE         COUNT(*)
------------------ ----------
TEST_RANGE
TABLE PARTITION             2

TEST_INDEX1
INDEX PARTITION             2

TEST_INDEX2
INDEX PARTITION             1


Categories: DBA Blogs

Pages