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.

Golden gate replication when the redo logs are in ASM

In order for the extract process to read from the redo logs located on ASM diskgroup you need to specify a connect string in the tnsnames.ora like the following.


ASM=
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
    )
  )

To test the connection run a remote connection sqlplus session

[oracle@localhost admin]$ sqlplus sys@asm as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 21 12:59:43 2014

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

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Automatic Storage Management option

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------

+ASM             STARTED

In the extract parameter file make an entry like following.

TRANLOGOPTIONS ASMUSER SYS@ASM, ASMPASSWORD XXXXX

There is another option to read from ASM .Some versions support the following option.

TRANLOGOPTIONS DBLOGREADER

ORA-12705: Cannot access NLS data files or invalid environment specified

After upgrading CRS from 10.2.0.3 to 11.2.0.3 started seeing the following issue while starting the ASM or DB using the srvctl utilitty.ASM starts fine using sqlplus.


ORA-12705: Cannot access NLS data files or invalid environment specified

To resolve the issue we changed the following entries.


1)$GRID_HOME/crs/install/s_crsconfig__env.txt

Changed #NLS_LANG=CRS_LANGUAGE_ID

to following

NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1



2)$GRID_HOME/crs/install/crsconfig_params, we commented

Changed

#LANGUAGE_ID=CRS_LANGUAGE_ID='AMERICAN_AMERICA.WE8ISO8859P1'

to following

LANGUAGE_ID=AMERICAN_AMERICA.WE8ISO8859P1
CRS_LANGUAGE_ID=AMERICAN_AMERICA.WE8ISO8859P1

Problem with "with clause" queries after upgrade to 11.2.0.3

This was the issue identified when we upgraded the database from 11.2.0.2 to 11.2.0.3 the performance of entire database was impacted.This was a datawarehouse database and there were lots of queries generated from OBIEE using the "with clause".To workaround the issue quickly we switched back the optimizer_features_enable parameter to 11.2.0.2. Now it came to identifying the issue with 11.2.0.3 optimizer.We ran 10053 trace on the problem query which was not performing well with optimizer_features_enable set to 11.2.0.3 and 11.2.0.2 and compared the two traces.It was found that with 11.2.0.3 the CBQT (cost based query transformation ) was not getting successful causing the issue
.Later it was identified to be an issue with bug fix 11740670.To solve the issue we did following

Alter system set "_fix_control"='11740670:OFF';
Alter system set optimizer_features_enable=11.2.0.3;

11.2.0.3 optimizer Query transformations (QT) ************************** CBQT: copy not possible on query block SEL$10 (#0) because linked to with clause CBQT bypassed for query block SEL$10 (#0): Cannot copy query block. CBQT: Validity checks failed for 98utkpdkpq0af. 11.2.0.2 optimizer Query transformations (QT)

**************************

JF: Checking validity of join factorization for query block SEL$1 (#0)

JF: Bypassed: not a UNION or UNION-ALL query block.

ST: not valid since new CBQT star transformation parameter is FALSE

TE: Checking validity of table expansion for query block SEL$1 (#0)

TE: Bypassed: table expansion disabled.

JF: Checking validity of join factorization for query block SEL$4 (#0)

JF: Bypassed: not a UNION or UNION-ALL query block.

ST: not valid since new CBQT star transformation parameter is FALSE

TE: Checking validity of table expansion for query block SEL$4 (#0)

TE: Bypassed: table expansion disabled.

CBQT: Validity checks passed for 98utkpdkpq0af.

ORA-15150: instance lock mode 'EXCLUSIVE' conflicts with other ASM instance(s)

This is a new standalone server using ASM and crs.The database is not clustered so cluster_database and RAC options are turned off for DB and ASM as expected.

SQL> startup nomount

ORA-15150: instance lock mode 'EXCLUSIVE' conflicts with other ASM instance(s)

We were able to start the database using srvctl start database -d command but not using sqlplus.

The cause of the problem was some parameter in the spfile used by the DB instance when were using sqlplus (I was suspecting it to be instance_number)

To resolve the issue we did the following.

Since the DB was able to start with using the srvctl command,we started it and created pfile from the spfile.Then we used this pfile to start  the instance using sqlplus.

This issue could be caused by some other  reasons in the cluster environment which could be due to following.

• Cluster_database parameter is set to false in ASM/database
• RAC option is not turned on.To turn on RAC option use the following oracle note

How to Check Whether Oracle Binary/Instance is RAC Enabled and Relink Oracle Binary in RAC (Doc ID 284785.1)  

Unidirectional Goldengate replication 12c on same host and same pluggable database

After release of goldengate 12c we hear two terms in  GG replication integrated and classic replication .In short Integrated replication is applicable for the version 12c database and classic is for the older database versions like 11.2.0.4.I have configured a simple setup to test the replication.
Integrated replicat is tightly integrated to oracle database.Some of the features of oracle streams are also taken into consideration that's why we see the apply process in the integrated replicat which is coming from the streams.

Environment:
Container database:cdbfs
Pluggable database:pdb1
Replication between schema src to schema tgt on the same pluggable database pdb1 


1) First we need to configure a golden-gate capture user and assign it the necessary privileges.This user has to be on the root or container database.

CREATE USER c##ggadm IDENTIFIED BY oracle container= ALL;

grant dba to c##ggadm container=all;

exec dbms_goldengate_auth.grant_admin_privileges('c##ggadm','capture', grant_optional_privilege=>'*') 


2) Log in to SQL*Plus as a user with ALTER SYSTEM privilege.

Issue the following command to determine whether the database is in supplemental logging mode and in forced logging mode. If the result is YES for both queries, the database meets the Oracle GoldenGate requirement.

SELECT supplemental_log_data_min, force_logging FROM v$database;

If the result is NO for either or both properties, continue with these steps to enable them as needed:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE FORCE LOGGING;

Issue the following command to verify that these properties are now enabled.

SELECT supplemental_log_data_min, force_logging FROM v$database;

The output of the query must be YES for both properties.

Switch the log files.

SQL> ALTER SYSTEM SWITCH LOGFILE;

3)Adjust the streams pool size as per the requirement.

alter system set streams_pool_size=25m;

4)Create the extract process.One extract process can capture the changes from multiple pluggable databases so you can specify the multiple PDBs with the help of "sourcecatalog" parameter in the param file.I have specified pdb1.

GGSCI (localhost.localdomain) 4> add extract ext1,tranlog,begin now

EXTRACT added.

• Register the extract with the pluggable database.This is new in 12c and you will have to login first to the container database to do that

dblogin userid c##ggadm password oracle

GGSCI (localhost.localdomain) 16> REGISTER EXTRACT ext1 database CONTAINER (pdb1)

Extract EXT1 successfully registered with database at SCN 2170346.

5)Add extract trail and modify the parameter file.

GGSCI (localhost.localdomain) 7> add exttrail /home/oracle/gg/dirdat/ex,extract ext1

EXTTRAIL added.

GGSCI (localhost.localdomain) 9> edit param ext1

extract EXT1

USERID C##ggadm@cdb_fs, PASSWORD oracle

LOGALLSUPCOLS

UPDATERECORDFORMAT COMPACT

DDL INCLUDE MAPPED SOURCECATALOG pdb1

exttrail /home/oracle/gg/dirdat/ex

SOURCECATALOG pdb1

TABLE SRC.*;

6)Add the pump process and modify the parameter file

 ADD EXTRACT EPUMP, EXTTRAILSOURCE /home/oracle/gg/dirdat/ex, begin now

ADD RMTTRAIL /home/oracle/gg/dirdat/rt, EXTRACT EPUMP

EXTRACT EPUMP

PASSTHRU

RMTHOST localhost,MGRPORT 7809

RMTTRAIL /home/oracle/gg/dirdat/rt

SOURCECATALOG pdb1

TABLE SRC.*;

7)Add the replicat process 

add checkpointtable pdb1.c##ggadm.ctable

dblogin userid c##ggadm password oracle

ADD REPLICAT REP1, EXTTRAIL /home/oracle/gg/dirdat/ex, CHECKPOINTTABLE c##ggadm.ctable

register replicat rep1 database

edit param rep1

REPLICAT REP1
USERID C##ggadm@pdb1, PASSWORD oracle
ASSUMETARGETDEFS
DISCARDFILE /home/oracle/gg/dirrpt/rep1.dsc,APPEND
DDLOPTIONS REPORT
HANDLECOLLISIONS
APPLYNOOPUPDATES
DDL
SOURCECATALOG pdb1
MAP SRC.*, TARGET TGT.*;

GGSCI (localhost.localdomain) 10> start *

GGSCI (localhost.localdomain) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     EPUMP       00:00:00      00:00:09

EXTRACT     RUNNING     EXT1        00:00:06      00:00:05

REPLICAT    RUNNING     REP1        00:00:00      00:00:09

8)Testing

I am creating a table t1 in the src schema and populating it with some values.

[oracle@localhost gg]$ sqlplus src/oracle@pdb1

SQL*Plus: Release 12.1.0.1.0 Production on Tue Dec 10 09:10:57 2013

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

Last Successful login time: Wed Dec 04 2013 15:26:51 -07:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table t1(c1 number);

Table created.

SQL> insert into t1 values('&val');

Enter value for val: 2

old   1: insert into t1 values('&val')

new   1: insert into t1 values('2')

1 row created.

SQL> /

Enter value for val: 3

old   1: insert into t1 values('&val')

new   1: insert into t1 values('3')

1 row created.

SQL> /

Enter value for val: 4

old   1: insert into t1 values('&val')

new   1: insert into t1 values('4')

1 row created.

SQL> commit;

The same table gets populated into the tgt schema as shown below.

[oracle@localhost ~]$ sqlplus tgt/oracle@pdb1

SQL*Plus: Release 12.1.0.1.0 Production on Tue Dec 10 09:12:12 2013

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select table_name from user_tables;

TABLE_NAME

--------------------------------------------------------------------------------

T1

SQL> select * from t1;

        C1

----------

         2

         3

         4