Thinus Meyer's DBA adventures

Resolving Gaps in Data Guard Apply Using Incremental RMAN Backup

2012-01-26T14:58:00.003+02:00

I found quite a few blog entries on the web and off course the Oracle Documentation also helped. The reason I want to create a post is that I encountered 2 problems that I first had to overcome before the Physical Standby was in sync again. A sort overview of the problem and the steps to overcome them are:

On the standby get the current scn which will be used in your RMAN backup script as a starting point.

SQL>select current_scn FROM V$DATABASE;

CURRENT_SCN

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

11133157117269

On the primary run the RMAN script with the supplied current_scn number from the standby

run {

allocate channel c1 type disk format '/dump/abcprd/%U.rmb';

backup as compressed backupset skip readonlyincremental from scn 11133157117269 database;

And this is were I got my first unexpected problem. The RMAN backup gave the following warning message:

RMAN-06755: WARNING: datafile 408: incremental-start SCN is too recent;using checkpoint SCN 9733080640801 instead

File 408 is a Read Only Tablespace with a much older scn number. V$SESSION_LONGOPS showed that the backup will take longer than 24 hours to complete, so I immediately stopped it and reset the Read Only Tablespace's scn number with the aim that it will shorten the duration of the RMAN backup.

SQL> alter tablespace SA_ORD_RO read write ;
SQL> alter tablespace SA_ORD_RO read only ;

I restarted the RMAN backup, it did not give the warning again, but still took 16 hours to complete. All files created by the backup were copied to the standby server. I also created a new standby controlfile:

RMAN> backup current controlfile for standby format'/usr/users/oracle/ForStandbyCTRL.bck';

On the standby server I cataloged and then restored the controlfile

RMAN> catalog start with '/dump/backup/';

RMAN> restorestandby controlfile from '/dump/backup/ForStandbyCTRL.bck';

And the recover script:

RMAN> catalog start with ' /dump/abcprd/';

RMAN> recover database noredo;

But the Incremental backup failed almost immediately with the error

RMAN-06094: datafile 569 must be restored

It turns out that datafile 569 was created on the primary after the gap occured, but before the rman incremental was run. So the controlfile was aware of the datafile, but the file was not on the standby server. I also need to do a datafile backup for the newly created datafile. So back to Primary:

RMAN> backup datafile 569;
scp the file to the standby server and restore it on the standby server:
RMAN> restore datafile 569;

This time I could start the RMAN> recover database noredo; again and it completed successfully.

DB link from Oracle to SQL Server

2012-01-26T14:18:00.003+02:00

Wehad a need for a DB link from an Oracle database to a SQL Server database. I initially feared that this might be a huge task, but it turned out to be quite a easy setup. Thanks Oracle for making it almost seamless!

There are basically 4 steps involved

1.) Install & configure the Gateway

The Oracle Gateway can be found in theOracle Database 11g Release 2 Enterprise Edition software. Installation isstraight forward. You basically have to make 2 choices during installation. What Gateway you want to install(SQL Server in our case) and which ORACLE_HOME you want to use. You can createa separate Gateway home or just use the current ORACLE_HOME, which is what we did.

After installation you will find a newdirectory in ORACLE_HOME called dg4msql. Configure the Gateway by creating a agentinit.ora file in $ORACLE_HOME/dg4msql/admin. The name of the file is important, as the name you use will be used in your listener as well.We kept it to the default initdg4msql.ora. The information you need to supplyin the agent init.ora file are SQL Server Database Server Host Name, SQL ServerDatabase Server Port number and SQL Server Database. The information all goes into one parameter called HS_FDS_CONNECT_INFO.

Ex:

# This is a customized agent init filethat contains the HS parameters

# that are needed for the DatabaseGateway for Microsoft SQL Server

# HS init parameters

# HS_FDS_CONNECT_INFO=[server_name]:port//mssql_db

HS_FDS_CONNECT_INFO=[ABC02.company.co.za]:1433//MSCIM

# alternate connect format ishostname/serverinstance/databasename

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

2.) Listener & names lookup

We usedport 1524 to setup the gateway

LISTENER =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = tcp)(HOST = abc01.company.co.za)(PORT = 1521))

(ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1524))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = dg4msql)

(ORACLE_HOME = /opt/apps/oracle/product/11.2.0/dbhome_1)

(PROGRAM = dg4msql)

)

And Local Naming

MSCIM =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1524))

(CONNECT_DATA =

(SID = dg4msql)

)

(HS = OK)

)

HS=OK - indicates this connectdescriptor is using heterogeneous Service.

3.) DatabaseLink

Once the SQL Server team gave us ausername and password we could just create a DB link

CREATE PUBLIC DATABASE LINK MSCIM_DBLINK CONNECT TO 'mssql_username' IDENTIFIED BY 'mssql_password' USING 'MSCIM'

4.) Datadictionary translation support

Oracle has also supplied a script in$ORACLE_HOME/ dg4msql/admin/ called dg4msql_cvw.sql that must be run on the SQLServer side. The script creates views on SQL Server for usage by the Oracle DataDictionary. 4 viewscalled ALL_IND_COLUMNS, USER_IND_COLUMNS, ALL_CONS_COLUMNS, and USER_CONS_COLUMNSwill be created on the SQL Server db making it easier for someone familiar withOracle Data Dictionary views to query the SQL Server database.

Background Managed Standby Recovery process not detected

2011-09-16T14:15:00.000+02:00

We have had 2 instances where the dg broker did not start MAnaged Standby Recovery of the physical standby database. The first time was after aswitchover and the next time was after hardware maintenance. The physical standby came up successfullyand the dg broker started up, but Managed Standby Recovery never started. On both occasions Inoticed these entries in the alert.log file:

...

Mon Sep 1214:03:32 2011

Starting DataGuard Broker (DMON)

OCISessionBeginwith PasswordVerifier succeeded

ALTER SYSTEMSET ..

ALTER DATABASERECOVER MANAGED STANDBY DATABASE THROUGHALL SWITCHOVER DISCONNECT USING CURRENTLOGFILE

...

Mon Sep 1214:08:06 2011

Background MRP initialization phase wait timeout **

Background Managed Standby Recovery process notdetected **

Completed:ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE

I bounced the database a few times but the same errorpersisted. The only way I could solve this was to “kick start” the recovery by manuallyrecovering a few archivelogs:

SQL>ALTERDATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROMSESSION;

ALTER DATABASERECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

ERROR at line1:

ORA-01153: anincompatible media recovery is active

But

SQL>ALTERDATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

onlyhangs

shutdownimmediate also only hangs

shutdownabort did it.

SQL> startupmount

Start manual recovery before the DG Broker starts Managed Standby Recovery

SQL> RECOVERstandby DATABASE UNTIL CANCEL;

ORA-00279:change 10662691458828 generated at 09/13/2011 01:18:04 needed for thread 1

ORA-00289:suggestion : +PCASDGF/abcprd/archivelog/2011_09_13/thread_1_seq_82723.528.742958295

ORA-00280:change 10662691458828 for thread 1 is in sequence #82723

Specify log:{=suggested | filename | AUTO | CANCEL}

ORA-00279:change 10662691458829 generated at 09/13/2011 01:18:13 needed for thread 1

ORA-00289:suggestion : +PCASDGF/abcprd/archivelog/2011_09_13/thread_1_seq_82724.2112.742961677

ORA-00280:change 10662691458829 for thread 1 is in sequence #82724

ORA-00278: logfile '+PCASDGF/abcprd/archivelog/2011_09_13/thread_1_seq_82723.528.742958295'no longer needed for this recovery

Specify log:{=suggested | filename | AUTO | CANCEL}

Hitenter, to manually recover a few logs and then

ORA-00279:change 10663204118583 generated at 09/13/2011 07:00:27 needed for thread 1

ORA-00289:suggestion : +PCASDGF/abcprd/archivelog/2011_09_13/thread_1_seq_82745.2365.742980297

ORA-00280:change 10663204118583 for thread 1 is in sequence #82745

ORA-00278: logfile '+PCASDGF/abcprd/archivelog/2011_09_13/thread_1_seq_82744.2938.742978831'no longer needed for this recovery

Specify log:{=suggested | filename | AUTO | CANCEL}

CANCEL

Media recoverycancelled.

SQL>ALTERDATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVERDISCONNECT USING CURRENT LOGFILE

Databasealtered.

Justto be sure I did another bounce a few minutes later and this time the DG Brokerdid everything it was supposed to and Managed Standby Recovery was started automatically.

Logon to Active Data Guard fails with ORA-16000

2011-09-16T11:16:00.003+02:00

We have a reporting user used by executives for real timereporting on one of our OLTP databases. Some of the queries were gettingresource intensive so we decided to move those queries to our Active Data Guardinstance.

But then the reports started failing with

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database open for read-only access

on database logon.

I found an old bug in MOS that pointed me in the rightdirection. After a failed login on Production, the same userid will get thiserror on the Active Data Guard instance, until the “logon counter” gets resetby a successful logon on production.

On Active Data Guard:

SQL >select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bitProduction

...

SQL> select OPEN_MODE, DATABASE_ROLE from v$database;

OPEN_MODE DATABASE_ROLE

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

READ ONLY PHYSICAL STANDBY

SQL> conn infoweb/correctpwd

Connected.

On Production:

SQL >select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bitProduction

...

SYS@casprd>select OPEN_MODE, DATABASE_ROLE from v$database;

OPEN_MODE DATABASE_ROLE

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

READ WRITE PRIMARY

now login as infoweb with a wrong password on Production:

SQL>connect infoweb/wrongpwd

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Back to Active Data Guard, logon as infoweb with the correctpassword:

SQL> conn infoweb/correctpwd

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database open for read-only access

Warning: You are no longer connected to ORACLE.

Back to Production, logon as infoweb with the correct password to reset the logon counter:

SQL> conn infoweb/correctpwd

Connected.

Back to Active Data Guard, logon as infoweb with the correctpassword:

SQL> conn infoweb/correctpwd

Connected.

This is similar to a 10.2.0.4 bug, Bug 5847453 Failed logon counter with read only DB throwsORA-16000 on failed logon [ID 5847453.8]. The note states that the issue wasfixed in 10.2.0.5 and 11.1.0.6.

ACCOUNT_STATUS = EXPIRED(GRACE)

2011-03-08T15:48:00.003+02:00

Users are identified within the app, so all DB connections are done through 1 schema. We created a profile for this schema where the password will never expire. App support has a manual process to change the password every 60 days, but Oracle must never be allowed to expire the password automatically.

The problem is that I found the ACCOUNT_STATUS for the main schema to be EXPIRED(GRACE) and that the EXPIRY_DATE is 3 days away.

This schemas profile was changed by accident, and rectified within a few hours. But the damage was done the ACCOUNT_STATUS still showed EXPIRED(GRACE).

