KADDACHI Hafedh Databases Blog

lundi 23 avril 2012

10g Physical Standby Data Guard configuration

I. Introduction

According to oracle note Oracle D ata Guard ensures high availability, data protection, and disaster recovery for enterprise data. Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.

A standby database can be either a physical standby database or a logical standby database:

Physical standby database
Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, though Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.
A physical standby database can be used for business purposes other than disaster recovery on a limited basis.
Logical standby database
Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database though SQL Apply, which transforms the data in the redo received from the primary database into SQL statements and then executing the SQL statements on the standby database.
A logical standby database can be used for other business purposes in addition to disaster recovery requirements. This allows users to access a logical standby database for queries and reporting purposes at any time. Also, using a logical standby database, you can upgrade Oracle Database software and patch sets with almost no downtime. Thus, a logical standby database can be used concurrently for data protection, reporting, and database upgrades.

Figure 1-1 Typical Data Guard Configuration

Complete article : (http://docs.oracle.com/cd/B19306_01/server.102/b14239/concepts.htm)

For my example, the primary database is ORALNDO (host=hots1), and the standby is NASHVILL (host=hots2).
The OS is linux x86_64 and the oracle version is 10.2.0.5.

II. Configuration

1. Ensure that ORLANDO is in archivelog mode, otherwise convert it.

remote_login_passwordfile =exclusive.
Enable the logging mode

SQL> ALTER DATABASE FORCE LOGGING;

2. Create Standby Redo Logs (optional)
on primary db ORALNDO :
SQL> select group#, thread#, bytes, members from v$log;
GROUP# THREAD# BYTES MEMBERS
---------- ---------- ---------- ----------
1 1 52428800 2
2 1 52428800 2
3 1 52428800 2

create standby redo log : (# of online redo log file groups on primary + 1) * maximum # of threads
for oralndo : threads = 1 because no RAC
The number of standby redo logs required for the physical standby database in this example is (3 + 1) * 1 = 4 at 50MB each.

From the primary database, connect as SYS and run the following to create four standby redo log file groups:
SQL> alter database add standby logfile thread 1 group 4 size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 5 size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 7 size 50m;
Database altered.
sql > select group#, type, member from v$logfile order by group#, member;

....

3. Init param for primary db : ORLANDO

##############################################################################

## Primary Role Parameters ##

DB_UNIQUE_NAME=ORLANDO

SERVICE_NAMES=ORLANDO

LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORLANDO,NASHVILL)'

compatible=10.2.0.5.0

control_files=("/u02/oracle/oradata/ORLANDO/controlfile/control01.ctl", "/u03/oracle/oradata/ORLANDO/controlfile/control02.ctl",

"/u04/oracle/oradata/ORLANDO/controlfile/control03.ctl")

LOG_ARCHIVE_DEST_1=

'LOCATION=/u05/oracle/oradata/ORLANDO/archive

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=ORLANDO'

LOG_ARCHIVE_DEST_2=

'SERVICE=NASHVILL

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=NASHVILL'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=DEFER

## Standby Role Parameters ##

DB_FILE_NAME_CONVERT= '/NASHVILL/', '/ORLANDO/'

LOG_FILE_NAME_CONVERT= '/NASHVILL/', '/ORLANDO/'

STANDBY_FILE_MANAGEMENT=AUTO

FAL_SERVER='ORLANDO','NASHVILL'

FAL_CLIENT='ORLANDO'

###########################################

# Sort, Hash Joins, Bitmap Indexes

###########################################

pga_aggregate_target=1247805440

###########################################

# Processes and Sessions

###########################################

processes=150

###########################################

# File Configuration

###########################################

db_recovery_file_dest=/u05/oracle/flash_recovery_area

db_recovery_file_dest_size=2147483648

###########################################

# SGA Memory

###########################################

sga_target=1610612736

###########################################

# Diagnostics and Statistics

###########################################

background_dump_dest=/u01/app/oracle/admin/ORLANDO/bdump

###########################################

# Job Queues

###########################################

job_queue_processes=10

###########################################

# Database Identification

###########################################

db_name=ORLANDO

###########################################

# Diagnostics and Statistics

###########################################

user_dump_dest=/u01/app/oracle/admin/ORLANDO/udump

###########################################

# Shared Server

###########################################

dispatchers="(PROTOCOL=TCP) (SERVICE=ORLANDOXDB)"

###########################################

# Security and Auditing

###########################################

audit_file_dest=/u01/app/oracle/admin/ORLANDO/adump

###########################################

# Database Identification

###########################################

db_domain=""

###########################################

# Cursors and Library Cache

###########################################

open_cursors=300

###########################################

# Cache and I/O

###########################################

db_block_size=8192

###########################################

# System Managed Undo and Rollback Segments

###########################################

undo_tablespace=UNDOTBS1

###########################################

# Diagnostics and Statistics

###########################################

core_dump_dest=/u01/app/oracle/admin/ORLANDO/cdump

###########################################

# Security and Auditing

###########################################

remote_login_passwordfile=EXCLUSIVE

###########################################

# System Managed Undo and Rollback Segments

###########################################

undo_management=AUTO

###########################################

# Cache and I/O

###########################################

db_file_multiblock_read_count=16

4. Rman backup of primary db: orlando
RMAN > backup database plus archivelog;

5. Create a Standby Controlfile
RMAN> backup device type disk format '/u02/oracle/oradata/ORLANDO/standby_controlfile/%U' current controlfile for standby;

6. create dirtectories on the standby server (host2)
mkdir -p /u01/app/oracle/admin/NASHVILL/adump
mkdir -p /u01/app/oracle/admin/NASHVILL/udump
mkdir -p /u01/app/oracle/admin/NASHVILL/dpdump
mkdir -p /u01/app/oracle/admin/NASHVILL/bdump
mkdir -p /u01/app/oracle/admin/NASHVILL/pfile
mkdir -p /u01/app/oracle/admin/NASHVILL/scripts
mkdir -p /u02/oracle/oradata/NASHVILL/controlfile/
mkdir -p /u03/oracle/oradata/NASHVILL/controlfile/
mkdir -p /u04/oracle/oradata/NASHVILL/controlfile/
mkdir -p /u02/oracle/oradata/NASHVILL/data_system
mkdir -p /u02/oracle/oradata/ORLANDO/data
mkdir -p /u02/oracle/oradata/NASHVILL/data
mkdir -p /u03/oracle/oradata/NASHVILL/data
mkdir -p /u04/oracle/oradata/NASHVILL/data
mkdir -p /u02/oracle/oradata/NASHVILL/redo
mkdir -p /u03/oracle/oradata/NASHVILL/redo
mkdir -p /u04/oracle/oradata/NASHVILL/redo
mkdir -p /u05/oracle/oradata/NASHVILL/archive
mkdir -p /u02/oracle/oradata/NASHVILL/standby_controlfile

mkdir -p /u02/oracle/oradata/ORLANDO/ ----> to copy standby_controlfile see 13.

7. copy orlando.pfile to standby

From primary host (host1) to stdb host (host2)
a. scp ORLANDO_init.ora oracle@host2:/u01/app/oracle/admin/NASHVILL/pfile
b. on the stdb host : mv ORLANDO_init.ora NASHVILL_init.ora

8.update NASHVILL_init.ora

##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
## Primary Role Parameters ##
DB_NAME=ORLANDO
INSTANCE_NAME=NASHVILL
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORLANDO,NASHVILL)'
compatible=10.2.0.5.0
control_files=("/u02/oracle/oradata/NASHVILL/controlfile/control01.ctl", "/u03/oracle/oradata/NASHVILL/controlfile/control02.ctl",
"/u04/oracle/oradata/NASHVILL/controlfile/control03.ctl")
DB_UNIQUE_NAME=NASHVILL
LOG_ARCHIVE_DEST_1=
'LOCATION=/u05/oracle/oradata/NASHVILL/archive
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=NASHVILL'
LOG_ARCHIVE_DEST_2=
'SERVICE=ORLANDO VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORLANDO'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
SERVICE_NAMES=NASHVILL

