Installation & Configuration of Golden Gate on RAC using ASM:

1.    Installation:

1. Go to https://edelivery.oracle.com/

2. Select Oracle Fusion MiddleWare

3. Linux X86/64

4. I downloaded Oracle GoldenGate on Oracle v11.1.1.0.0 Media Pack for Linux x86-64

5. Extracted on /u01/goldengate

2.       Create skeletons:

Source DB:

1.       Create the necessary working directories for GG.

[oracle@db1 gg]$ ./ggsci
GGSCI (db1) 1>create subdirs
GGSCI (db1) 1>exit
[oracle@db1 gg]$ mkdir $GGATE/discard

Target DB:

Do the same alike source DB

3. Configure Source/Destination database

The GoldenGate software having been installed successfully, we must prepare the source database for replication.

Source DB:

1.    Switch the database to archivelog mode:

      SQL> shutdown immediate

      SQL> startup mount

      SQL> alter database archivelog;

      SQL> alter database open;

 

 2. Enable minimal supplemental logging:

     

SQL> alter database add supplemental log data;

   3. Prepare the database to support ddl replication (optional).

      a) Turn off recyclebin for the database . . .

      SQL> alter system set recyclebin=off scope=spfile;

Though it’s not required for 11g and onwards.

       . . . and bounce it.

 b) Create schema for ddl support replication . . .

 SQL> create user ggate identified by qwerty default tablespace users temporary tablespace temp;

      . . . and grant the necessary privileges to the new user..

      [oracle@db1 gg]$ cd $GGATE

      

      SQL> grant connect,resource,unlimited tablespace to ggate;

      SQL> grant execute on utl_file to ggate;

      c) Run scripts for creating all necessary objects for support ddl replication:

      SQL> @$GGATE/marker_setup.sql

      SQL> @$GGATE/ddl_setup.sql

      SQL> @$GGATE/role_setup.sql

      SQL> grant GGS_GGSUSER_ROLE to ggate;

      SQL> @$GGATE/ddl_enable.sql

Create test schemas for replication. I will create a replication from schema sender to schema receiver (the receiving schema can be on the same database or on another).

a) Source database:

SQL> create user sender identified by qwerty default tablespace users temporary tablespace temp;

SQL> grant connect,resource,unlimited tablespace to sender;

b) Destination database:

SQL> create user receiver identified by qwerty default tablespace users temporary tablespace temp;

SQL> grant connect,resource,unlimited tablespace to receiver;

4.Replication

We’re going to create the simplest replication without the GG data pump (we can add it later). Our goal is to create ddl and ddl replication from the sender schema on the source database to receiver schema on the destination.

Replication also works if you’re using only one database. This is replication between schemas.

   1. Create and start manager on the source and the destination.

Source DB:

      [oracle@db1 gg]$ cd $GGATE

      [oracle@db1 gg]$ ./ggsci

      

      GGSCI (db1) 4> info all

      

      Program     Status      Group       Lag           Time Since Chkpt

      MANAGER     STOPPED

      

      GGSCI (db1) 6> edit params mgr

      PORT 7809

      

      GGSCI (db1) 7> start manager

      Manager started.

      We can check status of our processes:

      GGSCI (db1) 8> info all

      Program     Status      Group       Lag           Time Since Chkpt

      MANAGER     RUNNING

Create the extract group on the source side:

GGSCI (db1) 1> add extract extdm, tranlog, begin now

EXTRACT added.

GGSCI (db1) 2> ADD RMTTRAIL /u01/goldengate/dirdat/rt, EXTRACT exdm

GGSCI (db1) 3> edit params exdm

Add the following lines to the new parameter file for our extract:

EXTRACT exdm

--connection to database--

userid ggate, password qwerty

--specify redo/archive location

TRANLOGOPTIONS ALTARCHIVELOGDEST /u03/oralogs/

--specify ASM connectivity as we are using ASM

TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD DBApassword
--specify target database

rmthost warehouselab, mgrport 7809

rmttrail /u01/app/oracle/product/gg/dirdat/lt

--DDL support

ddl include mapped objname sender.*;

--DML

table sender.*;

We can check our processes again:

GGSCI (db1) 6> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED

EXTRACT     STOPPED     EXT1        00:00:00      00:10:55

Create replicat on the destination side:

