Archive

Archive for the ‘DBA’ Category

Oracle GoldenGate 11g direction

June 15, 2011 2 comments

Oracle GoldenGate is great for heterogeneous environments by replicating and integrating data across different systems.

This post is about Oracle GoldenGate 11g and implementing a simple replication method. Installing and configuring GoldenGate is quite straightforward. I will not cover this just mention that the source database should be in archivelog mode, have supplemental logging enabled, another thing is to create golden gate user gg_owner. This post will show simple example how to replicate source schema to target, both located on the same Oracle database 11.2.0.2.

1. Users

SQL> CREATE USER SOURCEDB
IDENTIFIED BY …
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

User created.

SQL> GRANT CONNECT, RESOURCE TO SOURCEDB;

Grant succeeded.

SQL> CREATE USER TARGETDB
IDENTIFIED BY 12345678
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

User created.

SQL> GRANT CONNECT, RESOURCE TO TARGETDB;

Grant succeeded.

The purpose is to create replication between the two schemas. Golden Gate is installed in /oracle/gg11

2. Check existing configuration

[oracle@orclbox ~]$ cd /oracle/gg11
[oracle@orclbox gg11]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 11g on Apr 21 2011 22:42:14

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

GGSCI (orclbox) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

GGSCI (orclbox) 2> stop manager
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER …
Request processed.
Manager stopped.

GGSCI (orclbox) 3> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED

GGSCI (orclbox) 4> start manager

Manager started.

GGSCI (orclbox) 5> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING

GGSCI (orclbox) 6> edit params mgr
PORT 7810
~
“/oracle/gg11/dirprm/mgr.prm” 1L, 10C

Manager is configured on default port 7809. In this case I prefer not to use the default one and configure manager on 7810.

3. Create extract s_extr on the source

GGSCI (orclbox) 7> add extract s_extr, tranlog, begin now

2011-06-13 WARNING OGG-01752  Cannot register EXTRACT S_EXTR with database because no database login was provided. You can manually register this group later with the REGISTER EXTRACT <GROUP NAME> LOGRETENTION command. Issue DBLOGIN first.
EXTRACT added.

GGSCI (orclbox) 8> add exttrail /oracle/gg11/dirdat/lt, extract s_extr
EXTTRAIL added.

GGSCI (orclbox) 9> edit params s_extr

extract s_extr
–connection to database
userid gg_owner, password
TRANLOGOPTIONS DBLOGREADER
rmthost orclbox, mgrport 7810
rmttrail /oracle/gg11/dirdat/lt
ddl include mapped objname sourcedb.*;
table sourcedb.*;
~
“/oracle/gg11/dirprm/s_extr.prm”

GGSCI (orclbox)>add exttrail /oracle/gg11/dirdat/lt, extract s_extr

GGSCI (orclbox) 10> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     S_EXTR      00:00:00      00:10:38

4. First add checkpoint to the destination and then create the replicat on the destination (in this case in the same database, Oracle GoldenGate supports this)

GGSCI (orclbox) 11> edit params ./GLOBAL

GGSCHEMA gg_owner
CHECKPOINTTABLE gg_owner.checkpoint

GGSCI (orclbox) 12>dblogin userid gg_owner

Password:
Successfully logged into database.

GGSCI (orclbox) 13>add checkpointtable gg_owner.checkpoint

GGSCI (orclbox) 14> add replicat t_rep, exttrail /oracle/gg11/dirdat/lt,checkpointtable gg_owner.checkpoint
REPLICAT added.

GGSCI (orclbox) 15>edit params t_rep

replicat t_rep
ASSUMETARGETDEFS
–database login
userid gg_owner, password …
discardfile /oracle/gg11/discard/t_rep_disc.dat, append, megabytes 10
–ddl support
DDL
–table mapping
map sourcedb.*, target targetdb.*;

5. Time to test extraction and replication

GGSCI (orclbox) 16> info all

Program     Status      Group       Lag

MANAGER     RUNNING
EXTRACT     STOPPED     S_EXTR      00:00:00
REPLICAT    STOPPED     T_REP       00:00:00

In case you need to investigate if something is not working, you can use ‘info PAR s_extr , detail’ where PAR can be manager, extract, replicat.

GGSCI (orclbox) 17> start extract s_extr

Sending START request to MANAGER …
EXTRACT S_EXTR starting

GGSCI (orclbox) 18> start replicat t_rep

Sending START request to MANAGER …
REPLICAT T_REP starting

GGSCI (orclbox) 19> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     S_EXTR      00:01:15      00:00:07
REPLICAT    RUNNING     T_REP       00:00:00      00:00:04

6. Now let’s start some activity on the source and see the replication in action

In the source schema

SQL> create table sourcedb.sync_table( sync_id number, sync_desc varchar2(50));

Table created.

— check the target schema and see it is already there

SQL> select * from targetdb.sync_table;

no rows selected

SQL> desc targetdb.sync_table;
Name                                      Null?    Type
—————————————– ——– —————————-
SYNC_ID                                            NUMBER
SYNC_DESC                                          VARCHAR2(50)

Insert a test row in the source table and ensure it populates to the target

SQL> insert into sourcedb.sync_table( sync_id , sync_desc ) values ( 1, ‘Description1’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from targetdb.sync_table;

SYNC_ID SYNC_DESC
———- ————————————————–
1 Description1

— add new clob column to the source table and check the replicat

SQL> alter table sourcedb.sync_table add ( sync_detail clob);

Table altered.

SQL> desc targetdb.sync_table;
Name                                          Null?    Type
———————————————————————
SYNC_ID                                                NUMBER
SYNC_DESC                                              VARCHAR2(50)
SYNC_DETAIL                                            CLOB

SQL>

— update value for clob sync_detail column

SQL> update sourcedb.sync_table set sync_detail = ‘ DETAIL1’ where sync_id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from targetdb.sync_table;

SYNC_ID SYNC_DESC                                          SYNC_DETAIL
———- ————————————————– ————-
1 Description1                                        DETAIL1

SQL>

Great synchronization is successful and clob calue is replicated.

Now let’s go on and add a primary key on the source table and see what’s going on:

SQL> alter table sourcedb.sync_table add constraint SYNC_TABLE_PK primary key (SYNC_ID);

Table altered.

SQL> desc targetdb.sync_table;
Name                                                                             Null?    Type
——————————————————————————————————–
SYNC_ID                                                                          NOT NULL NUMBER
SYNC_DESC                                                                                 VARCHAR2(50)
SYNC_DETAIL                                                                               CLOB

SQL> select index_name from all_indexes where owner = ‘TARGETDB’ and table_name = ‘SYNC_TABLE’;

INDEX_NAME
——————————
SYNC_TABLE_PK

We can see the primary key is created in the target table as well. We see both DDL and DML effect of the replication with Oracle GoldenGate.

Cheers,

Maria

Categories: DBA

Using Flashback Database

June 9, 2011 1 comment

 

I have played again with Oracle Flashback on Oracle database 11.2, last time I used it on Oracle database 10g. It is good to double security your production database before huge database upgrades on application level. Though we use storage snapshots we can also use and Oracle flashback database before big upgrades. You know, safety first.

As the Oracle documentation says(both in 10.2 and 11.2 Oracle keeps this section) :

“In practice, guaranteed restore points provide a useful alternative to storage snapshots. Storage snapshots are often used to protect a database before risky operations such as large-scale database updates or application patches or upgrades. Rather than creating a snapshot or duplicate database to test the operation, you can create a guaranteed restore point on a primary or physical standby database. You can then perform the risky operation with the certainty that the required flashback logs are retained.”

 

So here is what we can do step by step:
1. As a prerequisite the Oracle database should be in archivelog

[oracle@orclbox ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production

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

SQL> conn / as sysdba
Connected.
SQL> alter system set log_archive_dest_1=’location=/oracle/test_flash’;

System altered.

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

Total System Global Area 1653518336 bytes
Fixed Size                  2227032 bytes
Variable Size            1275069608 bytes
Database Buffers          369098752 bytes
Redo Buffers                7122944 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/test_flash
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4
SQL>

 

1.1 Configure FRA

SQL> alter system set db_recovery_file_dest_size=4g;

System altered.

SQL> alter system set db_recovery_file_dest=’/oracle/test_flash’;

System altered.

 

Note: If it is a RAC database the FRA should point to the shared storage. Ensure you estimate appropriately db_recovery_file_dest_size and have enough space. STORAGE_SIZE column from V$RESTORE_POINT can help you for that.

 

2. Flashback configuration

SQL> shutdown immediate
SQL> alter system set db_flashback_retention_target=1440; /*1 day retention*/
SQL> alter database flashback on;
SQL> alter database open;

 

3. Flashback time – as in the song Calvin Harris – This is like a flashback 🙂

3.1 Create guaranteed restore point

Before we start the upgrade, create a guaranteed restore point

SQL> create restore point before_upg guarantee flashback database;

Restore point created.

— to view the restore points, can filter them by type – guaranteed or normal
SQL> select NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
2  from V$RESTORE_POINT
3  where GUARANTEE_FLASHBACK_DATABASE=’YES’;

 

3.2 Monitor flashback  v$flashback_database_log

SQL> select oldest_flashback_scn from v$flashback_database_log;

OLDEST_FLASHBACK_SCN
——————–
2882481

 

4. Flashback database example

SQL> create table flashback_test ( col1 number);

Table created.

SQL> insert into flashback_test values(1);

1 row created.

SQL> insert into flashback_test values(2);

1 row created.

SQL> insert into flashback_test values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– ————————————————————-
2955736 2011-06-08 13:05:53.000000000

SQL> truncate table flashback_test;

Table truncated.

SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– —————————————————————-
2955809 2011-06-08 13:07:41.000000000

— table flashback_test is dropped by mistake
SQL> drop table flashback_test;

Table dropped.

SQL> select * from flashback_test;
select * from flashback_test
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– —————————————————————–
2955891 2011-06-08 13:09:35.000000000

 

If  you want to revert the database to SCN=2955736 where flashback_test table has 3 rows

SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to scn 2955736;
SQL> alter database open resetlogs;

SQL> select * from flashback_test;

COL1
———-
1
2
3

 

Done, reverse operation is successful. You can use FLASHBACK TABLE in this case also. I will show that later in this post.

Now let’s try and revert the database up to the moment after the truncate has been executed, SCN=2955809

SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to scn 2955809;
SQL> alter database open resetlogs;

SQL> select * from flashback_test;

COL1
———-
1
2
3

 

Note: Rows are still here.

Now let’s try with delete:

SQL> delete from flashback_test;

3 rows deleted.

SQL> commit;

Commit complete.

SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– —————————————————————–
2956216 2011-06-08 13:24:39.000000000

SQL> insert into flashback_test values(4);

1 row created.
SQL> commit;

SQL> select * from flashback_test;

COL1
———-
4

— Now let’s try and reverse up to the moment after the delete
SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to scn 2956216;
SQL> alter database open resetlogs;

SQL> select * from flashback_test;

no rows selected

SQL>

 

Note: Correct!

 

When finished and all went well with the upgrade, drop the guaranteed restore point

SQL> drop restore point before_upg;

Restore point dropped.

Note: Don’t forget to switch flashback database off when finished so that flashback logs don’t fill up your FRA area.
SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
NO

 

5. You can use flashback table, let’s use user test and create the same table

SQL> create table test.flashback_test ( col1 number);

Table created.

— in order not to get ORA-08189: cannot flashback the table because row movement is not enabled
SQL> alter table test.flashback_test enable row movement;

Table altered.

SQL> insert into test.flashback_test values(1);

1 row created.

SQL> insert into test.flashback_test values(2);

1 row created.

SQL> insert into test.flashback_test values(3);

1 row created.

SQL> commit;

SQL> select current_scn, scn_to_timestamp(current_scn) from v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
———– ——————————————————————–
2957732 2011-06-08 13:58:15.000000000

SQL> delete from test.flashback_test;

3 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from test.flashback_test;

no rows selected

SQL> flashback table test.flashback_test to scn 2957732;

Flashback complete.

SQL> select * from test.flashback_test;

COL1
———-
1
2
3

 

You don’t need to enable row movement for flashback drop as the objects are recovered from the recycle bin:

SQL> alter table test.flashback_test disable row movement;

Table altered.

SQL> select table_name,row_movement FROM all_tables where owner=’TEST’ and table_name=’FLASHBACK_TEST’;

TABLE_NAME                     ROW_MOVE
—————————— ——–
FLASHBACK_TEST                 DISABLED

SQL> FLASHBACK TABLE test.flashback_test TO BEFORE DROP;

Flashback complete.

SQL> select * from test.flashback_test;

COL1
———-
1
2
3

 

Enjoy 🙂

 

 

Regards,

Maria

 

Categories: DBA Tags:

Modify the attribute AUTO_START to “always”

With Oracle 11.2 database auto start policy in the clusterware is restore, which means that clusterware will remember the last state of the database.  If the database was stopped normally then on the next restart of clusterware it won’t be started. Otherwise if the server crashes or by some reason the OS is rebooted then clusterware will start the database because last state was ONLINE (running).

If you are running on Linux there is no need to write your own scripts for automatically startup/shutdown anymore. But you need to configure something.

Here is what it is:
By default with Oracle 11.2 several important resources come in the profile with attribute AUTO_START=restore.  Such resources are Oracle Database resource, Oracle ASM resource, CRS resource type for the Listener. This means that if Oracle database server is restarted for some reason , it will keep and restore the last state.

It is a good practice to change this default behaviour and it is the first thing I usually do after a new installation. I change AUTO_START=always for the resources listed above.

Check out the current state

$ crs_stat -p

Find the resource you’d like to change and modify the attribute AUTO_START to “always”

$ crsctl modify resource resource_name -attr AUTO_START=always

$ crsctl modify resource ora.asm -attr AUTO_START=always

$ crsctl modify resource ora.<SID>.db -attr AUTO_START=always

$ crsctl modify resource ora.LISTENER.lsnr -attr AUTO_START=always

You are done.

Cheers,

Maria

Categories: DBA Tags:

How to recompile a PL/SQL package if it is used by another session?

April 8, 2011 7 comments

 

Imagine you have to recompile a PL/SQL package urgently. In case it is being used and other sessions are executing it , you will not be able to compile this package successfully.

 

What you get is :

ORA-04021: timeout occurred while waiting to lock object error

 

The solution is to find out the locking session and to kill it. Let me say you need to change PCK_TEST package, then:

select * from dba_lock_internal
where lock_id1 like ‘%PCK_TEST%’;

 

Get the session_id and if possible for the system kill that session in order to compile the package.

 

Regards,

Maria

 

Categories: DBA, Development Tags:

Standalone Grid Infrastructure is not upgraded properly after 11201 to 11202 upgrade

February 9, 2011 1 comment

 

I had to upgrade Oracle 11.2.0.1 Database to Oracle 11.2.0.2 running on Linux x86-64. The datafiles and FRA are on ASM so the steps for upgrade are first to upgrade Grid Infrastructure (GI) and then the database. I will not go into details for the whole upgrade but I will mention the steps roughly for completeness:

 

1)6880880 Opatch 11.2 in $GI_HOME

2)9655006 to the 11.2.0.1 GI

