Istvan Stahl's Oracle blog

Just another Oracle DBA site

Oracle Database Appliance, Golden Gate or Optimizer tricks

leave a comment »

Written by Istvan Stahl

2012/04/21 at 23:10

End of holiday season at Oracle

leave a comment »

It seems that the summer season ended at Oracle as well.

This week they have announced the Oracle Database Appliance (tech spec here), and today the 11.2.0.3 database patchset became also available for download from Metalink (you can download it from here).

As per the documentation, there are not too many new features in this patchset compared to 11.2.0.2. Hopefully more bugfixes :)

Off-topic: I have to speed up reading the Expert Oracle Exadata book, as on 17 Oktober Jonathan Lewis’es new book “Oracle Core: Essential Internals for DBAs and Developers” will be published at Apress.

Written by Istvan Stahl

2011/09/23 at 16:19

Posted in Uncategorized

Expert Oracle Exadata is available at Apress

leave a comment »

Expert Oracle Exadata is available since 08.Aug.2011 at Apress.com.

However, I have never worked with Exadata – and most probably will not work with it in the coming months/years -, I just got the eBook version of it on my iPad right now.

So, happy reading for the weekend :)

… will come up with some review once I’m done with it.

Written by Istvan Stahl

2011/08/19 at 13:21

Posted in books

Tagged with ,

FREE !!! Oracle Database training video collection – internals, best practices

leave a comment »

During the summer – when there is really not too much work going on – you probably have a chance to read some books, do some online trainings and so on. If you are interested in some oracle internal stuff, take a look at the collection below.

The collection has three parts:

1. Oracle Internals, SQL tuning and RAC internals by Tanel Pőder, Jonathan Lewis and Riyaj Shamsudeen

2. PL/SQL by Steven Feuerstein

3. DBA 2.0 youtube series (just to have some fun)

I’d suggest, before your headache starts, take a look at one of the DBA 2.0 series videos – I still love them – and then continue :)

So, here they are:

1. a) http://bit.ly/qnf0fd

Here you will find two videos provided by Tanle Pőder, which you can download to iPAD as well. I liked them really!

Oracle Troubleshooting TV show: Season 1 – Episode 1: ORA-4031 errors and shared pool memory troubleshooting

Oracle Troubleshooting TV show: Season 1 – Episode 2: 2 hour session which takes 4 hours :)

In the second video Tanel shows where you can get his powerful script library. http://tech.e2sn.com/oracle-scripts-and-tools

b) Tuning a two-table join by Jonathan Lewis

c) RAC Hacking Session by Riyaj Shamsudeen: Probing LMS Process Internals With Advanced UNIX Utilities

2. Best PL/SQL Video Series by Steven Feuerstein

3. and finally the DBA 2.0 video Series

Oracle Database related:

DBA 2.0 – SQL Tuning

DBA 2.0 – Managing Change

Feeling the Storage Squeeze? (my favourite one)

Sure You’re Secure?

Bye Buy Idle Redundancy

No Time for Downtime?

Oracle Exadata related:

Oracle Exadata: Consolidating Database Applications

Oracle Exadata: A Single Source of Truth

APEX related:

Spreadsheets or Simplicity?


Written by Istvan Stahl

2011/08/16 at 10:50

Posted in videos

Tagged with , ,

RAC alert log synchro reader for SQL

leave a comment »

Few years ago, I have seen a post on the OakTable network about RAC synhro reader for the alert logs (you can still find it here). At that time the download link was not working, so I thought, let me write my own solution, and do it in SQL.

This method was designed for 10g. In 11g there are lots of new features, and probably a better solution as well.

Challenges and answers:

  1. How to access the alert log from SQL – External tables
  2. How to access the right access log in a RAC environment independently from which node you are running the select statement – Database Link
  3. Unfortunately the alert logs are not very well formatted. How can we put the date of the event in one line with the event – LAST_VALUE() analytical function
  4. How can we setup a timeline (different events from different nodes at different time) – simple view with UNION on the date, and then FULL OUTER JOIN based on the date

Things to consider:

  • if your alert logs are huge, the processing can take lots of time. (setup a policy for the alert log maintenance)

Now, let us look at the steps in detail (the below example is based on a 2 node RAC, but it can be extended)

1. First create two database links. One for each instance.

CREATE DATABASE LINK node1 CONNECT TO system IDENTIFIED BY  USING 'instance1';
CREATE DATABASE LINK node2 CONNECT TO system IDENTIFIED BY  USING 'instance2';

Make sure that the tnsnames.ora entries behind the “instance1″ and “instance2″ aliases, are pointing directly to the right instance. You can use the INSTANCE_NAME parameter to achive this. For example:

instance1 =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = testdb)
 (INSTANCE_NAME = testdb1)
 )
 )

