Posts

Showing posts from January 11, 2009

List of Patchset number in metalink

You want to download patchset from metalink but you don't know the patchset number. In that case it may take some moments to find patchset number. For example you want to upgrade your database version from 10.2.0.1 to 10.2.0.2. Now which patchset number you want to download? In this post the patchset number along with oracle version is below. A)For Oracle9iR2, Base version 9.2.0.1 i)To upgrade to 9.2.0.2 patchset number 2632931. ii)To upgrade to 9.2.0.3 patchset number 2761332 iii)To upgrade to 9.2.0.4 patchset number 3095277 iv)To upgrade to 9.2.0.5 patchset number 3501955 v)To upgrade to 9.2.0.6 patchset number 3948480 vi)To upgrade to 9.2.0.7 patchset number 4163445 vii)To upgrade to 9.2.0.8 patchset number 4547809 B)For Oracle10g, Base version 10.1.0.2 i)To upgrade to 10.1.0.3 patchset number 3761843 ii)To upgrade to 10.1.0.4 patchset number 4163362 iii)To upgrade to 10.1.0.5 patchset number 4505133 C)For Oracle10gR2 Base version 10.2.0.1 i)To upgrade to 10.2.0.2 patchset numbe...

RMAN-00554,RMAN-04005,ORA-0103 when remote connection by rman

Whenever I try to connect remotely to a database through rman it fails with message RMAN-00554, RMAN-04005, ORA-0103. In this example my source database is saturn and destination database is jupiter. From jupiter let me try to connect to saturn. Source database Before proceed let's see the parameter that we can use to connect to rman without tnsnames.ora. Our needed parameter is hostname/ipaddress, service_name and port number. These three can be found by issuing lsnrctl status in source machine. Note that you must have a password file setup in the source database. This requires the use of the "orapwd" command and the initialization parameter "REMOTE_LOGIN_PASSWORDFILE". bash-3.00$ hostname saturn SQL> show parameter remote_login NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string EXCLUSIVE bash-3.00$ lsnrctl status LSNRCTL for S...

Recreating controlfile fails with ORA-01503, ORA-01504

I recreated my controlfile and whenever I run it fails with ORA-01503, ORA-01504. SQL> @f:\controlfile.ctl ORACLE instance started. Total System Global Area 104857600 bytes Fixed Size 1247516 bytes Variable Size 71304932 bytes Database Buffers 25165824 bytes Redo Buffers 7139328 bytes CREATE CONTROLFILE REUSE DATABASE " shaikcl " NORESETLOGS ARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-01504: database name ' shaikcl ' does not match parameter db_name ' shaikcl ' Cause of the problem The error message already shown the database name in controlfile does not match with db_name parameter inside spfile/pfile. Solution of the Problem Be sure what would be your database name. In this example with message ' shaikcl ' does not match parameter db_name 'shaikcl' I see in controlfile the database name is specified shaikcl but in the spfile/file it is specified as shaikcl...

Creating controlfile fails with ORA-01503, ORA-01161

Error Description While creating cloning database I have modified controlfile contents as below where I like to change my database name to shaikcl. STARTUP NOMOUNT PFILE='F:\PFILE.ORA' CREATE CONTROLFILE REUSE DATABASE "shaikcl" RESETLOGS ARCHIVELOG . . . 'F:\ORACLE\SHAIKCL\SYSTEM01.DBF', . . . CHARACTER SET WE8MSWIN1252 ; Now I ran this script to create controlfile and it fails SQL> @f:\controlfile.ctl ORACLE instance started. Total System Global Area 104857600 bytes Fixed Size 1247516 bytes Variable Size 71304932 bytes Database Buffers 25165824 bytes Redo Buffers 7139328 bytes CREATE CONTROLFILE REUSE DATABASE "shaikcl" NORESETLOGS ARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-01161: database name SHAIK file header does not match given name of SHAIKCL ORA-01110: data file 1: 'F:\ORACLE\SHAIKCL\SYSTEM01.DBF' Cause of The Problem Control file script has bee...

How to clone database on the same host with different name

