|
Error Code |
ORA-00001 |
|
Error Message |
ORA-00001: unique
constraint (constraint_name) violated |
|
Error Reason |
An INSERT or UPDATE
statement execution which violating the unique key constraint on the
Table |
|
Error Resolution |
1)Drop The unique
Constraint |
|
|
|
|
2)Allow duplicates by
droping the constraint |
|
|
Error Code |
ORA-00054 |
|
Error Message |
ORA-00054: resource busy
and acquire with NOWAIT specified |
|
Error Reason |
An insert or update
or select command against a LOCK Table |
|
Error Resolution |
1)Try to execute the
statment after sometime |
|
|
|
|
2)Execute the command
with a NOWAIT commands |
|
|
Error Code |
ORA-00257 |
|
Error Message |
ORA-00257: archiver is
stuck. CONNECT INTERNAL only, until freed |
|
Error Reason |
Error occurs at the
archival of the redo logs |
|
Error Resolution |
1)Check the storage space
availabilty |
|
|
|
|
2)Check the
initialization paramter ARCHIVE_LOG_DEST |
|
|
Error Code |
ORA-00600 |
|
Error Message |
ORA-00600 |
|
Error Reason |
Internal
Error generated by generic kernal code of the Oracle Software |
|
Error Resolution |
Should be reported to
oracle support |
|
Error Code |
ORA-12154 |
|
Error Message |
ORA-12154: TNS:could not
resolve service name |
|
Error Reason |
This error occurs due to
missing tnsnames.ora file or tnsname.ora file not configured properly . |
|
Error Resolution |
Reconfigure the
tnsname.ora |
|
Error Code |
ORA-03113 |
|
Error Description |
ORA-03113: end-of-file
on communication channel |
|
Error -Reason |
An unexpected end of file
occured During communication |
|
Error Resolution |
1)Check the network may
be the network went down |
|
|
|
|
2)check the server may be
the server down |
|
|
Error Code |
ORA-00936 |
|
Error Description |
ORA-00936: missing
expression |
|
Error -Reason |
missing sysntax when
executing SQL statments |
|
Error Resolution |
1) Check
your statement sytax properly |
|
Error Code |
ORA-01017 |
|
Error Description |
ORA-01017: invalid
username/password; logon denied |
|
Error -Reason |
Login activity uisng
worng database credentials |
|
Error Resolution |
1) Enter the login
credentials properly |
|
Error Code |
ORA-01555 |
|
Error Description |
ORA-01555: snapshot too
old (rollback segment too small) |
|
Error -Reason |
Insufficient Rollback
segments |
|
Error Resolution |
1) Make larger rollback
segments |
|
Error Code |
ORA-04031 |
|
Error Description |
ORA-04031: unable to
allocate num bytes of shared memory |
|
Error -Reason |
More usage of shared
memory than availabe |
|
Error Resolution |
1. Reduce the use of shared memory availabe. |
|
2. Increase the SHARED_POOL_SIZE paramter |
|
|
Error Code |
ORA-01031 |
|
Error Description |
ORA-01031: insufficient
privileges |
|
Error -Reason |
Due to the lack of the
privileges for the login user to thedatabase objects |
|
Error Resolution |
Contact the Oracle DBA
for accessing privilages |
|
Error Code |
ORA-01000 |
|
Error Description |
ORA-01000: maximum open
cursors exceeded |
|
Error -Reason |
Each user is restricted
in using the number of cursors at a time . You may get this error when your
cursor limit exeeds the set parameter |
|
Error Resolution |
shut down Oracle database
, increase the OPEN_CURSORS parameter in the initialization file, and restart
Oracle database . |
|
Error Code |
ORA-12560 |
|
Error Description |
ORA-12560: TNS:protocol
adapter error |
|
Error -Reason |
This is an error related
to the protocol adaptor |
|
Error Resolution |
Check the Protcol
specification |
|
Error Code |
ORA-27101 |
|
Error Description |
ORA-27101: shared
memory realm does not exist |
|
Error -Reason |
Unable to locate the
shared memory realm |
|
Error Resolution |
Make sure that ORACLE_SID
and ORACLE_HOME are correct. |
|
|
|
|
A tralling slash on the
ORACLE_HOME can cause error |
|
|
|
|
|
ORACLE_HOME=’/home/oracle/prod’
—- Correct |
|
|
|
|
|
ORACLE_HOME=’/home/oracle/prod/’
—-Wrong |
|
|
Error Code |
ORA-04030 |
|
Error Description |
ORA-04030: out of process
memory |
|
Error -Reason |
This is a common error
while running import . This error is related to RAM settings. If the server
having low RAM and memeory parameters are configured with a high
configuration causes this error |
|
Error Resolution |
The best solution is to
increase the RAM of the server if your server running with a low RAM other
option is to Increase pga_aggregate_target |
|
Error Code |
ORA-00020 |
|
Error Description |
ORA-00020: maximum number
of processes (%s) exceeded |
|
Error -Reason |
Maximum number of
processes are exceeded than the limit in oracle database |
|
Error Resolution |
Login as SYSDBA and kill
the unwanted sessions |
|
Error Code |
ORA-04030 |
|
Error Description |
ORA-04030: out of process
memory |
|
Error -Reason |
This is a common error
while running import . This error is related to RAM settings. If the server
having low RAM and memeory parameters are configured with a high
configuration causes this error |
|
Error Resolution |
The best solution is to
increase the RAM of the server if your server running with a low RAM other
option is to Increase pga_aggregate_target |
|
Error Code |
ORA-04030 |
|
Error Description |
ORA-04030: out of process
memory |
|
Error -Reason |
This is a common error
while running import . This error is related to RAM settings. If the server
having low RAM and memeory parameters are configured with a high
configuration causes this error |
|
Error Resolution |
The best solution is to
increase the RAM of the server if your server running with a low RAM other
option is to Increase pga_aggregate_target |
Friday, 21 February 2014
Most Common Oracle Errors with Solution
Migrating RAC with ASM to standalone database in filesystem using RMAN
Recently we got a requirement to migrate the RAC database to NON RAC Database. You may ask why from RAC-ASM to NON RAC-FILESYSTM :) Reason is we are hosting the database in AWS cloud Environment. AWS cloud does not provide the clustering infrastructure required to run RAC.
In this scenario, customer has given us the full RMAN back of RAC environment and shared the backup set to us. So here is the steps to restore for the single instance node.
1. Take the PFILE which will be available in the backup set and remove all RAC related parameters. That environment was of ASM which we are migrating it to File-system, so we need to add some additional parameter. Below are the format
<DB_NAME>.__db_cache_size=603979776
<DB_NAME>.__java_pool_size=50331648
<DB_NAME>.__large_pool_size=16777216
<DB_NAME>.__oracle_base='/home/'#ORACLE_BASE set from environment
<DB_NAME>.__pga_aggregate_target=603979776
<DB_NAME>.__sga_target=1157627904
<DB_NAME>.__shared_io_pool_size=0
<DB_NAME>.__shared_pool_size=469762048
<DB_NAME>.__streams_pool_size=0
*.audit_file_dest='/home/oracle/app/oracle/admin/<DB_NAME>/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='control1.ctl,control2.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/app/oracle/oradata/<DB_NAME>/'
*.db_file_name_convert='+DG_01/<DB_NAME>/datafile/','/home/oracle/app/o
racle/oradata/<DB_NAME>/'
*.db_create_online_log_dest_1=’/home/oracle/app/oracle/oradata/'
*.db_name='<DB_NAME>'
*.db_recovery_file_dest='/home/oracle/app/flash_recovery_area'
*.db_recovery_file_dest_size=4G
*.diagnostic_dest='/home/oracle/app/oracle/'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1761607680
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
<DB_NAME>.undo_tablespace='UNDOTBS1'
2. Create the password file
$ORACLE_HOME/bin/orapwd
file=$ORACLE_HOME/dbs/orapwd<SID>.orapassword=oracle entries=5
3. Start the database in mount state
$ sqlplus / as sysdba
Sql> startup nomount
3. Login in RMAN with no catalog
$ rman target / nocatalog
4. restore the control file which is available in backup set
$RMAN> restore controlfile from ‘<location of control file>’
5. Once the control file is restored, mount the database
RMAN> alter database mount;
6. Catalog backup set using rman
RMAN> catalog backuppiece ‘<Location of backupset>’;
7. Once the catalog is done run the below RMAN script to restore the data
run
{
SET NEWNAME FOR DATABASE TO
'<NEW_LOCATION_TO_WHERE_THE_DATAFILE_NEED_TO_RESTORE>/%d';
SET NEWNAME FOR tempfile 1 TO
'<NEW_LOCATION_TO_WHERE_THE_DATAFILE_NEED_TO_RESTORE>/%d';
restore database;
switch datafile all;
switch tempfile all;
}
Newname is for restoring the database to the file system. If we miss to mention that the database will be restored to ASM location and while opening the database it will throw error
8. Check the sequence and threads using below command
RMAN> list backup of archivelog all;
List of Archived Logs in backup set 9
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 56 214541 01-FEB-07 226238 01-FEB-07
1 57 226238 01-FEB-07 226240 01-FEB-07
1 58 226240 01-FEB-07 233107 12-FEB-07
2 1 186185 28-JAN-07 225714 01-FEB-07
2 2 225714 01-FEB-07 226037 01-FEB-07
2 3 226037 01-FEB-07 233110 12-FEB-07
Check the last archive sequence for all redo threads and select the archive sequence
having LEAST "Next SCN" among them. In our case sequence 58 of thread 1 has Next SCN of
233107 while sequence 3 of thread 2 has Next SCN of 233110. Since squence 58 of thread 1 has
least Next SCN we will recover upto this point. (If you are keen to have recovery run until some
specific time you can always give SET UNTIL TIME)
9. Restore and recover the database using the below command in RMAN prompt
run {
set until sequence 59 thread 1;
recover database;
}
*59 is the maximum sequence number and 1 is the minimum thread. The value will change on each database
10. Disable the block change tracking in database.
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
11. Open the database in reset log file using below script:
SQL> alter database open resetlog;
Check the status of Database
SQL> select status from v$instance;
STATUS
------------
OPEN
Please revert back if i miss any steps and any clarification required
In this scenario, customer has given us the full RMAN back of RAC environment and shared the backup set to us. So here is the steps to restore for the single instance node.
1. Take the PFILE which will be available in the backup set and remove all RAC related parameters. That environment was of ASM which we are migrating it to File-system, so we need to add some additional parameter. Below are the format
<DB_NAME>.__db_cache_size=603979776
<DB_NAME>.__java_pool_size=50331648
<DB_NAME>.__large_pool_size=16777216
<DB_NAME>.__oracle_base='/home/'#ORACLE_BASE set from environment
<DB_NAME>.__pga_aggregate_target=603979776
<DB_NAME>.__sga_target=1157627904
<DB_NAME>.__shared_io_pool_size=0
<DB_NAME>.__shared_pool_size=469762048
<DB_NAME>.__streams_pool_size=0
*.audit_file_dest='/home/oracle/app/oracle/admin/<DB_NAME>/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='control1.ctl,control2.ctl'
*.db_block_size=8192
*.db_create_file_dest='/home/oracle/app/oracle/oradata/<DB_NAME>/'
*.db_file_name_convert='+DG_01/<DB_NAME>/datafile/','/home/oracle/app/o
racle/oradata/<DB_NAME>/'
*.db_create_online_log_dest_1=’/home/oracle/app/oracle/oradata/'
*.db_name='<DB_NAME>'
*.db_recovery_file_dest='/home/oracle/app/flash_recovery_area'
*.db_recovery_file_dest_size=4G
*.diagnostic_dest='/home/oracle/app/oracle/'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1761607680
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
<DB_NAME>.undo_tablespace='UNDOTBS1'
2. Create the password file
$ORACLE_HOME/bin/orapwd
file=$ORACLE_HOME/dbs/orapwd<SID>.orapassword=oracle entries=5
3. Start the database in mount state
$ sqlplus / as sysdba
Sql> startup nomount
3. Login in RMAN with no catalog
$ rman target / nocatalog
4. restore the control file which is available in backup set
$RMAN> restore controlfile from ‘<location of control file>’
5. Once the control file is restored, mount the database
RMAN> alter database mount;
6. Catalog backup set using rman
RMAN> catalog backuppiece ‘<Location of backupset>’;
7. Once the catalog is done run the below RMAN script to restore the data
run
{
SET NEWNAME FOR DATABASE TO
'<NEW_LOCATION_TO_WHERE_THE_DATAFILE_NEED_TO_RESTORE>/%d';
SET NEWNAME FOR tempfile 1 TO
'<NEW_LOCATION_TO_WHERE_THE_DATAFILE_NEED_TO_RESTORE>/%d';
restore database;
switch datafile all;
switch tempfile all;
}
Newname is for restoring the database to the file system. If we miss to mention that the database will be restored to ASM location and while opening the database it will throw error
8. Check the sequence and threads using below command
RMAN> list backup of archivelog all;
List of Archived Logs in backup set 9
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 56 214541 01-FEB-07 226238 01-FEB-07
1 57 226238 01-FEB-07 226240 01-FEB-07
1 58 226240 01-FEB-07 233107 12-FEB-07
2 1 186185 28-JAN-07 225714 01-FEB-07
2 2 225714 01-FEB-07 226037 01-FEB-07
2 3 226037 01-FEB-07 233110 12-FEB-07
Check the last archive sequence for all redo threads and select the archive sequence
having LEAST "Next SCN" among them. In our case sequence 58 of thread 1 has Next SCN of
233107 while sequence 3 of thread 2 has Next SCN of 233110. Since squence 58 of thread 1 has
least Next SCN we will recover upto this point. (If you are keen to have recovery run until some
specific time you can always give SET UNTIL TIME)
9. Restore and recover the database using the below command in RMAN prompt
run {
set until sequence 59 thread 1;
recover database;
}
*59 is the maximum sequence number and 1 is the minimum thread. The value will change on each database
10. Disable the block change tracking in database.
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
11. Open the database in reset log file using below script:
SQL> alter database open resetlog;
Check the status of Database
SQL> select status from v$instance;
STATUS
------------
OPEN
Please revert back if i miss any steps and any clarification required
Subscribe to:
Posts (Atom)