instance2 =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = testdb)
 (INSTANCE_NAME = testdb2)
 )
 )

2. Create a database directory, which will be used to access the alert logs. Usually the alert logs are in the same patch on each node (if not, then you can create different directories), so we need only one directory alias:

create directory bdump as '/u01/app/oracle/admin/testdb/bdump';

3. Create the external tables.

Things to know about:

  • Check the name of the alert log file for each instance
  • These external tables doesn’t guarantee that accessing data will work, it depends on from which instance you access them (the only exception is, if you have a shared ORACLE_HOME)
  • The max length of one line in the alert log. You can check it in linux, using: “wc -L” . In my case VARCHAR2(500) should be sufficient
drop table alert_log1;
CREATE TABLE alert_log1
 (
 msg VARCHAR2(500)
 )
 ORGANIZATION EXTERNAL
 (
 TYPE ORACLE_LOADER DEFAULT DIRECTORY bdump1 ACCESS PARAMETERS (
 records delimited BY newline
 ) LOCATION ( 'alert_testdb1.log' )
 )
 REJECT LIMIT UNLIMITED;

drop table alert_log2;
CREATE TABLE alert_log2
 (
 msg VARCHAR2(500)
 )
 ORGANIZATION EXTERNAL
 (
 TYPE ORACLE_LOADER DEFAULT DIRECTORY bdump1 ACCESS PARAMETERS (
 records delimited BY newline
 ) LOCATION ( 'alert_testdb2.log' )
 )
 REJECT LIMIT UNLIMITED;

4. Create the views

With these views we achieve two things:

  • througt these views we can access the appropriate alert logs idenpendently from the current instance we are logged on
  • we will achieve the date of event and the event message separation

The format of the date in the alert log may wary.

create or replace view alert_log1_v as
select rownum as "ROW1" ,last_value(datetime ignore nulls) over (order by rownum ) datetime, msg from
(select case
 when msg like '___ ___ % __:__:__ ____' then to_date(upper(msg),'DY MON fmDD hh24:mi:ss yyyy')
 else null
 end datetime,
 msg
from alert_log1@node1);

create or replace view alert_log2_v as
select rownum as "ROW1" ,last_value(datetime ignore nulls) over (order by rownum ) datetime, msg from
(select case
 when msg like '___ ___ % __:__:__ ____' then to_date(upper(msg),'DY MON fmDD hh24:mi:ss yyyy')
 else null
 end datetime,
 msg
from alert_log2@node2);

5. Now we need a list of unique datetimes (simple UNION does it for us)

create or replace view alert_log_dates_v as
select datetime from alert_log1_v
union
select datetime from alert_log2_v;

6. And the last step is to put everything together.

create or replace view alert_logs_v as
select da.datetime, a.msg as node1, b.msg as node2
 from alert_log_dates_v da
 full outer join alert_log1_v a on da.datetime=a.datetime
 full outer join alert_log2_v b on da.datetime=b.datetime
order by da.datetime, a.row1, b.row1

7. And you are ready to use it…

select * from alert_logs_v;

I hope you will be able to use it.

Written by Istvan Stahl

2011/07/19 at 15:16

RMAN & Golden Gate

leave a comment »

OK. Let’s do some blogging :)

In my first blog entry I’ll cover how rman can be used to instantiate a database which will be kept in sync later using Golden Gate.

Prerequisites:
- source database (10.2.0.5) called:  source (everything in ASM, this adds one more challenge to configure Golden Gate)
- on the same host we are going to create the target database, and setup the golden gate replication from a particular SCN

The steps for this tutorial:

1. Create you source database using dbca  (make sure you have ASM instance and listener on 1521 port ready).

I don’t want to put any comment on this one, as it should be very straighforward. Your spfile should look something similar to this one

source.__db_cache_size=1191182336
source.__java_pool_size=16777216
source.__large_pool_size=16777216
source.__shared_pool_size=352321536
source.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/source/adump'
*.background_dump_dest='/u01/app/oracle/admin/source/bdump'
*.compatible='10.2.0.5.0'
*.control_files='+DATA_DG/source/controlfile/current...','+DATA_DG/source/controlfile/current...'
*.core_dump_dest='/u01/app/oracle/admin/source/cdump'
*.db_block_sq20ize=8192
*.db_create_file_dest='+DATA_DG'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='source'
*.db_recovery_file_dest='+DATA_DG'
*.db_recovery_file_dest_size=104857600000
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sourceXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=+DATA_DG/'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=3424649216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/source/udump'

2. Download the Golden Gate sofwtare (Version 11.1.1.1 for Oracle 10g on Linux x86-64) from OTN

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

This is the fastest way to get the software however – as you can see at the bottom of the page -, I’d recommend using the edelivery.oracle.com site, as you can download the differnt Golden Gate products for different Databases only from here. Yes, you will have to use different software if you want to setup replication between different database products (eg. MySQL vs. Oracle)

