********************************************************************************************************************************************************************* Oracle Trobulshoting Questions & Answers ********************************************************************************************************************************************************************* 1) Will RMAN take backups of read-only tablespaces? No 2) Which log would you check if a database has a problem? Alert log 3) How can you check the structure of a table from sqlplus? DESCRIBE or DESC1. 4) What command will you start to run the installation of Oracle software on Linux? runInstaller 5) What should you do if you encounter an ORA-600? Contact Oracle Support 6) Where can you find official Oracle documentation? tahiti.oracle.com 7) What is the address of the Official Oracle Support? metalink.oracle.com or support.oracle.com 8) What file will you use to establish Oracle connections from a remote client? tnsnames.ora 9) How can you check if the database is accepting connections? lsnrctl status or lsnrctl services 10) In which Oracle version was Data Pump introduced? Oracle 10g 11) What is the most important requirement in order to use RMAN to make consistent hotbackups? Your database has to be in ARCHIVELOG mode 12) Can you connect to a local database without a listener? Yes, you can 13) How can you view all the users account in the database? SELECT USERNAME FROM DBA_USERS; 14) How can you view all the current users connected in your database in this moment? SELECT COUNT(*),USERNAME FROM V$SESSION GROUP BY USERNAME; 15) Is it possible to backup your database without the use of an RMAN database to store thecatalog? Yes, but the catalog would be stored in the controlfile 16) Will RMAN take backups of read-only tablespaces? No 17) How to clear database buffer cache ,redolog buffer cache and shared pool. shared Pool-ALTER SYSTEM FLUSH SHARED_POOL; database buffer cache- 15) What are the scripts run at the time of manual daataabase creation. 1.Catalog.sql 2.catproc.sql conn / as sysdba 3.pupbld.sql 16) How to check how many databases are runing on OS level. ps -ef|grep pmon 17) How to open database in archive log mode. shutdown immediate alter database nomount alter database mount alter database archivelog; alter database open 18) Where Alert logfile locted. Background_dump_dest 19) What is being written into the Redo Log Files? Redo log records all changes made in datafiles. 20) What are the network file in oracle. sqlnet.ora tns.ora,Listener.ora 21) What is password file; Password file is the authitication secure file to login to dataabse if it is present on the server .It is not case sensitive. 22) How to resize the size of tablespace. Alter database datafile '/u01/app/oracle/oradtaa/db01.dbf' resize 20m; ---------------With Backup------------- If datafile is deleted how to recover it by backup or without backup; RMAN>restore datafile 2; RMAN>Recover datafile 2; (Backup) RMAN>sql'Alter database open'; ------------Without Backup------------- 1.tablespace offline. 2.alter tablespace add datafile /u01/app/oracle/oradtaa/db01.dbf' size 20m; 3.recover datafile 2; 4.alter tablespace online 22) Can we create dictionary managed tablespace in 10g/11g? Yes, but SYSTEM tablespace also should be dictionary for that 23) Can we take SYSTEM and SYSAUX tablespace offline? We can take SYSAUX, but not SYSTEM 24) Can we make temporary tablespace read only? Not possible 25) Is it possible to reduce the size of datafile? Yes, but data level should not have been crossed the limit 26) If a transaction is running and DBA has taken particular rollback segment offline. What happens to that transaction? It will start using SYSTEM tablespace 27) How to convert dictionary managed permanent tablespace to temporary tablespace? It is not possible 28) You are trying to add a datafile, but its not allowing and saying you cannot add it. What might be the reason? Either MAXDATAFILES controlfile parameter or DB_FILES parameter in pfile might have reached its max value 29) Your standby database was out of reach because of network issue. How will you synchronize it with primary database again? Data Guard automatically resynchronizes the standby following network or standby outages using redo data that has been archived at the primary. 30) Can we use same target database as catalog? No, The recovery catalog should not reside in the target database (database should be backed up), because the database can’t be recovered in the mounted state. 31) You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week/day old and don’t have backup of this (newly created) datafile. How do you restore/recover file? Create data file and recover datafile. 32) In catalog database, if some of the blocks are corrupted due to system crash, How will you recover? using RMAN BLOCK RECOVER command 33) There was a media failure. How can you find which files you must recover? By querying the V$RECOVER_FILE view, which lists all files that need media recovery. 34) Can one FRA directory be used for more than one database? You can use the same flash recovery area for as many databases as you want. The RMAN backup process will create a subdirectory called , the same name as the database you are backing up. 35) Can Recovery Catalog database also be shutdown from RMAN prompt like TARGET Database can be done? All the shutdown and startup commands applies only to the target database. You can’t start and stop the recovery catalog instance from RMAN. The only way to start up and shut down the recovery catalog instance is by connecting to the recovery catalog database as the target database and by issuing the relevant commands to start or stop the instance. 36) Does using recovery catalog means RMAN won’t use the control file to store information? No. Even when you choose to use a recovery catalog, backup information will continue to be stored in the control file as well by default. 37) You want to move your recovery catalog from one database to another. How you can do it? a) Create a new recovery catalog in the target database. You don’t need to register any database to it b) Use the import catalog command in RMAN after connecting to the target database: 38) Is there a way to force rman to use these obsolete backups or once it is marked obsolete? As per my understanding it is just a report, they are still there until you delete them. 39) Can I use the same snapshot controlfile to backup multiple databases(one after another) running on the same server? This file is only use temporarily like a scratch file. Only one rman session can access the snapshot controlfile at any time so this would tend to serialize your backups if you do that. 40) Why does not oracle keep RMAN info after recreating the controlfile? Creating the new controlfile from scratch how do you expect the create controlfile to "make up" the missing data? that would be like saying similarly we have drop and recreated my table and now it is empty similarly here recreating from the scratch means the contents there will be naturally will be gone. Use the rman catalog to deal this situation. It is just a suggestion. 41) Can RMAN write to disk and tape Parallel? Is it possible? Rman currently won't do tape directly, you need a media manager for that, regarding disk and tape parallel not as far as I know, you would run two backups separately (not sure). May be trying to maintain duplicate like that could get the desired. 42) You recreated the control file by “using backup control file to trace” and using alter database backup controlfile to ‘location’ command what have you lost in that case? You lost all of the backup information as using backup controlfile to trace where as using other ALTER DATABASE BACKUP CONTROLFILE to ‘D:\Backup\control01.ctl’. All backup information is retained when you take binary control file backup. 43) If a backup is issued after “shutdown abort” command what kind of backup is that? It is an inconsistent backup. If you are in noarchivelog mode ensure that you issue the shutdown immediate command or startup force is another option that you can issue: startup force->shutdown abort; followed by shutdown immediate; 44) Can I have archivelogs and datafile backup in a single backupset? No. We can not put datafiles and archive logs in the same backupset 45) Can I have datafiles and contolfile backup in a single backup set? Yes, If the controlfile autobackup is not ON then RMAN takes backup of controlfile along with the datafile 1, whenever you take backup of the database or System tablespace 46) Can I regulate the size of backup piece and backup set? Yes! You can set max size of the backupset as well as the backup piece By default one RMAN channel creates a single backupset with one backup piece in it You can use the MAXPIECESIZE channel parameter to set limits on the size of backup pieces You can also use the MAXSETSIZE parameter on the BACKUP and CONFIGURE commands to set a limit for the size of backup sets 47) Can I take RMAN backup when the database is down? No! You can take RMAN backup only when the target database is Open or in Mount stage It’s because RMAN keep the backup metadata in controfile Only in open or mount mode controlfile is accessible 48) Do I need to place the database in begin backup mode while taking RMAN inconsistent backup? RMAN does not require extra logging or backup mode because it knows the format of data blocks RMAN is guaranteed not to back up fractured blocks No extra redo is generated during RMAN backup 49) If an oracle database is crashed? How would you recover that transaction which is not in backup? If the database is in archivelog we can recover that transaction otherwise we cannot recover that transaction which is not in backup. 50) Can you backup the online redo log files? We cannot backup the online redo logs; online redo logs are protected by multiplexing and optionally by archiving 51) Can we take incremental backup without taking the full backup? No, full backup should be taken before the incremental backup. 52) Can you take offline backup with RMAN? Yes, you can take offline backup with RMAN 53) Can you us RMAN without recovery catalog? Yes, RMAN can be used without reocvery catalog. 54) If the database is running in the noarchivelog mode, then which type of backups can you take? In this case, you can take only cold backups 55) Can you take online backup of a control file? Yes, by using the following statement: Alter database backup controlfile to '' or trace. 56) Can you backup the online redo log files? We cannot backup the online redo logs; however online redo logs are protected by multiplexing and optionally by archiving. 57) Can you take online backups if the database is running in the NOARCHIVELOG mode? No 58) Can we take incremental backup without taking the full backup? No, full backup should be taken before the incremental backup. 59) Can you track changes to blocks during hot backup? Oracle database 11g offers block change tracking feature. It records the information in block change tracking file. RMAN uses this information to determine specific blocks to be backed up again without re-scanning the entire datafile. 60) Can you take the backup of log files if the database is running in the ARCHIVELOG mode? No, you cannot take the backup of log files if the database is running in the ARCHIVELOG mode. 61) How can you manage storage options during export or import? You can manage storage options during export or import by using the compress option. It ensures that the storage is compressed whenever the parameter value is Y. 61) Can you use RMAN without recovery catalog? Yes 62) How to check database is in archivelog mode or not.? sql>archivelog list; sql>select log_mode from v$database 63) Can we use backup set create by rman with any other utility.? NO 64) Can we take image backup in rman how.? Yes, rman>backup database as image copy device type to disk 65) Can we take online rman backup if database is runing in noarchivelog mode.? No database wants to be in archivelog mode. 66) Should u place catalog in same database.? No 67) Loss of controlfile .how will u recover it. alter database recover using backup controlfile untill cancel; ========================================================================THE END=============================================================================== -3