## Standby Role Parameters ##
DB_FILE_NAME_CONVERT= '/ORLANDO/', '/NASHVILL/'
LOG_FILE_NAME_CONVERT= '/ORLANDO/', '/NASHVILL/'
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER='ORLANDO','NASHVILL'
FAL_CLIENT='NASHVILL'
standby_file_management='auto'
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=1247805440
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# File Configuration
###########################################
db_recovery_file_dest=/u05/oracle/flash_recovery_area
db_recovery_file_dest_size=2147483648
###########################################
# SGA Memory
###########################################
sga_target=1610612736
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/u01/app/oracle/admin/NASHVILL/bdump
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Diagnostics and Statistics
###########################################
user_dump_dest=/u01/app/oracle/admin/NASHVILL/udump
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=NASHVILLXDB)"
###########################################
# Security and Auditing
###########################################
audit_file_dest=/u01/app/oracle/admin/NASHVILL/adump
###########################################
# Database Identification
###########################################
db_domain=""
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Cache and I/O
###########################################
db_block_size=8192
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
###########################################
# Diagnostics and Statistics
###########################################
core_dump_dest=/u01/app/oracle/admin/NASHVILL/cdump
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
###########################################
# Cache and I/O
###########################################
db_file_multiblock_read_count=16

9. tnsnames

host2 :
NASHVILL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOST2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NASHVILL)
)
)

hots1 :
NASHVILL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOST1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NASHVILL) (UR=A) -- This will allow the primary database to connect remotely to a database in nomount or mount mode. It’s a feature introduced in oracle 10g.
)
)

10. listener (host2)
(SID_DESC =
(GLOBAL_DBNAME = NASHVILL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = NASHVILL)

Check with the SQL*Net configuration using the following commands on the Primary AND Standby

from host1 server, check the communication beetween the 2 databases : tnsping NASHVILL

11. create pwdfile on host2
orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapwNASHVILL password=xxxx

12. startup stanby on nomount

export ORACLE_SID=NASHVILL
sqlplus / as sysdba
SQL> startup nomount pfile='/u01/app/oracle/admin/NASHVILL/pfile/NASHVILL_init.ora';

13.copy backups
Using an OS remote copy utility, transfer the backup of the primary database, standby controlfile, and standby initialization parameter file to the standby host :
scp -r /u05/oracle/flash_recovery_area/ORLANDO oracle@xxxx:/u05/oracle/flash_recovery_area/
scp -r /u02/oracle/oradata/ORLANDO/standby_controlfile oracle@xxxx:/u02/oracle/oradata/ORLANDO/

14.Create the Physical Standby Database

on stdby host (host2):

[oracle@host2 ~]$ export ORACLE_SID=NASHVILL

[oracle@host2 ~]$ rman target sys/xxxxl@ORLANDO auxiliary/

Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 19 17:23:49 2012

connected to target database: ORLANDO (DBID=4090161544)

connected to auxiliary database: ORLANDO (not mounted)

RMAN> duplicate target database for standby;

Starting Duplicate Db at 20-MAR-12

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:

{

restore clone standby controlfile;

sql clone 'alter database mount standby database';

}

executing Memory Script

Starting restore at 20-MAR-12

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u02/oracle/oradata/ORLANDO/standby_controlfile/12n697ah_1_1

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u02/oracle/oradata/ORLANDO/standby_controlfile/12n697ah_1_1 tag=TAG20120319T151009

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02

output filename=/u02/oracle/oradata/NASHVILL/controlfile/control01.ctl

output filename=/u03/oracle/oradata/NASHVILL/controlfile/control02.ctl

output filename=/u04/oracle/oradata/NASHVILL/controlfile/control03.ctl

Finished restore at 20-MAR-12

sql statement: alter database mount standby database

contents of Memory Script:

{

set newname for tempfile 1 to

"/u02/oracle/oradata/NASHVILL/data_system/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/u02/oracle/oradata/NASHVILL/data_system/system01.dbf";

set newname for datafile 2 to

"/u02/oracle/oradata/NASHVILL/data_system/undotbs01.dbf";

set newname for datafile 3 to

"/u02/oracle/oradata/NASHVILL/data_system/sysaux01.dbf";

set newname for datafile 4 to

"/u02/oracle/oradata/NASHVILL/data_system/users01.dbf";

restore

check readonly

clone database

;

}

executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /u02/oracle/oradata/NASHVILL/data_system/temp01.dbf in control file

executing command: SET NEWNAME

Starting restore at 20-MAR-12

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u02/oracle/oradata/NASHVILL/data_system/system01.dbf

restoring datafile 00002 to /u02/oracle/oradata/NASHVILL/data_system/undotbs01.dbf

restoring datafile 00003 to /u02/oracle/oradata/NASHVILL/data_system/sysaux01.dbf

restoring datafile 00004 to /u02/oracle/oradata/NASHVILL/data_system/users01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u05/oracle/flash_recovery_area/ORLANDO/backupset/2012_03_19/o1_mf_nnndf_TAG20120319T150920_7pgd00oh_.bkp

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u05/oracle/flash_recovery_area/ORLANDO/backupset/2012_03_19/o1_mf_nnndf_TAG20120319T150920_7pgd00oh_.bkp tag=TAG20120319T150920

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 20-MAR-12

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=6 stamp=778420113 filename=/u02/oracle/oradata/NASHVILL/data_system/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=7 stamp=778420113 filename=/u02/oracle/oradata/NASHVILL/data_system/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=8 stamp=778420113 filename=/u02/oracle/oradata/NASHVILL/data_system/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=9 stamp=778420113 filename=/u02/oracle/oradata/NASHVILL/data_system/users01.dbf

Finished Duplicate Db at 20-MAR-12

exit;

15. primary db
alter system set log_archive_dest_state_2=enable scope=both;

16. standby
To use Real Time Apply, run the following alternate version of the alter database command to place the standby database in managed recovery mode:

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

17.test
primary : alter system archive log current;


SQL> select status, error from v$archive_dest where dest_id = 2;

STATUS    ERROR
--------- -----------------------------------------------------------------
VALID

standby : select sequence#, first_time, next_time, archived, applied from v$archived_log order by sequence#;

SEQUENCE# FIRST_TIM NEXT_TIME ARC APP
---------- --------- --------- --- ---
131 11-APR-12 11-APR-12 YES YES
132 11-APR-12 12-APR-12 YES YES
133 12-APR-12 16-APR-12 YES YES

III. Role transition : Implement Role Switchover Operation

1. Initialisation
Primary and stantdby databases must support the role transition.

Primary :

LOG_ARCHIVE_DEST_2=

'SERVICE=NASHVILL VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=NASHVILL'

FAL_SERVER='ORLANDO','NASHVILL'

FAL_CLIENT='ORLANDO'

Standby:

LOG_ARCHIVE_DEST_2=

'SERVICE=ORLANDO VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORLANDO'
DB_UNIQUE_NAME=NASHVILL'

FAL_SERVER='ORLANDO','NASHVILL'

FAL_CLIENT='NASHVILL'

2.tnsnames

hots2 :
ORLANDO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOST1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORLANDO )
)
)

