Daily Archives: June 11, 2016

12c, audit

Oracle 12c – Migrate to unified auditing

Leave a comment

When upgrading to 12c the unified auditing is not enabled.  The audit processes of the upgraded database will be used.   Now newly installed databases will use the mixed use unified auditing by default.  In upgrade, you must migrate the database to unified auditing to disable traditional auditing.   The following procedure does this.

 

  1. Logon as SYSDBA
  2. Determine if database is already in unified auditing with the following statement. TRUE indicates no migration is necessary.

select VALUE from V$OPTION where PARAMETER = ‘Unified Auditing’;

  1. Shutdown the database
  2. Stop the listener.
  3. Change directory to $ORACLE_HOME/rdbms/lib
  4. Enable unified auditing executable with unix command:

make –f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORALE_HOME

In Windows:

Rename file %ORACLE_HJOME%/bin/oauniaud12dll.dbl to %ORACLE_HJOME%/bin/oauniaud12dll

  1. Restart listener.
  2. Restart the database.

 

Larry Catt

OCP

12c, flashback

Oracle 12c – Flashback Table

Leave a comment

By default when a table is dropped oracle does not immediately remove the space, but renames it and places the table and associated objects in recycle bin which allows it to be recovered at a later time.  The recycle bin is a data dictionary table that contains info needed to recover dropped tables.  Original table stays in place and occupies same space until purged or database needs space for storage.  Tablespaces are not placed in recycle bin and once dropped are gone.   All tables in that tablespace are removed from recycle bin if they have previously been dropped.   Recycle bin can be disabled with recyclebin initialization parameter.   To recover a dropped table from the recyclebin the command ‘FLASHBACK TABLE ….  TO BEFORE DROP; ‘is used.  You must use the system generated recyclebin name.  You can rename the table with RENAME clause during recovery.  The USER_RECYCLEBIN view can be used to find the system generated recyclebin name.  This article demonstrates the use of flashback table.

 

  1. Logon to SQLPLUS as the user scott.

 

[oracle@linux2 ~]$ scott/password

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 6 08:25:35 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>

 

 

  1. Create table fb_test.

 

create table fb_test(fname varchar2(30), amt number);

SQL> create table fb_test(fname varchar2(30), amt number);

Table created.

SQL>

 

 

  1. Perform insert into test table fb_test and commit;

 

insert into scott.fb_test(fname,amt) values(‘Larry’,111);

commit;

SQL> insert into scott.fb_test(fname,amt) values(‘Larry’,111);

commit;

1 row created.

SQL>

Commit complete.

SQL>

 

 

  1. Select the values from test table fb_test.

 

SQL> select * from fb_test;

FNAME                                 AMT

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

Larry                                 111

SQL>

 

  1. Drop the test table fb_test.

 

SQL> drop table fb_test;

Table dropped.

SQL>

 

 

  1. Attempt a select from table fb_test and see the table does not exist.

 

SQL>  select * from fb_test;

 select * from fb_test

               *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

 

 

 

  1. Flashback the dropped table.

 

SQL> flashback table fb_test to before drop;

Flashback complete.

SQL>

 

  1. Perform select operation on table fb_test.

 

SQL> select * from fb_test;

FNAME                                 AMT

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

Larry                                 111

SQL>

 

  1. This completes flashback to table.

 

 

Larry Catt

OCP