Thursday, 31 December 2015

Bidirectional DDL replication in goldengate

=====>Node 1 details

OS details:
[oracle@gg1 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)

hostname: gg1.example.com

database details:
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Database name : Dev1

golden gate details:

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

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


====>Node2 details

OS details:

[oracle@gg2 ~]$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 5.4 (Tikanga)

hostname: gg2.example.com

database details:
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Database name : Dev2

golden gate details:

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

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


====>TNS configuration both node..

edit tnsname.ora

node 1:

DEV2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = gg2.example.com)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dev2)
    )
  )

DEV1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = gg1.example.com)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dev1)
    )
  )

node 2:


DEV2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = gg2.example.com)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dev2)
    )
  )

DEV1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = gg1.example.com)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dev1)
    )
  )


====>On source database


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT log_mode FROM v$database;

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

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;

FOR SUPPLEME
--- --------
NO  NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;

FOR SUPPLEME
--- --------
NO  YES

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;

FOR SUPPLEME
--- --------
YES YES

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> CREATE USER ggate IDENTIFIED BY oracle;

User created.

SQL> grant create session to ggate;

Grant succeeded.


SQL> grant dba to ggate;

Grant succeeded.


SQL> alter system set undo_retention=86400 scope=both;

System altered.

SQL> grant flashback any table to ggate;

Grant succeeded.

SQL>



==>On target server


SQL> SELECT log_mode FROM v$database;

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

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;

FOR SUPPLEME
--- --------
NO  NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;

FOR SUPPLEME
--- --------
NO  YES

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;

FOR SUPPLEME
--- --------
YES YES

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> CREATE USER ggate IDENTIFIED BY ggate;

User created.

SQL> grant create session to ggate;

Grant succeeded.

SQL> grant dba to ggate;

Grant succeeded.


SQL> alter system set undo_retention=86400 scope=both;

System altered.

SQL> grant flashback any table to ggate;

Grant succeeded.

SQL>

============> run below sql script on both node(server)

scrip location GG_HOME:
/u01/app/oracle/product/gg

SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate;
SQL> @/ddl_enable.sql


====>Source

GGSCI (gg1.example.com) 3> dblogin userid ggate, Password ggate
Successfully logged into database.

GGSCI (gg1.example.com) 4> ADD CHECKPOINTTABLE ggate.OGGCHKPT

GGSCI (gg1.example.com) 5> add trandata scott.*

=====>target

GGSCI (gg1.example.com) 3> dblogin userid ggate, Password ggate
Successfully logged into database.

GGSCI (gg1.example.com) 4> ADD CHECKPOINTTABLE ggate.OGGCHKPT

GGSCI (gg1.example.com) 5> add trandata scott.*


Note:creating extract and replicate process on both server.

====>Source

GGSCI (gg1.example.com) 2> edit param mgr

PORT 7809
DYNAMICPORTLIST 7810-7820
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/dirdat/rx*, USECHECKPOINTS, MINKEEPHOURS 2


GGSCI (gg1.example.com) 4> edit param RX1        -----extract process

EXTRACT rx1
USERID ggate, PASSWORD ******
EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx
DDL INCLUDE ALL
TranLogOptions ExcludeUser ggate
TABLE scott.*;

GGSCI (gg1.example.com) 5> edit param DP1       -------datapump process

EXTRACT dp1
USERID ggate, PASSWORD ******
RMTHOST gg2.example.com, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/rt
DDL INCLUDE ALL
TABLE scott.*;


GGSCI (gg1.example.com) 6> edit param REP1      ------replication process

REPLICAT rep1
USERID ggate, PASSWORD ******
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/gg/discards, PURGE
HANDLECOLLISIONS
--COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
--RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, IGNORE)),
--RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),
--RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
MAP scott.*, TARGET scott.*;

GGSCI (gg1.example.com) 7> ADD EXTRACT rx1, TRANLOG, BEGIN NOW

GGSCI (gg1.example.com) 8> ADD EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx, EXTRACT rx1

GGSCI (gg1.example.com) 9> ADD EXTRACT dp1 EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/rx

GGSCI (gg1.example.com) 10> ADD RMTTRAIL /u01/app/oracle/product/gg/dirdat/rt, EXTRACT dp1

GGSCI (gg1.example.com) 11> ADD REPLICAT rep1, EXTTRAIL /u01/app/oracle/product/gg/dirdat/rt CHECKPOINTTABLE rx025n.OGGCHKPT
REPLICAT added.

GGSCI (gg1.example.com) 20> Info ExtTrail *

       Extract Trail: /u01/app/oracle/product/gg/dirdat/rt
             Extract: DP1
               Seqno: 3
                 RBA: 1513
           File Size: 100M

       Extract Trail: /u01/app/oracle/product/gg/dirdat/rx
             Extract: EX1
               Seqno: 3
                 RBA: 1243
           File Size: 100M


========> On targate

GGSCI (gg2.example.com) 2> edit param mgr

PORT 7809
DYNAMICPORTLIST 7810-7820
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/dirdat/rx*, USECHECKPOINTS, MINKEEPHOURS 2


GGSCI (gg2.example.com) 4> edit param RX1        -----extract process

EXTRACT rx1
USERID ggate, PASSWORD ******
EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx
DDL INCLUDE ALL
TranLogOptions ExcludeUser ggate
TABLE scott.*;

GGSCI (gg2.example.com) 5> edit param DP1       -------datapump process

EXTRACT dp1
USERID ggate, PASSWORD ******
RMTHOST gg2.example.com, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/tx
DDL INCLUDE ALL
TABLE scott.*;


GGSCI (gg2.example.com) 6> edit param REP1      ------replication process

REPLICAT rep1
USERID ggate, PASSWORD ******
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/gg/discards, PURGE
HANDLECOLLISIONS
--COMPARECOLS (ON UPDATE ALL, ON DELETE ALL),
--RESOLVECONFLICT (DELETEROWEXISTS, (DEFAULT, IGNORE)),
--RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE)),
--RESOLVECONFLICT (DELETEROWMISSING, (DEFAULT, DISCARD));
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
MAP scott.*, TARGET scott.*;