3) Oracle GI to 11.2.0.2 patch 10098816(p10098816_112020_Linux-x86-64_3of7.zip)

4) Database software for 11.2.0.2(p10098816_112020_Linux-x86-64_1of7.zip, p10098816_112020_Linux-x86-64_2of7.zip)

 

The main subject of this post is in fact the thing which I will remember after the successful upgrade and it will be on the top of my head for some time. Guess what it is?

All went well, GI and database upgraded to 11.2.0.2 successfully on Red Hat Enterprise Linux 64 bit. But guess what, after subsequent reboot standalone Grid Infrastructure is not starting. Moreover it tried to start old grid.

After some investigation I tried to remove old grid, but the deinstall is not working. Metalink points some manual procedure for reinstalling but at the time of the installation it was not available and posted in Metalink. So I detached and then removed old grid home and database home with rm –rf . This cleaned the environment but it did not help.

The new Grid Infrastructure is not starting. I noticed that “crsctl start has” is hanging.

After some investigation it turned out that the upgrade scripts did not do the upgrade clean. In case of standalone GI root upgrade script finished successfully but missed to copy ohasd  scripts to the right place. It is reported as a Bug 10167269 STANDALONE GI INSTALL: INIT.OHASD/OHASD NOT UPDATED AFTER 11201 TO 11202 UPGRADE.