3. Let us keep it simple and put the GG sofware in the oracle users home directory (~/source – for the source database; ~/target – for the target database)

oracle@vm-ora2:~$ mkdir source
oracle@vm-ora2:~$ cp fbo_ggs_Linux_x64_ora10g_64bit.zip source/
oracle@vm-ora2:~$ cd source/
oracle@vm-ora2:~/source$ unzip fbo_ggs_Linux_x64_ora10g_64bit.zip
oracle@vm-ora2:~/source$ tar xf fbo_ggs_Linux_x64_ora10g_64bit.tar
oracle@vm-ora2:~/source$ cd ..
oracle@vm-ora2:~$ cp -r source target

4.  Configure the source database
In this step we are going to setup the schema which will be used by Golden Gate to connect to our source database (and also later on after duplicating this database to target, it will be used on the target side as well)

oracle@vm-ora2:~$ . oraenv
ORACLE_SID = [oracle] ? source
oracle@vm-ora2:~$ sqlplus -x / as sysdba

SQL> create tablespace ggs_data datafile size 200m autoextend on;

Tablespace created.

SQL> create user ggs_owner identified by ggs_owner default tablespace ggs_data temporary tablespace temp;

User created.

SQL> grant connect,resource to ggs_owner;

Grant succeeded.

SQL> grant select any dictionary, select any table to ggs_owner;

Grant succeeded.

SQL> grant create table to ggs_owner;

Grant succeeded.

SQL> grant flashback any table to ggs_owner;

Grant succeeded.

SQL> grant execute on dbms_flashback to ggs_owner;

Grant succeeded.

SQL> grant execute on utl_file to ggs_owner;

Grant succeeded.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

The following ones are documented in MOS note: 1330577.1

SQL> exec dbms_streams_auth.grant_admin_privilege('ggs_owner');

PL/SQL procedure successfully completed.

SQL> grant insert on system.logmnr_restart_ckpt$ to ggs_owner;

Grant succeeded.

SQL> grant update on sys.streams$_capture_process to ggs_owner;

Grant succeeded.

SQL> grant become user to ggs_owner;

Grant succeeded.

5. Create a simple demo schema in the source database (the column “text” is clob just to try if replication of log columns is working as well)


SQL> grant create session, create table to demo identified by demo;

SQL> alter user demo quota unlimited on users;
SQL> conn demo/demo
Connected.
SQL> create table mytable (id number primary key, name varchar2(50), text clob);

Table created.
SQL> insert into mytable values (1, 'Istvan Stahl', 'istvanstahl.worldpress.com');

1 row created.

SQL> commit;

Commit complete.

6. Backup your source database

oracle@vm-ora2:~$ . oraenv
ORACLE_SID = [oracle] ? source
oracle@vm-ora2:~$ rman target /
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> backup database plus archivelog;

7. Configure source GG

First of all create a directory for the target’s trail files (we will need this during the source GG setup):

oracle@vm-ora2:~$ mkdir -p /home/oracle/target/dirdat/rt

Now, configure the GG source site (manager and the extract process)

oracle@vm-ora2:~$ cd source
oracle@vm-ora2:~/source$ ggsci

GGSCI (vm-ora2) 1> create subdirs
 GGSCI (vm-ora2) 2> edit params mgr
 PORT 7777
 USERID ggs_owner, PASSWORD ggs_owner
 PURGEOLDEXTRACTS /home/oracle/source/dirdat/ex, USECHECKPOINTS
 GGSCI (vm-ora2) 3> start mgr

Manager started.

GGSCI (vm-ora2) 4> info all
 Program Status Group Lag Time Since Chkpt
 MANAGER RUNNING

GGSCI (vm-ora2) 5> EDIT PARAMS ./GLOBALS
GGSCHEMA GGS_OWNER
CHECKPOINTTABLE GGS_OWNER.CHKPTAB
GGSCI (vm-ora2) 6> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner
Successfully logged into database.

GGSCI (vm-ora2) 7> ADD CHECKPOINTTABLE GGS_OWNER.CHKPTAB

Successfully created checkpoint table GGS_OWNER.CHKPTAB.

GGSCI (vm-ora2) 8> ADD EXTRACT ext1, TRANLOG, BEGIN NOW

2011-07-08 16:42:07  INFO    OGG-01749  Successfully registered EXTRACT EXT1 to start managing log retention at SCN 401678.
EXTRACT added.
GGSCI (vm-ora2) 9> ADD RMTTRAIL /home/oracle/target/dirdat/rt, EXTRACT ext1
RMTTRAIL added.
GGSCI (vm-ora2) 10> EDIT PARAMS ext1
EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST vm-ora2, MGRPORT 8888
RMTTRAIL /home/oracle/target/dirdat/rt
TABLE demo.mytable;
TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD *****

