1.What are the steps involved in Database Startup ?1)Three steps are involved

Start an instance.

Mount the database.

Open the database

2)connect as sys dba and after that give command startup open if u dont want to do any maintenance for the database.otherwise give starup nomount and then alter database mount and finally alter database open

3)

1. Log in as oracle (or any user in group dba)

2. Set your ORACLE_SID to the instance you want to start. Verify that your ORACLE_HOME is set to the correct database version

3. Start SQL*Plus and connect as sysdba

4. Start the database.

During database startup you can specify the state it will open to. The default is open(startup) but you can specify STARTUP NOMOUNT or STARTUP MOUNT depending on which task to perform.

4)c:/>SQLPLUS / AS SYSDBA

connect to an ideal instance

now we can start the database with any of the stage as the requirement.

SQL>STARTUP NOMOUNT

or

SQL>STARTUP MOUNT

or

SQL>STARTUP OPEN

5) Steps are

NOMOUNT STAGE

SGA is allocated

background processes are started

Alert log file is opened

Mount Stage

controlfiles is read

name & location of the daatfiles logfiles are detected

Open Stage

files are opened

In this way database gets opened. Hope you got clear picture.

6).Steps for Database Startup.

(a) Startup Nomount : In Nomount Stage the SGA and Background process gets started.And it will check the database name.

(b)Mount : In Mount stage the Controlfiles Datafiles and Redolog File will be read at mount stage.

(c)Open : After successfully mount we can open the Database.

7) NOMOUNT : The init.ora file is read to check the existence of control files.MOUNT: The control file is read to get check the existence of data files and redolog filesSTART: The status of various data files is check by comparing the SCN of the file headers of various data files and comparing them with the SCN in control file.

8) Steps involved in database startup are as follows

Startup nomount - Instance starts SGA allocated Background processes started

Used during database creation

Startup mount - Control file read datafiles & online redolog files are allocated

Used for putting database in archivelog mode & for recovery

Open - All files open database is open for use

9) There are the three steps need to follow to start database:1) Start an Instance.2) Mount the database.3) Open the database.

Connect with operating system authentication:conncet / as sysdba1) Start an Instance:sql> startup nomount;

2) Mount the database:

sql> alter database mount;

3) open the database:sql> alter database open;

Instance:Every running oracle database is associated with an instance. when a database is started on the database server oracle allocates a memory area called the system global area(SGA) and start one or more oracle process.The combination of oracle processes and SGA is called an Oracle instance.

Mount the database:To mount the database is the process in that instance finds the database control files and open them. In mounting process of database Oracle reads the control files to get the name of the database's datafiles and redo log files.

Open the database:Opening a mounted database make it available for normal database operation . Any valid user can connect to an open database and access it's information.Usually a database administrator opens the database to make it available for the general use.When we open the database oracle opens the online datafiles and online redolog files

Table Export 2.How we you identify if a table was exported in its life time?

Refer cumulative or an incremental backup (export )we have a parameter (RECORD) & the default parameter is yes & is to track the incremental backup of a table so those backup (export) information will get update in the following sys tablesSYS.INCEXP SYS.INCFIL SYS.INCVID

desc SYS.INCVIDEXPID

desc SYS.INCEXPOWNER#NAMETYPE#CTIMEITIMEEXPID

desc SYS.INCFIL

EXPIDEXPTYPEEXPFILEEXPDATEEXPUSER

i have not checked any thing after an export from these tables try it out and let others also know about this

3.How can I Export 9i data in 8 ? I mean when I Export from 9i and Import in 8, I face errors. what sould I do and my pc is not in Network.

execute catexp.sql of 8 in oracle 9i then take export and then import in 8 database.

but i m confused how can i do it? explain me.

Follow these steps

1) Install Oracle 8i Client

2) Export database from Oracle 8i Client PC

3) Import data into Oracle 8i Database.

How can I Export 9i data in 8 ? I mean when I Expo... -------------------------------------------------------------------------------- Follow these steps1) Install Oracle 8i Client 2) Export database from Oracle 8i Client PC 3) Import data into 'Oracle 9i' Database.

By Applying Patches and Upgradation

The Best possible method is using Oracle 81 EXP utility take the export and import it into 8i database. For this either one should install Oracle 8i and take export or Install 8i client and take the export using oracle 8i client.

Oracle does not support backward compatibility for exp/imp so if you export in 9i you have to import in higher version than 9i only.

we can do it. but first we have to confirm that parameter NLS_LANG is same in both database. otherwise in IMP process oracle will give errorI would suggest create a db link in Oracle 8 to Oracle 9i and run the CREATE TABLE AS SELECT FROM another database.

I would suggest you move to Oracle 10g /11g soon as Oracle support is already stopped to 9i and 8i release.

4.what is the difference between recovery and restoring of the database.

The basic difference between recovery and restore is that in recovery the updated value from the database is removed while in resstore the value which is errased from database is restore.

Restoring a database is copying the physical files from a backup medium (disk or tape) to the appropriate file locations for database operationRecovery is process of updating database files restored from backup w/changes made to the database since backup typically using redo log files.(Restored backup files + changes = Recovery)

both the answers given seems to be contradictory

according to first one is we can get the changes made during restore

while according to the second one the changes are reflected during recovery.

please clear which one is write

Here is a scenario to understand Restore & Recovery

Sunday 10pm : Database is backed up. and is running fine.

Monday 11am : Went down / crashed due to some reason.

To bring up the database we have 2 options:

1. Simple Restore : copying files from backup taken sunday night and open the database. Here we loose all the changes that are done since sunday night.

2. Restore and Recovery: Copying files from backup taken sunday night and applying all the archivelog and redo log files to bring up the database to the point of failure. Here you dont loose the changes done until monday 11 am.

Restore : copying files from the backup overwriting the existing database files

Recovery: applying the changes to the database till point of failure. these changes are recorded in online redolog and archivelog ( which are the backups of redolog) files.

Restore means copying already backed up files from tape to respective location.

recovery means rollforward the database by appying the redo logfiles

to bring the DB to latest stat.

simply the restoration means copy the database object from the backup media to the destination where actually it is required. whereas recovery means to apply the database object copied earlier in order to bring the database to the consistent state.

restore is replacing the corrupted or missing files. recovery is apply the redo logs

1. Restoring the datafiles involves in copying the datafiles to their original locations from the last full backup.2. Recovery involves in applying all the changes that we happen on the database after that full backup from which the datafiles are restored.

Restore means that you are copying either datafiles(or)controlfile from one database to another database. It is possible only through RMAN. While connecting through RMAN this stage require only at nomount.Recover means that you are trying the recover database at mount stage.

The terms "recover" and "restore" both refer to retrieving files from a backup device such as a hard disk external hard drive or other storage media.

However recovering files typically refers to salvaging one or more files while a restore usually refers replacing a complete system or hard drive from a full system backup.

Restore means Loss of physical files restore from latest backup aviable.Recovery log apply from redo log files and archive log files.

 Restore:1. Restore is process of Copying data from Tapes to Archive log destination.2. It has nothing to do with Backup.

Recovery: Recovery is process when Archive log files are recovered or brought back from Archive log destination to Database.

5.What is mean live dead lock? How to prevent it?

When there r 2 or more process run at a time and want to same resource then if one process take resource and othere process want same resource then there will be dedlock occour.

A deadlock occurs when two or more users are waiting for data locked by each other(Not commited by user01).

To fix this recreate the segment with higher INITTRANS and/or PCTFREE values. This will allow more space in the data blocks for Oracle to allocate more transaction entries (24 bytes at a time) when required

Whenever you have competing DML running against the same data you run the risk of a deadlock.

Tune the application - Single-threading related updates and other application changes can often remove deadlocks. Re-scheduling batch update jobs to low-update times an also help.

Add INITRANS - In certain conditions increasing INITRANS for the target tables and indexes(adding slots to the ITL) can relieve deadlocks.

Use smaller blocks with less data - Since the deadlock contention is at the block-level consider moving these tables and indexes to a super-small blocksize (create a db2k_cache_size) and using a high PCTFREE to space-out the data over MORE blocks.

6.how much time will it take a hotbackup if the batabase size is 60Gb

its all upto the Kind of Server and No of server Parameters configured and the type of backup device

How Much Time??? for 60 GB?Depends! On the I/O at that time...

Hot backup involves many Steps including - taking the particular Tablespace in begin backup mode and then copying the file to Backup directory.

