ORA-00600: internal error code, arguments: [4194]

Hi guys Over long time I am writing this doc reg ora-600 please find it.

sql> startup
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Force terminated......

exit



When I check in the alert log it through the ora-600 error with arguments:[4194]
and I done with below action and succeed


sql>startup mount
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.


sql> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1

sql> alter system set undo_management = manual scope=spfile;

System altered.

Note: disabling the undo tablespace here, this'll let us go back to "rollback segments"
and we'll use the system RBS to get back on our feet


sql> shutdown
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

sql> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

[ora920@tkyte-pc-isdn ora920]$ !sql
sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 08:35:34 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

sql> connect / as sysdba;
Connected to an idle instance.
sql> startup mount
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.


sql> alter database datafile  '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'
  2  offline drop;

Database altered.

sql> alter database open;

Database altered.

sql> drop tablespace undotbs1;

Tablespace dropped.

sql> create UNDO tablespace undotbs1
  2  datafile '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf' size 25m
  3  autoextend on next 1m maxsize 1024m;

Tablespace created.

sql> alter system set undo_management = auto scope=spfile;

System altered.

Now, we've put the undo tablespace "back" by creating a new one.  Just bounce...

sql> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
sql> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
[ora920@tkyte-pc-isdn ora920]$ !sql
sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 08:40:51 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

sql> connect / as sysdba;
Connected to an idle instance.
sql> startup
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
sql> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1
sql>


and we are right back where we started from


Ora 600 with Undo regards,

Suresh R
oracle dba.

SQL*Plus COPY Command between two database

SQL*Plus COPY Command

Dear all,

 After long time I am writing this docs.

I'm doing the update command between two database using copy command which is available in sql* plus

and without creating database link , but using the tns names from connecting and updating .

Rule :

 update the (bangalore db table ) table using Chennai (database table) table. with below criteria

UPDATE DO_CR_STR (bangalore table) SET SC_CR_STR='REDEEM' WHERE ID_CR_STR IN(SELECT STR_NO FROM LS_STR_CRDT_RDM_STG (chennai table) );


Source database - Chennai ( tnsname - chennai )

Destination database - Bangalore ( tnsname - bangalore )

UPDATE DO_CR_STR SET SC_CR_STR='REDEEM' WHERE ID_CR_STR IN(SELECT STR_NO FROM LS_STR_CRDT_RDM_STG_temp );

Step 1 :

Connect the sqlplus by using

sqlplus /nolog

Step 2 :

Create the table in bangalore database from chennai database using following command,

sql> COPY FROM username/passwd@chennai -
>TO username/passwd@bangalore -
>create LS_STR_CRDT_RDM_STG_temp -
>USING SELECT * from LS_STR_CRDT_RDM_STG;

disc

Step 3 :

Connect the bangalore database from your server using tns names and update the table by using created table from chennai server.

sql> conn username/passwd@bangalore;



sql> UPDATE DO_CR_STR SET SC_CR_STR='REDEEM' WHERE ID_CR_STR IN(SELECT STR_NO FROM LS_STR_CRDT_RDM_STG_temp );

commit;

Step 4 :

once the update process completed do the drop command .

sql> drop table storebo.LS_STR_CRDT_RDM_STG_temp;

disc.

This above scripts only applicable for , if your updating many database from one database as on daily basis.

Otherwise go for DB link creation .

Best of regards,

Suresh R

EXP-00056 and ORA-00932 in oracle 10g while export

The Errors are :

EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR

After the investigation I came to know that the problem lies with accessing the XDB object views. This is because XDB object views were created with dummy SQL statements.

SQL> SELECT schema_val FROM sys.ku$_xmlschema_view;
SELECT schema_val FROM sys.ku$_xmlschema_view
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR

SQL> SELECT text FROM dba_views
  2  WHERE view_name = 'KU$_XMLSCHEMA_VIEW';

TEXT
-------------------------------------------------------------------------------
select '1','0', 0, NULL, NULL, NULL, 0, NULL, NULL
    from dual where 1=0

Here is how the above fake XDB object views were created. The script “catproc.sql” has been executed while creating the database. The script “catproc.sql” calls “catmeta.sql” which contains the fake object view names which are required for XDB objects. Interestingly, the real views can be created by executing the script “catmex.sql”. While enabling the XMLDB, the script “catmex.sql” will be invoked by “catxdbv.sql” and that is invoked by “catqm.sql”

To resolve the issue, connect as user SYS and execute “catmetx.sql”.

If the database “NLS_LENGTH_SEMANTICS” parameter is set to “CHAR”, while executing the “catmetx.sql” we may get ORA-22973 error message. This is because the script is attempting to compile INVALID object SYS.KU$_XMLSCHEMA_VIEW and internal name size for an OID (Object Identifier) is not sufficient to hold the names of several XDB types and sub-types.

SQL> @?/rdbms/admin/catmetx.sql
create or replace force view sys.ku$_xmlschema_view of sys.ku$_xmlschema_t
*
ERROR at line 1:
ORA-22973: size of object identifier exceeds maximum size allowed

Hence set database parameter NLS_LENGTH_SEMANTICS=BYTE before executing the “catmetx.sql”

SQL> ALTER SYSTEM SET nls_length_semantics=byte;
SQL> @?/rdbms/admin/catmetx.sql
SQL> @?/rdbms/admin/utlrp.sql

Now I am able to execute the SELECT statement on object view “SYS.KU$_XMLSCHEMA_VIEW” without any errors and also I am able to export the full database dump.

Refer metalink notes: Note – 339938.1, Note – 393156.1 andBug 2743295

ORA-01157 undo tablespace corrupted

ORA-01157 undo tablespace corrupted


In rare cases (usually DBA error) the Oracle UNDO tablespace can become corrupted.  This manifests with this error:

ORA-00376: file xx cannot be read at this time

 [ora920@tkyte-pc-isdn ora920]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 08:33:29 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

idle> connect / as sysdba
Connected to an idle instance.
idle> startup
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'


idle> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1

idle> alter system set undo_management = manual scope=spfile;

System altered.

Note: disabling the undo tablespace here, this'll let us go back to "rollback segments"
and we'll use the system RBS to get back on our feet


idle> shutdown
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

idle> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

[ora920@tkyte-pc-isdn ora920]$ !sql
sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 08:35:34 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

idle> connect / as sysdba;
Connected to an idle instance.
idle> startup
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'

Note: we still have to get rid of that thing..

idle> alter database datafile  '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'
  2  offline drop;

Database altered.

idle> alter database open;

Database altered.

idle> drop tablespace undotbs1;

Tablespace dropped.

idle> create UNDO tablespace undotbs1
  2  datafile '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf' size 25m
  3  autoextend on next 1m maxsize 1024m;

Tablespace created.

idle> alter system set undo_management = auto scope=spfile;

System altered.

Now, we've put the undo tablespace "back" by creating a new one.  Just bounce...

idle> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
idle> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
[ora920@tkyte-pc-isdn ora920]$ !sql
sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 08:40:51 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

idle> connect / as sysdba;
Connected to an idle instance.
idle> startup
ORACLE instance started.

Total System Global Area  143725064 bytes
Fixed Size                   451080 bytes
Variable Size             109051904 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
idle> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1
idle>


and we are right back where we started from


Undo regards,

Suresh R
oracle dba.

Rename oracle schema or Alter schema in oracle

Rename the schema name in oracle by using below one step;

Step 1;

  Make sure no connection using the username which is going to rename.

If its using ,bounce the DB and do the below steps.

Step 2 :

  

   In oracle one of the table is having all the user information , so we can update the username through that table avoiding alter command.

select * from sys.user$;

Now I am going to change the username from nath to nathan as like below;

UPDATE SYS.USER$ SET NAME='NATHAN' where name='NATH';

AND ALTER THE USER LIKE BELOW;

ALTER USER NATHAN IDENTIFIED BY NATHAN;

GRANT DBA TO NATHAN;

Good luck

Suresh R

oracle dba.

Difference Between Archive, Redo and Undo Logs.

Difference Between Archive, Redo and Undo Logs.

Difference between Redo and Undo logs

Redo information, in a nutshell, is stored ONLY for recovery purposes (whether instance recovery or database recovery) whereas UNDO has extended functionality and usage. Redo stores undone changes as well. To be prcise, redo = do again (roll forward) and undo = erase the changes (roll back). I'll try and explain couple of scenarios.

Redo log stores all information about changes to database blocks. You insert 1000 rows--Oracle will generate redo and store it in log buffer (which will be later written into redo logs and then archived logs for recovery purposes). And the changes will be stored in UNDO (in case you want to rollback the insert later). Not only that. You now update 1000 rows and commit it. Oracle does not "have to necessarily" mark those blocks as committed. Instead it updates the data block(s) with a reference to the undo segment information. In fact Oracle only marks that UNDO segment slot as committed at this point and not necessarily the data block. Now a different session throws a select query on the table and Oracle duly visits that data block but the data block header will say now that there is an uncommitted change in the block(s). Here comes UNDO in play. Oracle will now go to the UNDO segment header and check the slot where it will say it's committed--so it will go back to the data block and do a cleanout of dirty blocks (delayed clean out). Another scenario is, you throw a query on a million record table and while it's visiting the required blocks........another session comes and updates some of the blocks that you need. Now Oracle will have to look at the datablock header and then go to UNDO and get that information. So UNDO has much more to do than the redo.

Now, coming to your second question, I think it will be much easier to explain now after the first question. Database files will have to store only the committed transactions, in an ideal situation. Otherwise, you cannot recover your database in case of disaster. On the other hand, REDO doesn't care whether it's a committed transaction or an uncommitted transaction. It has to store all changes to the blocks. Now if your database crashes, all the transactions will be applied to your database files using archived logs (or redo logs based on the DR situation) and then uncommitted transactions will be undone (rolled back).





Rememeber database files are online and they are written to by DBWR process. So we have database files, redo log files and archive log files.
In order to avoid scanning the entire log when doing a recovery, database performs checkpoints that summarize the status of the database. This checkpoint operation provides a shortcut to recovery. At checkpoint time, the database knows that all dirty pages have been written to disk (i.e. database files). At time of recovery, the log (that includes both completed and incomplete transactions) is used to bring the database back to a consistent state. The system locates the time of last checkpoint and finds that position in the log file. It then rolls forward all completed (read commited) transactions that occurred after the last checkpoint, and rolls back all transactions that were not committed, but began before the last checkpoint. So that is where online redo log files are used.

Now imagine you had to back up your 100+GB database every 10 minutes. That would be a waste of space! So you take a backup of your database at time t and the archiver process backups redo logs periodically to archive logs so that redo log files can be overwritten and RMAN will use the last backup plus the archive log files to recover your database to point in time.

Now I mentioned checkpoint process. The Checkpoint Process regularly initiates a checkpoint, which uses DBWR to write all dirty blocks back to the datafiles, thus synchronizing the database. Imagine a single checkpoint is running and all redo log files have been used up. At this point, Oracle will wait until all the previously queued dirty blocks have been written from the buffer to disc (database files) before any of the redo log files can be considered redundant and available for re-use (i.e. can be overwritten). This will result in the following message in the alert.log:

Thread 1 advanced to log sequence 17973
  Current log# 3 seq# 17973 mem# 0: /oracle/data/HAM1/log3aHAM1.dbf
  Current log# 3 seq# 17973 mem# 1: /oracle/data/HAM1/log3bHAM1.dbf
Thread 1 cannot allocate new log, sequence 17974
Checkpoint not complete
  Current log# 3 seq# 17973 mem# 0: /oracle/data/HAM1/log3aHAM1.dbf
  Current log# 3 seq# 17973 mem# 1: /oracle/data/HAM1/log3bHAM1.dbf
Thread 1 advanced to log sequence 17974

I am sure you have done the following:

alter database mount;
 
Database altered.

When you mount a database, Oracle associates the started instance with the database. Oracle control files are opened and read. However no verification such as rollback/recovery is carried out

alter database open;
  
Database altered.

An open command opens data files, redo logs, it performs database consistency and auto recovery. At this stage the database is now ready to be used by all valid users.

A checkpoint process is an integral part of database logging and recovery. The operation itself is known as "checkpoint." A checkpoint writes both the log records , plus writing dirty (modified) data buffers to disk. It is essential that a checkpoint forces the flushing of log records from redo buffers to redo log filese. This makes sense in that if we flushed the modified data buffers to disk and do not flush the log records to disk and the system crashed, we would have modified records on disk with no log information associated with them. We need to flush the log buffers before writing modified data buffers so that the database integrity from a transactional point of view is maintained. BTW, a buffer is a database block, which happens to be in memory. A checkpoint causes the latest SCN to be written (updated) into the datafile header and control files.

The checkpoint frequency is affected by different parameters and equally importantly the size of redo logs. A change of redo log file will force a checkpoint in the database. Checkpoint is a resource intensive activity and it needs to be kept to a reasonable number to ensure an optimum recovery time and little or no impact on the database activity. You should use big enough redo logs - to reduce normal checkpoint frequency.

The following events trigger a checkpoint.
) Redo log switch (alter system switch logfile).
2) LOG_CHECKPOINT_TIMEOUT has expired
3) LOG_CHECKPOINT_INTERVAL has been reached
4) Manaul checkpoint force (alter system checkpoint)


No,the checkpoint process doesn't, It's job checkpointing process by updating the file headers of the data files.The Database Block Writer (DBWn) responsible for writing dirty blocks to disk.Basically chekpointing is the process of two sub process.

1) Write Dirty buffer from database buffer cache to datafile which is done by DBWR.
2) CKPT update alls datafile header with the SCN to memorize that i have written alls data buffer block from database buffer cache to that time when it occurs by SCN.

Regards,

Suresh R
oracle dba .

Kill oracle session process in linux and windows platforms

Kill oracle session process in linux and windows platforms :

In various critical situations Oracle DBA has to decide to kill existing session or kill the background process of Oracle database. At this moment remote Oracle DBA should need to terminate session/process called as termination of session of any instance.

If you want kill or terminate process using only operating system command only.How can you kill process in Unix?

In linux and unix :

We can check running processes in Unix and Linux using "ps" command. We are able to get process id from said command.

$ps -ef|grep ora_|grep -v grep 

Now use "kill" command with "-9" syntax to kill process id as given below example.kill command with -9 for ensure not blocking command and surity if execution of "kill" command.

$kill -9 1451

In Windows oracle kill cmd :

For using ORAKILL utility we should need process id from sqlplus. ORAKILL utility is shipped with Oracle software itself. When we install Oracle software same time ORAKILL also is being installed in ORACLE_HOME\bin folder. It can kill process same as "kill -9" command in Unix/Linux. But one drowback of this utility that is we need process id using sqlplus and from v$process,v$session data dictionary views as follows.

SQL> select a.username, b.spid from v$session a, v$process b where a.paddr = b.addr and a.username ='SCOTT';

USERNAME SPID.
-------- ----
SCOTT 1456 

Now we can kill server process of 1456 of scott user using ORAKILL utility from command line of Windows as follows.

C:\> orakill instance_name spid

Example
C:\> orakill orcl 1456

We will get following message in our command prompt of windows.
"Kill of thread id 1456 in instance ORCL successfully signalled[sic]."

In Windows task kill cmd :

But if sometime SQL*Plus doesn't working like database stuck or instance hang situation. We need to terminate Oracle instance. But we are unable to execute command using SQL*Plus "shutdown abort" or our shutdown abort command also stuck in Windows. Then how can we terminate instance? Yes, we can restart instance service from service menu. But if we want to use only command line then how to terminate instance or kill instance or background process.

We can use TASKKILL command for terminating instance. Using tasklist command we can get thread id of Oracle.exe and using TASKKILL command we can terminate or kill instance in command line as follows.

C:\>tasklist 
Image Name PID Session Name Session# Mem Usage
=============== ======== ======== ============
System Idle Process 0 Console 0 16 K
oracle.exe 1456 Console 0 282,100 K
smss.exe 636 Console 0 420 K 

C:\>taskkill /pid 1456 /T 

Best of luck.

Thanks & Regards,

Suresh R

oracle dba.