[oracle@db2 gg]$ cd $GGATE

[oracle@db2 gg]$ ./ggsci

add checkpoint table to the destination database

GGSCI (db2) 1> edit params ./GLOBAL

and put following lines to the global parameter file:

GGSCHEMA ggate

CHECKPOINTTABLE ggate.checkpoint

~

GGSCI (db2) 2> dblogin userid ggate

 Password:

 Successfully logged into database.

GGSCI (db2) 3> add checkpointtable ggate.checkpoint

 Successfully created checkpoint table GGATE.CHECKPOINT.

Create replicat group:

GGSCI (db2) 4> add replicat rep1, exttrail /u01/goldengate/dirdat/rt,checkpointtable ggate.checkpoint

REPLICAT added.

***** Note that the EXTTRAIL location which is on the target local system conforms to the RMTTRAIL parameter which we used when we created the parameter file for the extract process on the source system.

Create parameter file for replicat:

GGSCI (db2) 5> edit params rep1

--Replicat group --

replicat rep1

--source and target definitions

ASSUMETARGETDEFS

--target database login --

userid ggate, password qwerty

--file for dicarded transaction --

discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10

--ddl support

DDL

--Specify table mapping ---

map sender.*, target receiver.*;

# Start extract and replicat:

Source:

GGSCI (db1) 14> start extract exdm

Destination:

GGSCI (db2) 15> start replicat rep1

# Check all processes.

Source:

GGSCI (db1) 8> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

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

Destination:

GGSCI (db1) 8> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

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

Our replication has been successfully created.

5.    Checking

Now we can check our replication. We will create some tables in the sender schema on the source, insert some rows, and check how it will replicate to destination side.

   1. Source database:

      SQL> create table sender.test_tab_1 (id number,rnd_str varchar2(12));

      SQL> insert into sender.test_tab_1 values (1,'test_1');

      SQL>commit;

   2. Destination database:

      SQL> select * from receiver.test_tab_1;

      

      ID RND_STR

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

      1 test_1

Our GoldenGate DDL and DML replication is now working. The table was created on the destination side and data were replicated.

You may want to follow the below thread for ASM configuration:
ORACLE @ Work: Oracle ASM for Oracle GoldenGate extract

dot in gmail !!

Came to know something interesting . Suppose you created an email id in gmail like first.last@gmail.com. Now whether someone mails at first.last@gmail.com or firstlast@gmail.com or you place dots at any other place or remove them, the mail will land in your inbox. Reason: Google’s system ignores dots. But at the time of login you need to use the actual email id which you have created.

Said that its clear that when someone has already registered as first.last now no-one can use any other combination like firstlast or firstlas.t as everything has already been associated with your id.

Interesting isn’t it !

/usr/bin/find: Argument list too long error

When /usr/bin/find: Argument list too long error

USE THIS:

find /u01/app/oracle/diag/rdbms/TEST/TEST/trace/ -name '*.trm'  -mtime +30 -exec /bin/rm {} \;

INSTEAD OF THIS!!!

