Script to analyze a particular schema in the DB

Script to analyze schema

begin
for x in (select table_name from dba_tables where owner='<SCHEMA_NAME>')
loop
dbms_output.put_line('TABLE : ' || x.table_name);
begin
dbms_stats.gather_table_stats(ownname => '<SCHEMA_NAME>',tabname =>
x.table_name, cascade => TRUE, estimate_percent => 35);
exception
when others then
dbms_output.put_line('### ERROR: ' || sqlerrm);
end;
end loop;
end;
/

Script to generate Analyze table script

select 'Analyze table <SCHEMA_NAME>.'|| table_name || ' estimate statistics;' from
dba_tables where owner = 'OWNER_NAME'

How to recover missing datafile from RMAN utility?

How to recover missing datafile from RMAN utility?

How to recreate a datafile that is missing at the operating system level. Missing/inaccessible files may be reported with one or more of these errors:

ORA-01116: error in opening database file %s
ORA-27041: unable to open file
ORA-01157: cannot identify/lock data file %s - see DBWR trace file
ORA-01119: error in creating database file '%s'

No backup or copy of the datafile is required. We only need the redo logs starting from the time of the datafile creation to the current point in time.

When a datafile goes missing at the operating system level, you would normally need to restore and recover it from a backup. If you do not have backups of this datafile, but do have redo logs you can still able to create and recover the datafile. You only need the redo logs starting from the datafile creation time to now.

Prior to 10g, you would use the following SQL command:

SQL> alter database create datafile 'missing name' as 'misisng name';
SQL> recover datafile 'missing name';
SQL> alter database datafile '<missing name>' online;

As of 10g, you can also do this in RMAN.

1) RMAN will create the datafile if there is no backups or copies of this datafile:

 

RMAN> restore datafile <missing file id>;

2) Recover the newly created datafile:

      RMAN> recover datafile <missing file id>;

3) Bring it online:

      RMAN> sql 'alter database datafile <missing file id> online';

RMAN> list backup of datafile 6;
specification does not match any backup in the repository
RMAN> restore datafile 6;
RMAN> recover datafile 6;
RMAN> sql 'alter database datafile 6 online';

Script to monitor active users activities in the Oracle DB

set pagesize 66
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "SID"
col b3 for 99999 justify left
col b3 heading "SERIAL#"
col sql_text for a35
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
a.sql_text
  from v$sqltext a, v$session b, v$process c
   where a.address    = b.sql_address
   and b.status     = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE
--                                  ACTVE TRANSACTION ON THAT MOMENT */
   and b.paddr      = c.addr
   and a.hash_value = b.sql_hash_value
 order by c.spid,a.hash_value,a.piece
/

RMAN Full Compressed backup and Maintenance in 11g

RMAN Full Compressed backup and Maintenance in 11g

Master batch file calling script

<<Full_Rman.bat>>
set oracle_sid=<Oracle_SID>
set oracle_home=E:\oracle\product\11.1.0\db_1
echo Starting backup on .... %date:~-7,2%%date:~-10,2%%date:~-4,4% >> full_Rman.log
rman target sys/<PASSWORD> @g:\backup\full_Rman.rcv LOG g:\backup\full_Rman.rcv APPEND

Full database script

<<Script Name : full_Rman.rcv>>
-- Run as sys on rman prompt
rman> connect target sys/<PASSWORD>
RUN
{
  ALLOCATE CHANNEL disk1 DEVICE TYPE DISK  FORMAT   'd:\OraBack\b_%U';
  BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG DELETE INPUT;
 }

Maintenance Script

<<Script Name: Maint_Rman.rcv>>
-- Run as sys on rman prompt
rman> connect target sys/<PASSWORD>
RUN
{
  Crosscheck archivelog all;
  delete expired archivelog all;
  crosscheck backup;
  delete expired backup;
  delete obsolete;
}

Configuration for full backup

Login to RMAN with sys user and set the below configuration :-
-- Sample RMAN configuration for full backup with retention period of 3 days
-- Run as sys on rman prompt
rman> connect target sys/<PASSWORD>
RMAN configuration parameters for database with db_unique_name <Oracle_SID> are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2';
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\11.1.0\DB_1\DATABASE\SNCF<ORACLE_SID>.ORA'; # default

Note: Please do not use the scripts on production until you tried on test environment

 

Solaris / Linux Useful Commands - Part 1