3. Redo current with primary DB : ORLANDO

SQL> alter system switch logfile;

System altered.


SQL> select status, error from v$archive_dest where dest_id = 2;

STATUS    ERROR
--------- ---------------------------------------------------------
VALID

4. Redo apply within standby DB : NASHVILL

SQL> select client_process, process, sequence#, status
2 from v$managed_standby;

CLIENT_P PROCESS SEQUENCE# STATUS
-------- --------- ---------- ------------
ARCH ARCH 150 CLOSING
ARCH ARCH 151 CLOSING
N/A MRP0 152 WAIT_FOR_LOG
UNKNOWN RFS 0 IDLE

5. Perform the Switcher Operation

Primary DB:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

Status "Session active" indicates to apply "WITH SESSION SHUTDOWN" clause :

SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.

6. Shtdown and restart the primary DB as a standby DB

SQL> shutdown immediate;
SQL> startup mount;

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY

7. standby DB NASHVILL becomes primary :

SQL> alter database commit to switchover to primary;

SQL> shutdown immediate;

SQL> startup ;

8.standby manages operations :

--START REDO APPLY
SQL> alter database recover managed standby database disconnect;

--START REDO APPLY USING REAL-TIME APPLY
SQL> alter database recover managed standby database using current logfile disconnect;

vendredi 23 mars 2012

Access to MySql from Oracle

A. Introduction
According to Oracle note, Oracle Database Gateway for ODBC is intended for low-end data integration solutions requiring the dynamic query capability to connect from an Oracle database to non-Oracle systems. Any data source compatible with the ODBC standards described in this chapter can be accessed using Oracle Database Gateway for ODBC.

Figure 1-1 shows an example of a configuration in which an Oracle and non-Oracle database are on separate machines, communicating through Oracle Database Gateway for ODBC. The client connects to the non-Oracle system through a network.

Figure 1-1 Oracle and Non-Oracle Systems on a Separate Machines

Description of "Figure 1-1 Oracle and Non-Oracle Systems on a Separate Machines"

complete article :
http://docs.oracle.com/cd/B28359_01/gateways.111/e10311/intro.htm#CEGCBAIE.

B. Installation
You need to install an ODBC driver to use Oracle Database Gateway for ODBC.
In my case, i have downloaded mysql-connector-odbc-5.1.10-1.rhel5.x86_64.rpm from //www.mysql.org
and installed it on my Oracle server (RedHat x86_64), /usr/lib64.

C. Environnement
Oracle host : Linux Redhat x86_64
Oracle version : 11.2.0.1

Mysql server : Linux Redhat x86_64
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.47 |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | redhat-linux-gnu |
+-------------------------+---------------------+

D. Set up

1. /etc/odbc.ini

Using the root user, i have to instantiate the odbc.ini

I'm be using the [mysql] DSN entry.

[mysql]
Driver   = /usr/lib64/libmyodbc5.so
UsageCount        = 1
DATABASE               = my_mysql_db
PORT                         = 3306
SERVER                    = @IP MySql Host
USER        = mysl_user
PASSWORD              = pwd_mysl_user
CHARSET          = utf8 ---------------> used while creating tables on my_mysl_db
TRACE         = OFF
TDS_Version         = 8

2. Test the MySql ODBC Driver
You can easily test the MySql driver using the unixODBC tool : isql
[oracle@xxxx etc]$ isql mysql -v

+---------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+---------------------------------------+

2. Configure dg4odbc process.

2.1 The listener needs a new entry :

SID_DESC =
(SID_NAME=dg4odbc)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=
/u01/app/oracle/product/11.2.0/dbhome_1/
:/usr/lib64/:/usr/lib/
:/u01/app/oracle/product/11.2.0/dbhome_1/lib)
)

[oracle@xxxx etc]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-MAR-2012 13:44:22
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521)))
The command completed successfully
[oracle@xxx etc]$

2.2 The tnsnames needs a new entry :

dg4odbc=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521))
(CONNECT_DATA=(SID=dg4odbc))
(HS=OK)
)

The (HS=OK) parameter must be outside the SID section and specifies that this connector

uses the Oracle Heterogeneous Service Option.

[oracle@SRVD-SSM etc]$ tnsping dg4odbc
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 23-MAR-2012 13:45:04
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521)) (CONNECT_DATA=(SID=dg4odbc)) (HS=OK))
OK (0 msec).

2.3 init.ora

The SID is also relevant for the init.ora file of the gateway. The name of the file is init.ora.  In this example it is called  initdg4odbc.ora.

The file is located at $ORACLE_HOME/hs/admin.

/u01/app/oracle/product/11.2.0/dbhome_1/hs/admin/initdg4odbc.ora :
HS_FDS_CONNECT_INFO=mysql ----------> odbc.ini    [mysql]
 HS_FDS_TRACE_LEVEL=0
HS_FDS_SHAREABLE_NAME=/usr/lib64/libmyodbc5.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
HS_FDS_SQLLEN_INTERPRETATION=64
set ODBCINI=/etc/odbc.ini
set ODBCSYSINI=/etc

2.4 Configuring the environment:
I create a database link from my oracle user to the mysql user :

create database link linkmysql
connect to "mysl_user" -------------------> odbc.ini USER = mysl_user
identified by "pwd_mysl_user" -------------------> odbc.ini PASSWORD = pwd_mysl_user
using 'dg4odbc';

Username and password must be in double quotes !!

2.5 Test
in the mysql database, i create the table my_sql_table (id number, dest char(5)), and i insert the following data :
id dest
---- ----
1 dest1
2 dest2

from my oracle user, i execute : select * from " my_sql_table"@ linkmysql;

SQL> select * from " my_sql_table"@linkmysql;
ERROR:
ORA-28528: Heterogeneous Services datatype conversion error
ORA-02063: preceding line from LINKMYSQL
no rows selected
SQL>

To avoid the ORA-28528 error add to the initdg4odbc.ora : HS_FDS_FETCh_ROWS=1
Metalink note [ID 1080048.1].

connect with a new oracle session :

SQL> select * from " my_sql_table"@linkmysql;

id dest
---- ----
1 dest1
2 dest2

SQL>

jeudi 15 mars 2012

Oracle active Data Guard for standby database : RMAN

You can use Oracle active Data Guard to create a physical standby database by using RMAN backups.
For my example, the primary database is STATTAR (host=hots1), and the standby is STATSTDB (host=hots2).
The OS is linux x86_64 and the oracle version is 11.2.0.1.

I. Set up Standby database

1. Ensure that STATTAR is in archivelog mode, otherwise convert it.

2. Enable the logging mode

SQL> ALTER DATABASE FORCE LOGGING;

3. do a complete backup of STATTAR
export ORACLE_SID=STATTAR
rman target /
RMAN> backup database plus archivelog;

4. tnsnames
STATSTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STATSTDB)
)
)

5. listner (host2)
(SID_DESC =
(GLOBAL_DBNAME = STATSTDB )
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = STATSTDB )
)