I found an excellent blog post by Robert Geier (http://blog.contractoracle.com/2009/11/analysis-of-oracle-password-expiry.html) to explain the situation. Here’s a abbreviation of his explanation: password expiry is controlled by the PASSWORD_LIFE_TIME profile limit, is activated at login, and is reset by a password change. Changing the profile will not change aud$.ASTATUS.

SQL> select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users where username = ‘QWERTY’;

USERNAME PROFILE ACCOUNT_STATUS

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

QWERTY QWERTY_NONEXPIRY EXPIRED(GRACE)

So even though the profile was changed back to QWERTY_NONEXPIRY the ACCOUNT_STATUS was still EXPIRED(GRACE). The only way to reset the ACCOUNT_STATUS back to OPEN is to change the password, because only a password change will reset aud$.ASTATUS. But a password change will give a ORA-28007 because of the current profile limits for PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME gets violated.

SQL> select password from sys.user$ where name='QWERTY';

PASSWORD

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

87FE2DD49B2882B2

SQL> alter user QWERTY identified by values '87FE2DD49B2882B2';

alter user QWERTY identified by values '87FE2DD49B2882B2'

ERROR at line 1:

ORA-28007: the password cannot be reused

So I had to create a new temporary profile with the required settings to allow me to change the password to be the same as the current password:

SQL> create profile tmtmp limit PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME UNLIMITED;

SQL> alter user QWERTY profile tmtmp;

SQL> alter user QWERTY identified by values '87FE2DD49B2882B2';

User altered.

SQL> alter user QWERTY profile QWERTY_NONEXPIRY;

SQL> select USERNAME, PROFILE, ACCOUNT_STATUS from dba_users where username = 'QWERTY';

USERNAME PROFILE ACCOUNT_STATUS

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

QWERTY QWERTY_NONEXPIRY OPEN

Manually Add Datafile

2010-06-22T15:23:00.004+02:00

I have a database that runs on RAW devices. Whenever I add a datafile to a tablespace, out UNIX Sys Admins must first create the RAW device on the Primary & Standby Server. The other day they created the RAW device on the standby server but forgot to change the ownership of the RAW device to oracle:dba.

The datafile was successfully added to the primary database, but I found the following error in the alert.log of the standby:

File #323 added to control file as 'UNNAMED00323'.

Originally created as:

'/dev/vx/rdsk/oradg1/casprd_SA_I_CMD10'

Recovery was unable to create the file as:

'/dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10'

MRP0: Background Media Recovery terminated with error 1119

Mon Mar 9 23:41:44 2010

Errors in file /opt/apps/oracle/admin/caspp/bdump/caspp_mrp0_15261.trc:

ORA-01119: error in creating database file '/dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10'

ORA-27040: file create error, unable to create file

HPUX-ia64 Error: 13: Permission denied

Managed Standby Recovery not using Real Time Apply

Some recovered datafiles maybe left media fuzzy

Media recovery may continue but open resetlogs may fail

Mon Mar 9 23:41:45 2010

Errors in file /opt/apps/oracle/admin/caspp/bdump/caspp_mrp0_15261.trc:

ORA-01119: error in creating database file '/dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10'

ORA-27040: file create error, unable to create file

HPUX-ia64 Error: 13: Permission denied

I can only qquery some v$ views when a standby database is mounted. So to fix the problem I did the following:

On production get the relevant details

SQL>select ts# from v$tablespace where name = 'SA_I_CMD';

TS#

----------

SQL> col name for a80;

SQL> select FILE#, NAME, bytes/1024/1024 MB from v$datafile where ts#=17;

FILE# NAME MB

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

34 /dev/vx/rdsk/oradg/casprd_SA_I_CMD03 32500

35 /dev/vx/rdsk/oradg/casprd_SA_I_CMD 31144

36 /dev/vx/rdsk/oradg/casprd_SA_I_CMD02 30712

37 /dev/vx/rdsk/oradg/casprd_SA_I_CMD01 32360

38 /dev/vx/rdsk/oradg/casprd_SA_I_CMD04 32500

39 /dev/vx/rdsk/oradg/casprd_SA_I_CMD05 30000

201 /dev/vx/rdsk/oradg/casprd_SA_I_CMD06 20000

266 /dev/vx/rdsk/oradg1/casprd_SA_I_CMD07 32500

307 /dev/vx/rdsk/oradg1/casprd_SA_I_CMD08 32500

311 /dev/vx/rdsk/oradg1/casprd_SA_I_CMD09 32500

323 /dev/vx/rdsk/oradg1/casprd_SA_I_CMD10 32500

Lets look at the same query on the standby side

SQL> col name for a80

SQL> select FILE#, NAME, bytes/1024/1024 MB from v$datafile where ts#=17;

FILE# NAME MB

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

34 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD03 32500

35 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD 31144

36 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD02 30712

37 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD01 32360

38 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD04 32500

39 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD05 30000

201 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD06 20000

266 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD07 32500

307 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD08 32500

311 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD09 32500

323 /opt/apps/oracle/product/10.2.0.4.0/dbs/UNNAMED00323 0

After the UNIX admin corrected the file permissions of the RAW device I had to manually add it as a datafile to the standby database.

SQL>alter system set STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL>ALTER DATABASE CREATE DATAFILE ‘/opt/apps/oracle/product/10.2.0.4.0/dbs/UNNAMED00323' as '/dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10';

Database altered.

SQL>select FILE#, NAME, bytes/1024/1024 MB from v$datafile where ts#=17;

FILE# NAME MB

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

34 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD03 32500

35 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD 31144

36 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD02 30712

37 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD01 32360

38 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD04 32500

39 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD05 30000

201 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD06 20000

266 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD07 32500

307 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD08 32500

311 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD09 32500

323 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10 32500

11 rows selected.

SQL>alter system set STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;

Database altered.

Bug when creating a physical standby database in 11G

2010-05-07T14:00:00.004+02:00

Creating a physical standby database in 11G became a whole lot easier with just one RMAN command – unless you run into a bug :-)

My primary DB runs on Oracle Managed Files on ASM. My physical standby will also run on Oracle Managed Files on ASM on another server, but the ASM diskgroups are not the same.

I used Arub Nanda’s great article Oracle Database 11g: The Top New Features for DBAs and Developers as a guide. The Data Guard article is number 15 in the series and well worth a read if you want to get up and running quickly.
The RMAN script I used:

run {
allocate channel c1 type disk;
allocate auxiliary channel s1 type disk;

SET NEWNAME FOR DATAFILE 1 TO NEW;
SET NEWNAME FOR DATAFILE 2 TO NEW;
SET NEWNAME FOR DATAFILE 3 TO NEW;
SET NEWNAME FOR DATAFILE 4 TO NEW;
SET NEWNAME FOR DATAFILE 5 TO NEW;
SET NEWNAME FOR DATAFILE 6 TO NEW;
SET NEWNAME FOR DATAFILE 7 TO NEW;
SET NEWNAME FOR DATAFILE 8 TO NEW;
SET NEWNAME FOR DATAFILE 9 TO NEW;
SET NEWNAME FOR DATAFILE 10 TO NEW;
SET NEWNAME FOR DATAFILE 11 TO NEW;
SET NEWNAME FOR DATAFILE 12 TO NEW;
SET NEWNAME FOR TEMPFILE 1 TO NEW;

duplicate target database
for standby
from active database
dorecover
spfile
parameter_value_convert 'adevc','adevt'
set db_unique_name='adevt'
set control_files='/opt/apps/oracle/product/11.1.0.7.0/dbs/control01.ctl'
set fal_client='adevt'
set fal_server='adevc'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(adevc,adevt)'
set log_archive_dest_1='location="/dump/oracle/adevt_archivelogs", valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
set log_archive_dest_2='service=adevc LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=casdev'
set log_archive_dest_state_2='enable'
set db_create_file_dest='+TADG1'
set db_create_online_log_dest_1='+TADG1'
;

sql channel c1 'alter system archive log current';
sql channel s1 'alter database recover managed standby database using current logfile disconnect';
}
The error found in the RMAN logfile:
channel c1: starting datafile copy
input datafile file number=00023 name=+CADG1/adevc/datafile/all_ts.408.688130429
RMAN-03009: failure of backup command on c1 channel at 04/30/2010 14:54:55
ORA-19504: failed to create file "+TADG1"
channel c1 disabled, job failed on it will be run on another channel
released channel: c1
released channel: s1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/30/2010 14:54:55
RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on c1 channel at 04/30/2010 14:54:55
ORA-19504: failed to create file "+TADG1"

I found the root cause of the problem on metalink - Standby DATAFILE Creation Failing with RAC / ASM / OMF [ID 787753.1] It turns out to be Bug:7462589. The workaround for the Bug is that the ASM diskgroups on the standby must also be on the primary. The note states that this will be fixed in 11GR2. So my workaround was to create a temporary ASM diskgroup called TADG1 (with only 1 disk) on the primary database server. After that the restore completed successfully.

Long wait times for cursor: pin S and latch: library cache

2010-04-20T14:56:00.004+02:00

On our OLTP db we sometimes experienced short bursts (5-15 seconds) of CPU starvation and waits for cursor: pin S or latch: library cache. Especially during busy times. CPU utilization is normally 65% so we were confident that the waits also cause the CPU starvation and not vice versa. In the past few months we have logged multiple SRs with Oracle for the issue but only made progress with 1 where bug Bug 6904068 - High CPU usage when there are "cursor: pin S" waits [ID 6904068.8] was identified. The problem with the other SRs was a case of wrong scope definition. Even though I would upload all the relevant information (ASH, AWR and OS Watcher stats) Oracle Support would look at the top wait in AWR (“db file sequenctial read”) and suggest that we solve that by doing some query tuning and hopefully the mutex/latch problem will also disappear. Even when I convinced them that the problem is not “db file sequential read” they would ask me to take 3x system state dumps when the problem appears. That was an impossible task as the first sytem state dump would kick in when the problem is already occurring, so we could never give support the info they claimed they needed to diagnose the problem.

We were in the fortunate position to ask Tanel Poder to have a look. He also posted some info about the problem and how he systematically solved it. And with the same info I always give to Oracle support, he could correctly identify the problem in a couple of minutes! Most amazing is that the solution is available in the Oracle Documentation. 10g Release 2 (10.2) for UNIX-Based Operating Systems. B15658-02. Appendix B Administering Oracle Database on HP-UX.

What the documentation says:
On HP-UX, most processes use a time-sharing scheduling policy. Time sharing can have detrimental effects on Oracle performance by descheduling an Oracle process during critical operations, for example, when it is holding a latch. HP-UX has a modified scheduling policy, referred to as SCHED_NOAGE, that specifically addresses this issue. Unlike the normal time-sharing policy, a process scheduled using SCHED_NOAGE does not increase or decrease in priority, nor is it preempted.
This feature is suited to online transaction processing environments because online transaction processing environments can cause competition for critical resources.

So the OS scheduler may deschedule a Oracle process while it is holding a mutex/latch because the OS is not aware of what is going on inside Oracle. Now other processes which happen to use that cursor will fail to get the mutex and start spinning. If the mutex holder process has a lower priority than all the other processes who yielded CPU, then it may not get onto CPU soon enough, so it still holds that mutex and the other processes spin and use CPU trying to get that mutex.

How Tanel proved it:
Below are queries from v$event_histogram for the top 2 problem waits. Notice the number of histogram buckets where the wait time is > 512ms:

EVENT_NAME WAIT_TIME_MILLI WAIT_COUNT WAIT%
------------------------------ --------------- -------------- ------
latch: library cache 1 815,980 52.76
latch: library cache 2 187,232 12.11
latch: library cache 4 168,402 10.89
latch: library cache 8 138,053 8.93
latch: library cache 16 102,906 6.65
latch: library cache 32 69,149 4.47
latch: library cache 64 43,797 2.83
latch: library cache 128 11,916 0.77
latch: library cache 256 3,867 0.25
latch: library cache 512 1,005 0.06
latch: library cache 1,024 1,108 0.07
latch: library cache 2,048 1,516 0.10
latch: library cache 4,096 1,293 0.08
latch: library cache 8,192 351 0.02

EVENT_NAME WAIT_TIME_MILLI WAIT_COUNT WAIT%
------------------------------ --------------- -------------- ------
cursor: pin S 1 59,309,179 86.63
cursor: pin S 2 5,988,534 8.75
cursor: pin S 4 2,313,442 3.38
cursor: pin S 8 675,114 0.99
cursor: pin S 16 113,084 0.17
cursor: pin S 32 23,804 0.03
cursor: pin S 64 15,434 0.02
cursor: pin S 128 9,643 0.01
cursor: pin S 256 4,247 0.01
cursor: pin S 512 2,912 0.00
cursor: pin S 1,024 2,871 0.00
cursor: pin S 2,048 2,626 0.00
cursor: pin S 4,096 1,980 0.00
cursor: pin S 8,192 967 0.00
cursor: pin S 16,384 278 0.00
cursor: pin S 32,768 32 0.00

ps –efl > ps.txt
cat ps.txtgrep ".:..:.."awk '{ printf "%6d %50s\n", $7, $15 }'>ps1.txt
cat ps.txtgrep -v ".:..:.."awk '{ printf "%6d %50s\n", $7, $16 }'>ps2.txt
cat ps1.txt ps2.txtgrep ora.*abcprdsort -nuniq -csort -nbrhead -20

NUM
PROCS PRIO PROCESS_NAME
----- ------ -------------------
1936 154 oracleabcprd
25 148 oracleabcprd
4 241 oracleabcprd
1 240 oracleabcprd
1 235 oracleabcprd
1 229 oracleabcprd
1 223 oracleabcprd
1 207 oracleabcprd
1 195 oracleabcprd
1 187 oracleabcprd
1 181 oracleabcprd
1 179 oracleabcprd
1 154 ora_smon_abcprd
1 154 ora_rvwr_abcprd
1 154 ora_rsm0_abcprd
1 154 ora_reco_abcprd
1 154 ora_rbal_abcprd
1 154 ora_qmnc_abcprd
1 154 ora_q004_abcprd
1 154 ora_q003_abcprd

Note the different priorities of the oracle client processes. After we enabled SCHED_NOAGE for Oracle Database as described in the documentation. The output looked like this:

EVENT_NAME WAIT_TIME_MILLI WAIT_COUNT WAIT%
------------------------------ --------------- -------------- ------
latch: library cache 1 53,212 57.39
latch: library cache 2 10,517 11.34
latch: library cache 4 11,069 11.94
latch: library cache 8 5,308 5.72
latch: library cache 16 3,715 4.01
latch: library cache 32 3,506 3.78
latch: library cache 64 5,254 5.67
latch: library cache 128 131 0.14
latch: library cache 256 5 0.01
latch: library cache 512 7 0.01
latch: library cache 1,024 3 0.00

EVENT_NAME WAIT_TIME_MILLI WAIT_COUNT WAIT%
------------------------------ --------------- -------------- ------
cursor: pin S 1 2,267 15.46
cursor: pin S 2 0 0.00
cursor: pin S 4 0 0.00
cursor: pin S 8 0 0.00
cursor: pin S 16 0 0.00
cursor: pin S 32 0 0.00
cursor: pin S 64 0 0.00
cursor: pin S 128 12,380 84.45
cursor: pin S 256 12 0.08

NUM
PROCS PRIO PROCESS_NAME
----- ------ -------------------
1992 178 oracleabcprd
1 178 ora_smon_abcprd
1 178 ora_rvwr_abcprd
1 178 ora_rsm0_abcprd
1 178 ora_reco_abcprd
1 178 ora_rbal_abcprd
1 178 ora_qmnc_abcprd
1 178 ora_q001_abcprd
1 178 ora_q000_abcprd
1 178 ora_psp0_abcprd
1 178 ora_pmon_abcprd
1 178 ora_p009_abcprd
1 178 ora_p008_abcprd
1 178 ora_p007_abcprd
1 178 ora_p006_abcprd
1 178 ora_p005_abcprd
1 178 ora_p004_abcprd
1 178 ora_p003_abcprd
1 178 ora_p002_abcprd

Note that the number of wait buckets are less. The wait times are sort as it should be. And the process priority report shows that all oracle client processes are on the same priority.

Thanks Tanel!

OCM Disconnected Mode: quick setup guide

2009-11-16T09:07:00.001+02:00

Download and move ocm-Production-HP-UX-IA64.zip to $ORACLE_HOME
# unzip ocm-Production-HP-UX-IA64.zip
#cd ccr/config
#vi ccr.properties and add the following line to disable the collection of the host IP address:
ccr.metric.host.ecm_hw_nic.inet_address=false
Add the following line to disable the Network Interface MAC address collection:
ccr.metric.host.ecm_hw_nic.mac_address=false

Configuring Oracle Configuration Manager in Disconnected Mode (so that it does not upload info to Oracle via the internet through the proxy server
$ORACLE_HOME/ccr/bin/setupCCR -s -d

Instrument the Database for Configuration Collections. The following steps must be repeated for every database instance, including multiple instances running from the same ORACLE_HOME.

$ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectconfig –s

After Oracle Configuration Manager has been configured, you must run the emCCR
collect command to collect configuration data.

$ORACLE_HOME/ccr/bin/emCCR collect

You can find the disconnected mode collection at the following location:
$ORACLE_HOME/ccr/hosts//state/upload/ocmconfig.jar

To upload this configuration to My Oracle Support, transfer the ocmconfig.jar to a
system that is connected to the Internet and attach it to a Service Request (SR).

Last password change date

2009-11-13T11:18:00.000+02:00

Our databases recently went through an audit and we were asked to supply a list of all database users and the date when the last password change occurred. My first reaction was that Oracle does not keep that information, but a little research proofed that I was wrong. The information is kept in sys.user$.ptime
The following query will show when last a password was changed:

select du.username, du.profile, du.account_status, u.ptime last_pwd_change
from dba_users du, sys.user$ u
where du.username = u.name
order by 2, 4
/

DBMS_FILE_TRANSFER

2009-04-06T14:10:00.002+02:00

Oracle recommends the use of RMAN to create a physical standby database. But in the manual and various MAA white papers they simply state that you should make the backups available to the target system and that the path to the backup files on the source and standby server must be the same.

Unfortunately they do not recommend how you should do this, especially when you have a FRA configured in ASM.

With the release of version 11 ASM Oracle introduced the ASMCMD cp command. I thought that solves the problem since the white paper (Oracle Database 11G Automatic Storage Management New Features Overview – published June 2007) states “The ASMCMD cp command allows you to copy files between ASM disk groups between 2 ASM servers”, but I could not get the command to copy my RMAN backupsets between ASM disk groups. I eventually logged SR 7173090.993 but even Oracle support was not sure and they wasted 2 days trying to make it work. Only after 2 days, did Oracle support say that the command does not work for copying backupsets between ASM disk groups (only data files).

OK what now?

I had a look around and decided that my only option is the cumbersome (but at least workable) DBMS_FILE_TRANSFER package.

First step is to locate the rman backupsets on the source server:


ASMCMD> pwd
+PCASDGF/CASOB1/BACKUPSET/2009_03_27

Now create the same directory structure on the remote server. On the standby server I first created an ASM Diskgroup with the same name as the FRA on production (+PCASDGF).


ASMCMD> mkdir +PCASDGF/CASOB1
ASMCMD> mkdir +PCASDGF/CASOB1/BACKUPSET
ASMCMD> mkdir +PCASDGF/CASOB1/BACKUPSET/2009_03_27

Use DBMS_FILE_TRANSFER to instantiate an ASM DataGuard database

DBMS_FILE_TRANSFER.GET_FILE contacts a remote database to read a remote file and then creates a copy of the file in the local file system. But before I can use this method I need a "stage" instance on the standby server. (I used DBCA to create a stage database - it took 20 minutes to create)

Prepare the newly created stage database to use DBMS_FILE_TRANSFER:


CONNECT sys/XXX
CREATE OR REPLACE DIRECTORY DEST_DIR AS '+PCASDGF/CASOB1/BACKUPSET/2009_03_27'
CREATE DATABASE LINK SOURCE_DB CONNECT TO system IDENTIFIED BY XXX USING 'CASOB1.VODACOM.CO.ZA';

On Primary:


CREATE OR REPLACE DIRECTORY SOURCE_DIR AS '+PCASDGF/CASOB1/BACKUPSET/2009_03_27';

Get a list of the backupsets on the source server:


ASMCMD> pwd
+PCASDGF/CASOB1/BACKUPSET/2009_03_27
ASMCMD> ls
annnf0_TAG20090327T115944_0.417.682603193
annnf0_TAG20090327T115944_0.418.682603193
...

Use an editor to create a SQL command (one per backupset) which looks like this:


execute dbms_file_transfer.get_file(source_directory_object => 'SOURCE_DIR',source_file_name => 'ncsdf0_TAG20090406T092911_0.1
367.683458159', destination_directory_object => 'DEST_DIR',destination_file_name => 'BACKUP1',source_database => 'SOURCE_DB');
execute dbms_file_transfer.get_file(source_directory_object => 'SOURCE_DIR',source_file_name => 'nnndf0_TAG20090406T092911_0.1
364.683458159', destination_directory_object => 'DEST_DIR',destination_file_name => 'BACKUP2',source_database => 'SOURCE_DB');
…

Note that the destination_file_name is not the same as the source file name. You will get an ORA-15046 if they are the same, this error is the result of the backupset being an Oracle-Managed file name.

So I execute these commands on the standby server from the stage instance. The progress of the command can be monitored using the V$SESSION_LONGOPS view. Once all the backupsets are copied over I was done with the stage database.

The next step is to create aliases for the new names of the backuppieces, so back on the source server issue:


ALTER DISKGROUP PCASDGF ADD ALIAS '+PCASDGF/CASOB1/BACKUPSET/2009_03_27/BACKUP1' for '+PCASDGF/CASOB1/BACKUPSET/2009_03_27/annnf0_TAG20090327T115944_0.417.682603193';
…

Once done, I can query V$ASM_ALIAS and there are two entries per backuppiece. One for the backuppiece and one for the alias.

The aim of doing this was to create a Data Guard instance, so before I can restore the backup I first have to prepare a Data Guard instance:

Log onto the standby server


# $export ORACLE_SID=casobc
# $orapwd=orapwcasobc password=XXX

sqlplus
SYS@casobc>create spfile from pfile; (I previously created a pfile from production with a few changes necessary for DG)
File created.
SYS@casobc>startup force nomount;
ORACLE instance started.

OK now I am ready to start the restore. First catalog the backuppieces in the source server/primary RMAN

Connect to RMAN on the primary server


RMAN> connect target sys/XXX@casob1.vodacom.co.za;

connected to target database: CASOB1 (DBID=1215042422)

RMAN> connect auxiliary /

connected to auxiliary database: CASOB1 (not mounted)

RMAN> catalog backuppiece '+PCASDGF/ CASOB1/BACKUPSET/2009_03_27/BACKUP1';
RMAN> catalog backuppiece '+PCASDGF/CASOB1/BACKUPSET/2009_03_27/BACKUP2';
…

Once done, I could list the backup in RMAN and see two entries, one for the backuppiece and one for the alias.
Eventually I am ready to use the RMAN backup to create a data guard instance.


RMAN> duplicate target database for standby ;
...
RMAN> exit

RMAN restore to new host

2009-03-09T11:14:00.003+02:00

The mission:

Restore a production 10.2.0.4.0 database to a new host in order to test our recoveries. The production database is part of a Data Guard configuration in Maximum Availability mode and it runs on Unix RAW devices. The restore will be to ASM.

Notes by my colleague Ian Baugaard:

If you perform a test restore only, then do not connect to the recovery catalog when restoring the datafiles. Otherwise, RMAN records information about the restored datafiles to the recovery catalog. This intereferes with future attempts to restore and recover the primary database. If you must use a recovery catalog because the control file is not large enough to contain the RMAN repository data on all of the backups that you need to restore, then export the catalog and import it into a different schema or database and use the copied recovery catalog for the test restore. Otherwise, the catalog considers the restored database as the current target database.

Also confirm all of the following before commencing:

- Ensure that the /etc/oratab file is correctly configured
- Ensure that the following environment variable are set to the values shown
o NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS
o NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
- Ensure that the $ORACLE_BASE/admin/$ORACLE_SID/bdump directory is empty
- Ensure that the job_queue_processes parameter is set to 0 when editing the parameter file at step 3
- That the location specified in the log_archive_dest parameter is valid, writable and empty
- Confirm whether or not the source database is using a change tracking file, and if so, try to ensure that the corresponding path exists on the node where the restore is being performed
- Ensure that the tnsnames.ora file does not contain any references whatsoever to a production system

1. Ensure your environment is correctly configured, i.e. ORACLE_SID is set correctly and that the Oracle software installation matches the environment of the source system. When ready, start RMAN and connect to the target without connecting to the recovery catalog.

rman target / NOCATALOG

2. Start the instance without mounting it, using the DBID obtained from the v$database view on the source database. RMAN will fail to find the server parameter file, which has not yet been restored, but will start the instance with a "dummy" file.

RMAN> set DBID dbid ;
RMAN> startup nomount ;

3. Restore and edit the server parameter file. NB: The values for NSR Server and NSR Client will have to be adjusted accordingly, and can be derived from the values of the production script

run {
allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
restore spfile to pfile '$ORACLE_HOME/dbs/init$ORACLE_SID.ora' from autobackup;
shutdown abort;
}

4. From a second session, edit all appropriate parameters of the restored parameter file to cater for the environment you are restoring on, i.e. to reflect new directory structures, memory differences, etc. Once done, start the instance using the new file

RMAN> startup force nomount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora' ;

5. Proceed to restore the controlfile from an autobackup from your original session

run {
allocate channel c1 device type SBT PARMS=' ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
restore controlfile from autobackup;
alter database mount;
}

6. Query the database filenames recorded in the control file on the new host by running the following query in SQL*Plus

col name for a60
SELECT file# AS "File/Grp#", name FROM v$datafile
UNION
SELECT group#, member FROM v$logfile;
EXIT

7. Write the RMAN recovery script. The script must include the following steps:
a. For each datafile on the destination host that is restored to a different path than it had on the source host, use a SET NEWNAME command to specify the new path on the destination host. (If the file systems on the destination system are set up to have the same paths as the source host, then do not use SET NEWNAME for those files restored to the same path as on the source host.) See an example of a dynamic SQL script to generate the commands needed below

select
'set newname for datafile '|| file# ||' to '''|| replace( name, 'rabdg', 'rabbcv' ) ||''' ;'
from v$datafile
order by file#

b. For each online redo log that is to be created at a different location than it had on the source host, use SQL ALTER DATABASE RENAME FILE commands to specify the pathname on the destination host. (If the file systems on the destination system are set up to have the same paths as the source host, then do not use ALTER DATABASE RENAME FILE for those files restored to the same path as on the source host.) See an example of a dynamic SQL script to generate the commands needed below

select
'SQL "alter database rename file '''''|| member ||''''' to '''''|| replace ( member, 'rabdg', 'rabbcv' ) ||''''' " ;'
from v$logfile
order by member
c. Perform a SET UNTIL to limit media recovery to the end of the archived redo logs.
d. Run SWITCH so that the control file recognizes the new path names as the official new names of the datafiles
e. Restore and recover the database
NB: By default, RMAN does not restore read-only files when you issue the RESTORE DATABASE command.

run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
allocate channel c2 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';
allocate channel c3 device type SBT PARMS='ENV=(NSR_SERVER=nsr server, NSR_CLIENT=original server)';

# RENAME THE DATAFILES AND ONLINE REDO LOGS
set newname for datafile 1 to '+TUNXDG_RESTORE/casprd/datafile/casprd_SYSTEM01' ;
set newname for datafile 2 to '+TUNXDG_RESTORE/casprd/datafile/casprd_UNDO01' ;
SQL "alter database rename file ''/dev/vx/rdsk/redodg/casprd_REDO1'' to ''+TUNXDG_RESTORE/casprd/onlinelog/casprd_REDO1'' ";

# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20080310 08:33:12','YYYYMMDD HH24:MI:SS')" ;
# RESTORE THE DATABASE AND SWITCH THE DATAFILE NAMES
restore database check readonly force;
switch datafile all;

# RECOVER THE DATABASE
recover database;
}
EXIT

8. Now perform an OPEN RESETLOGS at the restored database.

RMAN> alter database open resetlogs ;

9. Issue a shutdown and start the restored database in mount mode, and correct the location of all tempfiles

10. If this was a test restore, and it was successful, then you can shut down the test database instance, and delete the test database with all of its files.

SQL> startup force mount restrict pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora' ;
SQL> drop database ;

rm $ORACLE_HOME/dbs/init$ORACLE_SID.ora
rm log_archive_dest/*

Scripts and Logfiles


casprd1:/opt/apps/oracle/database/10.2.0.4/rdbms/admin> rman target / NOCATALOG

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Feb 18 14:17:28 2009

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

connected to target database (not started)

RMAN> set dbid 2070848595;

executing command: SET DBID

RMAN> startup nomount ;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/opt/apps/oracle/database/10.2.0.4/dbs/initcasprd1.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area     159383552 bytes

Fixed Size                     2054608 bytes
Variable Size                 67110448 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6332416 bytes

RMAN> run {
2> allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1,NSR_CLIENT=pcapa2)';
3> restore spfile to pfile '$ORACLE_HOME/dbs/init$ORACLE_SID.ora' from autobackup;
4> shutdown abort;
5> }

allocated channel: c1
channel c1: sid=39 devtype=SBT_TAPE
channel c1: NMO v4.5.0.0

Starting restore at 19-FEB-2009 12:40:16

Change init.ora file to:
*.audit_file_dest='/opt/apps/oracle/admin/casprd1/adump'
*.background_dump_dest='/opt/apps/oracle/admin/casprd1/bdump'
*.compatible='10.2.0'
*.control_file_record_keep_time=21
*.core_dump_dest='/opt/apps/oracle/admin/casprd1/cdump'
*.db_block_size=8192
*.db_cache_size=500000000
*.db_files=500
*.db_name='casprd'
*.db_writer_processes=4
*.instance_name='casprd1'
*.large_pool_size=50000000
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='casprd_%t_%s_%r.arc'
*.log_archive_max_processes=5
*.log_archive_min_succeed_dest=1
*.log_buffer=16384000
*.os_authent_prefix='ops$'
*.recovery_parallelism=4
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='casprd'
*.sga_target=2G
*.undo_management='AUTO'
*.undo_retention=28800
*.undo_tablespace='UNDO'
*.user_dump_dest='/opt/apps/oracle/admin/casprd1/udump'
*.workarea_size_policy='auto'
*.db_create_file_dest='+TUNXDG_RESTORE'
*.db_recovery_file_dest='+TUNXDG_RESTORE'
*.DB_RECOVERY_FILE_DEST_SIZE=2G
*.PROCESSES=200

RMAN> startup force nomount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora' ;

Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     2057496 bytes
Variable Size                503319272 bytes
Database Buffers            1610612736 bytes
Redo Buffers                  31494144 bytes

RMAN> run {
2> allocate channel c1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1,NSR_CLIENT=pcapa2,NSR_DEBUG_FILE=/home/oracle/nsr_icap2.log)';
3> restore controlfile from autobackup;
4> alter database mount;
5> }

allocated channel: c1
channel c1: sid=33 devtype=SBT_TAPE
channel c1: NMO v4.5.0.0

Starting restore at 19-FEB-2009 13:53:31

channel c1: looking for autobackup on day: 20090219
channel c1: autobackup found: c-2070848595-20090219-04
channel c1: control file restore from autobackup complete
output filename=+TUNXDG_RESTORE/casprd/controlfile/current.351.679240505
output filename=+TUNXDG_RESTORE/casprd/controlfile/current.352.679240507
Finished restore at 19-FEB-2009 13:55:17

database mounted
released channel: c1

Edit init.ora file to reflect new controlfile settings:
*.control_files='+TUNXDG_RESTORE/casprd/controlfile/current.351.679240505’,’+TUNXDG_RESTORE/casprd/controlfile/current.352.679240507'


casprd1:/home/oracle/meyert> cat icap_restore.ksh
rman target / nocatalog log='/home/oracle/meyert/icap_restore.log' cmdfile='/home/oracle/meyert/icap_restore.par'

casprd1:/home/oracle/meyert> cat icap_restore.par
run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel chnl1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl2 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl3 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';

# RENAME THE DATAFILES AND ONLINE REDO LOGS
set newname for datafile 1 to '+TUNXDG_RESTORE/casprd/datafile/casprd_SYSTEM01' ;
set newname for datafile 2 to '+TUNXDG_RESTORE/casprd/datafile/casprd_UNDO01' ;
…
set newname for datafile 321 to '+TUNXDG_RESTORE/casprd/datafile/casprd_SA_T_CMD_PARAM_0422' ;

SQL "alter database rename file ''/dev/vx/rdsk/redodg/casprd_REDO1'' to ''+TUNXDG_RESTORE/casprd/onlinelog/casprd_REDO1'' ";
…

# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20090303 06:00:00','YYYYMMDD HH24:MI:SS')" ;

# RESTORE THE DATABASE AND SWITCH THE DATAFILE NAMES
restore database check readonly force;
switch datafile all;

}
EXIT

casprd1:/home/oracle/meyert> cat icap_restore.log

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Mar 5 15:44:38 2009

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

connected to target database: CASPRD (DBID=2070848595, not open)
using target database control file instead of recovery catalog

allocated channel: chnl1
channel chnl1: sid=26 devtype=SBT_TAPE
channel chnl1: NMO v4.5.0.0

allocated channel: chnl2
channel chnl2: sid=33 devtype=SBT_TAPE
channel chnl2: NMO v4.5.0.0

allocated channel: chnl3
channel chnl3: sid=11 devtype=SBT_TAPE
channel chnl3: NMO v4.5.0.0

executing command: SET NEWNAME

executing command: SET NEWNAME

…

executing command: SET until clause

Starting restore at 05-MAR-2009 15:51:58

channel chnl1: starting datafile backupset restore
channel chnl1: specifying datafile(s) to restore from backup set
restoring datafile 00009 to +TUNXDG_RESTORE/casprd/datafile/casprd_ic_t_met2_12
restoring datafile 00027 to +TUNXDG_RESTORE/casprd/datafile/casprd_pp_t_conf_0401
restoring datafile 00156 to +TUNXDG_RESTORE/casprd/datafile/casprd_sa_i_ssb
restoring datafile 00193 to +TUNXDG_RESTORE/casprd/datafile/casprd_pp_t_conf_0409
channel chnl1: reading from backup piece /full_CASPRD_t680405445_s84501_p1/
…
Finished restore at 07-MAR-2009 01:19:52

datafile 1 switched to datafile copy
input datafile copy recid=665 stamp=680836812 filename=+TUNXDG_RESTORE/casprd/datafile/casprd_system01
datafile 2 switched to datafile copy
input datafile copy recid=666 stamp=680836813 filename=+TUNXDG_RESTORE/casprd/datafile/casprd_undo01
…
datafile 321 switched to datafile copy
input datafile copy recid=985 stamp=680837070 filename=+TUNXDG_RESTORE/casprd/datafile/casprd_sa_t_cmd_param_0422

Starting recover at 07-MAR-2009 01:25:06
channel chnl1: starting incremental datafile backupset restore
channel chnl1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: +TUNXDG_RESTORE/casprd/datafile/casprd_sa_t_cmd_param_0418
destination for restore of datafile 00035: +TUNXDG_RESTORE/casprd/datafile/casprd_sa_i_cmd
destination for restore of datafile 00042: +TUNXDG_RESTORE/casprd/datafile/casprd_sa_i_event
destination for restore of datafile 00192: +TUNXDG_RESTORE/casprd/datafile/casprd_ic_t_met2_05
channel chnl1: reading from backup piece /level1_CASPRD_t680504446_s84621_p1/
…
channel chnl3: reading from backup piece /level1_CASPRD_t680506557_s84697_p1/
channel chnl3: restored backup piece 1
piece handle=/level1_CASPRD_t680506557_s84697_p1/ tag=TAG20090303T050038
channel chnl3: restore complete, elapsed time: 00:17:46

starting media recovery

Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+TUNXDG_RESTORE/casprd/datafile/casprd_system01'

released channel: chnl1
released channel: chnl2
released channel: chnl3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/07/2009 08:19:08
RMAN-06557: unable to restore archived log thread 1, sequence 12670
RMAN-06558: archived log size of 2043389 kb is bigger than available space of 2020336 kb

Recovery Manager complete.

(My RECOVERY_DEST_SIZE was too small, so I increased it and simply ran a recovery – since the restore was completed)

casprd1:/home/oracle/meyert> cat icap_recovery.ksh
rman target / nocatalog log='/home/oracle/meyert/icap_recovery.log' cmdfile='/home/oracle/meyert/icap_recovery.par'

casprd1:/home/oracle/meyert> cat icap_recovery.par
run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel chnl1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl2 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl3 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';

# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20090303 06:00:00','YYYYMMDD HH24:MI:SS')" ;

# RECOVER THE DATABASE
recover database;
}
EXIT

casprd1:/home/oracle/meyert> cat icap_recovery.par
run {
# ALLOCATE A CHANNEL TO THE TAPE DEVICE
allocate channel chnl1 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl2 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';
allocate channel chnl3 device type SBT PARMS='ENV=(NSR_SERVER=pbcka1, NSR_CLIENT=pcapa2)';

# DO A SET UNTIL TO PREVENT RECOVERY OF THE ONLINE LOGS
set until time "TO_DATE('20090303 06:00:00','YYYYMMDD HH24:MI:SS')" ;

# RECOVER THE DATABASE
recover database;
}
EXIT


casprd1:/home/oracle/meyert> cat icap_recovery.log

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Mar 9 07:33:00 2009

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

connected to target database: CASPRD (DBID=2070848595, not open)
using target database control file instead of recovery catalog

allocated channel: chnl1
channel chnl1: sid=33 devtype=SBT_TAPE
channel chnl1: NMO v4.5.0.0

allocated channel: chnl2
channel chnl2: sid=12 devtype=SBT_TAPE
channel chnl2: NMO v4.5.0.0

allocated channel: chnl3
channel chnl3: sid=27 devtype=SBT_TAPE
channel chnl3: NMO v4.5.0.0

executing command: SET until clause

Starting recover at 09-MAR-2009 07:33:37

starting media recovery

channel chnl1: starting archive log restore to default destination
channel chnl1: restoring archive log
archive log thread=1 sequence=12668
channel chnl1: restoring archive log
archive log thread=1 sequence=12669
channel chnl1: reading from backup piece /arch_CASPRD_t680507135_s84703_p1/
channel chnl1: restored backup piece 1
piece handle=/arch_CASPRD_t680507135_s84703_p1/ tag=TAG20090303T054533
channel chnl1: restore complete, elapsed time: 00:01:17
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12668.354.681032213 thread=1 sequence=12668
channel default: deleting archive log(s)
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12668.354.681032213 recid=108554 stamp=681032266
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12669.353.681032215 thread=1 sequence=12669
channel default: deleting archive log(s)
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12669.353.681032215 recid=108553 stamp=681032231
channel chnl1: starting archive log restore to default destination
channel chnl1: restoring archive log
archive log thread=1 sequence=12670
channel chnl1: reading from backup piece /arch_CASPRD_t680515326_s84705_p1/
channel chnl1: restored backup piece 1
piece handle=/arch_CASPRD_t680515326_s84705_p1/ tag=TAG20090303T080205
channel chnl1: restore complete, elapsed time: 00:02:06
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12670.353.681033147 thread=1 sequence=12670
channel default: deleting archive log(s)
archive log filename=+TUNXDG_RESTORE/casprd/archivelog/2009_03_09/thread_1_seq_12670.353.681033147 recid=108555 stamp=681033249
media recovery complete, elapsed time: 00:05:01
Finished recover at 09-MAR-2009 07:59:13
released channel: chnl1
released channel: chnl2
released channel: chnl3

Recovery Manager complete.

Startup fails with ORA-16072: a minimum of one standby database destination is required Instance terminated by LGWR
I found the solution on Stewart F's blog.

Metalink note 245731.1 this was caused by that fact that there was still some kind of reference in my Primary databases data dictionary: "The Primary Database has still the Protection Mode stored in the Data Dictionary. The Protection Mode requires a running connection to a Standby Database with LGWR SYNC as Log-Transportation Mode. Since the standby is not available anymore,LGWR of the primary terminates the instance."

Solution:



SYS@casprd1> startup mount;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2057496 bytes
Variable Size             503319272 bytes
Database Buffers         1610612736 bytes
Redo Buffers               31494144 bytes
Database mounted.
SYS@casprd1> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Database altered.

SYS@casprd1> shutdown immediate
SYS@casprd1> startup mount;
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size                  2057496 bytes
Variable Size             503319272 bytes
Database Buffers         1610612736 bytes
Redo Buffers               31494144 bytes
Database mounted.
SYS@casprd1> select name from v$tempfile ;

NAME
-----------------------------------------------------------------------------------------------------------------------------
/dev/vx/rdsk/oradg/casprd_TEMP01
/dev/vx/rdsk/oradg/casprd_TEMP02
/dev/vx/rdsk/oradg/casprd_TEMP04
/dev/vx/rdsk/oradg1/casprd_TEMP05
/dev/vx/rdsk/oradg1/casprd_TEMP06
/dev/vx/rdsk/oradg1/casprd_TEMP07
/dev/vx/rdsk/oradg1/casprd_TEMP08

7 rows selected.

SYS@casprd1> alter database rename file '/dev/vx/rdsk/oradg/casprd_TEMP01' to '+TUNXDG_RESTORE/casprd/tempfile/casprd_TEMP01';

Database altered.

Etc for all 7 tempfiles.

SYS@casprd1> alter database open;

Database altered.

Data Guard Lag Time

2009-01-23T14:38:00.006+02:00

We use the Broker to administer our Data Guard configurations and we also use OEM to monitor our databases, we have found that every time we change the state of the Physical Standby between ONLINE and READ-ONLY (and back to ONLINE again) we start getting OEM alarms for reaching our set limits for Lag Time (the time in seconds that the Physical Standby is behind the Primary database).This Lag Time can also be found in v$dataguard_stats (in fact that is where OEM gets it from).

What we have found was that we need to bounce the Physical Standby to force v$dataguard_stats to get updated with the correct values. The good news is that the Physical standby does not really have a lag time, it is just that v$dataguard_stats does not update itself when DG STATES change. The bad news is that OEM shows the faulty lag time, generates an alarm and since our alarms are visible to the whole organization we have to explain that it is not really a problem.

To confirm that the problem is in v$dataguard_stats and is not a real reflection of reality, do the following:


On Standby:

SQL>select TIME_COMPUTED from v$dataguard_stats;

TIME_COMPUTED
------------------------------
09-JAN-2009 09:45:13
09-JAN-2009 09:45:13
09-JAN-2009 09:45:13
09-JAN-2009 09:45:13
09-JAN-2009 09:45:13

SQL>select current_scn from v$database;

CURRENT_SCN
--------------------------
9661858803219

On Primary:

SQL>select scn_to_timestamp(9661857384219) from dual;

SCN_TO_TIMESTAMP(9661857384219)
---------------------------------------------------------
12-JAN-09 12.26.57.000000000 PM

SQL>select SCN_TO_TIMESTAMP(current_scn) from v$database;

SCN_TO_TIMESTAMP(CURRENT_SCN)
---------------------------------------------------------
12-JAN-09 12.30.00.000000000 PM

So we are only a few seconds behind, but v$dataguard_stats and OEM show otherwise. Bouncing the Physical Standby database fixed this.

KeePass and Putty

2009-01-21T15:47:00.003+02:00

KeePass and Putty are software programs that work great together, especially in an enterprise where you have a lot of passwords to remember. Autotmatic Login to a Unix server is simply a “Ctrl+U” away.

First you need to setup your Putty Configuration, for example:

Servers are saved as sessions and Auto-login username is specified

In KeepPass you need to configure 1 entry per server. The URL entry is what makes KeePass more than a list of passwords.

If putty is in your $PATH then the command you typed in the URL field above (putty -load -pw XX) will also work from the command line. In the main KeepPass window you simply need to highlight the entry and press “Ctrl-U”.

Problem setting up Archive Log file management on ASM

2008-12-23T12:16:00.010+02:00

I created a ASM diskgroup for FRA and set the relevant parameters. The aim to to write Archive logs, Flashback logs and RMAN backups to this FRA.


SQL>select FREE_MB from v$asm_diskgroup where NAME='PRNADGF';

 FREE_MB
----------
 2,761,992

SQL>show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0

SQL>alter system set db_recovery_file_dest_size=2700000M;

System altered.

SQL>alter system set db_recovery_file_dest='+PRNADGF';

System altered.

My problems started when I changed to Archive Log Mode


SQL>archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Current log sequence           12

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

SQL>startup mount
ORACLE instance started.

Total System Global Area 3156803584 bytes
Fixed Size                  2110872 bytes
Variable Size             402655848 bytes
Database Buffers         2734686208 bytes
Redo Buffers               17350656 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            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Next log sequence to archive   12
Current log sequence           12

So the archive log files should be written to my FRA on ASM, but when I tested it I got a unexpected error:


SQL>alter system archive log current;

alter system archive log current
*
ERROR at line 1:
ORA-16038: log 3 sequence# 12 cannot be archived
ORA-19502: write error on file "", block number  (block size=)
ORA-00312: online log 3 thread 1: '+PRNADG1/rnatst/onlinelog/group_3.278.672669129'

This error message is misleading because there is nothing wrong with the FRA on ASM. I found the real error message in the alert.log file:


ARCH: Encountered disk I/O error 19502
ARCH: Closing local archive destination LOG_ARCHIVE_DEST_1: '/opt/apps/oracle/product/database/11.1.0.6.0/dbs/arch1_12_672668870.dbf' (error 19502)
(rnatst)
Errors in file /opt/apps/oracle/diag/rdbms/rnatst/rnatst/trace/rnatst_ora_21990.trc:

So the archiver also wants to archive to my $ORACLE_HOME/dbs directory, it cannot do that because there is not enough space on that filesystem, but the real question is why does it want to write to that destination?
When I do a show parameter I don't get the answer:


SQL> show parameter log_archive_dest

log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
log_archive_duplex_dest              string
log_archive_min_succeed_dest         integer     1

Again I found the answer in the alert.log


Starting ORACLE instance (normal)
Using LOG_ARCHIVE_DEST_1 parameter default value as /opt/apps/oracle/product/database/11.1.0.6.0/dbs/arch
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST

It seems to be a DEFAULT destination that does not show up when I use ARCHIVE LOG LIST or SHOW PARAMETER LOG_ARCHIVE_DEST.
It does show up in V$ARCHIVE_DEST though:


SQL> select dest_id, destination, status from V$ARCHIVE_DEST;


 DEST_ID DESTINATION                                             STATUS
---------- ------------------------------------------------------- ---------
       1 /opt/apps/oracle/product/database/11.1.0.6.0/dbs/arch   VALID
       2                                                         INACTIVE
       3                                                         INACTIVE
       4                                                         INACTIVE
       5                                                         INACTIVE
       6                                                         INACTIVE
       7                                                         INACTIVE
       8                                                         INACTIVE
       9                                                         INACTIVE
      10 USE_DB_RECOVERY_FILE_DEST                               VALID

To solve the problem:


SQL> alter system set log_archive_dest_state_1=DEFER;

And to test


SQL>alter system archive log current;

System altered.

SQL>select dest_id, name from v$archived_log;

 DEST_ID NAME
---------- ----------------------------------------------------------------------
      10 +PRNADGF/rnatst/archivelog/2008_12_22/thread_1_seq_12.256.674133347

SQL> select dest_id, destination, status from V$ARCHIVE_DEST;


 DEST_ID DESTINATION                                             STATUS
---------- ------------------------------------------------------- ---------
       1 /opt/apps/oracle/product/database/11.1.0.6.0/dbs/arch   DISABLED
       2                                                         INACTIVE
       3                                                         INACTIVE
       4                                                         INACTIVE
       5                                                         INACTIVE
       6                                                         INACTIVE
       7                                                         INACTIVE
       8                                                         INACTIVE
       9                                                         INACTIVE
      10 USE_DB_RECOVERY_FILE_DEST                               VALID

Lesson learned.
Keep on looking until you can explain the error.

Ora-17505 using RMAN with ASM

2008-12-12T10:43:00.005+02:00

I had to create a Data Guard instance on test, so that we can evaluate Transparent Application Failover (TAF) for the app. I wanted to test the same procedure to build Data Guard that I will use for Production, and that meant that I had to do a RMAN backup to ASM. Our Storage team loaned me LUNs of different sizes for my temporary ASM on test.

But my backup command (Backup filesperset 10 database include current controlfile for standby) failed with:

RMAN-03009: failure of backup command on ORA_DISK_13 channel at 10/30/2008 20:21:27
ORA-19510: failed to set size of 5779210 blocks for file "+PCASDGF" (blocksize=8192)
ORA-17505: ksfdrsz:1 Failed to resize file to size 5779210 blocks

This was a surprise because the test db is 7TB and FRA is 9TB.


SQL> SELECT sum(space), sum(bytes) FROM v$asm_file;
and
SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

confirmed that I had more than enough space available. In fact the backup failed after using only 47% fo the available space.

It turns out that the smallest disk in the diskgroup was the bottleneck.


SQL>select group_number, disk_number, total_mb, free_mb from v$asm_disk order by 4;

GROUP_NUMBER DISK_NUMBER TOTAL_MB FREE_MB
----------------------------------------
1         13       86315          70
1         16       17263       17080
1         14       17263       17080
1        129       34522       34168
1        130       34522       34168
1        131       34522       34168
1         19       34522       69052
...

As you can see disk 13 only had 70MB of space available. I removed all the disks of varying sizes and only kept the disks of 69052 MB Size. The total size of the FRA came down to 8493396 MB, but the RMAN backup completed successfully.

Lesson Learned:
ASM spreads file extents evenly accross all the disks disks on a diskgroup. An ORA-17505 error can still be encountered due to imbalanced free space between disks. The reason for this is that one disk lacking sufficient free space makes it impossible to do any allocation in a disk group because every file must be evenly allocated across all disks.

Remove ASM disk

2008-12-12T08:32:00.008+02:00

One of the big selling points of ASM is the ability to reconfigure the storage online. I had to remove 10 disks from a +ASM test system that had to be redeployed on another server. The steps seemed easy enough until I ran into a problem;


SQL> select d.MOUNT_STATUS, d.MODE_STATUS, d.STATE, d.NAME, d.PATH
from v$asm_disk d, v$asm_diskgroup dg
where d.GROUP_NUMBER=dg.GROUP_NUMBER
and dg.name = 'PCASDGF'
order by 4
/

MOUNT_S MODE_ST STATE NAME PATH HEADER_STATUS
------- ------- -------- -------------------- --------------------------- -------------
...
CACHED ONLINE NORMAL PCASDGF_0132 /dev/oracle/fra/c17t6d7 MEMBER
CACHED ONLINE NORMAL PCASDGF_0133 /dev/oracle/fra/c17t7d0 MEMBER
CACHED ONLINE NORMAL PCASDGF_0134 /dev/oracle/fra/c17t7d1 MEMBER
CACHED ONLINE NORMAL PCASDGF_0135 /dev/oracle/fra/c17t7d2 MEMBER
CACHED ONLINE NORMAL PCASDGF_0136 /dev/oracle/fra/c17t7d3 MEMBER
CACHED ONLINE NORMAL PCASDGF_0137 /dev/oracle/fra/c17t7d4 MEMBER
CACHED ONLINE NORMAL PCASDGF_0138 /dev/oracle/fra/c17t7d5 MEMBER
CACHED ONLINE NORMAL PCASDGF_0139 /dev/oracle/fra/c17t7d6 MEMBER
CACHED ONLINE NORMAL PCASDGF_0140 /dev/oracle/fra/c17t7d7 MEMBER
CACHED ONLINE NORMAL PCASDGF_0141 /dev/oracle/fra/c17t8d5 MEMBER

SQL> alter diskgroup PCASDGF drop disk PCASDGF_0132;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0133;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0134;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0135;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0136;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0137;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0138;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0139;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0140;
SQL> alter diskgroup PCASDGF drop disk PCASDGF_0141;

You can happily drop disks in a disk group and ASM will seamlessly migrate the data to the existing disks in the disk group. The prompt returns immediatly, but the job (of migrating the data) is not yet done. In order to monitor progress use the following SQL.


SQL> select * from v$asm_operation
/

When the job is done the SQL will retun no rows. The status of the disks are also updated.


SQL> select MOUNT_STATUS, MODE_STATUS, STATE, NAME, PATH, header_status
from v$asm_disk
where name is null
/
MOUNT_S MODE_ST STATE NAME PATH HEADER_STATU
------- ------- -------- ------------------------------ ------------------------------ ------------
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t6d7 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d0 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d1 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d2 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d3 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d4 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d5 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d6 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t7d7 FORMER
CLOSED ONLINE NORMAL /dev/oracle/fra/c17t8d0 FORMER

BUT

# fuser /dev/oracle/fra/c17t6d7 returns/dev/oracle/fra/c17t6d7: 2924o 18184o 18175o 4129o 618o

The process details are

oracle 2924 1 0 Nov 4 ? 17:53 asm_rbal_+ASM
oracle 18184 1 0 Nov 17 ? 1:16 ora_rbal_casbcva
oracle 18175 1 0 Nov 17 ? 1:34 ora_rvwr_casbcva
oracle 4129 4128 0 10:49:06 ? 0:00 oracle+ASM (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 618 1 0 Nov 25 ? 37:06 oraclecasbcva (LOCAL=NO)

Is it safe to physically remove these disks? +ASM still knows about these disks and Oracle processes are still attached to these disks. I logged a SR with Oracle Support and got the following feedback:

Bug 7516653 - DROPPED ASM DISK STILL HELD BY ASM PROCESSES
Closed as duplicate for bug:
Bug 7225720 - ASM DOES NOT CLOSE OPEN DESCRIPTORS EVEN AFTER APPLYING THE Patch 4693355

Fixed in 11.2

Please perform the following workaround:
1. create dummy diskgroup using this disk:
SQL> create diskgroup test external redundancy disk ;
2. drop this diskgroup:
SQL> drop diskgroup test;
3. check if the disk still help by any process.
If the disk still held then we will have to restart the ASM instance.

So that's what I did


SQL> create diskgroup test 
external redundancy 
disk /dev/oracle/fra/c17t6d7','/dev/oracle/fra/c17t7d0',
'/dev/oracle/fra/c17t7d1','/dev/oracle/fra/c17t7d2',
'/dev/oracle/fra/c17t7d3','/dev/oracle/fra/c17t7d4',
'/dev/oracle/fra/c17t7d5','/dev/oracle/fra/c17t7d6',
'/dev/oracle/fra/c17t7d7','/dev/oracle/fra/c17t8d0'
/
SQL> drop diskgroup test
/

Fuser still showed os processes accesing the devices and I eventually had to bounce +ASM before the devices could be safely removed.

SQL workload history with DBA_HIST_SQLSTAT

2008-12-10T13:06:00.008+02:00

Using the DBA_HIST_XXX views from the AWR (automatic workload repository), it has become easier for a DBA to track changes in workload metrics over time. Through a SR I logged with Oracle Support they have supplied me with the following sql statement to track such changes for a single SQL statement.

Just substitute XXX with your SQL_ID.


set trimspool on
set lines 220
set long 10000
set longchunk 10000
set pages 99
set longchunk 100000
set long 10000
set time on
undefine SQL_ID
undefine PLAN_HASH_VALUE
undefine SQL_TEXT

Select distinct dbid, sql_id, plan_hash_value, timestamp
from dba_hist_sql_plan where sql_id='XXX'
order by dbid, timestamp
/

undefine PLAN_HASH_VALUE
accept PLAN_HASH_VALUE prompt 'Please enter PLAN_HASH_VALUE to show Statistics for: '
col iowait_delta format 9999999.99 heading iowaitdelta(ms)
col iowait_total format 9999999.99 heading iowaittotal(ms)
col ELAPSED_TIME_TOTAL format 9999999.99 heading elapsdtimetotal(ms)
col ELAPSED_TIME_DELTA format 9999999.99 heading elapsdtimedelta(ms)
col PLAN_HASH_VALUE heading plan_hashvalue
col CONCURRENCY_WAIT_TOTAL format 9999999.99 heading concwaittotal(ms)
col CONCURRENCY_WAIT_delta format 9999999.99 heading concwaitdelta(ms)
col CLUSTER_WAIT_DELTA format 9999999.99 heading clustwaitdelta(ms)
col CLUSTER_WAIT_TOTAL format 9999999.99 heading clustwaittotal(ms)
col APWAIT_TOTAL format 9999 heading applwaittimetotal(micro)
col APWAIT_DELTA format 9999 heading applwaittimedelta(micro)
col PLSEXEC_TIME_TOTAL format 9999 heading plsqlexectimetotal(micro)
col PLSEXEC_TIME_DELTA format 9999 heading plsqlexectimedelta(micro)
col JAVAEXEC_TIME_DELTA format 9999 heading javaexectimedelta(micro)
col JAVAEXEC_TIME_TOTAL format 9999 heading javaexectimetotal(micro)
col optimizer_cost format 9999 heading optcostcol optimizer_mode format a10 heading optimmode
col kept_versions format 999 heading keptvers
col invalidations_total format 999 heading invalidtot
col invalidations_delta format 999 heading invaliddlt
col parse_calls_total format 99999 heading parsecallstotal
col parse_calls_delta format 99999 heading parsecallsdelta
col executions_total format 999999 heading exectotal
col executions_delta format 999999 heading execdelta
col fetches_total format 9999999 heading fetchestotal
col fetches_delta format 9999999 heading fetchesdelta
col end_of_fetch_count_total format 9999 heading endoffetchcalltotal
col end_of_fetch_count_delta format 9999 heading endoffetchcalldelta
col buffer_gets_total format 99999999 heading buffergetstotal
col buffer_gets_delta format 99999999 heading buffergetsdelta
col disk_reads_total format 999999 heading diskreadstotal
col disk_reads_delta format 9999999 heading diskreadsdelta
col rows_processed_total format 9999999 heading rowsprocessedtotal
col rows_processed_delta format 9999999 heading rowsprocesseddelta
col rows_ex format 999999 heading rowsexeccol snap_id format 99999 heading snapid
col ela_ex format 9999999.99 heading elapsedperexecution
col cwt_ex format 9999999.99 heading cwtperexecution
col instance_number format 99 heading inID

select sql_id, plan_hash_value,dba_hist_sqlstat.snap_id,
to_char(dba_hist_snapshot.BEGIN_INTERVAL_TIME,'dd-mm_hh24:mi') snap_beg,dba_hist_sqlstat.instance_number,invalidations_delta,
parse_calls_delta,executions_delta,fetches_delta,buffer_gets_delta,
disk_reads_delta,rows_processed_delta,elapsed_time_delta/1000 elapsed_time_delta,iowait_delta/1000 iowait_delta,clwait_delta/1000 cluster_wait_delta,ccwait_delta/1000 concurrency_wait_delta,optimizer_mode, optimizer_cost,
substr(optimizer_mode,1,3) opt,
case when executions_delta = 0 then NULL
when rows_processed_delta = 0 then NULL
else(rows_processed_delta/executions_delta)end rows_ex,
case when executions_delta = 0 then NULL
when clwait_delta = 0 then NULL
else(clwait_delta/executions_delta)/1000 end cwt_ex,
case when executions_delta = 0 then NULL
when elapsed_time_delta = 0 then NULL
else(elapsed_time_delta/executions_delta)/1000 end ela_ex
from dba_hist_sqlstat, dba_hist_snapshot
where sql_id='XXX'
and plan_hash_value='&PLAN_HASH_VALUE'
and dba_hist_sqlstat.snap_id=dba_hist_snapshot.snap_id
and dba_hist_sqlstat.instance_number=dba_hist_snapshot.instance_number
order by dba_hist_sqlstat.instance_number,dba_hist_sqlstat.snap_id
/

The view DBA_HIST_SQLSTAT displays historical information about SQL statistics. Each statistic is stored in two separate columns:
metric_TOTAL for the total value of the statistic since instance startup.
metic_DELTA for the change in a statistic’s value between BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME that is stored in the DBA_HIST_SNAPSHOT view.

You can also query DBA_HIST_SQL_PLAN to compare the execution plans, if PLAN_HASH_VALUE has changed .

Delete archive logs from the standby server

2008-12-05T08:47:00.009+02:00

I implemented the following script to delete archive logs from a standby server.
You cannot just delete the archive log files because that will leave entries in the V$ARCHIVED_LOG table.


#!/bin/ksh
#**************************************************************
# Here is where we loop through each SID in /etc/oratab . . .
#**************************************************************
cat /etc/oratab grep '^c.*:.*:[Yy]$' {
while IFS=":" read ORACLE_SID ORACLE_HOME junk
do
PATH=${ORACLE_HOME}/bin:$PATH
export ORACLE_SID ORACLE_HOME PATH

# Now run the SQL script to generate a rm script
sqlplus -s /nolog <<EOF
connect / as sysdba
set heading off feedback off timing off
spool /usr/users/oracle/cronscripts/logs/rm_arch_${ORACLE_SID}.ksh
select 'rm '||NAME from v\$archived_log 
where REGISTRAR='RFS' 
and APPLIED='YES' 
and DELETED='NO' 
and COMPLETION_TIME < (SYSDATE-1);
spool off
exit
EOF


# Now run the generated rm script
chmod 740 /usr/users/oracle/cronscripts/logs/rm_arch_${ORACLE_SID}.ksh
/usr/users/oracle/cronscripts/logs/rm_arch_${ORACLE_SID}.ksh

# Now use RMAN to update V$ARCHIVED_LOG.DELETED
rman <<EOF
connect target /
crosscheck archivelog all;
delete noprompt expired archivelog all;
EOF

done
}

Notes:
columns V$ARCHIVED_LOG.REGISTRAR and APPLIED
If REGISTRAR='RFS' and APPLIEDis NO, then the log has arrived at the standby but has not yet been applied.
If REGISTRAR='RFS' and APPLIED is YES, the log has arrived and been applied at the standby database.

column V$ARCHIVED_LOG.DELETED
Indicates whether an RMAN DELETE command has physically deleted the archived log file from disk (YES) or not (NO)

RMAN EXPIRED
Removes only files whose status in the repository is EXPIRED.
RMAN marks backups and copies as expired when you run a CROSSCHECK command and the files are absent or inaccessible.
To determine which files are expired, run a LIST EXPIRED command.

RMAN NOPROMPT
Beginning in Oracle9i, RMAN's default behavior is to prompt for confirmation when you run DELETE EXPIRED.
In prior releases, RMAN did not prompt.

Fast Index Creation

2008-12-05T07:51:00.007+02:00

I recently had to create a new index on a fairly large date ranged partitioned table. I tried a few permutations on test, but found the following steps to be the fastest;


01:01:03 SQL> alter session set workarea_size_policy=manual

Session altered.

01:13:44 SQL> alter session set hash_area_size=1073741824

Session altered.

01:13:44 SQL> alter session set sort_area_size=1073741824

Session altered.

01:13:44 SQL> select force_logging from v$database

FOR
---
YES

01:13:44 SQL> alter database no force logging

Database altered.

01:13:46 SQL>create index imei_prod.usage_profile_idx1 on imei_prod.usage_profile(msisdn)
01:13:46 2 nologging
01:13:46 3 compress
01:13:46 4 parallel 16
01:13:46 5 local
01:13:46 6 (
01:13:46 7 partition USAGE_199401 tablespace indx nologging compress,
01:13:46 8 partition USAGE_200106 tablespace indx nologging compress,
01:13:46 9 partition USAGE_200202 tablespace indx nologging compress,
01:13:46 10 partition USAGE_200207 tablespace indx nologging compress,
...
01:13:46 56 partition USAGE_200809 tablespace indx nologging compress,
01:13:46 57 partition USAGE_200810 tablespace indx nologging compress,
01:13:46 58 partition USAGE_200811 tablespace indx nologging compress
01:13:46 59 )
01:13:46 60 /

Index created

Elapsed: 01:58:57.99


03:12:44 SQL> SELECT st.sid, se.username, TO_CHAR(se.logon_time,'dd-mon-yy hh24:mi')
03:12:44 2 logtime, se.program, (value/1048576) VALUE
03:12:44 3 FROM gv$sesstat st, gv$statname sn, gv$session se
03:12:44 4 WHERE username = 'MEYERM'
03:12:44 5 and sn.name = 'redo size'
03:12:44 6 AND sn.statistic# = st.statistic#
03:12:44 7 AND st.sid = se.sid
03:12:44 8 /


Redo
Generated
SID USERNAME Logon Time PROGRAM in MB
--------------------------------------------------------------
146 MEYERM 26-nov-08 01:13 sqlplus@pxxxx1 (TNS V1-V3) 116.583

03:12:46 SQL> alter database force logging

Database altered.

03:12:46 SQL> alter index imei_prod.usage_profile_idx1 logging;

Index altered.

Drop an unmounted ASM diskgroup

2008-11-19T13:06:00.007+02:00

We are getting our hands dirty with ASM and will be moving all our databases from RAW devices to ASM in the next couple of months. My worst fear came to be when a Unix Sys Admin used a disk already allocated to ASM for a local filesystem (luckily this did not happen on a production server!).
When ASM realised this is immediatly issued a ALTER DISKGROUP PCASDG1 DISMOUNT FORCE command.
By the time the Unix Sys Admin returned the disk it was unrecognozable by ASM as the disk headers had been overwritten. The diskgroup was unrecoverable and unusable.


SQL> select GROUP_NUMBER, NAME, state from v$asm_diskgroup;
GROUP_NUMBER NAME STATE
------------ --------- ----------
0 PCASDG1 DISMOUNTED
2 PCASDGF MOUNTED
3 PCASDGR MOUNTED
3 rows selected.

SQL>alter diskgroup PCASDG1 mount;
alter diskgroup PCASDG1 mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "19" is missing

SQL>drop diskgroup PCASDG1 including contents;
drop diskgroup PCASDG1 including contents
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15001: diskgroup "PCASDG1" does not exist or is not mounted

SQL>alter diskgroup PCASDG1 check;
alter diskgroup PCASDG1 check
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15001: diskgroup "PCASDG1" does not exist or is not mounted

I had to recreate the whole diskgroup from scratch. I followed Metalink Note 387103.1 to recreate the diskgroup.
Basically you clean up the asm disk header of all the disks in the diskgroup. This will also remove the whole diskgroup from asm.


SQL> select 'dd if=/dev/zero of='PATH' bs=8192 count=100' from v$asm_disk where GROUP_NUMBER=0;

$dd if=/dev/zero of=/dev/oracle/dg/c11t15d2 bs=8192 count=100
100+0 records in
100+0 records out

$dd if=/dev/zero of=/dev/oracle/dg/c11t7d0 bs=8192 count=100
100+0 records in
100+0 records out
etc ... 

SQL> select GROUP_NUMBER, NAME, state from v$asm_diskgroup;
GROUP_NUMBER NAME STATE
------------ --------- ----------
2 PCASDGF MOUNTED
3 PCASDGR MOUNTED
2 rows selected.

The diskgroup is now removed. I then used DBCA to recreate the ASM diskgroup, all these disks showed up as Candidate disks.

Oracle Data Guard Presentation

2008-11-10T11:22:00.008+02:00

I had the privilege to present at the SAOUG (South African Oracle User Group) in Sun City last month. The conference was well organized, the location was great and we had close to 1000 attendees. I presented on Oracle Data Guard.

Thanks to Karen Morton (one of my favorite bloggers) for making me aware of SlideShare. To access my presentation, just click on the slideshare image on the right side and it will take you to my presentation on slideshare. Happy viewing!

Easy Streams Cookbook Example

2008-10-30T09:29:00.008+02:00

We have Call Centres all over the country. The Call Centre’s IVR (interactive voice response, is a phone technology that allows a computer to detect voice and touch tones using a normal phone call) is used for routing calls to the correct call centre. The subscriber data is stored in 1 location and I was asked to replicate that data to 2 databases in other parts of the country. The main reasons are to have the data closer to the IVR system and also to relieve the main production system from all these queries.

Decisions: Use one-directional replication. Capture the changes at the source. Do the instantiation (first copy of data) with DataPump.

Preperation Steps

Enable Supplemental Logging (at source)
Use columns in PK constraints for Supplemental log group

ALTER TABLE "SCHEMA_ABC"."TABLE_ABC" ADD SUPPLEMENTAL LOG GROUP ivr_rep (XXMSISDN) ALWAYS;

This step might wait on “enq: TX – contention” on a busy production system, so it better to do it with a Change Control after hours

To confirm Supplemental Log Groups:


COLUMN LOG_GROUP_NAME HEADING 'Log Group' FORMAT A20
COLUMN TABLE_NAME HEADING 'Table' FORMAT A15
COLUMN ALWAYS HEADING 'Conditional or|Unconditional' FORMAT A14
COLUMN LOG_GROUP_TYPE HEADING 'Type of Log Group' FORMAT A20
SELECT LOG_GROUP_NAME, TABLE_NAME, DECODE(ALWAYS,'ALWAYS', 'Unconditional','CONDITIONAL', 'Conditional') ALWAYS, LOG_GROUP_TYPE
FROM DBA_LOG_GROUPS;

Create default tablespace for the Streams Administrator Account to store the queue table. Do these steps on the source and destinations.

CREATE TABLESPACE streams_tbs DATAFILE '/data/oradata/lfivrdb/streams_tbs.dbf' SIZE 7168M;

CREATE USER strmadmin IDENTIFIED BY STRMXXX 
  PROFILE VODA_COMPLIANT
  DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;
GRANT CONNECT, RESOURCE, DBA to STRMADMIN;
GRANT SELECT ANY DICTIONARY TO STRMADMIN;
GRANT SELECT_CATALOG_ROLE TO STRMADMIN;
EXEC DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');

Set specific initialization parameters at the databases participating in the Streams setup

ALTER SYSTEM SET STREAMS_POOL_SIZE=200M SCOPE=BOTH;
ALTER SYSTEM SET AQ_TM_PROCESSES = 1 scope=both;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 scope=both;
ALTER SYSTEM SET “_job_queue_interval”=1 scope=spfile;

(This is not a dynamic parameter and the db will need to be bounced)

Create 2 database links on the source to the 2 destinations.

CONNECT STRMADMIN/STRMXXX
CREATE DATABASE LINK LFIVRDB.VODACOM.CO.ZA CONNECT TO STRMADMIN IDENTIFIED BY STRMXXX USING ‘LFIVRDB.VODACOM.CO.ZA’;
CREATE DATABASE LINK BEIVRDB.VODACOM.CO.ZA CONNECT TO STRMADMIN IDENTIFIED BY STRMXXX USING ‘BEIVRDB.VODACOM.CO.ZA’;

STEP 1
Create Streams Queues
One Capture on the source db and one Apply on each destination:

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE STREAMS_TBS',
queue_name => ' STREAMS_CAPTURE_Q', 
queue_user => 'STRMADMIN');
END;
/

And on both dest sites:

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE STREAMS_TBS',
queue_name => ' STREAMS_APPLY_Q', 
queue_user => 'STRMADMIN');
END;
/

STEP2
Create Capture Process on Source.
This step might wait on “enq: TX – contention” on a busy production system, so it better to do it with a Change Control after hours

connect STRMADMIN/STRMXXX
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name         => '"SCHEMA_ABC"."TABLE_ABC"',
    streams_type       => 'CAPTURE',
    streams_name       => '"STREAMS_CAPTURE"',
    queue_name         => '"STRMADMIN"."STREAMS_CAPTURE_Q"',
    include_dml        => true,
    include_ddl        => true,
    source_database    => 'DESTPRD.VODACOM.CO.ZA');
    END;
/

STEP3
Create 2 propagation process on the Source (one per destination)

connect STRMADMIN/STRMXXX
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name          => '"SCHEMA_ABC"."TABLE_ABC"',
    streams_name        => '"STREAMS_PROP_LF"',
    source_queue_name   => '"STRMADMIN"."STREAMS_CAPTURE_Q"',
    destination_queue_name  => '"STRMADMIN"."STREAMS_APPLY_Q"@LFIVRDB.VODACOM.CO.ZA',
    include_dml         => true,
    include_ddl         => true,
    source_database    => 'DESTPRD.VODACOM.CO.ZA',
    inclusion_rule      => true);
END;
/

And to the 2nd dest site:

connect STRMADMIN/STRMXXX
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name          => '"SCHEMA_ABC"."TABLE_ABC"',
    streams_name        => '"STREAMS_PROP_BE"',
    source_queue_name   => '"STRMADMIN"."STREAMS_CAPTURE_Q"',
    destination_queue_name  => '"STRMADMIN"."STREAMS_APPLY_Q"@BEIVRDB.VODACOM.CO.ZA',
    include_dml         => true,
    include_ddl         => true,
    source_database    => 'DESTPRD.VODACOM.CO.ZA',
    inclusion_rule      => true);
END;
/

STEP 4
Create 2 Apply processes. One on each destination.

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name          => '"SCHEMA_ABC"."TABLE_ABC"',
streams_type => 'APPLY', 
streams_name => 'STREAMS_APPLY', 
queue_name => 'STRMADMIN.STREAMS_APPLY_Q',
include_dml => true, 
include_ddl => true, 
source_database    => 'DESTPRD.VODACOM.CO.ZA');
END;
/

And 2nd site

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name          => '"SCHEMA_ABC"."TABLE_ABC"',
streams_type => 'APPLY', 
streams_name => 'STREAMS_APPLY', 
queue_name => 'STRMADMIN.STREAMS_APPLY_Q',
include_dml => true, 
include_ddl => true, 
source_database    => 'DESTPRD.VODACOM.CO.ZA');
END;
/

Instantiation. This is the initial copy of the table.

Set & Get SCN:

EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name =>  'SCHEMA_ABC.TABLE_ABC');

This step might wait on “enq: TX – contention” on a busy production system, so it better to do it with a Change Control after hours

To confirm the SCN number that you will need in a future step:

col scn for 9999999999999999
select * from dba_capture_prepared_tables

Export table at source

expdp strmadmin/STRMXXX TABLES= SCHEMA_ABC.TABLE_ABC DUMPFILE=a%u.dmp parallel=8

compress and ftp dump files to the 2 destination servers

Import table at each destination
First create the schema owner of the table

create user SCHEMA_ABC identified by STRMXXX default tablespace streams_tbs;
grant connect, resource to SCHEMA_ABC
/

impdp strmadmin/STRMXXX TABLES=SCHEMA_ABC.TABLE_ABC DUMPFILE=a%u.dmp remap_tablespace=TBS_ABC:streams_tbs

Manually instantiating the objects at each destination

connect STRMADMIN/STRMXXX
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCHEMA_ABC.TABLE_ABC',
source_database_name => 'DESTPRD.VODACOM.CO.ZA',
instantiation_scn => &iscn);
END;
/
Enter value for iscn:

STEP5
Finally start the Process:
On source

CONNECT strmadmin/STRMXXX
exec dbms_capture_adm.start_capture(capture_name => 'STREAMS_CAPTURE');

At 1st destination:

CONNECT strmadmin/STRMXXX
exec dbms_apply_adm.start_apply(apply_name => 'STREAMS_APPLY');

At 2nd destination:

CONNECT strmadmin/STRMXXX
exec dbms_apply_adm.start_apply(apply_name => 'STREAMS_APPLY');

Post Installation steps
On Source force Stream checkpoint (see Metalink Note:342342.1)

EXEC DBMS_CAPTURE_ADM.SET_PARAMETER('STREAMS_CAPTURE','_CHECKPOINT_FORCE','Y');

On each destination

EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'STREAMS_APPLY', parameter => 'WRITE_ALERT_LOG', value => 'Y' );
EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'STREAMS_APPLY',parameter => 'DISABLE_ON_ERROR',value => 'N' );

SQL*Loader direct

2008-09-15T15:47:00.003+02:00

We had to setup SQL Server Replication to an Oracle database. The 2 tables in questions are huge. The small table has 63mill rows and the big one has 1.5bill rows. The big table has 50 date ranged partitions.

I used SQL Loader direct path without any indexes in place from csv files to do the initial load.

The first thing to do was to prepare the database for Direct Loading by executing the script $ORACLE_HOME/rdbms/admin/catldr.sql.

I also had to set my session’s DATE format to be the same as the format in the CSV file.

$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

To get the fastest possible load performance on the 50 partitions I followed these rules:
There were no indexes on the table
The table was set to NOLOGGING
The database was put in NOARCHIVELOG mode

And the following SQL Loader options were used:
DIRECT=TRUE to invoke Direct Loading. This will effectively bypass most of the RDBMS processing
UNRECOVERABLE to turn off database logging
PARELLEL=TRUE and APPEND in order to run multiple load jobs concurrently for the different partitions.

My Korn Shell Script looked like this:


#!/bin/ksh

export ORAENV_ASK=NO;
export ORACLE_SID=xxprd;
. oraenv;

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS".000"'

sqlldr xx/xx control=c1.ctl DIRECT=TRUE PARALLEL=TRUE log=c1.log &
sqlldr xx/xx control=c2.ctl DIRECT=TRUE PARALLEL=TRUE log=c2.log &
sqlldr xx/xx control=c3.ctl DIRECT=TRUE PARALLEL=TRUE log=c3.log &

… etc up to 10 jobs

And I created 10 control files they all looked identical except for the infile:


UNRECOVERABLE LOAD DATA
 infile '/dump/exports/XXprd/x200512.csv'
 append into table usage_profile
 fields terminated by "," TRAILING NULLCOLS
 (MONTH_ID, MSISDN, IMSI, IMEI, START_DT, END_DT, EVENT_CNT, STREAM, SUCCESS_CALLS, UNSUCCESS, DURATION, VOLUME_UPLINK, VOLUME_DOWNLINK, VOLUME, SUBSCRIBER_TYPE, SP_CODE)

The job completed in 12 minutes and 5 jobs later all 1.5 billion records were loaded!

Moving Online Redo Log Files

2008-09-03T10:40:00.010+02:00

We found a lot of sessions waiting for Log File Sync and in turn found that LGWR was waiting for Log File Parallel Write.

We have 2 dedicated disks for Online redo logs. They are multiplexed, with members of the same group on different disks. When a Log Switch occurs 1 member of the Redo Logs being Archived gets READS from the ARCH process while both members of the CURRENT group are receiving WRITE requests from LGWR. We decided to add 2 more disks and split the even numbered groups from the uneven numbered groups. So that we can separate the READS and WRITES.

Current layout:


SQL>select GROUP#, STATUS, TYPE, MEMBER from v$logfile;

G# STATUS TYPE MEMBER
-------------------------
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1b
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2b
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3b
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4b
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5b
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6b

SQL> select GROUP#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS,FIRST_TIME from v$log;

G# S# BYTES MEMBERS ARC STATUS FIRST_TIME
------------------------------------------
1 45181 2147483648 2 YES INACTIVE 03-SEP-2008 08:00:18
2 45182 2147483648 2 YES INACTIVE 03-SEP-2008 08:45:28
3 45183 2147483648 2 NO CURRENT 03-SEP-2008 09:00:47
4 45178 2147483648 2 YES INACTIVE 03-SEP-2008 06:25:02
5 45179 2147483648 2 YES INACTIVE 03-SEP-2008 07:02:21
6 45180 2147483648 2 YES INACTIVE 03-SEP-2008 07:48:50

We use RAW devices, so after the Unix Admins created the new RAW volumes on the new disks I simply added the members:

Creating Redo Log Members for the even groups on the 2 new disks:


ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2c' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2d' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4c' TO GROUP 4;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4d' TO GROUP 4;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6c' TO GROUP 6;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6d' TO GROUP 6;

The status of the new log member is shown as INVALID. This is normal and it will change to active (blank) when it is first used.


SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile order by group#, member;

G STATUS TYPE MEMBER
-----------------------------------------
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1b
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2b
2 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO2c
2 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO2d
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3b
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4b
4 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO4c
4 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO4d
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5b
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6b
6 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO6c
6 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO6d

SQL> select GROUP#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS,FIRST_TIME from v$log; 

G# S# BYTES MEMBERS ARC STATUS FIRST_TIME 
------------------------------------------------
1 45181 2147483648 2 YES ACTIVE 03-SEP-2008 09:24:18
2 45182 2147483648 4 YES ACTIVE 03-SEP-2008 09:27:28
3 45183 2147483648 2 NO CURRENT 03-SEP-2008 09:30:47
4 45178 2147483648 4 YES INACTIVE 03-SEP-2008 09:17:02
5 45179 2147483648 2 YES INACTIVE 03-SEP-2008 09:19:21
6 45180 2147483648 4 YES INACTIVE 03-SEP-2008 09:22:50

Before dropping Redo Log Members you should be aware of the following rules:
You can drop a redo log member only if it is not part of an active or current group.
Make sure the group to which a redo log member belongs is archived before dropping the member.
If the member you want to drop is the last valid member of the group, you cannot drop the member until the other members become valid.
So after a few log switches (ALTER SYSTEM SWITCH LOGFILE) I was ready to drop the old members.
Note that V$LOG.STATUS show ACTIVE for group 1 and 2. This means the Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived. This was the result of all the log switches I did. Once their STATUS changed to INACTIVE I continued dropping the old member.


ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2b';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4b';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6b';

Using various tools to solve a problem

2008-07-08T13:51:00.007+02:00

I was recently called in to take over a performance problem. I quickly determined that only a portion of the database was affected, but it was the most visible part, affecting the business ability to offer a certain feature to millions of our customers. Some queries that should take less than 5 seconds suddenly took minutes to complete.

Of course the App Vendor claimed that nothing changed around that particular query in years, so it had to be a database problem.

Looking at OEM’s Average Active Sessions graph on the Performance tab, I could clearly see a huge red stripe (red means the wait is Application related). Drilling down I found waits for an event called “enq: UL – contention” to be the problem. A metalink search retuned no hits and a google search retuned hundreds of forum websites with DBA’s asking “What is enq: UL – contention?”, most of them did not have any replies. The only reference to this event in the manual was found in Oracle® Database Reference 10g Release 2 (10.2) (B14237-01) Appendix E: Oracle Enqueue Names . It simply stated “UL, User-defined Locks”. To me that meant that the app could be using DBMS_LOCK, but the Vendor said they don’t and app support told me that these queries do not write to the database.

I queried V$SESSION for sessions waiting for EVENT=”enq: UL – contention” to find the SID of the BLOCKING_SESSION, but the BLOCKING_SESSION kept on changing. One blocking session would have confirmed an App issue and the short term solution would have been to kill that process.

Then I logged a priority 1 SR and was asked to provide system state dumps. Eventually BUG 4367986 was identified as a possibility. The patch was applied, but it made no difference!

OK, I clearly had to try something else, so I decided to trace 1 session and go through the TKPROF output. I found that the app related SQL statements all executed in less than 5 second, but one statement stood out at 60 seconds elapsed time. That SQL statement was;

begin ctxsys.drvdml.com_sync_index(:idxname, :idxmem, :partname); end;

Schema ctxsys means Oracle Text is in use and the Vendor then confirmed that Oracle Text indexes where build on an app related audit table almost 2 months ago. The TKPROF also showed INSERTs into the app audit table, so we were finally on the right track.

This information was uploaded in the SR and I had to check the SYNC intervals via a select from dr$index and confirmed that the 2 audit indexes had SYNC ON COMMIT. We changed the COMMIT interval to MANUAL , just to proof if this problem is related to the SYNC ON COMMIT. Once we changed the commit interval to MANUAL, the wait event disappeared.

exec CTX_DDL.REPLACE_INDEX_METADATA('XX','REPLACE METADATA SYNC (MANUAL)');

The SR engineer then supplied the following script to change the SYNC to every 5 minutes.

exec CTX_DDL.REPLACE_INDEX_METADATA('XX','REPLACE METADATA SYNC (every "SYSDATE+5/1440")');

So the “moral of the story” is that as a DBA you have more than one tool to solve a problem and you should use as many of them as possible until the root cause of the problem is clear.