Solaris / Linux Useful Commands - Part 1

Find & List Archive files older than 1 day
find ./ -name "*.ARC" -mtime +1 -exec ls -l {} \;


Find and remove Archive files older than 1 day
find ./ -name "*.ARC" -mtime +1 -exec rm {} \;


Find and zip archive files older than 1 day
find ./ -name "*.ARC" -mtime +1 -exec gzip {} \;


Find and move archive files older than 1 day
find ./ -name "*.arch" -mtime +1 -exec mv {} /u01/andy/;

 
Delete the 500 oldest files
rm -f `ls -tr|head -500`

 
Find and grep
find ./ -grep <what> {} \; -print 2>/dev/null


Or...
find ./ -exec grep -l "string" {} \;

 
list all files opened by a process
lsof -p <pid>

 
List and sorted by size
ls -l |sort -k 5

Sum and sort files for size
du -sk *|sort -n

 
cpio unzip syntax
cpio -idmv < <filename>

 
man commands
man -k <what> -displays the chapters containing the specified
man <chapter> <what> -shows the page

 
pipes
mknod <name> p

 
Turn off auto indent and bracket matching in vi
:set noai nosm

 
Capture a control
ctl-v then press the control key (eg. backspace)

Configure ksh environment
To display the current settings
set -o
set -o

To set HP/UX stylee...
set -o vi
 
Show routing tables
netstat -r

 
Check all logs for ORA- errors
grep ^ORA- *log |cut -f2 -d"-"|cut -f1 -d:|awk '{print "ORA-" $1}'|sort -u

#dtterm -bg white -fn <fontset>
Kill all the oracle processes
# ps -fu oracle| awk '{print "kill -9 " $2}' > /tmp/kill.sh

Setting the VT terminal
setenv TERM vt100

Oracle Recovery Methodologies by RMAN in Oracle 9i

RMAN Recovery Methods

When performing a restore operation, it is best to open two telnet sessions, one for SQL commands, and one for RMAN commands.  For the rest of this document, RMAN commands will be prefaced with the RMAN> prompt, and SQL commands will be prefaced with the SQL> prompt.  A $ indicates that the command is executed from the Unix prompt.

Restoring and Recovering All Datafiles

In this scenario, it is assumed that your control files are still accessible. You have a backup, done for example with backup database plus archivelog;

Your first step is to make sure that the target database is shut down:

$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;ORACLE instance shut down.

Next, you need to start up your target database in mount mode. RMAN cannot restore datafiles unless the database is at least in mount mode, because RMAN needs to be able to access the control file to determine which backup sets are necessary to recover the database. If the control file isn't available, you have to recover it first. Issue the STARTUP MOUNT command shown in the following example to mount the database:

SQL> startup mount;
Oracle instance started.         

Database mounted.

Since backup set files are created in an RMAN-specific format, you must use RMAN to restore the datafiles. To use RMAN, connect to the target database:

$ rman target / rcvcat rcvcat/rcvcat@oemprod

The remainder of this example shows how to restore all of the datafiles of the target database. When the restore command is executed, RMAN will automatically go to its last good backup set and restore the datafiles to the state they were in when that backup set was created.

When restoring database files, RMAN reads the datafile header and makes the determination as to whether the file needs to be restored. The recovery is done by allocating a channel for I/O and then issuing the RMAN restore database command.

With Oracle9i and above, you don't need to allocate a channel explicitly. Instead, you can use the default channel mode:

RMAN> restore database;
RMAN> recover database;
SQL> alter database open;

For Oracle8i, the ALLOCATE, RESTORE, and RECOVER commands need to be enclosed by the run{} command:

RMAN> run {
  allocate channel d1 type disk;
  restore database;
  recover database;
}
alter database open;

Once the recovery has been completed, execute a complete RMAN backup to establish a new baseline.

Restoring Specific Tablespaces

In this scenario, it is assumed that your control files are still accessible. You have a backup, done for example with backup database plus archivelog;

Take the tablespace that needs recovery offline, restore the tablespace, recover the tablespace, and bring the tablespace online. If you cannot take the tablespace offline, then shutdown abort the database and restore in mount mode.

First try to take the tablespace offline;

$ sqlplus "/ as sysdba"
SQL> alter tablespace tab offline;

If this works, continue with the RMAN recovery:

