Maaz Anjum's Blog

[oracle@blue ggate]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.13 17435036 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131002.1206_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct  3 2013 02:39:46

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



GGSCI (blue.color) 2> dblogin userid ggate, password ggate
Successfully logged into database.

GGSCI (blue.color) 3> add trandata song.stone

2013-12-05 15:17:07  WARNING OGG-00869  No unique key is defined for table 'STONE'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table SONG.STONE.

GGSCI (blue.color) 4> add trandata song.greystreet

2013-12-05 15:17:15  WARNING OGG-00869  No unique key is defined for table 'GREYSTREET'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table SONG.GREYSTREET.

GGSCI (blue.color) 5> edit params e_gg
...
TABLE song.stone;
TABLE song.greystreet;
...

GGSCI (blue.color) 2> stop e_gg

Sending STOP request to EXTRACT E_GG ...
Request processed.


GGSCI (blue.color) 3> start e_gg

Sending START request to MANAGER ...
EXTRACT E_GG starting

[oracle@blue ggate]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 5 15:28:31 2013

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


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 current_scn from v$database;

CURRENT_SCN
-----------
    1113396

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@blue ggate]$ expdp system/oracle directory=data_pump_dir dumpfile=additional_tables.dmp flashback_scn=1113396 tables=song.stone,song.greystreet

Export: Release 11.2.0.3.0 - Production on Thu Dec 5 15:32:04 2013

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

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
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=data_pump_dir dumpfile=additional_tables.dmp flashback_scn=1113396 tables=song.stone,song.greystreet
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SONG"."GREYSTREET"                         5.882 KB     100 rows
. . exported "SONG"."STONE"                              5.679 KB     100 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/sky/dpdump/additional_tables.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 15:32:14

[oracle@blue ggate]$ impdp system/oracle directory=data_pump_dir dumpfile=additional_tables.dmp remap_schema=song:river

Import: Release 11.2.0.3.0 - Production on Thu Dec 5 15:45:29 2013

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

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
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=data_pump_dir dumpfile=additional_tables.dmp remap_schema=song:river
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "RIVER"."GREYSTREET"                        5.882 KB     100 rows
. . imported "RIVER"."STONE"                             5.679 KB     100 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 15:45:31

GGSCI (blue.color) 2> edit params r_gg
...
MAP song.stone, TARGET river.stone , FILTER ( @GETENV("TRANSACTION", "CSN") > 1113396);
MAP song.greystreet, TARGET river.greystreet, FILTER ( @GETENV("TRANSACTION", "CSN") > 1113396);
...

GGSCI (blue.color) 4>  stop r_gg

Sending STOP request to REPLICAT R_GG ...
start Request processed.


GGSCI (blue.color) 5> start r_gg

Sending START request to MANAGER ...
REPLICAT R_GG starting

GGSCI (blue.color) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     E_GG        00:00:00      00:00:09   
REPLICAT    RUNNING     R_GG        00:00:00      00:00:03 

SQL> select name from v$database;

NAME
---------
SKY

SQL> select count(1) from song.stone;

  COUNT(1)
----------
       100

SQL> select name from v$database;

NAME
---------
WATER

SQL> select count(1) from river.stone;

  COUNT(1)
----------
       100

SQL> INSERT INTO song.stone
SELECT dbms_random.string('A', 2) RNDMSTR
  FROM all_objects
 WHERE ROWNUM;

100 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from song.stone;

  COUNT(1)
----------
       200

SQL> select count(1) from river.stone;

  COUNT(1)
----------
       200

GGSCI (blue.color) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     E_GG        00:00:00      00:00:02   
REPLICAT    RUNNING     R_GG        00:00:00      00:00:08   

GGSCI (blue.color) 2> edit params r_gg
...
MAP song.stone, TARGET river.stone;
MAP song.greystreet, TARGET river.greystreet;
...

GGSCI (blue.color) 4>  stop r_gg

Sending STOP request to REPLICAT R_GG ...
start Request processed.


GGSCI (blue.color) 5> start r_gg

Sending START request to MANAGER ...
REPLICAT R_GG starting

GGSCI (blue.color) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     E_GG        00:00:00      00:00:06   
REPLICAT    RUNNING     R_GG        00:00:00      00:00:02  

Components:


For this example, I will install the Java based agent.

Installation

1. Create the Tablespace, User, and Grant Privileges.

2. Create directories

3. Unzip Binaries

4. Create a copy of the agent.properties.sample file as agent.properties and edit the files highlighted.

5. Set the JRE_HOME or JAVA_HOME parameter, and start the agent.

6. Check the agent run log

