Muhammad Nadeem Chaudhry's Blog


What is the difference between a 32-bit and 64-bit Windows operating system?

Posted in Uncategorized by Muhammad Nadeem Chaudhry on January 27, 2011

Most of you are running Windows XP or Vista in its 32-bit iteration. But as hardware gets cheaper, people are curious as to what the 64-bit operating system has to offer. First let’s see if we can grasp the difference between 32- and 64-bit.

Think of your computer as a series of tubes that can either be 32 or 64 bits wide. When you have the smaller 32-bit size, there is more potential for bottlenecks to occur. Bottlenecks slow down your system because one process has to wait for another to finish before it can begin. But if you want to have 64-bit wide tubes, your computer needs to be thinking in 64-bit so your software and hardware all need to support 64-bit.
If you do not know the difference between 32-bit and 64-bit, I would have told you in the past that you are running a 32-bit version of Windows. But now with Windows 7 I am seeing more and more 64-bit operating systems shipped by default without the end users knowledge. Don’t get me wrong, a 64-bit system is better but you also need to be running 64-bit programs and have a 64-bit processor or else all the trouble of setting up the 64-bit operating system would be worthless.

On a 32-bit operating system, you are restricted to a maximum of 4 gigabytes of RAM. On a 64-bit operating system, you really do not have a limit. Let’s look at Wikipedia and find out the maximum amount of RAM for a 64-bit operating system:

264 addresses, equivalent to approximately 17.2 billion gigabytes, 16.3 million terabytes, or 16 exabytes of RAM.

That is a huge amount of RAM! Normally when you exhaust your physical RAM on a 32-bit system, it has to use virtual memory or hard disk space to pick up the slack. On a 64-bit system, you can install as much RAM as you can to cover your overhead. From here on, 32-bit operating systems will be referred to as x86 and 64 bit operating systems as x64. You can tell what you are running by right clicking on My Computer and choosing Properties.  Below is a shot of a 64-bit machine using 12GB of memory.

And in this shot, we see a 32-bit machine trying to use 7GB of RAM.. Not going to happen!

If you are running 3D modeling systems or AutoCAD systems, you can benefit from a x64 bit architecture but remember, you need to be running ALL x64 applications, print drivers and anything else you are setting up on your system to realize its full potential. Not all programs have been created for x64 yet and you will find yourself installing applications to your Program Files x86 directory. On a x64 machine, you will have two Program File directories — one for 32-bit and one for 64-bit applications.

So after reading through that and you still want to run a x64 operating system, you will need to make sure your processor supports x64. Most new servers and new computers bought this year or beyond will support x64 but you will still need to check. Here are some facts you should know (taken from ZDNet):

  • Almost all new servers sold within the last two years from AMD or Intel will have x64 capability.
  • Most mid- to high-end desktop processors from AMD or Intel within the last year have x64 capability.
  • Some higher-end Semprons have x64; lower-end Semprons do not.
  • No AMD Durons have x64.
  • All AMD Opteron processors have x64.
  • All AMD X2, FX, and Athlon64 chips have x64.
  • All Intel Pentium D and Celeron D chips have x64.
  • All AMD Turion notebook processors have x64.
  • All Intel Core 2 processors (mobile, desktop, and server) have x64.
  • No Intel Core Duo notebook processors have x64
  • No Intel Pentium M notebook processors have x64.

If you are still not sure if your processor can support x64 check out GRC’s SecurAble and let them help you figure it out! You might also want to check out Mahendra’s post How To Choose Between 32-bit & 64-bit Windows 7 Operating Systems.

If you are running a server that has all its hardware and software certified for x64, then you should install the 64-bit version but beware of device drivers and any 32-bit environments because if I used the word difficult, it would be an understatement!

Restoring RMAN backup on a new machine

Posted in Uncategorized by Muhammad Nadeem Chaudhry on January 22, 2011