find /u01/app/oracle/diag/rdbms/TEST/TEST/trace/*.trc +30 -exec /bin/rm -rf {} \;

READ ONLY tables in oracle 11g

In Oracle 11g, we can change the table to READ ONLY mode which doesn't allow any DML over it.

Below is a tested scenario.

SQL> alter table scott.emp read only;

Table altered.

SQL> select

owner,

table_name,

read_only from dba_tables

where owner='SCOTT'

and table_name='OBJECTS';

OWNER TABLE_NAME READ_ONLY

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

SCOTT EMP YES

SQL> update scott.emp

set owner='SCOTT';

update objects

*

ERROR at line 1:

ORA-12081: update operation not allowed on table "EMP"."SCOTT"

SQL> delete from scott.emp where empno = 7369;

ERROR at line 1:

ORA-12081: update operation not allowed on table "SCOTT"."EMP"

Now change the mode of table to read write again.

SQL> alter table scott.emp read write;

Table altered.

SQL> select owner,table_name,read_only from dba_tables where owner='SCOTT'

2 and table_name='EMP';

OWNER TABLE_NAME READ_ONLY

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

SCOTT EMP NO

SQL> truncate table SCOTT.EMP;

Table truncated.

Log all database errors to a table

drop trigger log_errors_trig;
drop table   log_errors_tab;

create table log_errors_tab (
 error     varchar2(30),
 timestamp date,
 username  varchar2(30),
        osuser    varchar2(30),
        machine   varchar2(64),
 process   varchar2(8),
 program   varchar2(48));

create or replace trigger log_errors_trig 
 after servererror on database
declare
 var_user     varchar2(30);
 var_osuser   varchar2(30);
 var_machine  varchar2(64);
 var_process  varchar2(8);
 var_program  varchar2(48);
begin
 select username, osuser, machine, process, program
 into   var_user, var_osuser, var_machine, var_process, var_program
 from   sys.v_$session
 where  audsid = userenv('sessionid');

 insert into log_errors_tab
   values(dbms_standard.server_error(1),sysdate,var_user,
          var_osuser,var_machine,var_process,var_program);
end;
/

Problem: ORA-10631 errors encountered during "SHRINK SPACE" operation

OEM was nice the other day and offered some Segment Adviser Recommendations to shrink a few tables that had become fragmented. However, when I ran the shrink job, it failed.

PROBLEM:

• "ALTER TABLE my_test_table SHRINK SPACE;" results in "ORA-10631: SHRINK clause should not be specified for this object"

SOLUTION:

• Tables that have function-based indexes cannot be compacted using the SHRINK clause
• Apparently, someone forgot to inform OEM of this fact (thanks the bogus recommendations! :-)
• See MetaLink Bug No.:5968539

Here's a SQL statement that shows all the tables in a given schema and whether they are candidates for the SHRINK clause:

SELECT   dt.owner, dt.table_name,
         (CASE
            WHEN NVL(ind.cnt, 0) < 1 THEN 'Y'
            ELSE 'N'
         END) AS can_shrink
    FROM dba_tables dt,
         (SELECT   table_name, COUNT(*) cnt
              FROM dba_indexes di
             WHERE index_type LIKE 'FUNCTION-BASED%'
          GROUP BY table_name) ind
   WHERE dt.table_name = ind.table_name(+)
     AND dt.table_name NOT LIKE 'AQ$%'
     AND dt.table_name NOT LIKE 'BIN$%'
     AND dt.owner = 'NADVI'
ORDER BY 1, 2;

SED – Search and replace string:

If you want to change all occurrences of developer to administrator in the oracle.txt file in the grep example, enter this:

Content of oracle.txt

Oracle database developer

SQL database developer

MYSQL database developer

sed 's/developer/administrator/g' oracle.txt

See the changes below after running SED.

oracle database administrator

SQL database administrator

MYSQL database administrator

In the quoted string, the "s" means substitute, and the "g" means make a global change. You can also leave off the "g" (to change only the first occurrence on each line) or specify a number instead (to change the first n occurrences on each line).

Searching and replacing several strings on a file:

example:

sed 's/-//g' test.txt |sed 's/"//g' | sed 's/;//g' | sed 's/\[//g'

Take care of any escape character using additional ‘\’ while searching for ‘[‘

Temp Tablespace recreate to resize / Shrink

In 11g, there is a new SQL command that can be used to shrink temporary tablespaces. This command can be used to shrink only locally managed temporary tablespaces :

ALTER TABLESPACE tablespace SHRINK SPACE | TEMPFILE tempfile_name [KEEP size];

The SHRINK SPACE clause allows a user to shrink a temporary tablespace, whereas SHRINK TEMPFILE allows the shrink of a temporary file. The optional KEEP clause defines the lower bound that a tablespace can be shrunk to. It is the opposite for MAXSIZE for auto extensible tablespace. If it is not specified, the system will try to shrink as much as possible, as long as the other storage attibutes are satisfied. Otherwise, shrink will stop once the tablespace/tempfile already reaches the size specified through the KEEP option.  

Below steps are OK for any Oracle Versions Release: 8.1.5 to 11.2


1. Create another temp tablespace with moderate size on temp tablespace datafile location

CREATE  TEMPORARY TABLESPACE "TEMP2"

TEMPFILE '/mylocation/temp/temp02.dbf' SIZE 3072M REUSE AUTOEXTEND ON NEXT  100M MAXSIZE  32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

2. Make the new TEMP tablespace as default for database.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Drop old big default tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

4. Re create that big TBS with new size

CREATE

TEMPORARY TABLESPACE "TEMP" TEMPFILE '/mylocation/temp/temp01.dbf' SIZE 3072M REUSE AUTOEXTEND ON NEXT  100M MAXSIZE  32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

5.  Make the resized TEMP TBS as default for Database.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

6. Drop second temp tablespace.

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

While trying to drop first temp tbs, if users are still using that tablespace you won't be able to drop it. 


Following is the work around:

1. Find out the all the sessions that are not active and have an entry in V$sort_usage.
You can do it by

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

2. Kill those session Using
alter system kill session 'SID_NUMBER, SERIAL#NUMBER'; kill those session that are not being used actually. where SID_NUMBER and SERIAL#NUMBER is found in step 


3. Drop old tablespace 

Create password file for SYS

Cd to $ORACLE_HOME/dbs

orapwd file=orapw password=<>same as SYS> entries=10

Recreate EM DBCONSOLE :

Remove the following directories from your filesystem:
/
/oc4j/j2ee/OC4J_DBConsole__

If the dbcontrol is upgraded from lower version, for example, from
10.2.0.3 to 10.2.0.4, then the following directory also needs to be removed from the file system.
/.upgrade
/oc4j/j2ee/OC4J_DBConsole__.upgrade

NOTE:
On Windows you also need to delete the DB Console service:
- run regedit
- navigate to HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services
- locate the OracleDBConsole entry and delete it

Alternatively on Windows XP and Windows Server 2003 you can run the following from the command line:
'sc delete '
- where is the DB Control service name (typically: OracleDBConsole)

OR use the command,

'nmesrvops delete '
- where is the DB Control service name (typically: OracleDBConsole)

Also available from Microsoft is the delsrv.exe command. (Free download from Microsoft)



Step 1: Drop AQ related objects in the SYSMAN schema
Logon SQLPLUS as user SYS
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'SYSMAN.MGMT_NOTIFY_QTABLE',force=>TRUE);

Step 2: Drop the DB Control Repository Objects

Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT;
SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
SQL> EXEC sysman.setEMUserContext('',5);
SQL> REVOKE dba FROM sysman;
SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/

SQL> DROP USER mgmt_view CASCADE;
SQL> DROP ROLE mgmt_user;
SQL> DROP USER sysman CASCADE;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;


to create the dbconsole you need to:

ORACLE_HOME/bin/emca -config dbcontrol db -repos create

Rename ASM Diskgroups

Rename diskgroup option is a new feature of 11gR2  but can be done for 10g & 11gR1 as well.
It requires installing 11gR2 SIHA Grid Infrastructure software only installation.

cd <11gr2 grid home>/bin
use renamedg tool to rename 10g or 11gR1 diskgroup.
Diskgroup cannot be renamed under following conditions :

- the Diskgroup is mounted
- the Diskgroup is being used by CSS
- Diskgroup contains offline disks

1) First of all, the desired diskgroup needs to be dismounted (from each node if this is a RAC configuration):

# asmcmd umount DATA

2) Verify that the desired diskgroup was dismounted:

# asmcmd lsdg

3) Then execute the rename statement:

# renamedg phase=both dgname=DATA newdgname=ORACLE_DATA1 verbose=true

4) Finally, mount the renamed diskgroup and validate that it was mounted (on each node if this is a RAC configuration):

# asmcmd mount ORACLE_DATA1

# asmcmd lsdg

DROP QUEUE TABLES

While trying to drop a user with QUEUE table objects; it won't let you drop with below warnings.

ORA-00604: error occurred at recursive SQL level 1
ORA-24170: SCOTT.TEST_QUEUE_R is created by AQ, cannot be dropped directly
ORA-06512: at "SYS.DBMS_RULE_ADM", line 158
ORA-06512: at line 1

It requires to drop QUEUE tables first from user and then can be dropped.

--FIND THE QUEUE TABLES BY LOGGING as USER SCOTT.

select * from dba_tables
where table_name like 'AQ%'
and tablespace_name = 'USERS'

-- Grant execute on DBMS_AQDM to the user SCOTT having queue tables.

GRANT EXECUTE ON DBMS_AQADM to SCOTT


-- LOGIN AS SCOTT DROP QUEUE TABLES

select * from user_tables
where table_name like 'AQ%'

exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_TEST_IAI_NR',force => TRUE);

--- NOW DROP SCOTT as SYS