$ rman target / rcvcat rcvcat/rcvcat@oemprodRMAN> restore tablespace tab;
RMAN> recover tablespace tab;
SQL> alter tablespace tab online;

If taking the tablespace offline fails, follow these steps:

$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / rcvcat rcvcat/rcvcat@oemprodRMAN> restore tablespace tab;
RMAN> recover tablespace tab;
SQL> alter database open;

Once the recovery has been completed, execute a complete RMAN backup to establish a new baseline.

Restoring Specific Datafiles

In this scenario, it is assumed that your control files are still accessible. You have a backup, done for example with backup database plus archivelog;

Take the datafile that needs recovery offline, restore the datafile, recover the datafile, and bring the datafile online. If you cannot take the datafile offline, then shutdown abort the database and restore in mount mode.

First try to take the datafile offline:

SQL> alter database datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf' offline;

If this works, continue with the RMAN recovery:

$ rman target / rcvcat rcvcat/rcvcat@oemprod

RMAN> restore datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf'
RMAN> recover datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf'

SQL> alter database datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf' online;

If taking the datafile offline fails, follow these steps:

$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
SQL> startup mount;
$ rman target / rcvcat rcvcat/rcvcat@oemprodRMAN> restore datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf';
RMAN> recover datafile '/u01/oracle/db/AKI1/tab/AKI1_tab.dbf';
SQL> alter database open;

Once the recovery has been completed, execute a complete RMAN backup to establish a new baseline.

Restoring Control Files

In this scenario, it is assumed that your control files are backed up. You have a backup, done for example with backup database plus archivelog;

In an ideal world you'll never use RMAN to restore a control file. But if something catastrophic happens, and you lose all control files, here are the steps for getting them back:

The following examples assume that you are using a catalog. First, here's the simplest Oracle9i syntax for restoring a control file:

$ sqlplus “/ as SYSDBA”
SQL> shutdown abort;
SQL> startup nomount;$ rman target / rcvcat rcvcat/rcvcat@oemprod

Set the database id (DBID) with the following command.  This is a 10-digit number that RMAN uses to uniquely identify this database in the recovery log.  The number can be obtained from any previous RMAN backup log file.

RMAN> set dbid = xxxxxxxxxx;

RMAN> restore controlfile;SQL> alter database mount;
SQL> alter database open;

If this fails with ...

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

... then you must perform a recover database:

SQL> shutdown abort;
SQL> startup mount;$ rman target / rcvcat rcvcat/rcvcat@oemprodRMAN> recover database;SQL> alter database open resetlogs;

RMAN> reset database;

Note, that all offline archivelogs are now useless, perform a full back as soon as possible.

Restoring Online Redologs

In this scenario, it is assumed that your control files are backed up. You have a backup, done for example with backup database plus archivelog;

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup nomount;$ rman target / rcvcat rcvcat/rcvcat@oemprod

Set the database id (DBID) with the following command.  This is a 10-digit number that RMAN uses to uniquely identify this database in the recovery log.  The number can be obtained from any previous RMAN backup log file.

RMAN> set dbid = xxxxxxxxxx;RMAN> restore controlfile;SQL> alter database mount;RMAN> restore database;RMAM> recover database;RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/28/2004 11:03:23
RMAN-06054: media recovery requesting unknown log: thread 1 seq 1 lowscn 8448414

Since the online logs were lost, complete recovery is not possible. Open the database with resetlogs to continue.

RMAN> alter database open resetlogs;

-------------------------------------IMPORTANT-------------------------------------------

During this type of recovery, if you receive error messages like this:

RMAN> restore database;

Starting restore at 11-JUL-05

using channel ORA_SBT_TAPE_1

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/11/2005 14:25:22

RMAN-06026: some targets not found - aborting restore

RMAN-06023: no backup or copy of datafile 10 found to restore

RMAN-06023: no backup or copy of datafile 9 found to restore

RMAN-06023: no backup or copy of datafile 8 found to restore

RMAN-06023: no backup or copy of datafile 7 found to restore

RMAN-06023: no backup or copy of datafile 6 found to restore

RMAN-06023: no backup or copy of datafile 5 found to restore

RMAN-06023: no backup or copy of datafile 4 found to restore

RMAN-06023: no backup or copy of datafile 3 found to restore

RMAN-06023: no backup or copy of datafile 2 found to restore

RMAN-06023: no backup or copy of datafile 1 found to restore

