Oracle DBA Tips and Techniques

By Arjun Raja

Archive for July, 2009

Drop and Recreate Online Redolog Files

Posted by Arjun Raja on July 29, 2009

Method to drop and recreate online redolog files with 2 members to each group.

Firstly ORACLE will never allow you to drop the current ONLINE redolog file

Ex :

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS
———- ———- —————-
1 1 CURRENT
2 1 UNUSED
3 1 INACTIVE
4 1 INACTIVE

SQL> alter database drop logfile group 1;

alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance test (thread 1) – cannot drop
ORA-00312: online log 1 thread 1: ‘/u03/oradata/testdb/redo01.log’

Now to drop and recreate online redolog files in the same directory –

SQL> select  member from v$logfile;

MEMBER
——————————————————————————–
/u50/oradata/test/redo4a.log
/u51/oradata/test/redo4b.log
/u50/oradata/test/redo3a.log
/u51/oradata/test/redo3b.log
/u50/oradata/test/redo2a.log
/u51/oradata/test/redo2b.log
/u50/oradata/test/redo1a.log
/u51/oradata/test/redo1b.log

8 rows selected.

Intention is to drop and recreate these online logs with a different size.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS
———- ———- —————-
1 1 INACTIVE
2 1 INACTIVE
3 1 CURRENT
4 1 INACTIVE

Begin dropping the redolog groups with STATUS=’INACTIVE’ – As mentioned earlier Oracle will not allow you to drop a current online redolog file.

sql > alter database drop logfile group 1;

cd /u50/oradata/test

rm redo1a.log

cd /u51/oradata/test

rm redo1b.log

Recreate group with both members –

sql > alter database add logfile group 1('/u50/oradata/test/log1a.ora','/u51/oradata/test/log1b.ora')size 2048m;

Do the same for the other three groups –

To change the status of a log group from CURRENT to INACTIVE, simply switch a logfile with this command-

sql> alter system switch logfile;

Run the command 2 or 3 times if the group status does not change.

To check status of group remember the command is

sql > select GROUP#,THREAD#,STATUS from v$log;

Also make sure the logfile is physically removed from the disk with the rm command – if you don’t do this and try to recreate a logfile an error will appear similar to this –

Ex: You dropped log group 3 but did not remove the physical file redo03.log from disk /u03/oradata/test and tried to recreate the group.

SQL> alter database add logfile group3 '/u03/oradata/test/redo03.log' size 50m;

alter database add logfile ‘/u03/oradata/test/redo03.log’ size 50m
*
ERROR at line 1:
ORA-01577: cannot add log file ‘/u03/oradata/test/redo03.log’ – file already
part of database

Posted in Administration | Tagged: , , | Leave a Comment »

Script – Check RMAN Backup Status

Posted by Arjun Raja on July 28, 2009

Scripts to check backup status and timings of database backups –

This script will be run in the database, not the catalog.

Login as sysdba –

This script will report on all backups – full, incremental and archivelog backups –

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

This script will report all on full and incremental backups, not archivelog backups –

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
where input_type='DB INCR'
order by session_key;

Posted in Administration, Backup and Recovery, Scripts | Tagged: , , | Leave a Comment »

Troubleshooting Grid Control Agent issues with startup

Posted by Arjun Raja on July 28, 2009

Sometimes the GRID CONTROL agent will not start because an old HTTP process is still running on the host – this usually happens if the agent has crashed for any reason.

Example –

Check status of agent.

oracle(DATABASE)@hostname:./emctl status agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
—————————————————————
Agent is Not Running

START AGENT –

oracle(DATABASE)@fhostname:./emctl start agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent …… failed.
Failed to start HTTP listener.
Consult the log files in: /u01/oracle/agent10g/sysman/log

To solve the problem.

CHECK IF PORT 3872 – PORT USED BY AGENT IS IN USE.

oracle(DATABASE)@hostname:netstat -an | grep 3872
tcp4 0 0 *.3872 *.* LISTEN

oracle(DATABASE)@hostname:ps -ef | grep emagent
oracle 864486 1 0 Jan 27 – 5:36 /u01/oracle/agent10g/perl/bin/perl /u01/oracle/agent10g/bin/emwd.pl agent /u01/oracle/agent10g/sysman/log/emagent.nohup
oracle 1593344 864486 0 Jan 27 – 97:38 /u01/oracle/agent10g/bin/emagent

KILL ALL EMAGENT PROCESSES STILL RUNNING –

oracle(DATABASE)@hostname:ps -ef | grep emagent | awk ‘ {print $2}’ | xargs kill -9

oracle(DATABASE)@hostname:ps -ef | grep emagent| grep -v grep

No emagent process running now.

cd $AGENT_HOME/bin

oracle(DATABASE)@hostname:pwd
/u01/oracle/agent10g/bin

START AGENT

oracle(DATABASE)@hostname:./emctl start agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.

