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 number 4547817
ii)To upgrade to 10.2.0.3 patchset number 5337014
iii)To upgrade to 10.2.0.4 patchset number 6810189
iv)For Warehouse build of 10.2.0.4 patchset number 7005587
D)For Oracle11gR1 Base version 11.1.0.6
i)To upgrade to 11.1.0.7.0 Patchset number 6890831
Friday, January 16, 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 number 4547817
ii)To upgrade to 10.2.0.3 patchset number 5337014
iii)To upgrade to 10.2.0.4 patchset number 6810189
iv)For Warehouse build of 10.2.0.4 patchset number 7005587
D)For Oracle11gR1 Base version 11.1.0.6
i)To upgrade to 11.1.0.7.0 Patchset number 6890831
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 number 4547817
ii)To upgrade to 10.2.0.3 patchset number 5337014
iii)To upgrade to 10.2.0.4 patchset number 6810189
iv)For Warehouse build of 10.2.0.4 patchset number 7005587
D)For Oracle11gR1 Base version 11.1.0.6
i)To upgrade to 11.1.0.7.0 Patchset number 6890831
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
bash-3.00$ lsnrctl status
LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 08-OCT-2008 06:07:54
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date 27-JUL-2008 02:00:31
Uptime 73 days 4 hr. 7 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=saturn)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "SHAIK.SATURN.SHAIKBD.COM" has 1 instance(s).
Instance "SHAIK", status READY, has 1 handler(s) for this service...
The command completed successfully
Our interest is bolded. hostname=saturn, port=1521(default) and service_name=SHAIK.SATURN.SHAIKBD.COM
Try to connect to source database from destination database (from jupiter to saturn)
bash-3.00$ hostname
jupiter
bash-3.00$ rman target system/sistem@saturn/ASHAIK.SATURN.SHAIKBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 8 05:48:50 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges
We did not used port here as port is default. If it was not default suppose it was 1522 then we would use rman target system/sistem@saturn:1522/SHAIK.SATURN.SHAIKBD.COM
Diagnosis why problem happens (In the source database)
In order to connect to rman a user must have the sysdba system privilege otherwise it will fail. As we are login through network password file authentication will be used. And in the password file user system must be entryed as sysdba.
SQL> !hostname
saturn
As we see here in the passwordfile only SYS is there. So only sys can do the task to connect to rman through network. In order to permit system to logon through network grant him to SYSDBA.
SQL> grant sysdba to system;
Grant succeeded.
Now check the entry in passwordfile.
Check now from destination database (Here it is jupiter)
bash-3.00$ hostname
jupiter
bash-3.00$ rman target system/sistem@saturn/SHAIK.SATURN.SHAIKBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 8 05:50:13 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: SHAIK(DBID=2869417476)
And now it succeeds.
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 Solaris: Version 10.2.0.1.0 - Production on 08-OCT-2008 06:07:54
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date 27-JUL-2008 02:00:31
Uptime 73 days 4 hr. 7 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=saturn)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "SHAIK.SATURN.SHAIKBD.COM" has 1 instance(s).
Instance "SHAIK", status READY, has 1 handler(s) for this service...
The command completed successfully
Our interest is bolded. hostname=saturn, port=1521(default) and service_name=SHAIK.SATURN.SHAIKBD.COM
Try to connect to source database from destination database (from jupiter to saturn)
bash-3.00$ hostname
jupiter
bash-3.00$ rman target system/sistem@saturn/ASHAIK.SATURN.SHAIKBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 8 05:48:50 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges
We did not used port here as port is default. If it was not default suppose it was 1522 then we would use rman target system/sistem@saturn:1522/SHAIK.SATURN.SHAIKBD.COM
Diagnosis why problem happens (In the source database)
In order to connect to rman a user must have the sysdba system privilege otherwise it will fail. As we are login through network password file authentication will be used. And in the password file user system must be entryed as sysdba.
SQL> !hostname
saturn
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
As we see here in the passwordfile only SYS is there. So only sys can do the task to connect to rman through network. In order to permit system to logon through network grant him to SYSDBA.
SQL> grant sysdba to system;
Grant succeeded.
Now check the entry in passwordfile.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
SYSTEM TRUE FALSE
Check now from destination database (Here it is jupiter)
bash-3.00$ hostname
jupiter
bash-3.00$ rman target system/sistem@saturn/SHAIK.SATURN.SHAIKBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 8 05:50:13 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: SHAIK(DBID=2869417476)
And now it succeeds.
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
bash-3.00$ lsnrctl status
LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 08-OCT-2008 06:07:54
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date 27-JUL-2008 02:00:31
Uptime 73 days 4 hr. 7 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=saturn)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "SHAIK.SATURN.SHAIKBD.COM" has 1 instance(s).
Instance "SHAIK", status READY, has 1 handler(s) for this service...
The command completed successfully
Our interest is bolded. hostname=saturn, port=1521(default) and service_name=SHAIK.SATURN.SHAIKBD.COM
Try to connect to source database from destination database (from jupiter to saturn)
bash-3.00$ hostname
jupiter
bash-3.00$ rman target system/sistem@saturn/ASHAIK.SATURN.SHAIKBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 8 05:48:50 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges
We did not used port here as port is default. If it was not default suppose it was 1522 then we would use rman target system/sistem@saturn:1522/SHAIK.SATURN.SHAIKBD.COM
Diagnosis why problem happens (In the source database)
In order to connect to rman a user must have the sysdba system privilege otherwise it will fail. As we are login through network password file authentication will be used. And in the password file user system must be entryed as sysdba.
SQL> !hostname
saturn
As we see here in the passwordfile only SYS is there. So only sys can do the task to connect to rman through network. In order to permit system to logon through network grant him to SYSDBA.
SQL> grant sysdba to system;
Grant succeeded.
Now check the entry in passwordfile.
Check now from destination database (Here it is jupiter)
bash-3.00$ hostname
jupiter
bash-3.00$ rman target system/sistem@saturn/SHAIK.SATURN.SHAIKBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 8 05:50:13 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: SHAIK(DBID=2869417476)
And now it succeeds.
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 Solaris: Version 10.2.0.1.0 - Production on 08-OCT-2008 06:07:54
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
Start Date 27-JUL-2008 02:00:31
Uptime 73 days 4 hr. 7 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=saturn)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "SHAIK.SATURN.SHAIKBD.COM" has 1 instance(s).
Instance "SHAIK", status READY, has 1 handler(s) for this service...
The command completed successfully
Our interest is bolded. hostname=saturn, port=1521(default) and service_name=SHAIK.SATURN.SHAIKBD.COM
Try to connect to source database from destination database (from jupiter to saturn)
bash-3.00$ hostname
jupiter
bash-3.00$ rman target system/sistem@saturn/ASHAIK.SATURN.SHAIKBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 8 05:48:50 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges
We did not used port here as port is default. If it was not default suppose it was 1522 then we would use rman target system/sistem@saturn:1522/SHAIK.SATURN.SHAIKBD.COM
Diagnosis why problem happens (In the source database)
In order to connect to rman a user must have the sysdba system privilege otherwise it will fail. As we are login through network password file authentication will be used. And in the password file user system must be entryed as sysdba.
SQL> !hostname
saturn
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
As we see here in the passwordfile only SYS is there. So only sys can do the task to connect to rman through network. In order to permit system to logon through network grant him to SYSDBA.
SQL> grant sysdba to system;
Grant succeeded.
Now check the entry in passwordfile.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
SYSTEM TRUE FALSE
Check now from destination database (Here it is jupiter)
bash-3.00$ hostname
jupiter
bash-3.00$ rman target system/sistem@saturn/SHAIK.SATURN.SHAIKBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 8 05:50:13 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: SHAIK(DBID=2869417476)
And now it succeeds.
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. If you are sure there is wrong entry inside spfile/pfile then you have to edit the db_name parameter in the spfile/pfile.
-If you have pfile then open it with text editor and edit db_name initialization parameter.
-If you have default spfile then use ALTER SYSTEM SET DB_NAME=real_name scope=spfile
and restart your database.
If you are sure that your spfile/pfile db_name parameter is correct and there is wrong setting on controlfile then edit the control file text script. Change database wrong_database_name to database right_database_name in the script.
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. If you are sure there is wrong entry inside spfile/pfile then you have to edit the db_name parameter in the spfile/pfile.
-If you have pfile then open it with text editor and edit db_name initialization parameter.
-If you have default spfile then use ALTER SYSTEM SET DB_NAME=real_name scope=spfile
and restart your database.
If you are sure that your spfile/pfile db_name parameter is correct and there is wrong setting on controlfile then edit the control file text script. Change database wrong_database_name to database right_database_name in the script.
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. If you are sure there is wrong entry inside spfile/pfile then you have to edit the db_name parameter in the spfile/pfile.
-If you have pfile then open it with text editor and edit db_name initialization parameter.
-If you have default spfile then use ALTER SYSTEM SET DB_NAME=real_name scope=spfile
and restart your database.
If you are sure that your spfile/pfile db_name parameter is correct and there is wrong setting on controlfile then edit the control file text script. Change database wrong_database_name to database right_database_name in the script.
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. If you are sure there is wrong entry inside spfile/pfile then you have to edit the db_name parameter in the spfile/pfile.
-If you have pfile then open it with text editor and edit db_name initialization parameter.
-If you have default spfile then use ALTER SYSTEM SET DB_NAME=real_name scope=spfile
and restart your database.
If you are sure that your spfile/pfile db_name parameter is correct and there is wrong setting on controlfile then edit the control file text script. Change database wrong_database_name to database right_database_name in the script.
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 been changed, more specifically database name has been changed.
Solution of the Problem
To create the new controlfile for the new database you must use the word 'SET'
in the CREATE CONTROLFILE COMMAND. The SET DATABASE command is what enables the
changes to the name of the database. So the script will be like below
STARTUP NOMOUNT PFILE='F:\PFILE.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "shaikcl" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:\ORACLE\SHAIKCL\REDO01.LOG' SIZE 50M,
GROUP 2 'F:\ORACLE\SHAIKCL\REDO02.LOG' SIZE 50M,
GROUP 3 'F:\ORACLE\SHAIKCL\REDO03.LOG' SIZE 50M
DATAFILE
'F:\ORACLE\SHAIKCL\SYSTEM01.DBF',
'F:\ORACLE\SHAIKCL\UNDOTBS01.DBF',
'F:\ORACLE\SHAIKCL\SYSAUX01.DBF',
'F:\ORACLE\SHAIKCL\USERS01.DBF',
'F:\ORACLE\SHAIKCL\EXAMPLE01.DBF',
'F:\ORACLE\SHAIKCL\TEST_TBS01.DBF'
CHARACTER SET WE8MSWIN1252
;
Now run this will successfully run,
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
Control file created.
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 been changed, more specifically database name has been changed.
Solution of the Problem
To create the new controlfile for the new database you must use the word 'SET'
in the CREATE CONTROLFILE COMMAND. The SET DATABASE command is what enables the
changes to the name of the database. So the script will be like below
STARTUP NOMOUNT PFILE='F:\PFILE.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "shaikcl" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:\ORACLE\SHAIKCL\REDO01.LOG' SIZE 50M,
GROUP 2 'F:\ORACLE\SHAIKCL\REDO02.LOG' SIZE 50M,
GROUP 3 'F:\ORACLE\SHAIKCL\REDO03.LOG' SIZE 50M
DATAFILE
'F:\ORACLE\SHAIKCL\SYSTEM01.DBF',
'F:\ORACLE\SHAIKCL\UNDOTBS01.DBF',
'F:\ORACLE\SHAIKCL\SYSAUX01.DBF',
'F:\ORACLE\SHAIKCL\USERS01.DBF',
'F:\ORACLE\SHAIKCL\EXAMPLE01.DBF',
'F:\ORACLE\SHAIKCL\TEST_TBS01.DBF'
CHARACTER SET WE8MSWIN1252
;
Now run this will successfully run,
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
Control file created.
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 been changed, more specifically database name has been changed.
Solution of the Problem
To create the new controlfile for the new database you must use the word 'SET'
in the CREATE CONTROLFILE COMMAND. The SET DATABASE command is what enables the
changes to the name of the database. So the script will be like below
STARTUP NOMOUNT PFILE='F:\PFILE.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "shaikcl" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:\ORACLE\SHAIKCL\REDO01.LOG' SIZE 50M,
GROUP 2 'F:\ORACLE\SHAIKCL\REDO02.LOG' SIZE 50M,
GROUP 3 'F:\ORACLE\SHAIKCL\REDO03.LOG' SIZE 50M
DATAFILE
'F:\ORACLE\SHAIKCL\SYSTEM01.DBF',
'F:\ORACLE\SHAIKCL\UNDOTBS01.DBF',
'F:\ORACLE\SHAIKCL\SYSAUX01.DBF',
'F:\ORACLE\SHAIKCL\USERS01.DBF',
'F:\ORACLE\SHAIKCL\EXAMPLE01.DBF',
'F:\ORACLE\SHAIKCL\TEST_TBS01.DBF'
CHARACTER SET WE8MSWIN1252
;
Now run this will successfully run,
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
Control file created.
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 been changed, more specifically database name has been changed.
Solution of the Problem
To create the new controlfile for the new database you must use the word 'SET'
in the CREATE CONTROLFILE COMMAND. The SET DATABASE command is what enables the
changes to the name of the database. So the script will be like below
STARTUP NOMOUNT PFILE='F:\PFILE.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "shaikcl" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:\ORACLE\SHAIKCL\REDO01.LOG' SIZE 50M,
GROUP 2 'F:\ORACLE\SHAIKCL\REDO02.LOG' SIZE 50M,
GROUP 3 'F:\ORACLE\SHAIKCL\REDO03.LOG' SIZE 50M
DATAFILE
'F:\ORACLE\SHAIKCL\SYSTEM01.DBF',
'F:\ORACLE\SHAIKCL\UNDOTBS01.DBF',
'F:\ORACLE\SHAIKCL\SYSAUX01.DBF',
'F:\ORACLE\SHAIKCL\USERS01.DBF',
'F:\ORACLE\SHAIKCL\EXAMPLE01.DBF',
'F:\ORACLE\SHAIKCL\TEST_TBS01.DBF'
CHARACTER SET WE8MSWIN1252
;
Now run this will successfully run,
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
Control file created.
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_name, file_id from dba_data_files;
FILE_NAME FILE_ID
---------------------------------------------------------------------- ----------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\USERS01.DBF 4
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSAUX01.DBF 3
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\UNDOTBS01.DBF 2
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSTEM01.DBF 1
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\EXAMPLE01.DBF 5
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\TEST_TBS01.DBF 6
6 rows selected.
SQL> select member from v$logfile;
MEMBER
----------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO02.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO01.LOG
Step 02:
Create a pfile from source spfile and controfile trace from source controlfile.
SQL> create pfile='f:\pfile.ora' from spfile;
File created.
SQL> alter database backup controlfile to trace as 'f:\controlfile.ctl';
Database altered.
On linux based system as linux file structure to save controlfile. Like instead of f:\controlfile.ctl use /oracle/controlfile.ctl
Step 03:
Shutdown the source database.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step 04:
Create the required directory for the cloned database. You should do this step in previous.
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Queen>mkdir f:\oracle
C:\Documents and Settings\Queen>mkdir f:\oracle\shaikcl
On unix based machine adjust the location as you wish to create clone database.
Step 05:
Copy datafiles and redo log files to the location of f:\oracle\shaikcl
C:\Documents and Settings\Queen>copy E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\*.dbf f:\oracle\shaikcl
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\TEMP01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\TEST_TBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\USERS01.DBF
7 file(s) copied.
C:\Documents and Settings\Queen>copy E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\*.log f:\oracle\shaikcl
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO01.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO02.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO03.LOG
3 file(s) copied.
Step 06:
At this stage task with the source database is finished. Edit the pfile f:\pfile.ora and change parameter control_files, db_name, different dump directory in order to reflect cloned database name and location.
On windows,
C:\Documents and Settings\Queen>notepad f:\pfile.ora
shaikcl.__db_cache_size=25165824
shaikcl.__java_pool_size=4194304
shaikcl.__large_pool_size=4194304
shaikcl.__shared_pool_size=62914560
shaikcl.__streams_pool_size=0
*.audit_file_dest='F:\oracle\adump'
*.audit_trail='DB','EXTENDED'
*.background_dump_dest='F:\oracle\bdump'
*.compatible='10.2.0.1.0'
*.control_files='F:\oracle\control01.ctl',
'F:\oracle\control02.ctl','F:\oracle\control03.ctl'
*.core_dump_dest='F:\oracle\cdump'
*.db_block_size=8192
*.db_create_file_dest='C:\'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='shaikcl'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='c:\test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=shaikclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=c:\test'
*.log_archive_dest_2='LOCATION=g:\'
*.log_archive_dest_3='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.open_cursors=300
*.pga_aggregate_target=202375168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=104857600
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='F:\oracle\udump'
On linux based system use as, $vi pfile.ora
Step 07:
Edit the created text controlfile and change the database name along with logfile and datafile location and use SET DATABASE database_name in the controlfile creation script.
On windows,
C:\Documents and Settings\Queen>notepad f:\CONTROLFILE.CTL
STARTUP NOMOUNT PFILE='F:\PFILE.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "shaikcl" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:\ORACLE\SHAIKCL\REDO01.LOG' SIZE 50M,
GROUP 2 'F:\ORACLE\SHAIKCL\REDO02.LOG' SIZE 50M,
GROUP 3 'F:\ORACLE\SHAIKCL\REDO03.LOG' SIZE 50M
DATAFILE
'F:\ORACLE\SHAIKCL\SYSTEM01.DBF',
'F:\ORACLE\SHAIKCL\UNDOTBS01.DBF',
'F:\ORACLE\SHAIKCL\SYSAUX01.DBF',
'F:\ORACLE\SHAIKCL\USERS01.DBF',
'F:\ORACLE\SHAIKCL\EXAMPLE01.DBF',
'F:\ORACLE\SHAIKCL\TEST_TBS01.DBF'
CHARACTER SET WE8MSWIN1252
;
On unix based system open the controlfile with any editor software and then edit.
Step 08:Avoid this step if you are on unix based system. On windows, with oradim create new oracle service shaikcl. However if you are on unix machine then simply ignore this step.
C:\Documents and Settings\Queen>oradim -new -sid shaikcl
Instance created.
Step 09:
Set environmental variable to shaikcl and connect to instance as sysdba.
C:\Documents and Settings\Queen>set ORACLE_SID=shaikcl
On unix based system, export ORACLE_SID=shaikcl
C:\Documents and Settings\Queen>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 7 00:04:32 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
Step 10:
Run the controlfile creation script.
On windows,
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
Control file created.
On unix based system run the modified controlfile script where you saved.
Step 11:
Open the database with resetlogs option.
SQL> alter database open resetlogs;
Database altered.
Check the cloned database name.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string shaikcl
You can check the cloned database datafile location.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
F:\ORACLE\SHAIKCL\TEST_TBS01.DBF
F:\ORACLE\SHAIKCL\EXAMPLE01.DBF
F:\ORACLE\SHAIKCL\USERS01.DBF
F:\ORACLE\SHAIKCL\SYSAUX01.DBF
F:\ORACLE\SHAIKCL\UNDOTBS01.DBF
F:\ORACLE\SHAIKCL\SYSTEM01.DBF
6 rows selected.
-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_name, file_id from dba_data_files;
FILE_NAME FILE_ID
---------------------------------------------------------------------- ----------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\USERS01.DBF 4
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSAUX01.DBF 3
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\UNDOTBS01.DBF 2
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSTEM01.DBF 1
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\EXAMPLE01.DBF 5
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\TEST_TBS01.DBF 6
6 rows selected.
SQL> select member from v$logfile;
MEMBER
----------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO02.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO01.LOG
Step 02:
Create a pfile from source spfile and controfile trace from source controlfile.
SQL> create pfile='f:\pfile.ora' from spfile;
File created.
SQL> alter database backup controlfile to trace as 'f:\controlfile.ctl';
Database altered.
On linux based system as linux file structure to save controlfile. Like instead of f:\controlfile.ctl use /oracle/controlfile.ctl
Step 03:
Shutdown the source database.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step 04:
Create the required directory for the cloned database. You should do this step in previous.
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Queen>mkdir f:\oracle
C:\Documents and Settings\Queen>mkdir f:\oracle\shaikcl
On unix based machine adjust the location as you wish to create clone database.
Step 05:
Copy datafiles and redo log files to the location of f:\oracle\shaikcl
C:\Documents and Settings\Queen>copy E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\*.dbf f:\oracle\shaikcl
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\TEMP01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\TEST_TBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\USERS01.DBF
7 file(s) copied.
C:\Documents and Settings\Queen>copy E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\*.log f:\oracle\shaikcl
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO01.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO02.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO03.LOG
3 file(s) copied.
Step 06:
At this stage task with the source database is finished. Edit the pfile f:\pfile.ora and change parameter control_files, db_name, different dump directory in order to reflect cloned database name and location.
On windows,
C:\Documents and Settings\Queen>notepad f:\pfile.ora
shaikcl.__db_cache_size=25165824
shaikcl.__java_pool_size=4194304
shaikcl.__large_pool_size=4194304
shaikcl.__shared_pool_size=62914560
shaikcl.__streams_pool_size=0
*.audit_file_dest='F:\oracle\adump'
*.audit_trail='DB','EXTENDED'
*.background_dump_dest='F:\oracle\bdump'
*.compatible='10.2.0.1.0'
*.control_files='F:\oracle\control01.ctl',
'F:\oracle\control02.ctl','F:\oracle\control03.ctl'
*.core_dump_dest='F:\oracle\cdump'
*.db_block_size=8192
*.db_create_file_dest='C:\'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='shaikcl'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='c:\test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=shaikclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=c:\test'
*.log_archive_dest_2='LOCATION=g:\'
*.log_archive_dest_3='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.open_cursors=300
*.pga_aggregate_target=202375168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=104857600
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='F:\oracle\udump'
On linux based system use as, $vi pfile.ora
Step 07:
Edit the created text controlfile and change the database name along with logfile and datafile location and use SET DATABASE database_name in the controlfile creation script.
On windows,
C:\Documents and Settings\Queen>notepad f:\CONTROLFILE.CTL
STARTUP NOMOUNT PFILE='F:\PFILE.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "shaikcl" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:\ORACLE\SHAIKCL\REDO01.LOG' SIZE 50M,
GROUP 2 'F:\ORACLE\SHAIKCL\REDO02.LOG' SIZE 50M,
GROUP 3 'F:\ORACLE\SHAIKCL\REDO03.LOG' SIZE 50M
DATAFILE
'F:\ORACLE\SHAIKCL\SYSTEM01.DBF',
'F:\ORACLE\SHAIKCL\UNDOTBS01.DBF',
'F:\ORACLE\SHAIKCL\SYSAUX01.DBF',
'F:\ORACLE\SHAIKCL\USERS01.DBF',
'F:\ORACLE\SHAIKCL\EXAMPLE01.DBF',
'F:\ORACLE\SHAIKCL\TEST_TBS01.DBF'
CHARACTER SET WE8MSWIN1252
;
On unix based system open the controlfile with any editor software and then edit.
Step 08:Avoid this step if you are on unix based system. On windows, with oradim create new oracle service shaikcl. However if you are on unix machine then simply ignore this step.
C:\Documents and Settings\Queen>oradim -new -sid shaikcl
Instance created.
Step 09:
Set environmental variable to shaikcl and connect to instance as sysdba.
C:\Documents and Settings\Queen>set ORACLE_SID=shaikcl
On unix based system, export ORACLE_SID=shaikcl
C:\Documents and Settings\Queen>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 7 00:04:32 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
Step 10:
Run the controlfile creation script.
On windows,
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
Control file created.
On unix based system run the modified controlfile script where you saved.
Step 11:
Open the database with resetlogs option.
SQL> alter database open resetlogs;
Database altered.
Check the cloned database name.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string shaikcl
You can check the cloned database datafile location.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
F:\ORACLE\SHAIKCL\TEST_TBS01.DBF
F:\ORACLE\SHAIKCL\EXAMPLE01.DBF
F:\ORACLE\SHAIKCL\USERS01.DBF
F:\ORACLE\SHAIKCL\SYSAUX01.DBF
F:\ORACLE\SHAIKCL\UNDOTBS01.DBF
F:\ORACLE\SHAIKCL\SYSTEM01.DBF
6 rows selected.
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_name, file_id from dba_data_files;
FILE_NAME FILE_ID
---------------------------------------------------------------------- ----------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\USERS01.DBF 4
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSAUX01.DBF 3
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\UNDOTBS01.DBF 2
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSTEM01.DBF 1
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\EXAMPLE01.DBF 5
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\TEST_TBS01.DBF 6
6 rows selected.
SQL> select member from v$logfile;
MEMBER
----------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO02.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO01.LOG
Step 02:
Create a pfile from source spfile and controfile trace from source controlfile.
SQL> create pfile='f:\pfile.ora' from spfile;
File created.
SQL> alter database backup controlfile to trace as 'f:\controlfile.ctl';
Database altered.
On linux based system as linux file structure to save controlfile. Like instead of f:\controlfile.ctl use /oracle/controlfile.ctl
Step 03:
Shutdown the source database.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step 04:
Create the required directory for the cloned database. You should do this step in previous.
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Queen>mkdir f:\oracle
C:\Documents and Settings\Queen>mkdir f:\oracle\shaikcl
On unix based machine adjust the location as you wish to create clone database.
Step 05:
Copy datafiles and redo log files to the location of f:\oracle\shaikcl
C:\Documents and Settings\Queen>copy E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\*.dbf f:\oracle\shaikcl
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\TEMP01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\TEST_TBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\USERS01.DBF
7 file(s) copied.
C:\Documents and Settings\Queen>copy E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\*.log f:\oracle\shaikcl
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO01.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO02.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO03.LOG
3 file(s) copied.
Step 06:
At this stage task with the source database is finished. Edit the pfile f:\pfile.ora and change parameter control_files, db_name, different dump directory in order to reflect cloned database name and location.
On windows,
C:\Documents and Settings\Queen>notepad f:\pfile.ora
shaikcl.__db_cache_size=25165824
shaikcl.__java_pool_size=4194304
shaikcl.__large_pool_size=4194304
shaikcl.__shared_pool_size=62914560
shaikcl.__streams_pool_size=0
*.audit_file_dest='F:\oracle\adump'
*.audit_trail='DB','EXTENDED'
*.background_dump_dest='F:\oracle\bdump'
*.compatible='10.2.0.1.0'
*.control_files='F:\oracle\control01.ctl',
'F:\oracle\control02.ctl','F:\oracle\control03.ctl'
*.core_dump_dest='F:\oracle\cdump'
*.db_block_size=8192
*.db_create_file_dest='C:\'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='shaikcl'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='c:\test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=shaikclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=c:\test'
*.log_archive_dest_2='LOCATION=g:\'
*.log_archive_dest_3='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.open_cursors=300
*.pga_aggregate_target=202375168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=104857600
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='F:\oracle\udump'
On linux based system use as, $vi pfile.ora
Step 07:
Edit the created text controlfile and change the database name along with logfile and datafile location and use SET DATABASE database_name in the controlfile creation script.
On windows,
C:\Documents and Settings\Queen>notepad f:\CONTROLFILE.CTL
STARTUP NOMOUNT PFILE='F:\PFILE.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "shaikcl" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:\ORACLE\SHAIKCL\REDO01.LOG' SIZE 50M,
GROUP 2 'F:\ORACLE\SHAIKCL\REDO02.LOG' SIZE 50M,
GROUP 3 'F:\ORACLE\SHAIKCL\REDO03.LOG' SIZE 50M
DATAFILE
'F:\ORACLE\SHAIKCL\SYSTEM01.DBF',
'F:\ORACLE\SHAIKCL\UNDOTBS01.DBF',
'F:\ORACLE\SHAIKCL\SYSAUX01.DBF',
'F:\ORACLE\SHAIKCL\USERS01.DBF',
'F:\ORACLE\SHAIKCL\EXAMPLE01.DBF',
'F:\ORACLE\SHAIKCL\TEST_TBS01.DBF'
CHARACTER SET WE8MSWIN1252
;
On unix based system open the controlfile with any editor software and then edit.
Step 08:Avoid this step if you are on unix based system. On windows, with oradim create new oracle service shaikcl. However if you are on unix machine then simply ignore this step.
C:\Documents and Settings\Queen>oradim -new -sid shaikcl
Instance created.
Step 09:
Set environmental variable to shaikcl and connect to instance as sysdba.
C:\Documents and Settings\Queen>set ORACLE_SID=shaikcl
On unix based system, export ORACLE_SID=shaikcl
C:\Documents and Settings\Queen>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 7 00:04:32 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
Step 10:
Run the controlfile creation script.
On windows,
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
Control file created.
On unix based system run the modified controlfile script where you saved.
Step 11:
Open the database with resetlogs option.
SQL> alter database open resetlogs;
Database altered.
Check the cloned database name.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string shaikcl
You can check the cloned database datafile location.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
F:\ORACLE\SHAIKCL\TEST_TBS01.DBF
F:\ORACLE\SHAIKCL\EXAMPLE01.DBF
F:\ORACLE\SHAIKCL\USERS01.DBF
F:\ORACLE\SHAIKCL\SYSAUX01.DBF
F:\ORACLE\SHAIKCL\UNDOTBS01.DBF
F:\ORACLE\SHAIKCL\SYSTEM01.DBF
6 rows selected.
-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_name, file_id from dba_data_files;
FILE_NAME FILE_ID
---------------------------------------------------------------------- ----------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\USERS01.DBF 4
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSAUX01.DBF 3
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\UNDOTBS01.DBF 2
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSTEM01.DBF 1
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\EXAMPLE01.DBF 5
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\TEST_TBS01.DBF 6
6 rows selected.
SQL> select member from v$logfile;
MEMBER
----------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO02.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO01.LOG
Step 02:
Create a pfile from source spfile and controfile trace from source controlfile.
SQL> create pfile='f:\pfile.ora' from spfile;
File created.
SQL> alter database backup controlfile to trace as 'f:\controlfile.ctl';
Database altered.
On linux based system as linux file structure to save controlfile. Like instead of f:\controlfile.ctl use /oracle/controlfile.ctl
Step 03:
Shutdown the source database.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step 04:
Create the required directory for the cloned database. You should do this step in previous.
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Queen>mkdir f:\oracle
C:\Documents and Settings\Queen>mkdir f:\oracle\shaikcl
On unix based machine adjust the location as you wish to create clone database.
Step 05:
Copy datafiles and redo log files to the location of f:\oracle\shaikcl
C:\Documents and Settings\Queen>copy E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\*.dbf f:\oracle\shaikcl
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\TEMP01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\TEST_TBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\USERS01.DBF
7 file(s) copied.
C:\Documents and Settings\Queen>copy E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\*.log f:\oracle\shaikcl
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO01.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO02.LOG
E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\REDO03.LOG
3 file(s) copied.
Step 06:
At this stage task with the source database is finished. Edit the pfile f:\pfile.ora and change parameter control_files, db_name, different dump directory in order to reflect cloned database name and location.
On windows,
C:\Documents and Settings\Queen>notepad f:\pfile.ora
shaikcl.__db_cache_size=25165824
shaikcl.__java_pool_size=4194304
shaikcl.__large_pool_size=4194304
shaikcl.__shared_pool_size=62914560
shaikcl.__streams_pool_size=0
*.audit_file_dest='F:\oracle\adump'
*.audit_trail='DB','EXTENDED'
*.background_dump_dest='F:\oracle\bdump'
*.compatible='10.2.0.1.0'
*.control_files='F:\oracle\control01.ctl',
'F:\oracle\control02.ctl','F:\oracle\control03.ctl'
*.core_dump_dest='F:\oracle\cdump'
*.db_block_size=8192
*.db_create_file_dest='C:\'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='shaikcl'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='c:\test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=shaikclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=c:\test'
*.log_archive_dest_2='LOCATION=g:\'
*.log_archive_dest_3='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.open_cursors=300
*.pga_aggregate_target=202375168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=104857600
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='F:\oracle\udump'
On linux based system use as, $vi pfile.ora
Step 07:
Edit the created text controlfile and change the database name along with logfile and datafile location and use SET DATABASE database_name in the controlfile creation script.
On windows,
C:\Documents and Settings\Queen>notepad f:\CONTROLFILE.CTL
STARTUP NOMOUNT PFILE='F:\PFILE.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "shaikcl" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:\ORACLE\SHAIKCL\REDO01.LOG' SIZE 50M,
GROUP 2 'F:\ORACLE\SHAIKCL\REDO02.LOG' SIZE 50M,
GROUP 3 'F:\ORACLE\SHAIKCL\REDO03.LOG' SIZE 50M
DATAFILE
'F:\ORACLE\SHAIKCL\SYSTEM01.DBF',
'F:\ORACLE\SHAIKCL\UNDOTBS01.DBF',
'F:\ORACLE\SHAIKCL\SYSAUX01.DBF',
'F:\ORACLE\SHAIKCL\USERS01.DBF',
'F:\ORACLE\SHAIKCL\EXAMPLE01.DBF',
'F:\ORACLE\SHAIKCL\TEST_TBS01.DBF'
CHARACTER SET WE8MSWIN1252
;
On unix based system open the controlfile with any editor software and then edit.
Step 08:Avoid this step if you are on unix based system. On windows, with oradim create new oracle service shaikcl. However if you are on unix machine then simply ignore this step.
C:\Documents and Settings\Queen>oradim -new -sid shaikcl
Instance created.
Step 09:
Set environmental variable to shaikcl and connect to instance as sysdba.
C:\Documents and Settings\Queen>set ORACLE_SID=shaikcl
On unix based system, export ORACLE_SID=shaikcl
C:\Documents and Settings\Queen>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 7 00:04:32 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
Step 10:
Run the controlfile creation script.
On windows,
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
Control file created.
On unix based system run the modified controlfile script where you saved.
Step 11:
Open the database with resetlogs option.
SQL> alter database open resetlogs;
Database altered.
Check the cloned database name.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string shaikcl
You can check the cloned database datafile location.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
F:\ORACLE\SHAIKCL\TEST_TBS01.DBF
F:\ORACLE\SHAIKCL\EXAMPLE01.DBF
F:\ORACLE\SHAIKCL\USERS01.DBF
F:\ORACLE\SHAIKCL\SYSAUX01.DBF
F:\ORACLE\SHAIKCL\UNDOTBS01.DBF
F:\ORACLE\SHAIKCL\SYSTEM01.DBF
6 rows selected.
Thursday, January 15, 2009
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.
Step 04: Rename the original Audit table.
rename AUD$ to AUD$$;
Step 05: Rename the aud_aux to AUD$
rename aud_aux to aud$;
Step 06: Create Index on the AUD$ table.
create index aud_i
on aud$(sessionid, ses$tid)
tablespace AUDIT_TBS;
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.
Step 04: Rename the original Audit table.
rename AUD$ to AUD$$;
Step 05: Rename the aud_aux to AUD$
rename aud_aux to aud$;
Step 06: Create Index on the AUD$ table.
create index aud_i
on aud$(sessionid, ses$tid)
tablespace AUDIT_TBS;
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 back to SYS.AUD$.
SQL> insert into sys.aud$ select * from audit_record;
You can also import it if you exported it in step 2.
5)Drop the audit_record table(optional).
SQL>DROP TABLE audit_record;
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 back to SYS.AUD$.
SQL> insert into sys.aud$ select * from audit_record;
You can also import it if you exported it in step 2.
5)Drop the audit_record table(optional).
SQL>DROP TABLE audit_record;
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)Truncate audit table to reduce size.
SQL>CONN / as sysdba
SQL>TRUNCATE TABLE sys.aud$;
Truncate uses the DROP STORAGE clause but keeps only minextents extents, thus only 1 extent.
-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)Truncate audit table to reduce size.
SQL>CONN / as sysdba
SQL>TRUNCATE TABLE sys.aud$;
Truncate uses the DROP STORAGE clause but keeps only minextents extents, thus only 1 extent.
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.
Step 04: Rename the original Audit table.
rename AUD$ to AUD$$;
Step 05: Rename the aud_aux to AUD$
rename aud_aux to aud$;
Step 06: Create Index on the AUD$ table.
create index aud_i
on aud$(sessionid, ses$tid)
tablespace AUDIT_TBS;
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.
Step 04: Rename the original Audit table.
rename AUD$ to AUD$$;
Step 05: Rename the aud_aux to AUD$
rename aud_aux to aud$;
Step 06: Create Index on the AUD$ table.
create index aud_i
on aud$(sessionid, ses$tid)
tablespace AUDIT_TBS;
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 back to SYS.AUD$.
SQL> insert into sys.aud$ select * from audit_record;
You can also import it if you exported it in step 2.
5)Drop the audit_record table(optional).
SQL>DROP TABLE audit_record;
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 back to SYS.AUD$.
SQL> insert into sys.aud$ select * from audit_record;
You can also import it if you exported it in step 2.
5)Drop the audit_record table(optional).
SQL>DROP TABLE audit_record;
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)Truncate audit table to reduce size.
SQL>CONN / as sysdba
SQL>TRUNCATE TABLE sys.aud$;
Truncate uses the DROP STORAGE clause but keeps only minextents extents, thus only 1 extent.
-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)Truncate audit table to reduce size.
SQL>CONN / as sysdba
SQL>TRUNCATE TABLE sys.aud$;
Truncate uses the DROP STORAGE clause but keeps only minextents extents, thus only 1 extent.
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 /oradata2/shaikdba/shaikdba/shaik/users01.dbf
.
.
.
Here I get date as 08-SEP-08 which only shows date, month and year. The exact minute, hour and second are not displayed here. In order to get output as hour,minute second along with above output I have to set NLS_DATE_FORMAT. Note that this need to be set in OS level. Suppose in my bash shell, I have set
-bash-3.00$ export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
Ans now it returns as I wanted.
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 03:59:49
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 03:58:35 /oradata2/shaikdba/shaikdba/shaik/system01.dbf
2 Full 1030282 08-SEP-08 03:58:35 /oradata2/shaikdba/shaikdba/shaik/undotbs01.dbf
3 Full 1030282 08-SEP-08 03:58:35 /oradata2/shaikdba/shaikdba/shaik/sysaux01.dbf
4 Full 1030282 08-SEP-08 03:58:35 /oradata2/shaikdba/shaikdba/shaik/users01.dbf
On windows you have to set as,
>set NLS_DATE_FORMAT=MON DD, YYYY HH24:MI:SS
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 /oradata2/shaikdba/shaikdba/shaik/users01.dbf
.
.
.
Here I get date as 08-SEP-08 which only shows date, month and year. The exact minute, hour and second are not displayed here. In order to get output as hour,minute second along with above output I have to set NLS_DATE_FORMAT. Note that this need to be set in OS level. Suppose in my bash shell, I have set
-bash-3.00$ export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
Ans now it returns as I wanted.
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 03:59:49
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 03:58:35 /oradata2/shaikdba/shaikdba/shaik/system01.dbf
2 Full 1030282 08-SEP-08 03:58:35 /oradata2/shaikdba/shaikdba/shaik/undotbs01.dbf
3 Full 1030282 08-SEP-08 03:58:35 /oradata2/shaikdba/shaikdba/shaik/sysaux01.dbf
4 Full 1030282 08-SEP-08 03:58:35 /oradata2/shaikdba/shaikdba/shaik/users01.dbf
On windows you have to set as,
>set NLS_DATE_FORMAT=MON DD, YYYY HH24:MI:SS
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 /oradata2/shaikdba/shaikdba/shaik/users01.dbf
.
.
.
Here I get date as 08-SEP-08 which only shows date, month and year. The exact minute, hour and second are not displayed here. In order to get output as hour,minute second along with above output I have to set NLS_DATE_FORMAT. Note that this need to be set in OS level. Suppose in my bash shell, I have set
-bash-3.00$ export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
Ans now it returns as I wanted.
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 03:59:49
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 03:58:35 /oradata2/shaikdba/shaikdba/shaik/system01.dbf
2 Full 1030282 08-SEP-08 03:58:35 /oradata2/shaikdba/shaikdba/shaik/undotbs01.dbf
3 Full 1030282 08-SEP-08 03:58:35 /oradata2/shaikdba/shaikdba/shaik/sysaux01.dbf
4 Full 1030282 08-SEP-08 03:58:35 /oradata2/shaikdba/shaikdba/shaik/users01.dbf
On windows you have to set as,
>set NLS_DATE_FORMAT=MON DD, YYYY HH24:MI:SS
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 /oradata2/shaikdba/shaikdba/shaik/users01.dbf
.
.
.
Here I get date as 08-SEP-08 which only shows date, month and year. The exact minute, hour and second are not displayed here. In order to get output as hour,minute second along with above output I have to set NLS_DATE_FORMAT. Note that this need to be set in OS level. Suppose in my bash shell, I have set
-bash-3.00$ export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
Ans now it returns as I wanted.
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 03:59:49
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 03:58:35 /oradata2/shaikdba/shaikdba/shaik/system01.dbf
2 Full 1030282 08-SEP-08 03:58:35 /oradata2/shaikdba/shaikdba/shaik/undotbs01.dbf
3 Full 1030282 08-SEP-08 03:58:35 /oradata2/shaikdba/shaikdba/shaik/sysaux01.dbf
4 Full 1030282 08-SEP-08 03:58:35 /oradata2/shaikdba/shaikdba/shaik/users01.dbf
On windows you have to set as,
>set NLS_DATE_FORMAT=MON DD, YYYY HH24:MI:SS
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 procedures of making partition of forms table
1)Get a creation script of the original Table.
I get it by using DBMS_METADATA package.
SQL>SET LONG 99999
SQL>SELECT DBMS_METADATA.GET_DDL('TABLE','FORMS') FROM DUAL;
CREATE TABLE "PROD"."FORMS"
( "ID" NUMBER NOT NULL ENABLE,
"SAR" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA"
2)Get the creation script of the associated Indexes of the table.
SQL> select dbms_metadata.get_ddl('INDEX',index_name) from dba_indexes where owner='PROD' and table_name='FORMS';
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
--------------------------------------------------------------------------------
CREATE INDEX "PROD"."FA_DATE" ON "PROD"."FORMS" ("CREATED_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA02"
CREATE INDEX "PROD"."TF_SAR_I" ON "PROD"."FORMS" ("SAR")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA02"
3)Get the creation script of the associated Constraints.
SQL> select dbms_metadata.get_ddl('CONSTRAINT',constraint_name)from dba_constraints where owner='PROD' and table_name='FORMS';
DBMS_METADATA.GET_DDL('CONSTRAINT',CONSTRAINT_NAME)
--------------------------------------------------------------------------------
ALTER TABLE "PROD"."FORMS" MODIFY ("ID" NOT NULL ENABLE)
ALTER TABLE "PROD"."FORMS" MODIFY ("CREATED_DATE" NOT NULL ENABLE)
4)Now create a temporary Partitioned Table
Define the range of date to make a partitioned table. Here I made 11 partitions. It's structure will be same as of FORMS table. I named here of partition table as FORMS_PART.
CREATE TABLE "FORMS_PART"
( "ID" NUMBER NOT NULL ENABLE,
"SAR" 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-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
5)Load data into the temporary partitioned table.
In order to minimize to load time greatly I gave append and nologging hints. Append hints will not generate undo data and nologging hint will generate a minimal redo log. So my data load will be faster.
insert /*+APPEND NOLOGGING */ into FORMS_PART select * from forms;
78474831 rows created.
6) After load data into paritioned table rename original table to a new one. Here I gave it a name from FORMS to FROMS_BAK. You later can dropped it. Also rename the temporary partitioned table to original table name.
SQL> rename forms to forms_bak;
Table renamed.
SQL> rename forms_part to forms;
Table renamed.
7)Rebuild the index as well as create constraints if any.
alter index FA_DATE rebuild;
alter index TF_SAR_I rebuild;
8)Gather partitioned table statistics.
EXEC DBMS_STATS.gather_table_stats('PROD', 'FORMS', cascade => TRUE);
Now have a test your original query and compare performance with the one not partitioned.
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 procedures of making partition of forms table
1)Get a creation script of the original Table.
I get it by using DBMS_METADATA package.
SQL>SET LONG 99999
SQL>SELECT DBMS_METADATA.GET_DDL('TABLE','FORMS') FROM DUAL;
CREATE TABLE "PROD"."FORMS"
( "ID" NUMBER NOT NULL ENABLE,
"SAR" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA"
2)Get the creation script of the associated Indexes of the table.
SQL> select dbms_metadata.get_ddl('INDEX',index_name) from dba_indexes where owner='PROD' and table_name='FORMS';
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
--------------------------------------------------------------------------------
CREATE INDEX "PROD"."FA_DATE" ON "PROD"."FORMS" ("CREATED_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA02"
CREATE INDEX "PROD"."TF_SAR_I" ON "PROD"."FORMS" ("SAR")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA02"
3)Get the creation script of the associated Constraints.
SQL> select dbms_metadata.get_ddl('CONSTRAINT',constraint_name)from dba_constraints where owner='PROD' and table_name='FORMS';
DBMS_METADATA.GET_DDL('CONSTRAINT',CONSTRAINT_NAME)
--------------------------------------------------------------------------------
ALTER TABLE "PROD"."FORMS" MODIFY ("ID" NOT NULL ENABLE)
ALTER TABLE "PROD"."FORMS" MODIFY ("CREATED_DATE" NOT NULL ENABLE)
4)Now create a temporary Partitioned Table
Define the range of date to make a partitioned table. Here I made 11 partitions. It's structure will be same as of FORMS table. I named here of partition table as FORMS_PART.
CREATE TABLE "FORMS_PART"
( "ID" NUMBER NOT NULL ENABLE,
"SAR" 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-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
5)Load data into the temporary partitioned table.
In order to minimize to load time greatly I gave append and nologging hints. Append hints will not generate undo data and nologging hint will generate a minimal redo log. So my data load will be faster.
insert /*+APPEND NOLOGGING */ into FORMS_PART select * from forms;
78474831 rows created.
6) After load data into paritioned table rename original table to a new one. Here I gave it a name from FORMS to FROMS_BAK. You later can dropped it. Also rename the temporary partitioned table to original table name.
SQL> rename forms to forms_bak;
Table renamed.
SQL> rename forms_part to forms;
Table renamed.
7)Rebuild the index as well as create constraints if any.
alter index FA_DATE rebuild;
alter index TF_SAR_I rebuild;
8)Gather partitioned table statistics.
EXEC DBMS_STATS.gather_table_stats('PROD', 'FORMS', cascade => TRUE);
Now have a test your original query and compare performance with the one not partitioned.
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,
partition fa_part6 values less than (to_date('01-JUN-07','DD-MON-YY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-07','DD-MON-YY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-07','DD-MON-YY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-08','DD-MON-YY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-08','DD-MON-YY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02,
*
ERROR at line 7:
ORA-14120: incompletely specified partition bound for a DATE column
Cause of The Problem
There may be several causes behind the problem. One reason is you don't use TO_DATE conversion of date column while range partition and you specified date format inside partitioning column does not match with the NLS_DATE_FORMAT setting.
Another reason is starting with 8.0.3, Oracle insists that a partition bound for a DATE
partitioning column is fully specified. Fully specified indicates that it will at least contain day, month and year with 4 digits. Here inside to_date conversion I used 2 digits of date and hence error appears. Instead of '01-JUN-08' we have to use '01-JUN-2008'.
Solution of The Problem
Along with to_date conversion use 4 digits of year for a DATE partitioning column in oracle.
After fixing format it look like 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-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
Table created.
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,
partition fa_part6 values less than (to_date('01-JUN-07','DD-MON-YY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-07','DD-MON-YY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-07','DD-MON-YY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-08','DD-MON-YY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-08','DD-MON-YY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02,
*
ERROR at line 7:
ORA-14120: incompletely specified partition bound for a DATE column
Cause of The Problem
There may be several causes behind the problem. One reason is you don't use TO_DATE conversion of date column while range partition and you specified date format inside partitioning column does not match with the NLS_DATE_FORMAT setting.
Another reason is starting with 8.0.3, Oracle insists that a partition bound for a DATE
partitioning column is fully specified. Fully specified indicates that it will at least contain day, month and year with 4 digits. Here inside to_date conversion I used 2 digits of date and hence error appears. Instead of '01-JUN-08' we have to use '01-JUN-2008'.
Solution of The Problem
Along with to_date conversion use 4 digits of year for a DATE partitioning column in oracle.
After fixing format it look like 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-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
Table created.
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 procedures of making partition of forms table
1)Get a creation script of the original Table.
I get it by using DBMS_METADATA package.
SQL>SET LONG 99999
SQL>SELECT DBMS_METADATA.GET_DDL('TABLE','FORMS') FROM DUAL;
CREATE TABLE "PROD"."FORMS"
( "ID" NUMBER NOT NULL ENABLE,
"SAR" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA"
2)Get the creation script of the associated Indexes of the table.
SQL> select dbms_metadata.get_ddl('INDEX',index_name) from dba_indexes where owner='PROD' and table_name='FORMS';
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
--------------------------------------------------------------------------------
CREATE INDEX "PROD"."FA_DATE" ON "PROD"."FORMS" ("CREATED_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA02"
CREATE INDEX "PROD"."TF_SAR_I" ON "PROD"."FORMS" ("SAR")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA02"
3)Get the creation script of the associated Constraints.
SQL> select dbms_metadata.get_ddl('CONSTRAINT',constraint_name)from dba_constraints where owner='PROD' and table_name='FORMS';
DBMS_METADATA.GET_DDL('CONSTRAINT',CONSTRAINT_NAME)
--------------------------------------------------------------------------------
ALTER TABLE "PROD"."FORMS" MODIFY ("ID" NOT NULL ENABLE)
ALTER TABLE "PROD"."FORMS" MODIFY ("CREATED_DATE" NOT NULL ENABLE)
4)Now create a temporary Partitioned Table
Define the range of date to make a partitioned table. Here I made 11 partitions. It's structure will be same as of FORMS table. I named here of partition table as FORMS_PART.
CREATE TABLE "FORMS_PART"
( "ID" NUMBER NOT NULL ENABLE,
"SAR" 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-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
5)Load data into the temporary partitioned table.
In order to minimize to load time greatly I gave append and nologging hints. Append hints will not generate undo data and nologging hint will generate a minimal redo log. So my data load will be faster.
insert /*+APPEND NOLOGGING */ into FORMS_PART select * from forms;
78474831 rows created.
6) After load data into paritioned table rename original table to a new one. Here I gave it a name from FORMS to FROMS_BAK. You later can dropped it. Also rename the temporary partitioned table to original table name.
SQL> rename forms to forms_bak;
Table renamed.
SQL> rename forms_part to forms;
Table renamed.
7)Rebuild the index as well as create constraints if any.
alter index FA_DATE rebuild;
alter index TF_SAR_I rebuild;
8)Gather partitioned table statistics.
EXEC DBMS_STATS.gather_table_stats('PROD', 'FORMS', cascade => TRUE);
Now have a test your original query and compare performance with the one not partitioned.
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 procedures of making partition of forms table
1)Get a creation script of the original Table.
I get it by using DBMS_METADATA package.
SQL>SET LONG 99999
SQL>SELECT DBMS_METADATA.GET_DDL('TABLE','FORMS') FROM DUAL;
CREATE TABLE "PROD"."FORMS"
( "ID" NUMBER NOT NULL ENABLE,
"SAR" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA"
2)Get the creation script of the associated Indexes of the table.
SQL> select dbms_metadata.get_ddl('INDEX',index_name) from dba_indexes where owner='PROD' and table_name='FORMS';
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
--------------------------------------------------------------------------------
CREATE INDEX "PROD"."FA_DATE" ON "PROD"."FORMS" ("CREATED_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA02"
CREATE INDEX "PROD"."TF_SAR_I" ON "PROD"."FORMS" ("SAR")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA02"
3)Get the creation script of the associated Constraints.
SQL> select dbms_metadata.get_ddl('CONSTRAINT',constraint_name)from dba_constraints where owner='PROD' and table_name='FORMS';
DBMS_METADATA.GET_DDL('CONSTRAINT',CONSTRAINT_NAME)
--------------------------------------------------------------------------------
ALTER TABLE "PROD"."FORMS" MODIFY ("ID" NOT NULL ENABLE)
ALTER TABLE "PROD"."FORMS" MODIFY ("CREATED_DATE" NOT NULL ENABLE)
4)Now create a temporary Partitioned Table
Define the range of date to make a partitioned table. Here I made 11 partitions. It's structure will be same as of FORMS table. I named here of partition table as FORMS_PART.
CREATE TABLE "FORMS_PART"
( "ID" NUMBER NOT NULL ENABLE,
"SAR" 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-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
5)Load data into the temporary partitioned table.
In order to minimize to load time greatly I gave append and nologging hints. Append hints will not generate undo data and nologging hint will generate a minimal redo log. So my data load will be faster.
insert /*+APPEND NOLOGGING */ into FORMS_PART select * from forms;
78474831 rows created.
6) After load data into paritioned table rename original table to a new one. Here I gave it a name from FORMS to FROMS_BAK. You later can dropped it. Also rename the temporary partitioned table to original table name.
SQL> rename forms to forms_bak;
Table renamed.
SQL> rename forms_part to forms;
Table renamed.
7)Rebuild the index as well as create constraints if any.
alter index FA_DATE rebuild;
alter index TF_SAR_I rebuild;
8)Gather partitioned table statistics.
EXEC DBMS_STATS.gather_table_stats('PROD', 'FORMS', cascade => TRUE);
Now have a test your original query and compare performance with the one not partitioned.
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,
partition fa_part6 values less than (to_date('01-JUN-07','DD-MON-YY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-07','DD-MON-YY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-07','DD-MON-YY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-08','DD-MON-YY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-08','DD-MON-YY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02,
*
ERROR at line 7:
ORA-14120: incompletely specified partition bound for a DATE column
Cause of The Problem
There may be several causes behind the problem. One reason is you don't use TO_DATE conversion of date column while range partition and you specified date format inside partitioning column does not match with the NLS_DATE_FORMAT setting.
Another reason is starting with 8.0.3, Oracle insists that a partition bound for a DATE
partitioning column is fully specified. Fully specified indicates that it will at least contain day, month and year with 4 digits. Here inside to_date conversion I used 2 digits of date and hence error appears. Instead of '01-JUN-08' we have to use '01-JUN-2008'.
Solution of The Problem
Along with to_date conversion use 4 digits of year for a DATE partitioning column in oracle.
After fixing format it look like 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-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
Table created.
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,
partition fa_part6 values less than (to_date('01-JUN-07','DD-MON-YY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-07','DD-MON-YY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-07','DD-MON-YY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-08','DD-MON-YY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-08','DD-MON-YY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02,
*
ERROR at line 7:
ORA-14120: incompletely specified partition bound for a DATE column
Cause of The Problem
There may be several causes behind the problem. One reason is you don't use TO_DATE conversion of date column while range partition and you specified date format inside partitioning column does not match with the NLS_DATE_FORMAT setting.
Another reason is starting with 8.0.3, Oracle insists that a partition bound for a DATE
partitioning column is fully specified. Fully specified indicates that it will at least contain day, month and year with 4 digits. Here inside to_date conversion I used 2 digits of date and hence error appears. Instead of '01-JUN-08' we have to use '01-JUN-2008'.
Solution of The Problem
Along with to_date conversion use 4 digits of year for a DATE partitioning column in oracle.
After fixing format it look like 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-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
Table created.
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 semaphores on operating system. You set semmns 32767 .
To make the setting permanent make an entry in /etc/sysctl.conf file on linux system.
sem = semmsl semmns semopm semmni
kernel.sem = 256 32768 100 228
Solution 2)
Change the initialization parameter processes to a lower one in the initialization file and then startup the database. In my initialization file processes was set to 555. Whenever I start my database it fails with above error. I then reduced it to by 55 and it started my database successfully.
CREATE PFILE='1.pfile' FROM SPFILE;
edit pfile and set Processes parameter to lower value
STARTUP PFILE='1.pfile';
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 semaphores on operating system. You set semmns 32767 .
To make the setting permanent make an entry in /etc/sysctl.conf file on linux system.
sem = semmsl semmns semopm semmni
kernel.sem = 256 32768 100 228
Solution 2)
Change the initialization parameter processes to a lower one in the initialization file and then startup the database. In my initialization file processes was set to 555. Whenever I start my database it fails with above error. I then reduced it to by 55 and it started my database successfully.
CREATE PFILE='1.pfile' FROM SPFILE;
edit pfile and set Processes parameter to lower value
STARTUP PFILE='1.pfile';
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;
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 semaphores on operating system. You set semmns 32767 .
To make the setting permanent make an entry in /etc/sysctl.conf file on linux system.
sem = semmsl semmns semopm semmni
kernel.sem = 256 32768 100 228
Solution 2)
Change the initialization parameter processes to a lower one in the initialization file and then startup the database. In my initialization file processes was set to 555. Whenever I start my database it fails with above error. I then reduced it to by 55 and it started my database successfully.
CREATE PFILE='1.pfile' FROM SPFILE;
edit pfile and set Processes parameter to lower value
STARTUP PFILE='1.pfile';
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 semaphores on operating system. You set semmns 32767 .
To make the setting permanent make an entry in /etc/sysctl.conf file on linux system.
sem = semmsl semmns semopm semmni
kernel.sem = 256 32768 100 228
Solution 2)
Change the initialization parameter processes to a lower one in the initialization file and then startup the database. In my initialization file processes was set to 555. Whenever I start my database it fails with above error. I then reduced it to by 55 and it started my database successfully.
CREATE PFILE='1.pfile' FROM SPFILE;
edit pfile and set Processes parameter to lower value
STARTUP PFILE='1.pfile';
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;
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;
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.
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-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.
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.
Wednesday, January 14, 2009
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 varchar2(200);
Table altered.
SQL> alter index tab3_I rebuild online;
alter index tab3_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded
Cause of the Problem
When creating a index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.
If 2K block size then maximum index key length=758
If 4K block size then maximum index key length=1578
If 8K block size then maximum index key length=3218
If 16K block size then maximum index key length=6498
How the maximum index key length is measured by?
Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)
The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.
So we can say that the maximum key length for an index will be less than half of
the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.
Solution of the Problem
The causes already indicates what might be the solutions. Solution may be,
1)Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.
2)If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.
3)Rebuild the index without online clause. That is
ALTER INDEX index_name REBUILD;
Because The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.
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 varchar2(200);
Table altered.
SQL> alter index tab3_I rebuild online;
alter index tab3_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded
Cause of the Problem
When creating a index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.
If 2K block size then maximum index key length=758
If 4K block size then maximum index key length=1578
If 8K block size then maximum index key length=3218
If 16K block size then maximum index key length=6498
How the maximum index key length is measured by?
Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)
The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.
So we can say that the maximum key length for an index will be less than half of
the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.
Solution of the Problem
The causes already indicates what might be the solutions. Solution may be,
1)Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.
2)If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.
3)Rebuild the index without online clause. That is
ALTER INDEX index_name REBUILD;
Because The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.
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 varchar2(200);
Table altered.
SQL> alter index tab3_I rebuild online;
alter index tab3_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded
Cause of the Problem
When creating a index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.
If 2K block size then maximum index key length=758
If 4K block size then maximum index key length=1578
If 8K block size then maximum index key length=3218
If 16K block size then maximum index key length=6498
How the maximum index key length is measured by?
Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)
The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.
So we can say that the maximum key length for an index will be less than half of
the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.
Solution of the Problem
The causes already indicates what might be the solutions. Solution may be,
1)Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.
2)If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.
3)Rebuild the index without online clause. That is
ALTER INDEX index_name REBUILD;
Because The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.
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 varchar2(200);
Table altered.
SQL> alter index tab3_I rebuild online;
alter index tab3_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded
Cause of the Problem
When creating a index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.
If 2K block size then maximum index key length=758
If 4K block size then maximum index key length=1578
If 8K block size then maximum index key length=3218
If 16K block size then maximum index key length=6498
How the maximum index key length is measured by?
Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)
The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.
So we can say that the maximum key length for an index will be less than half of
the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.
Solution of the Problem
The causes already indicates what might be the solutions. Solution may be,
1)Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.
2)If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.
3)Rebuild the index without online clause. That is
ALTER INDEX index_name REBUILD;
Because The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.
Subscribe to:
Posts (Atom)
