It is very simple to convert a LONG column to CLOB.
Create table test( col1 long);
Alter table test modify col1 clob;
That's it.
Tuesday, March 29, 2011
Tuesday, March 1, 2011
ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value
SYS_CONNECT_BY_PATH is used in hierarchial queries. It returns the path of column value from a root node
example: SYS_CONNECT_BY_PATH(column_name, '/') where '/' is the seperator.
If the column_name contains the separator character, then you get the above error
"ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value"
In my example below, foldername had values like "Tree for Case/Document". Since the foldername has "/" which matches your seperator, you get the error.
select f.foldername, sys_connect_by_path(f.foldername,'/') dir
from folder f
connect by parentfolderkey = prior folderkey
start with casekey = 7457950
To remove the error, make sure that your seperator is unique, The error disappeared when I replaced the seperator "/" with "//"
select f.foldername, sys_connect_by_path(f.foldername,'//') dir
from folder f
connect by parentfolderkey = prior folderkey
start with casekey = 7457950
example: SYS_CONNECT_BY_PATH(column_name, '/') where '/' is the seperator.
If the column_name contains the separator character, then you get the above error
"ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have seperator as part of column value"
In my example below, foldername had values like "Tree for Case/Document". Since the foldername has "/" which matches your seperator, you get the error.
select f.foldername, sys_connect_by_path(f.foldername,'/') dir
from folder f
connect by parentfolderkey = prior folderkey
start with casekey = 7457950
To remove the error, make sure that your seperator is unique, The error disappeared when I replaced the seperator "/" with "//"
select f.foldername, sys_connect_by_path(f.foldername,'//') dir
from folder f
connect by parentfolderkey = prior folderkey
start with casekey = 7457950
Monday, February 14, 2011
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr]
I have an Oracle 11g R2 database on a Vista box. I got this error twice, and in both occasions, it was due to Vista crashing (ran out of battery, blue screen)
Here are the steps I performed to resolve this error
Launch SQL Plus from Oracle\bin folder ( not from Client folder)
SQL> conn sys/sys as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
SQL> alter database recover;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[136], [65730], [65738], [], [], [], [], [], [], []
SQL> shutdown immediate;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
SQL> alter database recover until cancel using backup controlfile;
alter database recover until cancel using backup controlfile
*
ERROR at line 1:
ORA-00279: change 3997604 generated at 02/11/2011 21:08:59 needed for thread 1
ORA-00289: suggestion :
C:\APP\RMANNI\RECOVERY_AREA\ORCLDB11G\ARCHIVELOG\2011_02_14\O1_MF_1_136_%U_.ARC
ORA-00280: change 3997604 for thread 1 is in sequence #136
Since my database is not running in archive log mode, I couldn't find the file specified in the above error message. I proceeded with the following
SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00308: cannot open archived log
'C:\APP\RMANNI\RECOVERY_AREA\ORCLDB11G\ARCHIVELOG\2011_02_14\O1_MF_1_136_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL> alter database recover cancel;
Database altered.
If the above step errored out, then proceed with the lines shown below inbetween the dotted lines, if not
SQL> alter database open resetlogs;
Database altered.
SQL> Your database is good to go, You should now be able to connect
-----------If Alter database recover cancel Failed ------------------
SQL> alter database recover cancel;
alter database recover cancel
*
ERROR at line 1:
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\APP\RMANNI\ORADATA\ORCLDB\SYSTEM01.DBF'
SQL> recover database using backup controlfile until cancel;
ORA-00275: media recovery has already been started
SQL> shutdown immediate;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Now, when you try to recover, Oracle will provide a suggestion,provide the path for your REDO log files. One of the log files will fix your database. I started with Redo03, but Redo01 fixed my database. You probably don't need to shutdown and restart the database for each Redo log flies.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2448238 generated at 11/16/2010 22:05:53 needed for thread 1
ORA-00289: suggestion :
C:\APP\RMANNI\FLASH_RECOVERY_AREA\ORCLDB\ARCHIVELOG\2011_02_14\O1_MF_1_67_%U_.ARC
ORA-00280: change 2448238 for thread 1 is in sequence #67
Specify log: {=suggested | filename | AUTO | CANCEL}
C:\app\rmanni\oradata\ORCLDB\REDO03.LOG
ORA-00310: archived log contains sequence 66; sequence 67 required
ORA-00334: archived log: 'C:\APP\RMANNI\ORADATA\ORCLDB\REDO03.LOG'
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\APP\RMANNI\ORADATA\ORCLDB\SYSTEM01.DBF'
SQL> shutdown immediate;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2448238 generated at 11/16/2010 22:05:53 needed for thread 1
ORA-00289: suggestion :
C:\APP\RMANNI\FLASH_RECOVERY_AREA\ORCLDB\ARCHIVELOG\2011_02_14\O1_MF_1_67_%U_.ARC
ORA-00280: change 2448238 for thread 1 is in sequence #67
Specify log: {=suggested | filename | AUTO | CANCEL}
C:\app\rmanni\oradata\ORCLDB\REDO02.LOG
ORA-00310: archived log contains sequence 65; sequence 67 required
ORA-00334: archived log: 'C:\APP\RMANNI\ORADATA\ORCLDB\REDO02.LOG'
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\APP\RMANNI\ORADATA\ORCLDB\SYSTEM01.DBF'
SQL> Shutdown immediate;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2448238 generated at 11/16/2010 22:05:53 needed for thread 1
ORA-00289: suggestion :
C:\APP\RMANNI\FLASH_RECOVERY_AREA\ORCLDB\ARCHIVELOG\2011_02_14\O1_MF_1_67_%U_.ARC
ORA-00280: change 2448238 for thread 1 is in sequence #67
Specify log: {=suggested | filename | AUTO | CANCEL}
C:\app\rmanni\oradata\ORCLDB\REDO01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
-----------If Alter database recover cancel Failed ------------------
Here are the steps I performed to resolve this error
Launch SQL Plus from Oracle\bin folder ( not from Client folder)
SQL> conn sys/sys as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
SQL> alter database recover;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[136], [65730], [65738], [], [], [], [], [], [], []
SQL> shutdown immediate;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
SQL> alter database recover until cancel using backup controlfile;
alter database recover until cancel using backup controlfile
*
ERROR at line 1:
ORA-00279: change 3997604 generated at 02/11/2011 21:08:59 needed for thread 1
ORA-00289: suggestion :
C:\APP\RMANNI\RECOVERY_AREA\ORCLDB11G\ARCHIVELOG\2011_02_14\O1_MF_1_136_%U_.ARC
ORA-00280: change 3997604 for thread 1 is in sequence #136
Since my database is not running in archive log mode, I couldn't find the file specified in the above error message. I proceeded with the following
SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00308: cannot open archived log
'C:\APP\RMANNI\RECOVERY_AREA\ORCLDB11G\ARCHIVELOG\2011_02_14\O1_MF_1_136_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL> alter database recover cancel;
Database altered.
If the above step errored out, then proceed with the lines shown below inbetween the dotted lines, if not
SQL> alter database open resetlogs;
Database altered.
SQL> Your database is good to go, You should now be able to connect
-----------If Alter database recover cancel Failed ------------------
SQL> alter database recover cancel;
alter database recover cancel
*
ERROR at line 1:
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\APP\RMANNI\ORADATA\ORCLDB\SYSTEM01.DBF'
SQL> recover database using backup controlfile until cancel;
ORA-00275: media recovery has already been started
SQL> shutdown immediate;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Now, when you try to recover, Oracle will provide a suggestion,provide the path for your REDO log files. One of the log files will fix your database. I started with Redo03, but Redo01 fixed my database. You probably don't need to shutdown and restart the database for each Redo log flies.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2448238 generated at 11/16/2010 22:05:53 needed for thread 1
ORA-00289: suggestion :
C:\APP\RMANNI\FLASH_RECOVERY_AREA\ORCLDB\ARCHIVELOG\2011_02_14\O1_MF_1_67_%U_.ARC
ORA-00280: change 2448238 for thread 1 is in sequence #67
Specify log: {
C:\app\rmanni\oradata\ORCLDB\REDO03.LOG
ORA-00310: archived log contains sequence 66; sequence 67 required
ORA-00334: archived log: 'C:\APP\RMANNI\ORADATA\ORCLDB\REDO03.LOG'
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\APP\RMANNI\ORADATA\ORCLDB\SYSTEM01.DBF'
SQL> shutdown immediate;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2448238 generated at 11/16/2010 22:05:53 needed for thread 1
ORA-00289: suggestion :
C:\APP\RMANNI\FLASH_RECOVERY_AREA\ORCLDB\ARCHIVELOG\2011_02_14\O1_MF_1_67_%U_.ARC
ORA-00280: change 2448238 for thread 1 is in sequence #67
Specify log: {
C:\app\rmanni\oradata\ORCLDB\REDO02.LOG
ORA-00310: archived log contains sequence 65; sequence 67 required
ORA-00334: archived log: 'C:\APP\RMANNI\ORADATA\ORCLDB\REDO02.LOG'
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\APP\RMANNI\ORADATA\ORCLDB\SYSTEM01.DBF'
SQL> Shutdown immediate;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2448238 generated at 11/16/2010 22:05:53 needed for thread 1
ORA-00289: suggestion :
C:\APP\RMANNI\FLASH_RECOVERY_AREA\ORCLDB\ARCHIVELOG\2011_02_14\O1_MF_1_67_%U_.ARC
ORA-00280: change 2448238 for thread 1 is in sequence #67
Specify log: {
C:\app\rmanni\oradata\ORCLDB\REDO01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
-----------If Alter database recover cancel Failed ------------------
Wednesday, December 22, 2010
Oracle: Disable Archive log mode
Determine if the database is setup in archive log mode
SQL> Select log_mode from v$database
SQL> archive log list;
Archive log list gives a little bit detailed information about your archive log setup. By default, when you enable archive log, all the archive log files are written into flash_recovery_area.Below command will show you the OS path
sql> show parameter recovery_file_dest
To Disable Archive log
SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog
SQL> alter database open
SQL> archive log list
Your database is now running in NO archive log mode
Oracle: Enable Archive log mode
Launch SQLPLUS and login as SYS
Determine if the database is setup in archive log mode
SQL> Select log_mode from v$database
SQL> archive log list;
Archive log list gives a little bit detailed information about your archive log setup. By default, when you enable archive log, all the archive log files are written into flash_recovery_area.Below command will show you the OS path
SQL> show parameter recovery_file_dest
To Enable Archive log
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog
SQL> alter database open
SQL> archive log list
Your database is now running in archive log mode
Determine if the database is setup in archive log mode
SQL> Select log_mode from v$database
SQL> archive log list;
Archive log list gives a little bit detailed information about your archive log setup. By default, when you enable archive log, all the archive log files are written into flash_recovery_area.Below command will show you the OS path
SQL> show parameter recovery_file_dest
To Enable Archive log
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog
SQL> alter database open
SQL> archive log list
Your database is now running in archive log mode
Subscribe to:
Posts (Atom)