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)

ORA-01114 ( ORA-27072 or ORA-27069)

ORA-01114: IO error writing block to file 1501 (block # 579517) :
ORA-27072: skgfdisp: I/O error :Linux Error: 25: Inappropriate ioctl for device :
Additional information: 579516 :
Or
ORA-01114: IO error writing block to file 1501 (block # 579517) :
ORA-27069 : skgfdisp: attempt to do I/O beyond the range of the file
Additional information: 16385
At the first attempt, quickly I had search for the file_id in the dba_data_files and could not find and tried with the block#, no luck.
We may have to try finding file_id parameter from dba_temp_files, you will find the id there. Temp file_id start from next value from db_files parameter.
Now you are clear with the culprit.
There are two causes, either you need to add some space to resolve the issue or you have enough space to expand and still you are getting the same issue, please add a temp tablespace , make that as default tablespace and drop old temp tablespace.

Illustrations :
SQL> show parameter db_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 1500

Here you will see the temp file_id starg from the 1501 and so on. Temp file_id numbering start from

SQL> select file_name,tablespace_name,file_id from dba_temp_files;

FILE_NAME TABLESPACE_NAME FILE_ID
-------------------------------------------------------------------------------------------------------------- ----------


/u02/oradata/TESTDB/temp01.db TEMP2 1501


SQL> CREATE TEMPORARY TABLESPACE temp2
2 TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE
3 AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.


SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE temp
2 TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
3 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.


SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

nohup and running a process in background....

There are scenarios that we encounter that we run a job that will take more time to complete than our session time out.

Eg. We run an export for database which run for 6 hrs where are our ssh session will have a time out of 30 or 45, once the job in process and no input from keyboard to our ssh session then the session idle timeout count start. Once the idle time out happen our session and the job running in the session gets terminated.

There are three ways that we can overcome this situation.

1. Hard core just press enter in our ssh ( putty window) though it make no sence just to keep session active.
2. Run job in background using & option.
3. Use “ nohup option”

Option 2:

Eg.
# exp sys@dbname file=exp_dbname_date.exp log=exp_dbname_date.log full=y &

This will send the export process to back ground but, export process get terminated the moment the session terminates.

You can use the below script to keep your session alive with a simple while loop to run infinite loop.

#while true
>do
>echo " Exp going on"
>sleep 200
>done
Here you will not get prompt till you press Ctl + c (^c). This process will keep your session alive till you press enter.


Option 3:

Using nohup option

Eg.
# nohup exp sys@dbname file=exp_dbname_date.exp log=exp_dbname_date.log full=y &

or

# vi exp_script.sh
exp sys@dbname file=exp_dbname_date.exp log=exp_dbname_date.log full=y
:wq

# ls –l
Check the permissions if that has execute permissions
#chomod 755 exp_script.sh

# nohup ./exp_script.sh &

Here even when your session terminates job run in background.