Check with the SQL*Net configuration using the following commands on the Primary AND Standby

from host1 server, check the communication beetween the 2 databases :

tnsping STATSTDB tnsping STATTAR

6. on host2, create the directories for data, controlfile and redo log files
mkdir -p /u01/app/oracle/admin/ STATSTDB /adump
mkdir -p /u01/app/oracle/admin/ STATSTDB /dpdump
mkdir -p /u01/app/oracle/admin/ STATSTDB /pfile
mkdir -p /u01/app/oracle/admin/ STATSTDB /scripts
mkdir -p /u02/oracle/oradata/ STATSTDB /controlfile/
mkdir -p /u03/oracle/oradata/ STATSTDB /controlfile/
mkdir -p /u04/oracle/oradata/ STATSTDB /controlfile/
mkdir -p /u02/oracle/oradata/ STATSTDB /data_system
mkdir -p /u02/oracle/oradata/ STATSTDB /data
mkdir -p /u02/oracle/oradata/ STATSTDB /data
mkdir -p /u03/oracle/oradata/ STATSTDB /data
mkdir -p /u04/oracle/oradata/ STATSTDB /data
mkdir -p /u02/oracle/oradata/ STATSTDB /redo
mkdir -p /u03/oracle/oradata/ STATSTDB /redo
mkdir -p /u04/oracle/oradata/ STATSTDB /redo

7. create pfile for standby (host2)
db_name=STATTAR
db_unique_name=STATSTDB
REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
db_block_size=8192
memory_max_target=1500M
memory_target=1500M

8. create pwdfile : the same as the target DB (host2)
orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapw STATSTDB password=<syspwd>

9. startup STATSTDB in nomount mode
export ORACLE_SID=STATSTDB
sqlplus / as sysdba
SQL> startup nomount pfile='/u01/app/oracle/admin/STATSTDB/pfile/STATSTDB_init.ora';

10. Create stanby redo log on the on primary db STATTAR

SQL> alter database add standby logfile group 4 ('/u02/oracle/oradata/ STATTAR /redostdb.ora') size 50M;

11. Modify the primary initialization parameter for dataguard on primary db STATTAR
-- primary DB

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(STATTAR,STATSTDB)';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u06/oracle/oradata/STATTAR/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STATTAR';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=STATSTDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STATSTDB';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.

SQL> alter system set FAL_SERVER=STATSTDB;
System altered.

SQL> alter system set FAL_CLIENT=STATTAR;
System altered.

SQL> alter system set DB_FILE_NAME_CONVERT= '/u02/oracle/oradata/STATSTDB/redo',
'/u02/oracle/oradata/STATTAR/redo',
'/u03/oracle/oradata/STATSTDB/redo',
'/u03/oracle/oradata/STATTAR/redo',
'/u04/oracle/oradata/STATSTDB/redo',
'/u04/oracle/oradata/STATTAR/redo' scope=spfile;
System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT= '/u02/oracle/oradata/STATSTDB/data_system',
'/u02/oracle/oradata/STATTAR/data_system',
'/u02/oracle/oradata/STATSTDB/data',
'/u02/oracle/oradata/STATTAR/data',
'/u03/oracle/oradata/STATSTDB/data',
'/u03/oracle/oradata/STATTAR/data',
'/u04/oracle/oradata/STATSTDB/data',
'/u04/oracle/oradata/STATTAR/data' scope=spfile;
System altered.

12. on primary host (host1) invoke RMAN :
rman target sys/<password>@STATTAR

RMAN>
connect auxiliary sys/<password>@STATSTDB
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;

duplicate target database for standby from active database
spfile
parameter_value_convert 'STATTAR','STATSTDB'
set db_unique_name='STATSTDB'
set db_file_name_convert='/STATTAR/','/STATSTDB/'
set log_file_name_convert='/STATTAR/','/STATSTDB/'
set control_files='/u02/oracle/oradata/STATSTDB/controlfile/control01.ctl','/u03/oracle/oradata/STATSTDB/controlfile/control02.ctl','/u04/oracle/oradata/STATSTDB/controlfile/control03.ctl'
set log_archive_max_processes='5'
set fal_client='STATSTDB'
set fal_server='STATTAR'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(STATTAR,STATSTDB)'
set log_archive_dest_1='service=STATTAR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=STATTAR'
;
}

...

''STATMAC'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config =
''dg_config=(STATMAC,STATSTDB)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''service=STATMAC ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name= STATMAC'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/STAT STDB/adump'' comment= '''' scope=spfile

sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=STATST DBXDB)'' comment= '''' scope=spfile

sql statement: alter system set db_unique_name = ''STATSTDB'' comment= '''' sc ope=spfile

sql statement: alter system set db_file_name_convert = ''/STATMAC/'', ''/STATS TDB/'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/STATMAC/'', ''/STAT STDB/'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''/u02/oracle/oradata/STATSTDB /controlfile/control01.ctl'', ''/u03/oracle/oradata/STATSTDB/controlfile/control 02.ctl'', ''/u04/oracle/oradata/STATSTDB/controlfile/control03.ctl'' comment= '' '' scope=spfile

sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile

sql statement: alter system set fal_client = ''STATSTDB'' comment= '''' scope=spfile

sql statement: alter system set fal_server = ''STATMAC'' comment= '''' scope=spfile

sql statement: alter system set standby_file_management = ''AUTO'' comment= '' '' scope=spfile

sql statement: alter system set log_archive_config = ''dg_config=(STATMAC,STAT STDB)'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''service=STATMAC ASYNC v alid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=STATMAC'' comment= '''' sc ope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1570009088 bytes

Fixed Size 2213696 bytes
Variable Size 905971904 bytes
Database Buffers 637534208 bytes
Redo Buffers 24289280 bytes
allocated channel: stby
channel stby: SID=63 device type=DISK

....

input datafile copy RECID=156 STAMP=778006506 file name=/u03/oracle/oradata/STATSTDB/data/TBS_FMA_DATA_01.ora

datafile 49 switched to datafile copy

input datafile copy RECID=157 STAMP=778006506 file name=/u03/oracle/oradata/STATSTDB/data/TBS_FMA_DATA_02.ora

Finished Duplicate Db at 15-MAR-12

released channel: prmy1

released channel: prmy2

released channel: prmy3

released channel: prmy4

released channel: stby

exit;

II. Manage Standby database

1. Stop the managed recovery process and open STATSTDB:
export ORACLE_SID=STATSTDB
sqlplus / as sysdba
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 STATSTDB
pprod_db.local
11.2.0.1.0 15-MAR-12 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> SQL> alter database open;
Database altered.
SQL>

2. Restart the managed recovery process

SQL> alter database recover managed standby database 
             using current logfile disconnect;

3. Test the active data guard:
on the primary database for user SCOTT:

create table my_test (id number);
insert into my_test(id) vlaues (1);
commit;
witth sys user : alter system switch logfile;

on the auxiliary database :
the table my_test is created with one record.

mardi 6 mars 2012

Duplicate Database on the Same Host Using RMAN

You can use RMAN to create a duplicate database on the same server by using RMAN backups.
For my example, the target database is STATTAR, and the duplicate is STATDUP.
The OS is linux x86_64 and the oracle version is 11.2.0.1.

1. Ensure that STATTAR is in archivelog mode, otherwise convert it.

2. Enable the logging mode

SQL> ALTER DATABASE FORCE LOGGING;

3. do a complete backup of STATTAR
export ORACLE_SID=STATTAR
rman target /
RMAN> backup database plus archivelog;

4. tnsnames
STATDUP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STATDUP)
)
)

5. listner
(SID_DESC =
(GLOBAL_DBNAME = STATDUP)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = STATDUP)
)

6. create the directories for data, controlfile and redo log files
mkdir -p /u01/app/oracle/admin/STATDUP/adump
mkdir -p /u01/app/oracle/admin/STATDUP/dpdump
mkdir -p /u01/app/oracle/admin/STATDUP/pfile
mkdir -p /u01/app/oracle/admin/STATDUP/scripts
mkdir -p /u02/oracle/oradata/STATDUP/controlfile/
mkdir -p /u03/oracle/oradata/STATDUP/controlfile/
mkdir -p /u04/oracle/oradata/STATDUP/controlfile/
mkdir -p /u02/oracle/oradata/STATDUP/data_system
mkdir -p /u02/oracle/oradata/STATTAR/data
mkdir -p /u02/oracle/oradata/STATDUP/data
mkdir -p /u03/oracle/oradata/STATDUP/data
mkdir -p /u04/oracle/oradata/STATDUP/data
mkdir -p /u02/oracle/oradata/STATDUP/redo
mkdir -p /u03/oracle/oradata/STATDUP/redo
mkdir -p /u04/oracle/oradata/STATDUP/redo

7. create pfile
db_name=STATDUP
db_block_size=8192
compatible='11.2.0.0.0'
remote_login_passwordfile=exclusive
control_files = ('/u02/oracle/oradata/STATDUP/controlfile/control01.ctl',
'/u03/oracle/oradata/STATDUP/controlfile/control01.ctl',
'/u04/oracle/oradata/STATDUP/controlfile/control01.ctl')
db_file_name_convert = ('/u02/oracle/oradata/STATTAR/data_system',
'/u02/oracle/oradata/STATDUP/data_system',
'/u02/oracle/oradata/STATTAR/data',
'/u02/oracle/oradata/STATDUP/data',
'/u03/oracle/oradata/STATTAR/data',
'/u03/oracle/oradata/STATDUP/data',
'/u04/oracle/oradata/STATTAR/data',
'/u04/oracle/oradata/STATDUP/data'
)
log_file_name_convert = ('/u02/oracle/oradata/STATTAR/redo',
'/u02/oracle/oradata/STATDUP/redo',
'/u03/oracle/oradata/STATTAR/redo',
'/u03/oracle/oradata/STATDUP/redo',
'/u04/oracle/oradata/STATTAR/redo',
'/u04/oracle/oradata/STATDUP/redo'
)

8. create pwdfile : the same as the target DB
orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSTATDUP password=<syspwd>

9. startup STATDUP in nomount mode
export ORACLE_SID=STATDUP
sqlplus / as sysdba
SQL> startup nomount pfile='/u01/app/oracle/admin/STATDUP/pfile/STATDUP_init.ora';

10. duplicate the target DB
export ORACLE_SID=STATDUP
rman target sys/<syspwd>@STATTAR auxiliary/

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 6 10:46:38 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: STATTAR (DBID=2026*****)
connected to auxiliary database: STATDUP (not mounted)

RMAN > duplicate target database to STATDUP;

Starting Duplicate Db at 06-MAR-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=57 device type=DISK

contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 233861120 bytes

Fixed Size 2212088 bytes
Variable Size 159387400 bytes
Database Buffers 67108864 bytes
Redo Buffers 5152768 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''STATTAR'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''STATDUP'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ''STATTAR'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''STATDUP'' comment= ''Modified by RMAN duplicate'' scope=spfile

.....

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 06-MAR-12

exit;

mardi 14 février 2012

Script for Pga memory for each process

Hi,
you can find here a script that displays the pga memory for each process, and specifies the apply name, capture name and propogation name, in the case you use Oracle Streams.

SELECT se.sid,n.name , SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, MAX(se.value) pga_memory,
DECODE( r.APPLY_NAME, NULL, DECODE(coor.APPLY_NAME,NULL, ser.APPLY_NAME, coor.APPLY_NAME), r.APPLY_NAME ) APPLY_NAM,
cap.CAPTURE_NAME, q.QNAME, s.USERNAME
FROM v$sesstat se, v$statname n, V$SESSION s,
V$STREAMS_APPLY_READER r, V$STREAMS_APPLY_coordinator coor, V$STREAMS_APPLY_SERVER ser,
GV$STREAMS_CAPTURE c, dba_capture cap ,
dba_queue_schedules q
WHERE n.statistic# = se.statistic#
AND s.sid= se.sid
AND n.name IN ('session pga memory')
AND s.SID = r.SID(+)
AND s.SERIAL# = r.SERIAL#(+)
AND s.SID = coor.SID(+)
AND s.SERIAL# = coor.SERIAL#(+)
AND s.SID = ser.SID(+)
AND s.SERIAL# = ser.SERIAL#(+)
AND c.CAPTURE_NAME = cap.CAPTURE_NAME(+)
AND s.SID = c.SID(+)
AND s.SERIAL# = c.SERIAL# (+)
AND s.SID = TO_NUMBER(SUBSTR (q.session_id(+), 1, INSTR(q.session_id(+), ',')- 1 ))
AND s.SERIAL# = TO_NUMBER(SUBSTR (q.session_id(+), INSTR(q.session_id(+), ',') +1 , LENGTH(q.session_id(+)) - INSTR(q.session_id(+), ',')))
GROUP BY n.name,se.sid, SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4),DECODE( r.APPLY_NAME, NULL, DECODE(coor.APPLY_NAME,NULL, ser.APPLY_NAME, coor.APPLY_NAME), r.APPLY_NAME ), cap.CAPTURE_NAME, q.qname, s.USERNAME
ORDER BY 4 DESC ;