GGSCI (gg2.example.com) 7> ADD EXTRACT ex1, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (gg2.example.com) 8> ADD EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx, EXTRACT rx1
EXTTRAIL added.

GGSCI (gg2.example.com) 9> ADD EXTRACT dp1 EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/rx
EXTRACT added.

GGSCI (gg2.example.com) 10> ADD RMTTRAIL /u01/app/oracle/product/gg/dirdat/tx, EXTRACT dp1
RMTTRAIL added

GGSCI (gg2.example.com) 11> ADD REPLICAT rep1, EXTTRAIL /u01/app/oracle/product/gg/dirdat/tx CHECKPOINTTABLE rx025n.OGGCHKPT
REPLICAT added.


GGSCI (gg2.example.com) 30> Info ExtTrail *

       Extract Trail: /u01/app/oracle/product/gg/dirdat/tx
             Extract: DP1
               Seqno: 0
                 RBA: 0
           File Size: 100M

       Extract Trail: /u01/app/oracle/product/gg/dirdat/rx
             Extract: EX1
               Seqno: 0
                 RBA: 0
           File Size: 100M


======> start gg extract,datapump process on source

- start manager process
- start extract process
- start datapump process

GGSCI (gg1.example.com) 33> start manager

Manager started.


GGSCI (gg1.example.com) 34> start EXTRACT EX1

Sending START request to MANAGER ...
EXTRACT EX1 starting


GGSCI (gg1.example.com) 35> start EXTRACT DP1

Sending START request to MANAGER ...
EXTRACT DP1 starting                       


GGSCI (gg1.example.com) 36> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DP1         00:00:00      00:00:10
EXTRACT     RUNNING     RX1         00:00:00      00:00:03
REPLICAT    STOPPED     REP1        00:00:00      00:20:17

note: replicate process we will start after target extract and replication process.

======> start gg extract,datapump process on targate

- start manager process
- start extract process
- start datapump process

GGSCI (gg2.example.com) 47> start extract EX1

Sending START request to MANAGER ...
EXTRACT EX1 starting


GGSCI (gg2.example.com) 48>  start extract DP1

Sending START request to MANAGER ...
EXTRACT DP1 starting


GGSCI (gg2.example.com) 49> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DP1         00:00:00      00:00:07
EXTRACT     RUNNING     RX1         00:00:00      00:00:04
REPLICAT    STOPPED     REP1        00:00:00      00:00:09

note: replicate process we will start after target extract and replication process.

=======> start gg replicate process on target

GGSCI (gg2.example.com) 50> start REPLICAT REP1

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (gg1.example.com) 52> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DP1         00:00:00      00:00:03
EXTRACT     RUNNING     RX1         00:00:00      00:00:01
REPLICAT    RUNNING     REP1        00:00:00      00:00:04


=====> start gg replicate process on source

GGSCI (gg1.example.com) 66> start REPLICAT REP1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (gg1.example.com) 67> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DP1         00:00:00      00:00:07
EXTRACT     RUNNING     RX1         00:00:00      00:00:04
REPLICAT    RUNNING     REP1        00:00:00      00:00:09

======> create on table on source database (on source)

SQL> create table t2(id number(10) primary key);

Table created.

SQL> select TABLE_NAME from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
EMP1
T1
T2

7 rows selected.

===> insert one row into dept

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        66 marketing      new york
        68 testing        perth
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

6 rows selected.

SQL> insert into dept values(11,'test','BROOME');

1 row created.

SQL> commit;

Commit complete.

====> GG process stats


GGSCI (gg1.example.com) 9> stats EX1

Sending STATS request to EXTRACT EX1 ...

Start of Statistics at 2016-01-01 02:39:45.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                         1.00
        Mapped operations                                  1.00
        Unmapped operations                                0.00
        Other operations                                   0.00
        Excluded operations                                0.00

Extracting from SCOTT.DEPT to SCOTT.DEPT:

*** Total statistics since 2016-01-01 02:27:07 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Daily statistics since 2016-01-01 02:27:07 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Hourly statistics since 2016-01-01 02:27:07 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Latest statistics since 2016-01-01 02:27:07 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

End of Statistics.

GGSCI (gg1.example.com) 7> stats DP1

Sending STATS request to EXTRACT DP1 ...

Start of Statistics at 2016-01-01 02:31:14.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                         1.00
        Mapped operations                                  0.00
        Unmapped operations                                0.00
        Other operations                                   0.00
        Excluded operations                                0.00

Output to /u01/app/oracle/product/gg/dirdat/rt:

Extracting from SCOTT.DEPT to SCOTT.DEPT:

*** Total statistics since 2016-01-01 02:30:56 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Daily statistics since 2016-01-01 02:30:56 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Hourly statistics since 2016-01-01 02:30:56 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Latest statistics since 2016-01-01 02:30:56 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

End of Statistics.

====> replication checking (on target)

GGSCI (gg2.example.com) 8> stats rep1

Sending STATS request to REPLICAT REP1 ...

Start of Statistics at 2016-01-01 02:35:39.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                         1.00
        Mapped operations                                  1.00
        Unmapped operations                                0.00
        Other operations                                   0.00
        Excluded operations                                0.00
        Errors                                             0.00
        Retried errors                                     0.00
        Discarded errors                                   0.00
        Ignored errors                                     0.00

Replicating from SCOTT.DEPT to SCOTT.DEPT:

*** Total statistics since 2016-01-01 02:35:29 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Daily statistics since 2016-01-01 02:35:29 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Hourly statistics since 2016-01-01 02:35:29 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Latest statistics since 2016-01-01 02:35:29 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

End of Statistics.

SQL> conn scott
Enter password:
Connected.
SQL> select TABLE_NAME from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
T1
T2

6 rows selected.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        68 testing        perth
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        66 marketing      new york
        11 test           BROOME

7 rows selected.

======> perform DDL and DML opration target

SQL> conn scott
Enter password:
Connected.
SQL> select TABLE_NAME from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
T1
T2

6 rows selected.

SQL> insert into t2 values(11);

1 row created.

SQL> commit;

Commit complete.

SQL> create table t3(id number(10) primary key);

Table created.

SQL> select TABLE_NAME from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
T1
T2
T3

7 rows selected.