Step by steps cloning operation is described below. The scenario is, -Source database shaik be cloned as shaikcl -Datafile named E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\*.dbf will be cloned as F:\ORACLE\SHAIKCL\*.dbf -Controlfile named E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\*.ctl will be cloned as F:\ORACLE\*.CTL -In cloned database adump,bdump,cdump,udump will be located in F:\ORACLE\*dump Step 01: In source database identify the datafile location and redo logfile location. On windows machine, C:\Documents and Settings\Queen>set ORACLE_SID=shaik On linux based machine, $export ORACLE_SID=shaik C:\Documents and Settings\Queen>sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 6 23:19:45 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> col file_name format a70 SQL> set linesize 160 SQL> select file_n...

How to truncate or delete rows from audit trail table sys.aud$

1)Only appropriate privileged user can do delete operation on SYS.AUD$ table. The user must have either of the following privileges. -SYS user. -DELETE ANY TABLE system privilege. (If O7_DICTIONARY_ACCESSIBILITY=TRUE) -A user to whom SYS has granted the object privilege DELETE on SYS.AUD$ table. 2)Before deleting any rows you may want to archive the table. You can achive this by creating a table from SYS.AUD$ and export that. Don't export SYS.AUD$ directly. SQL>CREATE TABLE AUDIT_RECORD TABLESPACE users as select * from SYS.AUD$; Now export the table as, SQL> host exp tables=AUDIT_RECORD file=audit_record.dmp 3)To delete all records from audit trail table SYS.AUD$ issue, SQL>DELETE FROM SYS.AUD$; To delete all records of particular audited table from the audit trail issue, SQL>DELETE FROM sys.aud$ WHERE obj$name='&table_nmae'; But deleting in this way will not reduce size on the system tablespace or aud$ table. In order to reduce size follow section 4. 4)Tru...

How to Reorganize Audit trail SYS.AUD$ Table

You may want to reorganize your auditing table if you optionally delete records from it regularly. In the following steps it is described. 1)Enable restricted session. In order to ensure consistency in the auditing table temporary disable auditing activity. You can do this by opening database with STARTUP RESTRICT or during open make the system as restricted session. SQL> connect / as sysdba Connected. SQL> alter system enable restricted session; System altered. Check if sessions are still connected by, SQL> select sid, serial#, username from v$session; If necessary kill these sessions with, SQL> alter system kill session 'sid , serial#'; 2)Copy SYS.AUD$ table. SQL>CREATE TABLE audit_record TABLESPACE USERS as select * from SYS.AUD$; You now can take a dump of audit_record. 3)Truncate SYS.AUD$ table. SQL> truncate table sys.aud$; Table truncated. SQL> select count(*) from SYS.AUD$; COUNT(*) -------- 0 4)Copy the rows ...

How to move audit table out of SYSTEM tablespace

Oracle strongly recommended to use the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION provided with the DBMS_MGMT package in order to move audit trail table out of SYSTEM tablespace. But the procedure SET_AUDIT_TRAIL_LOCATION by default is not available until 11g. It is available in 11g. However with manual script you can move SYS.AUD$ table out of SYSTEM tablespace. But you need to remember moving AUD$ out of SYSTEM tablespace is not a supported procedure. Oracle does not support changing ownership of AUD$, or any triggers on it. Below is the scripts that you can do as your own risk, Step 01: Connect to database as SYS user. conn / as sysdba Step 02: Create tablespace where audit file will reside. create tablespace AUDIT_TBS datafile '/oradata2/datafile/aud01.dbf' size 10M autoextend on maxsize unlimited; Step 03: Create audit table inside AUDIT_TBS create table aud_aux tablespace AUDIT_TBS as select * from aud$ where 1 = 2; Note that no rows will be created in this state....

Set Date format inside RMAN environment

Suppose inside rman environment I got the following output. RMAN> list backup; using target database control file instead of recovery catalog List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 558.80M DISK 00:01:14 08-SEP-08 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20080908T035835 Piece Name: /oracle/app/oracle/product/10.2.0/db_1/flash_recovery_area/SHAIK/backupset/ 2008_09_08/o1_mf_nnndf_TAG20080908T035835_4d9pscwz_.bkp List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1030282 08-SEP-08 /oradata2/shaikdba/shaikdba/shaik/system01.dbf 2 Full 1030282 08-SEP-08 /oradata2/shaikdba/shaikdba/shaik/undotbs01.dbf 3 Full 1030282 08-SEP-08 /oradata2/shaikdba/shaikdba/shaik/sysaux01.dbf 4 Full 1030282 08-SEP-08 ...

ORA-14120: incompletely specified partition bound for a DATE column

In order to create partitioning table of FORM_AT whenever I try to create a temporary partitioned table name FORM_AT_PART it fails with ORA-14120: incompletely specified partition bound for a DATE column error as below. SQL>CREATE TABLE "FORM_AT_PART" ( "ID" NUMBER NOT NULL ENABLE, "AT" VARCHAR2(32) NOT NULL ENABLE, "CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE )partition by range (created_date) ( partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02, partition fa_part2 values less than (to_date('01-JUN-06','DD-MON-YY')) tablespace DATA02, partition fa_part3 values less than (to_date('01-SEP-06','DD-MON-YY')) tablespace DATA02, partition fa_part4 values less than (to_date('01-DEC-06','DD-MON-YY')) tablespace DATA02, partition fa_part5 values less than (to_date('01-MAR-07','DD-MON-YY')) tablespace DATA02, partit...

How to make partitioning in Oracle more Quickly

As the table size grows and full table scans happens more frequently then there is no alternative than partitioning in oracle. Partition greatly enhance performance of a query. I will start my partitioning system simply with an example. In my database the following query took 30~35 seconds to complete. After partitioning the performance increased amazingly and it then took only 1 second. My query was, SELECT DISTINCT fs.type, fs.id , fs.pid, fs.cid , fs.cr_id, fs.created_date FROM summary fs where fs.id in (select fa.id from forms fa where fa.sar in (select la.sar from login la where la.login_id=1 and fa.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') and fs.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') ) )and mode=0; After seeing above query I decide to make range partition on column created_date both in summary table and forms table. Below is the list of procedur...

Crash Recovery Fails With ORA-27067

Problem Symptoms After performing shutdown abort whenever I try to start my database it fails to perform crash recovery. Below is from alert log. Wed May 21 07:11:43 2008 Errors in file /ora/udump/shaik_ora_12424.trc: ORA-01115: IO error reading block from file 2 (block # 11546) ORA-01110: data file 2: '/ora/data/data01/shaik_undotbs01.dbf' ORA-27067: size of I/O buffer is invalid Additional information: 2 Additional information: 1245184 Cause of the Problem This is oracle bug. It fired whenever someone gave very large insert operation and during that time issued shutdown abort. Solution of the Problem Just perform normal recovery. To do so, 1)$sqlplus / as sysdba 2)startup mount; 3)recover database; 4)alter database open;

Database startup fails with ORA-27302: failure occurred at: sskgpsemsper

Error Description Whenever I try to start my database it fails with ORA-27302: failure occurred at: sskgpsemsper as following. RMAN> STARTUP FORCE NOMOUNT PFILE='/backup03/webkey/testinitdb.ora'; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 09/23/2008 00:45:51 RMAN-04014: startup failed: ORA-27154: post/wait create failed ORA-27300: OS system dependent operation:semget failed with status: 28 ORA-27301: OS failure message: No space left on device ORA-27302: failure occurred at: sskgpsemsper Cause of the problem The error may mislead you. Though it indicates No space left on device but whenever I issue df -h on my OS there is enough space. The problem happened because of short of semaphores setting in the OS. Solution of the problem Solution 1) Increase number of sema...

ORA-00997: illegal use of LONG datatype

Error Description Whenever you try to do some illegal operations of LONG datatype it fails with error ORA-00997: illegal use of LONG datatype. SQL> alter table a move ; alter table a move * ERROR at line 1: ORA-00997: illegal use of LONG datatype Cause of the Problem The usage of LONG datatype has several restrictions. One of the restriction is, if a table contain long datatype column then it can't be used to move by using ALTER TABLE. Solution of the Problem Don't use any LONG datatype in any table. If you have LONG datatype you must convert it to either BLOB/CLOB datatype using TO_LOB function.

ORA-01450: maximum key length (3215) exceeded

Error Description 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. Below is the scenario. SQL> create table tab1(a varchar2(3000),b varchar2(2000)); Table created. SQL> create index tab1_I on tab1(a,b); Index created. SQL> alter index tab1_I rebuild online; alter index tab1_I rebuild online * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01450: maximum key length (3215) exceeded Let's now create one table with column length 3000+199=3199 bytes and see what happens. SQL> create table tab3(a varchar2(3000),b varchar2(199)); Table created. SQL> create index tab3_I on tab3(a,b); Index created. Try to rebuild it online and it works. SQL> alter index tab3_I rebuild online; Index altered. Now just add extra 1 bytes on column b. And whenever we try to rebuild it online it will fail. SQL> alter table tab3 modify b varc...

How to move LOB data to another tablespace

We know with the ALTER TABLE .. MOVE clause we can relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment. If you want to make no other changes to the table other than rebuilding it then your statement is simply, SQL>ALTER TABLE table_name MOVE; Or if you want to move it to another tablespace then specify, SQL>ALTER TABLE table_name MOVE TABLESPACE tablespace_name; With this statement it does not affect any of the lob segments associated with the lob columns in the table. If you want to move only lob segment to a new tablespace then your command will be, ALTER TABLE table_name MOVE LOB(lob_columnname) STORE AS (TABLESPACE new_tablespace_name); Along with the log segment you can also move the table as well as storage attribute of table and log by following query, ALTER TABLE table_name MOVE TABLESPACE new_tablespace STORAGE(new_storage) LOB (lobcol) STORE AS (TABLESPACE new_tablespace STORAGE (new_storage)); If you want to move all t...

How to catalog and uncatalog a backup to RMAN repository

Catalog Backup 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 comma...

ORA-07445: exception encountered: core dump SIGSEGV

Problem Symptoms Whenever I check my alert log file I got the text as Errors in file /var/opt/oracle/admin/udump/orastdby_ora_31795.trc ORA-07445: exception encountered: core dump [_intel_fast_memcmp()+38] [SIGSEGV] [Address not mapped to object] [0x0] [] [] After checking the trace file I got, Call Stack Trace shows: ksedst ksedmp ssexhd intel_fast_memcmp Recent Changes of The Database We have changes the CURSOR_SHARING parameter to SIMILAR from EXACT. Cause of The Problem This is oracle bug. Bug number 4456646. When the cursor sharing parameter is not set to EXACT this bug may fire. This occur while literal replacement when there are empty string literals in use. This bug happened in oracle version 10.2.0.1. In our environment it was RHL linux 32 bit production server. Solution of The Problem Way 1: As a workaround disable literal replacement. You can do it by, ALTER SYSTEM SET cursor_sharing='EXACT' SCOPE=both; if you use spfile. Way 2: This bug is fixed in 10.2.0.2 patchse...

Minimum privilege needed to take data pump export

In your organization you may assign a user who is only responsible to take data pump export. Suppose everyday evening he will be responsible to take a logical backup of the database. The minimum privilege need to perform data pump export operation is given below. 1)Create Session privilege. This is required to logon to database. 2)Create Table privilege. This is required as while doing export operation he needs to create a master table. 3)Read and write permission on a valid database directory. Or Create Directory privilege. 4)Sufficient tablespace quota on the user's default tablespace. As master table need to be created while data pump export operation. In addition to above 4 privileges it is needed to grant EXP_FULL_DATABASE to the intended user if he might need the following things - to run a full database Export or - to run a transport_tablespace job or - to run an Export DataPump job with the TRACE parameter or - to run an operation that exports a different schema. Now suppos...

Expdp fails with ORA-31626, ORA-31633, ORA-06512, ORA-01031

Error Description Whenever I run the expdp command to export only my schema objects it fails with ORA-31626, ORA-31633, ORA-06512 and ORA-01031. A screenshot is below from my console. SQL> host expdp test/t full=y directory=d dumpfile=a.dmp Export: Release 10.2.0.1.0 - Production on Saturday, 20 September, 2008 14:58:53 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORA-31626: job does not exist ORA-31633: unable to create master table "TEST.SYS_EXPORT_SCHEMA_05" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT", line 863 ORA-01031: insufficient privileges Cause of the Problem As ORA-01031 indicates the test user that is performing data pump export does not have enough privilege. In order to see the current privilege assigned to the user issue, SQL> select * from session_privs; PRIVILE...

Expdp fails with ORA-01950 and ORA-01536

Error Description Whenever I run expdp the export process fails with ORA-01950 or ORA-01536. SQL> host expdp test/t full=y dumpfile=a.dmp directory=d Export: Release 10.2.0.1.0 - Production on Saturday, 20 September, 2008 15:54:18 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORA-31626: job does not exist ORA-31633: unable to create master table "TEST.SYS_EXPORT_FULL_05" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT", line 863 ORA-01950: no privileges on tablespace 'USERS' Or, ORA-31626: job does not exist ORA-31633: unable to create master table "TEST.SYS_EXPORT_SCHEMA_01" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT", line 863 ORA-01536: space quota exceeded for tablespace 'USERS' Cause of the Problem While exp...