…use the following directions to recover (recreate the controlfile):

• With the database mounted, execute ‘alter database backup controlfile to trace resetlogs;’
• Perform a shutdown abort on the database, but remain at the SQL> prompt.
• In another telnet session, go to the udump directory to retrieve the resulting trace file and copy it to another location to edit it.
• Edit the file and add the phrase “until cancel” to the recover database command at the end.  The phrase should read “recover database until cancel using backup controlfile”.  Remove the “alter database open” command after the recover command.  Save the file with a .sql extension.
• Back at the SQL> prompt, execute the modified trace file.  When prompted for an archived log, type in “cancel” and the reply should be “media recovery cancelled”.
• Issue “alter database open resetlogs”.  The database should open after a few moments.
• Connect to the RMAN recovery catalog and issue the “reset database” command.
• Perform a full RMAN backup as soon as possible.

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

Time-Based, Change-Based, or SCN-based Incomplete Recovery

Incomplete recovery uses a backup to produce a non-current version of the database. In other words, you
do not apply all of the redo records generated after the most recent backup.

You usually perform incomplete recovery of the whole database in the following situations:

   Media failure destroys some or all of the online redo logs.

   A user error causes data loss, for example, a user inadvertently drops a table.

   You cannot perform complete recovery because an archived redo log is missing.

   You lose your current control file and must use a backup control file to open the database.

To perform incomplete media recovery, you must restore all datafiles from backups created prior to the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. The RESETLOGS operation creates a new incarnation of the database; in other words, a database with a new stream of log sequence numbers starting with log sequence 1.

NOTE – Start every RMAN incomplete recovery with the following commands:

$ sqlplus "/ as sysdba"
SQL> shutdown abort;
SQL> startup mount;$ rman target / rcvcat rcvcat/rcvcat@oemprod

--For time-based recovery, use these commands:

RMAN> restore database until time "to_date('07/12/05 12:0:00','MM/DD/YY HH24:MI:SS')";RMAN> recover database until time "to_date('07/12/05 12:0:00','MM/DD/YY HH24:MI:SS')";media recovery complete.
SQL> alter database open resetlogs;

--For SCN-based recovery, user these commands:

RMAN> restore database until scn 1000;RMAN> recover database until scn 1000;media recovery complete.
SQL> alter database open resetlogs;

--For change-based recovery, user these commands:

RMAN> restore database until sequence 9923;   --Archived log sequence numberRMAN> recover database until sequence 9923;   --Archived log sequence numbermedia recovery complete.
SQL> alter database open resetlogs;

Once the recovery has been completed, execute the following steps:

• Delete prior backups with this command (from the RMAN prompt):

RMAN> delete force backup;

This command removes all prior backups from the RMAN catalog as they can no longer be used once the database has been restarted with the resletlogs option.  After completing this command, create a new RMAN backup to establish a new baseline.

Recovering Archived Logs only

In the event that you want to recover the database archived redo logs until a desired time, you can use the following commands:

$ rman target / rcvcat rcvcat/rcvcat@oemprodRMAN> restore ARCHIVELOG FROM TIME 'SYSDATE-1' UNTIL TIME 'SYSDATE';

or

RMAN> restore ARCHIVELOG FROM TIME "to_date('07/11/05 00:00:01','MM/DD/YY HH24:MI:SS')

UNTIL TIME 'SYSDATE';

Some Useful Database Commands Part - 1

SOme  Useful Oracle Database Commands Part - 1

Flushing the shared pool

To flush the shared pool use:
    ALTER SYSTEM FLUSH SHARED_POOL;

Flushing the buffer cacheIn Oracle 10.1 and above, the following supported command can be used to flush the buffer cache:
    ALTER SYSTEM FLUSH BUFFER_CACHE;

In Oracle 9.0.1 and above the following unsupported command can also be used to flush the buffer cache:
    ALTER SESSION SET EVENTS 'immediate trace name flush_cache';

After this command has been executed, the STATE column for all rows in X$BH will be zero unless the block is currently pinned
In Oracle 9.2.0.1 (Windows 2000) the equivalent ORADEBUG command fails with ORA-3113
Forcing a checkpoint

To force a checkpoint use
    ALTER SYSTEM CHECKPOINT;

Forcing a logfile switch
To force a logfile switch use

    ALTER SYSTEM SWITCH LOGFILE;