The workaround to manually copy ohasd  script worked out for me. This is what I did :

 

cp $GRID_HOME/crs/init/init.ohasd /etc/init.d/init.ohasd

cp $GRID_HOME/crs/init/ohasd /etc/init.d/ohasd

kill –HUP 1

crsctl start has

 

In case you happen to know this before the upgrade you can try to fix the script in 11.2.0.2 $GRID_HOME/crs/install/roothas.pl in advance.

 

I am glad finally all went well. Otherwise, the alternative is to start fresh 11.2.0.2 installation from the p10098816_112020 patch.

 

Cheers,

Maria

 

 

Categories: DBA

ORA-600[qsmmixInitKey-2]) detected

January 26, 2011 2 comments

 

I encountered several times this week a critical incident alert in Enterprise Manager as generic internal error notifying for Internal error (ORA-600[qsmmixInitKey-2]) detected in alert\log.xml file.

After investigation it turned out that the reason can be in SQL Tuning Advisor.

The database is 11.2.0.1 . I have Automatic Maintenance Tasks configured and the Automatic SQL Tuning is also enabled.

 

 

 

 

 

 

 

You can refer to Metalink  [ID 9602359.8] that gives overview of this Bug 9602359  OERI[qsmmixInitKey-2] using SQL Tuning Advisor.