It all depends on the Server Configuration & I/O at that time. Generally the DB will be stored on EMC - where EMC connects to large number of Servers.

Depends on the amount of data inside the database.

It depends on the datafiles related to database at OS Level.

Depends upon number of servers and RAM.

7.How do you verify and validate the Datafile which is backed up?

use dbverify to validate the backups

The DBVERIFY Utility

An OS executable (dbv in $ORACLE_HOME/bin) used to verify datafiles

Works against datafiles live or backup But doesnot work on Backup sets...

Works for datafiles on raw devices but you must give it a range of blocks to look at.

In the output it refers to blocks as pages . Block 5 would be page 5.

RMAN performs its own validation when backing up so dbv is not necessary. It can be used on user backups (aka non-RMAN backups).

If a block is influx then DBV was forced to check it multiple times because it was in use.

Its most asked Interview Question!

Ravi Prakash

http://ravidba-oracle.blogspot.com/

To verify that the datafile is particularly backed up or not.

Sql>select * from v$backup-->This will shows the status of a particular file.sql>select * from v$recover_file-->This is show whether any file has to be recovered.

in rman list recoverable backup of database

Use RMAN Backup validate database.

It will performs a validation of the datafiles by checking for logical and physical block corruption.

No backupsets are created.

8.How do you setup a Physical stanby for Oracle 9i in Linux?

Creating a physical standby using RMAN

Steps for Physical standby for Oracle 9i in LINUX.

On Primary Side

1. Take rman fullbackup with controlfile and spfile.2. Take standby controlfile backup as SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/disk1/oracle/oradata/payroll/standby/payroll2.ctl';3.3. CREATE PFILE='/disk1/oracle/dbs/initpayroll2.ora' FROM SPFILE;

On StandBy Side1. Restore database using rman by using pfile from primary database.2. Start the Physical Standby Database.3. Initiate Log Apply Services

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

4.Verifying the Physical Standby Database

SQL> SELECT SEQUENCE# FIRST_TIME NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

5. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;6. Check logs of both the database both should be sync.

9.how to recover control file without backup and without 'create control file' command?

If we lost the controlfile and there is no controlfile backup trace. The only soln is for that is recreate the controlfile script by own.

if we don;t have any backup of Controlfile...then we have to use create controlfile command..

If this is the situation simple process is :

1. Take a trace of control file from clone do the changes in that like database name and datafile name and location.2. Save it as < name >.ora3. Run the file in nomount stage.

This will create your control file .

Note : Always make a habit of taking control file trace with the cold back.

10.How to take cold and hot backup using UNIX

We can perform cold backup in unix by shutting down the database in proper mode so the control file will in consistent state and after that u can perform backing up all database files.I hot back we can perform by the stmt >alter tablespace begin backup;Then enter into unix promt and backup the datafile of that tablespace then>alter tablespace ? end backup;

Cold backup we can perform using OS commands after shutting down the database.In DB running position we can take hot backup using the following commans;alter database begin backup;

If an oracle database has crashed. How would you recover the transactions that are not in the backups?

By time based recovery.

we can issue recover database till time

If an Oracle Database has been crashed we can recover the database with SCN. For every commit transaction the SCN will generate.

Sql>Alter database recover automatic using backup controlfile until SCN;

 Following steps are involved in Database Startup 1. Start the listener 2. Mount the database

If the database is in ARCHIVELOG mode we can recover the transactions using archive logs otherwise we can not recover the transactions that are not in the backups.

You have taken a manual backup of a datafile using o/s. How RMAN will know about it?

use CATALOG command

You have to catalog those backups with RMAN using catalog.

Full backup is offline backup of datafiles Parameter file Controlfiles Redologfiles 

Whenever we take any backup through RMAN in the repository information of the backup is recorded. The RMAN respository can be either controlfile or recovery catalog. However if I take a backup through OS command then RMAN does not aware of that and hence recorded are not reflected in the repository. This is also true whenever we create a new controlfile or a backup taken by RMAN is transferred to another place using OS command then controlfile/recovery catalog does not know about the prior backups of the database. So in order to restore database with a new created controlfile we need to inform RMAN about the backups taken before so that it can pick one to restore.

This task can be done by catalog command in RMAN. With catalog command it can-Add information of backup pieces and image copies in the repository that are on disk.-Record a datafile copy as a level 0 incremental backup in the RMAN repository.-Record of a datafile copy that was taken by OS.

But CATALOG command has some restrictions. It can't do the following.-Can't catalog a file that belong to different database.-Can't catalog a backup piece that exists on an sbt device.

Examples of Catalog command1)Catalog an archive log: To catalog two archived logs named /oracle/oradata/arju/arc001_223.arc and /oracle/oradata/arju/arc001_224.arc the command is RMAN>CATALOG ARCHIVELOG '/oracle/oradata/arju/arc001_223.arc' '/oracle/oradata/arju/arc001_224.arc';

2)Catalog a file copy as an incremental backup: To catalog datafile copy '/oradata/backup/users01.dbf' as an incremental level 0 backup your command will be RMAN>CATALOG DATAFILECOPY '/oradata/backup/users01.dbf' LEVEL 0;Note that this datafile copy was taken backup either using the RMAN BACKUP AS COPY command or by using operating system utilities in conjunction with ALTER TABLESPACE BEGIN/END BACKUP.

3)Catalog multiple copies in a directory: To catalog all valid backups from directory /tmp/backups issue

RMAN>CATALOG START WITH '/tmp/backups' NOPROMPT;

4)Catalog files in the flash recovery area: To catalog all files in the currently enabled flash recovery area without prompting the user for each one issue RMAN>CATALOG RECOVERY AREA NOPROMPT;

5)Catalog backup pieces: To catalog backup piece /oradata2/o4jccf4 issue RMAN>CATALOG BACKUPPIECE '/oradata2/o4jccf4';

Uncatalog BackupIn many cases you need to uncatalog command. Suppose you do not want a specific backup or copy to be eligible to be restored but also do not want to delete it. To uncatalog all archived logs issue RMAN>CHANGE ARCHIVELOG ALL UNCATALOG;

To uncataog tablespace USERS issue RMAN>CHANGE BACKUP OF TABLESPACE USERS UNCATALOG;

To uncatalog a backuppiece name /oradata2/oft7qq issue RMAN>CHANGE BACKUPPIECE '/oradata2/oft7qq' UNCATALOG; 

Oracle log switch question

My database is in archive mode. when log switch occur that log file is archiving . what happens when log switch occurs

Log switch event will take place. But the status of the log file which is archiving will be ACTIVE till it finish archiving.

After logswitch checkpoint accour.It update the hedder of datafile and controlfile with new SCN no. and checkpoint position.it also give the signal to dbwr to writ dirty buffer to datafile.

You can take a coldbackup from solaris and use it on windows NT?

A) YesB) Yes, if RMAN backup performed from NT serverC) Yes, after running RMAN convertD) No

D) NO

Reason :

We can use rman convert only for Os with same endian format.

Solaris is big endian and windows is small endian and hence rman convert will not work in this scenario. We will need to use transportable tablespace here. Check note 413586.1

SQL> select PLATFORM_NAME ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;

PLATFORM_NAME ENDIAN_FORMAT---------------------------------------- --------------Solaris[tm] OE (32-bit) BigSolaris[tm] OE (64-bit) BigMicrosoft Windows IA (32-bit) Little

Linux IA (32-bit) LittleAIX-Based Systems (64-bit) BigHP-UX (64-bit) BigHP Tru64 UNIX LittleHP-UX IA (64-bit) BigLinux IA (64-bit) LittleHP Open VMS LittleMicrosoft Windows IA (64-bit) LittleIBM zSeries Based Linux BigLinux 64-bit for AMD LittleApple Mac OS BigMicrosoft Windows 64-bit for AMD LittleSolaris Operating System (x86) LittleIBM Power Based Linux Big

This roughly translates as 3 thumb rules 1) Microsoft and linux are little endian 2) IBM Soalris and Apple are big endian 3) HP tru and open are little endian but 64 bit are BIG endian

NO Because here OS is different this cold backup can be used in solaris only.

Yes we can use it... RMAN can convert the datafiles from little endian to big endian... The only exception is that both the databases have to use the same national character set and character set.

Whats the Concept of Flashback Query?

Whats the Concept of Flashback Query in Oracle (10g in particular) and how is it beneficial?

It lets you to view and repair historical data.They query the database by TIME or user specified SCN. It do not UNDO anything .It is only a query mechanism.You can take o/p from flashback query and perform an undo yourself.It do not work through DDL operation.for example the data before 5 minutes which has lost is repaired as-

execute dbms_flashback.enable_at_time(sysdate-5/1440);

............................................execute dbms_flashback.disable 

What is the difference between ntbackup and third party backup

One difference between NT Backup and full-blown third-party backup utilities is the lack of an internal backup job scheduler. While most backup utilities can be set to run automatically at a certain time of the day NT Backup doesn t have this option. However scheduling NT Backups is possible with the Windows NT AT command. The AT command which requires that the Windows NT Schedule service be running (by default this service does not run; if you wish to enable it go to Services change its startup type to Automatic and click the start button to have it start immediately)

Types of Backup

What are different types of backup? (Hot, Cold, logical, Physical) Explain in detail.

Hot backups are when database is up and running you take backup(DB has to be in archivelog mode in order to do that.) Cold backups are shutdown the database and copy datafiles control files and save them some place

safer(on a different server) Good dba should do that atleast once a month. When you rman backups benefit is that it only backsup blocks that have been used empty data does not get backed up. This is very efficient as far as space issues go.

logical backups are export dumps 

1. Hot inconsistance and online backup are same2. Cold close consistance and offline backup are same

For 1. db must be archive mode inconsistance mean the SCN are not sameFor 2. db may be archive mode consistance mean SCN are same (controlfile datafile)

Logical backup through import/export utilities

Physical backup simply copy all file using OS command

RMAN BackupDetail script of backup using RMAN in both mode catalog and

nocatalog

rman is backup and recovery tool of oracleeasy to perform

Full Backup in the sense taking up the whole database backup into a specific folder or a path but we cant take any jobs as backup and SQL Server logins also we cant take. 

Recovery CatalogWhat is recovery catalog? Why do you need this?

When we specify Catalog - RMAN uses recovery catalog database to store RMAN metadata.

Using a recovery catalog preserves RMAN repository information if the control file is lost making it much easier to restore and recover following the loss of the control file.

It stores much more extensive history of your backups than the control file due to limits on the number of control file records. By using Recovery catalog you will get some additional features which you will not find when using control file e.gTo save the scripts etc

we can start the target database in the event of loss of controlfile using recovery catalog. Also The repository information is not saved in the controlfile when we make backup of control file.

Recovery catalog is a repository of metadata that is available in the control file of the target database. Whenver we take backups using RMAN the copy of the backup is placed in the control file in the form of reusable records and as well as in the recovery catalog in the form of tables. So that while taking recovery also these table info is useful to apply the backup data.

Recovery catalog contains metadata of control file of target database whenever we take backup by RMAN it contains information and location of backup.

Configure CommandWhat is configure command? When do you use this?

Configure command is used in RMAN. This is used to configure the parameters for the RMAN database to work while taking backups and doing recoveries.Like:

1. configure controlfile autobackup on;2. configure retention policy to redundancy 2;

show all; command at RMAN will show all configure command in default or update configuration mode

CONFIGURE allows you to set options and have the setting stick around (persistence)

common things to set:SNAPSHOT CONTROLFILE NAMERETENTION POLICY CHANNEL DEVICE TYPE CONTROLFILE AUTOBACKUP

See what is set (or standing default values now)

SHOW ALL;

assumes the use of a recovery catalog

Corrupted Archieve LogsThere are 100 datafiles, numbered from 1 to 100. File number 10 is deleted and it has 500 MB of data. The database is working in archive log mode. How can be the database recovered ? While

doing recovery the archive logs applied are from nos 1 to 100, but 50 gets corrupted. What do we do? Can we proceed further? If so how?

you can recover the datafiles byusing the following command mount the database and issues the following comand before openining it

SQL> alter database recover datafile '< datafile file name.';SQL> alter database open;

Part 2 if while doing a recovery the archive logs gets corrupted then we are in serious trouble. Rolling forward tranactions need the archive logs to be proper if they are corrupted we will need some advanced recovery techniques I forgot what the names of the advanced recovery utilities but Oracle suggests in such a case the you get in touch with support before using those utilities. But to be more precise and answer your question I do not think we can recover the database fully with corrupted archive logs especially if the redo logs have been swtiched.

If the archive log which was missed has been still exists in the online redo log file. That means the log sequence number for that file in the online redo log files doesnt get overwritten at the time we plan to do recovery then switch that redo log file again so that we will get the corrupted log file back.

Your database in archive log mode one of your archive file is missing there is no way to performed complete recovery you can able to do incomplete recovery recover database till cancelled or there is another way if you have a export copy of that datafile latest than that archive log file missed then import that datafile data loss is lesser as compared to incomplete recovery.

In this situation you're not able to recover the database. Of course it can be done by using nonsupported and nondocumented parameter "_ALLOW_RESETLOGS_CORRUPTED" but you HAVE NOT to use it without Oracle Support's suggestion

If your database is running take a whole backup delete all archived redo log files and start creating archived redo log files again

In this critical situation I had a pritical example which follows this.SQL> recover database until cancel using backup controlfile;ORA-00279: change 9867098396261 generated at 03/21/2008 13:37:44 needed forthread 1ORA-00289: suggestion : /arcredo/XSCLFY/log1_648355446_2093.arcORA-00280: change 9867098396261 for thread 1 is in sequence #2093

Specify log: {=suggested | filename | AUTO | CANCEL}cancelORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01195: online backup of file 1 needs more recovery to be consistentORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’ORA-01112: media recovery not started

SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01195: online backup of file 1 needs more recovery to be consistentORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’

After doing some research we found out one hidden parameter (_ALLOW_RESETLOGS_CORRUPTION=TRUE) will allow us to open database even though it’s not properly recovered.

We forced open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE. It allows us to open database but instance crashed immediately after open. we checked the alert.log file and found out we have undo tablespace corruption.

Alert log shows below error

Errors in file /u01/XSCLFYDB/admin/XSCLFY/udump/xsclfy_ora_9225.trc:ORA-00600: internal error code arguments: [4194] [17] [9] [] [] [] [] []Tue Mar 25 12:45:55 2008

Errors in file /u01/XSCLFYDB/admin/XSCLFY/bdump/xsclfy_smon_24975.trc:ORA-00600: internal error code arguments: [4193] [53085] [50433] [] [] [] [] []Doing block recovery for file 433 block 13525Block recovery from logseq 2 block 31 to scn 9867098416340

To resolve undo corruption issue we changed undo_management to “Manual” in init.ora. Now it allowed us to open database successfully. Once database was up and running we created new undo tablespace and dropped old corrupted undo tablespace. we changed back the undo_management to “Auto” and undo_tablespace to “NewUndoTablespace”.

It resolved our issue and database was up and running without any issue.

_ALLOW_RESETLOGS_CORRUPTION=TRUE allows database to open without consistency checks. This may result in a corrupted database. The database should be recreated.

As per Oracle Metalink there is no 100 guarantee that setting _ALLOW_RESETLOGS_CORRUPTION=TRUE will open the database. However once the database is opened then we must immediately rebuild the database. Database rebuild means doing the following namely: (1) perform a full-database export (2) create a brand new and separate database and finally (3) import the recent export dump. This option can be tedious and time consuming but once we successfully open the new database then we expect minimal or perhaps no data loss at all. Before you try this option ensure that you have a good and valid backup of the current database.Solution:

1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.2) Startup Mount3) Recover database4) Alter database open resetlogs.5) reset undo_management to “manual” in init.ora file.6) startup database7) Create new undo tablespacechanged undo_management to “AUTO” and undo_tablespace to “NewTablespace”9) Bounce database.

Authentication Methods for DBAsWhat are the steps to creating a password authentication file?

There are two types of authenticationsOS levelDB level

For OS level authentication:

1. Shutdown the database2. Set OS_AUTHENT_PREFIX=' ' in init.ora file3.$ connect '/ as sysdba'4. > startup5.> create user username(OS level userid created by admin) identified externally;6.>grant connect resource to username;7.dbs$ sqlplus /8. $username connect / as sysdba9. startup force

For DB level authentication:

1. set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE in init.ora file2. dbs$orapwd file=orapw$ORACLE_SID password=sys force=y3. startup force;4. grant sysdba to user(any user in the database);5. conn user/user6. conn user/user as sysdba7. show user