Query to check whether user has datapump privilege.

Check for whether user has full database export/import privilege You can check whether user has privilege to export or import the full database using data pump. To export full database using data pump user must have EXP_FULL_DATABASE role or dba role and to import full database using data pump user must have IMP_FULL_DATABASE or dba role. In order to see whether user has these privilege or not you can query, SQL>SET lines 100 COL privilege FOR a50 SELECT grantee, granted_role, default_role FROM dba_role_privs WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE') ORDER BY 1,2; GRANTEE GRANTED_ROLE DEF ------------------------------ ------------------------------ --- SHAIK DBA YES DBA EXP_FULL_DATABASE YES DBA IMP_FULL_DATABASE YES FARUK DBA ...

Audit Trigger Activity in Oracle

Auditing a trigger activity or SQL inside trigger is no different than auditing normal SQL. In our business environment it was required to audit triggering event whenever the SQL statement inside trigger does unsucessful execution. We can achive our goal by simply audit the SQL for which trigger fires. Additionally you may also wish to audit the SQL statements inside trigger. Here is a test. Inside test schema I have made an example. Connect as test user and create three tables. SQL> conn test/test Connected. SQL> create table test(a number, b varchar2(4), c varchar2(8)); Table created. SQL> create table test1(a number, b varchar2(3), c varchar2(3)); Table created. SQL> create table test2(a number, b varchar2(3), c varchar2(3)); Table created. 2)Create the trigger. It will fire before insert operation done on table test. Then it will insert these value into table test1 and update the table test2. SQL> create or replace trigger test_t before insert on test for each row be...

How to avoid of recreating pfile or spfile if any error in spfile

Suppose I have done some wrong-edit inside spfile using ALTER SYSTEM SET ... SCOPE=SPFILE from database. Now the next startup process of my database will fail with oracle errors. The conventional approach of solution to this problem is, 1)Connect to SQL*plus as sysdba 2)Create pfile from the spfile. 3)Edit the pfile and correct the parameter settings. 4)Create spfile from the edited correct pfile. 5)Startup your database with the spfile. But we can omit these steps easily by just creating a pfile and invoke the name of the spfile inside the pfile. After invoking spfile parameter inside pfile in the next line specify the name of the parameter which is cause problem of startup. In this case we gave multiple values of the parameter and oracle will use the last one. So override the invalid parameter value with the valid one and thus will startup the create. After startup we can set valid value in the spfile. The following example will make you a clear idea. Step 1: I have set background_du...

List of Parameters that must have identical in RAC database

In a RAC database there may contain several instances and the initialization parameters of all of the instances should not have identical setting. In fact the initialization parameters that are critical for database creation and affect certain database operations must have the same value for every instance in an Oracle RAC database. Below is the list of initialization parameters that must have identical settings for all instances in a RAC database. 1)ACTIVE_INSTANCE_COUNT 2)ARCHIVE_LAG_TARGET 3)CLUSTER_DATABASE 4)CLUSTER_DATABASE_INSTANCES 5)COMPATIBLE 6)CONTROL_FILES 7)DB_BLOCK_SIZE 8)DB_DOMAIN 9)DB_FILES 10)DB_NAME 11)DB_RECOVERY_FILE_DEST 12)DB_RECOVERY_FILE_DEST_SIZE 13)DB_UNIQUE_NAME 14)DML_LOCKS (Only if set to zero). 15)INSTANCE_TYPE (RDBMS or ASM) 16)PARALLEL_MAX_SERVERS 17)REMOTE_LOGIN_PASSWORD_FILE 18)UNDO_MANAGEMENT

How to debug Backup, Restore Session in RMAN

In many cases in RMAN we need to debug the session to find and investigate the session about what is happening there. Also to obtain and check the correct diagnostic evidence we sometimes need the debug the rman session. The most common use of debugging RMAN session is whenever we fail RMAN operation. In fact if any failure in RMAN operation RMAN log and debug files are NOT generated by default and we explicitly need to enable it. We can log and debug session whichever operation we do in RMAN. Like Backup, Restore, TSPITR, DBPITR, Duplicate Database, Restore to a new host etc. The commnad is simple, The syntax is, $ rman target [un/pwd@target_db] catalog [un/pwd@catalog_db] debug trace rman.trc log rman.log RMAN>[RMAN Commands Here] Where un indicates username and pwd indicates password. In my system I used debugging and logging RMAN session by, $ rman target / log=/backup03/webkey/rmanlog.txt trace=/backup03/webkey/rmantrace.log RMAN> debug on RMAN> @/backup03/webkey/rman_scr...