AGENT WILL START.

Posted in EM Grid Control | Tagged: , | Leave a Comment »

Alert Log Test Message

Posted by Arjun Raja on July 28, 2009

FORCE MESSAGE TO BE WRITTEN TO THE ALERT LOG

If you wish to force an alert to be written to the alert log- for example ORA-600 to test the functioning of a monitoring system you can use this command to force an ORA-600 alert to be written to the alert log.

Login as sysdba

exec dbms_system.ksdwrt(2,’ORA-00600: This is a test error message for monitoring and can be ignored.’);

To check –

cd $BDUMP

view alert_SID.log and you will find the alert written to the log.

Tue Jul 28 10:06:28 2009 ORA-00600: ” This is a test error message for monitoring and can be ignored “

Posted in Administration | Tagged: , , | Leave a Comment »

RMAN Validate Backup

Posted by Arjun Raja on July 23, 2009

The validate command for backup and restore is useful to confirm that backup and restoration is posssible and valid without actually backing up or restoring datafiles.

We can validate the restore of a spfile,controlfile,archivelog or even the whole database backup.

The following example enables us to specify a point in time upto which we need to test the validity of a backup.

RMAN> run {
2> set until time “to_date(’23-JUL-2009 17:00:00′,’DD-MON-YYYY HH24:MI:SS’)”;
3> restore database validate;4> }

executing command: SET until clause
using target database control file instead of recovery catalog

Starting restore at 23-JUL-09
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=159 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK

channel ORA_SBT_TAPE_1: starting validation of datafile backupset
channel ORA_SBT_TAPE_1: reading from backup piece 1pkkbkop_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=1pkkbkop_1_1 tag=TAG20090717T085801
channel ORA_SBT_TAPE_1: validation complete, elapsed time: 00:04:27
Finished restore at 23-JUL-09

We can validate spfile and controlfile backups as well as shown below

RMAN> restore validate spfile;
Starting restore at 23-JUL-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /u02/oradata/testdb/TESTDB/autobackup/2009_07_22/o1_mf_s_692887841_56f6o2jr_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/testdb/TESTDB/autobackup/2009_07_22/o1_mf_s_692887841_56f6o2jr_.bkp tag=TAG20090722T125041
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 23-JUL-09

RMAN> restore validate controlfile;

Starting restore at 23-JUL-09
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /u02/oradata/testdb/TESTDB/autobackup/2009_07_22/o1_mf_s_692887841_56f6o2jr_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/oradata/testdb/TESTDB/autobackup/2009_07_22/o1_mf_s_692887841_56f6o2jr_.bkp tag=TAG20090722T125041
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 23-JUL-09

Let us now simulate a case where the restore validate will fail because either a backup or backupset is not available or because a file is not available on the disk itself.

We will rename one of the most recent archivelog files and then attempt a restore validate of archivellogs for the past day.

$ mv arch.355.1.692887607.log arch.355.1.692887607.log.old

RMAN> restore validate archivelog from time ‘sysdate -1’;

Starting restore at 23-JUL-09
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=140 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/23/2009 21:22:21
RMAN-06026: some targets not found – aborting restore
RMAN-06025: no backup of log thread 1 seq 355 lowscn 5192098 found to

This command can be safely run at anytime .

Also worth noting that even if you simply forget to enter validate and type for example –

RMAN > restore database ;

RMAN will never restore on top of an ‘OPEN’ database –

An error similar to the one below will be received if you try to overwrite files of an open database.

ORA-19870: error while restoring backup piece 16kermqo_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 2

Posted in Administration | Tagged: , | Leave a Comment »

Unix – tar and gzip commands

Posted by Arjun Raja on July 23, 2009

tar and compress a bunch of datafiles and then untar and uncompress them

cd /u02/oradata/test– (area of database files )

Now tar and compress and copy the files to backup area – /u02/oradata/test_bkup and name the file as test.tar.gz

tar cvf – * |gzip -c >/u02/oradata/test_bkup/test.tar.gz –

cd /u02/oradata/test_bkup > ls -lrt

-rw-r–r– 1 ofsad1 dba 105952962 Feb 26 11:31 test.tar.gz

Now to untar and uncompress the files back to the original area –

cd /u02/oradata/test

gzip -dc < /u02/oradata/test_bkup/test.tar.gz | tar xvf –

Posted in Unix | Tagged: , , | Leave a Comment »

EM Grid Control target discovery after a clone

Posted by Arjun Raja on July 22, 2009

If a database is cloned or recreated on another machine with the same name, the new database will not be discovered on the GRID.

There is a reason behind this .

Example

Database called test already discovered on GRID – database on ABC machine.

Create or clone a database with the same name test on another machine XYZ.

Try to discover this new database on the GRID using the manual option or the option to monitor via grid control while creating the database.

The new database test will not be discovered on the GRID and cannot be seen in the list of targets on the GRID .

That is because a databse with the same name the test database has also has been discovered and runs from another box.

Only when a manual install was attempted and the name of the instance was issued as test.domain instead of just test that it worked .

As you can see below – the repository ( sysman in emrep database ) has a table called mgmt_targets which maintains these details and does not allow duplicates( so a little tweaking is necessary )

The agent tried to upload test automatically when installed on the new box and start it up- and it failed to do so as this entry exists in the table.

Currently both databases are disovered – run this command in the emrep ( repository database) as sysman.

SQL> select target_name from mgmt_targets where target_name like ‘%TEST%’;

TARGET_NAME
test
test.domain

Posted in EM Grid Control | Tagged: , | 1 Comment »

EM Grid Control Agent Target Discovery

Posted by Arjun Raja on July 22, 2009

Sometimes , you may install the 10g OEM GRID agent before you actually create a new database or you may add a new database to an existing box.

This new database will have to be discovered by GRID CONTROL.

I created a database called test on a box which already had a Grid agent running on it.

This database has to be discovered by the agent and it’s details uploaded to the GRID.

Make sure the ORAINVENTORY location in /etc/oraInst.loc matches the location when you installed the AGENT.

Go to the new AGENT_HOME/bin and issue command agentca- d

test:/u01/oracle/agent10g/bin> ./agentca -d
Stopping the agent using /u01/ofsap/agent10g/bin/emctl stop agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Stopping agent … stopped.
Running agentca using /u01/oracle/agent10g/oui/bin/runConfig.sh
ORACLE_HOME=/u01/oracle/agent10g ACTION=Configure MODE=Perform
RESPONSE_FILE=/u01/oracle/agent10g/response_file RERUN=TRUE
INV_PTR_LOC=/etc/oraInst.loc
COMPONENT_XML={oracle.sysman.top.agent.10_2_0_1_0.xml}
Perform – mode finished for action: Configure
Perform – mode finished for action: Configure

You can see the log file:
/u01/oracle/agent10g/cfgtoollogs/oui/configActions2009-06-18_01-13-37-PM.log
test:/u01/oracle/agent10g/bin>

Output of log –
/u01/oracle/agent10g/cfgtoollogs/oui/configActions2009-06-18_01-13-37-PM.log

The action configuration is performing
——————————————————
The plug-in Agent Configuration Assistant is running

Performing free port detection on host=xxxxxx.test.com
Performing targets discovery and agent configuration
Starting the agent
AgentPlugIn:agent configuration finished with status = true

The plug-in Agent Configuration Assistant has successfully been performed
——————————————————
The action configuration has successfully completed
###################################################

Now login to the database as sys, unlock the dbsnmp user and also change
the password of dbsnmp user – for example to temp.

Login to the GRID , and click on targets – choose the new box and then the
new database and configure the dbsmp user .

Once this is complete, the GRID screen will show both the agent and
database on the targets page.

Posted in EM Grid Control | Tagged: , , , , , | Leave a Comment »

Hello world!

Posted by Arjun Raja on July 16, 2009

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

Posted in High Availability | 1 Comment »

TSM (Tivoli Storage Manager) – Using the dsmc command

Posted by Arjun Raja on July 15, 2009

Many DBA’s have to be familiar with TSM ( Tivoli Storage Manager) commands to backup files to tape or restore backups from tape.

To query backups – for example all compressed files with ‘.Z’ extension.

oracle:/u05/backup/test > dsmc q b -inactive ‘*.Z’

File

/u05/backup/TEST/20090702_0841.test_tables.dmp.Z

To Restore the file above –

dsmc restore -inactive /u05/backup/TEST/20090702_0841.ofsap_tables.dmp.Z

To backup permanently and retain for 2 years.

First query the management classes available –

dsmc q mgmt

Domain Name : XXXUNIX
Activated Policy Set Name : STANDARD
Activation date/time : 05/11/09 13:16:54
Default Mgmt Class Name : STANDARD
Grace Period Backup Retn. : 30 day(s)
Grace Period Archive Retn.: 730 day(s)

dsmc archive -archmc=RETAIN2YEARS -description=”test_remove” “/u05/backup/TEST/test_export.sh” -su=yes > test.log

The log for the output is written to test.log

To query all permanent archived backups under a particular directory –

dsmc query Archive -detail “/u05/backup/TEST/test_export.sh” -su=yes

To retrive the backup above

dsmc retrieve “/u05/backup/TEST/test_export.sh”

Retrieve function invoked.

— User Action is Required —
File /u05/backup/TEST/test_export.sh ‘ exists

Select an appropriate action
1. Replace this object
2. Replace all objects that already exist
3. Skip this object
4. Skip all objects that already exist
A. Abort this operation
Action [1,2,3,4,A] : 1
Action [1,2,3,4,A] : 1
Retrieving 472 /u05/backup/TEST/test_export.sh [Done]

Retrieve processing finished.

Posted in Administration | Tagged: , | Leave a Comment »