In active.. It been a long time ther is no post on the blog

In active.. It been a long time there is no post on the blog

was bit busy.. will keep posting here after..

Skip an archive log or mark an archive log unavailable in RMAN backup

run {
change archivelog from logseq 44937 until logseq 44947 unavailable;
}

run {
change archivelog 44937 unavailable;
}

Rotate listener log.

Rotate listener log.
If the listener log grow beyond our limit size and you want to rotate listener log without restarting listener service we might do something like this.
Go to listener log path.

Eg. Listener log file name like listener.log

cp listener.log listener.log.old; tail -500 listner.log.old > listener.log

- try to maintain this in single line so that the time gap should not be more
Small note :- whenever we try to move the existing file and add touch a new file mean time listener service will fail to Wright the log to listener.log file hence the service might stop. Whenever we move the file and create a new file. The I node number of the file changes and we might need to restart listener as the service will be writing log to the old inode file and that changes now. We might need to restart the service for service to identify the new listener.log file.

Move, copy or delete files of particular date.

Move, copy or delete files of particular date.
Quick and the good one.
Eg. To delete files with a particular date without using find command ( hardcore process)
ls -ltr | grep "May 23" | awk '{print "mv "$9" /disk1/oradata/arch/"}' | more
above will list all the files that are having May 23 date.
ls -ltr | grep "May 23" | awk '{print "mv "$9" /disk1/oradata/arch/"}' | sh –x
This will execute the output. Customize the output and sh –x will execute the output.

RMAN 8i commands

RMAN 8i Most commands
Create Recovery Catalog
First create a user to hold the recovery catalog.
CONNECT sys/password@TSH1

-- Create tablepsace to hold repository

CREATE TABLESPACE "TOOLS" DATAFILE E:\ORACLE\ORADATA\DDBA1\TOOLS01.DBF' SIZE 10M AUTOEXTEND ON NEXT 1024K EXTENT MANAGEMENT LOCAL;

-- Create rman schema owner

CREATE USER rman IDENTIFIED BY rman TEMPORARY TABLESPACE temp DEFAULT TABLESPACE tools QUOTA UNLIMITED ON tools;


GRANT connect, resource, recovery_catalog_owner TO rman;

-- Create the recovery catalog:

c:> rman catalog rman/rman@tsh1
RMAN> create catalog tablespace tools;
-- Register Database
Each database to be backed up by RMAN must be registered:
C:>rman target sys/password@tsh1 rcvcat rman/rman@dba1 msglog 'C:OracleBackupTSH1TSH1_Daily_Backup.log'
RMAN> register database;


-- Cold Backup
rman target sys/password@tsh1 rcvcat rman/rman@dba1
RMAN> run
{
allocate channel ch1 type disk format 'C:\Oracle\Backup\TSH1\%d_DB_%u_%s_%p';
backup database include current controlfile
release channel ch1;
# Open the database and Archive all logfiles including current
alter database open;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
# Backup outdated archlogs and delete them
allocate channel ch1 type disk format 'C:\Oracle\Backup\TSH1\%d_ARCH_%u_%s_%p';
backup archivelog until time 'Sysdate-2' all delete input;
release channel ch1;
# Backup remaining archlogs
allocate channel ch1 type disk format 'C:\Oracle\Backup\TSH1\%d_ARCH_%u_%s_%p';
backup archivelog all;
release channel ch1;
}


--recovery catalog should be resyncronized

RMAN> resync catalog;

-- Hot Backup
Hot backups using RMAN are very simple. There is no need to alter the tablespace or database mode.
run {
allocate channel ch1 type disk format 'd:\oracle\backup%d_DB_%u_%s_%p';
backup database;
backup archivelog all;
release channel ch1;
}

-- Restore & Recover The Whole Database
Recovering from a media failure is as simple as:
run {
startup mount pfile=c:\Oracle\Admin\TSH1\pfile\init.ora;
allocate channel ch1 type disk;
restore database;
recover database;
release channel ch1;
}

-- Restore & Recover A Subset Of The Database
A subset of the database can be restored in a similar fashion:
run {
sql 'ALTER TABLESPACE users OFFLINE IMMEDIATE';
restore tablespace users;
recover tablespace users;
sql 'ALTER TABLESPACE users ONLINE';
}
-- Incomplete Recovery
As you would expect, RMAN allows incomplete recovery to a specified time, SCN or sequence number:
run
{
set until time 'Nov 15 2000 09:00:00';
# set until scn 1000; # alternatively, you can specify SCN
# set until sequence 9923; # alternatively, you can specify log sequence number
restore database;
recover database;
}

alter database open resetlogs;
The incomplete recovery requires the database to be opened using the RESETLOGS option.

-- Lists And Reports
RMAN has extensive listing and reporting functionality allowing you to monitor you backups and maintain the recovery catalog. Here are a few useful commands:
# Show all backup details
list backup;

# Show items that beed 7 days worth of
# archivelogs to recover completely
report need backup days = 7 database;

# Show/Delete items not needed for recovery
report obsolete;
delete obsolete;

# Show/Delete items not needed for point-in-time
# recovery within the last week
report obsolete recovery window of 7 days;
delete obsolete recovery window of 7 days;

# Show/Delete items with more than 2 newer copies available
report obsolete redundancy = 2 device type disk;
delete obsolete redundancy = 2 device type disk;

# Show datafiles that connot currently be recovered
report unrecoverable database;
report unrecoverable tablespace 'USERS';

Using commands with ssh to execute on remote server

Using commands with ssh to execute on remote server.

If you want to execute a command on a remote server we need not login to server but we can execute the command directly from the command prompt only.
Eg. If we want to check corntab entries of few servers we canuse contab –l from the command prompt only.

ssh servername crontab –l

If we want to execute more than one command from command prompt we can do as below with “;” as command .

ssh servername “hostname ; crontab –l | grep /u01/app/oracle “

Script to connect all the database on a server and run a SQL Script

Script to connect all the database on a server and run a SQL Script
!#!/bin/sh
for SID in `ps -ef grep pmon grep -v grep awk -F_ '{print $3}' sort `
do
ORACLE_SID=$SID
export ORACLE_SID
ORACLE_HOME=`grep $SID /var/opt/oracle/oratab awk -F: '{print $2}'`
export ORACLE_HOME
sqlplus "/as sysdba" << EOF
select name from v\$database;
EOF
Done
Please run this script only for any select operations, any updates or modifications in the SQL script It is suggested do them manually.
Note :- The above script check for oratab file in /var/opt/oracle/ path, if you want to run the script in LINUX or any other UNIX OS please change the oratab location to /etc/oratab
Include your script in place of “select name from v$databae” ( between the two EOFs)