7. Check the log for messages

Today, I’d like to share my experience with a tool that I’ve used infrequently with GoldenGate.

Veridata is Oracle’s answer to Data Validation for environments where a replication technology is utilized. In other words, the tool is agnostic (but partial) to a set Database vendors. You can install it on a variety of platforms, and a few database types.

Components:


Installation

1. Download and unzip the media from eDelivery.

2. Invoke the bash script to start the installer.

3. Click “Next”.



4. Pick your installation directory and click “Next”.



5. On the ports section, click “Next”.



6. Leave the defaults and click “Next”.



7. Enter at the account username and password. This will be used to install the tables in the repository database.



8. Of course we will chose “Oracle” :)



9. Confirm the Oracle Home (Veridata Home) location.



10. Select and Instance and click Continue. This article assumes that the Veridata installation is on the same server as its intended repository.



11. I will let the installer create a new user for me.



12. Enter the DBA



13. On the next screen, enter the oracle database user where the Veridata objects will be installed.



14. Enter the default, and temp tablespace for the above user.



15.  Check the box if you want it to start Veridata after the installation.



16. Review the settings and click “Next”.



17. Progress is always good.



18. Awesome! All done.



At this point, we can log into the Veridata Server successfully!



In subsequent posts, I will discuss the other aspects such as the Veridata Agent install, Configuration, etc.

Cheers!

To continue with the previous series, I want to illustrate another useful feature with the Patching & Provisioning functionality within EM12c. Using the provisioning functionality, one can create software components which contain compressed installation binaries. This feature is not limited to only Oracle software, but to illustrate an example, I’d like to show you how to clone an Oracle Database Home to create a software library component.

1. Navigate to the “Software Library” home from the Enterprise Menu.



2. On the “Software Library” home page, you will see several folders. The ones with a “Lock” icon are not editable, so we will focus on, either creating a new one, or use an existing one.



3. To keep the set up consistent, I’d like to use the “Component” folder since that is precisely what we are creating. Expand the tree structure, to show the folders underneath.



4. Click on “Actions”->”Create Entity”->”Component"



5. Select “Oracle Database Software Clone” from the list, and click “Continue”.



6. Enter an appropriate name and attributes for the component. Click “Next”.




7. On the Configuration screen, select the appropriate “Create Component from” location. In my case, it was a Reference Oracle Home. Then, search for the Reference Oracle Home. In addition, pick the correct credential set. Click “Next”.



8. On the last page, click “Save and Upload”. If you happen to click on “Save” only, then the binaries will not be uploaded. I’ll talk about that in a separate thread later.



9. Easy enough. On the next screen, you’ll see the option to view the job that gets submitted.



10. If you didn’t click on the “Job Details” page, and wanted to view the status, don’t worry. Go to the “Jobs Activity” page, and find the appropriate job name.



11. Click on the Job Name to view its details.



12. And just wait for the job to complete.


13. Now, back to the Software Library page, we can see that the component is now ready!



Summary:

In short, once a software component is created, one can use it for provisioning with the profiles and deployment procedures. For example, in one of my earlier posts on Provisioning a Database, step 4.4 has a screenshot of the software component used with the deployment. That is where the one we just created can be used instead.

I hope you found this moderately helpful :)

Cheers!

I’ve recently gone through an experience (see title), that I thought would be a good one to share.

As of late, I’ve started documenting the life out of any project/task, and this is one of in the long line of many.

Basically, the goal here was to upgrade an existing 11gR1 RAC configuration to 11gR2 RAC. To supplement the pretty picture, the following tasks were involved:

• Upgrade 11gR1 CRS+ASM to 11gR2 Grid Infrastructure
• Upgrade 11gR1 RDBMS to 11gR2 RDBMS
• Upgrade 11gR2 Instances to 11gR2

I’ve uploaded the document to the Oracle RAC SIG, if you’re interested please follow this link to download it. The document includes the steps for the upgrade, any encountered issues with fixes, plus additional tidbits I felt would be useful.

Thanks to my friend Osama Mustafa (@osamaoracle) for reviewing it!! I would cherish any comments, or feedback from my readers with their experiences.

Cheers

I've only just caught up on the new OGG release news! Stepping through just the documentation, I'm excited about trying it out in the next few weeks.

 

Edited December 19, 2013: It took me a while to get around it, but I did write a post on installation/upgrade to this version here.



http://docs.oracle.com/goldengate/1212/gg-winux/index.html

Cheers!

RMAN-03002: failure of recover command at 10/07/2013 15:11:47
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 53302 and starting SCN of 143193979270

RUN
{
        ALLOCATE auxiliary CHANNEL CH01 TYPE 'SBT_TAPE' parms "ENV=(NB_ORA_POLICY=ORACLE_RAC01_PRD1_FULL,NB_ORA_CLIENT=RAC01,NB_ORA_SCHED=Default-Application-Backup)";
        ALLOCATE auxiliary CHANNEL CH02 TYPE 'SBT_TAPE' parms "ENV=(NB_ORA_POLICY=ORACLE_RAC01_PRD1_FULL,NB_ORA_CLIENT=RAC01,NB_ORA_SCHED=Default-Application-Backup)";
        ALLOCATE auxiliary CHANNEL CH03 TYPE 'SBT_TAPE' parms "ENV=(NB_ORA_POLICY=ORACLE_RAC01_PRD1_FULL,NB_ORA_CLIENT=RAC01,NB_ORA_SCHED=Default-Application-Backup)";
        ALLOCATE auxiliary CHANNEL CH04 TYPE 'SBT_TAPE' parms "ENV=(NB_ORA_POLICY=ORACLE_RAC01_PRD1_FULL,NB_ORA_CLIENT=RAC01,NB_ORA_SCHED=Default-Application-Backup)";
        ALLOCATE auxiliary CHANNEL CH05 TYPE 'SBT_TAPE' parms "ENV=(NB_ORA_POLICY=ORACLE_RAC01_PRD1_FULL,NB_ORA_CLIENT=RAC01,NB_ORA_SCHED=Default-Application-Backup)";
        ALLOCATE auxiliary CHANNEL CH06 TYPE 'SBT_TAPE' parms "ENV=(NB_ORA_POLICY=ORACLE_RAC01_PRD1_FULL,NB_ORA_CLIENT=RAC01,NB_ORA_SCHED=Default-Application-Backup)";

        SET NEWNAME FOR DATAFILE 54 TO '/u03/oradata/RPTPRD/data_01.365.668441285';
        SET NEWNAME FOR DATAFILE 54 TO '/u03/oradata/RPTPRD/data_01.365.668441285';
        . . .
        . . .
        SET NEWNAME FOR DATAFILE 54 TO '/u03/oradata/RPTPRD/data_01.365.668441285';

        DUPLICATE TARGET DATABASE TO RPTPRD
                UNTIL SCN 143197743846
                LOGFILE GROUP 1 ('/r01/oradata/RPTPRD/redo01a.log',
                                '/r02/oradata/RPTPRD/redo01b.log') SIZE 500M REUSE,
                        GROUP 2 ('/r01/oradata/RPTPRD/redo02a.log',
                                '/r02/oradata/RPTPRD/redo02b.log') SIZE 500M REUSE,
                        GROUP 3 ('/r01/oradata/RPTPRD/redo03a.log',
                                '/r02/oradata/RPTPRD/redo03b.log') SIZE 500M REUSE
                NOFILENAMECHECK
                SPFILE
                PARAMETER_VALUE_CONVERT 'PRD','RPTPRD'
                SET CONTROL_FILES='/u01/oradata/RPTPRD/control01.ctl','/u02/oradata/RPTPRD/control02.ctl','/u03/oradata/RPTPRD/control03.ctl'
                SET DB_CREATE_FILE_DEST='/u03/oradata/RPTPRD'
                SET AUDIT_FILE_DEST='/u01/app/oracle/admin/RPTPRD/adump'
                SET DB_RECOVERY_FILE_DEST='/a01/flash_recovery_area'
                set DIAGNOSTIC_DEST='/u01/app/oracle/'
                SET DB_RECOVERY_FILE_DEST_SIZE='20G'
                SET local_listener='(address=(protocol=TCP)(host=apvmrdb01)(port=1529))'
                SET FAL_CLIENT=''
                SET FAL_SERVER=''
                SET log_archive_dest_2=''
                SET dg_broker_start='FALSE'
                SET CLUSTER_DATABASE='FALSE'
                SET log_archive_dest_1='LOCATION=/a01/flash_recovery_area'
                SET db_create_online_log_dest_2='/r02/oradata/RPTPRD'
                SET db_create_online_log_dest_1='/r01/oradata/RPTPRD'
                SET standby_archive_dest='';
}

Starting recover at 07-OCT-13

starting media recovery

unable to find archived log
archived log thread=1 sequence=53302
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u03/oradata/RPTPRD/system.326.665261191'

released channel: CH01
released channel: CH02
released channel: CH03
released channel: CH04
released channel: CH05
released channel: CH06
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/07/2013 15:11:47
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 53302 and starting SCN of 143193979270

contents of Memory Script:
{
   set until scn  143197743846;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 07-OCT-13

starting media recovery

channel CH01: starting archived log restore to default destination
channel CH01: restoring archived log
archived log thread=1 sequence=53302
channel CH01: reading from backup piece al_180608_1_828139086
channel CH02: starting archived log restore to default destination
channel CH02: restoring archived log
archived log thread=2 sequence=46813
channel CH02: restoring archived log
archived log thread=1 sequence=53303
channel CH02: reading from backup piece al_180607_1_828139086
channel CH03: starting archived log restore to default destination
channel CH03: restoring archived log
archived log thread=2 sequence=46814
channel CH03: restoring archived log
archived log thread=2 sequence=46815
channel CH03: reading from backup piece al_180609_1_828139086
channel CH04: starting archived log restore to default destination
channel CH04: restoring archived log
archived log thread=1 sequence=53304
channel CH04: reading from backup piece al_180612_1_828145845
channel CH05: starting archived log restore to default destination
channel CH05: restoring archived log
archived log thread=2 sequence=46816
channel CH05: reading from backup piece al_180611_1_828145845
channel CH06: starting archived log restore to default destination
channel CH06: restoring archived log
archived log thread=1 sequence=53305
channel CH06: reading from backup piece al_180614_1_828153049
channel CH01: piece handle=al_180608_1_828139086 tag=TAG20131006T223805
channel CH01: restored backup piece 1
channel CH01: restore complete, elapsed time: 00:00:25
archived log file name=/a01/flash_recovery_area/1_53302_663959504.dbf thread=1 sequence=53302
channel CH01: starting archived log restore to default destination
channel CH01: restoring archived log
archived log thread=2 sequence=46817
channel CH01: reading from backup piece al_180616_1_828153049
channel CH03: piece handle=al_180609_1_828139086 tag=TAG20131006T223805
channel CH03: restored backup piece 1
channel CH03: restore complete, elapsed time: 00:00:25
channel CH02: piece handle=al_180607_1_828139086 tag=TAG20131006T223805
channel CH02: restored backup piece 1
channel CH02: restore complete, elapsed time: 00:00:40
archived log file name=/a01/flash_recovery_area/2_46813_663959504.dbf thread=2 sequence=0
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/2_46813_663959504.dbf RECID=7 STAMP=828199660
archived log file name=/a01/flash_recovery_area/2_46814_663959504.dbf thread=2 sequence=46814
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/2_46814_663959504.dbf RECID=4 STAMP=828199644
archived log file name=/a01/flash_recovery_area/2_46815_663959504.dbf thread=2 sequence=46815
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/1_53302_663959504.dbf RECID=3 STAMP=828199644
archived log file name=/a01/flash_recovery_area/1_53303_663959504.dbf thread=1 sequence=53303
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/1_53303_663959504.dbf RECID=2 STAMP=828199643
channel CH04: piece handle=al_180612_1_828145845 tag=TAG20131007T003044
channel CH04: restored backup piece 1
channel CH04: restore complete, elapsed time: 00:01:00
archived log file name=/a01/flash_recovery_area/1_53304_663959504.dbf thread=1 sequence=53304
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/2_46815_663959504.dbf RECID=1 STAMP=828199642
channel CH05: piece handle=al_180611_1_828145845 tag=TAG20131007T003044
channel CH05: restored backup piece 1
channel CH05: restore complete, elapsed time: 00:01:00
archived log file name=/a01/flash_recovery_area/2_46816_663959504.dbf thread=2 sequence=46816
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/1_53304_663959504.dbf RECID=5 STAMP=828199657
channel CH01: piece handle=al_180616_1_828153049 tag=TAG20131007T023048
channel CH01: restored backup piece 1
channel CH01: restore complete, elapsed time: 00:01:03
channel CH06: piece handle=al_180614_1_828153049 tag=TAG20131007T023048
channel CH06: restored backup piece 1
channel CH06: restore complete, elapsed time: 00:01:28
archived log file name=/a01/flash_recovery_area/1_53305_663959504.dbf thread=1 sequence=53305
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/2_46816_663959504.dbf RECID=6 STAMP=828199659
archived log file name=/a01/flash_recovery_area/2_46817_663959504.dbf thread=2 sequence=46817
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/1_53305_663959504.dbf RECID=8 STAMP=828199666
channel clone_default: deleting archived log(s)
archived log file name=/a01/flash_recovery_area/2_46817_663959504.dbf RECID=9 STAMP=828199670
media recovery complete, elapsed time: 00:00:08
Finished recover at 07-OCT-13