In the last line we had to specify the connection details to the ASM instance, without this GG is not able to mine the logs.

8. Now let’s create the target database using RMAN

First backup the newly created archive logs of the source database, and create a pfile.

oracle@vm-ora2:~$ . oraenv
ORACLE_SID = [oracle] ? source
oracle@vm-ora2:~$ rman target /
RMAN> sql 'alter system switch logfile';
RMAN> backup archivelog all;

RMAN> sql 'create pfile from spfile';

add an entry for the target database to the /etc/oratab file:

oracle@vm-ora2:~$ echo "target:/u01/app/oracle/product/10.2.0/db_1:N" >> /etc/oratab

Create the password file:

oracle@vm-ora2:~$ . oraenv
ORACLE_SID =
1 ? target
oracle@vm-ora2:~$ cd $ORACLE_HOME/dbs
oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ orapwd file=orapwtarget password=oracle entries=5

Edit the pfile, so that it can be used by the target database and create the required directories:

oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ cp initsource.ora inittarget.ora
oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ sed -i "s/source/target/g" inittarget.ora
oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ sed -i "/control_files/d" inittarget.ora
oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/adump
oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/bdump
oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/cdump
oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ mkdir -p /u01/app/oracle/admin/target/udump

Finally start the database using spfile:

oracle@vm-ora2:/u01/app/oracle/product/10.2.0/db_1/dbs$ sqlplus -x / as sysdba
SQL> create spfile from pfile;
File created.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes

Duplicate the source database to target (you can use the SCN number what you have got when you created the extract process from Golden Gate, in our case 401678)

oracle@vm-ora2:~$ rman target sys/oracle@source auxiliary /
connected to target database: SOURCE (DBID=2874333303)
connected to auxiliary database: TARGET (not mounted)
RMAN> duplicate target database to "target" until scn 401678;

Now, grant write access for the ggs_owner on our demo table:

oracle@vm-ora2:~$ sqlplus -x / as sysdba

SQL> grant all on demo.mytable to ggs_owner;

Grant succeeded.

9. Configure GG for the target database

oracle@vm-ora2:~$ . oraenv
ORACLE_SID = [oracle] ? target
oracle@vm-ora2:~$ cd target/
oracle@vm-ora2:~/target$ ggsci
GGSCI (vm-ora2) 1> create subdirs
GGSCI (vm-ora2) 2> edit params mgr
port 8888
USERID ggs_owner, PASSWORD ggs_owner
GGSCI (vm-ora2) 3> start mgr

Manager started.

GGSCI (vm-ora2) 4> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
GGSCI (vm-ora2) 5> ADD REPLICAT rep1, EXTTRAIL /home/oracle/target/dirdat/rt, CHECKPOINTTABLE GGS_OWNER.CHKPTAB
REPLICAT added.
GGSCI (vm-ora2) 6> edit params rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP demo.mytable, TARGET demo.mytable;

10. Now start the source extratct and the target replicat
Start the extract:

oracle@vm-ora2:~/target$ . oraenv
ORACLE_SID = [target] ? source

oracle@vm-ora2:~/target$ cd ../source
oracle@vm-ora2:~/source$ ggsci
GGSCI (vm-ora2) 1> start extract ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (vm-ora2) 2> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:01:01      00:00:06

Look at the log if any problem:

GGSCI (vm-ora2) 3> view ggsevt

and start the replicat process after the appropriate scn:

oracle@vm-ora2:~/source$ . oraenv
ORACLE_SID = 1 ? target
oracle@vm-ora2:~/source$ cd ../target

oracle@vm-ora2:~/target$ ggsci
GGSCI (vm-ora2) 3> start replicat rep1, aftercsn 401678

Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (vm-ora2) 4> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:07

11. Do some testing

oracle@vm-ora2:~$ . oraenv
ORACLE_SID = 1 ? source
oracle@vm-ora2:~$ sqlplus -x demo/demo

SQL> select * from mytable;

SQL> insert into mytable values (2,'Tom Kyte','asktom.oracle.com');

1 row created.

SQL> commit;

Commit complete.
oracle@vm-ora2:~$ . oraenv
ORACLE_SID = 1 ? target
oracle@vm-ora2:~$ sqlplus -x demo/demo
SQL> select * from mytable;

        ID NAME                                               TEXT
---------- -------------------------------------------------- --------------------------------------------------------------------------------
         1 Istvan Stahl                                       istvanstahl.worldpress.com
         2 Tom Kyte                                           asktom.oracle.com

I hope you succeeded as well :) If not please leave a comment.

Additional Documentation:

Written by Istvan Stahl

2011/07/08 at 23:34

Posted in Golden Gate

Tagged with ,

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: