ORACLE DBA

DDL Replication and DDL mapping Restriction DEMO in oracle goldengate

noreply@blogger.com (Rajeshkumar Govindarajan) — Sun, 23 Dec 2018 01:17:00 +0530

The components that support the replication of DDL and the replication of transactional data changes (DML) are independent of each other. Therefore, you can synchronize:

just DDL changes
just DML changes
both DDL and DML

By default the DDL replication is disabled on the source database (extract side) but is enabled on the target Database (replicat side).
If you need to capture DDL from replicated process, then you need to configure the extract with GETREPLICATES.
Only supports Oracle Database to Oracle Database replications.
The source and target object definitions must be identical.
In Oracle database 12c, DDL replication does not require any setup of triggers as it is natively supported at the database level, you no longer need to run the SQL scripts such as marker_setup.sql and ddl_setup.sql to set up the DDL replication.

Configure Extract Process with DDL Replication

For learning purpose, I have configured both EXTRACT and REPLICAT in same server same database ggtest1
Adding “DDL INCLUDE MAPPED” enables extracting the DDL
Don’t forget to add DDL INCLUDE MAPPED in the Pump and Replicat processes.

Sample :- Extract parameter file and REPLICAT parameter file

GGSCI (rac1.localdomain) 10> view params ex1

EXTRACT ex1
userid ogguser@ggtest1, password oracle
TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 4194304
DISCARDFILE /home/oracle/gghome/dirrpt/ex1.dsc, APPEND, MEGABYTES 50
EXTTRAIL /home/oracle/gghome/dirdat/ex
DDL INCLUDE MAPPED
TABLE us01.*;

GGSCI (rac1.localdomain) 11> view params rep2

REPLICAT rep2
USERID ogguser@ggtest1, PASSWORD oracle
DISCARDFILE /home/oracle/gghome/dirrpt/rep2.dsc, APPEND, MEGABYTES 90
HANDLECOLLISIONS
ASSUMETARGETDEFS
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
MAP us01.*, TARGET demo.*;

GGSCI (rac1.localdomain) 21> start ex1

Sending START request to MANAGER ...

EXTRACT EX1 starting

GGSCI (rac1.localdomain) 22> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EX1 00:00:00 00:00:01

REPLICAT STOPPED REP2 00:00:00 00:04:04

GGSCI (rac1.localdomain) 23> start rep2

Sending START request to MANAGER ...

REPLICAT REP2 starting

GGSCI (rac1.localdomain) 25> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EX1 00:00:00 00:00:00

REPLICAT RUNNING REP2 00:00:00 00:00:00

Testing by adding table and add, modify, drop column in source schema database.

ON SOURCE SCHEMA US01

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

TB2 TABLE

TB1 TABLE

SQL> show user

USER is "US01"

SQL> create table tb3 as select * from tb1;

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

TB1 TABLE

TB2 TABLE

TB3 TABLE

on Target Schema DEMO, We can see the data replication

SQL> col tname for a15

SQL> col tabtype for a15

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

TB1 TABLE

TB2 TABLE

SQL> show user

USER is "DEMO"

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

TB1 TABLE

TB2 TABLE

TB3 TABLE --> Table created in DEMO schema

ON SOURCE SCHEMA US01

SQL> select count(*) from tb3;

COUNT(*)

----------

SQL> set time on

20:42:57 SQL>

20:43:08 SQL> truncate table tb3;

Table truncated.

20:43:14 SQL> select count(*) from tb3;

COUNT(*)

----------

on Target Schema DEMO, We can see the data replication

SQL> set time on

20:42:54 SQL> 

20:43:25 SQL> select count(*) from tb3;

  COUNT(*)

----------

  0  (After truncate statement executed on US01 schema replicated in demo)

ON SOURCE SCHEMA US01

20:43:22 SQL> insert into tb3 select * from tb1;

6 rows created.

20:43:48 SQL> commit;

Commit complete.

20:43:54 SQL> select count(*) from tb3;

COUNT(*)

----------

on Target Schema DEMO, We can see the replication

20:44:08 SQL> select count(*) from tb3;

  COUNT(*)

----------

  6 --> Data replicated in demo schema after insert statement in US01

ON SOURCE SCHEMA US01

20:44:20 SQL> desc tb3

Name Null? Type

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

ID NUMBER

NAME VARCHAR2(20)

DEPT VARCHAR2(20)

20:48:04 SQL> alter table tb3 add (Address varchar2(20));

Table altered.

20:48:54 SQL> desc tb3

Name Null? Type

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

ID NUMBER

NAME VARCHAR2(20)

DEPT VARCHAR2(20)

ADDRESS VARCHAR2(20)

on Target Schema DEMO, We can see the replication

20:44:23 SQL> desc tb3

 Name        Null?    Type

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

 ID          NUMBER

 NAME          VARCHAR2(20)

 DEPT          VARCHAR2(20)

20:49:07 SQL> desc tb3

 Name        Null?    Type

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

 ID          NUMBER

 NAME          VARCHAR2(20)

 DEPT          VARCHAR2(20)

 ADDRESS         VARCHAR2(20) -->ADDRESS COLUMN added in replicat side

ON SOURCE SCHEMA US01

20:49:12 SQL> alter table tb3 drop column address;

Table altered.

20:49:41 SQL> desc tb3

Name Null? Type

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

ID NUMBER

NAME VARCHAR2(20)

DEPT VARCHAR2(20)

on Target Schema DEMO, We can see the replication

20:49:19 SQL> desc tb3

 Name        Null?    Type

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

 ID          NUMBER

 NAME          VARCHAR2(20)

 DEPT          VARCHAR2(20)

 COLUMN address dropped in demo schema TB3 Table

ON SOURCE SCHEMA US01

20:49:46 SQL> alter table tb3 modify (dept varchar2(40));

Table altered.

20:50:18 SQL> desc tb3

Name Null? Type

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

ID NUMBER

NAME VARCHAR2(20)

DEPT VARCHAR2(40)

on Target Schema DEMO, We can see the replication

20:49:53 SQL> desc tb3

 Name        Null?    Type

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

 ID          NUMBER

 NAME          VARCHAR2(20)

 DEPT          VARCHAR2(40)

Column DEPT modified from varchar2(20) to Varchar(40) 

ON SOURCE SCHEMA US01

20:50:23 SQL> drop table tb3 purge;

Table dropped.

20:50:49 SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

TB1 TABLE

TB2 TABLE

on Target Schema DEMO, We can see the replication

20:50:28 SQL> select * from tab;

TNAME  TABTYPE   CLUSTERID

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

TB2  TABLE

TB1  TABLE

Table TB3 dropped in DEMO Schema. 

Below is the DEMO of DDL replication restricting example if you do not want to drop statement replication we can restrict and allow set of DDL replication as shown below. 

Below is demo to DDL replication of only CREATE TABLE statement

GGSCI (rac1.localdomain) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

EXTRACT     RUNNING     EX1         00:00:00      00:00:09    

REPLICAT    RUNNING     REP2        00:00:00      00:00:01    

GGSCI (rac1.localdomain) 12> stop rep2

Sending STOP request to REPLICAT REP2 ...

Request processed.

GGSCI (rac1.localdomain) 13> stop ex1

Sending STOP request to EXTRACT EX1 ...

Request processed.

GGSCI (rac1.localdomain) 14> info all 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

EXTRACT     STOPPED     EX1         00:00:00      00:00:05    

REPLICAT    STOPPED     REP2        00:00:00      00:00:10    

GGSCI (rac1.localdomain) 16> edit params ex1

GGSCI (rac1.localdomain) 17> edit params rep2

GGSCI (rac1.localdomain) 18> edit params ex1

GGSCI (rac1.localdomain) 19> view params ex1

EXTRACT ex1

userid ogguser@ggtest1, password oracle

TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 4194304

DISCARDFILE /home/oracle/gghome/dirrpt/ex1.dsc, APPEND, MEGABYTES 50

EXTTRAIL /home/oracle/gghome/dirdat/ex

DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table'

TABLE us01.*;

GGSCI (rac1.localdomain) 20> view params rep2

REPLICAT rep2

USERID ogguser@ggtest1, PASSWORD oracle

DISCARDFILE /home/oracle/gghome/dirrpt/rep2.dsc, APPEND, MEGABYTES 90

HANDLECOLLISIONS

ASSUMETARGETDEFS

--DDL INCLUDE ALL

DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table'

DDLERROR DEFAULT IGNORE

MAP us01.*, TARGET demo.*;

GGSCI (rac1.localdomain) 21> start ex1

Sending START request to MANAGER ...

EXTRACT EX1 starting

GGSCI (rac1.localdomain) 23> start rep2

Sending START request to MANAGER ...

REPLICAT REP2 starting

GGSCI (rac1.localdomain) 25> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

EXTRACT     RUNNING     EX1         00:00:00      00:00:00    

REPLICAT    RUNNING     REP2        00:00:00      00:00:00    

ON SOURCE SCHEMA US01

20:53:35 SQL> create table tb3 as select * from tb1;

Table created.

on Target Schema DEMO

21:19:28 SQL> /

TNAME  TABTYPE   CLUSTERID

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

TB3  TABLE

TB2  TABLE

TB1  TABLE

ON SOURCE SCHEMA US01

21:19:22 SQL> desc us01.TB3

 Name        Null?    Type

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

 ID          NUMBER

 NAME          VARCHAR2(20)

 DEPT          VARCHAR2(20)

21:19:42 SQL> alter table tb3 add (address varchar2(20));

Table altered.

21:20:12 SQL> desc US01.tb3

 Name        Null?    Type

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

 ID          NUMBER

 NAME          VARCHAR2(20)

 DEPT          VARCHAR2(20)

 ADDRESS         VARCHAR2(20)

on Target Schema DEMO

21:20:20 SQL> DESC DEMO.TB3

 Name        Null?    Type

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

 ID          NUMBER

 NAME          VARCHAR2(20)

 DEPT          VARCHAR2(20)

added ADDRESS COLUMN not replicated due to mapping restriction

ALTER statement not replicated

ON SOURCE SCHEMA US01

21:21:27 SQL> select * from tab;

TNAME  TABTYPE   CLUSTERID

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

TB1  TABLE

TB2  TABLE

TB3  TABLE

21:21:40 SQL> drop table tb3 purge;

Table dropped.

21:21:54 SQL> select * from tab;

TNAME  TABTYPE   CLUSTERID

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

TB1  TABLE

TB2  TABLE

on Target Schema DEMO

21:22:05 SQL> select * from tab;

TNAME  TABTYPE   CLUSTERID

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

TB1  TABLE

TB2  TABLE

TB3  TABLE --> Table still exists, DROP Statement not replicated due to mapping restriction. 

Sample mapping Extract and Replicat Parameter files to allow alter table, create index, alter index, create view, and alter view.

GGSCI (rac1.localdomain) 45> view params ex1

EXTRACT ex1

userid ogguser@ggtest1, password oracle

TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 4194304

DISCARDFILE /home/oracle/gghome/dirrpt/ex1.dsc, APPEND, MEGABYTES 50

EXTTRAIL /home/oracle/gghome/dirdat/ex

DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table' &

INCLUDE MAPPED OPTYPE alter OBJTYPE 'table' 

TABLE us01.*;

GGSCI (rac1.localdomain) 46> view params rep2

REPLICAT rep2

USERID ogguser@ggtest1, PASSWORD oracle

DISCARDFILE /home/oracle/gghome/dirrpt/rep2.dsc, APPEND, MEGABYTES 90

HANDLECOLLISIONS

ASSUMETARGETDEFS

--DDL INCLUDE ALL

DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table' &

INCLUDE MAPPED OPTYPE alter OBJTYPE 'table'

DDLERROR DEFAULT IGNORE

MAP us01.*, TARGET demo.*;

GGSCI (rac1.localdomain) 52> view params ex1

EXTRACT ex1

userid ogguser@ggtest1, password oracle

TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 4194304

DISCARDFILE /home/oracle/gghome/dirrpt/ex1.dsc, APPEND, MEGABYTES 50

EXTTRAIL /home/oracle/gghome/dirdat/ex

DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table' &

INCLUDE MAPPED OPTYPE alter OBJTYPE 'table' 

INCLUDE MAPPED OPTYPE create OBJTYPE 'index' &

INCLUDE MAPPED OPTYPE alter OBJTYPE 'index'

TABLE us01.*;

GGSCI (rac1.localdomain) 53> view params rep2

REPLICAT rep2

USERID ogguser@ggtest1, PASSWORD oracle

DISCARDFILE /home/oracle/gghome/dirrpt/rep2.dsc, APPEND, MEGABYTES 90

HANDLECOLLISIONS

ASSUMETARGETDEFS

--DDL INCLUDE ALL

DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table' &

INCLUDE MAPPED OPTYPE alter OBJTYPE 'table' &

INCLUDE MAPPED OPTYPE create OBJTYPE 'index' &

INCLUDE MAPPED OPTYPE alter OBJTYPE 'index'

DDLERROR DEFAULT IGNORE

MAP us01.*, TARGET demo.*;

GGSCI (rac1.localdomain) 61> view params ex1

EXTRACT ex1

userid ogguser@ggtest1, password oracle

TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 4194304

DISCARDFILE /home/oracle/gghome/dirrpt/ex1.dsc, APPEND, MEGABYTES 50

EXTTRAIL /home/oracle/gghome/dirdat/ex

DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table' &

INCLUDE MAPPED OPTYPE alter OBJTYPE 'table' &

INCLUDE MAPPED OPTYPE create OBJTYPE 'index' &

INCLUDE MAPPED OPTYPE alter OBJTYPE 'index' &

INCLUDE MAPPED OPTYPE create OBJTYPE 'view' &

INCLUDE MAPPED OPTYPE alter OBJTYPE 'view'

TABLE us01.*;

GGSCI (rac1.localdomain) 63> view params rep2

REPLICAT rep2

USERID ogguser@ggtest1, PASSWORD oracle

DISCARDFILE /home/oracle/gghome/dirrpt/rep2.dsc, APPEND, MEGABYTES 90

HANDLECOLLISIONS

ASSUMETARGETDEFS

--DDL INCLUDE ALL

DDL INCLUDE MAPPED OPTYPE create OBJTYPE 'table' &

INCLUDE MAPPED OPTYPE alter OBJTYPE 'table' &

INCLUDE MAPPED OPTYPE create OBJTYPE 'index' &

INCLUDE MAPPED OPTYPE alter OBJTYPE 'index' &

INCLUDE MAPPED OPTYPE create OBJTYPE 'view' &

INCLUDE MAPPED OPTYPE alter OBJTYPE 'view'

DDLERROR DEFAULT IGNORE

MAP us01.*, TARGET demo.*;

hope, this helps.

References:-

https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/ddl.htm#GIORA307

http://www.juliandyke.com/Research/GoldenGate/GoldenGateDDLReplication.php

https://www.perftuning.com/blog/ddl-mapping-oracle-goldengate-12-2/

http://www.vitalsofttech.com/configure-goldengate-ddl-replication/

https://jinyuwang.weebly.com/for-oracle-db/goldengate-121-new-feature-how-to-configure-ddl-replications-in-oracle-goldengate

Understanding RBA using demo by table data recover in goldengate environment

noreply@blogger.com (Rajeshkumar Govindarajan) — Sat, 22 Dec 2018 05:21:00 +0530

This is purely for understanding the RBA. please do not try this in production environment without proper understanding and knowledge of your goldengate replicat configuration.

reverting back the Replicat RBA will impact all the tables in replicat group. so be cautious.

Demo:-

below are the RBA noted for testing.

before insert

2018-12-22 00:34:34.316052 RBA 1486 --> got RBA from output > info rep1, detail

after 1st insert

2018-12-22 01:10:50.000000 RBA 2127

after 2nd insert

2018-12-22 01:14:09.000000 RBA 2264

SQL> conn us01/us01

Connected.

SQL> select * FROM TB1;

ID NAME DEPT

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

3 RAMESH IT

4 VIDHYA MEDICAL

1 DEEKSHI IT

2 PRITHVI IT

SQL> insert into tb1 values (5, 'PRIYA', 'MEDICAL');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> insert into tb1 values(6, 'SUBHA','IT');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>

[oracle@rac1]$ sqlplus demo/demo

SQL> truncate table tb1;

Table truncated.

SQL> select * from tb1;

no rows selected

GGSCI (rac1.localdomain) 7> info rep1

REPLICAT REP1 Last Started 2018-12-22 00:34 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:01 ago)

Process ID 13828

Log Read Checkpoint File /home/oracle/gghome/dirdat/ex000000002

2018-12-22 00:34:34.316052 RBA 1486

GGSCI (rac1.localdomain) 8> info rep1, detail

REPLICAT REP1 Last Started 2018-12-22 00:34 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:06 ago)

Process ID 13828

Log Read Checkpoint File /home/oracle/gghome/dirdat/ex000000002

2018-12-22 00:34:34.316052 RBA 1486

Current Log BSN value: (requires database login)

Last Committed Transaction CSN value: (requires database login)

Extract Source Begin End

/home/oracle/gghome/dirdat/ex000000002 2018-12-21 01:14 2018-12-22 00:34

/home/oracle/gghome/dirdat/ex000000001 2018-12-21 01:01 2018-12-21 01:14

/home/oracle/gghome/dirdat/ex000000000 * Initialized * 2018-12-21 01:01

/home/oracle/gghome/dirdat/ex000000000 * Initialized * First Record

Current directory /home/oracle/gghome

Report file /home/oracle/gghome/dirrpt/REP1.rpt

Parameter file /home/oracle/gghome/dirprm/rep1.prm

Checkpoint file /home/oracle/gghome/dirchk/REP1.cpr

Checkpoint table ogguser.checkpointtable

Process file /home/oracle/gghome/dirpcs/REP1.pcr

Error log /home/oracle/gghome/ggserr.log

GGSCI (rac1.localdomain) 59> stop rep1

Sending STOP request to REPLICAT REP1 ...

Request processed.

Logdump 73 >open /home/oracle/gghome/dirdat/ex000000002

Current LogTrail is /home/oracle/gghome/dirdat/ex000000002

Logdump 87 >pos 2264

Reading in reverse from RBA 2264

Logdump 88 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x0c)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 32 (x0020) IO Time : 2018/12/22 01:14:09.000.000

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 56 AuditPos : 2091024

Continued : N (x00) RecCount : 1 (x01)

2018/12/22 01:14:09.000.000 Insert Len 32 RBA 2127

Name: US01.TB1 (TDR Index: 1)

After Image: Partition 12 G s

0000 0500 0000 0100 3601 0009 0000 0005 0053 5542 | ........6........SUB

4841 0200 0600 0000 0200 4954 | HA........IT

Logdump 89 >detail on

Logdump 90 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x0c)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 37 (x0025) IO Time : 2018/12/22 01:10:50.000.000

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 56 AuditPos : 1452560

Continued : N (x00) RecCount : 1 (x01)

2018/12/22 01:10:50.000.000 Insert Len 37 RBA 1980

Name: US01.TB1 (TDR Index: 1)

After Image: Partition 12 G s

0000 0500 0000 0100 3501 0009 0000 0005 0050 5249 | ........5........PRI

5941 0200 0b00 0000 0700 4d45 4449 4341 4c | YA........MEDICAL

Column 0 (x0000), Len 5 (x0005)

Column 1 (x0001), Len 9 (x0009)

Column 2 (x0002), Len 11 (x000b)

Logdump 91 >pos 2264

Reading in reverse from RBA 2264

Logdump 92 >usertoken detail

Logdump 93 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x0c)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 32 (x0020) IO Time : 2018/12/22 01:14:09.000.000

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 56 AuditPos : 2091024

Continued : N (x00) RecCount : 1 (x01)

2018/12/22 01:14:09.000.000 Insert Len 32 RBA 2127

Name: US01.TB1 (TDR Index: 1)

After Image: Partition 12 G s

0000 0500 0000 0100 3601 0009 0000 0005 0053 5542 | ........6........SUB

4841 0200 0600 0000 0200 4954 | HA........IT

Column 0 (x0000), Len 5 (x0005)

Column 1 (x0001), Len 9 (x0009)

Column 2 (x0002), Len 6 (x0006)

Logdump 94 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x0c)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 37 (x0025) IO Time : 2018/12/22 01:10:50.000.000

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 56 AuditPos : 1452560

Continued : N (x00) RecCount : 1 (x01)

2018/12/22 01:10:50.000.000 Insert Len 37 RBA 1980

Name: US01.TB1 (TDR Index: 1)

After Image: Partition 12 G s

0000 0500 0000 0100 3501 0009 0000 0005 0050 5249 | ........5........PRI

5941 0200 0b00 0000 0700 4d45 4449 4341 4c | YA........MEDICAL

Column 0 (x0000), Len 5 (x0005)

Column 1 (x0001), Len 9 (x0009)

Column 2 (x0002), Len 11 (x000b)

Logdump 95 >reclen 128

Reclen set to 128

Logdump 96 >pos 2264

Reading in reverse from RBA 2264

Logdump 97 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x0c)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 32 (x0020) IO Time : 2018/12/22 01:14:09.000.000

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 56 AuditPos : 2091024

Continued : N (x00) RecCount : 1 (x01)

2018/12/22 01:14:09.000.000 Insert Len 32 RBA 2127

Name: US01.TB1 (TDR Index: 1)

After Image: Partition 12 G s

0000 0500 0000 0100 3601 0009 0000 0005 0053 5542 | ........6........SUB

4841 0200 0600 0000 0200 4954 | HA........IT

Column 0 (x0000), Len 5 (x0005)

Column 1 (x0001), Len 9 (x0009)

Column 2 (x0002), Len 6 (x0006)

Logdump 98 >n

___________________________________________________________________

Hdr-Ind : E (x45) Partition : . (x0c)

UndoFlag : . (x00) BeforeAfter: A (x41)

RecLength : 37 (x0025) IO Time : 2018/12/22 01:10:50.000.000

IOType : 5 (x05) OrigNode : 255 (xff)

TransInd : . (x03) FormatType : R (x52)

SyskeyLen : 0 (x00) Incomplete : . (x00)

AuditRBA : 56 AuditPos : 1452560

Continued : N (x00) RecCount : 1 (x01)

2018/12/22 01:10:50.000.000 Insert Len 37 RBA 1980

Name: US01.TB1 (TDR Index: 1)

After Image: Partition 12 G s

0000 0500 0000 0100 3501 0009 0000 0005 0050 5249 | ........5........PRI

5941 0200 0b00 0000 0700 4d45 4449 4341 4c | YA........MEDICAL

Column 0 (x0000), Len 5 (x0005)

Column 1 (x0001), Len 9 (x0009)

Column 2 (x0002), Len 11 (x000b)

Logdump 99 >

GGSCI (rac1.localdomain) 60> alter replicat rep1, extrba 2127

2018-12-22 01:57:38 INFO OGG-06594 Replicat REP1 has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start REP1 with NOFILTERDUPTRANSACTIONS option.

REPLICAT altered.

GGSCI (rac1.localdomain) 61> start replicat rep1 NOFILTERDUPTRANSACTIONS

Sending START request to MANAGER ...

REPLICAT REP1 starting

GGSCI (rac1.localdomain) 62> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EX1 00:00:00 00:00:08

REPLICAT RUNNING REP1 00:00:00 00:00:05

SQL> /

ID NAME DEPT

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

6 SUBHA IT

SQL> truncate table tb1;

Table truncated.

Stop replicat again and alter replicat to begin with previous RBA and check the table data again.

GGSCI (rac1.localdomain) 53> stop replicat rep1

REPLICAT REP1 is already stopped.

GGSCI (rac1.localdomain) 54> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EX1 00:00:00 00:00:06

REPLICAT ABENDED REP1 00:00:00 00:00:27

GGSCI (rac1.localdomain) 55> alter replicat rep1, extrba 1980

2018-12-22 01:56:20 INFO OGG-06594 Replicat REP1 has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start REP1 with NOFILTERDUPTRANSACTIONS option.

REPLICAT altered.

GGSCI (rac1.localdomain) 56> start replicat rep1 NOFILTERDUPTRANSACTIONS

Sending START request to MANAGER ...

REPLICAT REP1 starting

GGSCI (rac1.localdomain) 57> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EX1 00:00:00 00:00:09

REPLICAT RUNNING REP1 00:00:00 00:00:03

check again the demo schema table TB1

ID NAME DEPT

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

5 PRIYA MEDICAL

6 SUBHA IT

Hope, this helps.

Modifying Columns of a Table in Goldengate configuration when not using DDL replication

noreply@blogger.com (Rajeshkumar Govindarajan) — Fri, 21 Dec 2018 02:27:00 +0530

Here is a demo to modify/add columns in a table in oracle goldengate configuration.

In this demo, I used both source and target in the same database.

Database name:- ggtest1

Step 1:- Preparing the Environment

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> alter database force logging;

SQL>

SQL> alter database add supplemental log data;

SQL> select force_logging, supplemental_log_data_min from v$database;

FORCE_LOGGING SUPPLEME

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

YES YES

created goldengate user ogguser

SQL> create user ogguser identified by oracle default tablespace goldengate;

User created.

SQL> grant connect, resource, dba, unlimited tablespace to ogguser;

Grant succeeded.

SQL>ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;SQL>show parameter ENABLE_GOLDENGATE_REPLICATION

SQL>EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ogguser', privilege_type=>'CAPTURE , grant_optional_privileges=> '*');

Create source/target user in the same database and create sample table and insert records for testing.

creating Source username US01

SQL> CREATE USER us01 IDENTIFIED BY us01;

SQL> GRANT CONNECT,RESOURCE,DBA TO us01;

create table tb1(id number primary key, name varchar2(20));

insert into tb1 values(1, 'DEEKSHI');

insert into tbl values(2, 'PRITHVI');

creating target schema DEMO

SQL> CREATE USER demo IDENTIFIED BY demo;

SQL> GRANT CONNECT,RESOURCE,DBA TO demo;

create table tb1(id number primary key, name varchar2(20));

insert into tb1 values(1, 'DEEKSHI');

insert into tbl values(2, 'PRITHVI');

From Goldengate home(GGHOME) run the below scripts as sysdba

[oracle@rac1 Desktop]$ cd /home/oracle/gghome/

[oracle@rac1 gghome]$ sqlplus / as sysdba

SQL> @marker_setup

SQL> @ddl_setup

SQL > @role_setup

GRANT GGS_GGSUSER_ROLE TO

where is the user assigned to the GoldenGate processes.

SQL> grant ggs_ggsuser_role to ogguser;

SQL> @ddl_enable

Step 2:- Configuring Goldengate environment

GGSCI (rac1.localdomain) 1> view params mgr

PORT 7809

GGSCI (rac1.localdomain) 2> start mgr

GGSCI (rac1.localdomain) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (rac1.localdomain) 7> edit params ex1

EXTRACT ex1

userid gguser@ggtest1, password gguser

TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 4194304

DISCARDFILE /home/oracle/gghome/dirrpt/ex1.dsc, APPEND, MEGABYTES 50

EXTTRAIL /home/oracle/gghome/dirdat/ex

TABLE us01.*;

GGSCI (rac1.localdomain) 10> edit params rep1

REPLICAT rep1

USERID gguser@ggtest1, PASSWORD gguser

ASSUMETARGETDEFS

DISCARDFILE /home/oracle/gghome/dirrpt/rep1.dsc, APPEND, MEGABYTES 90

INSERTALLRECORDS

HANDLECOLLISIONS

MAP us01.*, TARGET demo.*;

GGSCI (rac1.localdomain) 13> edit params ./GLOBALS

GGSCHEMA OGGUSER

CHECKPOINTTABLE OGGUSER.CHECKPOINTTABLE

GGSCI (rac1.localdomain) 14> view params mgr

PORT 7809

GGSCI (rac1.localdomain) 15> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

GGSCI (rac1.localdomain) 21> add extract ex1, tranlog, threads 1, begin now

EXTRACT added.

GGSCI (rac1.localdomain) 22> add exttrail /home/oracle/gghome/dirdat/ex, EXTRACT ex1, MEGABYTES 500

EXTTRAIL added.

GGSCI (rac1.localdomain) 23> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EX1 00:00:00 00:00:29

GGSCI (rac1.localdomain) 26> dblogin userid ogguser, password oracle

Successfully logged into database.

GGSCI (rac1.localdomain as ogguser@ggtest1) 27> add checkpointtable ogguser.checkpointtable

Successfully created checkpoint table ogguser.checkpointtable.

GGSCI (rac1.localdomain as ogguser@ggtest1) 28> add trandata us01.*

2018-12-21 00:56:49 INFO OGG-15131 Logging of supplemental redo log data is already enabled for table US01.TB1.

2018-12-21 00:56:49 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table US01.TB1.

GGSCI (rac1.localdomain as ogguser@ggtest1) 29> info trandata us01.*

Logging of supplemental redo log data is enabled for table US01.TB1.

Columns supplementally logged for table US01.TB1: "ID".

Prepared CSN for table US01.TB1: 2538827

GGSCI (rac1.localdomain as ogguser@ggtest1) 30> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EX1 00:00:00 00:03:10

GGSCI (rac1.localdomain as ogguser@ggtest1) 31> add replicat rep1 exttrail /home/oracle/gghome/dirdat/ex, checkpointtable ogguser.checkpointtable

REPLICAT added.

GGSCI (rac1.localdomain as ogguser@ggtest1) 32> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EX1 00:00:00 00:03:56

REPLICAT STOPPED REP1 00:00:00 00:00:04

GGSCI (rac1.localdomain as ogguser@ggtest1) 33> start ex1

Sending START request to MANAGER ...

EXTRACT EX1 starting

GGSCI (rac1.localdomain as ogguser@ggtest1) 34> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STARTING EX1 00:00:00 00:04:10

REPLICAT STOPPED REP1 00:00:00 00:00:18

GGSCI (rac1.localdomain as ogguser@ggtest1) 36> info ex1, detail

EXTRACT EX1 Last Started 2018-12-21 00:58 Status RUNNING

Checkpoint Lag 00:04:13 (updated 00:00:05 ago)

Process ID 26709

Log Read Checkpoint Oracle Redo Logs

2018-12-21 00:54:39 Thread 1, Seqno 54, RBA 30467088

SCN 0.0 (0)

Target Extract Trails:

Trail Name Seqno RBA Max MB Trail Type

/home/oracle/gghome/dirdat/ex 0 1428 500 EXTTRAIL

Extract Source Begin End

+DATA/GGTEST1/ONLINELOG/group_3.311.995089343 2018-12-21 00:54 2018-12-21 00:54

Not Available * Initialized * 2018-12-21 00:54

Current directory /home/oracle/gghome

Report file /home/oracle/gghome/dirrpt/EX1.rpt

Parameter file /home/oracle/gghome/dirprm/ex1.prm

Checkpoint file /home/oracle/gghome/dirchk/EX1.cpe

Process file /home/oracle/gghome/dirpcs/EX1.pce

Error log /home/oracle/gghome/ggserr.log

GGSCI (rac1.localdomain as ogguser@ggtest1) 37> info ex1

EXTRACT EX1 Last Started 2018-12-21 00:58 Status RUNNING

Checkpoint Lag 00:04:13 (updated 00:00:09 ago)

Process ID 26709

Log Read Checkpoint Oracle Redo Logs

2018-12-21 00:54:39 Thread 1, Seqno 54, RBA 30467088

SCN 0.0 (0)

GGSCI (rac1.localdomain as ogguser@ggtest1) 38> info ex1

EXTRACT EX1 Last Started 2018-12-21 00:58 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:01 ago)

Process ID 26709

Log Read Checkpoint Oracle Redo Logs

2018-12-21 00:58:51 Thread 1, Seqno 54, RBA 31052800

SCN 0.2539046 (2539046)

GGSCI (rac1.localdomain as ogguser@ggtest1) 39> start rep1

Sending START request to MANAGER ...

REPLICAT REP1 starting

GGSCI (rac1.localdomain as ogguser@ggtest1) 40> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EX1 00:00:00 00:00:04

REPLICAT STARTING REP1 00:00:00 00:00:47

GGSCI (rac1.localdomain as ogguser@ggtest1) 42> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EX1 00:00:00 00:00:08

REPLICAT RUNNING REP1 00:00:00 00:00:01

GGSCI (rac1.localdomain as ogguser@ggtest1) 43> info rep1, detail

REPLICAT REP1 Last Started 2018-12-21 00:59 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:07 ago)

Process ID 26857

Log Read Checkpoint File /home/oracle/gghome/dirdat/ex000000000

First Record RBA 0

Current Log BSN value: (no data)

Last Committed Transaction CSN value: (no data)

Extract Source Begin End

/home/oracle/gghome/dirdat/ex000000000 * Initialized * First Record

Current directory /home/oracle/gghome

Report file /home/oracle/gghome/dirrpt/REP1.rpt

Parameter file /home/oracle/gghome/dirprm/rep1.prm

Checkpoint file /home/oracle/gghome/dirchk/REP1.cpr

Checkpoint table ogguser.checkpointtable

Process file /home/oracle/gghome/dirpcs/REP1.pcr

Error log /home/oracle/gghome/ggserr.log

Step 3: - Check Lag and stop the extract/replicat

GGSCI (rac1.localdomain as ogguser@ggtest1) 45> lag extract ex1

Sending GETLAG request to EXTRACT EX1 ...

Last record lag 2 seconds.

At EOF, no more records to process

GGSCI (rac1.localdomain as ogguser@ggtest1) 47> stop extract ex1

Sending STOP request to EXTRACT EX1 ...

Request processed.

GGSCI (rac1.localdomain as ogguser@ggtest1) 48> lag replicat rep1

Sending GETLAG request to REPLICAT REP1 ...

Last record lag 7 seconds.

At EOF, no more records to process

GGSCI (rac1.localdomain as ogguser@ggtest1) 49> stop replicat rep1

Sending STOP request to REPLICAT REP1 ...

Request processed.

GGSCI (rac1.localdomain as ogguser@ggtest1) 50> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EX1 00:00:00 00:00:16

REPLICAT STOPPED REP1 00:00:00 00:00:02

Step 4:- Check table is accessed by any users using below query.

SQL> SELECT SID, OBJECT, OWNER FROM GV$ACCESS WHERE OBJECT='TB1' AND OWNER='US01'

SQL> /

no rows selected

SQL> SELECT SID, OBJECT, OWNER FROM GV$ACCESS WHERE OBJECT='TB1' AND OWNER='DEMO';

no rows selected

also If the newly added column is a composite key column, make sure there are no open transactions on the affected source table.

Step 5: -Perform the table modifications on the source and target databases/schema.

SQL> conn us01/us01

Connected.

SQL> show user

USER is "US01"

SQL> desc TB1

Name Null? Type

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

ID NOT NULL NUMBER

NAME VARCHAR2(20)

SQL> alter table tb1 add ( DEPT varchar2(20));

Table altered.

SQL> DESC TB1

Name Null? Type

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

ID NOT NULL NUMBER

NAME VARCHAR2(20)

DEPT VARCHAR2(20)

SQL> select * from tb1;

ID NAME DEPT

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

3 RAMESH

1 DEEKSHI

2 PRITHVI

3 rows selected.

on demo schema

SQL> conn demo/demo

Connected.

SQL> show user

USER is "DEMO"

SQL> DESC TB1

Name Null? Type

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

ID NOT NULL NUMBER

NAME VARCHAR2(20)

SQL> alter table tb1 add (dept varchar2(20));

Table altered.

SQL> desc TB1

Name Null? Type

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

ID NOT NULL NUMBER

NAME VARCHAR2(20)

DEPT VARCHAR2(20)

SQL> select * from tb1;

ID NAME DEPT

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

1 DEEKSHI

2 PRITHVI

3 RAMESH

Step 6:- Start extract and replicat

GGSCI (rac1.localdomain as ogguser@ggtest1) 52> start ex1

Sending START request to MANAGER ...

EXTRACT EX1 starting

GGSCI (rac1.localdomain as ogguser@ggtest1) 53> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EX1 00:00:00 00:00:02

REPLICAT STOPPED REP1 00:00:00 00:03:45

GGSCI (rac1.localdomain as ogguser@ggtest1) 54> start replicat rep1

Sending START request to MANAGER ...

REPLICAT REP1 starting

GGSCI (rac1.localdomain as ogguser@ggtest1) 55> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EX1 00:00:00 00:00:05

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

GGSCI (rac1.localdomain as ogguser@ggtest1) 56> info all, all processes

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EX1 00:00:00 00:00:00

REPLICAT RUNNING REP1 00:00:00 00:00:05

GGSCI (rac1.localdomain as ogguser@ggtest1) 57>

Final Step:- Verify the changes on both source and target schemas by update/insert statements.

SQL> update tb1 set dept='IT' ;

3 rows updated.

SQL> commit;

Commit complete.

SQL> select * from tb1;

ID NAME DEPT

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

3 RAMESH IT

1 DEEKSHI IT

2 PRITHVI IT

3 rows selected.

SQL> insert into tb1 values (4,'VIDHYA','MEDICAL');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM TB1;

ID NAME DEPT

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

3 RAMESH IT

4 VIDHYA MEDICAL

1 DEEKSHI IT

2 PRITHVI IT

4 rows selected.

SQL> SELECT * FROM TB1 ORDER BY ID;

ID NAME DEPT

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

1 DEEKSHI IT

2 PRITHVI IT

3 RAMESH IT

4 VIDHYA MEDICAL

4 rows selected.

on DEMO schema side.

SQL> conn demo/demo

Connected.

SQL> show user

USER is "DEMO"

after update statement demo schema output as below.

SQL> SELECT * FROM TB1 ORDER BY ID;

ID NAME DEPT

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

1 DEEKSHI IT

2 PRITHVI IT

3 RAMESH IT

after insert result on demo schema.

SQL> SELECT * FROM TB1 ORDER BY ID;

ID NAME DEPT

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

1 DEEKSHI IT

2 PRITHVI IT

3 RAMESH IT

4 VIDHYA MEDICAL

SQL>

hope, this helps.

References:-

https://docs.oracle.com/goldengate/1212/gg-winux/GITER/ogg_wu_admin_tera.htm#GITER269

http://www.oracledbwr.com/goldengate/how-to-handle-oracle-ddl-add-a-column-when-not-using-ddl-replication/

ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: Cannot DROP object used in Oracle GoldenGate replication while trigger is enabled.

noreply@blogger.com (Rajeshkumar Govindarajan) — Thu, 20 Dec 2018 04:10:00 +0530

Steps to drop goldengate user/schema when DDL trigger enabled.

If we try to drop goldengate user we will get below error message as below.

SQL> drop user ogguser cascade;
drop user ogguser cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: Cannot
DROP object used in Oracle GoldenGate replication while trigger is enabled.
Consult Oracle GoldenGate documentation and/or call Oracle GoldenGate Technical
Support if you wish to do so., error stack: ORA-06512: at line 305
ORA-06512: at line 1267

There are two options to drop it.

1. Drop trigger manually and then drop goldengate user.

2. Run goldengate scripts to disable/remove trigger and remove marker.

Option 1 Demo:-

SQL> select * from dba_triggers where owner='OGGUSER';

no rows selected

SQL> select a. obj#, a.sys_evts, b.name from trigger$ a, obj$ b

2 where a.sys_evts >0

3 and a.obj#=b.obj#

4 and baseobject=0;

OBJ# SYS_EVTS NAME

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

13999 96 SYSLSBY_EDS_DDL_TRIG

16766 524256 LOGMNRGGC_TRIGGER

18195 4096 AW_TRUNC_TRG

18197 8192 AW_REN_TRG

18199 128 AW_DROP_TRG

19200 4224 XDB_PI_TRIG

20279 12512 NO_VM_DDL

20280 128 NO_VM_DROP_A

73495 128 OJDS$ROLE_TRIGGER$

76034 64 CDC_ALTER_CTABLE_BEFORE

76035 32 CDC_CREATE_CTABLE_AFTER

76036 32 CDC_CREATE_CTABLE_BEFORE

76037 128 CDC_DROP_CTABLE_BEFORE

86599 524256 SDO_GEOR_ADDL_TRIGGER

86687 128 SDO_NETWORK_DROP_USER

86598 524256 SDO_GEOR_BDDL_TRIGGER

86340 128 SDO_TOPO_DROP_FTBL

83383 128 SDO_DROP_USER

83659 32 SDO_ST_SYN_CREATE

87298 128 LBAC$AFTER_DROP

87299 64 LBAC$BEFORE_ALTER

87300 32 LBAC$AFTER_CREATE

92054 524256 GGS_DDL_TRIGGER_BEFORE

select owner, trigger_name, trigger_type, triggering_event, table_owner, table_name, base_object_type from dba_triggers where trigger_name='GGS_DDL_TRIGGER_BEFORE'

OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT TABLE_OWNER TABLE_NAME BASE_OBJECT_TYPE

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

SYS GGS_DDL_TRIGGER_BEFORE BEFORE EVENT DDL SYS DATABASE

SQL> drop trigger sys.GGS_DDL_TRIGGER_BEFORE;

Trigger dropped.

SQL> drop user ogguser cascade;

User dropped.

SQL>

Option 2 demo:-

Go to OGG Installation location and open SQL prompt and login as sysdba

run below 3 scripts

1)@ddl_disable.sql
2)@ ddl_remove.sql
3)@marker_remove.sql

SQL> drop user ogguser cascade;

drop user ogguser cascade

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 2

ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: Cannot

DROP object used in Oracle GoldenGate replication while trigger is enabled.

Consult Oracle GoldenGate documentation and/or call Oracle GoldenGate Technical

Support if you wish to do so., error stack: ORA-06512: at line 305

ORA-06512: at line 1267

SQL> @ddl_disable.sql

Trigger altered.

SQL> @ddl_remove.sql

DDL replication removal script.

WARNING: this script removes all DDL replication objects and data.

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:ogguser

Working, please wait ...

Spooling to file ddl_remove_spool.txt

Script complete.

SQL> @marker_remove.sql

Marker removal script.

WARNING: this script removes all marker objects and data.

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:ogguser

PL/SQL procedure successfully completed.

Sequence dropped.

Table dropped.

Script complete.

SQL> --- now we can drop the goldengate user/schema

SQL>

SQL> drop user ogguser cascade;

User dropped.

Sometimes , if you get below error message after drop trigger, please disconnect/logout of current session and open new session and try to drop user as shown below.

SQL> drop user ogguser cascade;

drop user ogguser cascade

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

[oracle@rac1 gghome]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 20 03:38:36 2018

Connected to:

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Advanced Analytics and Real Application Testing options

SQL> drop user ogguser cascade;

User dropped.

SQL>

hope, this helps.

References:-

Unable to drop GoldenGate user: ORA-20782: Cannot DROP object used in Oracle GoldenGate replication while trigger is enabled (Doc ID 1924642.1)

http://orabliss.blogspot.com/2015/08/dropping-goldengate-user-schema.html

http://dbarohit.blogspot.com/2013/07/error-ora-20782-while-deleting-golden.html

Global temporary tables in goldengate configuration

noreply@blogger.com (Rajeshkumar Govindarajan) — Wed, 19 Dec 2018 02:40:00 +0530

Goldengate classic configuration does not support global temporary tables.

below is the demo done in my classic goldengate configuration system

SQL> create global temporary table us01.gt (id number);

SQL> drop table us01.gt;

Only create statement captured, and drop statement did not captured by OGG. you need to manually drop the table in target.

Only integrated capture with integrated dictionary supports capture of DDLs which contain Global Temporary Tables but with limitation. For more information please refer below oracle support documents

Does GOldenGate support replication of Global Temporary Table DDL replication? (Doc ID 1563450.1)
How To Enable Global Temporary Table DDL Replication In Goldengate? (Doc ID 2322955.1)
References:-

http://www.dbaglobe.com/2012/05/oracle-global-temporary-table-not.html

Does GOldenGate support replication of Global Temporary Table DDL replication? (Doc ID 1563450.1)
How To Enable Global Temporary Table DDL Replication In Goldengate? (Doc ID 2322955.1)

OGG-01004 OCI Error ORA-00001: unique constraint violated OGG-01154 ERROR OGG-01296 and OGG-01668 Manual method of fixing OGG replicat broken

noreply@blogger.com (Rajeshkumar Govindarajan) — Wed, 19 Dec 2018 00:03:00 +0530

Manual fix OGG Replicat Broken Demo

Here in our demo , I have stimulated the issue as below.

-- insert into target first

SQL> insert into tb1 values (1, 'DEEKSHI');

1 row created.

SQL> commit;

Commit complete.

SQL> set time on

22:52:05 SQL> select * from tb1;

ID NAME

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

1 DEEKSHI

-- insert into source later

22:50:10 SQL> insert into tb1 values (1, 'DEEKSHI');

1 row created.

22:52:18 SQL> insert into tb1 values (2, 'PRITHVI');

1 row created.

22:53:27 SQL> commit;

Commit complete.

22:54:01 SQL> select * from tb1;

ID NAME

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

1 DEEKSHI

2 PRITHVI

Replicat rep2 abended with below error message in ggserr.log file.

File: $GGS_HOME/ggserr.log

2018-12-18T22:54:03.033+0530 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rep2.prm: Aborted grouped transaction on DEMO.TB1, Database error 1 (OCI Error ORA-00001: unique constraint (DEMO.SYS_C0010157) violated (status = 1), SQL ).
2018-12-18T22:54:03.033+0530 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rep2.prm: Repositioning to rba 2146 in seqno 8.
2018-12-18T22:54:03.033+0530 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rep2.prm: SQL error 1 mapping US01.TB1 to DEMO.TB1 OCI Error ORA-00001: unique constraint (DEMO.SYS_C0010157) violated (status = 1), SQL .
2018-12-18T22:54:03.033+0530 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rep2.prm: Error mapping from US01.TB1 to DEMO.TB1.
2018-12-18T22:54:03.033+0530 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rep2.prm: PROCESS ABENDING.

Solution:-
GGSCI (rac1.localdomain) 21> info rep2

REPLICAT REP2 Last Started 2018-12-18 22:48 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:02:47 ago)
Log Read Checkpoint File /home/oracle/gghome/dirdat/ex000000008
2018-12-18 22:50:10.000000 RBA 2146

GGSCI (rac1.localdomain) 22> view params rep2

REPLICAT rep2

USERID gguser@ggtest1, PASSWORD gguser

DISCARDFILE /home/oracle/gghome/dirrpt/rep2.dsc, APPEND, MEGABYTES 90

---HANDLECOLLISIONS

---ASSUMETARGETDEFS

---DDL INCLUDE ALL

---DDLERROR DEFAULT IGNORE

---MAPEXCLUDE us01.test1

MAP us01.*, TARGET demo.*;

GGSCI (rac1.localdomain) 23> info rep2

REPLICAT REP2 Last Started 2018-12-18 22:48 Status ABENDED

Checkpoint Lag 00:00:00 (updated 00:05:38 ago)

Log Read Checkpoint File /home/oracle/gghome/dirdat/ex000000008

2018-12-18 22:50:10.000000 RBA 2146

Open discardfile to see the failed transaction data seq no and RBA

File: $GGS_HOME/dirrpt/rep2.dsc

Process Abending : 2018-12-18 21:24:06

Oracle GoldenGate Delivery for Oracle process started, group REP2 discard file opened: 2018-12-18 21:29:19.471291

Oracle GoldenGate Delivery for Oracle process stopped, group REP2 : 2018-12-18 21:30:13.275460

Oracle GoldenGate Delivery for Oracle process started, group REP2 discard file opened: 2018-12-18 22:47:51.097218

Current time: 2018-12-18 22:54:03

Discarded record from action ABEND on error 1

OCI Error ORA-00001: unique constraint (DEMO.SYS_C0010157) violated (status = 1), SQL

Aborting transaction on /home/oracle/gghome/dirdat/ex beginning at seqno 8 rba 2146

error at seqno 8 rba 2146

Problem replicating US01.TB1 to DEMO.TB1

Mapping problem with insert record (target format)...

ID = 1

000000: 31 |1 |

NAME = DEEKSHI

000000: 44 45 45 4b 53 48 49 |DEEKSHI |

Process Abending : 2018-12-18 22:54:03

/home/oracle/gghome

[oracle@rac1 gghome]$ ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle

Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154

Logdump 15 >open /home/oracle/gghome/dirdat/ex000000008

Current LogTrail is /home/oracle/gghome/dirdat/ex000000008

Logdump 16 >pos 2146

Reading forward from RBA 2146

Logdump 17 >n

2018/12/18 22:54:01.000.000 Insert Len 24 RBA 2146

Name: US01.TB1 (TDR Index: 1)

After Image: Partition 12 G b

0000 0500 0000 0100 3101 000b 0000 0007 0044 4545 | ........1........DEE

4b53 4849 | KSHI

Logdump 18 >n

2018/12/18 22:54:01.000.000 Insert Len 24 RBA 2277

Name: US01.TB1 (TDR Index: 1)

After Image: Partition 12 G e

0000 0500 0000 0100 3201 000b 0000 0007 0050 5249 | ........2........PRI

5448 5649 | THVI

GGSCI (rac1.localdomain) 27> info rep2

REPLICAT REP2 Last Started 2018-12-18 23:01 Status ABENDED

Checkpoint Lag 00:00:00 (updated 00:01:43 ago)

Log Read Checkpoint File /home/oracle/gghome/dirdat/ex000000008

First Record RBA 2146

GGSCI (rac1.localdomain) 28> alter replicat rep2 extseqno 8, extrba 2277

2018-12-18 23:02:50 INFO OGG-06594 Replicat REP2 has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start REP2 with NOFILTERDUPTRANSACTIONS option.

REPLICAT altered.

GGSCI (rac1.localdomain) 29> start replicat rep2

Sending START request to MANAGER ...

REPLICAT REP2 starting

GGSCI (rac1.localdomain) 30> info rep2

REPLICAT REP2 Last Started 2018-12-18 23:02 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:00 ago)

Process ID 19591

Log Read Checkpoint File /home/oracle/gghome/dirdat/ex000000008

2018-12-18 22:54:01.000000 RBA 2381

now the issue is resolved. verify the data in demo schema tb1 table.

22:55:48 SQL> show user

USER is "DEMO"

before fixing

22:56:04 SQL> select * from demo.tb1;

ID NAME

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

1 DEEKSHI

after fixing

23:01:45 SQL> /

ID NAME

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

1 DEEKSHI

2 PRITHVI

Hope, this helps

Source and Reference:- http://www.dbaglobe.com/2012/05/manual-fix-ogg-replicat-broken-example.html

ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, XXXX.prm: No valid log files for current redo sequence X, thread X, error retrieving redo file name for sequence X, archived = 0, use_alternate = 0Not able to establish initial position for begin time

noreply@blogger.com (Rajeshkumar Govindarajan) — Tue, 18 Dec 2018 02:17:00 +0530

While starting goldengate extract process, extract PROCESS ABENDING with below error message. It was due to filesytem mismatch, goldengate extract process was unable to extract data from file. I have created archive log destination in ASM storage. To overcome below error message we need to use TRANLOGOPTIONS and DBLOGREADER in extract parameter file.

ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, ex1.prm: No valid log files for current redo sequence 8, thread 1, error retrieving redo file name for sequence 8, archived = 0, use_alternate = 0Not able to establish initial position for begin time 2018-12-18 00:44:48.000000.

sample extract parameter file after modification:-

EXTRACT ex1
USERID gguser PASSWORD gguser

DISCARDFILE /home/oracle/gghome/dirrpt/ex1.dsc, APPEND, MEGABYTES 50
EXTTRAIL /home/oracle/gghome/dirdat/ex

TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 4194304

TRANLOGOPTIONS BUFSIZE 10000000
TABLE us01.*;

PARAMETER	DESCRIPTION

TRANLOGOPTIONS	Specific directives while mining transactions
DBLOGREADER	New ASM API to extract data
BUFSIZE	Max bytes of memory allocated to read redo data – Increases Capture Speed
DBLOGREADERBUFSIZE	Increases Maximum read size for ASM

after added parameters TRANLOGOPTIONS and DBLOGREADER in extract parameter file, the issue resolved.

GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start ex1.
2018-12-18T00:59:37.330+0530 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host [192.168.56.101]:38938 (START EXTRACT EX1 ).
2018-12-18T00:59:37.330+0530 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EX1 starting.
2018-12-18T00:59:37.502+0530 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, ex1.prm: EXTRACT EX1 starting.
2018-12-18T00:59:37.505+0530 INFO OGG-03059 Oracle GoldenGate Capture for Oracle, ex1.prm: Operating system character set identified as UTF-8.
2018-12-18T00:59:37.505+0530 INFO OGG-02695 Oracle GoldenGate Capture for Oracle, ex1.prm: ANSI SQL parameter syntax is used for parameter parsing.
2018-12-18T00:59:38.349+0530 INFO OGG-03522 Oracle GoldenGate Capture for Oracle, ex1.prm: Setting session time zone to source database time zone 'GMT'.
2018-12-18T00:59:38.436+0530 INFO OGG-01635 Oracle GoldenGate Capture for Oracle, ex1.prm: BOUNDED RECOVERY: reset to initial or altered checkpoint.
2018-12-18T00:59:38.460+0530 INFO OGG-01815 Oracle GoldenGate Capture for Oracle, ex1.prm: Virtual Memory Facilities for: BR
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/home/oracle/gghome/BR/EX1.
2018-12-18T00:59:38.460+0530 INFO OGG-01851 Oracle GoldenGate Capture for Oracle, ex1.prm: filecaching started: thread ID: 140188920772352.
2018-12-18T00:59:38.474+0530 INFO OGG-01815 Oracle GoldenGate Capture for Oracle, ex1.prm: Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/home/oracle/gghome/dirtmp.
2018-12-18T00:59:38.574+0530 INFO OGG-06604 Oracle GoldenGate Capture for Oracle, ex1.prm: Database GGTEST1 CPU info: CPU Count 1, CPU Core Count 0, CPU Socket Count 0.
2018-12-18T00:59:38.731+0530 INFO OGG-03522 Oracle GoldenGate Capture for Oracle, ex1.prm: Setting session time zone to source database time zone 'GMT'.
2018-12-18T00:59:38.886+0530 INFO OGG-02089 Oracle GoldenGate Capture for Oracle, ex1.prm: Source redo compatibility version is: 12.1.0.2.0.
2018-12-18T00:59:38.886+0530 INFO OGG-01515 Oracle GoldenGate Capture for Oracle, ex1.prm: Positioning to begin time 2018 M12 18 00:52:05.
2018-12-18T00:59:40.435+0530 INFO OGG-01516 Oracle GoldenGate Capture for Oracle, ex1.prm: Positioned to Sequence 8, RBA 25189904, SCN 0.0 (0), 2018 M12 18 00:52:05.
2018-12-18T00:59:40.435+0530 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, ex1.prm: EXTRACT EX1 started.
2018-12-18T00:59:40.435+0530 INFO OGG-01052 Oracle GoldenGate Capture for Oracle, ex1.prm: No recovery is required for target file /home/oracle/gghome/dirdat/ex000000000, at RBA 0 (file not opened).
2018-12-18T00:59:40.435+0530 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, ex1.prm: Output file /home/oracle/gghome/dirdat/ex is using format RELEASE 12.3.
2018-12-18T00:59:40.435+0530 INFO OGG-01517 Oracle GoldenGate Capture for Oracle, ex1.prm: Position of first record processed Sequence 8, RBA 25189904, SCN 0.1849786 (1849786), 2018 M12 18 00:52:05.

hope, this helps.

Convert normal database(NON-CDB) to PDB database Demo

noreply@blogger.com (Rajeshkumar Govindarajan) — Sun, 16 Dec 2018 04:05:00 +0530

Here is a demo to convert normal NONCDB database to container database as pluggable database (PDB) in oracle 12c.

I have converted normal non-ASM filesystem database to PDB database, same steps will be applicable to ASM filesystem databases.

DEMO:-

NORMAL database name:- NONCDB
Container database name:- CDBRAC

I have created Container database with the name CDBRAC and normal database name with the name NONCDB using dbca.

1. Open the non-cdb database in read only mode:

SQL> select name, cdb, open_mode from v$database;

NAME CDB OPEN_MODE

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

NONCDB NO READ ONLY

2. Check the compatibility of PDB on ( NONCDB)

Describe the non-DBC using the DBMS_PDB.DESCRIBE procedure. This procedure creates an XML file

SQL> BEGIN

2 DBMS_PDB.DESCRIBE(pdb_descr_file=> '/home/oracle/NonCDB_to_CDB.xml');

3 END;

4 /

PL/SQL procedure successfully completed.

3. shutdown the NON-CDB database ( NONCDB)

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

4. Now connect to the container database, where it need to be plugged.

SQL> select name, cdb, open_mode from v$database;

NAME CDB OPEN_MODE

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

CDBRAC YES READ WRITE

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

2 compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=> '/home/oracle/NonCDB_to_CDB.xml')

3 WHEN TRUE THEN 'YES'

4 ELSE 'NO'

5 END;

6 BEGIN

7 DBMS_OUTPUT.PUT_LINE(compatible);

8 END;

9 /

YES

PL/SQL procedure successfully completed.

5. Check the violations:

SQL> select name, cause, type, message , status from pdb_plug_in_violations where name='NONCDB';

NAME CAUSE TYPE

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

MESSAGE

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

STATUS

---------

NONCDB Non-CDB to PDB WARNING

PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.

PENDING

NONCDB OPTION WARNING

Database option RAC mismatch: PDB installed version NULL. CDB installed version 12.1.0.2.0.

PENDING

NONCDB Parameter WARNING

CDB parameter sga_target mismatch: Previous 452M Current 0

PENDING

NONCDB Parameter WARNING

CDB parameter pga_aggregate_target mismatch: Previous 150M Current 0

PENDING

6. Create pluggable database

SQL> create pluggable database NONCDB using '/home/oracle/NonCDB_to_CDB.xml' NOCOPY;

create pluggable database NONCDB using '/home/oracle/NonCDB_to_CDB.xml' NOCOPY

ERROR at line 1:

ORA-27038: created file already exists

ORA-01119: error in creating database file '/u01/app/oracle/oradata/noncdb/temp01.dbf'

-- Tempfile already exists, so to avoid above temp error use tempfile REUSE Clause

SQL> CREATE PLUGGABLE DATABASE NONCDB using '/home/oracle/NonCDB_to_CDB.xml' NOCOPY tempfile reuse;

Pluggable database created.

7 . Run the noncdb_to_pdb.sql script

SQL> ALTER SESSION SET CONTAINER=NONCDB;

SQL > @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

8. Open the PDB:

SQL> SELECT NAME, OPEN_MODE FROM V$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 MOUNTED

PDB2 MOUNTED

NONCDB MOUNTED

SQL> alter session set container=NONCDB;

Session altered.

SQL> ALTER PLUGGABLE DATABASE OPEN;

SQL> show con_name

CON_NAME

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

NONCDB

SQL> show con_id

CON_ID

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

SQL> select name from v$datafile;

NAME

+DATA/CDBRAC/DATAFILE/undotbs1.280.994898349

/u01/app/oracle/oradata/noncdb/system01.dbf

/u01/app/oracle/oradata/noncdb/sysaux01.dbf

/u01/app/oracle/oradata/noncdb/users01.dbf

SQL> select name from v$tempfile;

NAME

/u01/app/oracle/oradata/noncdb/temp01.dbf

SQL> alter session set container=cdb$root;

SQL> SELECT NAME, OPEN_MODE FROM V$pdbs;

NAME OPEN_MODE

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

PDB$SEED READ ONLY

PDB1 MOUNTED

PDB2 MOUNTED

NONCDB READ WRITE

Source and Reference:- http://dbaclass.com/article/convert-non-cdb-database-to-pdb-database-in-oracle-12c/

[INS-75012] Software Location specified is already an existing oracle home and does not contain the selected oracle Goldengate install type

noreply@blogger.com (Rajeshkumar Govindarajan) — Thu, 7 Dec 2017 20:43:00 +0530

[INS-75012] Software Location specified is already an existing oracle home and does not contain the selected oracle Goldengate install type

Problem:-
While installing oracle goldengate we got an error [INS-75012] while selecting an oracle Goldengate home.

Reason :-
The specified directory is already used by an existing deinstalled/improperly removed goldengate Home and oracle central oraInventory inventory.xml file contains the goldengate home information.

[oracle@vm2 ContentsXML]$ cat inventory.xml

Solution:-

Invoke runInstaller to remove the oraInventory information from oracle central inventory.

Example:-
[oracle@vm2 ~]$ . oraenv
ORACLE_SID = [targetdb] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@vm2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/oui/bin/

[oracle@vm2 bin]$ ./runInstaller -silent -deinstall REMOVE_HOMES={"/u01/gghome"}
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 5199 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-12-07_07-50-34PM. Please wait ...[oracle@vm2 bin]$ Oracle Universal Installer, Version 11.2.0.1.0 Production
Copyright (C) 1999, 2009, Oracle. All rights reserved.

Starting deinstall

Deinstall in progress (Thursday, December 7, 2017 7:50:45 PM IST)

End of install phases.(Thursday, December 7, 2017 7:50:45 PM IST)
End of deinstallations
Please check '/u01/app/oraInventory/logs/silentInstall2017-12-07_07-50-34PM.log' for more details.

[oracle@vm2 bin]$

Repeat the steps by replacing ORACLE_HOME in REMOVE_HOMES and remove the unwanted Goldengate home and try the installation again.

now inventory.xml file

[oracle@vm2 ContentsXML]$ cat inventory.xml

cancel the installation and Starting the goldengate configuration again by invoking runInstaller

Hope, this helps you.

Thanks and Regards,

Rajesh Kumar Govindarajan.

Steps to uninstall oracle Goldengate in Linux

noreply@blogger.com (Rajeshkumar Govindarajan) — Thu, 7 Dec 2017 19:10:00 +0530

Steps to uninstall/deinstall oracle Goldengate in Linux machine

1. stop the goldengate manager and extract/replicat processes
2. invoke the deinstall.sh scipt
3. drop the goldengate user in oracle database.

Example:-

[oracle@vm2 gghome1]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jul 21 2017 20:31:38
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI (vm2.localdomain) 1>
GGSCI (vm2.localdomain) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 02:44:26

GGSCI (vm2.localdomain) 1> stop *
GGSCI (vm2.localdomain) 1> stop mgr

[oracle@vm2 ~]$ . oraenv
ORACLE_SID = [targetdb] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@vm2 ~]$ cd /u01/gghome1/
[oracle@vm2 gghome1]$ ls
[oracle@vm2 gghome1]$ cd deinstall/
[oracle@vm2 deinstall]$ ./deinstall.sh

ALERT: Ensure all the processes running from the current Oracle Home are shutdown prior to running this software uninstallation script.

Proceed with removing Oracle GoldenGate home: /u01/gghome1 (yes/no)? [no]
yes
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 5199 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-12-07_06-07-41PM. Please wait ...
Oracle Universal Installer, Version 12.2.0.1.4 Production
Copyright (C) 1999, 2016, Oracle. All rights reserved.

Starting deinstall

Deinstall in progress (Thursday, December 7, 2017 6:11:53 PM IST)
............................................................... 100% Done.

Deinstall successful

End of install phases.(Thursday, December 7, 2017 6:12:55 PM IST)
End of deinstallations
Please check '/u01/app/oraInventory/logs/silentInstall2017-12-07_06-07-41PM.log' for more details.
[oracle@vm2 deinstall]$
[oracle@vm2 deinstall]$

SQL> drop user gguser cascade;
drop user gguser cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-20782: Oracle GoldenGate DDL Replication Error: Code :ORA-20782: Cannot
DROP object used in Oracle GoldenGate replication while trigger is enabled.
Consult Oracle GoldenGate documentation and/or call Oracle GoldenGate Technical
Support if you wish to do so., error stack: ORA-06512: at line 305
ORA-06512: at line 1267

SQL> select * from dba_triggers where owner='GGUSER';

no rows selected

SQL> r
1 select a.obj#, a.sys_evts, b.name from trigger$ a, obj$ b
2* where a.sys_evts>0 and a.obj#=b.obj# and baseobject=0

OBJ# SYS_EVTS NAME
---------- ---------- ------------------------------
8785 128 AW_DROP_TRG
12783 8192 AW_REN_TRG
12781 4096 AW_TRUNC_TRG
54484 64 CDC_ALTER_CTABLE_BEFORE
54485 32 CDC_CREATE_CTABLE_AFTER
54486 32 CDC_CREATE_CTABLE_BEFORE
54487 128 CDC_DROP_CTABLE_BEFORE
56590 4224 XDB_PI_TRIG
64167 2 OLAPISHUTDOWNTRIGGER
64166 1 OLAPISTARTUPTRIGGER
13476 8416 NO_VM_DDL

OBJ# SYS_EVTS NAME
---------- ---------- ------------------------------
13477 128 NO_VM_DROP_A
55387 8256 EXPFIL_ALTEREXPTAB_MAINT
55384 128 EXPFIL_DROPOBJ_MAINT
55385 128 EXPFIL_DROPUSR_MAINT
55386 96 EXPFIL_RESTRICT_TYPEEVOLVE
57367 4096 RLMGR_TRUNCATE_MAINT
66005 128 SDO_NETWORK_DROP_USER
62264 128 SDO_DROP_USER
65930 524256 SDO_GEOR_ADDL_TRIGGER
65929 524256 SDO_GEOR_BDDL_TRIGGER
65928 4 SDO_GEOR_ERR_TRIGGER

OBJ# SYS_EVTS NAME
---------- ---------- ------------------------------
62532 32 SDO_ST_SYN_CREATE
62368 128 SDO_TOPO_DROP_FTBL
69475 1 MGMT_STARTUP
73477 524256 GGS_DDL_TRIGGER_BEFORE

26 rows selected.

SQL> drop trigger GGS_DDL_TRIGGER_BEFORE;

Trigger dropped.

SQL> drop user gguser cascade;
drop user gguser cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use

SQL>
SQL> conn / as sysdba
Connected.
SQL> drop user gguser cascade;

User dropped.

SQL>

hope, this helps you.

Thanks and Regards,
Rajesh Kumar Govindarajan.

Happy New Year 2014

noreply@blogger.com (Rajeshkumar Govindarajan) — Wed, 1 Jan 2014 01:04:00 +0530

Dear All,

“Another fresh new year is here . . .
Another year to live!
To banish worry, doubt, and fear,
To love and laugh and give!

This bright new year is given me
To live each day with zest . . .
To daily grow and try to be
My highest and my best!

I have the opportunity
Once more to right some wrongs,
To pray for peace, to plant a tree,
And sing more joyful songs!

“We will open the book. Its pages are blank. We are going to put words on them ourselves. The book is called Opportunity and its first chapter is New Year's Day.”

Thanks and Regards,
Rajesh Kumar Govindarajan

prvf 5300 failed to retrieve active version for crs on this node

noreply@blogger.com (Rajeshkumar Govindarajan) — Wed, 26 Sep 2012 08:51:00 +0530

We tried to install 11.2.0.2 oracle software version.When we ran the runInstaller it throws the
prvf 5300 failed to retrieve active version for crs on this node error.
11.2.0.3 ASM Grid already installed in server also the server has 11.1.0.7 version software and 11.1.0.7 databases using 11.2.0.3 ASM

Solution:-

1. Backup the files /etc/ocr.loc or /etc/oracle/ocr.loc and /etc/oracle/olr.loc, then rename them to some other names.
2. Retry the standalone (non-RAC) Database 11.2.0.2 installation.
3. After installation completed; restore/rename the backed up files to ocr.loc and olr.loc files in the /etc/ocr.loc or /etc/oracle/ocr.loc and /etc/oracle/olr.loc

Note:-
--No need to shutdown the existing databases and ASM while performing this operations.
--In some environment ocr.loc file is located in /etc/ocr.loc or /etc/oracle/ocr.loc. It depends on the Operating System.

Problem we have faced at our environment:-

After renamed both the files we are unable to log in to existing databases available in that server.
We have not restored the original files ocr.loc and olr.loc files in original location after completing 11.2.0.2 oracle software installation ,so we have faced the below error;
Note:- We came to know ; These 2 files are needed if we are using a grid,.
after renamed both the files to original name; we are able to log in to existing databases available in that server.

see the below error message:-
SQL> select status from v$datafile;
select status from v$datafile
*
ERROR at line 1:
ORA-00204: error in reading (block 1, # blocks 1) of control file
ORA-00202: control file: '+DG1/eagleb/controlfile/current.716.751453263'
ORA-15081: failed to submit an I/O operation to a disk

we have renamed the files ocr.loc and olr.loc again from backup. and the problem got resolved.

These 2 files are needed if we are using a grid,.

I really thank my colleague Krishna; We worked together in this problem.

Regards,
Rajesh Kumar Govindarajan

Drop Database Command Demo

noreply@blogger.com (Rajeshkumar Govindarajan) — Thu, 31 May 2012 04:32:00 +0530

From 10g onwards, we have Drop Database command, that made a fast and efficient cleanup of all datafiles, redologs and control files in one command.

Drop database do require to mount the database in exclusive restricted mode.

The steps are as follows:

•shutdown abort;

•startup mount exclusive restrict;

•drop database;

Here is a Demo:-

$ . oraenv

ORACLE_SID = [oracle] ? miadev07

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/10.2.0.4 is /u01/app/oracle/product/10.2.0.4

dev07: /home/oracle

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 30 18:46:37 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup mount exclusive restrict;

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size 2089472 bytes

Variable Size 390073856 bytes

Database Buffers 675282944 bytes

Redo Buffers 6295552 bytes

Database mounted.

SQL> select name from v$datafile;

NAME

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

+DG1/miadev07/datafile/system.342.712614251

+DG1/miadev07/datafile/undotbs1.269.712587099

+DG1/miadev07/datafile/sysaux.311.712587103

+DG1/miadev07/datafile/users.315.712587107

SQL> select member from v$logfile;

MEMBER

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

+DG1/miadev07/onlinelog/group_3.351.712625873

+DG1/miadev07/onlinelog/group_2.350.712625871

+DG1/miadev07/onlinelog/group_1.349.712625871

SQL> select name from v$controlfile;

NAME

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

+DG1/miadev07/controlfile/current.348.712625853

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit

Check Datafile/Redologs/Controlfile after drop database in ASM/File system. it will not available.

Regards,
Rajesh Kumar Govindarajan.

Step by Step document to Create Physical Standby Database from Active Database using RMAN

noreply@blogger.com (Rajeshkumar Govindarajan) — Mon, 27 Feb 2012 04:54:00 +0530

Step by step document to create a physical standby database using RMAN DUPLICATE FROM ACTIVE DATABASE command without shutting down the primary and using primary active database files (No need to take backup)

Database Name :- WHITE
Primary db_unique_name :- WHITE
standby db_unique_name :- BLACK

Primary Database Server Name :- DC1
Standby Database Server Name :- DC2

Steps:-
1.Make the necessary changes to the primary database.
a. Enable force logging.
b. Creating the password file if one does not exist.
c. Create standby redologs.
d. Modify the parameter file suitable for Dataguard.
e. Change the database to archivelog mode if it is in no archivelog mode.
2. Ensure that the sql*net connectivity is working fine.
----if not add an entry for database in TNSNAMES.ORA and LISTENER.ORA file for primary and standby database.
3. Create the standby database over the network using the active(primary) database files.

a.create a password file for standby database
The username is required to be SYS and the password needs to be the same on the Primary and Standby.
The password file name must match the instance name/SID used at the standby site, not the DB_NAME.
b. Create the initialization parameter file for the standby database (auxiliary database)
c. Create the necessary mount points or the folders for the database files
d. Run the standby creation ON STANDBY by connecting to primary as target database.


      RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;

duplicate target database for standby from active database
spfile
  parameter_value_convert 'white','black'
  set db_unique_name='black'
  set db_file_name_convert='/white/','/black/'
  set log_file_name_convert='/white/','/black/'
  set control_files='+DATA/black/controlfile/control01.ctl'
  set log_archive_max_processes='5'
  set fal_client='black'
  set fal_server='white'
  set standby_file_management='AUTO'
  set log_archive_config='dg_config=(white,black)'
  set log_archive_dest_1='service=white ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=white'
;
}

4. Check the log shipping and apply.

Demo:-


SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(white,black)';


SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATA/archivelog/white/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=white';


SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=black LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=black';


SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;


SQL> alter system set FAL_SERVER=black;


SQL> alter system set FAL_CLIENT=white;


SQL> alter system set DB_FILE_NAME_CONVERT='+DATA/black/datafile/','+DATA/white/datafile/','+DATA/black/tempfile/','+DATA/white/tempfile/' scope=spfile;


SQL> alter system set LOG_FILE_NAME_CONVERT='+DATA/black/onlinelog/','+DATA/white/onlinelog/' scope=spfile;


SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATA/archivelog/white/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=white';


SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=black LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=black';


SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;


SQL> alter system set FAL_SERVER=black;


SQL> alter system set FAL_CLIENT=white;


SQL> alter system set DB_FILE_NAME_CONVERT='+DATA/black/datafile/','+DATA/white/datafile/','+DATA/black/tempfile/','+DATA/white/tempfile/' scope=spfile;


SQL> alter system set LOG_FILE_NAME_CONVERT='+DATA/black/onlinelog/','+DATA/white/onlinelog/' scope=spfile;

System altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence     13
Current log sequence           15
SQL> select force_logging from v$database;

FOR
---
NO


SQL> Alter database force logging;


SQL> select force_logging from v$database;

FOR
---
YES


SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> select name,database_role,db_unique_name from v$database;

NAME      DATABASE_ROLE    DB_UNIQUE_NAME
--------- ---------------- ------------------------------
WHITE     PRIMARY          white

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/white/datafile/system.324.776321695
+DATA/white/datafile/sysaux.337.776321703
+DATA/white/datafile/undotbs1.336.776321707
+DATA/white/datafile/users.335.776321707

SQL> col member for a65          
SQL> select a.member,b.bytes/1024/1024 from v$Logfile a,v$log b where a.group#=b.group#;

MEMBER
-----------------------------------------------------------------
B.BYTES/1024/1024
-----------------
+DATA/white/onlinelog/group_3.302.776321951
                5

+DATA/white/onlinelog/group_2.322.776321951
                5

+DATA/white/onlinelog/group_1.311.776321949
                5


SQL> select name from v$tempfile;

NAME
---------------------------------------------------
+DATA/white/tempfile/temp.321.776321963


Adding standby redolog files.

SQL> alter database add standby logfile size 5m;

Database altered.

SQL> alter database add standby logfile size 5m;

Database altered.

SQL> alter database add standby logfile size 5m;

Database altered.

SQL> alter database add standby logfile size 5m;

Database altered.

SQL> col member for a56
SQL> select member,type from v$logfile;

MEMBER                                                   TYPE
-------------------------------------------------------- -------
+DATA/white/onlinelog/group_3.302.776321951              ONLINE
+DATA/white/onlinelog/group_2.322.776321951              ONLINE
+DATA/white/onlinelog/group_1.311.776321949              ONLINE
+DATA/white/onlinelog/group_4.358.776324285              STANDBY
+DATA/white/onlinelog/group_5.456.776324299              STANDBY
+DATA/white/onlinelog/group_6.331.776324299              STANDBY
+DATA/white/onlinelog/group_7.318.776324301              STANDBY

7 rows selected.

SQL> 


follow the below steps, if your database is in no archivelog, or else no need to shutdown the database
========================================================

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1456349184 bytes
Fixed Size                  1336568 bytes
Variable Size             855640840 bytes
Database Buffers          587202560 bytes
Redo Buffers               12169216 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> create pfile='/tmp/init.ora' from spfile;

File created.



=======================================================

add the following entry in the listener.ora file on primary database
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.101 )(PORT = 1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = white )
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
     (SID_NAME = white)
    )
   )


==================================================
tnsnames.ora file on both machines
==================================================
BLACK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dc2.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = black)
    )
  )



WHITE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dc1.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = white)
    )
  )

=============================================================

[oracle@dc1 admin]$ tnsping white

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 27-FEB-2012 05:46:29

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc1.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = white)))
OK (50 msec)
[oracle@dc1 admin]$ tnsping black

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 27-FEB-2012 05:46:33

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc2.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = black)))
OK (10 msec)
[oracle@dc1 admin]$ 

=========================================================

[oracle@dc1 dbs]$ scp orapwwhite dc2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwblack
orapwwhite                                                                                                100% 1536     1.5KB/s   00:00    
[oracle@dc1 dbs]$ 

==========================================================

[oracle@dc1 dbs]$ scp /tmp/init.ora dc2:/u01/app/oracle/product/11.2.0/db_1/dbs/initblack.ora
init.ora                                                                                                  100% 1333     1.3KB/s   00:00    
[oracle@dc1 dbs]$ 

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/white/spfilewhite.ora
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DATA/archivelog/white/
Oldest online log sequence     16
Next log sequence to archive   18
Current log sequence           18
SQL> 

follow below steps on machine two for creating standby database

=================================================

[oracle@dc2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@dc2 dbs]$ cat initblack.ora 
white.__db_cache_size=587202560
white.__java_pool_size=16777216
white.__large_pool_size=16777216
white.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
white.__pga_aggregate_target=587202560
white.__sga_target=872415232
white.__shared_io_pool_size=0
white.__shared_pool_size=234881024
white.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/white/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/white/controlfile/current.310.776321931'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/black/datafile/','+DATA/white/datafile/','+DATA/black/tempfile/','+DATA/white/tempfile/'
*.db_name='white'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=whiteXDB)'
*.fal_client='WHITE'
*.fal_server='BLACK'
*.log_archive_config='DG_CONFIG=(white,black)'
*.log_archive_dest_1='LOCATION=+DATA/archivelog/white/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=white'
*.log_archive_dest_2='SERVICE=black LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=black'
*.log_archive_dest_state_1='ENABLE'
*.log_file_name_convert='+DATA/black/onlinelog/','+DATA/white/onlinelog/'
*.memory_target=1449132032
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@dc2 dbs]$ 

===================================
after editing the pfile parameter required for standby database
=================================

black.__db_cache_size=587202560
black.__java_pool_size=16777216
black.__large_pool_size=16777216
black.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
black.__pga_aggregate_target=587202560
black.__sga_target=872415232
black.__shared_io_pool_size=0
black.__shared_pool_size=234881024
black.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/black/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/black/controlfile/current.310.776321931'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/white/datafile/','+DATA/black/datafile/','+DATA/white/tempfile/','+DATA/black/tempfile/'
*.db_name='white'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=blackXDB)'
*.fal_client='BLACK'
*.fal_server='WHITE'
*.log_archive_config='DG_CONFIG=(white,black)'
*.log_archive_dest_1='LOCATION=+DATA/archivelog/black/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=black'
*.log_archive_dest_2='SERVICE=white LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=white'
*.log_archive_dest_state_1='ENABLE'
*.log_file_name_convert='+DATA/white/onlinelog/','+DATA/black/onlinelog/'
*.memory_target=1449132032
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

==========================================
add an entry in the /etc/oratab file

black:/u01/app/oracle/product/11.2.0/db_1:N

add the following lines to the listener.ora file 

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = black )
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
     (SID_NAME = black)
    )
   )


add the below entries on both nodes tnsnames.ora file


# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

BLACK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dc2.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = black)
    )
  )

WHITE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dc1.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = white)
    )
  )

[oracle@dc2 admin]$ . oraenv
ORACLE_SID = [black] ? black
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 27 05:59:50 2012

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

Connected to an idle instance.

SQL> startup nomount
ORA-00304: requested INSTANCE_NUMBER is busy

to resolve this issue add db_unique_name='black' in the pfile of standby database.



ORA-09925: Unable to create audit trail file
Linux Error: 2: No such file or directory
Additional information: 9925

to resolve this issue create adump directory 

[oracle@dc2 dbs]$ mkdir -p /u01/app/oracle/admin/black/adump

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1456349184 bytes
Fixed Size                  1336568 bytes
Variable Size             855640840 bytes
Database Buffers          587202560 bytes
Redo Buffers               12169216 bytes
SQL> 

SQL> conn sys/oracle@white as sysdba;
Connected.
SQL> conn sys/oracle@black as sysdba;
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

-- to resolve this issue  need to check/add the below entries in listener.ora file.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = black )
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
     (SID_NAME = black)
    )
   )


Warning: You are no longer connected to ORACLE.
SQL> conn sys/oracle@black as sysdba;
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

--- to resolve blocking new connections shutdown the database and startup nomount the standby database again will resolve it.

SQL> conn sys/oracle@black as sysdba;
Connected.
SQL>

SQL> conn sys/oracle@black as sysdba;
Connected.
SQL> 

create standby database from active primary database using RMAN

[oracle@dc2 dbs]$ . oraenv
ORACLE_SID = [oracle] ? black
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 dbs]$ rman target sys/oracle@white auxiliary sys/oracle@black

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Feb 27 06:11:51 2012

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

connected to target database: WHITE (DBID=3671326283)
connected to auxiliary database: WHITE (not mounted)


RMAN> run {
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel stby type disk;

duplicate target database for standby from active database
spfile
  parameter_value_convert 'white','black'
  set db_unique_name='black'
  set db_file_name_convert='/white/','/black/'
  set log_file_name_convert='/white/','/black/'
  set control_files='+DATA/black/controlfile/control01.ctl'
  set log_archive_max_processes='5'
  set fal_client='black'
  set fal_server='white'
  set standby_file_management='AUTO'
  set log_archive_config='dg_config=(white,black)'
;
} 
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=55 device type=DISK

allocated channel: p2
channel p2: SID=40 device type=DISK

allocated channel: p3
channel p3: SID=1 device type=DISK

allocated channel: p4
channel p4: SID=39 device type=DISK

allocated channel: stby
channel stby: SID=32 device type=DISK

Starting Duplicate Db at 27-FEB-12

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwwhite' auxiliary format 
 '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwblack'   targetfile 
 '+DATA/white/spfilewhite.ora' auxiliary format 
 '/u01/app/oracle/product/11.2.0/db_1/dbs/spfileblack.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileblack.ora''";
}
executing Memory Script

Starting backup at 27-FEB-12
Finished backup at 27-FEB-12

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfileblack.ora''