Restore operation fails with RMAN-11003 ORA-01511 ORA-01516

Error Description And Symptoms 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 as below. RMAN> @/backup03/webkey/rman_script RMAN> run{ 2> set newname for datafile 1 to '/backup03/webkey/system01.dbf'; 3> set newname for datafile 2 to '/backup03/webkey/undotbs01.dbf'; 4> set newname for datafile 3 to '/backup03/webkey/sysaux01.dbf'; 5> set newname for datafile 4 to '/backup03/webkey/users01.dbf'; 6> set newname for datafile 5 to '/back...

How to debug Backup, Restore Session in RMAN

In many cases in RMAN we need to debug the session to find and investigate the session about what is happening there. Also to obtain and check the correct diagnostic evidence we sometimes need the debug the rman session. The most common use of debugging RMAN session is whenever we fail RMAN operation. In fact if any failure in RMAN operation RMAN log and debug files are NOT generated by default and we explicitly need to enable it. We can log and debug session whichever operation we do in RMAN. Like Backup, Restore, TSPITR, DBPITR, Duplicate Database, Restore to a new host etc. The commnad is simple, The syntax is, $ rman target [un/pwd@target_db] catalog [un/pwd@catalog_db] debug trace rman.trc log rman.log RMAN>[RMAN Commands Here] Where un indicates username and pwd indicates password. In my system I used debugging and logging RMAN session by, $ rman target / log=/backup03/webkey/rmanlog.txt trace=/backup03/webkey/rmantrace.log RMAN> debug on RMAN> @/backup03/webkey/rman_scr...

How to get port number list of EM and isqlplus

You can get your portlist information of enterprise manger or isqlplus or others web services in the location of $ORACLE_HOME/install/portlist.ini. On my system the output is, bash-3.00$ cat /oracle/app/oracle/product/10.2.0/db_1/install/portlist.ini iSQL*Plus HTTP port number =5560 Enterprise Manager Console HTTP Port (DBASE) = 1158 Enterprise Manager Agent Port (DBASE) = 3938 Enterprise Manager Console HTTP Port (shaikdba) = 5500 Enterprise Manager Agent Port (shaikdba) = 1830 Enterprise Manager Console HTTP Port (shaik) = 5501 Enterprise Manager Agent Port (shaik) = 1831 But note that the ports that are listed in portlist.ini file were assigned during the installation. This file is not updated if port numbers are changed after the installation.

ORA-32010: cannot find entry to delete in SPFILE

Error Description With ALTER SYSTEM SET ..... SID='*' we can set the parameter value to all instances of the database( Like in RAC). But to set parameter only to a single instance of a database we use ALTER SYSTEM SET ..... SID='instance_name'; Similarly to reset a parameter value from all instances we have to use ALTER SYSTEM RESET ... sid='*' and to reset a parameter value from a single instance we have to use ALTER SYSTEM RESET ..... sid='instance_name'. Now while we reset value from a single instance it fails with error ORA-32010. SQL> alter system reset open_cursors scope=both sid='shaik'; alter system reset open_cursors scope=both sid='shaik' * ERROR at line 1: ORA-32010: cannot find entry to delete in SPFILE Cause and Solution of The Problem Inside the spfile if you notice the parameter is set for all instances. That is with *. Oracle try to finds/looks in spfile for parameter with name shaik.open_cursors but could not find tha...

How to recover or recreate temporary tablespace in 10g

In database you may discover that your temporary tablespace is deleted from OS or it might got corrupted. In order to get it back you might think about recover it. The recovery process is simply recover the temporary file from backup and roll the file forward using archived log files if you are in archivelog mode. Another solution is simply drop the temporary tablespace and then create a new one and assign new one to the database users. In order to do that follow the steps here. 1)Find out the temporary datafiles. SQL> col file_name format a50 SQL> set linesize 200 SQL> select file_name,file_id, tablespace_name from dba_temp_files; FILE_NAME FILE_ID TABLESPACE_NAME -------------------------------------------------- ---------- ------------------------------ /oradata2/temp2.dbf 1 TEMP2 /oradata2/temp.dbf 2 TEMP /oradata2/temp3.dbf ...