Can you take the RMAN backup of catalog database using the same catalog database ??

This is very interesting question.Can you take the backup of rman catalog database by connecting to the same database.The document suggest that the catalog database is same as any other database and the restore and recovery operations are also the same .So I thought of running a test to confirm.

1) Create tablespace to keep the recovery catalog schema

SQL> create tablespace rman datafile  '/u01/app/oracle/oradata/fsdb/rman01.dbs' size 10m autoextend on ;

Tablespace created.

2) Create the recovery catalog schema

SQL> CREATE USER rman IDENTIFIED BY XXXXX  DEFAULT TABLESPACE rman TEMPORARY TABLESPACE temp  QUOTA UNLIMITED ON rman;

User created.

SQL> GRANT CONNECT, RECOVERY_CATALOG_OWNER TO rman;

Grant succeeded.

3) Create catalog using rman command line

[oracle@localhost ~]$ rman catalog rman/password@fsdb

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 27 15:01:01 2014

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

connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

4) Register the  same database to the catalog

[oracle@localhost ~]$ rman target / catalog rman/password@fsdb

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Aug 27 15:02:59 2014

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

connected to target database: FSDB (DBID=1085674687)

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

5)Run rman L0 backup to check

RMAN> backup incremental level 0 database;

Starting backup at 27-AUG-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=55 device type=DISK

channel ORA_DISK_1: starting incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

.

.

.

.

Finished backup at 27-AUG-14

Starting Control File and SPFILE Autobackup at 27-AUG-14

piece handle=/u01/app/oracle/flash_recovery_area/FSDB/autobackup/2014_08_27/o1_mf_s_856710457_9zwl4vls_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 27-AUG-14

Seems like its working but still I feel that we should backup the  control file and spfile seperately even though the spfile and control file autobackup is on.

Fine grained auditing (FGA) : Use AND,OR operators in the audit condition using funtions and avoid ORA-28138

The issue here is that we want to use the "and" operator  as the auditing condition to compare two values  of the columns with fine grained auditing or FGA. But If  I do something like the following.I get error ORA-28138.

begin
 DBMS_FGA.ADD_POLICY (
       object_schema => 'hr',
       object_name => 'fg_test',
       policy_name => 'test_users_fga_policy',
       audit_column => 'C1, C2', 
       statement_types => 'SELECT,INSERT,UPDATE,DELETE',
       audit_condition => 'C1=35 AND C2= 45',
       audit_column_opts => DBMS_FGA.ANY_COLUMNS,
       audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED );
end;
/

 

SQL> insert into fg_test values('35','45');

insert into fg_test values('35','45')

            *

ERROR at line 1:

ORA-28138: Error in Policy Predicate

As per the oracle documentation you cannot use a complex condition in the FGA policies. See the documentation:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_fga.htm#CDEIECAG

"The audit_condition must be a Boolean expression that can be evaluated using the values in the row being inserted, updated, or deleted. The expression can also use functions, such as the USER or SYS_CONTEXT functions.

The expression must not combine conditions using operators such as AND and OR. audit_condition can be NULL (or omitted), which is interpreted as TRUE, but it cannot contain the following elements:"

So to fix the problem we need to create a function that returns a Boolean value .See the following test case.

   Environment:OEL 5 64 bit,DB 11.2.0.1 
1) Create Table

SQL> create table hr.fg_test(c1 number,c2 number);

Table created.

2) Create function

create or replace function hr.cond_fg(col1 number ,col2 number ) return number is
begin
if (col1 =35 and col2=45)
then
return 1;
else
return 0;
end if;
end;
/


3) Add Policy
exec DBMS_FGA.drop_policy ( -
       object_schema => 'hr', -
       object_name => 'fg_test', -
       policy_name => 'test_users_fga_policy');

begin
 DBMS_FGA.ADD_POLICY (
       object_schema => 'hr',
       object_name => 'fg_test',
       policy_name => 'test_users_fga_policy',
       audit_column => 'C1, C2', 
       statement_types => 'SELECT,INSERT,UPDATE,DELETE',
       audit_condition => 'hr.cond_fg(C1,C2)=1',
       audit_column_opts => DBMS_FGA.ANY_COLUMNS,
       audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED );
end;
/


4)Enable Policy

 begin
   DBMS_FGA.ENABLE_POLICY (
   object_schema    =>  'HR',
   object_name      =>  'FG_TEST',
   policy_name      =>  'test_users_fga_policy',
   enable           =>   TRUE);
   end;
   /


SQL> select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, POLICY_TEXT,
POLICY_COLUMN, ENABLED, SEL, INS, UPD, DEL
from DBA_AUDIT_POLICIES ;  2    3

OBJECT_SCHEMA                  OBJECT_NAME
------------------------------ ------------------------------
POLICY_NAME
------------------------------
POLICY_TEXT
--------------------------------------------------------------------------------
POLICY_COLUMN                  ENA SEL INS UPD DEL
------------------------------ --- --- --- --- ---
HR                             FG_TEST
TEST_USERS_FGA_POLICY
hr.cond_fg(C1,C2)=1
C1                             YES YES YES YES YES

5) Run the test

As HR User
===========================================
SQL>  insert into fg_test values('&a','&b');
Enter value for a: 35
Enter value for b: 45
old   1:  insert into fg_test values('&a','&b')
new   1:  insert into fg_test values('35','45')

1 row created.

SQL> commit;

Commit complete.


As SYS User
==========================================================================================
SQL> select db_user,sql_text from dba_fga_audit_trail;

DB_USER
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
HR
 insert into fg_test values('35','45')
 



It seems to be working but as per the guidelines the function should not be too complex to avoid the performance issues.