contents of Memory Script:
{
   sql clone "alter system set  audit_file_dest = 
 ''/u01/app/oracle/admin/black/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  dispatchers = 
 ''(PROTOCOL=TCP) (SERVICE=blackXDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 = 
 ''LOCATION=+DATA/archivelog/black/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=white'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''black'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert = 
 ''/white/'', ''/black/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert = 
 ''/white/'', ''/black/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files = 
 ''+DATA/black/controlfile/control01.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_max_processes = 
 5 comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_client = 
 ''black'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server = 
 ''white'' comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management = 
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_config = 
 ''dg_config=(white,black)'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/black/adump'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=blackXDB)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''LOCATION=+DATA/archivelog/black/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=white'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''black'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/white/'', ''/black/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/white/'', ''/black/'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''+DATA/black/controlfile/control01.ctl'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_max_processes =  5 comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''black'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''white'' comment= '''' scope=spfile

sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_config =  ''dg_config=(white,black)'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1456349184 bytes

Fixed Size                     1336568 bytes
Variable Size                855640840 bytes
Database Buffers             587202560 bytes
Redo Buffers                  12169216 bytes
allocated channel: stby
channel stby: SID=29 device type=DISK

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '+DATA/black/controlfile/control01.ctl';
}
executing Memory Script

Starting backup at 27-FEB-12
channel p1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_white.f tag=TAG20120227T061458 RECID=1 STAMP=776326500
channel p1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 27-FEB-12

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "+data";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "+data";
   set newname for datafile  2 to 
 "+data";
   set newname for datafile  3 to 
 "+data";
   set newname for datafile  4 to 
 "+data";
   backup as copy reuse
   datafile  1 auxiliary format 
 "+data"   datafile 
 2 auxiliary format 
 "+data"   datafile 
 3 auxiliary format 
 "+data"   datafile 
 4 auxiliary format 
 "+data"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 27-FEB-12
channel p1: starting datafile copy
input datafile file number=00001 name=+DATA/white/datafile/system.324.776321695
channel p2: starting datafile copy
input datafile file number=00002 name=+DATA/white/datafile/sysaux.337.776321703
channel p3: starting datafile copy
input datafile file number=00003 name=+DATA/white/datafile/undotbs1.336.776321707
channel p4: starting datafile copy
input datafile file number=00004 name=+DATA/white/datafile/users.335.776321707
output file name=+DATA/black/datafile/undotbs1.266.776326483 tag=TAG20120227T061514
channel p3: datafile copy complete, elapsed time: 00:00:08
output file name=+DATA/black/datafile/users.514.776326483 tag=TAG20120227T061514
channel p4: datafile copy complete, elapsed time: 00:00:09
output file name=+DATA/black/datafile/sysaux.518.776326483 tag=TAG20120227T061514
channel p2: datafile copy complete, elapsed time: 00:01:19
output file name=+DATA/black/datafile/system.517.776326481 tag=TAG20120227T061514
channel p1: datafile copy complete, elapsed time: 00:01:40
Finished backup at 27-FEB-12

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=776326581 file name=+DATA/black/datafile/system.517.776326481
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=776326581 file name=+DATA/black/datafile/sysaux.518.776326483
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=776326581 file name=+DATA/black/datafile/undotbs1.266.776326483
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=776326581 file name=+DATA/black/datafile/users.514.776326483
Finished Duplicate Db at 27-FEB-12
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: stby

RMAN> exit


Recovery Manager complete.
[oracle@dc2 dbs]$ 


SQL> conn / as sysdba   
Connected.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/black/datafile/system.517.776326481
+DATA/black/datafile/sysaux.518.776326483
+DATA/black/datafile/undotbs1.266.776326483
+DATA/black/datafile/users.514.776326483

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA

SQL> col member for a56
SQL> select member,type from v$logfile;

MEMBER                                                   TYPE
-------------------------------------------------------- -------
+DATA/black/onlinelog/group_3.587.776326583              ONLINE
+DATA/black/onlinelog/group_2.519.776326583              ONLINE
+DATA/black/onlinelog/group_1.516.776326583              ONLINE
+DATA/black/onlinelog/group_4.520.776326583              STANDBY
+DATA/black/onlinelog/group_5.522.776326583              STANDBY
+DATA/black/onlinelog/group_6.535.776326583              STANDBY
+DATA/black/onlinelog/group_7.534.776326583              STANDBY

7 rows selected.

SQL> select name,database_role,db_unique_name from v$database;

NAME      DATABASE_ROLE    DB_UNIQUE_NAME
--------- ---------------- ------------------------------
WHITE     PHYSICAL STANDBY black

SQL> 


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            ?/dbs/arch
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           0
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            ?/dbs/arch
Oldest online log sequence     22
Next log sequence to archive   0
Current log sequence           23
SQL> 


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Regards,
Rajesh Kumar Govindarajan.

Create single instance standby database for RAC Database

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Sun, 22 Jan 2012 07:18:00 +0530

here i created a physical standby database stbydb for RAC database primdb instance names primdb1 and primdb2 , server names:- DC1 and DC2.

PRIMARY RAC DATABASE INSTANCE PRIMDB1 IN HOST MACHINE DC1
PRIMARY RAC DATABASE INSTANCE PRIMDB2 IN HOST MACHINE DC2

CREATING A STANDBY DATABASE IN SERVER DC2 WITH NAME STBYDB

ASSUMPTION:
Primary:

· The RAC Primary Database is in fully operating condition in Archivelog mode using ASM storage (shared location) as archivelog destination.
· The Primary Database is using ASM as Storage and OMFs.
· Listener LISTENER has already been created.

steps in primary database Servers:-
# Database must in Archivelog mode.
# Enable Force Logging.
# Create the Standby Redo logs.
# Create a password file for all instances( Password must same in all nodes).
# Update listener.ora file to include SID information on each node in cluster.
# Verify cluster Service is available.
# Verify TNSNAMES.ORA file.
# Modify init.ora Parameters For DataGuard Configuration.
# Create temporary directory to hold the RMAN backup of this database.
# Backup the Primary Database for Standby.
# copy the Primary Database backup to the standby database server to create standby database.

steps in Standby database server:-
# create required folders and directories.
# Modify the init.ora parameter on standby database.
# create a password file(Password must same as primary database instances)
# Update listener.ora file to include SID information
# Add required TNS entries for standby database on all servers.
# Verify TNSNAMES.ORA file.
# Create standby database.

[oracle@dc1 dbs]$ . oraenv
ORACLE_SID = [black] ? primdb1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 22 03:41:53 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     16
Next log sequence to archive   17
Current log sequence           17
SQL> alter database force logging;

Database altered.

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
primdb1          OPEN
primdb2          OPEN

SQL> select name,db_unique_name,database_role from gv$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
PRIMDB    primdb                         PRIMARY
PRIMDB    primdb                         PRIMARY

IN PRIMARY DATABASE(any one of the node)
=====================================================================================

alter system set log_file_name_convert='+DATA/stbydb,'+DATA/primdb' scope=spfile sid='*';
alter system set db_file_name_convert='+DATA/stbydb','+DATA/primdb' scope=spfile sid='*';
alter system set fal_client='primdb1' sid='primdb1';
alter system set fal_client='primdb2' sid='primdb2';
alter system set fal_server='stbydb' sid='*';
alter system set log_archive_config='dg_config=(primdb,stbydb)' sid='*';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primdb' sid='*';
alter system set log_archive_dest_state_2='ENABLE' sid='*';
alter system set log_archive_dest_2='SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb' sid='*';
alter system set log_archive_dest_3='LOCATION=+DATA/primdb/STANDBYLOG  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=primdb' sid='*';
alter system set log_archive_dest_state_1='enable' sid='*';
alter system set log_archive_dest_state_3='enable' sid='*';
alter system set log_archive_max_processes=7 sid='*';
alter system set log_archive_min_succeed_dest=2 sid='*';
alter system set remote_login_passwordfile='EXCLUSIVE' sid='*';
alter system set standby_file_management='auto' sid='*';


primdb2.__db_cache_size=520093696
primdb1.__db_cache_size=520093696
primdb2.__java_pool_size=16777216
primdb1.__java_pool_size=16777216
primdb2.__large_pool_size=16777216
primdb1.__large_pool_size=16777216
primdb2.__pga_aggregate_target=587202560
primdb1.__pga_aggregate_target=587202560
primdb2.__sga_target=872415232
primdb1.__sga_target=872415232
primdb2.__shared_io_pool_size=0
primdb1.__shared_io_pool_size=0
primdb2.__shared_pool_size=301989888
primdb1.__shared_pool_size=301989888
primdb2.__streams_pool_size=0
primdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/primdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/primdb/controlfile/current.466.773206191','+DATA/primdb/controlfile/current.465.773206191'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/stbydb','+DATA/primdb'
*.db_name='primdb'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primdbXDB)'
primdb1.fal_client='primdb1'
primdb2.fal_client='primdb2'
*.fal_server='stbydb'
primdb2.instance_number=2
primdb1.instance_number=1
*.log_archive_config='dg_config=(primdb,stbydb)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primdb'
*.log_archive_dest_2='SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb'
*.log_archive_dest_3='LOCATION=+DATA/primdb/STANDBYLOG  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=primdb'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=7
*.log_archive_min_succeed_dest=2
*.memory_target=1449132032
*.open_cursors=300
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.standby_archive_dest='+DATA/primdb/STANDBYLOG'
*.standby_file_management='auto'
primdb1.thread=1
primdb2.thread=2
primdb2.undo_tablespace='UNDOTBS1'
primdb1.undo_tablespace='UNDOTBS2'

SQL> select force_logging from v$database;

FOR
---
YES

SQL> alter system set SEC_CASE_SENSITIVE_LOGON=FALSE scope=spfile sid='*';

System altered.



[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb1 password=oracle force=y entries=5 ignorecase=y
[oracle@dc1 dbs]$

[oracle@dc2 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb2 password=oracle entries=5 force=y ignorecase=y

SQL> sho parameter password

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL>

create standby logfile on primary database
=====================================================================================
SQL> col member for a56
SQL> select group#,member,type from v$logfile order by 1;

  GROUP# MEMBER                                                   TYPE
---------- -------------------------------------------------------- -------
       1 +DATA/primdb/onlinelog/group_1.468.773206197             ONLINE
       1 +DATA/primdb/onlinelog/group_1.467.773206195             ONLINE
       2 +DATA/primdb/onlinelog/group_2.470.773206197             ONLINE
       2 +DATA/primdb/onlinelog/group_2.469.773206197             ONLINE
       3 +DATA/primdb/onlinelog/group_3.473.773206427             ONLINE
       3 +DATA/primdb/onlinelog/group_3.474.773206427             ONLINE
       4 +DATA/primdb/onlinelog/group_4.475.773206429             ONLINE
       4 +DATA/primdb/onlinelog/group_4.476.773206429             ONLINE

8 rows selected.


SQL> select group#,bytes from v$Log;

  GROUP#      BYTES
---------- ----------
       1    5242880
       2    5242880
       3    5242880
       4    5242880

SQL>
alter database add standby logfile thread 1
group 5 size 5M,
group 6 size 5M;

Database altered.

SQL> alter database add standby logfile thread 2
group 7 size 5M,
group 8 size 5M;

Database altered.


SQL> select group#,member ,type from v$Logfile order by 1;

  GROUP# MEMBER                                                   TYPE
---------- -------------------------------------------------------- -------
       1 +DATA/primdb/onlinelog/group_1.467.773206195             ONLINE
       1 +DATA/primdb/onlinelog/group_1.468.773206197             ONLINE
       2 +DATA/primdb/onlinelog/group_2.469.773206197             ONLINE
       2 +DATA/primdb/onlinelog/group_2.470.773206197             ONLINE
       3 +DATA/primdb/onlinelog/group_3.473.773206427             ONLINE
       3 +DATA/primdb/onlinelog/group_3.474.773206427             ONLINE
       4 +DATA/primdb/onlinelog/group_4.475.773206429             ONLINE
       4 +DATA/primdb/onlinelog/group_4.476.773206429             ONLINE
       5 +DATA/primdb/onlinelog/group_5.481.773209067             STANDBY
       5 +DATA/primdb/onlinelog/group_5.482.773209067             STANDBY
       6 +DATA/primdb/onlinelog/group_6.483.773209067             STANDBY
       6 +DATA/primdb/onlinelog/group_6.484.773209069             STANDBY
       7 +DATA/primdb/onlinelog/group_7.485.773209079             STANDBY
       7 +DATA/primdb/onlinelog/group_7.486.773209079             STANDBY
       8 +DATA/primdb/onlinelog/group_8.487.773209079             STANDBY
       8 +DATA/primdb/onlinelog/group_8.488.773209081             STANDBY

16 rows selected.


create pfile for standby database
=====================================================================================
SQL> create pfile='$ORACLE_HOME/dbs/initstbydb.ora' from spfile;

File created.

SQL>

edit the required parameters

remove the red coloured lines for the standby database initstbydb.ora file
edit/change the blue coloured lines as per standby database required.

primdb2.__db_cache_size=520093696 primdb1.__db_cache_size=520093696 primdb2.__java_pool_size=16777216 primdb1.__java_pool_size=16777216 primdb2.__large_pool_size=16777216 primdb1.__large_pool_size=16777216 primdb2.__pga_aggregate_target=587202560 primdb1.__pga_aggregate_target=587202560 primdb2.__sga_target=872415232 primdb1.__sga_target=872415232 primdb2.__shared_io_pool_size=0 primdb1.__shared_io_pool_size=0 primdb2.__shared_pool_size=301989888 primdb1.__shared_pool_size=301989888 primdb2.__streams_pool_size=0 primdb1.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/primdb/adump' *.audit_trail='db' *.cluster_database=true *.compatible='11.2.0.0.0' *.control_files='+DATA/primdb/controlfile/current.466.773206191','+DATA/primdb/controlfile/current.465.773206191' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_name_convert='+DATA/stbydb','+DATA/primdb' *.db_name='primdb' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=4039114752 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=primdbXDB)' primdb1.fal_client='primdb1' primdb2.fal_client='primdb2' *.fal_server='stbydb' primdb2.instance_number=2 primdb1.instance_number=1 *.log_archive_config='dg_config=(primdb,stbydb)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primdb' *.log_archive_dest_2='SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb' *.log_archive_dest_3='LOCATION=+DATA/primdb/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=primdb' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='defer' *.log_archive_dest_state_3='enable' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=7 *.log_archive_min_succeed_dest=2 *.memory_target=1449132032 *.open_cursors=300 *.processes=150 *.remote_listener='dc-scan:1521' *.remote_login_passwordfile='exclusive' *.sec_case_sensitive_logon=FALSE *.standby_file_management='auto' primdb1.thread=1 primdb2.thread=2 primdb2.undo_tablespace='UNDOTBS1' primdb1.undo_tablespace='UNDOTBS2'

*************************************************************************************
after editing the initstbydb.ora file
*************************************************************************************

db_cache_size=520093696 java_pool_size=16777216 large_pool_size=16777216 pga_aggregate_target=587202560 sga_target=872415232 shared_pool_size=301989888 streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/stbydb/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='+DATA/stbydb/controlfile/control01.ctl' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_name_convert='+DATA/primdb/','+DATA/stbydb/' *.db_name='primdb' db_unique_name='stbydb' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=4039114752 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=stbydbXDB)' fal_client='stbydb' *.fal_server='primdb' *.log_archive_config='dg_config=(primdb,stbydb)' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbydb' *.log_archive_dest_2='SERVICE=primdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primdb' *.log_archive_dest_3='LOCATION=+DATA/stbydb/STANDBYLOG VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=stbydb' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='defer' *.log_archive_dest_state_3='enable' *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=7 *.log_archive_min_succeed_dest=2 *.open_cursors=300 *.processes=150 *.remote_listener='dc-scan:1521' *.remote_login_passwordfile='exclusive' *.standby_file_management='auto' undo_tablespace='UNDOTBS1' SEC_CASE_SENSITIVE_LOGON=FALSE

================================================================================

create required folder for stbydb database and also add an entry in the /etc/oratab for stbydb database ,
create a password file for stbydb database.(must have similar password like primary RAC database )
add required tns entries in the tnsnames.ora and listener entry in the listener.ora file
take a RMAN backup of target database and target database controlfile from any one of the rac node
and scp the backup to the standby database host machine to create standby database.

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

[oracle@dc1 backup]$ pwd
/u01/app/oracle/backup
[oracle@dc1 backup]$ . oraenv
ORACLE_SID = [oracle] ? primdb1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc1 backup]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 22 04:37:01 2012

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

connected to target database: PRIMDB (DBID=447762734)

RMAN> configure channel device type disk format '/u01/app/oracle/backup/%U';

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u01/app/oracle/backup/%U';
new RMAN configuration parameters are successfully stored

RMAN> backup as compressed backupset database plus archivelog;

RMAN> backup current controlfile for standby;

RMAN> backup archivelog all;


creating directories
=====================================================

[oracle@dc2 dbs]$ mkdir -p /u01/app/oracle/admin/primdb/adump
[oracle@dc2 dbs]$ mkdir -p /u01/app/oracle/backup

[oracle@dc2 dbs]$ . oraenv
ORACLE_SID = [+ASM2] ?
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle
[oracle@dc2 dbs]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd data
ASMCMD> mkdir STBYDB
ASMCMD> cd STBYDB
ASMCMD> mkdir ARCHIVELOG CONTROLFILE DATAFILE ONLINELOG PARAMETERFILE STANDBYLOG TEMPFILE
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
STANDBYLOG/
TEMPFILE/
ASMCMD>

create password file for standby database.
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwstbydb password=oracle entries=5 ignorecase=y
[oracle@dc2 dbs]$

copy the backup to standby server.
[oracle@dc1 backup]$ ls -ltr
total 290584
-rw-r----- 1 oracle oinstall   4157952 Jan 22 04:38 01n1cg5n_1_1
-rw-r----- 1 oracle oinstall 259842048 Jan 22 04:40 02n1cg5t_1_1
-rw-r----- 1 oracle oinstall   1114112 Jan 22 04:40 03n1cg9g_1_1
-rw-r----- 1 oracle oinstall    157184 Jan 22 04:40 04n1cg9u_1_1
-rw-r----- 1 oracle oinstall  18546688 Jan 22 04:46 05n1cglv_1_1
-rw-r----- 1 oracle oinstall  13416448 Jan 22 04:47 06n1cgn8_1_1
[oracle@dc1 backup]$ scp * oracle@dc2:/u01/app/oracle/backup/
01n1cg5n_1_1                                                100% 4061KB   4.0MB/s   00:00  
02n1cg5t_1_1                                                100%  248MB   4.3MB/s   00:58  
03n1cg9g_1_1                                                100% 1088KB   1.1MB/s   00:00  
04n1cg9u_1_1                                                100%  154KB 153.5KB/s   00:00  
05n1cglv_1_1                                                100%   18MB   2.0MB/s   00:09  
06n1cgn8_1_1                                                100%   13MB   2.6MB/s   00:05  
[oracle@dc1 backup]$ pwd
/u01/app/oracle/backup
[oracle@dc1 backup]$


add an entry in the /etc/oratab file
vi /etc/oratab
stbydb:/u01/app/oracle/product/11.2.0/db_1:N


add tns entry in all nodes

example:-

[oracle@dc1 backup]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
[oracle@dc1 admin]$ vi tnsnames.ora
PRIMDB =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = primdb)
  )
)

STBYDB =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = stbydb)
  )
)


[oracle@dc1 admin]$ tnsping primdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:55:33

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primdb)))
OK (40 msec)
[oracle@dc1 admin]$ tnsping stbydb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:55:37

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stbydb)))
OK (0 msec)
[oracle@dc1 admin]$


[oracle@dc2 admin]$ tnsping primdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:56:52

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = primdb)))
OK (0 msec)
[oracle@dc2 admin]$ tnsping stbydb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2012 04:56:56

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dc-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stbydb)))
OK (10 msec)
[oracle@dc2 admin]$

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

start the standby database in nomount mode

[oracle@dc2 dbs]$ . oraenv
ORACLE_SID = [+ASM2] ? stbydb
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 22 05:00:18 2012

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

Connected to an idle instance.

SQL> startup nomount

[oracle@dc2 dbs]$ . oraenv
ORACLE_SID = [stbydb] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 dbs]$ rman target sys/oracle@PRIMDB auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan 22 05:33:25 2012

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

connected to target database: PRIMDB (DBID=447762734)
connected to auxiliary database: PRIMDB (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.
*************************************************************************************

alert log file inforamtion

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Using STANDBY_ARCHIVE_DEST parameter default value as +DATA/stbydb/standbylog
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY;
Sun Jan 22 05:52:53 2012
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (stbydb)
Sun Jan 22 05:52:53 2012
MRP0 started with pid=29, OS id=5844
MRP0: Background Managed Standby Recovery process started (stbydb)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 29
Completed: alter database recover managed standby database disconnect from session

Sun Jan 22 05:54:16 2012
alter database recover managed standby database cancel
Sun Jan 22 05:54:17 2012
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_mrp0_5844.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Waiting for MRP0 pid 5844 to terminate
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_mrp0_5844.trc:
ORA-16037: user requested cancel of managed recovery operation
MRP0: Background Media Recovery process shutdown (stbydb)
Managed Standby Recovery Canceled (stbydb)
Completed: alter database recover managed standby database cancel


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (stbydb)
Sun Jan 22 05:56:07 2012
MRP0 started with pid=29, OS id=6135
MRP0: Background Managed Standby Recovery process started (stbydb)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 29
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION



Completed: ALTER DATABASE   MOUNT
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_arc1_6584.trc:
ORA-01017: invalid username/password; logon denied
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
    returning error ORA-16191
------------------------------------------------------------
FAL[client, ARC1]: Error 16191 connecting to primdb for fetching gap sequence
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_arc1_6584.trc:
ORA-16191: Primary log shipping client not logged on standby
Errors in file /u01/app/oracle/diag/rdbms/stbydb/stbydb/trace/stbydb_arc1_6584.trc:
ORA-16191: Primary log shipping client not logged on standby
ARC6: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE

work around:-
======================================

create password file again on both rac node database and also on standby database

[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb1 password=oracle ignorecase=y force=y entries=5
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprimdb2 password=oracle ignorecase=y force=y entries=5
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwstbydb password=oracle ignorecase=y force=y entries=5

add the parameter on all databases
SEC_CASE_SENSITIVE_LOGON=FALSE      
SQL> show parameter SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           0

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@dc2 dbs]$ vi initstbydb.ora
[oracle@dc2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 22 05:59:26 2012

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  870457344 bytes
Fixed Size                  1339964 bytes
Variable Size             335547844 bytes
Database Buffers          528482304 bytes
Redo Buffers                5087232 bytes
Database mounted.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           0
SQL> show parameter SEC_CASE_SENSITIVE_LOGON=FALSE      
SQL> show parameter SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           0
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           0
SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  870457344 bytes
Fixed Size                  1339964 bytes
Variable Size             335547844 bytes
Database Buffers          528482304 bytes
Redo Buffers                5087232 bytes
Database mounted.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     37
Next log sequence to archive   0
Current log sequence           38
SQL>


verification
=============================================

(on primary database)

SQL> create tablespace new datafile size 2m;



SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
UNDOTBS2
NEW

7 rows selected.


(on standby database)

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     39
Next log sequence to archive   0
Current log sequence           40
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
UNDOTBS2

6 rows selected.

SQL> alter database recover managed standby database disconnect from session;

Database altered.


(on both RAC primary instances)

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     39
Next log sequence to archive   0
Current log sequence           40
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     39
Next log sequence to archive   0
Current log sequence           40
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
UNDOTBS2
NEW

7 rows selected.

SQL> select name,db_unique_name,database_role from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
PRIMDB    stbydb                         PHYSICAL STANDBY

Thanks and Regards,
Rajesh Kumar Govindarajan.

references:-
http://www.oracledba.org/10g/dr/10gR2_dataguard_RAC_to_RAC.html
http://jhdba.wordpress.com/2008/04/11/how-not-to-set-up-remote-log-shipping-dataguard-and-streams/
http://www.dba-oracle.com/t_ora_16191_primary_log_shipping_client_not_logged_on_standby_tips.htm

Steps to Rename RAC instance name

noreply@blogger.com (Rajeshkumar Govindarajan) — Sun, 22 Jan 2012 03:27:00 +0530

Steps to Rename RAC instance name

In this demo, The RAC database name:- RACDB
I have renamed the database instance racdb1 to rac1db (HOSTNAME DC1)
and instance racdb2 to rac2db (HOSTNAME DC2)
1. take a backup of the initracdb1.ora and initracdb2.ora files.

[oracle@dc1 dbs]$ cp initracdb1.ora initracdb1.ora_bkp
[oracle@dc1 dbs]$ cat initracdb1.ora
SPFILE='+DATA/proddb/spfileracdb.ora'

2. create a new pfile from spfile

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/proddb/spfileracdb.ora
SQL> create pfile='$ORACLE_HOME/dbs/initrac2db.ora' from spfile;

File created.

3. Shutdown the instance on all nodes

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

4. Rename or Edit the racdb1 and racdb2 to rac1db and rac2db respectively.

before changing the instance names in init.ora file

racdb2.__db_cache_size=708837376
racdb1.__db_cache_size=708837376
racdb2.__java_pool_size=4194304
racdb1.__java_pool_size=4194304
racdb2.__large_pool_size=4194304
racdb1.__large_pool_size=4194304
racdb1.__oracle_base='/u01/app/oracle'
racdb2.__oracle_base='/u01/app/oracle'
racdb2.__pga_aggregate_target=394264576
racdb1.__pga_aggregate_target=394264576
racdb2.__sga_target=1056964608
racdb1.__sga_target=1056964608
racdb2.__shared_io_pool_size=0
racdb1.__shared_io_pool_size=0
racdb2.__shared_pool_size=331350016
racdb1.__shared_pool_size=331350016
racdb2.__streams_pool_size=0
racdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/proddb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/proddb/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='racdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
racdb2.instance_number=2
racdb1.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1053818880
racdb2.thread=2
racdb1.thread=1
racdb2.undo_tablespace='UNDOTBS2'
racdb1.undo_tablespace='UNDOTBS1'

after editing

rac2db.__db_cache_size=708837376
rac1db.__db_cache_size=708837376
rac2db.__java_pool_size=4194304
rac1db.__java_pool_size=4194304
rac2db.__large_pool_size=4194304
rac1db.__large_pool_size=4194304
rac1db.__oracle_base='/u01/app/oracle'
rac2db.__oracle_base='/u01/app/oracle'
rac2db.__pga_aggregate_target=394264576
rac1db.__pga_aggregate_target=394264576
rac2db.__sga_target=1056964608
rac1db.__sga_target=1056964608
rac2db.__shared_io_pool_size=0
rac1db.__shared_io_pool_size=0
rac2db.__shared_pool_size=331350016
rac1db.__shared_pool_size=331350016
rac2db.__streams_pool_size=0
rac1db.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/proddb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/proddb/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='racdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
rac2db.instance_number=2
rac1db.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1053818880
rac2db.thread=2
rac1db.thread=1
rac2db.undo_tablespace='UNDOTBS2'
rac1db.undo_tablespace='UNDOTBS1'

5.create a new password file on all the nodes on new instance SID names.

[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwrac1db password=oracle
[oracle@dc1 dbs]$ 
[oracle@dc2 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwrac2db password=oracle

6.Add an entry in the /etc/oratab file on all the nodes
example:-
on host machine DC1
vi /etc/oratab
rac1db:/u01/app/oracle/product/11.2.0/db_1:N
on host machine DC2
vi /etc/oratab
rac2db:/u01/app/oracle/product/11.2.0/db_1:N

7.Startup the database with new init.ora file and create a spfile using new pfile.

[oracle@dc1 dbs]$ . oraenv
ORACLE_SID = [rac2db] ? rac1db
[oracle@dc1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 21 06:05:54 2012

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             339740916 bytes
Database Buffers          708837376 bytes
Redo Buffers                4673536 bytes
SQL> create SPFILE='+DATA/proddb/spfileracdb.ora' from pfile;

File created.

SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>

8.Edit/add the spfile location in the initrac1db.ora and initrac2db.ora file
example:-
[oracle@dc2 dbs]$ vi initrac2db.ora
SPFILE='+DATA/proddb/spfileracdb.ora'

[oracle@dc1 dbs]$ vi initrac2db.ora
SPFILE='+DATA/proddb/spfileracdb.ora'

9.remove the old instance name and add a new instance name using srvctl command.

[oracle@dc2 dbs]$ srvctl remove instance -i racdb1 -d racdb 
Remove instance from the database racdb? (y/[n]) y
[oracle@dc2 dbs]$ srvctl remove instance -i racdb2 -d racdb
Remove instance from the database racdb? (y/[n]) y

[oracle@dc2 dbs]$ srvctl add instance -i rac1db -d racdb -n dc1
[oracle@dc2 dbs]$ srvctl add instance -i rac2db -d racdb -n dc2
[oracle@dc2 dbs]$ srvctl start database -d racdb

10.Verify it.

SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
rac2db           OPEN
rac1db           OPEN

SQL> select name from v$database;

NAME
---------
RACDB

[oracle@dc2 dbs]$ srvctl config database -d racdb
Database unique name: racdb
Database name: 
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: 
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: racdb
Database instances: rac1db,rac2db
Disk Groups: 
Services: 
Database is administrator managed

Thanks and Regards,
Rajesh Kumar Govindarajan.

Rename RAC Database

noreply@blogger.com (Rajeshkumar Govindarajan) — Tue, 10 Jan 2012 02:57:00 +0530

Here is the document to rename RAC database and next post we can see how to rename rac instance.

here is an example.

renamed RAC database proddb to racdb in servers dc1 and dc2.

SQL> select instance_name,host_name from gv$instance;

INSTANCE_NAME    HOST_NAME
---------------- -------------------------
proddb1          dc1.localdomain
proddb2          dc2.localdomain

SQL> select name from v$database;

NAME
---------
PRODDB

taken backup of pfile initproddb1.ora and initproddb2.ora on both nodes.

[oracle@dc1 dbs]$ cat initproddb1.ora
SPFILE='+DATA/proddb/spfileproddb.ora'
[oracle@dc1 dbs]$ cp initproddb1.ora initproddb1.ora_orig 
[oracle@dc1 dbs]$ 

[oracle@dc2 ~]$ cd $ORACLE_HOME/dbs
[oracle@dc2 dbs]$ cat initproddb2.ora 
SPFILE='+DATA/proddb/spfileproddb.ora'
[oracle@dc2 dbs]$ cp initproddb2.ora initproddb2.ora_bkp
[oracle@dc2 dbs]$

create pfile from spfile.
taken controlfile backup to rename rac database.

SQL> create pfile='/tmp/initproddb1.ora' from spfile;

File created.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> --- or ----
SQL> 
SQL> alter database backup controlfile to trace as '/tmp/ctl_trc_bkp.txt';

Database altered.

stop the RAC database using srvctl command.

[oracle@dc1 ~]$ srvctl status database -d proddb
Instance proddb1 is running on node dc1
Instance proddb2 is running on node dc2
[oracle@dc1 ~]$ srvctl config database -d proddb
Database unique name: proddb
Database name: proddb
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/proddb/spfileproddb.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: proddb
Database instances: proddb1,proddb2
Disk Groups: DATA
Services: 
Database is administrator managed
[oracle@dc1 ~]$ 

[oracle@dc1 ~]$ srvctl stop database -d proddb
[oracle@dc1 ~]$ srvctl status database -d proddb
Instance proddb1 is not running on node dc1
Instance proddb2 is not running on node dc2
[oracle@dc1 ~]$

edit/replace proddb to racdb in the newly created pfile
and also the controlfile name.
change the value of the parameter cluster_database to false.

racdb2.__db_cache_size=708837376
racdb1.__db_cache_size=708837376
racdb2.__java_pool_size=4194304
racdb1.__java_pool_size=4194304
racdb2.__large_pool_size=4194304
racdb1.__large_pool_size=4194304
racdb2.__pga_aggregate_target=394264576
racdb1.__pga_aggregate_target=394264576
racdb2.__sga_target=1056964608
racdb1.__sga_target=1056964608
racdb2.__shared_io_pool_size=0
racdb1.__shared_io_pool_size=0
racdb2.__shared_pool_size=331350016
racdb1.__shared_pool_size=331350016
racdb2.__streams_pool_size=0
racdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/proddb/adump'
*.audit_trail='db'
#*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/proddb/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='racdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
racdb2.instance_number=2
racdb1.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1053818880
racdb2.thread=2
racdb1.thread=1
racdb2.undo_tablespace='UNDOTBS2'
racdb1.undo_tablespace='UNDOTBS1'

startup the database in nomount mode with newly created pfile.

[oracle@dc1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 7 17:35:55 2012

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

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/initproddb1.ora';
ORACLE instance started.

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             276826356 bytes
Database Buffers          771751936 bytes
Redo Buffers                4673536 bytes
SQL> 

create a script using backup of controlfile trace to re-create controlfile(to rename database)  
CREATE CONTROLFILE SET DATABASE "RACDB" RESETLOGS  NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+DATA/proddb/onlinelog/group_1.346.771671611'  SIZE 50M BLOCKSIZE 512,
GROUP 2 '+DATA/proddb/onlinelog/group_2.341.771671615'  SIZE 50M BLOCKSIZE 512
DATAFILE
'+DATA/proddb/datafile/system.256.771671395',
'+DATA/proddb/datafile/sysaux.282.771671401',
'+DATA/proddb/datafile/undotbs1.344.771671403',
'+DATA/proddb/datafile/users.343.771671403',
'+DATA/proddb/datafile/undotbs2.339.771671757'
CHARACTER SET WE8MSWIN1252
;

SQL> @'/tmp/create_ctl.sql';

Control file created.

SQL> sho parameter cluster_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects                string
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

change the value of the parameter cluster_database to true and startup the database.

SQL> startup pfile='/tmp/initproddb1.ora';
ORACLE instance started.

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             339740916 bytes
Database Buffers          708837376 bytes
Redo Buffers                4673536 bytes
Database mounted.
Database opened.

create new spfile using current pfile.

SQL> create SPFILE='+DATA/proddb/spfileracdb.ora' from pfile='/tmp/initproddb1.ora';

File created.

SQL> sho parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string
SQL> 
SQL>ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3,GROUP 4;

SQL> ALTER DATABASE OPEN RESETLOGS;

create a new init pfile in the dbs directory and copy and paste the spfile location.

[oracle@dc1 dbs]$ vi initracdb1.ora 
SPFILE='+DATA/proddb/spfileracdb.ora'
[oracle@dc1 dbs]$ scp initracdb1.ora dc2:$ORACLE_HOME/dbs/initracdb2.ora
initracdb1.ora                                100%   38     0.0KB/s   00:00    

create a new password file on both servers for database racdb.

[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwracdb1 password=oracle
[oracle@dc1 dbs]$ 

[oracle@dc2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwracdb2.ora password=oracle
[oracle@dc2 dbs]$ 

add an entry in the /etc/oratab file on both servers
[oracle@dc2 ~]$vi /etc/oratab
racdb2:/u01/app/oracle/product/11.2.0/db_1:N

[oracle@dc1 ~]$vi /etc/oratab
racdb1:/u01/app/oracle/product/11.2.0/db_1:N
[oracle@dc2 ~]$ . oraenv
ORACLE_SID = [oracle] ? racdb2
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 8 23:36:03 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             339740916 bytes
Database Buffers          708837376 bytes
Redo Buffers                4673536 bytes
Database mounted.
Database opened.
SQL> 

SQL> select name from v$database;

NAME
---------
RACDB

SQL> select instance_name,status from gv$Instance;

INSTANCE_NAME    STATUS
---------------- ------------
racdb1           OPEN
racdb2           OPEN

register the database with grid.

[oracle@dc1 dbs]$ srvctl status database -d racdb
PRCD-1120 : The resource for database racdb could not be found.
PRCR-1001 : Resource ora.racdb.db does not exist

[oracle@dc1 dbs]$ srvctl remove database -d proddb
Remove the database proddb? (y/[n]) y
[oracle@dc1 dbs]$ srvctl add database -d racdb -o $ORACLE_HOME
[oracle@dc1 dbs]$ srvctl add instance -d racdb -i racdb1 -n dc1
[oracle@dc1 dbs]$ srvctl add instance -d racdb -i racdb2 -n dc2

[oracle@dc1 dbs]$ srvctl status database -d racdb
Instance racdb1 is not running on node dc1
Instance racdb2 is not running on node dc2

[oracle@dc1 dbs]$ srvctl start database -d racdb
[oracle@dc1 dbs]$ srvctl status database -d racdb
Instance racdb1 is running on node dc1
Instance racdb2 is running on node dc2

finally, Change the instance names in the tnsnames.ora and listener.ora files.

regards,
Rajesh Kumar Govindarajan.

steps to upgrade 11.1.0.6.0 to 11.1.0.7.0

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Tue, 18 Oct 2011 17:32:00 +0530

steps to upgrade 11.1.0.6.0 to 11.1.0.7.0

Step1:

using winSCP or anyother tool copy the patch file p6890831_111070_Linux-x86-64.zip to the environment from the machine where you are having patch file.

Step2:
login : oracle
password:
Uncompress the binary source
$unzip p6890831_111070_Linux-x86-64.zip
Example:-
After unzipping , you can find the below folder Disk1
c111ptqhdd009: /home/oracle
$ cd /oracle/app/patches/
c111ptqhdd009: /oracle/app/patches
$ ls
Disk1 p6890831_111070_Linux-x86-64.zip

Step3:
Bring down the application

Step4:
login : oracle
password:
Bring down the database
sqlplus '/as sysdba'
sql>shutdown immediate;
sql>exit
>ps -ef |grep pmon

Make sure that oracle is not running
>lsnrctl stop
>lsnrctl status
Make sure that the listener is not running.
Take a cold database backup
Controlfiles, datafiles, redo logs, archive files, tempfiles must be included in the backup. If the files are in different paths, the same has to be backedup.
>mkdir /backup_date
>cp /App1/oracle/oradata/orcl/*.* /backup/backup_date
>cd $ORACLE_HOME/dbs
>cp inittest.ora inittest.ora_bkpdate
>cp spfiletest.ora spfiletest.or_bkpdate
>cp orapwtest.ora orapwtest.ora_bkpdate

Step5:
login : oracle
password:
Take a backup of home path
>mkdir product_bkp
>cd /App1/oracle/product
>tar czf /backup/oracle_11106.tar.gz 11.1.0

Step6:
dbhome, coraenv,oraenv backup
login : root
password:
#cd /usr/local/bin
#cp dbhome dhome_bkpdate
#cp coraenv coraenv_bkpdate
#cp oraenv oraenv_bkpdate

Step7:
login : oracle
Installation of patchset

Invoke the runInstaller using the software xming to install patch graphically.

cd /oracle/app/patches/Disk1/
>./runInstaller

Select the ORACLE_HOME to upgrade and then click next, the prerequisites steps will complete last click INSTALL. At the end up the installation it will ask to run root.sh as root user. We need root user password or sudo user privilege to run root.sh script. Follow the step 8

Step8:
login : root
password:
or
login : your_SUDU_USERNAME
password:

cd /App1/oracle/product/11.1.0/db_1
./root.sh
Or
Sudu ./root.sh

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /App1/oracle/product/11.1.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ... overwrite (Y/N) : Y
Copying oraenv to /usr/local/bin ... overwrite (Y/N) : Y
Copying coraenv to /usr/local/bin ... overwrite (Y/N) : Y

Step9:
login : oracle
password:

Prerequisite check before upgradation
cd $ORACLE_HOME/rdbms/admin
sqlplus '/as sysdba'
sql> startup upgrade;
sql>spool pre_upgrade.log
sql> @utlu111i.sql

If the all the values obtained above are valid, please proceed to step 10 , otherwise please correct it.

Step10:
Upgradation script execution
login : oracle
password:

cd $ORACLE_HOME/rdbms/admin
sqlplus '/as sysdba'
sql> spool upgrade_date.log
sql> @catupgrd.sql

Step11:
login : oracle
password:
Take check the validity, version of the installed components

sqlplus '/as sysdba'
sql>set pages 250
sql> set lines 1000
sql> col comp_name for a40
sql> select comp_name, version, status from dba_registry;

Note : The status column of the displayed output should be 'Valid' and the version column should be '11.1.0.7'
Upgradation ends with this step12. If in case any problem during the installation and the old version needs to be reverted back

sql>shutdown immediate;

Step12:
Recompiling the invalid objects
login : oracle
password:
cd $ORACLE_HOME/rdbms/admin
sqlplus '/as sysdba'
sql>startup;
sql>spool recompile.log
sql> @utlrp.sql

Fallback plan Or To revert back to old version (11.1.0.6.0)
1. untar the binary backup
2. Backup the database the current database and then replace them with cold back taken before applying the patch.

login : root
password:
cd /usr/local/bin
#cp dbhome_bkpdate dbhome
#cp coraenv_bkpdate coraenv
#cp oraenv_bkpdate oraenv

login : oracle
password:

oracle binary restoration
cd /App1/oracle/product
>\rm -rf 11.1.0
>mkdir 11.1.0
>cp /backup/oracle_11106.tar.gz 11.1.0
>cd 11.1.0
>gunzip oracle_11106.tar.gz
>tar -xvf oracle_11106.tar

Datafile backup restoration
cd /App/oracle/oradata/orcl/
>\rm *
>cd /backup/backup_date
>cp *.* /App1/oracle/oradata/orcl/
>cd $ORACLE_HOME/dbs
>cp inittest.ora_bkpdate inittest.ora
>cp spfiletest.ora_bkpdate spfiletest.ora
>cp orapwtest.ora_bkpdate orapwtest.ora

sqlplus '/as sysdba'
sql>startup;
sql>select * from v$version;
sql>select comp_name, version, status from dba_registry;

Output should be 11.1.0.6 for version column and valid for status column.

*******************************************************************************
Thank You

Physical Standby Database for Read/Write Testing and Reporting and revert back to the physical standby database using flashback restore point.

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Thu, 1 Sep 2011 23:58:00 +0530

Using a combination of Data Guard, restore points, and Flashback Database, a physical standby database can be opened temporarily in read/write mode for development, reporting, or testing purposes, and then flashed back to a point in the past to be reverted back to a physical standby database. When the database is flashed back, Data Guard automatically synchronizes the standby database with the primary database, without the need to re-create the physical standby database from a backup copy of the primary database.

You can repeat this cycle of activate, flashback and revert as many times as is necessary.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ on standby database +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

step 1:
Cancel Redo Apply and create a guaranteed restore point.


SQL> select flashback_on from v$database;



FLASHBACK_ON

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

NO



SQL> show parameter recovery  



NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /home/u01/app/oracle/flash_rec

                                                 overy_area

db_recovery_file_dest_size           big integer 2G

recovery_parallelism                 integer     0

SQL> 



SQL> alter database recover managed standby database cancel;



Database altered.



SQL> alter database recover managed standby database cancel;



Database altered.



SQL> create restore point before_switch_to_primary guarantee flashback database;



Restore point created.



SQL> select scn,name,time from v$restore_point;



       SCN

----------

NAME

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

TIME

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

    481243

BEFORE_SWITCH_TO_PRIMARY

31-AUG-11 10.49.34.000000000 AM



alertlog file 

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

Wed Aug 31 10:48:09 2011

MRP0: Background Media Recovery process shutdown (dbstby)

Wed Aug 31 10:48:10 2011

Managed Standby Recovery Canceled (dbstby)

Wed Aug 31 10:48:10 2011

Completed: alter database recover managed standby database cancel

Wed Aug 31 10:49:33 2011

Starting background process RVWR

RVWR started with pid=49, OS id=1629

Wed Aug 31 10:49:34 2011

Created guaranteed restore point BEFORE_SWITCH_TO_PRIMARY

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



SQL> select current_scn from v$database;



CURRENT_SCN

-----------

     481243



SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/u01/app/oracle/oradata/dbstby/arch/

Oldest online log sequence     47

Next log sequence to archive   0

Current log sequence           49

SQL>

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ on primary database +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Step 2:
Prepare the primary database to have the physical standby be diverged.


SQL> select current_scn from v$database;



CURRENT_SCN

-----------

     481860



SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/u01/app/oracle/oradata/dbtest/arch/

Oldest online log sequence     47

Next log sequence to archive   49

Current log sequence           49

SQL> 



SQL> alter system archive log current;



System altered.



SQL> alter system set log_archive_dest_state_2=defer;



System altered.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ on standby database +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Step 3 Activate the physical standby database.


SQL> alter database activate standby database;



Database altered.





SQL> startup mount force;

ORACLE instance started.



Total System Global Area  746586112 bytes

Fixed Size                  1221636 bytes

Variable Size             209718268 bytes

Database Buffers          532676608 bytes

Redo Buffers                2969600 bytes

Database mounted.

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;



Database altered.



SQL> alter database open;



Database altered.





SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/u01/app/oracle/oradata/dbstby/arch/

Oldest online log sequence     1

Next log sequence to archive   2

Current log sequence           2

SQL> 





Once the standby database has been activated, you can run reporting tools or perform other testing and activities for days or even weeks, independent of the primary database.



alert log file 

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

Wed Aug 31 10:54:18 2011

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[9]: Assigned to RFS process 8542

RFS[9]: Identified database type as 'physical standby'

Primary database is in MAXIMUM PERFORMANCE mode

Primary database is in MAXIMUM PERFORMANCE mode

RFS[9]: No standby redo logfiles created

Wed Aug 31 10:54:32 2011

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[10]: Assigned to RFS process 8884

RFS[10]: Identified database type as 'physical standby'

RFS[10]: No standby redo logfiles created

RFS[10]: Archived Log: '/home/u01/app/oracle/oradata/dbstby/arch/1_49_760329654.arc'

Wed Aug 31 10:55:33 2011

alter database activate standby database

Wed Aug 31 10:55:33 2011

ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (dbstby)

Wed Aug 31 10:55:35 2011

RESETLOGS after complete recovery through change 481244

Resetting resetlogs activation ID 1153152819 (0x44bbb733)

Online log /home/u01/app/oracle/oradata/dbstby/redo01.log: Thread 1 Group 1 was previously cleared

Online log /home/u01/app/oracle/oradata/dbstby/redo02.log: Thread 1 Group 2 was previously cleared

Online log /home/u01/app/oracle/oradata/dbstby/redo03.log: Thread 1 Group 3 was previously cleared

Standby became primary SCN: 481242

Wed Aug 31 10:55:35 2011

Setting recovery target incarnation to 5

Wed Aug 31 10:55:35 2011

Converting standby mount to primary mount.

Wed Aug 31 10:55:35 2011

ACTIVATE STANDBY: Complete - Database mounted as primary (dbstby)

Completed: alter database activate standby database

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

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ on primary database +
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


SQL> alter system switch logfile;



System altered.



SQL> /



System altered.



SQL> /



System altered.



SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/u01/app/oracle/oradata/dbtest/arch/

Oldest online log sequence     51

Next log sequence to archive   53

Current log sequence           53

SQL>


SQL> startup force mount

ORACLE instance started.



Total System Global Area  746586112 bytes

Fixed Size                  1221636 bytes

Variable Size             209718268 bytes

Database Buffers          532676608 bytes

Redo Buffers                2969600 bytes

Database mounted.

SQL> select name,scn,time from v$restore_point;



NAME                                  SCN TIME

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

BEFORE_SWITCH_TO_PRIMARY           481243 31-AUG-11 10.49.34.000000000 AM



SQL> flashback database to restore point BEFORE_SWITCH_TO_PRIMARY;



Flashback complete.



SQL> alter database convert to physical standby ;



Database altered.



SQL> startup mount force;

ORACLE instance started.



Total System Global Area  746586112 bytes

Fixed Size                  1221636 bytes

Variable Size             209718268 bytes

Database Buffers          532676608 bytes

Redo Buffers                2969600 bytes

Database mounted.



SQL> alter database recover managed standby database disconnect;



Database altered.



SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/u01/app/oracle/oradata/dbstby/arch/

Oldest online log sequence     53

Next log sequence to archive   0

Current log sequence           54

SQL>

reference:-
Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)
Part Number B14239-05

Duplicate a database without connecting to a target database and a recovery catalog (11gR2 New Features)

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Mon, 8 Aug 2011 02:34:00 +0530

Users can duplicate a database without connecting to a target database. The only requirements for this operation are a connection to a catalog and an auxiliary database. This new functionality is useful when the target database is not always available.
Users can also duplicate a database without connecting to a target database and a recovery catalog. In this case, the only requirement is that you provide a disk backup location where RMAN can find all the backups, datafile copies, archived logs, and control file copies for database duplication. This database duplication enhancement is helpful when it is not possible to connect to the target database and the recovery catalog.

here is an demo, In our example i am going to create a duplicate database with the name CLONEDB from the database DB2

1. Take backup of target database.
2. Copy the backupsets to the machine where you would like to clone the target database.
3. Create a new pfile for clone database or copy the pfile of target database and replace/edit the pfile entries for the clone database.
4. Create a password file
5. Create required folders for dump and database files.
6. Add an entry in /etc/oratab file
7. Create pfile for duplicate database
8. Add required TNS entry in tnsnames.ora file
9. startup nomount the clone/duplicate database.
10. Duplicate command in auxiliary instance.

  
SQL> select name from v$database;

NAME
--------------------------------------------------------
DB2

[oracle@node2 backupnew]$ . oraenv
ORACLE_SID = [db2] ? 
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@node2 backupnew]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Aug 8 00:31:43 2011

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

connected to target database: DB2 (DBID=1922019997)

RMAN> backup as compressed backupset database format '/u01/app/oracle/backup/%d_%s_%I_%T' plus archivelog format '/u01/app/oracle/backup/%d_%s_%I_%T';

RMAN>  backup archivelog all format '/u01/app/oracle/backup/%d_%s_%I_%T';

RMAN> EXIT;

[oracle@node2 backup]$ cd /u01/app/oracle/backup
[oracle@node2 backup]$ pwd
/u01/app/oracle/backup
[oracle@node2 backup]$ ls -ltr
total 463128
-rw-r----- 1 oracle dba  28600320 Aug  8 00:33 DB2_26_1922019997_20110808
-rw-r----- 1 oracle dba 265560064 Aug  8 00:35 DB2_27_1922019997_20110808
-rw-r----- 1 oracle dba   1114112 Aug  8 00:35 DB2_28_1922019997_20110808
-rw-r----- 1 oracle dba     94208 Aug  8 00:35 DB2_29_1922019997_20110808
-rw-r----- 1 oracle dba  89183232 Aug  8 00:36 DB2_30_1922019997_20110808
-rw-r----- 1 oracle dba  89189376 Aug  8 00:37 DB2_31_1922019997_20110808
******************************************************************************
copy the backups to the second server where you would like to clone the database

[oracle@node1 oracle]$ pwd
/u01/app/oracle
[oracle@node1 oracle]$ mkdir backup

[oracle@node2 backup]$ pwd
/u01/app/oracle/backup
[oracle@node2 backup]$ scp * oracle@192.168.2.101:/u01/app/oracle/backup
DB2_26_1922019997_20110808                    100%   27MB  13.6MB/s   00:02    
DB2_27_1922019997_20110808                    100%  253MB   6.5MB/s   00:39    
DB2_28_1922019997_20110808                    100% 1088KB   1.1MB/s   00:00    
DB2_29_1922019997_20110808                    100%   92KB  92.0KB/s   00:00    
DB2_30_1922019997_20110808                    100%   85MB   6.5MB/s   00:13    
DB2_31_1922019997_20110808                    100%   85MB  14.2MB/s   00:06    
[oracle@node2 backup]$ 

[oracle@node2 dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@node2 dbs]$ ls | grep init
initdb2.ora
init.ora
initorcl2.ora
[oracle@node2 dbs]$ scp initdb2.ora oracle@192.168.2.101:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initclonedb.ora

edit/replace the name and directory/folder for the clone database

eg:- I have replaced/edited the pfile as below
clonedb.__db_cache_size=541065216
clonedb.__java_pool_size=4194304
clonedb.__large_pool_size=4194304
clonedb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
clonedb.__pga_aggregate_target=260046848
clonedb.__sga_target=780140544
clonedb.__shared_io_pool_size=0
clonedb.__shared_pool_size=218103808
clonedb.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/clonedb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/clonedb/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='clonedb'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
*.open_cursors=300
*.pga_aggregate_target=258998272
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=776994816
*.undo_tablespace='UNDOTBS1'
db_file_name_convert='/u01/app/oracle/db2/','/u01/app/oracle/clonedb/'
log_file_name_convert='/u01/app/oracle/db2/','/u01/app/oracle/clonedb/'
*******************************************************************************
create a password file for clonedb 
*******************************************************************************
[oracle@node1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwclonedb password=oracle 

create required folders and directories for database files and dump files
[oracle@node1 clonedb]$ mkdir -p /u01/app/oracle/admin/clonedb/adump
[oracle@node1 clonedb]$ mkdir -p /u01/app/oracle/admin/clonedb/udump
[oracle@node1 clonedb]$ mkdir -p /u01/app/oracle/admin/clonedb/bdump
[oracle@node1 clonedb]$ mkdir -p /u01/app/oracle/admin/clonedb/cdump
[oracle@node1 clonedb]$ mkdir -p /u01/app/oracle/clonedb

add an entry in the /etc/oratab file
clonedb:/u01/app/oracle/product/11.2.0/dbhome_1:N       

[oracle@node1 clonedb]$ . oraenv
ORACLE_SID = [RAC1] ? clonedb
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@node1 clonedb]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 8 01:05:39 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1339344 bytes
Variable Size             222298160 bytes
Database Buffers          549453824 bytes
Redo Buffers                5296128 bytes
SQL> 

[oracle@node1 clonedb]$ . oraenv
ORACLE_SID = [RAC1] ? clonedb
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@node1 clonedb]$ rman auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Aug 8 01:06:32 2011

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

connected to auxiliary database: CLONEDB (not mounted)

RMAN> duplicate database to clonedb backup location '/u01/app/oracle/backup' nofilenamecheck; 


SQL> select name from v$database;

NAME
---------
CLONEDB

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

RMAN Tablespace Point-in-Time Recovery (TSPITR) 11gR2 Enhancements

noreply@blogger.com (Rajeshkumar Govindarajan) — Mon, 8 Aug 2011 02:19:00 +0530

One of the good new features in 11g Release 2 is that it enables us to recover from a case of a dropped tablespace. TSPITR (tablespace point in time recovery) has been around in earlier releases, but we could not recover a dropped tablespace.

here is an demo for your reference

  
 
22:33:43 SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                                TABLESPACE_NAME
-------------------------------------------------------- ---------------
/u01/app/oracle/db2/users01.dbf                          USERS
/u01/app/oracle/db2/undotbs01.dbf                        UNDOTBS1
/u01/app/oracle/db2/sysaux01.dbf                         SYSAUX
/u01/app/oracle/db2/system01.dbf                         SYSTEM

22:33:52 SQL> create tablespace newtbs1 datafile '/u01/app/oracle/db2/newtbs01.dbf' size 10m;

Tablespace created.

22:34:31 SQL> create user shyam identified by shyam default tablespace newtbs1;

User created.

22:35:24 SQL> grant connect,resource to shyam;

Grant succeeded.

22:35:57 SQL> conn shyam/shyam
Connected.
22:36:16 SQL> create table tb1 ( id number);

Table created.

22:36:26 SQL> insert into tb1 values (123);

1 row created.

Commit complete.
22:36:41 SQL> create table tb2 ( id number);

Table created.

22:37:01 SQL> insert into tb2 values (234);

1 row created.

Commit complete.
22:37:14 SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TB1                            TABLE
TB2                            TABLE


22:37:20 SQL> conn / as sysdba
Connected.
22:38:16 SQL> alter system switch logfile;

System altered.

22:38:33 SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     799576

[oracle@node2 backupnew]$ . oraenv
ORACLE_SID = [db2] ? 
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@node2 backupnew]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Aug 7 22:39:17 2011

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

connected to target database: DB2 (DBID=1922019997)

RMAN> backup as compressed backupset database format '/u01/app/oracle/backup/backupnew/%d_%s_%I_%T' plus archivelog format '/u01/app/oracle/backup/backupnew/%d_%s_%I_

%T';


Starting backup at 07-AUG-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=758566663
input archived log thread=1 sequence=4 RECID=2 STAMP=758566818
input archived log thread=1 sequence=5 RECID=3 STAMP=758566872
input archived log thread=1 sequence=6 RECID=4 STAMP=758574317
input archived log thread=1 sequence=7 RECID=5 STAMP=758574431
input archived log thread=1 sequence=8 RECID=6 STAMP=758574724
input archived log thread=1 sequence=9 RECID=7 STAMP=758575149
input archived log thread=1 sequence=10 RECID=8 STAMP=758575987
input archived log thread=1 sequence=11 RECID=9 STAMP=758581906
input archived log thread=1 sequence=12 RECID=10 STAMP=758581906
input archived log thread=1 sequence=13 RECID=11 STAMP=758582156
input archived log thread=1 sequence=14 RECID=12 STAMP=758582234
input archived log thread=1 sequence=15 RECID=13 STAMP=758582326
input archived log thread=1 sequence=16 RECID=14 STAMP=758582432
input archived log thread=1 sequence=17 RECID=15 STAMP=758582528
input archived log thread=1 sequence=18 RECID=16 STAMP=758582961
input archived log thread=1 sequence=19 RECID=17 STAMP=758584593
input archived log thread=1 sequence=20 RECID=18 STAMP=758587114
input archived log thread=1 sequence=21 RECID=19 STAMP=758587224
channel ORA_DISK_1: starting piece 1 at 07-AUG-11
channel ORA_DISK_1: finished piece 1 at 07-AUG-11
piece handle=/u01/app/oracle/backup/backupnew/DB2_22_1922019997_20110807 tag=TAG20110807T224025 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 07-AUG-11

Starting backup at 07-AUG-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/db2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/db2/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/db2/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/db2/newtbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/db2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-AUG-11
channel ORA_DISK_1: finished piece 1 at 07-AUG-11
piece handle=/u01/app/oracle/backup/backupnew/DB2_23_1922019997_20110807 tag=TAG20110807T224041 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 07-AUG-11
channel ORA_DISK_1: finished piece 1 at 07-AUG-11
piece handle=/u01/app/oracle/backup/backupnew/DB2_24_1922019997_20110807 tag=TAG20110807T224041 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-AUG-11

Starting backup at 07-AUG-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=22 RECID=20 STAMP=758587349
channel ORA_DISK_1: starting piece 1 at 07-AUG-11
channel ORA_DISK_1: finished piece 1 at 07-AUG-11
piece handle=/u01/app/oracle/backup/backupnew/DB2_25_1922019997_20110807 tag=TAG20110807T224229 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-AUG-11

RMAN> exit


Recovery Manager complete.
[oracle@node2 backupnew]$ 

22:38:46 SQL> conn shyam/shyam
Connected.
22:43:37 SQL> create table tb3 (id number);

Table created.

22:47:33 SQL> insert into tb3 values(123);

1 row created.

Commit complete.
22:48:27 SQL> select * from tb3;

        ID
----------
       123

22:48:37 SQL> conn / as sysdba
Connected.
22:48:47 SQL> alter system switch logfile;

System altered.

22:48:54 SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     800205

22:49:06 SQL> 


22:50:14 SQL> drop tablespace newtbs1 including contents and datafiles;

Tablespace dropped.

22:51:06 SQL> 


RMAN> recover tablespace newtbs1 until scn 800205  auxiliary destination '/u01/app/oracle/backup/backupnew';

Starting recover at 07-AUG-11
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='Aunx'

initialization parameters used for automatic instance:
db_name=DB2
db_unique_name=Aunx_tspitr_DB2
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/u01/app/oracle/backup/backupnew
log_archive_dest_1='location=/u01/app/oracle/backup/backupnew'
#No auxiliary parameter file used


starting up automatic instance DB2

Oracle instance started

Total System Global Area     292933632 bytes

Fixed Size                     1336092 bytes
Variable Size                100666596 bytes
Database Buffers             184549376 bytes
Redo Buffers                   6381568 bytes
Automatic instance created


List of tablespaces that have been dropped from the target database:
Tablespace newtbs1

contents of Memory Script:
{
# set requested point in time
set until  scn 800205;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log 
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 07-AUG-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=25 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/backupnew/DB2_24_1922019997_20110807
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/backupnew/DB2_24_1922019997_20110807 tag=TAG20110807T224041
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/backup/backupnew/DB2/controlfile/o1_mf_73xmbtfc_.ctl
Finished restore at 07-AUG-11

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until  scn 800205;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  5 to 
 "/u01/app/oracle/db2/newtbs01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 5;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 07-AUG-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/db2/newtbs01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/backupnew/DB2_23_1922019997_20110807
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/backupnew/DB2_23_1922019997_20110807 tag=TAG20110807T224041
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 07-AUG-11

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=758588962 file name=/u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_system_73xmc12w_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=758588962 file name=/u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_undotbs1_73xmc148_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=758588962 file name=/u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_sysaux_73xmc13r_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 800205;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  5 online";
# recover and open resetlogs
recover clone database tablespace  "NEWTBS1", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  5 online

Starting recover at 07-AUG-11
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/flash_recovery_area/DB2/archivelog/2011_08_07/o1_mf_1_22_73xkvxsz_.arc
archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/flash_recovery_area/DB2/archivelog/2011_08_07/o1_mf_1_23_73xl7yvr_.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/flash_recovery_area/DB2/archivelog/2011_08_07/o1_mf_1_24_73xlmfdj_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/DB2/archivelog/2011_08_07/o1_mf_1_22_73xkvxsz_.arc thread=1 sequence=22
archived log file name=/u01/app/oracle/flash_recovery_area/DB2/archivelog/2011_08_07/o1_mf_1_23_73xl7yvr_.arc thread=1 sequence=23
archived log file name=/u01/app/oracle/flash_recovery_area/DB2/archivelog/2011_08_07/o1_mf_1_24_73xlmfdj_.arc thread=1 sequence=24
media recovery complete, elapsed time: 00:00:03
Finished recover at 07-AUG-11

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  NEWTBS1 read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/backup/backupnew''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/backup/backupnew''";
}
executing Memory Script

sql statement: alter tablespace  NEWTBS1 read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/backup/backupnew''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/backup/backupnew''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_Aunx":  
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_Aunx" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_Aunx is:
   EXPDP>   /u01/app/oracle/backup/backupnew/tspitr_Aunx_72915.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace NEWTBS1:
   EXPDP>   /u01/app/oracle/db2/newtbs01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_Aunx" successfully completed at 23:11:06
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_Aunx" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_Aunx":  
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_Aunx" successfully completed at 23:11:35
Import completed


contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  NEWTBS1 read write';
sql 'alter tablespace  NEWTBS1 offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace  NEWTBS1 read write

sql statement: alter tablespace  NEWTBS1 offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_temp_73xmgrpk_.tmp deleted
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/onlinelog/o1_mf_3_73xmglwq_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/onlinelog/o1_mf_2_73xmgk7t_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/onlinelog/o1_mf_1_73xmghq1_.log deleted
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_sysaux_73xmc13r_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_undotbs1_73xmc148_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/datafile/o1_mf_system_73xmc12w_.dbf deleted
auxiliary instance file /u01/app/oracle/backup/backupnew/DB2/controlfile/o1_mf_73xmbtfc_.ctl deleted
Finished recover at 07-AUG-11

RMAN> 



23:14:57 SQL>  select file_name,tablespace_name from dba_data_files;

FILE_NAME                                                TABLESPACE_NAME
-------------------------------------------------------- ---------------
/u01/app/oracle/db2/users01.dbf                          USERS
/u01/app/oracle/db2/undotbs01.dbf                        UNDOTBS1
/u01/app/oracle/db2/sysaux01.dbf                         SYSAUX
/u01/app/oracle/db2/system01.dbf                         SYSTEM
/u01/app/oracle/db2/newtbs01.dbf                         NEWTBS1


SQL> alter tablespace newtbs1 online;

Tablespace altered.


23:15:28 SQL> conn shyam/shyam
Connected.
23:15:54 SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TB1                            TABLE
TB2                            TABLE
TB3                            TABLE

RMAN cloning in archive log mode

noreply@blogger.com (Rajeshkumar Govindarajan) — Mon, 23 May 2011 02:44:00 +0530

i have cloned the database SUNU in RAC2 machine to RAC1 Machine in the name DBDEMO.

****************************************************************
RAC2 MACHINE
****************************************************************
ORACLE_SID = [test] ? sunu
[oracle@rac2 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 15 19:27:41 2011

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
SQL>

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? sunu
[oracle@rac2 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 15 19:34:50 2011

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

connected to target database: SUNU (DBID=555109830)

RMAN> backup full database format '/home/u01/app/backup/%d_%U.bkp' plus archivelog format '/home/u01/app/backup/%d_%U.bkp';

[oracle@rac2 backup]$ pwd
/home/u01/app/backup
[oracle@rac2 backup]$ ls -ltr
total 516356
-rw-r----- 1 oracle oinstall 2124800 May 15 19:37 SUNU_01mcdp74_1_1.bkp
-rw-r----- 1 oracle oinstall 518946816 May 15 19:37 SUNU_02mcdp76_1_1.bkp
-rw-r----- 1 oracle oinstall 7143424 May 15 19:37 SUNU_03mcdp8j_1_1.bkp
-rw-r----- 1 oracle oinstall 2560 May 15 19:37 SUNU_04mcdp8l_1_1.bkp
[oracle@rac2 backup]$

step 2:-
copy the initsunu.ora file to rac1 machine and also create a password file in rac1 machine for dbdemo database
add an entry in oratab file in rac1 machine
also move the backup files to rac1 machine to exact folder as it is in rac2(source) machine

note: make sure the folder is available in RAC1 machine
here we are taking backup in /home/u01/app/backup so now i am going to create a folder in RAC1 machine and also moving the backup from RAC2 to RAC1.
[oracle@rac2 backup]$ scp * oracle@192.168.1.1:/home/u01/app/backup/

or you can create a symbolic link for the backup location

example, in machine RAC1(the machine/host/server i dont have exact folder or exact mount point as it is in RAC2 machine(target)
do like this,

[oracle@rac2 dbs]$ cd /home/u01/app/backup/
[oracle@rac2 backup]$ pwd
/home/u01/app/backup
[oracle@rac2 backup]$ scp * oracle@192.168.1.1:/home/u01/app/test_bk/

and then create a symbolic link:
[oracle@rac1 app]$ ln -s /home/u01/app/test_bk /home/u01/app/backup

[oracle@rac1 app]$ ls
backup oracle test_bk
[oracle@rac1 app]$ cd backup/
[oracle@rac1 backup]$ ls -ltr
total 671932
-rw-r----- 1 oracle oinstall 7029760 May 22 12:35 SUNU_09mcoc44_1_1.bkp
-rw-r----- 1 oracle oinstall 97460224 May 22 12:36 SUNU_0amcoc48_1_1.bkp
-rw-r----- 1 oracle oinstall 6144 May 22 12:36 SUNU_0cmcoc7a_1_1.bkp
-rw-r----- 1 oracle oinstall 1097728 May 22 12:36 SUNU_0bmcoc78_1_1.bkp
-rw-r----- 1 oracle oinstall 39838720 May 22 12:36 SUNU_0dmcvels_1_1.bkp
-rw-r----- 1 oracle oinstall 534740992 May 22 12:38 SUNU_0emcvem5_1_1.bkp
-rw-r----- 1 oracle oinstall 27648 May 22 12:38 SUNU_0gmcvepg_1_1.bkp
-rw-r----- 1 oracle oinstall 7143424 May 22 12:38 SUNU_0fmcvepe_1_1.bkp
[oracle@rac1 backup]$

now we are going to copy the init files from RAC2 to RAC1 machine using scp , as shown below.
you can mention hostname or ipaddress in scp command

[oracle@rac2 dbs]$ scp initsunu.ora oracle@192.168.1.1:$ORACLE_HOME/dbs/
initsunu.ora 100% 1070 1.0KB/s 00:00
***********************************************************************
IN RAC1 MACHINE
***********************************************************************

i copied the initsunu.ora file to the RAC1 machine and now i am going to replace
the database name sunu by dbdemo
also,
adding the parameter log_file_name_convert and db_file_name_convert parameters to clone database with different directory structure.

dbdemo.__db_cache_size=541065216
dbdemo.__java_pool_size=4194304
dbdemo.__large_pool_size=4194304
dbdemo.__shared_pool_size=192937984
dbdemo.__streams_pool_size=0
*.audit_file_dest='/home/u01/app/oracle/admin/dbdemo/adump'
*.background_dump_dest='/home/u01/app/oracle/admin/dbdemo/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/u01/app/oracle/oradata/dbdemo/control01.ctl','/home/u01/app/oracle/oradata/dbdemo/control02.ctl','/home/u01/app/oracle/oradata/dbdemo/control03.ctl'
*.core_dump_dest='/home/u01/app/oracle/admin/dbdemo/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dbdemo'
*.db_recovery_file_dest='/home/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbdemoXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=248512512
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/u01/app/oracle/admin/dbdemo/udump'
db_file_name_convert=(/home/u01/app/oracle/oradata/sunu/,/home/u01/app/oracle/oradata/dbdemo/)
log_file_name_convert=(/home/u01/app/oracle/oradata/sunu/,/home/u01/app/oracle/oradata/dbdemo/)
*.sga_target=745537536

Now i am going to add an entry in the /etc/oratab file for dbdemo database

dbdemo:/home/u01/app/oracle/product/10.2.0/db_1:N

going to create required folders for the dbdemo database files

[oracle@rac1 dbdemo]$ pwd
/home/u01/app/oracle/admin/dbdemo

[oracle@rac1 dbdemo]$ ls
adump bdump cdump dpdump hdump pfile udump
[oracle@rac1 dbdemo]$

[oracle@rac1 oradata]$ pwd
/home/u01/app/oracle/oradata
[oracle@rac1 oradata]$ ls
dbdemo
[oracle@rac1 oradata]$

creating a pwd file for dbdemo database

[oracle@rac1 oradata]$ orapwd file=$ORACLE_HOME/dbs/orapwdbdemo password=oracle
bash: orapwd: command not found
[oracle@rac1 oradata]$ . oraenv
ORACLE_SID = [orcl] ? dbdemo
[oracle@rac1 oradata]$ orapwd file=$ORACLE_HOME/dbs/orapwdbdemo password=oracle
[oracle@rac1 oradata]$

now i am going to start dbdemo database in nomount mode
and then do cloning

before that we should have add the tns entry of sunu database in RAC1 machine

[oracle@rac1 backup]$ cd /home/u01/app/oracle/product/10.2.0/db_1/network/admin/
[oracle@rac1 admin]$ ls -ltr

add/check this entries available in both machines else add this entry in tnsnames.ora file of both machines.

SUNU =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sunu)
)
)

DBDEMO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbdemo)
)
)
[oracle@rac1 admin]$ tnsping DBDEMO

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:31:21

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbdemo)))
OK (0 msec)
[oracle@rac1 admin]$ tnsping SUNU

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:31:26

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sunu)))
OK (10 msec)
[oracle@rac1 admin]$

[oracle@rac2 admin]$ tnsping DBDEMO

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:29:09

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dbdemo)))
OK (10 msec)
[oracle@rac2 admin]$ tnsping SUNU

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-MAY-2011 20:29:15

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/home/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sunu)))
OK (10 msec)
[oracle@rac2 admin]$

we are checking connectivity b/n the two machines using tnsping command.

now all the pre cloning steps finished.

now we can start the cloning in RAC1 machine.

startup nomount the DATABASE dbdemo in RAC1 machine

making/creating flash recovery area in RAC1 machine

[oracle@rac1 oracle]$ mkdir -p /home/u01/app/oracle/flash_recovery_area
[oracle@rac1 oracle]$ ls
admin flash_recovery_area oradata oraInventory product
[oracle@rac1 oracle]$ cd flash_recovery_area/
[oracle@rac1 flash_recovery_area]$ ls
[oracle@rac1 flash_recovery_area]$ mkdir dbdemo
[oracle@rac1 flash_recovery_area]$ ls
dbdemo

[oracle@rac1 dbs]$ . oraenv
ORACLE_SID = [dbdemo] ?
[oracle@rac1 dbs]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 19 20:40:50 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 746586112 bytes
Fixed Size 1221636 bytes
Variable Size 201329660 bytes
Database Buffers 541065216 bytes
Redo Buffers 2969600 bytes
SQL>

[oracle@rac1 flash_recovery_area]$ . oraenv
ORACLE_SID = [ORCL1] ? dbdemo
[oracle@rac1 flash_recovery_area]$ rman target sys/oracle@SUNU auxiliary /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 19 20:42:06 2011

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

connected to target database: SUNU (DBID=555109830)
connected to auxiliary database: DBDEMO (not mounted)

RMAN> duplicate target database to dbdemo;

[oracle@rac1 dbdemo]$ . oraenv
ORACLE_SID = [dbdemo] ?
[oracle@rac1 dbdemo]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 19 21:20:23 2011

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select name from v$database;

NAME
---------
DBDEMO

ORA-01591: lock held by in-doubt distributed transaction

noreply@blogger.com (Rajeshkumar Govindarajan) — Wed, 27 Apr 2011 00:09:00 +0530

ORA-01591: lock held by in-doubt distributed transaction
i restarted the server but of no use

solution:

SQL> select local_tran_id from dba_2pc_pending;

LOCAL_TRAN_ID

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

6.44.24689

SQL> rollback force '6.44.24689';

Rollback complete.

SQL> exec dbms_transaction.purge_lost_db_entry('6.44.24689');

PL/SQL procedure successfully completed.

SQL> select local_tran_id from dba_2pc_pending;

no rows selected

reference:

http://asktom.oracle.com/

Happy New Year 2011

noreply@blogger.com (Rajeshkumar Govindarajan) — Sat, 1 Jan 2011 12:40:00 +0530

ORA-00959: tablespace '_$deleted$3$0' does not exist Error

noreply@blogger.com (RAJESHKUMAR GOVINDARAJAN) — Tue, 30 Nov 2010 21:49:00 +0530

20-AUG-2010 in Oracle forum OTN, a interesting thread , here i documented it for future reference.

thread: ORA-00959: tablespace '_$deleted$3$0' does not exist

i thank the forum Opponent Phiri Lighton and participants especially nicolas,ogan and chinar here.

here, we will see the examples , What are the possiblities to get ORA-950 Error

Changes
Made changes to the TEMP tablespace setup.
1. Created a temporary tablespace called TEMP1.
2. Set this tablespace to become the default temporary tablespace.
3. Dropped the existing permanent TEMP tablespace.
4. Renamed the default temporary tablesapce TEMP1 to TEMP.
Reason for this problem/issue
There is an open unpublished bug where basic problem is that a tablespace is renamed to one that use
to exist on the database. Here are the steps that will cause this problem.
create tablespace aa
create tablespace bb
drop tablespace aa
rename bb to aa
Then why it occurs is because when a tablespace is dropped, its ts$ entry is set to an invalid state, but it
is not deleted. When a rename is done, if the target name already has a ts$ entry, then during the
rename, because we cannot have two ts$ entries with the same name, the _$deleted$ construct is
introduced. The change is not taken care of properly, and is flushed to other dictionary tables
causing this problem.
Solution
To resolve in this case, just alter the user to have the new temporary tablespace name, replacing the
_$deleted entry.
SQL> create temporary tablespace temp2 tempfile '+GROUP01' size 20M;
Tablespace created.
SQL> create temporary tablespace temp3 tempfile '+GROUP01' size 20M;
Tablespace created.
SQL> create user testuser identified by testuser temporary tablespace temp2;
User created.
SQL> drop tablespace temp2;
Tablespace dropped.
SQL> alter tablespace temp3 rename to temp2;
Tablespace altered.
- Now look in ts$ and will see the _$deleted construct which includes the ts#.
SQL> select ts#, name from ts$;
TS# NAME
---------- ------------------------------
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
3 TEMP
4 USERS
5 UNDO
6 MYDATA
7 MYINDEXES
8 TESTCASES
9 _$deleted$9$0
10 TEMP2
11 rows selected.
- And when you check the users temporary_tablespace, it too was changed to the _$deleted construct
which causes all the problems.
SQL> select username, temporary_tablespace
2 from dba_users where username = 'TESTUSER';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TESTUSER _$deleted$9$0
- To resolve, just alter the user to a valid temporary tablespace.
SQL> alter user testuser temporary tablespace temp2;
User altered.
SQL> select username, temporary_tablespace
2 from dba_users where username = 'TESTUSER';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TESTUSER TEMP2
To avoid this problem altogether, until the bug is fixed, do not rename a tablespace to one that use to
exist on the database. For example, here would be steps to complete the temp tablespace change.
create tablespace NEWTEMP
... change everyone and default temporary tablespace NEWTEMP
drop tablespace TEMP ... exisiting one
create tablespace TEMP ... building it the way you want
.... flip everyone back and default temporary tablespace TEMP
drop tablespace NEWTEMP

reference: ORACLE OTN Forum, MetaLink