Posts

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...
Post a Comment
Read more

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...
Post a Comment
Read more

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...
Post a Comment
Read more

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...
Post a Comment
Read more

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...
Post a Comment
Read more

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...
Post a Comment
Read more

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 ...
Post a Comment
Read more