to restore RMAN backup to a new machine follow these steps on windows:
– install oracle software on the machine with the same directory structure as the database
we have its backup
– copy backup comes from original database to the same directory as it is defined in the database
controlfile(backup folder)
– create windows service for the database
C:\>oradim -new -sid ORCL -startmode m
C:\>oradim -edit -sid ORCL -startmode a
– Get the dbid from the remote database
let dbid=1145595280
– Connect with rman to the target database at local server
C:\>Documents and Settings\Administrator>rman target /
Recovery Manager: Release 9.2.0.1.0 – Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> set dbid 1145595280
executing command: SET DBID
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘D:\ORACLE\ORA92\DATABASE\INITORCL.ORA’
trying to start the Oracle instance without parameter files …
Oracle instance started
Total System Global Area 97589952 bytes
Fixed Size 453312 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
RMAN> restore spfile from ‘E:\rman_backup\control\CC-1145595280-20070506-01’;
Starting restore at 06-MAY-07
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
channel ORA_DISK_1: autobackup found: E:\rman_backup\control\CC-1145595280-20070
506-01
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 06-MAY-07
– create db directory structure like the primary db
‘d:\oracle\oradata\orcl’,’d:\oracle\oradata\orcl\archive’
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

Error: 

RMAN-03002: failure of startup command at 04/09/2008 18:13:56 RMAN-04014: startup failed: 

ORA-02778: Name given for the log directory is invalid

Solutions: Create bdump,udump,cdump and create folder at C:\oracle\product\10.1.0\admin\orcl

RMAN> restore controlfile from ‘E:\rman_backup\control\CC-1145595280-20070506-01’;
Starting restore at 06-MAY-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
output filename=D:\ORACLE\ORADATA\ORCL\CONTROL02.CTL
output filename=D:\ORACLE\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 06-MAY-07
RMAN> shutdown
Oracle instance shut down
RMAN> exit
Recovery Manager complete.
C:\Documents and Settings\Administrator>rman target /
Recovery Manager: Release 9.2.0.1.0 – Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
database is already started
RMAN> shutdown immediate;
using target database controlfile instead of recovery catalog
database dismounted
Oracle instance shut down
– copy password file from primary database to the current database
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
RMAN> restore database;
Starting restore at 06-MAY-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=14 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF
restoring datafile 00004 to D:\ORACLE\ORADATA\ORCL\DRSYS01.DBF
restoring datafile 00005 to D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
restoring datafile 00006 to D:\ORACLE\ORADATA\ORCL\INDX01.DBF
restoring datafile 00007 to D:\ORACLE\ORADATA\ORCL\ODM01.DBF
restoring datafile 00008 to D:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
restoring datafile 00009 to D:\ORACLE\ORADATA\ORCL\USERS01.DBF
restoring datafile 00010 to D:\ORACLE\ORADATA\ORCL\XDB01.DBF
restoring datafile 00011 to D:\ORACLE\ORADATA\ORCL\OEM_REPOSITORY.DBF
restoring datafile 00012 to D:\ORACLE\ORADATA\ORCL\USERS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\RMAN_BACKUP\LEVEL0\BAK_64_621616943_LEVEL0.BAK tag=TAG20070503T1
52256 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 06-MAY-07

RMAN> recover database;

ORA-19698: F:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB1\REDO01.LOG is from different database: id=4011070094, db_name=TESTDB1″ 

Resolution:

If the error “ORA-19698: %s is from different database: id=string, db_name=string” is seen during an Oracle disaster recovery operation, perform the following.

Bring the database to nomount state. 

2. Empty the contents of the directory (under the oradata directory) containing the data files and online redo logs of the newly created database.

3. Re-try the control file restore operation

 RMAN> recover database;
Starting recover at 06-MAY-07
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
destination for restore of datafile 00002: D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
destination for restore of datafile 00003: D:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF
destination for restore of datafile 00004: D:\ORACLE\ORADATA\ORCL\DRSYS01.DBF
destination for restore of datafile 00005: D:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
destination for restore of datafile 00006: D:\ORACLE\ORADATA\ORCL\INDX01.DBF
destination for restore of datafile 00007: D:\ORACLE\ORADATA\ORCL\ODM01.DBF
destination for restore of datafile 00008: D:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
destination for restore of datafile 00009: D:\ORACLE\ORADATA\ORCL\USERS01.DBF
destination for restore of datafile 00010: D:\ORACLE\ORADATA\ORCL\XDB01.DBF
destination for restore of datafile 00011: D:\ORACLE\ORADATA\ORCL\OEM_REPOSITORY
.DBF
destination for restore of datafile 00012: D:\ORACLE\ORADATA\ORCL\USERS02.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\RMAN_BACKUP\LEVEL2\BAK_68_621875547_LEVEL2.BAK tag=TAG20070506T1
51313 params=NULL
channel ORA_DISK_1: restore complete
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=98
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\RMAN_BACKUP\ARCH\BAK_70_621875630_ARCH.BAK tag=TAG20070506T15143
7 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=D:\ORACLE\ORADATA\ORCL\ARCHIVE\ARC00098.001 thread=1 sequen
ce=98
unable to find archive log
archive log thread=1 sequence=99
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/06/2007 15:43:06
RMAN-06054: media recovery requesting unknown log: thread 1 scn 17505361
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.

Missing Redo Log Scenarios

Posted in Uncategorized by Muhammad Nadeem Chaudhry on January 14, 2011

Consider a scenario where you have lost your redo logs on a DB instance. It is possible to recreate them. It is also possible to recover the database with no data loss in certain situations. Let’s jump to action immediately. But first we need to know different ways of redo loss and which category we fit in.

A Redo log is a journal of events happening in the DB at any given time. Redo logs are the most crucial part of the database after the controlfile. The documentation defines the redo logs as follows:

The online redo log is a set of operating system files that record all changes made to any database block, including data, index, and rollback segments, whether the changes are committed or uncommitted. All changes to Oracle blocks are recorded in the online log.

A redo log, at the time of being deleted, will be in one of the below scenarios:
1) The redo log is not CURRENT and archived
2) The redo log is not CURRENT but not archived
3) The redo log is CURRENT (DB was shut down cleanly)
4) The redo log is CURRENT (DB was not shut down cleanly)
Identify which scenario fits you. In all the scenarios except (1) we will need to open the database using OPEN RESETLOGS option. You may use the below SQL to find the above details:

SQL> select a.group#, b.member, a.archived, a.status

  2  from v$log a, v$logfile b where a.group#=b.group#; 

    GROUP# MEMBER                                                                                          ARC     STATUS

         3            D:\VIKRAM\ORADATA\TEST\TEST\REDO03.LOG   YES     INACTIVE

         2            D:\VIKRAM\ORADATA\TEST\TEST\REDO02.LOG   YES     UNUSED

         1            D:\VIKRAM\ORADATA\TEST\TEST\REDO01.LOG    NO       CURRENT

Remember: RESETLOGS is compulsory when there is an incomplete recovery.

1) Redo log is not CURRENT and archived

If a redo log file is already archived, its loss can safely be ignored. Since all the changes in the DB are now archived and the online log file is only waiting for its turn to be re-written by LGWR (redo log files are written circularly) the loss of the redo log file doesnt matter much. It may be re-created using the command

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP <group#>;

This will re-create all groups and no transactions are lost. The database can be opened normally after this.

2) Redo log is not CURRENT and not archived

If the redo log is lost before it is archived then there are a few transactions in the redo log that are lost too. Since a crash recovery is no more possible, a media recovery is required. The DB needs to be restored from a previous backup and recovered until the last available archive log is applied. The reason I say crash recovery is not possible is that by definition a crash recovery should need only online redo logs to recover the database to a consistent state. If a recovery needs a log file that is not online (like archive logs) then the recovery is no more a crash recovery. It becomes media recovery. In our current case since we are applying the archives and using the previous backup to restore it is not a crash recovery.
Also since we cannot recover database completely as some data is lost in the redo log files before being archived, this becomes an incomplete recovery too. The DB needs to be opened up in RESETLOGS mode. The steps to recover are below:

— Restore the DB from previous backup and open the DB in mount state.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
— Apply all the archives available
SQL> ALTER DATABASE OPEN RESETLOGS;

In a similar scenario an RMAN restore will be as below:

RMAN> RESTORE CONTROLFILE FROM ‘<backup tag location>’;
RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE UNTIL TIME “to_date(‘MAR 05 2009 19:00:00′,’MON DD YYYY HH24:MI:SS’)”;
RMAN> ALTER DATABASE OPEN RESETLOGS;

The above commands can also be used in a normal recovery scenario from Production to Test with a few modifications. The actual plan of steps that I had followed can be found here. There is a metalink note 1044466.6 that discusses this recovery in greater detail.

3) Redo log is CURRENT (DB was shut down cleanly)

If the CURRENT redo log is lost and if the DB is closed consistently, OPEN RESETLOGS can be issued directly without any transaction loss. It is advisable to take a full backup of DB immediately after the STARTUP.

4) Redo log is CURRENT (DB was not shut down cleanly)

When a current redo log is lost, the transactions in the log file are also lost before making to archived logs. Since a DB startup can no more perform a crash recovery (since all the now-available online log files are not sufficient to startup the DB in consistent state), an incomplete media recovery is the only option. We will need to restore the DB from a previous backup and restore to the point just before the lost redo log file. The DB will need to be opened in RESETLOGS mode. There is some transaction loss in this scenario.

RMAN> RESTORE CONTROLFILE FROM ‘<backup tag location>’;
RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE UNTIL TIME “to_date(‘MAR 05 2009 19:00:00′,’MON DD YYYY HH24:MI:SS’)”;
RMAN> ALTER DATABASE OPEN RESETLOGS;

Here we come to an end of all the scenarios in which a redo log can be lost.

There is a simpler way to restore the deleted current redo log file on Unix/Linux platforms too. The procedure to do it will be mentioned in my subsequent posts.

Meanwhile, I would love to hear from you any suggestions or additions to this post. Thank you.

RMAN Backup and Recovery Scenarios

Posted in Uncategorized by Muhammad Nadeem Chaudhry on January 14, 2011

Complete Closed Database Recovery. System tablespace is missing

If the system tablespace is missing or corrupted the database cannot be started up so a complete closed database recovery must be performed.

Pre requisites: A closed or open database backup and archived logs.

1. Use OS commands to restore the missing or corrupted system datafile to its original location, ie:

cp -p /user/backup/uman/system01.dbf /user/oradata/u01/dbtst/system01.dbf

2.startup mount;
3. recover datafile 1;
4. alter database open;

Complete Open Database Recovery. Non system tablespace is missing

If a non system tablespace is missing or corrupted while the database is open, recovery can be performed while the database remain open.

Pre requisites: A closed or open database backup and archived logs.

1. Use OS commands to restore the missing or corrupted datafile to its original location, ie:

cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf

2. alter tablespace <tablespace_name> offline immediate;

3. recover tablespace <tablespace_name>;

4. alter tablespace <tablespace_name> online;

Complete Open Database Recovery (when the database is initially closed).Non system tablespace is missing

If a non system tablespace is missing or corrupted and the database crashed,recovery can be performed after the database is open.

Pre requisites: A closed or open database backup and archived logs.

1.   startup; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted)

2.   Use OS commands to restore the missing or corrupted datafile to its original location, ie:

cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbf

3.   alter database datafile3 offline; (tablespace cannot be used because the database is not open)

4.   alter database open;

5.   recover datafile 3;

6.   alter tablespace <tablespace_name> online;

Recovery of a Missing Datafile that has no backups (database is open).

If a non system datafile that was not backed up since the last backup is missing,recovery can be performed if all archived logs since the creation of the missing datafile exist.
Pre requisites: All relevant archived logs.

1.   alter tablespace <tablespace_name> offline immediate;

2.   alter database create datafile ‘/user/oradata/u01/dbtst/newdata01.dbf’;

3.   recover tablespace <tablespace_name>;

4.   alter tablespace <tablespace_name> online;

If the create datafile command needs to be executed to place the datafile on a location different than the original use:

alter database create datafile ‘/user/oradata/u01/dbtst/newdata01.dbf’ as ‘/user/oradata/u02/dbtst/newdata01.dbf’

Restore and Recovery of a Datafile to a different location.

If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed.
Pre requisites: All relevant archived logs.

1.    Use OS commands to restore the missing or corrupted datafile to the new location, ie:

cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf

2.    alter tablespace <tablespace_name> offline immediate;

3.    alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/dbtst/user01.dbf’ to ‘/user/oradata/u02/dbtst/user01.dbf’;

4.    recover tablespace <tablespace_name>;

5.    alter tablespace <tablespace_name> online;

Control File Recovery

Always multiplex your controlfiles. Controlfiles are missing, database crash.
Pre requisites: A backup of your controlfile and all relevant archived logs.

1.    startup; (you get ora-205, missing controlfile, instance start but database is not mounted)

2.    Use OS commands to restore the missing controlfile to its original location:

cp -p /user/backup/uman/control01.dbf /user/oradata/u01/dbtst/control01.dbf
cp -p /user/backup/uman/control02.dbf /user/oradata/u01/dbtst/control02.dbf

3.    alter database mount;

4.    recover automatic database using backup controlfile;

5.    alter database open resetlogs;

6.    make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

Incomplete Recovery, Until Time/Sequence/Cancel

Incomplete recovery may be necessaire when an archived log is missing, so recovery can only be made until the previous sequence, or when an important object was dropped, and recovery needs to be made until before the object was dropped.

Pre requisites: A closed or open database backup and archived logs, the time or sequence that the ‘until’ recovery needs to be performed.

1.  If the database is open, shutdown abort

2.  Use OS commands to restore all datafiles to its original locations:

cp -p /user/backup/uman/u01/*.dbf /user/oradata/u01/dbtst/

cp -p /user/backup/uman/u02/*.dbf /user/oradata/u01/dbtst/

cp -p /user/backu  p/uman/u03/*.dbf /user/oradata/u01/dbtst/

cp -p /user/backup/uman/u04/*.dbf /user/oradata/u01/dbtst/

etc…

3.  startup mount;

4.  recover automatic database until time ‘2004-03-31:14:40:45’;

5.  alter database open resetlogs;

6.  make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.Alternatively you   may use instead of until time, until sequence or until cancel:
recover automatic database until sequence 120 thread 1; OR
recover database until cancel;

Rman Recovery Scenarios

Rman recovery scenarios require that the database is in archive log mode, and that backups of datafiles, control files and archived redolog files are made using Rman. Incremental Rman backups may be used also.

Rman can be used with the repository installed on the archivelog, or with a recovery catalog that may be installed in the same or other database.

Configuration and operation recommendations:

Set the parameter controlfile autobackup to ON to have with each backup a
controlfile backup also:

configure controlfile autobackup on;

set the parameter retention policy to the recovery window you want to have,

ie redundancy 2 will keep the last two backups available, after executing delete obsolete commands:

configure retention policy to redundancy 2;

Execute your full backups with the option ‘plus archivelogs’ to include your archivelogs with every backup:

backup database plus archivelog;

Perform daily maintenance routines to maintain on your backup directory the number of backups you need only:

crosscheck backup;

crosscheck archivelog all;

delete noprompt obsolete backup;

To work with Rman and a database based catalog follow these steps:

1. sqlplus /

2. create tablespace repcat;

3. create user rcuser identified by rcuser default tablespace repcat temporary tablespace temp;

4. grant connect, resource, recovery_catalog_owner to rcuser

5. exit

6. rman catalog rcuser/rcuser # connect to rman catalog as the rcuser

7. create catalog # create the catalog

8. connect target /

Complete Closed Database Recovery. System tablespace is missing

In this case complete recovery is performed, only the system tablespace is missing,so the database can be opened without reseting the redologs.

1.  rman target /

2.  startup mount;

3.  restore database;

4.  recover database;

5.  alter database open;

Complete Open Database Recovery. Non system tablespace is missing,database is up

1.   rman target /

2.   sql ‘alter tablespace <tablespace_name> offline immediate’;

3.   restore datafile 3;

4.   recover datafile 3;

5.   sql ‘alter tablespace <tablespace_name> online’;

Complete Open Database Recovery (when the database is initially closed).Non system tablespace is missing

A user datafile is reported missing when tryin to startup the database. The datafile can be turned offline and the database started up. Restore and recovery are performed using Rman. After recovery is performed the datafile can be turned online again.

1.    sqlplus /nolog

2.    connect / as sysdba

3.    startup mount

4.    alter database datafile ‘<datafile_name>’ offline;

5.    alter database open;

6.    exit;

7.    rman target /

8.    restore datafile ‘<datafile_name>’;

9.    recover datafile ‘<datafile_name>’;

10.   sql ‘alter tablespace <tablespace_name> online’;

Recovery of a Datafile that has no backups (database is up).

If a non system datafile that was not backed up since the last backup is missing,recovery can be performed if all archived logs since the creation of the missing datafile exist. Since the database is up you can check the tablespace name and put it offline. The option offline immediate is used to avoid that the update of the datafile header.

Pre requisites: All relevant archived logs.

1.    sqlplus ‘/ as sysdba’

2.    alter tablespace <tablespace_name> offline immediate;

3.    alter database create datafile ‘/user/oradata/u01/dbtst/newdata01.dbf;

4.    exit

5.    rman target /

6.    recover tablespace <tablespace_name>;

7.    sql ‘alter tablespace <tablespace_name> online’;

If the create datafile command needs to be executed to place the datafile on a location different than the original use:

alter database create datafile ‘/user/oradata/u01/dbtst/newdata01.dbf’ as ‘/user/oradata/u02/dbtst/newdata01.dbf’

Restore and Recovery of a Datafile to a different location. Database is up.

If a non system datafile is missing and its original location not available, restore can be made to a different location and recovery performed.

Pre requisites: All relevant archived logs, complete cold or hot backup.

1.    Use OS commands to restore the missing or corrupted datafile to the new location, ie:

cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf

2.    alter tablespace <tablespace_name> offline immediate;

3.    alter tablespace <tablespace_name> rename datafile ‘/user/oradata/u01/dbtst/user01.dbf’ to ‘/user/oradata/u02/dbtst/user01.dbf’;

4.    rman target /

5.    recover tablespace <tablespace_name>;

6.    sql ‘alter tablespace <tablespace_name> online’;

Control File Recovery

Always multiplex your controlfiles. If you loose only one controlfile you can replace it with the one you have in place, and startup the Database. If both controlfiles are missing, the database will crash.

Pre requisites: A backup of your controlfile and all relevant archived logs. When using Rman alway set configuration parameter autobackup of controlfile to ON. You will need the dbid to restore the controlfile, get it from the name of the backed up controlfile.It is the number following the ‘c-‘ at the start of the name.

1.   rman target /

2.   set dbid <dbid#>

3.   startup nomount;

4.   restore controlfile from autobackup;

5.   alter database mount;

6.   recover database;

7.   alter database open resetlogs;

8.   make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant.

Incomplete Recovery, Until Time/Sequence/Cancel

Incomplete recovery may be necessaire when the database crash and needs to be recovered, and in the recovery process you find that an archived log is missing. In this case recovery can only be made until the sequence before the one that is missing.

Another scenario for incomplete recovery occurs when an important object was dropped or incorrect data was committed on it.

In this case recovery needs to be performed until before the object was dropped.

Pre requisites: A full closed or open database backup and archived logs, the time or sequence that the ‘until’ recovery needs to be performed.

1.   If the database is open, shutdown it to perform full restore.

2.   rman target \

3.   startup mount;

4.   restore database;

5.   recover database until sequence 8 thread 1; # you must pass the thread, if a single instance will always be 1.

6.  alter database open resetlogs;

7.  make a new complete backup, as the database is open in a new incarnation and previous archived log are not relevant. Alternatively you may use instead of until sequence, until time, ie: ‘2004-12-28:01:01:10’.

Spool to .csv file

Posted in Uncategorized by Muhammad Nadeem Chaudhry on January 11, 2011

Sometimes you need to export result of the query to CSV file. Here is a nice example of how to do this kind of operations.

SQL> SET LINESIZE 500 FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF HEAD OFF PAGESIZE 0 TERM OFF

SQL> spool outfile.csv

SQL> select ‘”‘|| column_1 || ‘”,’ || column_2 || ‘,’ || column_3 from mytable where [your where statement]

SQL> spool off

SQL> exit

In this case column_1 data will be surrounded by “” (you may need this if you have , character in field data).

Update: SET LINESIZE determines a maximum number of characters in the line. So if you expect output bigger than 500 lines you must set this to a bigger value.