Version confirmed as being affected is 11.1.0.7 and 11.2.0.1 for most platforms. In my case the production database is 11.2.0.1. The symptom is occurring internal ORA-600 [qsmmixInitKey-2]. Metalink says that this error can occur when SQL being tuned has duplicate columns. This issue is fixed in 11.2.0.2(Server Patch Set).

In my case I temporary disabled Automatic SQL Tuning and start planning the upgrade to 11.2.0.2.

 

 

 

 

 

BEGIN
dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;

 

This week I had one successful upgrade from  11.2.0.1 to 11.2.0.2 for Database and Grid Infrastructure on RHEL5.

 

This time I will plan and do the upgrade but on Windows Platform.

 

Cheers,
Maria

 

 

Categories: DBA

Encountered Oracle Bug 6392040 while doing exp of table with XMLTYPE column

December 7, 2010 Leave a comment

 

You know when you have a table with XMLTYPE column types then you can use expdp but then the impdb will not succeed if the database is not Oracle 11g(e.g 10.2 in my case).  Data Pump Export and Data Pump Import utilities do not support XMLType data in this case.  So we need to use exp and imp utilities. Go on and export the table with the XMLTYPE column with exp and here is what we get:

 

C:\export>exp system/psw@SID FILE=exp_xmldata.dmp TABLES=TEST
TEMPLATES LOG=exp_xmldata.log statistics=none

Export: Release 10.2.0.3.0 – Production on TЄ -хъ. 7 10:39:06 2010

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

EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 15:
PLS-00201: identifier ‘SYS.DBMS_EXPORT_EXTENSION’ must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully

 

We can check for invalid objects and recompile mannually if it is the case

 

SELECT OBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE STATUS =
‘INVALID’ AND OWNER = ‘SYS’;
ALTER PACKAGE SYS.DBMS_EXPORT_EXTENSION COMPILE BODY;

 

In my case this was not. The workaround for this is to:

 

GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO system;

 

And then it worked and the export finished successfully.

 

C:\export>exp system/psw@SID FILE=exp_xmldata.dmp TABLES=TEST
TEMPLATES LOG=exp_xmldata.log statistics=none

Export: Release 10.2.0.3.0 – Production on TЄ -хъ. 7 10:40:07 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit
Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export done in CL8MSWIN1251 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path …
Current user changed to RBANK
. . exporting table                      TEMPLATES          10 rows exported
Export terminated successfully without warnings.

 

This is an Oracle Bug 6392040 and you can check Metalink 464672.1.  The cause is:
“There are two execute privileges missing in the DBA role and  the EXP_FULL_DATABASE role.
There is no fix in this bug because the export utility is not longer supported in 11g and should be replaced by the Data Pump Export.”

 
Cheers,
Maria

 

 

Categories: DBA Tags:
%d bloggers like this: