Monday, 17 February 2014

SOME INTERVIEW QUESTIONS





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


http://img1.blogblog.com/img/icon18_wrench_allbkg.png




1)    How to check privileges granted on a particular table?


2)    How to check permissions given to a particular user?


3)    How to Resize a datafile?


4)    How to see the Installed product details of Oracle Database?


5)    Find out the tables that are ANALYZED in the Database?


6)    Show all Active Users in the System?


7)    How to identify what a current user is doing?


8)    How will I find the Count of Tables available in the Database?


9)    How will we see the available ALL INDEXES in a database?


10)Show ALL TABLES available in a database?


11)Identify the Space Used by Tablespace?


12)Identify the Space Used by Users/Schema in a database? 


13)How to find the OS Version, Database Version in Unix and Windows?


 


14)Identify the Reads and Writes of Datafiles in a Database?


15)Identify the SEGMENTS that are getting CLOSE TO MAX_EXTENT values?


16)Identify the SEGMENTS that are getting CLOSE TO RUNNING CONTIGUOUS FREE SPACE?


17)How will we display Archived Log Information? v$archive_dest


18)How to see the HISTORICAL ARCHIVED LOG information from a Control File? v$archived_log


19)How to find current archived log destinations? Archive log list


20)I want to know the details of BACKUP’s of ARCHIVED LOG files?


21)How to check the ALL ONLINE REDO LOG GROUPS and MEMBERS in a database?


V$log-CURRENT STATUS, v$logfile-group&member details,V$log_history-history of logs


22)Show ALL DATAFILES of a TABLESPACE? V$datafile –ts#=v$tablespace-ts# or dba_data_files


23)How to find SCHEMA / USER SIZE in a database? Dba_segments


24)What is the Size of QOUTA allocated for a particular user/schema? Dba_ts_quotas


25)How to find TABLESPACE Usage in %, Blocks Used and Blocks Empty?


Usage%:- sum(sys.seg$.blocks)*sys.ts$.blocksize*count(distinct(sys.file$.file#))*100


Total Available:- sum(sys.file$.blocks)*t.blocksize


Total Used:- sum(sys.seg$.blocks)*sys.ts$.blocksize


Total Space Remaining:- Total Available in sys.file$.blocks minus total used in sys.seg$


26)Display all tables in current schema along with TABLESPACE and TABLE SIZE in GB?


27)How to find ROWS spread across different TABLESPACES and DATAFILES? How many rows are there in each DATAFILE for EACH TABLE?


28)How to Recover Data after the loss of Redo Log Files in Archive mode & NoArchiveLog mode?


29)How to Recovering After the Loss of All Members of an Online Redo Log Group?


30)How to recover table from DROP/DELETE/TRUNCATE commands in Oracle Database?


Flashback table rman.rman_table to before drop;


Flashback table rman.rman_table to timestamp (systimestamp –interval ‘05’ minute);


31)How to recover database when ALL DATAFILES lost in User Managed Databases?


32)How to recover DATAFILE when there is no Backup available?


33)How to Restore and Recover database to a NEW HOST using RMAN?


34)How to SKIP a Tablespace for Restore Operation in RMAN?


35)Explain about Restore and Recover database in NOARCHIVELOG Mode?


36)Explain Restore and Recovery of INDIVIDUAL TABLESPACES / DATAFILES?


37)How to Restore SPFILE from a recent Backup?


38)How to Restore an SPFILE from Older backup more than 7 days / upto specific days?


39)Explain about Restore POINT and FLASHBACK DATABASE?


40) Write a script to TRACK SPACE UTILIZATION of your Database over time?


41)Cold backup procedure and Hot Backup Procedure explanation?


42)How to check ALERT LOG in oracle 11g?


43)What will happen if Oracle unable to write ALERT LOG, CORE DUMP OR TRACE FILES?


44)Stop ALERT NOTIFICATION for a specified period of time as when you plan to bring downtime for the database?


45)What is Server-Generated Alerts like Threshold Alerts, Event Based Alerts?


46) How to view and change Threshold Settings for Threshold Alerts?


47)How to notify or send email Event in oracle from EM?


48)What are the mandatory Variables to be declared for LOG_ARCHIVE_FORMAT?




50)Enable Archive Log Mode for RAC Database?


51)Solution for ORA-000257: Archive Error. Connect Internal Only, Until Freed?














58)RMAN-03002, RMAN-06059, ORA-19625 and ORA-27037 during RMAN archivelog backup. The problem happened because Oracle archivelog files were deleted at OS level.






61)I mounted remote location jupiter:/export/home/oracle on my local server as /export/home/oracle/remote and then I wanted to take RMAN backup on the mapped drive but it fails with ORA-19504: and ORA-27054.


62)How to take RMAN backup on a Remote Location?


63)How to extract data from a CORRUPT table?


64)WHERE Clause Fails with ORA-01578(Data block corrupted). This problem is caused due to a corrupted block in one or more blocks belonging to an index type of segment. Since the corrupted block does not belong to the table, some statements could be executed successfully if the information is retrieved via full tablescan.


65)How to handle Block Corruptions in ORACLE and How to Recover Data from Corrupted Blocks.


66)What is the solution for Solution of ORA-600 [4194] or ORA-600 [4193]. The problem ORA-600[4194] or ORA-600[4193] occurs when there is mismatch detected between Redo records and rollback (Undo) records.




68)Oracle data pump import fails with ORA-39002 and ORA-39322 during importing transport tablespaces?












74)ORA-07445: exception encountered: core dump [nstimexp()+28] [SIGSEGV] [ADDR:0x58] [PC:0x107D6071C] [Address not mapped to object] []


75)ORA-07445: exception encountered: core dump [osnsgl()+625] [SIGSEGV] [Invalid permissions for mapped object] [0x000000068] [] []






78)Database Crash With ORA-07445 [_ndoprnt()+4]. Sorry, no swap space to grow stack for pid 6048 (oracle)










83)Drop user hangs or it returns ORA-01940. SESSION HANGS WHEN TRYING TO DROP A USER


84)Drop user cascade takes more than 1 day. Dropping a simple user takes hour after hours and after 1 day it is not completed yet






87)ORA-00054, ORA-04022 while gathering table statistics. The following errors are reprted when executing DBMS_STATS procedures simultaneously in order to collect database tables statistics.


88)ORA-13503: Creating SYSAUX tablespace with invalid attributes. While creating SYSAUX tablespace manually it fails with error ORA-13503.


89)What is an Oracle database instance?  Also, showing the steps in creating an Oracle instance?


90)What is OEM (including showing some of its contents)?  Also explain its advantages.


91)What is OFA?  Also, what is the reason and purpose of adopting OFA?


92)What are the usages of the control and redo log files?  What kind of relationship exists between these two types of file.


93)Based on the logic structure (table, segment, extent, & data block) discussed in the textbook, explain how can a database table (e.g. CUSTOMER table which stores all customer data) be split and stored within the logic structure.


94)When creating an Oracle database table, the storage settings are optional, why?


95)What is the advantage and reasoning to include storage settings when creating a new database table?


96)What is a “role”?  What are the advantages of creating and using roles?






99)What to do when we found in trace fil WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!.


100)              Name the different types of Constraints


101)  What are the DDL statements?


102)  What are the Transaction Control (TC) statements?


103)  What are the Session Control statements?


104)  What are the System Control statements?




106)  While connecting to oracle database it fails with ORA-12532: TNS:invalid argument like below.


107)  It is used LDAP, sqlnet.ora entry is NAMES.DIRECTORY_PATH = (LDAP) and Tnsping fails with TNS-03505: Failed to resolve name or TNS-12154 / ORA-12154: TNS:could not resolve service name.




109)  ORA-28547 connection to server failed, probable Net8 admin Error


110)  ORA-12154: TNS:could not resolve the connect identifier specified














117)  ORA-00205: error in identifying control file. Whenever you try to start your oracle database instance or mount your database it fails with ORA-00205 error message


118)  ORA-00600: internal error code, arguments: [2141]. While starting up or shutting down the database it fails with oracle error message ORA-00600


119)  ORA-01665: control file is not a standby control file. While recovering standby database, using command alter database recover managed standby database disconnect from session it fails with message ORA-01665


120)  Recover database after only lose of all controlfiles. You have lost all your current and backup of controlfile. You have avaiable your current data files. You have available your online redo log files.


121)  MAXDATAFILES , DB_FILES parameters and ORA-00059. Whenever I try to create tablespace it fails with error message, ORA-00059


122)  ORA-01667: cannot add any more tablespaces: limit of exceeded. If your database version is higher than 8 suppose 10.2g then when MAXDATAFILES of your control file reached then MAXDATAFILES parameter would expand automatically unless you hit bug. You can see the post here,
New Feature of 10.2g Eliminate Control file Recreation
However if you are before 8 then while creating any tablespace and while adding a datafile to a tablespace you may get ORA-01667: cannot add any more tablespaces: limit of exceeded




124)  Both Data files and Online Redo logs are in tact


125)  Data files are intact but Online Redo logs are lost


126)  Data files are lost but online redo logs are available


127)  Both Data files and redo logs file are lost


128)  File size is not a multiple of logical block size ORA-00202,ORA-27046.  Whenever you tried to start the database the database failed to mount


129)  How to Restore the Controlfile from Backup. If you loss or if your all copies of control file is corrupted and if you have backup of your control file then it is required to restore your control file from your backup


130)  New Feature of 10.2g: Eliminate Control File Re-Creation. Before Oracle 10.2g if we need to change the limit of MAXLOGFILE, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES then the possible solutions is either RE-create new controlfile or create a new database. But from Oracle 10.2g all sections of the control file are now automatically extended when they run out of space. This means that there is no longer a requirement to re-create the control file when changes in the configuration parameter the MAXLOGFILE, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES are needed


131)  kccrsz: denied expansion of controlfile section 9 by 65535 record(s). The machine did not respond properly and in the alert log you the got the following. kccrsz: denied expansion of controlfile section 9 by 65535 record(s) the number of records is already at maximum value (65535).


132)  ORA-01450: maximum key length (3215) exceeded. Whenever I try to rebuild index online then it fails with message ORA-00604: error occurred at recursive SQL level 1 along with ORA-01450: maximum key length (3215) exceeded.


133)  How to determine OS block size for windows or unix. In many times you probably have heard about set the oracle db block size as a multiple of the OS block size. But whenever you ask how I can determine or find OS block size for windows or Unix then many one get stopped. In fact this question is OS related.


134)  Overview of Extents and when extents are allocated, When Extents are de-allocated.


135)  Playing with Oracle data block size. I have experiment of oracle data block size with 8k and 16K. I performed DML operation against both 8k and 16k data block size. I created two tables table_8k under 8k tablespace tbs_8k and table_16k under 16k tablespace tbs_16k. The summary of the experiment is bigger data block cause more time while update but less time while insert operation.






138)  DB_BLOCK_SIZE, DB_nK_CACHE_SIZE and BLOCKSIZE. First I want to say about BLOCKSIZE cluase in oracle. The BLOCKSIZE cluase is used to specify the block size for the tablespace. If you don't specify this clause while tablespace creation then standard that is default blocksize is used which is specified by parameter DB_BLOCK_SIZE.


139)  When datafile bytes size greater than maxbytes. From dba_data_files we can check the size of the datafile, the maximum size of the datafile it can grows if AUTOEXTENSIBLE option is set and their status with many other fields.




141)  ORA-01295: DB_ID mismatch between dictionary USE_ONLINE_CATALOG and logfiles. While doing remote mining in the mining database through logminer START_LOGMNR procedure fails with ORA-01295. May be you want to analysis redo log files of the production database in the testing environment.












147)  Archiving not possible: No primary destinations. Today I got a very interesting problem. The developer told me that they can't connect to database. It says ORA-00257: archiver error. Then I log on to the database and it was ok. I asked developer it is fine. After some time developers again asked me they are having the same problem. I then look for alert log and saw the following stack of message.




149)  Table, View, Tablespace Creation Script in Oracle, In order to get definitions of view, table ,tablespace etc we can use GET_DDL function of DBMS_METADATA package.






152)  Causes and Solution of ORA-00376: ORA-01110:, Whenever I try to access a objects it fails with errors.


153)  Troubleshooting ORA-00942: ORA-04043: ORA-00942: table or view does not exist, ORA-04043: object %s does not exist










158)  Error ORA-01555 on Active Data Guard Standby Database We have active data guard setup in our 11.2.0.1 version of Oracle database. It is upgraded to version 11.2.0.2 and now we see this ORA-1555 on the Standby Database. We can't logon to database with system, dbsnmp or other database users. Only sys as sysdba is working. Also we have investigated that we are running on system's undo Tablespace, - and not the one which has been created for undo.










163)  Primary DB freezed with waited too long for a row cache enqueue lock. On Oracle database 10.2.0.3.0 Data Guard with Broker configuration whenever there is an attempting to restart the standby in read only


164)  While startup standby database it fails with ORA-01154. Whenever you try to open or shutdown a standby database it fails with error ORA-01154


165)  ORA-01665: control file is not a standby control file. While recovering standby database, using command alter database recover managed standby database disconnect from session it fails with message ORA-01665: control file is not a standby control file like below.




















175)  impdp fails with ORA-39174 or ORA-39176. You have transparent data encryption feature enabled in your database. You took your dump with expdp utility with the ENCRYPTION_PASSWORD parameter. Now whenever you try to import your dump without ENCRYPTION_PASSWORD parameter you will get ORA-39174 error.


176)  How to export table containing encrypted columns. Let's first check whether database has any encrypted columns or not by querying dba_encrypted_columns view


177)  EXP-00107: Feature (COLUMN ENCRYPTION) of column is not supported. While exporting encrypted table using oracle export utility it fails with EXP-00107 error and encrypted table is not exported.


178)  ORA-39097, ORA-39065, ORA-31643, ORA-19510, ORA-27045 Linux-x86_64 Error: 5. Oracle data pump export operation on 10.2.0.4 database into CIFS file system fails with error ORA-39097, ORA-39065, ORA-31643, ORA-19510, ORA-27045 Linux-x86_64 Error: 5.


179)  ORA-39029, ORA-31671, ORA-39078 received during Oracle Parallel data pump Import. Oracle data pump import operation is working fine without PARALLEL parameter. But whenever we specify PARALLEL parameter within the import statement it fails with this error.


180)  Data pump fails with ORA-31626: job does not exist. Data pump export jobs failed with following errors:


181)  ORA-31626 ORA-31650 while using Oracle DataPump export in RAC database. ORA-31626: job does not exist
ORA-31650: timeout waiting for master process response


















190)  EM Daemon is not running. On windows whenever I try to start my dbconsole in one time during process it shows me message EM Daemon is not running.


191)  Authentication failed!null. After upgrading the database containing dbconsole's repository whenever you try to logon to Enterprise Manager or DBCONSOLE it succeed but return with error


192)  How to Change DB Control Http Port




194)  An Stack of Problems while creating Repository using emca. In my database I tried to create repository and stopped several times while creating repository. I spend a significant amount of time to create repository and after analysis I got the following


195)  Running EMCA Fails To Accept the DBSNMP Password. Today when I tried to create DB Control on my computer I got an interesting problem.








199)  Java.lang.Exception: IOException in sending Request. this problem will appear if you just change the Time Zone of your system.


200)  You must have CREATE TARGET privilege to perform this operation. In the Enterprise Manager main page (connected with SYS as SYSDBA or as SYSTEM), when I click on performance tab, it gives me following notification




202)  In EM connection fails with ERROR: NMO not setuid-root (Unix-only). This problem is notified on Unix system only.
Whenever you try to connect to enterprise manager after giving host username and password as prompted it fails with message
HostPassword - Connection to host as user oracle failed: ERROR: NMO not setuid-root (Unix-only)




204)  Does oversize of datatype VARCHAR2 causes performance problem. From the beginning of learning Oracle SQL you have possibly heard that in case of VARCHAR2 datatype it allocates space exactly what it needs. So if you allocates 4000 bytes of VARCHAR2 data type and database needs 10 bytes only then exactly 10 bytes are allocated






























219)  Export fails with ORA-24324 ORA-07445: exception encountered: core dump [ACCESS_VIOLATION]. Database export operation fails with ORA-24324 and ORA-07445
























231)  DataPump Export (EXPDP) Client Gets UDE-8 ORA-31626 ORA-39086. Doing Oracle data pump export operation from client side reports error UDE-00008, ORA-31626, ORA-39086 and ORA-06512.


232)  Export fails with ORA-01406: fetched column value was truncated. Exporting oracle 11g database using 10g exp utility fails with ORA-01406 errors


233)  exp fails with EXP-00023 and expdp fails with ORA-31631, ORA-39161, It is needed to export/import full database. To export full database, "EXPORT FULL DATABASE" privilege is granted and to import full database "IMPORT FULL DATABASE" privilege is granted. Now while doing full database export/import, exp fails with EXP-00023 and expdp fails with ORA-31631: privileges are required , ORA-39161: Full database jobs require privileges




235)  Version Query Pseudocolumns, The version query pseudocolumns are only valid for flashback version query which are discussed on Flashback Version Query.


236)  ORA-01466: unable to read data - table definition has changed, Whenever you tried to flashback of a table immediately after creating table and inserting data into it, or you tried to flashback of the table to a time or to a SCN before creating it then flashback table or flashback query fails with error message,


237)  ORA-08189: cannot flashback row movement is not enabled, Whenever I tried to Flashback Table feature of oracle the following error occurs.




239)  Flashback ON fails with ORA-38706 and ORA-38714, Whenever you tried to enable your database flashback feature it failed with errors.


240)  How to get back dropped Tablespace using Flashback Database, It needs to remember that if you drop your tablespace with including datafiles option then it is not possible to get your tablespace back with flashback database. While dropping if you use DROP TABLESPACE INCLUDING CONTENTS option then it is possible to get back tablespace.


241)  How to restore the old data using flashback query, My intention is , I want to get back past data of database after erroneously updated and committed.


242)  Limitation of Flashback Database, The Flashback Database allows you to get back the entire database to a specific point-in-time


243)  Flashback Database To The Right of Open Resetlogs, In some cases, you may need to return the database to a point in time in the parent incarnation, later than the SCN of the OPEN RESETLOGS at which the current incarnation path branched from the old incarnation.


244)  Flashback Database to Undo an OPEN RESETLOGS, Suppose you have opened your database with OPEN RESETLOGS option after performing flashback feature. And after that you have made unwanted changes in your database.


245)  Performing Flashback Database, Before going any production upgrade to database we can make a guaranteed restore point to database and if any wrong then we can get back to the restore point state. Guaranteed restore point always ensure that we can get back data to our restore point creation time.


246)  ORA-00439: feature not enabled: Flashback Database, While trying to enable flashback feature the error comes.


247)  Restore Point and Flashback Database. Flashback database enables us to get back our database to a prior time


248)  How to Enable Flashback Database, To enable flashback database the following operations is needed


249)  Flashback Table and Flashback Drop, The FLASHBACK TABLE statement enables users to get a table to a previous point in time. It provides a fast, online solution to get back a table that has been accidentally modified or deleted by a user or application. It eliminates the DBA to perform more complicated point in time recovery operations.




















259)  CSSCAN fails with ORA-00600, CSS-00152, CSS-00120, While running csscan it fails with error message ORA-600, CSS-00152: failed to enumerate all tables and CSS-00120 as below


260)  CSSCAN fails with error while loading shared libraries: libclntsh.so.10.1, While running csscan in order to check all character data in the database and tests for the effects and problems of changing the character set, it fails with error while loading shared libraries: libclntsh.so.10.1: cannot open shared object file as below








264)  ORA-39014 ORA-39029 ORA-31672 One or more workers have prematurely exited, Whenever you tried to import data into the database it failed with error as below


265)  How to Import data without Dumpfile-Network Mode Import, It is possible to import data in a database without the dump file. From network the data will be retrieved from one database and then import that data back to the target database. There are no dump files involved.


266)  Import Oracle Data from Anywhere, Suppose, I have server machine named "NEPTUNE". In the server machine under /oradata2 directory there is a dump file named net.dmp. However to run import operation we need not connect to server "NEPTUNE" and then perform import operation. From any machine which have data pump import client can do the operation.




268)  Verify Physical Data Structure in Oracle












































290)  How to uninstall Oracle 11g , Uninstall Oracle software 11.2g




292)  How to Identify OS or Oracle 64 bit or 32 bit on Unix










297)  How to discover DBID




















307)  ORA-19566: exceeded limit of 0 corrupt blocks for file, While I am taking a database backup it halt with returning an error,




309)  How to disable SELinux, Before going to directly disabling SELinux let's have an idea about what SELinux is. SELinux indicates Security Enhanced Linux which is a security patch applied to Linux kernel. When it is enabled in the kernel it follows some principle of least privilege. It is an implementation of mandatory access control using Linux Security Modules.






312)  While startup listener it fails with HPUX Error: 29: Illegal seek, While issuing "lsnrctl start" command on HP-UX it fails with HPUX Error: 29: Illegal seek error like below


313)  TNS-01169: The listener has not recognized the password, The TNS-01169 error occurred because security feature is enabled for listener i.e password is set in listener.ora file but lsnrctl command is issued without proper authentication






316)  The listener supports no services, Whenever I issue lsnrctl status command it says "The listener supports no services" and no one from outside could not connect to database through listener


317)  Listener Hangs, Child listener process remains persistence, Some days ago in our database server we got a problem regarding listener issue. Our TNS Listener hangs. Below is the problem symtompts.


318)  ORA-12541: TNS:no listener, For newbie users they found ORA-12541 and search here and there to solve the problem. But it is one word about the error which it says no listener and think next what to do. Never mix this error with another error like ORA-12514: which are different and mutually exclusive.


319)  ORA-12514: TNS:listener does not currently know of service requested in connect descriptor, I will try to show how efficiently we can avoid error ORA-12514. My suggestion is whenever you get this error forget about tnsnames.ora and other stuff related to it. Start fixing problem step by step.


320)  How to Enable Listener Logging and Tracing, I will try to show two different ways to enable logging and tracing of listener.ora file. These logging and tracing helps you to identify the network problems and troubleshooting connection problems in oracle.It also help to identify when a client is connected to oracle database.


321)  RMAN login fails with ORA-12638, RMAN login to the target database fails with the following errors


322)  CONFIG: Error uploading configuration data to the repository while, While configuring dbconsole it fails with error message oracle.sysman.emdrep.config.ConfigurationException: FATAL Configuration Exceptions.












328)  How to Check Your IP Address in Windows and Unix


329)  Who is connected to your windows machine using which port


330)  How to Monitor traffic between nodes, With the command netstat -i we can monitor the traffic in the network nodes. The sample output of netstat -i is below


331)  Types of Operators in Orace? Concatenation, Arthimetic, User-Defined, Hierarchial, Set Operators?








335)  Database Objects in Oracle, If we look for database objects in oracle then there comes two types of objects, one is schema objects that are associated with a particular schema or in other word they are owned by a database user. And the other is nonschema Objects that are not reside in a particular schema


336)  How to Rename a User Through Oracle, There is nothing straight forward command in oracle to rename a user. You have to do it through several steps. In following I have shown it.


337)  Required users and groups while installing oracle, Drop Table Command, Default Tablespace in Oracle




339)  Working an object that resides on multiple datafile, An object can span in multiple datafiles within a single tablespace. In this case if I make one datafile offline then the contents within that datafile will be affected, other datafile is online and can be possible to query which may return rows with errors. To illustarte this scenario I will make two datafiles inside a tablespace.




















349)  RMAN-06172: no autobackup found, While performing disaster recovery I get the error RMAN-06172 as below




351)  OUI-10020: A write lock cannot be obtained






354)  Online Redefinition fails with ORA-23540: Redefinition not defined or initiated, While doing online redefinition on a table dbms_redefinition.copy_table_dependents fails with error ORA-23540










359)  ORA-25153: Temporary Tablespace is Empty, Whenever you try to access temporary tablespace it fails with error ORA-25153. Suppose I want to get the user creation script of user ARJU but it fails with following message


360)  Logminer fails with ORA-01284, ORA-00308, ORA-27047, Whenever you try to run the logminer in the mining database (different from source database. Source database is one in which redo or archived redo logs are generated and mining database is one in which you try to analysis those logs) to add logfile using DBMS_LOGMNR.ADD_LOGFILE it generates error ORA-01284, ORA-00308, ORA-27047




































378)  java.util.zip.ZipException: reading zip file central directory failed, While applying opatch, from the front end I see following message.




































396)  Troubleshoot ORA-06512: at line, ORA-06512 is a common error faced by Oracle DBA, programmers as well as end users. ORA-06512 does not identify the root cause of the problem, rather it only prints the line number where the errors or exception happened. So just before ORA-06512 there will be additional error which we may need to investigate. If the errors come from any function or package or package body or procedure then with ORA-06512 there exists the name of the function or package or package body or procedure as well as the line number of those objects where error is occurred.
 


 






403)  Restore operation fails with RMAN-11003 ORA-01511 ORA-01516, I am performing disaster recovery operation. The new host path are different than from original host. So for the datafile I used SET NEWNAME .. clause. And for the online redo log file I used SQL "ALTER DATABASE RENAME FILE .. " clause to rename the online logfiles to a valid location on to a new host. I have already ran the restore srcipt in previous but it fails to rename third online redo logfiles due to invalid location. And after fixing location whenever I ran the script again it fails with serveral RMAN and Oracle error


404)  Recover database after missing online redo logs and all controlfiles




406)  Crash Recovery Fails With ORA-27067, After performing shutdown abort whenever I try to start my database it fails to perform crash recovery. Below is from alert log.


407)  Allow Recovery to Corrupt Blocks, Whenever during recovery database finds corrupt block then recovery will automatically stops. In order to do recovery with skipping corrupt blocks run the RECOVER command with the ALLOW n CORRUPTION clause, where n is the number of allowable corrupt blocks.


408)  Recovering Through an Added Datafile with a Backup Control File, If database recovery with a backup control file rolls forward through a CREATE TABLESPACE or an ALTER TABLESPACE ADD DATAFILE operation, then the database stops recovery when applying the redo record for the added files and lets you confirm the filenames.




410)  Media recovery can be used to recover from a lost or damaged current datafile, SPFILE or control file




















































436)  Script to Check Schemas with Stale Statistics, This script is an automated way to deal with stale statistics operations that are required to be done as part of manual upgrade OR when reported by DBUA


























449)  ORA-28008: invalid old password, We have a 2 node RAC database. We wanted to change our database password. As it is 2 node Oracle RAC so in the first node I changed the password.






452)  ORA-01012: not logged on, No other user can connect to database. Whenever user with sysdba privilege try to login to database it shows connected but it does not allow to happen any query to database instead it fails with ORA-01012: not logged on as below






















463)  Relocate Datafiles in Oracle Database, Rename/relocate datafiles operation vary based on the log mode of the database. Database may be in archival mode or noarchive mode.




























































































509)  ORA-27100: shared memory realm already exists, when you try to startup your database instance even you issue startup nomount ORA-27100 is reported.












































531)  Information about Temporary Segments






















































































574)  Who is connected to your windows machine using which port






at No comments:
Labels:

Sunday, 16 February 2014

ORA-00210 ORA-00202 ORA-27041 , ORA-326 ORA-1547 , ORA-00279 ORA-00289 ORA-00280 , ORA-00326 ORA-00334

 Database Recovery with lost of all controlfile.(With no trace file backup)
USER MANAGED AND SEE ( RMAN FOLLOW THE AUTO PROCESS in BACKGROUND)

1.
------------------------------------------------------------------------------------------------------------------------------
rman target / catalog /
run
{
ALLOCATE CHANNEL t1 DEVICE TYPE disk MAXPIECESIZE=1G;
ALLOCATE CHANNEL t2 DEVICE TYPE disk MAXPIECESIZE=1G;
CONFIGURE CONTROLFILE AUTOBACKUP format for device type disk to '/u02/backup/control_%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/backup/snapcf_nctscard.f';
backup as compressed backupset filesperset 10 tag="datafiles" format '/u02/backup/DBF_%d_%T_%U.bak' (database include current controlfile channel t1 ) ;
backup as compressed backupset filesperset 100 tag="archivelog" format '/u02/backup/ARC_%d_%T_%U.BAK' (archivelog all not backed up channel t2) ;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup current controlfile format '/u02/backup/CON_%d_%T_%U.ctl'  ;
backup as backupset Tag="Spfile" spfile format '/u02/backup/spfile_%d_%T_%U.spfile' SPFILE ;
sql "alter database backup controlfile to trace as ''/u02/backup/controlfiletrace.txt'' reuse";
}
-----------------------------------------------------------------------------------------------------------------------


2.
-----------------------------------------------------------------------------------------------------------------------
sqlplus / as sysdba
shutdown immediate
take a cold backup copy and paste
startup

select count(*) from scott.emp;
create table scott.abc as select * from scott.emp;
insert insto scott.abc select * from scott.emp;
/
alter system switch logfile;
create table scott.abc_1 as select * from scott.emp;
create table scott.abc_2 as select * from scott.emp;
-----------------------------------------------------------------------------------------------------------------------


3.
-----------------------------------------------------------------------------------------------------------------------
cd /oradata/orcl/

rm -rf *.ctl       # delete all controlfiles

sqlplus / as sysdba
select count(*) from scott.abc;
insert insto scott.abc select * from emp;
create table scott.abc_3 as select * from scott.emp;
insert insto scott.abc_3 select * from scott.emp;
alter system switch logfile;

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


 4.
-----------------------------------------------------------------------------------------------------------------------
NOTE: We received error when we try to create user, tablespace or check any v$datafile, v$log views as followings (SOME Times not show this error):

 SQL> create table abc as select * from emp;

 create table abc as select * from emp
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file:
'/oracle/oracle/oracle/product/10.2.0/db_4/oradata/test/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

 At this point we need to shut database If we try to shut immediate than we will get below error:

 SQL> shut immediate

 ORA-00210: cannot open the specified control file
ORA-00202: control file:
'/oracle/oracle/oracle/product/10.2.0/db_4/oradata/test/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort

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

5.
-----------------------------------------------------------------------------------------------------------------------
NOTE: Copy all the three controlfile from previous & recently backup with OS commands and mount the database (COPY & PASTE).


 SQL> recover database using backup controlfile until cancel;
auto

 ...
...
...

 ERROR at line 1:
ORA-00279: change 501804 generated at 02/14/2014 07:29:28 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 501804 for thread 1 is in sequence #7
----------------------Alert.log File Error------------------------------
Errors with log /oradb/archive/1_7_838562540.dbf
ORA-326 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Fri Feb 14 06:49:13 2014
ALTER DATABASE RECOVER CANCEL
Signalling error 1152 for datafile 1!
ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlog;

 showing error with system01.dbf tablespace error
-----------------------------------------------------------------------------------------------------------------------


 6. IMPORTANT STEPS
-----------------------------------------------------------------------------------------------------------------------

 select * from v$log;
alter database recover datafile list clear;

 select file#,name from v$datafile;

 alter database recover datafile list  1 , 2 , 3 , 4 , 5;

 SQL> alter database recover until cancel using backup controlfile;
alter database recover until cancel using backup controlfile
*
ERROR at line 1:
ORA-00279: change 501804 generated at 02/14/2014 07:29:28 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 501804 for thread 1 is in sequence #7

 ...

 select * from v$log;
select * from v$logfile;

 SQL> alter database recover logfile '/oradb/app/oracle/oradata/orcl/redo02.log';
alter database recover logfile '/oradb/app/oracle/oradata/orcl/redo02.log'
*
ERROR at line 1:
ORA-00310: archived log contains sequence 5; sequence 7 required
ORA-00334: archived log: '/oradb/app/oracle/oradata/orcl/redo02.log'
SQL>  alter database recover logfile '/oradb/app/oracle/oradata/orcl/redo03.log';
 alter database recover logfile '/oradb/app/oracle/oradata/orcl/redo03.log'
*
ERROR at line 1:
ORA-00310: archived log contains sequence 6; sequence 7 required
ORA-00334: archived log: '/oradb/app/oracle/oradata/orcl/redo03.log'
SQL> alter database recover logfile '/oradb/app/oracle/oradata/orcl/redo01.log';

 Database altered.

 SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 501876 generated at 02/14/2014 07:30:41 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 501876 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00326: log begins at change 526199, need earlier change 501876
ORA-00334: archived log: '/oradb/archive/1_7_838562540.dbf'
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;

 Database altered.

 SQL> select count(*) from scott.abc;

   COUNT(*)
    ----------
       54

SQL> select count(*) from scott.abc_1;

   COUNT(*)
    ----------
       14

SQL> select count(*) from scott.abc_2;

   COUNT(*)
    ----------
       14

SQL> select count(*) from scott.abc_3;

   COUNT(*)
    ----------
       28

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

at No comments:
Labels:

Friday, 14 February 2014

FRM-92101: There was a failure in form server during startup on IBM-AIX 6.1

After performing R12.1  EBS clone on IBM-AIX 6.1 for one of our customer we were not able to access EBS forms due to frm-92101 error. Forms was not launching.





Cause:
 One of the application dependent library file is still exists with the old production OS username - oraprod where as system is expecting this link should be owned by oratest user (clone)

[oratest@ORAEBSBKP[/oratest/R12_app/apps/tech_st/10.1.2/lib32]#ls -lrt ldflags
lrwxrwxrwx    1 oraprod  dba              44 Feb  5 15:42 ldflags -> /oraprod/R12/apps/tech_st/10.1.2/lib/ldflags

Solution:

1) remove soft link for ldflags
2) create new softlink
3) stop application services
4)  run make command for form libraries
5) start application services

>> Remove soft link:

[oratest@ORAEBSBKP[/oratest/R12_app/apps/tech_st/10.1.2/lib32]#rm ldflags
[oratest@ORAEBSBKP[/oratest/R12_app/apps/tech_st/10.1.2/lib32]#ls -lrt ldflags
ldflags not found
[oratest@ORAEBSBKP[/oratest/R12_app/apps/tech_st/10.1.2/lib32]#

>>  Create new soft link:

[oratest@ORAEBSBKP[/oratest/R12_app/apps/tech_st/10.1.2/lib32]#ln -s $ORACLE_HOME/lib/ldflags ldflags
[oratest@ORAEBSBKP[/oratest/R12_app/apps/tech_st/10.1.2/lib32]#

>>  Stop application services: 

>>  Install form libraries using make command

[oratest@ORAEBSBKP[/oratest/R12_app/apps/tech_st/10.1.2/forms/lib32]#make -f ins_forms.mk install

>> Verify ldflags is owned by proper user:

[oratest@ORAEBSBKP[/oratest/R12_app/apps/tech_st/10.1.2/lib32]#ls -lrt ldflags
lrwxrwxrwx    1 oratest  dba              48 Feb 10 15:04 ldflags -> /oratest/R12_app/apps/tech_st/10.1.2/lib/ldflags
[oratest@ORAEBSBKP[/oratest/R12_app/apps/tech_st/10.1.2/lib32]#

>> start application services

After restarting application services forms are launching  normally without any issues.

 
at No comments:
Labels:

Monday, 3 February 2014

ORA-00600: internal error code, arguments: [3020], [1], [3778], [1], [5], [2213], [16], []

----- INCOMPLETE RECOVERY WHEN DELETE "SYSTEM01.DBF"

Note:- This error mostly generated when OS and H/W failure some other "ORACLE BUGS" then you have restore the datafile without any error after that recovery time throwing this error   Datafile "SYSTEM01.DBF" and "SYSAUX01.DBF" and so on. So first clear its ORACLE BUG else DBF CURROPTION BLOCK BUG.  

 rm -rf system01.dbf   (incase of accidentaly delete restore after that recently and consistent backup)

copy system01.dbf from backup to detination location where online dbf works

SQL> shutdown abort;

SQL> startup mount

SQL> recover database;

ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [1], [3778], [1], [5],
[2213], [16], []
ORA-10567: Redo is inconsistent with data block (file# 1, block# 3778)
ORA-10564: tablespace SYSTEM
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 483

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

Solution of the Problem
-------Solution 01:
The fix is to do a manual recovery with allow 1 corruption.

That is "recover database allow 1 corruption;" which will skip the bad transaction. We need to repeat this command until the recovery completes.
rman target sys

RMAN> recover database;

RMAN> blockrecover datafile 1 block 3778;        # Only Try but not resolved then follow next one command

RMAN> recover database allow 1 corruption;

RMAN> recover database allow 1 corruption;

Starting recover at 11-APR-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=320 devtype=DISK

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/11/2010 20:00:00
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start allow 1 corruption
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [1], [3778], [1], [5],
[2213], [16], []
ORA-10567: Redo is inconsistent with data block (file# 1, block# 3778)
ORA-10564: tablespace SYSTEM
ORA-01110: data file 1: '/oradb/app/oracle/oradata/orcl/system01.dbf'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 483

RMAN> recover database allow 1 corruption;

Starting recover at 11-APR-10
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 11-APR-10





------Solution 02:
Solution 01 is recommended. However after you run "recover database allow 1 corruption;" in a row for 10 times still your problem is not resolved then you might need to do incomplete recovery. For example:

SQL> recover database until time 'YYYY-MON-DD:HH:MI:SS';



------Solution 03:
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
 recover database using backup controlfile until cancel;
alter database open resetlogs;
select open_mode from v$database;

exp system/password file='/orashare/xxxx.dmp' log='/orashare/xxxx.log' owner=xxxx





After searching to Oracle support I see so many related bugs exist regarding ORA-00600 [3020].

NB Bug Fixed Description
9847338 Session hang after applying the patch for Bug 9587912 which causes ORA-600 [3020]
11689702 12.1.0.0 ORA-600 [3020] during recovery after datafile RESIZE (to smaller size)
8774868 11.2.0.1.2, 11.2.0.2, 12.1.0.0 OERI[3020] reinstating primary
+ 8597106 11.2.0.2, 12.1.0.0 Lost Write in ASM when normal redundancy is used
10094823 12.1.0.0 Block change tracking on physical standby can cause data loss
10071193 11.2.0.3, 12.1.0.0 Lost write / ORA-600 [kclchkblk_3] / ORA-600 [3020] in RAC
P 8635179 10.2.0.5, 11.2.0.2, 12.1.0.0 Solaris: directio may be disabled for RAC file access. Corruption / Lost Write
9587912 11.2.0.2, 12.1.0.0 ORA-600 [3020] in datafile that went offline/online in a RAC instance
+ 10209232 11.2.0.2.1, 12.1.0.0 ORA-1578 / ORA-600 [3020] Corruption. Misplaced Blocks and Lost Write in ASM
+ 10425010 12.1 Stale data blocks may be returned by Flash Cache
8826708 10.2.0.5, 11.2.0.2 ORA-600 [3020] for block type 0x3a (58) during recovery for block restored by RMAN backup
8230457 10.2.0.4.1, 10.2.0.5, 11.1.0.7.1, 11.2.0.1 Physical standby media recovery gets OERI[krr_media_12]
4453449 10.2.0.2, 11.1.0.6 OERI:3020 / corruption errors from multiple FLASHBACK DATABASE
4594917 9.2.0.8, 10.2.0.2, 11.1.0.6 Write IO error can cause incorrect file header checkpoint information
4637668 10.2.0.3, 11.1.0.6 IMU transactions can produce out-of-order redo (OERI [3020] on recovery)
5610267 10.2.0.5 MRP terminated by ORA-600[krr_media_12] / OERI:3020 after flashback
7197445 10.2.0.4.1, 10.2.0.5 Standby Recovery session cancelled due to ORA-600 [3020] "CHANGE IN FUTURE OF BLOCK"
3560209 10.2.0.1 OERI[3020] stuck recovery under RAC
3762714 9.2.0.7, 10.1.0.4, 10.2.0.1 ALTER DATABASE RECOVER MANAGED STANDBY fails with OERI[3020]
* 3381950 10.2.0.1 Backups from RAC DB before Data Guard Failover cannot be used
3397181 9.2.0.5, 10.1.0.3, 10.2.0.1 ALTER SYSTEM KILL SESSION of recovery slave causes stuck recovery
3535712 9.2.0.6, 10.1.0.4 OERI[3020] / ORA-10567 from RAC with standby in max performance mode
4594912 9.2.0.8, 10.1.0.2 Incorrect checkpoint possible in datafile headers
3635331 9.2.0.6, 10.1.0.4 Stuck recovery (OERI:3020) / ORA-1172 on startup after a crash
2322620 9.2.0.1 OERI:3020 possible on recovery of LOB DATA
P+ 656370 7.3.3.4, 7.3.4.0, 8.0.3.0 AlphaNT only: Corrupt Redo (zeroed byte) OERI:3020

'*' against a bug indicates that an alert exists for that issue.
'+' indicates a particularly notable bug.
'P' indicates a port specific bug.

at No comments:
Labels:

Friday, 31 January 2014

ORA-19502: write error on file & ORA-16038 (with ORA-19502,ORA-00312)

Cause: Of these error mostly disk is full in that case to skip some blocks then generating the error. So keep monitor the server Mount point size.



1.
ORA-19502: write error on file "", block number (block size=) in Dataguard

We have received an alert in the morning that the Primary Database is out of Sync. Primary and Standby are with difference of 9 archives.

I have logged on to the primary and identified the below error.

ORA-19502: write error on file "", block number  (block size=)


SQL> set line 200
col DEST_NAME for a50
col BINDING for a10

select DEST_ID,DEST_NAME,STATUS,BINDING,ERROR from v$ARCHIVE_DEST where status<>'INACTIVE';

   DEST_ID DEST_NAME                                          STATUS    BINDING    ERROR
---------- -------------------------------------------------- --------- ---------- -----------------------------------------------------------------
         1 LOG_ARCHIVE_DEST_1                                 VALID     OPTIONAL
         2 LOG_ARCHIVE_DEST_2                                 ERROR     OPTIONAL   ORA-19502: write error on file "", block number  (block size=)


In the above output the STATUS column is showing ERROR.
I have logged on to the Standby database and found the archive mount point is full. I removed the applied archives and freed up some space. How to check Sync Status on Standby

Defer'd and Enable'd the shipping on Primary Database.

SQL> alter system set log_archive_dest_state_2=defer;

System Altered.

SQL> alter system set log_archive_dest_state_2=enable;

System Altered.

SQL> set line 200
SQL> col DEST_NAME for a50
SQL> col BINDING for a10


SQL> select DEST_ID,DEST_NAME,STATUS,BINDING,ERROR from v$ARCHIVE_DEST where status<>'INACTIVE'
/
   DEST_ID DEST_NAME                                          STATUS    BINDING    ERROR
---------- -------------------------------------------------- --------- ---------- -----------------------------------------------------------------
         1 LOG_ARCHIVE_DEST_1                                 VALID     OPTIONAL
         2 LOG_ARCHIVE_DEST_2                                 VALID     OPTIONAL

Now the STATUS column is showing Valid and the ERROR column is null.

Archive started shipping to Standby Database and got applied.
How to check Sync Status on Standby






2. 
ORA-16038 (with ORA-19502,ORA-00312)
-----------------------------------------------------------------------------

SQL> ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  1219160 bytes
Variable Size             167773608 bytes
Database Buffers         1073741824 bytes
Redo Buffers               15556608 bytes
Database mounted.
ORA-16038: log 1 sequence# 8557 cannot be archived
ORA-19502: write error on file "", blockno  (blocksize=)
ORA-00312: online log 1 thread 1: '/u02/redofs/redo01.log'

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


SQL> recover database until cancel;
Media recovery complete.

SQL> alter database open resetlogs;
Database altered

SQL>shutdown immediate
Database closed.
Database dismount.
ORACLE instance shutdown.

SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size                  1219160 bytes
Variable Size             167773608 bytes
Database Buffers         1073741824 bytes
Redo Buffers               15556608 bytes
Database mounted.
Database opened.

at No comments:
Labels:

Tuesday, 28 January 2014

DB LINK Create & Drop Through Procedure

CREATE or replace PROCEDURE scott.create_db_link AS
BEGIN
EXECUTE IMMEDIATE 'create database link LINK1 connect to scott identified by tiger using ''testdb''';
END create_db_link;
 /


exec scott.create_db_link;


col db_link format a50
select OWNER,DB_LINK,USERNAME from dba_db_links;
select * from dba_db_links where OWNER='SCOTT';



----drop database link scott.LINK1.REGRESS.RDBMS.DEV.US.ORACLE.COM;     ##
drop database link scott.LINK1.REGRESS.RDBMS.DEV.US.ORACLE.COM
                   *
ERROR at line 1:
ORA-02024: database link not found




----drop database link scott.LINK1;                                                 ##
drop database link scott.LINK1;
                   *
ERROR at line 1:
ORA-02024: database link not found



CREATE PROCEDURE scott.drop_db_link AS
BEGIN
EXECUTE IMMEDIATE 'drop database link LINK1';
END drop_db_link;
 /


exec scott.drop_db_link;


col db_link format a50
select OWNER,DB_LINK,USERNAME from dba_db_links;
select * from dba_db_links where OWNER='SCOTT';


drop procedure scott.create_db_link ;
drop procedure scott.drop_db_link;
at No comments:
Labels:

Friday, 20 September 2013

tar--- Command


-----tar command
man tar  <name of .tar> <which dir u have tar>
tar -czvf shc-3.8.9.tar shc-3.8.9


-----untar command
tar -xzvf shc-3.8.9.tar
at No comments:
Labels: ,

UNNAMED file in standby after adding new file to primary – ORA-01111, ORA-01110, ORA-01157


How to resolve ORA-01111, ORA-01110, ORA-01157

---Standby Case if any updation on primary site like DBF then throw this error...

There are many reasons for a file being created as UNNAMED or MISSING in the standby database, including insufficient disk space on standby site (or) Improper parameter settings related to file management.

STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.

For example if we add a data file on the Primary when parameter STANDBY_FILE_MANAGEMENT on standby set to MANUAL , While recovery process(MRP) is trying to apply archives, Due to that parameter setting it will create an Unnamed file in $ORACLE_HOME/dbs and it will cause to kill MRP process and Errors will be as below.

Errors From Alert Log file:-

Errors in file /oracle/app/oracle/admin/CC/bdump/cc_mrp0_22294.trc:

ORA-01111: name for data file 536 is unknown - rename to correct file

ORA-01110: data file 536: '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'

ORA-01157: cannot identify/lock data file 536 - see DBWR trace file

ORA-01111: name for data file 536 is unknown - rename to correct file

ORA-01110: data file 536: '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'

Trace File:-

MRP0: Background Media Recovery terminated with error 1111

ORA-01111: name for data file 536 is unknown - rename to correct file

ORA-01110: data file 536: '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'

ORA-01157: cannot identify/lock data file 536 - see DBWR trace file

ORA-01111: name for data file 536 is unknown - rename to correct file

ORA-01110: data file 536: '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'

ORA-01111: name for data file 536 is unknown - rename to correct file

ORA-01110: data file 536: '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'

ORA-01157: cannot identify/lock data file 536 - see DBWR trace file

ORA-01111: name for data file 536 is unknown - rename to correct file

ORA-01110: data file 536: '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536'

ARCH: Connecting to console port...

Troubleshooting:-

Check for the files needs to be recovered.

SQL> select * from v$recover_file where error like '%FILE%';

 

     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME

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

       536 ONLINE  ONLINE  FILE MISSING                  0

 

SQL>

Identify on primary of data file 536(Primary Database)

SQL>  select file#,name from v$datafile where file#=536;

 

     FILE# NAME

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

       536 +DATA/c/datafile/undotbs9.595.750444337

 

SQL>

Identify dummy file name created in (Standby)

SQL> select file#,name from v$datafile where file#=536;

 

     FILE# NAME

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

       536 /oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536

 

SQL>

Crosscheck that no MRP is running and STANDBY_FILE_MANAGEMENT can be enabled once after creating file on standby

SQL> alter database create datafile '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536' as '+DATA3/cc/datafile/undotbs9_595_750444337';

Database altered.

SQL>

If You are managing File system ASM with OMF, you are not allowed to create data file, Because it will file system names and format maintained by ASM.  If you try to Create datafile as above with ASM File, You will pass with below error.

SQL> alter database create datafile '/u01/oracle/orahome/dbs/UNNAMED00613' as '+DATA3/cc/datafile/undotbs9_595_750444337'

 *

 ERROR at line 1:

 ORA-01276: Cannot add file

 +DATA3/cc/datafile/undotbs9_595_750444337. File has an Oracle Managed Files file name.

Then Run above command as shown below

SQL> alter database create datafile '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00536' as new;

Database altered.

SQL>

Enable STANDBY_FILE_MANAGEMENT to AUTO & Start MRP.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE

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

standby_file_management              string      MANUAL

 

 

SQL> alter system set standby_file_management=AUTO scope=both;

System altered.

 

SQL>

 

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE

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

standby_file_management              string      AUTO

 

SQL>

 

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

After creating the file, MRP will start applying archives on standby database.

Note:-

Setting STANDBY_FILE_MANAGEMENT to AUTO causes Oracle to automatically create files on the standby database and, in some cases, overwrite existing files. Care must be taken when setting STANDBY_FILE_MANAGEMENT and DB_FILE_NAME_CONVERT so that existing standby files will not be accidentally overwritten.
at No comments:
Labels:

Monday, 21 January 2013

Multiplex Controlfiles for an Oracle DB


SQL>  show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/ALMD1213/CONTROL01.CTL,
                                                        /u01/ALMD1213/CONTROL02.CTL,
                                                        /u01/ALMD1213/CONTROL03.CTL

SQL> alter system set control_files='/u01/ALMD1213/CONTROL01.CTL',
                                                       '/u01/ALMD1213/CONTROL02.CTL',
                                                       '/u01/ALMD1213/CONTROL03.CTL',
                                                       '/u01/ALMD1213/CONTROL04.CTL' scope=spfile;



SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> ! cp /u01/ALMD1213/CONTROL03.CTL  /u01/ALMD1213/CONTROL04.CTL


SQL> startup

ORACLE instance started.
Total System Global Area  448790528 bytes
Fixed Size                  1291096 bytes
Variable Size             201329832 bytes
Database Buffers          239075328 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.


SQL>  show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/ALMD1213/CONTROL01.CTL,
                                                        /u01/ALMD1213/CONTROL02.CTL,
                                                        /u01/ALMD1213/CONTROL03.CTL,
                                                        /u01/ALMD1213/CONTROL04.CTL



...........!!!!





at No comments:
Labels:
Subscribe to: Posts (Atom)