RMAN Calatalog Database

Is it possible to take backup of catalog database using RMAN? If Yes, How?

Yes Schedule a Schema level Export backup of the RMAN Catalog Database in Task Scheduler or CRON

Exporting the Recovery Catalog

This example uses the Original Export utility described in Oracle Database Utilities to create a logical export of the recovery catalog. Refer to Oracle Database Utilities for concepts and procedures relating to the Data Pump Export utility.

The following procedure creates a logical export of the recovery catalog.

1. Execute the Oracle export utility at the operating system command line making sure to do the following:

o Connect as the owner of the recovery catalog

o Specify the OWNER option

o Specify an output file

For example if the owner of the catalog in database catdb is rman you can issue the following at the UNIX command

line to export the catalog to file cat.dmp:

exp rman/cat@catdb FILE=cat.dmp OWNER=rman

2. Examine the output to make sure you were successful:

3. Export terminated successfully without warnings.

Importing the Recovery Catalog

This example uses the Original Import utility described in Oracle Database Utilities to create a logical export of the recovery catalog. Refer to Oracle Database Utilities for concepts and procedures relating to the Data Pump Import utility.

To make a logical import of the recovery catalog from the command line:

1. Create a new user in another database.

2. Import the catalog data from the export file. Execute the import at the command line making sure to do the following:

Connect as the new owner of the recovery catalog.

Specify the old owner with the FROMUSER parameter.

Specify the new owner with the TOUSER parameter.

Specify the import file.

For example assume the following:

The old owner of the catalog in database prod1 is rman.

The user in the new recovery catalog database catdb2 is rman2.

The file containing the export of the catalog is cat.dmp.

The command is then as follows:

imp USERID=rman2/cat2@catdb2 FILE=cat.dmp FROMUSER=rman TOUSER=rman2

Method 2:

When backing up the recovery catalog database you can use RMAN to make the backups. you should start RMAN with the NOCATALOG option so that the repository for the recovery catalog is the control file in the catalog database.

Follow these guidelines when developing an RMAN backup strategy for the recovery catalog database:

Run the recovery catalog database in ARCHIVELOG mode so that you can do point-in-time recovery if

needed.

Set the retention policy to a REDUNDANCY value greater than 1.

Back up the database onto two separate media (for example disk and tape).

Run BACKUP DATABASE PLUS ARCHIVELOG at regular intervals to a media manager if available or just to

disk.

Do not use another recovery catalog as the repository for the backups.

Configure the control file autobackup feature to ON.

For my understanding can we take backup the catalog database through RMAN

1. yes possible to take backup like

rman target / catalog catalogdbusername/password@catalogdbname

How ever it wont be good to have backup like this better to go with offline usermanged backup

(or)

2. just say rman and then target as your catalog database and take backup but still it wont be advisable as there is no use of it too

Schedule a Schema level Export backup of the RMAN Catalog Database in Task Schedular or CRON

RMAN catalog is a usual database so just backup is with RMAN as any other database using it as target database:

RMAN sys/password@<RMAN catalog tnsname>.

It can be done with no catalog or any other catalog (surely not using itself as catalog)/

Diagnose the problemSuppose you have two servers, one at 2nd floor and another on 3rd floor,

If you have to configure the server on 3rd floor while sitting on 2nd floor how Will you do it?

To configure the Server please use Remote Desktop which is available from Windows 2000 Operating System. If both are same domain connect to ftp and ssh or scp / remote desktop connection/netmeetigs/vncserver.

Just using any telnet/ssh client (for Unix machines sure) like Putty.

Inconsistant Backup

Why is the header of datafile and information of control file different at the time of inconsistant backup?

When we start datafile backup with begin backup command which will freeze the datafile headers and SCN cannot be updated only in controlfile SCN will update. This process is till end backup.

When begin backup command is issued the datafile header's are blocked. While the controlfile SCN does gets changed. During startup of the database the database has to do a rollforward based on the SCN between the header of datafile and control file.

What are the steps involved in Database Startup ?

Start an instance, Mount the Database and Open the Database. Read answers (12)

What is the difference between recovery and restoring of the database. Read answers (12)

Can Full Backup be performed when the database is open ? No. Read answers (11)

What is Full Backup ?

A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter. Read answers (9)

What are the advantages of operating a database in ARCHIVELOG mode over operating

What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode ? Read answers (9)

What is the difference between media recovery & crash recovery..? Read answers (9)

How can I Export 9i data in 8 ? I mean when I Export from 9i and Import in 8, I face errors. what sould

How can I Export 9i data in 8 ? I mean when I Export from 9i and Import in 8, I face errors. what sould I do and my pc is not in Network. Read answers (9)

What are the different tools available for hot backups.Read answers (8)

What are the steps involved in Database Shutdown ? Read answers (7)

What is recovery catalog? Why do you need this?

Backup Mode Redo Generation

Why more redos are generated when the database is in begin backup mode? Read answers (6)

What is Restricted Mode of Instance Startup ? Read answers (5)

144. What is On-line Redo Log? Read answers (5)

What is Log Switch ?

The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch. Read answers (5)

What are the steps involved in Instance Recovery ?

Rolling forward to recover data that has not been recorded in data files, yet has been recorded in the on-line redo log, including the contents of rollback segments. Rolling back Read answers (5)

What do you mean by MEDIA RECOVERY..? Read answers (5)

How much time will it take a hotbackup if the batabase size is 60Gb Read answers (5)

How do you verify and validate the Datafile which is backed up? Read answers (5)

Corrupted Archieve Logs

There are 100 datafiles, numbered from 1 to 100. File number 10 is deleted and it has 500 MB of data. The database is working in archive log mode. How can be the database recovered ? While doing recovery Read answers (5)

What is Archived Redo Log ? Read answers (4)

If an oracle database has crashed. How would you recover the transactions that are not in the backups? Read answers (4)

You have taken a manual backup of a datafile using o/s. How RMAN will know about it? Read answers

(4)

RMAN Calatalog Database

Is it possible to take backup of catalog database using RMAN? If Yes, How? Read answers (4)

What is mean live dead lock? How to prevent it.? Read answers (3)

How to recover control file without backup and without 'create control file' command? Read answers (3)

You can take a coldbackup from solaris and use it on windows NT?A) YesB) Yes, if RMAN backup performed

You can take a coldbackup from solaris and use it on windows NT?A) YesB) Yes, if RMAN backup performed from NT serverC) Yes, after running RMAN convertD) No Read answers (3)

Types of Backup

What are different types of backup? (Hot, Cold, logical, Physical) Explain in detail. Read answers (3)

What is configure command? When do you use this? Read answers (3)

Diagnose the problem

Suppose you have two servers, one at 2nd floor and another on 3rd floor,If you have to configure the server on 3rd floor while sitting on 2nd floor how Will you do it? Read answers (3)

What is Mirrored on-line Redo Log ?

A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.

How do you setup a Physical stanby for Oracle 9i in Linux ? Read answers (2)

How to take cold and hot backup using UNIX Read answers (2)

This Question is related to Backup Questions

Oracle log switch question

My database is in archive mode. when log switch occur that log file is archiving . what happens when log switch occurs

Read answers (2)

RMAN Backup

Detail script of backup using RMAN in both mode catalog and nocatalog

Read answers (2)

Inconsistant Backup

Why is the header of datafile and information of control file different at the time of inconsistant backup? Read answers (2)

Background Processes

Which processes will run when we take online backup? Read answers (2)

Corrupted OS

How will you recover database if OS is corrupted. Assume you have hot backup on same hard drive. Read answers (2)

What are the different modes of mounting a Database with the Parallel Server ?

Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode Read answer (1)

This Question is related to Parallel Server Questions

What is Partial Backup ?

A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down. Read answer (1)

What is the disk migration?what isthe steps involved in diskmigration?

Whats the Concept of Flashback Query?

Whats the Concept of Flashback Query in Oracle (10g in particular) and how is it beneficial? Read answer (1)

What is the difference between ntbackup and third party backup Read answer (1)

Authentication Methods for DBAs

What are the steps to creating a password authentication file? Read answer (1)

Table Export

How we you identify if a table was exported in its life time? Read answer (1)

http://www.geekinterview.com/Interview-Questions/Oracle/Backup-and-Recovery/