SQL> select * from t2;

        ID
----------
        11

====> target gg process stats

GGSCI (gg2.example.com) 2> stats EX1

Sending STATS request to EXTRACT EX1 ...

Start of Statistics at 2016-01-01 02:55:17.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                        12.00
        Mapped operations                                  2.00
        Unmapped operations                                7.00
        Other operations                                   3.00
        Excluded operations                                0.00

Output to /u01/app/oracle/product/gg/dirdat/ex:

Extracting from RX025N.GGS_MARKER to RX025N.GGS_MARKER:

*** Total statistics since 2016-01-01 02:49:00 ***

        No database operations have been performed.

*** Daily statistics since 2016-01-01 02:49:00 ***

        No database operations have been performed.

*** Hourly statistics since 2016-01-01 02:49:00 ***

        No database operations have been performed.

*** Latest statistics since 2016-01-01 02:49:00 ***

        No database operations have been performed.

Extracting from SCOTT.DEPT to SCOTT.DEPT:

*** Total statistics since 2016-01-01 02:49:00 ***
        Total inserts                                      5.00
        Total updates                                      2.00
        Total deletes                                      4.00
        Total discards                                     0.00
        Total operations                                  11.00

*** Daily statistics since 2016-01-01 02:49:00 ***
        Total inserts                                      5.00
        Total updates                                      2.00
        Total deletes                                      4.00
        Total discards                                     0.00
        Total operations                                  11.00

*** Hourly statistics since 2016-01-01 02:49:00 ***
        Total inserts                                      5.00
        Total updates                                      2.00
        Total deletes                                      4.00
        Total discards                                     0.00
        Total operations                                  11.00

*** Latest statistics since 2016-01-01 02:49:00 ***
        Total inserts                                      5.00
        Total updates                                      2.00
        Total deletes                                      4.00
        Total discards                                     0.00
        Total operations                                  11.00

Extracting from SCOTT.T2 to SCOTT.T2:

*** Total statistics since 2016-01-01 02:49:00 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Daily statistics since 2016-01-01 02:49:00 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Hourly statistics since 2016-01-01 02:49:00 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Latest statistics since 2016-01-01 02:49:00 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

GGSCI (gg2.example.com) 4> stats DP1

Sending STATS request to EXTRACT DP1 ...

Start of Statistics at 2016-01-01 02:55:53.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                        12.00
        Mapped operations                                  0.00
        Unmapped operations                                0.00
        Other operations                                   0.00
        Excluded operations                                0.00

Output to /u01/app/oracle/product/gg/dirdat/tx:

Extracting from SCOTT.DEPT to SCOTT.DEPT:

*** Total statistics since 2016-01-01 02:50:01 ***
        Total inserts                                      5.00
        Total updates                                      2.00
        Total deletes                                      4.00
        Total discards                                     0.00
        Total operations                                  11.00

*** Daily statistics since 2016-01-01 02:50:01 ***
        Total inserts                                      5.00
        Total updates                                      2.00
        Total deletes                                      4.00
        Total discards                                     0.00
        Total operations                                  11.00

*** Hourly statistics since 2016-01-01 02:50:01 ***
        Total inserts                                      5.00
        Total updates                                      2.00
        Total deletes                                      4.00
        Total discards                                     0.00
        Total operations                                  11.00

*** Latest statistics since 2016-01-01 02:50:01 ***
        Total inserts                                      5.00
        Total updates                                      2.00
        Total deletes                                      4.00
        Total discards                                     0.00
        Total operations                                  11.00

Extracting from SCOTT.T2 to SCOTT.T2:

*** Total statistics since 2016-01-01 02:50:01 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Daily statistics since 2016-01-01 02:50:01 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Hourly statistics since 2016-01-01 02:50:01 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Latest statistics since 2016-01-01 02:50:01 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

End of Statistics.

=====> cheking replication on source database

GGSCI (gg1.example.com) 17> stats rep1

Sending STATS request to REPLICAT REP1 ...

Start of Statistics at 2016-01-01 03:05:12.

DDL replication statistics:

*** Total statistics since replicat started     ***
        Operations                                        12.00
        Mapped operations                                  1.00
        Unmapped operations                                8.00
        Other operations                                   3.00
        Excluded operations                                0.00
        Errors                                             0.00
        Retried errors                                     0.00
        Discarded errors                                   0.00
        Ignored errors                                     0.00

Replicating from SCOTT.DEPT to SCOTT.DEPT:

*** Total statistics since 2016-01-01 03:05:06 ***
        Total inserts                                      9.00
        Total updates                                      4.00
        Total deletes                                      9.00
        Total discards                                     0.00
        Total operations                                  22.00

*** Daily statistics since 2016-01-01 03:05:06 ***
        Total inserts                                      9.00
        Total updates                                      4.00
        Total deletes                                      9.00
        Total discards                                     0.00
        Total operations                                  22.00

*** Hourly statistics since 2016-01-01 03:05:06 ***
        Total inserts                                      9.00
        Total updates                                      4.00
        Total deletes                                      9.00
        Total discards                                     0.00
        Total operations                                  22.00

*** Latest statistics since 2016-01-01 03:05:06 ***
        Total inserts                                      9.00
        Total updates                                      4.00
        Total deletes                                      9.00
        Total discards                                     0.00
        Total operations                                  22.00

Replicating from SCOTT.T2 to SCOTT.T2:

*** Total statistics since 2016-01-01 03:05:06 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Daily statistics since 2016-01-01 03:05:06 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Hourly statistics since 2016-01-01 03:05:06 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Latest statistics since 2016-01-01 03:05:06 ***
        Total inserts                                      1.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

End of Statistics.


SQL>  select TABLE_NAME from user_tables;

TABLE_NAME
------------------------------
DEPT
   
EMP
BONUS
SALGRADE    
EMP1
T1
T2
T3

8 rows selected.

SQL>  select * from t2;

        ID
----------
        11


                                    *********END**********


at No comments:

Thursday, 24 December 2015

Bidirectional DML replication in oracle Goldengate

=====> Bidirectional DML replication in oracle Goldengate <=====

=====>Node 1 details

 OS details:

 [oracle@gg1 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
hostname: gg1.example.com

database details:
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Database name : Dev1

Goldengate details:

 Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

====>Node2 details

 OS details:

 [oracle@gg2 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
hostname: gg2.example.com

database details:
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Database name : Dev2

 Goldengate details:

 Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

====>TNS configuration both node..

 edit tnsname.ora
node 1:
DEV2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg2.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = dev2)
)
)
DEV1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg1.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dev1)
)
)
node 2:
DEV2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg2.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dev2)
)
)
DEV1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg1.example.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = dev1)
)
)

====>On source database

 Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
NO NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
NO YES

SQL> ALTER DATABASE FORCE LOGGING;
Database altered.

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
YES YES

SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

SQL> CREATE USER ggate IDENTIFIED BY oracle;
User created.

SQL> grant create session to ggate;
Grant succeeded.

SQL> grant dba to ggate;
Grant succeeded.

SQL> alter system set undo_retention=86400 scope=both;
System altered.

SQL> grant flashback any table to ggate;
Grant succeeded.
SQL>

==>On target server

SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
NO NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
NO YES
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
--- --------
YES YES

SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

SQL> CREATE USER ggate IDENTIFIED BY ggate;
User created.

SQL> grant create session to ggate;
Grant succeeded.

SQL> grant dba to ggate;
Grant succeeded.

SQL> alter system set undo_retention=86400 scope=both;
System altered.

SQL> grant flashback any table to ggate;
Grant succeeded.

SQL>
====>Source

 GGSCI (gg1.example.com) 3> dblogin userid ggate, Password ggate
Successfully logged into database.
GGSCI (gg1.example.com) 4> ADD CHECKPOINTTABLE ggate.OGGCHKPT
GGSCI (gg1.example.com) 5> add trandata scott.dept

=====>target

 GGSCI (gg1.example.com) 3> dblogin userid ggate, Password ggate
Successfully logged into database.
GGSCI (gg1.example.com) 4> ADD CHECKPOINTTABLE ggate.OGGCHKPT
GGSCI (gg1.example.com) 5> add trandata scott.dept
Note:creating extract and replicate process on both server.

====>Source

 GGSCI (gg1.example.com) 2> edit param mgr
PORT 7809
DYNAMICPORTLIST 7810-7820
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/dirdat/rx*, USECHECKPOINTS, MINKEEPHOURS 2

GGSCI (gg1.example.com) 4> edit param RX1 -----extract process
EXTRACT rx1
USERID ggate, PASSWORD ******
EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx
TABLE scott.dept;

GGSCI (gg1.example.com) 5> edit param DP1 -------datapump process
EXTRACT dp1
USERID ggate, PASSWORD ******
RMTHOST gg2.example.com, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/rt
TABLE scott.dept;

GGSCI (gg1.example.com) 6> edit param REP1 ------replication process
REPLICAT rep1
USERID rx025n, PASSWORD *****
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/gg/discards, PURGE
MAP scott.dept, TARGET scott.dept;

GGSCI (gg1.example.com) 7> ADD EXTRACT rx1, TRANLOG, BEGIN NOW

GGSCI (gg1.example.com) 8> ADD EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx, EXTRACT rx1

GGSCI (gg1.example.com) 9> ADD EXTRACT dp1 EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/rx

GGSCI (gg1.example.com) 10> ADD RMTTRAIL /u01/app/oracle/product/gg/dirdat/rt, EXTRACT dp1

GGSCI (gg1.example.com) 11> ADD REPLICAT rep1, EXTTRAIL /u01/app/oracle/product/gg/dirdat/rt CHECKPOINTTABLE rx025n.OGGCHKPT
REPLICAT added.

GGSCI (gg1.example.com) 20> Info ExtTrail *
Extract Trail: /u01/app/oracle/product/gg/dirdat/rt
Extract: DP1
Seqno: 3
RBA: 1513
File Size: 100M
Extract Trail: /u01/app/oracle/product/gg/dirdat/rx
Extract: EX1
Seqno: 3
RBA: 1243
File Size: 100M

========>targate

 GGSCI (gg2.example.com) 2> edit param mgr
PORT 7809
DYNAMICPORTLIST 7810-7820
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/dirdat/rx*, USECHECKPOINTS, MINKEEPHOURS 2

GGSCI (gg2.example.com) 4> edit param RX1 -----extract process
EXTRACT rx1
USERID ggate, PASSWORD ******
EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx
TABLE scott.dept;

GGSCI (gg2.example.com) 5> edit param DP1 -------datapump process
EXTRACT dp1
USERID ggate, PASSWORD ******
RMTHOST gg2.example.com, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/tx
TABLE scott.dept;

GGSCI (gg2.example.com) 6> edit param REP1 ------replication process
REPLICAT rep1
USERID rx025n, PASSWORD *****
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/gg/discards, PURGE
MAP scott.dept, TARGET scott.dept;

GGSCI (gg2.example.com) 7> ADD EXTRACT ex1, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (gg2.example.com) 8> ADD EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx, EXTRACT rx1
EXTTRAIL added.

GGSCI (gg2.example.com) 9> ADD EXTRACT dp1 EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/rx
EXTRACT added.

GGSCI (gg2.example.com) 10> ADD RMTTRAIL /u01/app/oracle/product/gg/dirdat/tx, EXTRACT dp1
RMTTRAIL added

GGSCI (gg2.example.com) 11> ADD REPLICAT rep1, EXTTRAIL /u01/app/oracle/product/gg/dirdat/tx CHECKPOINTTABLE rx025n.OGGCHKPT
REPLICAT added.

GGSCI (gg2.example.com) 30> Info ExtTrail *
Extract Trail: /u01/app/oracle/product/gg/dirdat/tx
Extract: DP1
Seqno: 0
RBA: 0
File Size: 100M
Extract Trail: /u01/app/oracle/product/gg/dirdat/rx
Extract: EX1
Seqno: 0
RBA: 0
File Size: 100M

 ======>start gg extract,datapump process on source

- start manager process
- start extract process
- start datapump process

GGSCI (gg1.example.com) 33> start manager
Manager started.

GGSCI (gg1.example.com) 34> start EXTRACT EX1
Sending START request to MANAGER ...
EXTRACT EX1 starting

GGSCI (gg1.example.com) 35> start EXTRACT DP1
Sending START request to MANAGER ...
EXTRACT DP1 starting

GGSCI (gg1.example.com) 36> info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:10
EXTRACT RUNNING RX1 00:00:00 00:00:03
REPLICAT STOPPED REP1 00:00:00 00:20:17

note: replicate process we will start after target extract and replication process.

======>start gg extract,datapump process on targate

 - start manager process
- start extract process
- start datapump process

GGSCI (gg2.example.com) 47> start extract EX1
Sending START request to MANAGER ...
EXTRACT EX1 starting

GGSCI (gg2.example.com) 48> start extract DP1
Sending START request to MANAGER ...
EXTRACT DP1 starting

GGSCI (gg2.example.com) 49> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:07
EXTRACT RUNNING RX1 00:00:00 00:00:04
REPLICAT STOPPED REP1 00:00:00 00:00:09

note: replicate process we will start after target extract and replication process.

=======>start gg replicate process on target

 GGSCI (gg2.example.com) 50> start REPLICAT REP1
Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (gg1.example.com) 52> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:03
EXTRACT RUNNING RX1 00:00:00 00:00:01
REPLICAT RUNNING REP1 00:00:00 00:00:04

=====>start gg replicate process on source

 GGSCI (gg1.example.com) 66> start REPLICAT REP1
Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (gg1.example.com) 67> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DP1 00:00:00 00:00:07
EXTRACT RUNNING RX1 00:00:00 00:00:04
REPLICAT RUNNING REP1 00:00:00 00:00:09

======>inserting one row in source database

 SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
dev1

SQL> conn scott
Enter password:
Connected.

SQL> show user
USER is "SCOTT"

SQL> set lines 220

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> insert into dept values(66,'marketing','new york');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
66 marketing new york

SQL>

GGSCI (gg1.example.com) 2> stats EXTRACT RX1

Sending STATS request to EXTRACT RX1 ...
Start of Statistics at 2015-12-25 01:41:36.
Output to /u01/app/oracle/product/gg/dirdat/rx:
Extracting from SCOTT.DEPT to SCOTT.DEPT:
*** Total statistics since 2015-12-25 00:29:16 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2015-12-25 00:29:16 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2015-12-25 01:00:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 00:29:16 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
End of Statistics.

=======>checking on target side

 GGSCI (gg2.example.com) 51> stats REPLICAT REP1

Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2015-12-25 01:42:22.
Replicating from SCOTT.DEPT to SCOTT.DEPT:
*** Total statistics since 2015-12-25 00:29:17 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2015-12-25 00:29:17 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2015-12-25 01:00:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 00:29:17 ***
Total inserts 1.00
Total updates 0.00
Total deletes 1.00
Total discards 0.00
Total operations 2.00
End of Statistics.

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
dev2

SQL> conn scott
Enter password:
Connected.

SQL> set lines 220
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
66 marketing new york

SQL>

===>Now inserting value in target database.

 SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
dev2

SQL> conn scott
Enter password:
Connected.

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
66 marketing new york

SQL> insert into dept values(68,'testing','perth');
1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
68 testing perth
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
66 marketing new york
6 rows selected.

====>checking gg process

 GGSCI (gg2.example.com) 2> stats RX1

Sending STATS request to EXTRACT RX1 ...
Start of Statistics at 2015-12-25 03:34:11.
Output to /u01/app/oracle/product/gg/dirdat/rx:
Extracting from SCOTT.DEPT to SCOTT.DEPT:

*** Total statistics since 2015-12-25 03:33:58 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2015-12-25 03:33:58 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2015-12-25 03:33:58 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 03:33:58 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.

GGSCI (gg2.example.com) 3> stats DP1

Sending STATS request to EXTRACT DP1 ...
Start of Statistics at 2015-12-25 03:34:19.
Output to /u01/app/oracle/product/gg/dirdat/tx:
Extracting from SCOTT.DEPT to SCOTT.DEPT:

*** Total statistics since 2015-12-25 03:34:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2015-12-25 03:34:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2015-12-25 03:34:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 03:34:00 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.

====>checking gg process on source

 GGSCI (gg1.example.com) 7> stats REP1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2015-12-25 03:42:02.
Replicating from SCOTT.DEPT to SCOTT.DEPT:
*** Total statistics since 2015-12-25 03:41:48 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2015-12-25 03:41:48 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2015-12-25 03:41:48 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2015-12-25 03:41:48 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.

==>database validation

 SQL> conn /as sysdba
Connected.

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
dev1

SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
66 marketing new york
68 testing perth
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
6 rows selected.
                                                         ******END****
at No comments:

Sunday, 6 December 2015

12c Golden gate installation steps.

:=  12c Golden gate installation for 11g Database.

Operating system details:
[oracle@localhost ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.4 (Santiago)

Database version details:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Golden gate version details:
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.0 OGGCORE_12.2.0.1.0_PLATFORMS_151101.1925.2_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Nov 11 2015 01:38:14
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

1)Create directory structure and user for goleden gate.
[gg_user@localhost ~]$ mkdir –p /u01/app/oracle/product/12.1.0.1/db_1/11.2.0/db_1
[gg_user@localhost ~]$ mkdir –p /u01/app/gg_user/product/12.1.2/oggcore_1

2)Got to golden gate s/w folder 

[gg_user@localhost Disk1]$ ls -ltr
total 16
drwxr-xr-x.  4 gg_user oinstall 4096 Nov 11 21:05 install
drwxr-xr-x. 11 gg_user oinstall 4096 Nov 11 21:05 stage
-rwxr-xr-x.  1 gg_user oinstall  918 Nov 11 21:05 runInstaller
drwxrwxr-x.  2 gg_user oinstall 4096 Nov 11 21:05 response

[gg_user@localhost Disk1]$ ./runInstaller




Note:  To install Oracle Golden Gate 12c for 11g database, select Oracle GoldenGate for Oracle Database 11g.  If you select oracle database 11g option, the installation will be valid but will not start against an 11g database due to shared libraries: libnnz11.so file and libclntsh.so.11.1.
Solution:
[gg_user@localhost oggcore_1]$ ln -s /u01/app/oracle/product/12.1.0.1/db_1/11.2.0/db_1/lib/libnnz11.so libnnz11.so
[gg_user@localhost oggcore_1]$ ln -s /u01/app/oracle/product/12.1.0.1/db_1/11.2.0/db_1/lib/libclntsh.so.11.1 libclntsh.so.11.1







[gg_user@localhost]$cd /u01/app/gg_user/product/12.1.2/oggcore_1

[gg_user@localhost oggcore_1]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.0 OGGCORE_12.2.0.1.0_PLATFORMS_151101.1925.2_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Nov 11 2015 01:38:14
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.


GGSCI (localhost.localdomain) 1> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
GGSCI (localhost.localdomain) 2>

                                                ********END*********









at No comments:

Thursday, 26 November 2015

Create Dummy database using rman 0 level backup


Server details

Node 1:
[oracle@target backup]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)

Node 2:
[oracle@source backup]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)


1) Take 0 level backup of source database

[oracle@source]$ mkdir /u01/app/backup/

[oracle@source]$ cd /u01/app/backup/

[oracle@source backup]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Nov 27 02:50:21 2015

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

connected to target database: DB11G (DBID=366062519)

RMAN>


RMAN> RUN
2> {
3> configure controlfile autobackup on;
4> set command id to 'ORCLOnlineBackupFull';
5> ALLOCATE CHANNEL c1 DEVICE TYPE disk;
6> ALLOCATE CHANNEL c2 DEVICE TYPE disk;
7> backup AS COMPRESSED BACKUPSET full database tag ORCL_FULL format '/u01/app/backup/%d_%T_%s_%p_FULL' ;
8> sql 'alter system archive log current';
9> backup tag ORCL_ARCHIVE format '/u01/app/backup/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ;
10> backup tag ORCL_CONTROL current controlfile format '/u01/app/backup/%d_%T_%s_%p_CONTROL';
11> backup tag spfile_pfile spfile format '/u01/app/backup/%d_%T_%s_%p_spfile';
12> release channel c1;
13> release channel c2;
14> }

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

executing command: SET COMMAND ID

allocated channel: c1
channel c1: SID=33 device type=DISK

allocated channel: c2
channel c2: SID=41 device type=DISK

Starting backup at 27-NOV-15
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/DB11G/system01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DB11G/example01.db
channel c1: starting piece 1 at 27-NOV-15
channel c2: starting compressed full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/DB11G/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DB11G/undotbs01.db
input datafile file number=00004 name=/u01/app/oracle/oradata/DB11G/users01.dbf
channel c2: starting piece 1 at 27-NOV-15
channel c2: finished piece 1 at 27-NOV-15
piece handle=/u01/app/backup/DB11G_20151127_37_1_FULL tag=ORCL_FULL comment=NONE
channel c2: backup set complete, elapsed time: 00:00:46
channel c1: finished piece 1 at 27-NOV-15
piece handle=/u01/app/backup/DB11G_20151127_36_1_FULL tag=ORCL_FULL comment=NONE
channel c1: backup set complete, elapsed time: 00:00:56
Finished backup at 27-NOV-15

Starting Control File and SPFILE Autobackup at 27-NOV-15
piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/c-366062519-20151127-00 comment=NONE
Finished Control File and SPFILE Autobackup at 27-NOV-15

sql statement: alter system archive log current

Starting backup at 27-NOV-15
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=9 STAMP=896841558
channel c1: starting piece 1 at 27-NOV-15
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=10 STAMP=896842340
input archived log thread=1 sequence=13 RECID=11 STAMP=896842340
channel c2: starting piece 1 at 27-NOV-15
channel c1: finished piece 1 at 27-NOV-15
piece handle=/u01/app/backup/DB11G_20151127_39_1_ARCHIVE tag=ORCL_ARCHIVE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
channel c1: deleting archived log(s)
archived log file name=/u01/archive/1_11_896392954.dbf RECID=9 STAMP=896841558
channel c2: finished piece 1 at 27-NOV-15
piece handle=/u01/app/backup/DB11G_20151127_40_1_ARCHIVE tag=ORCL_ARCHIVE comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c2: deleting archived log(s)
archived log file name=/u01/archive/1_12_896392954.dbf RECID=10 STAMP=896842340
archived log file name=/u01/archive/1_13_896392954.dbf RECID=11 STAMP=896842340
Finished backup at 27-NOV-15

Starting backup at 27-NOV-15
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 27-NOV-15
channel c1: finished piece 1 at 27-NOV-15
piece handle=/u01/app/backup/DB11G_20151127_41_1_CONTROL tag=ORCL_CONTROL comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-NOV-15

Starting backup at 27-NOV-15
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 27-NOV-15
channel c1: finished piece 1 at 27-NOV-15
piece handle=/u01/app/backup/DB11G_20151127_42_1_spfile tag=SPFILE_PFILE comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-NOV-15

Starting Control File and SPFILE Autobackup at 27-NOV-15
piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/c-366062519-20151127-01 comment=NONE
Finished Control File and SPFILE Autobackup at 27-NOV-15

released channel: c1

released channel: c2

2) Create same backup location on target database server and transfer all backup file to same location.


[oracle@target ~]$ mkdir /u01/app/backup/

[oracle@source backup]$ ls -ltr
total 303000
-rw-r----- 1 oracle oinstall  76931072 Nov 27 02:52 DB11G_20151127_37_1_FULL
-rw-r----- 1 oracle oinstall 212533248 Nov 27 02:52 DB11G_20151127_36_1_FULL
-rw-r----- 1 oracle oinstall   2514432 Nov 27 02:52 DB11G_20151127_40_1_ARCHIVE
-rw-r----- 1 oracle oinstall   8061440 Nov 27 02:52 DB11G_20151127_39_1_ARCHIVE
-rw-r----- 1 oracle oinstall   9797632 Nov 27 02:52 DB11G_20151127_41_1_CONTROL
-rw-r----- 1 oracle oinstall     98304 Nov 27 02:52 DB11G_20151127_42_1_spfile


[oracle@source backup]$ scp * oracle@target:/u01/app/backup/
The authenticity of host 'target (192.168.223.130)' can't be established.
RSA key fingerprint is 87:f6:a9:cc:0d:63:8c:40:6d:ee:17:2f:05:dd:ac:ad.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'target,192.168.223.130' (RSA) to the list of known hosts.
oracle@target's password:
DB11G_20151127_36_1_FULL                                                                                     100%  203MB  50.7MB/s   00:04
DB11G_20151127_37_1_FULL                                                                                     100%   73MB  36.7MB/s   00:02
DB11G_20151127_39_1_ARCHIVE                                                                                  100% 7873KB   7.7MB/s   00:01
DB11G_20151127_40_1_ARCHIVE                                                                                  100% 2456KB   2.4MB/s   00:00
DB11G_20151127_41_1_CONTROL                                                                                  100% 9568KB   9.3MB/s   00:00
DB11G_20151127_42_1_spfile                                                                                   100%   96KB  96.0KB/s   00:00

[oracle@target ~]$ cd /u01/app/backup/
[oracle@target backup]$ ls -ltr
total 303000
-rw-r----- 1 oracle oinstall 212533248 Nov 26 16:41 DB11G_20151127_36_1_FULL
-rw-r----- 1 oracle oinstall  76931072 Nov 26 16:41 DB11G_20151127_37_1_FULL
-rw-r----- 1 oracle oinstall   9797632 Nov 26 16:41 DB11G_20151127_41_1_CONTROL
-rw-r----- 1 oracle oinstall   2514432 Nov 26 16:41 DB11G_20151127_40_1_ARCHIVE
-rw-r----- 1 oracle oinstall   8061440 Nov 26 16:41 DB11G_20151127_39_1_ARCHIVE
-rw-r----- 1 oracle oinstall     98304 Nov 26 16:41 DB11G_20151127_42_1_spfile
[oracle@target backup]$

3) Create structure on target database same as source database.

[oracle@target oracle]$ mkdir -p /u01/app/oracle/oradata/DB11G
[oracle@source DB11G]$ mkdir -p /u01/app/oracle/admin/DB11G/dpdump
[oracle@source DB11G]$ mkdir -p /u01/app/oracle/admin/DB11G/pfile
[oracle@source DB11G]$ mkdir -p /u01/app/oracle/admin/DB11G/adump
[oracle@source DB11G]$

4) Restore spfile,contofile,database using rman (on target database).

[oracle@target dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Nov 26 17:25:00 2015

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

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initDB11G.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2226456 bytes
Variable Size                 92276456 bytes
Database Buffers              58720256 bytes
Redo Buffers                   5439488 bytes

RMAN> restore spfile from '/u01/app/backup/DB11G_20151127_42_1_spfile';

Starting restore at 26-NOV-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/backup/DB11G_20151127_42_1_spfile
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 26-NOV-15

RMAN> shutdown immediate;

Oracle instance shut down

RMAN>exit

[oracle@target dbs]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 26 17:51:36 2015

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

Enter user-name: /as sysdba

SQL> create pfile from spfile;

File created.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initDB11G.ora';
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2233000 bytes
Variable Size             520097112 bytes
Database Buffers          314572800 bytes
Redo Buffers                2379776 bytes
SQL> exit

[oracle@target admin]$ echo $ORACLE_SID
DB11G
[oracle@target admin]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Nov 26 17:52:32 2015

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

connected to target database: DB11G (not mounted)

RMAN> restore controlfile from '/u01/app/backup/DB11G_20151127_41_1_CONTROL';

Starting restore at 26-NOV-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/DB11G/control01.ctl
output file name=/u01/app/oracle/oradata/DB11G/control02.ctl
Finished restore at 26-NOV-15

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 26-NOV-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/DB11G/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/DB11G/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/DB11G/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/backup/DB11G_20151127_37_1_FULL
channel ORA_DISK_1: piece handle=/u01/app/backup/DB11G_20151127_37_1_FULL tag=ORCL_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/DB11G/system01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/DB11G/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/backup/DB11G_20151127_36_1_FULL
channel ORA_DISK_1: piece handle=/u01/app/backup/DB11G_20151127_36_1_FULL tag=ORCL_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:47
Finished restore at 26-NOV-15

RMAN> recover database;

Starting recover at 26-NOV-15
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=12
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=13
channel ORA_DISK_1: reading from backup piece /u01/app/backup/DB11G_20151127_40_1_ARCHIVE
channel ORA_DISK_1: piece handle=/u01/app/backup/DB11G_20151127_40_1_ARCHIVE tag=ORCL_ARCHIVE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/archive/1_12_896392954.dbf thread=1 sequence=12
archived log file name=/u01/archive/1_13_896392954.dbf thread=1 sequence=13
unable to find archived log
archived log thread=1 sequence=14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/26/2015 17:56:57
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 14 and starting SCN of 1075281

RMAN> alter database open RESETLOGS;

database opened

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> exit

[oracle@target dbs]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 26 17:59:24 2015

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initDB11G.ora';
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2233000 bytes
Variable Size             520097112 bytes
Database Buffers          314572800 bytes
Redo Buffers                2379776 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;

File created.

SQL> startup force;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2233000 bytes
Variable Size             520097112 bytes
Database Buffers          314572800 bytes
Redo Buffers                2379776 bytes
Database mounted.
Database opened.
SQL> select name from v$database;

NAME
---------
DB11G

SQL>


                                                             *****END*****


at No comments:

Saturday, 7 November 2015

Container and Pluggable database backup command in oracle database 12c.

1) Backup container database plus pluggable database.

-> connect rman with sys user.

[oracle@cdb1 dbs]$ rman target=/
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Oct 18 05:20:11 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1413747592)

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Starting backup at 18-OCT-15
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=1 STAMP=893395257
channel ORA_DISK_1: starting piece 1 at 18-OCT-15
channel ORA_DISK_1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_18/o1_mf_annnn_TAG20151018T052058_c25qq26k_.bkp tag=TAG20151018T052058 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-OCT-15
Starting backup at 18-OCT-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-OCT-15
channel ORA_DISK_1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_18/o1_mf_nnndf_TAG20151018T052059_c25qq3j0_.bkp tag=TAG20151018T052059 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/orcl/pdb1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/pdb1/system01.dbf
channel ORA_DISK_1: starting piece 1 at 18-OCT-15
channel ORA_DISK_1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/1BA9531D0DD53B0EE05348304B74C6C7/backupset/2015_10_18/o1_mf_nnndf_TAG20151018T052059_c25qr711_.bkp tag=TAG20151018T052059 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 18-OCT-15
channel ORA_DISK_1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/1BA927A157F92B65E05348304B740CF8/backupset/2015_10_18/o1_mf_nnndf_TAG20151018T052059_c25qrp62_.bkp tag=TAG20151018T052059 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 18-OCT-15
Starting backup at 18-OCT-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=2 STAMP=893395325
channel ORA_DISK_1: starting piece 1 at 18-OCT-15
channel ORA_DISK_1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_18/o1_mf_annnn_TAG20151018T052205_c25qs5pm_.bkp tag=TAG20151018T052205 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-OCT-15
Starting Control File Autobackup at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2015_10_18/o1_mf_n_893395326_c25qs7b9_.bkp comment=NONE
Finished Control File Autobackup at 18-OCT-15

2) Backup only container database.

RMAN> BACKUP DATABASE ROOT plus archivelog;
Starting backup at 18-OCT-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=1 STAMP=893395257
input archived log thread=1 sequence=11 RECID=2 STAMP=893395325
input archived log thread=1 sequence=12 RECID=3 STAMP=893395548
channel ORA_DISK_1: starting piece 1 at 18-OCT-15
channel ORA_DISK_1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_18/o1_mf_annnn_TAG20151018T052548_c25r04nz_.bkp tag=TAG20151018T052548 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-OCT-15
Starting backup at 18-OCT-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-OCT-15
channel ORA_DISK_1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_18/o1_mf_nnndf_TAG20151018T052549_c25r05xs_.bkp tag=TAG20151018T052549 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 18-OCT-15
Starting backup at 18-OCT-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=4 STAMP=893395585
channel ORA_DISK_1: starting piece 1 at 18-OCT-15
channel ORA_DISK_1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2015_10_18/o1_mf_annnn_TAG20151018T052625_c25r19gf_.bkp tag=TAG20151018T052625 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-OCT-15
Starting Control File Autobackup at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2015_10_18/o1_mf_n_893395586_c25r1btk_.bkp comment=NONE
Finished Control File Autobackup at 18-OCT-15

3) Backup only pluggable database.

I have only one pluggable database pdb1.

SQL> select con_id,name from v$containers;
CON_ID NAME
---------- ------------------------------
1 CDB$ROOT
2 PDB$SEED
3 PDB1
RMAN> BACKUP PLUGGABLE DATABASE pdb1;
Starting backup at 18-OCT-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/orcl/pdb1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/pdb1/system01.dbf
channel ORA_DISK_1: starting piece 1 at 18-OCT-15
channel ORA_DISK_1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/1BA9531D0DD53B0EE05348304B74C6C7/backupset/2015_10_18/o1_mf_nnndf_TAG20151018T053410_c25rhv5z_.bkp tag=TAG20151018T053410 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 18-OCT-15
Starting Control File Autobackup at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2015_10_18/o1_mf_n_893396066_c25rjbgf_.bkp comment=NONE
Finished Control File Autobackup at 18-OCT-15

---> backup pluggable database using pluggable database.

[oracle@cdb1 dbs]$ rman target=sys/*****@pdb1

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Oct 18 05:36:30 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1413747592, not open)

RMAN> backup database;

Starting backup at 18-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/orcl/pdb1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/pdb1/system01.dbf
channel ORA_DISK_1: starting piece 1 at 18-OCT-15
channel ORA_DISK_1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/1BA9531D0DD53B0EE05348304B74C6C7/backupset
/2015_10_18/o1_mf_nnndf_TAG20151018T053658_c25ro2c4_.bkp tag=TAG20151018T053658 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 18-OCT-15
Starting Control File Autobackup at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2015_10_18/o1_mf_n_893396233_c25roksn_.bkp comment=NONE
Finished Control File Autobackup at 18-OCT-15

4) Backup pluggable database tablespace.

RMAN> backup tablespace sysaux,system;

Starting backup at 18-OCT-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/orcl/pdb1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/pdb1/system01.dbf
channel ORA_DISK_1: starting piece 1 at 18-OCT-15
channel ORA_DISK_1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/1BA9531D0DD53B0EE05348304B74C6C7/backupset/2015_10_18/o1_mf_nnndf_TAG20151018T053829_c25rqxkt_.bkp tag=TAG20151018T053829 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 18-OCT-15
Starting Control File Autobackup at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2015_10_18/o1_mf_n_893396324_c25rrdt4_.bkp comment=NONE
Finished Control File Autobackup at 18-OCT-15

5) Backup pluggable database datafiles;

SQL> select CON_ID,FILE# from v$datafile;
CON_ID FILE#
---------- ----------
1 1
1 3
1 4
2 5
1 6
2 7
3 8
3 9
8 rows selected.

SQL> select con_id,name from v$containers;
CON_ID NAME
---------- ------------------------------
1 CDB$ROOT
2 PDB$SEED
3 PDB1

-> pdb1 database contain only two files 8 and 9;

RMAN> backup datafile 8,9;

Starting backup at 18-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/orcl/pdb1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/pdb1/system01.dbf
channel ORA_DISK_1: starting piece 1 at 18-OCT-15
channel ORA_DISK_1: finished piece 1 at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/1BA9531D0DD53B0EE05348304B74C6C7/backupset/2015_10_18/o1_mf_nnndf_TAG20151018T055231_c25sl7wh_.bkp tag=TAG20151018T055231 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 18-OCT-15
Starting Control File Autobackup at 18-OCT-15
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2015_10_18/o1_mf_n_893397166_c25slq4j_.bkp comment=NONE
Finished Control File Autobackup at 18-OCT-15

                                                      *******END********
at No comments:
Subscribe to: Posts (Atom)

Login to the database as user C##GGUSER failed because of error Error while trying to retrieve text for error ORA-01804.

 Error :  2026-09-19 11:28:18  ERROR   OGG-00303  Unable to connect to database using user C##GGUSER@CDB. Ensure that the necessary privileg...