31.1.18
What is Deprecation of Non-CDB Architecture means
Please check the below
link for point (8.1.1 : Deprecation
of Non-CDB Architecture).
https://docs.oracle.com/database/121/UPGRD/deprecated.htm#BABDBCJI
"Deprecation"
means "at some stage in future, Oracle *might* stop doing enhancements on
this features, and at some stage after that, Oracle *might* no longer support
it".
In 12.1.0.1.0 non-CDB
(old architecture) is not deprecated whereas from 12.1.0.2.0 it got deprecated.
Even if you have single database, it is better to have it in multitenant
architecture as 1CDB and 1PDB.
As per Oracle document:- https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/deprecated-features-oracle-database-12c-r2.html#GUID-5D181F03-F74D-4888-B7B2-7176CF6FA8F8
Deprecation of Non-CDB Architecture
The non-CDB
architecture was deprecated in Oracle Database 12c. It can be desupported and
unavailable in a release after Oracle Database 19c .
Oracle recommends use
of the CDB architecture.
Oracle Database 19c ?
Release 12.2: New
releases will be annual and the version will be the last two digits of the
release year. The release originally planned as 12.2.0.2 will now be release
18c, and the release originally planned as 12.2.0.3 will be release 19c.
Releases 18c and 19c will be treated as under the umbrella of 12.2 for Lifetime
Support purposes. The current plan is for Oracle Database 19c to be the last
release for 12.2. This may change in the future to Oracle 20 as the last
release for 12.2
29.12.17
COUNT STOPKEY ROWNUM optimization
No COUNT STOPKEY in explain plan:- ROWNUM optimization
Product:- Oracle Server (RDBMS)
Range of versions believed to be affected:- 12.1
confirmed affected:- 1. 11.1.0.7
2. 10.2.0.4
Platforms affected:- all
So I was doing explain plan for below SQL on database version 11.2.0.1.0:-
select * from test where rownum = 0;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1829668517
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3219K| 733M| 35580 (2)| 00:07:07 |
| 1 | COUNT | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| TEST | 3219K| 733M| 35580 (2)| 00:07:07 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
129363 consistent gets
129358 physical reads
0 redo size
6210 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
On Database version 11.2.0.4.0 it was:-
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2416982823
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 240 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| TEST | 1 | 240 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
6210 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Keep sharing :)
So I was doing explain plan for below SQL on database version 11.2.0.1.0:-
select * from test where rownum = 0;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1829668517
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3219K| 733M| 35580 (2)| 00:07:07 |
| 1 | COUNT | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| TEST | 3219K| 733M| 35580 (2)| 00:07:07 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
129363 consistent gets
129358 physical reads
0 redo size
6210 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
On Database version 11.2.0.4.0 it was:-
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2416982823
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 240 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| TEST | 1 | 240 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
6210 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
After studying on oracle support I found this as a bug on 11.2.0.1.0 explained under Oracle
Please check your version for the bug because you can easily see the difference and performance matrix between both explain plans.
Beware! This use of rownum< can cause performance problems. Using rownum may change the all_rows optimizer mode for a query to first_rows, causing unexpected sub-optimal execution plans. One solution is to always include an all_rows hint when using rownum to perform a top-n query.
Keep sharing :)
21.12.17
SQL*Loader
SQL*Loader
What is this?
SQL*Loader loads data to an oracle database.With the help of SQL*Loader you can load data from external files to Oracle database. With the powerful data parsing engine that puts little limitation on the format of the data in the datafile.
Below key points taken from Oracle documention:-
1. Load data across a network. This means that you can run the SQL*Loader client on a different system from the one that is running the SQL*Loader server.
2. Load data from multiple datafiles during the same load session.
3. Load data into multiple tables during the same load session.
4. Specify the character set of the data.
5. Selectively load data (you can load records based on the records' values).
6. Manipulate the data before loading it, using SQL functions.
7. Generate unique sequential key values in specified columns.
8. Use the operating system's file system to access the datafiles.
9. Load data from disk, tape, or named pipe.
10. Generate sophisticated error reports, which greatly aid troubleshooting.
11. Load arbitrarily complex object-relational data.
12. Use secondary datafiles for loading LOBs and collections.
Example to load data in fixed format:-
load data
infile 'test.dat' "fix 12"
into table test
fields terminated by ',' optionally enclosed by '"'
(col_1, col_2)
example.dat:
007, cd, 0008,fghi,
00009,lmn,
1, "pqrs",
0010,uvwx,
9.11.17
total size of oracle database
How to check the total size of oracle database
We know oracle database consists of data files, redo log files, control files, temporary files and temporary files.
The size of the database actually means the total size of all these files.
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
I found this on a blog and very use full. It will show you the total size and the used size. Total size includes size of all files.
output will be like :-
Database Size Used space Free space
-------------------- -------------------- --------------------
78 GB 59 GB 19 GB
Keep sharing :)
7.11.17
ORA-65016: FILE_NAME_CONVERT must be specified
ORA-65016: FILE_NAME_CONVERT must be specified
I just started working on 12C version of oracle database.It's new to me and facing many problems.
Error code: ORA-65016: FILE_NAME_CONVERT must be specified
Description:"ORA-65016: FILE_NAME_CONVERT must be specified" normally occurs when you create a PDB.I will explain later what is a PDB.
Cause and solution :
ORA-65016: FILE_NAME_CONVERT must be specified caused when Data files, and possibly other files, needed to be copied as a part of creating a pluggable database.Enable OMF or define PDB_FILE_NAME_CONVERT system parameter before issuing CREATE PLUGGABLE DATABASE statement, or specify FILE_NAME_CONVERT clause as a part of the statement and make sure the path you are giving to convert the file exists.
I think if you are creating the PDB's using GUI then you will not face this error "ORA-65016: FILE_NAME_CONVERT must be specified". If you creating ODB using script and you have gave a wrong path then may you face "ORA-65016: FILE_NAME_CONVERT must be specified".
Syntax to create PDB:-
create pluggable database TEST admin user TEST identified by TEST file_name_convert = ('lcoation/', '/lcoation/');
Please write in comment box if you think there is better explanation on this error "ORA-65016: FILE_NAME_CONVERT must be specified".
I just started working on 12C version of oracle database.It's new to me and facing many problems.
Error code: ORA-65016: FILE_NAME_CONVERT must be specified
Description:"ORA-65016: FILE_NAME_CONVERT must be specified" normally occurs when you create a PDB.I will explain later what is a PDB.
Cause and solution :
ORA-65016: FILE_NAME_CONVERT must be specified caused when Data files, and possibly other files, needed to be copied as a part of creating a pluggable database.Enable OMF or define PDB_FILE_NAME_CONVERT system parameter before issuing CREATE PLUGGABLE DATABASE statement, or specify FILE_NAME_CONVERT clause as a part of the statement and make sure the path you are giving to convert the file exists.
I think if you are creating the PDB's using GUI then you will not face this error "ORA-65016: FILE_NAME_CONVERT must be specified". If you creating ODB using script and you have gave a wrong path then may you face "ORA-65016: FILE_NAME_CONVERT must be specified".
Syntax to create PDB:-
create pluggable database TEST admin user TEST identified by TEST file_name_convert = ('lcoation/', '/lcoation/');
Please write in comment box if you think there is better explanation on this error "ORA-65016: FILE_NAME_CONVERT must be specified".
25.9.17
ORA-01578: ORACLE data block corrupted (file # XX, block # XXXXX)
ORA-01578: ORACLE data block corrupted (file # XX, block # XXXXX)
As this error (ORA-01578: ORACLE data block corrupted) message shows that you have a corrupt block. So for data corruption you need to check what is going wrong.Check alert logs for more detail.
You can also use below sql:-
select * from v$database_block_corruption;
When i run the SQL. I found that one file # in my case 14 and some block 15353 is corrupted. As we know Corruption can occur in a table or index. So i have checked for this. In my case it was an index. So for a solution i just drop the index and created again and rebuild it. Just remember you cannot rebuild the index if it is having error ORA-01578: ORACLE data block corrupted.
So drop and create index works for me and now i can rebuild. Dropping a index will not harm you much because it's already on corrupt block.
Also i think when we create index again then it will take a different block. I am not sure about this because i have checked with sql after dropping and creating the index:-
select relative_fno, owner,segment_name,segment_type from dba_extents where file_id = 14 and 15353 between block_id and block_id + blocks - 1;
And found the same file and block information. It is strange as when i run the SQL : -select * from v$database_block_corruption;
It also shows me the same information that showed me that i still have the corroupt block for the same index and same file. But the thing is my DB is working now. For now It's working fine.
Please if someone has faced it before let me know in comment section. How to get rid of the error ORA-01578: ORACLE data block corrupted.
For a table label corruption you can use RMAN. I am just putting the link below.
click here
Hope this will save your day.
Can anyone can tell, what are the reasons for block corruption?
“ORA-01578: ORACLE data block corrupted” This is not a common error message means we don’t receive/see this very often. Data block corruption is a serious issue, and it is crucial to address it promptly. Always perform regular backups and ensure the integrity of your database to minimize the risk of data corruption.
"ORA-01578: ORACLE data block corrupted" on INDEX
If it occurs on INDEX that means you are lucky :) to fix this simply dropping/recreating the index will solve the issue.
Let's say it's not INDEX, Which is scary but then you have mainly two options
- Restore the backup to avoid losing data.
- You don't have a backup or the restore is not working or you just want to extract as much data as possible.
As mentioned "ORA-01578: ORACLE data block corrupted" is not common but very serious issue which mostly relates to your storage as well. Here are the possible 8 solutions for "ORA-01578: ORACLE data block corrupted"
- Restore from Backup
- Use Data Recovery Advisor
- Run DBVERIFY Utility
- Use RMAN Utility
- Check Disk and Storage
- Check for Software Bugs
- Perform Block-Level Recovery
- Engage Oracle Support
Restore from Backup
If you have a valid and recent backup of the database, you can restore the corrupted data block from the backup. This requires performing a point-in-time recovery or a complete database restore.
Use Data Recovery Advisor
Oracle provides the Data Recovery Advisor (DRA) tool to diagnose and repair data block corruption issues. You can use the DRA to analyze the corruption and generate repair recommendations. Follow the instructions provided by the DRA to repair the corrupted data block.
Run DBVERIFY Utility
The DBVERIFY utility is a built-in Oracle tool used to check the logical and physical integrity of data blocks. Run DBVERIFY against the affected data files to identify the corrupt blocks. If possible, restore the corrupted blocks from a backup or recreate them using the RECOVER command.
Use RMAN Utility
If you are using Oracle Recovery Manager (RMAN), you can use its block media recovery feature to repair the corrupted data blocks. RMAN can restore the corrupted blocks from a backup or perform block recovery using online redo logs.
Check Disk and Storage
Verify that the disk or storage system where the corrupted data block resides is functioning correctly. Check for any hardware failures, disk errors, or storage issues that might have caused the corruption. Correct any underlying problems before attempting to repair the data block.
Check for Software Bugs
Consult the Oracle Support website, bug database, or community forums to check if the corruption issue is a known bug. There might be specific patches or workarounds available to address the problem.
Perform Block-Level Recovery
In some cases, it might be necessary to perform a block-level recovery. This involves using RMAN to restore and recover individual data blocks from a backup. Exercise caution when performing block-level recovery as it can be complex and may require expert guidance.
Engage Oracle Support
If none of the above solutions resolve the issue, it is recommended to contact Oracle Support for further assistance. Provide them with the error details, associated trace files, and any relevant diagnostic information for analysis.
Note that data block corruption is a serious issue, and it is crucial to address it promptly. Always perform regular backups and ensure the integrity of your database to minimize the risk of data corruption. If you want to add more please comment.
Here are a few solutions and studies by oracle support.
- Bug 7381632 - ORA-1578 Free corrupt blocks may not be reformatted when Flashback is enabled (Doc ID 7381632.8)
- Content Reset fails with "java.sql.SQLException: ORA-01578: ORACLE data block corrupted " (Doc ID 2119387.1)
- Data Collections failing with ORA-01578: ORACLE data block corrupted error (Doc ID 2752761.1)
- ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution (Doc ID 794505.1)
- Use RMAN to format corrupt data block which is not part of any object (Doc ID 1459778.1)
- ORA-1578 Methods to Skip Block Corruption (Doc ID 2199133.1)
13.7.17
ORA-12541: TNS:no listener
ORA-1254: TNS: could not resolve the connect identifier specified
This error is because the connect identifier given, wrongalias, cannot be resolved
into database connection details by the TNS (Transparent Network Substrate—not an
acronym particularly worth remembering) layer of Oracle Net. The name resolution
method to be used and its configuration is a matter for the database administrator. In
this case, the error is obvious: the user entered the wrong connect identifier.
The second connect attempt gives the correct identifier, orcl.
This fails with
ORA-12541: TNS:no listener
This indicates that the connect identifier has resolved correctly into the address
of a database listener, but that the listener is not actually running. Note that another
possibility would be that the address resolution is faulty and is sending SQL*Plus
to the wrong address. Following this error, the user should contact the database
administrator and ask him or her to start the listener. Then try again.
The third connect request fails with
ORA-12514: TNS:listener does not currently know of service
requested in connect descriptor
This error is generated by the database listener. SQL*Plus has found the listener
with no problems, but the listener cannot make the onward connection to the database
service. The most likely reason for this is that the database instance has not been
started, so the user should ask the database administrator to start it and then try again.
This error is because the connect identifier given, wrongalias, cannot be resolved
into database connection details by the TNS (Transparent Network Substrate—not an
acronym particularly worth remembering) layer of Oracle Net. The name resolution
method to be used and its configuration is a matter for the database administrator. In
this case, the error is obvious: the user entered the wrong connect identifier.
The second connect attempt gives the correct identifier, orcl.
This fails with
ORA-12541: TNS:no listener
This indicates that the connect identifier has resolved correctly into the address
of a database listener, but that the listener is not actually running. Note that another
possibility would be that the address resolution is faulty and is sending SQL*Plus
to the wrong address. Following this error, the user should contact the database
administrator and ask him or her to start the listener. Then try again.
The third connect request fails with
ORA-12514: TNS:listener does not currently know of service
requested in connect descriptor
This error is generated by the database listener. SQL*Plus has found the listener
with no problems, but the listener cannot make the onward connection to the database
service. The most likely reason for this is that the database instance has not been
started, so the user should ask the database administrator to start it and then try again.
21.6.17
Cómo solucionar ORA-12505, Listener no conoce actualmente de SID
Cómo solucionar ORA-12505, Listener no conoce actualmente de SID
Este es un error muy común y realmente se chupar la sangre si no estás camino correcto. Intentaré explicarle sobre este error.
Como se puede ver este error se muestra que el oyente está en marcha y en funcionamiento, pero no sirve el SID que está destinado a. Y sí primero usted necesita comprobar donde usted está haciendo frente a este error. Es en el lado del cliente o en el lado del servidor.
Entonces, ¿cuál es el SID que está buscando este oyente?
SID es el nombre de su base de datos o puede decir identificador de servicio, que es ayudar a identificar el servicio como utilizamos PROD para la producción de UAT para el servidor de pruebas. SID de la base de datos debe ser idéntico para evitar la confusión. Su longitud es de 8 caracteres sólo significa que no se puede extender más de 8 caracteres.
¿Cómo funciona el oyente?
Como todos sabemos que es como un coche, que nos caen desde el aeropuerto al hotel, hahah lo siento, no soy bueno en los ejemplos. Así que permítanme explicar técnicamente, cada vez que intenta hacer la conexión de una máquina cliente con el servidor DB en lugar de conectar directamente oracle tiene esta utilidad llamada oyente. Como usted puede entender por su nombre, sí, sí sí que va a escuchar su reqst y luego pasar que a DB. Wow gran derecho.?
Ahora, ¿qué pasa si usted está tomando un taxi a algún hotel y ese hotel no existe? Lo mismo ocurre con este error. El conductor del taxi no sabe adonde usted va tan él a través de usted el error: -
"ORA-12505, TNS: Listener no sabe actualmente de SID dado en el descriptor de conexión"
¿Cómo solucionar esto "ORA-12505, TNS: el oyente no está actualmente"?
Es muy fácil. Dile al taxista nombre del hotel que realmente existe. La tarifa suficiente ¿verdad?
OK Sugerencias para solucionar problemas.
Todos sabemos que listner.ora y tnsnames.ora son dos archivos que están involucrados en este caso. Y la ubicación de estos archivos son $ ORACLE_HOME \ network \ admin
Ahora compruebe su tnsnames.ora que SID se menciona es correcto o no. Al igual que para mi prueba de DB es debe ser como a continuación: -
TEST_TNS = (DESCRIPCION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = *******) (PORT = 0000))) (CONNECT_DATA = (SERVICE_NAME = TEST) (INSTANCE_NAME = TEST)
Usted puede cambiar el formato también pero me gusta esta manera. Y sí PORT no puede ser 0000.;)
Ahora sabe que el nombre del servicio SID TEST está escuchando en el número de puerto 0000.
Si he establecido entradas exactas de las que no debe enfrentar ningún problema. Por favor, compruebe estas entradas. También verifique el archivo listner.ora también. Si hay desajuste en las entradas. Que vas a enfrentar el mismo problema
Eso resolverá su problema.
También compruebe este enlace: - ora-12154-error-en-oracle-11g-and-12c
Sigue compartiendo. Mantén la sonrisa
Este es un error muy común y realmente se chupar la sangre si no estás camino correcto. Intentaré explicarle sobre este error.
Como se puede ver este error se muestra que el oyente está en marcha y en funcionamiento, pero no sirve el SID que está destinado a. Y sí primero usted necesita comprobar donde usted está haciendo frente a este error. Es en el lado del cliente o en el lado del servidor.
Entonces, ¿cuál es el SID que está buscando este oyente?
SID es el nombre de su base de datos o puede decir identificador de servicio, que es ayudar a identificar el servicio como utilizamos PROD para la producción de UAT para el servidor de pruebas. SID de la base de datos debe ser idéntico para evitar la confusión. Su longitud es de 8 caracteres sólo significa que no se puede extender más de 8 caracteres.
¿Cómo funciona el oyente?
Como todos sabemos que es como un coche, que nos caen desde el aeropuerto al hotel, hahah lo siento, no soy bueno en los ejemplos. Así que permítanme explicar técnicamente, cada vez que intenta hacer la conexión de una máquina cliente con el servidor DB en lugar de conectar directamente oracle tiene esta utilidad llamada oyente. Como usted puede entender por su nombre, sí, sí sí que va a escuchar su reqst y luego pasar que a DB. Wow gran derecho.?
Ahora, ¿qué pasa si usted está tomando un taxi a algún hotel y ese hotel no existe? Lo mismo ocurre con este error. El conductor del taxi no sabe adonde usted va tan él a través de usted el error: -
"ORA-12505, TNS: Listener no sabe actualmente de SID dado en el descriptor de conexión"
¿Cómo solucionar esto "ORA-12505, TNS: el oyente no está actualmente"?
Es muy fácil. Dile al taxista nombre del hotel que realmente existe. La tarifa suficiente ¿verdad?
OK Sugerencias para solucionar problemas.
Todos sabemos que listner.ora y tnsnames.ora son dos archivos que están involucrados en este caso. Y la ubicación de estos archivos son $ ORACLE_HOME \ network \ admin
Ahora compruebe su tnsnames.ora que SID se menciona es correcto o no. Al igual que para mi prueba de DB es debe ser como a continuación: -
TEST_TNS = (DESCRIPCION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = *******) (PORT = 0000))) (CONNECT_DATA = (SERVICE_NAME = TEST) (INSTANCE_NAME = TEST)
Usted puede cambiar el formato también pero me gusta esta manera. Y sí PORT no puede ser 0000.;)
Ahora sabe que el nombre del servicio SID TEST está escuchando en el número de puerto 0000.
Si he establecido entradas exactas de las que no debe enfrentar ningún problema. Por favor, compruebe estas entradas. También verifique el archivo listner.ora también. Si hay desajuste en las entradas. Que vas a enfrentar el mismo problema
Eso resolverá su problema.
También compruebe este enlace: - ora-12154-error-en-oracle-11g-and-12c
Sigue compartiendo. Mantén la sonrisa
ORA-00240控製文件入隊持續超過XXX秒
ORA-00240控製文件入隊持續超過XXX秒
說明：控製文件排隊持有超過字符串秒
原因：1.當前進程沒有在最大允許時間內釋放控製文件入隊。
2.未經發布的Bug 7570453 - [3 RAC NODE] ORA-00240在升級到10.2.0.4.0之後啟動的問題已被調查，該錯誤被關閉為Not Bug。開發人員確認這只是一個警告，讓DBA知道一個CF入隊被持有超過120秒。這不是一個錯誤，如果CF入隊超過900秒（15分鐘），則不會發生錯誤。
當數據庫中有很多數據文件時，會發生該消息。 DBWriter（dbw0）由於不得不打開這些數據文件而花費太多的時間來釋放CF入隊。
操作：1.重新發出失敗的任何命令，並與事件信息聯繫Oracle支持服務。
2.建議通過減少數據庫文件的數量來減少這個時間。更多的是建議使用較少但較大的數據文件，而不是較小的數據文件。
如何照顧Oracle審計
什麼是Oracle 10天規則？
說明：控製文件排隊持有超過字符串秒
原因：1.當前進程沒有在最大允許時間內釋放控製文件入隊。
2.未經發布的Bug 7570453 - [3 RAC NODE] ORA-00240在升級到10.2.0.4.0之後啟動的問題已被調查，該錯誤被關閉為Not Bug。開發人員確認這只是一個警告，讓DBA知道一個CF入隊被持有超過120秒。這不是一個錯誤，如果CF入隊超過900秒（15分鐘），則不會發生錯誤。
當數據庫中有很多數據文件時，會發生該消息。 DBWriter（dbw0）由於不得不打開這些數據文件而花費太多的時間來釋放CF入隊。
操作：1.重新發出失敗的任何命令，並與事件信息聯繫Oracle支持服務。
2.建議通過減少數據庫文件的數量來減少這個時間。更多的是建議使用較少但較大的數據文件，而不是較小的數據文件。
如何照顧Oracle審計
什麼是Oracle 10天規則？
20.6.17
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
How to solve ORA-12505, Listener does not currently know of SID
This is very common error and really it will suck your blood if you are not right path. I will try to explain you about this error.
As you can see this error is showing itself that listener is up and running but not serving the SID that it is meant for. And yes 1st you need to check where you are facing this error. Is it on client side or on server side.
So what is the SID this listener looking for?
SID is name for your database or you can say service identifier, That is help to to identify the the service like we use PROD for production UAT for testing server. SID of Database should be identical to avoid the confusion. It's length is 8 character only that means you can not extend it more then 8 character.
Now how listener works ?
As we all know its like a car, That drop us from airport to hotel, hahah sorry I am not good in examples. So let me explain technically, whenever you try to make connection from a client machine to the DB server rather than connecting directly oracle have this utility called listener. As you can understand by name it self, yes yes it is going to listen your reqst and then pass that to DB. wow great right.?
Now what if you are taking a taxi to some hotel and that hotel does not exist? Same happens with this error. Taxi driver don't know where you are going so it will through you the error :-
"ORA-12505, TNS:Listener does not currently know of SID given in connect descriptor"
How to solve this "ORA-12505, TNS:listener does not currently" ?
It's very easy. Tell the taxi driver name of hotel that really exist. Fare enough right?
OK Tips for troubleshooting.
We all know that listner.ora and tnsnames.ora are two files that are involved in this case. and location of these files are $ORACLE_HOME\network\admin
Now check your tnsnames.ora that SID is mentioned is correct or not. Like for my TEST DB is should be like below :-
TEST_TNS = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=*******)(PORT=0000)))(CONNECT_DATA=(SERVICE_NAME=TEST)(INSTANCE_NAME=TEST)))
You can change the format also but i like this way. and yes PORT can't be 0000. ;)
Now you know SID service name TEST is listening at 0000 port number.
If I have set accurate entries than you shouldn't face any issue. So Please check these entries. Also check listner.ora file too. If there is mismatch in entries. Than you going to face same issue
That will solve your problem.
Also check this link :- ora-12154-error-in-oracle-11g-and-12c
Keep sharing. Keep smile ;)
20.5.17
ORA-02291: integrity constraint violated - parent key not found
“Error: ORA-02291: integrity constraint violated - parent key not found”
Reason: A Primary key does not have the same value as the foreign key. We will discuss it in detail later in this article.
Action: For ORA-02291: integrity constraint violated - parent key not found You may either delete the foreign key or the matching primary key can be added. In either way, you may try to get this error corrected.
Let's understand more about ORA-02291: integrity constraint violated - parent key not found?
The Oracle software brought us the strength by which multiple tables in the database can pass on information so efficiently. Not only this, there are numerous devices in this software which enables access to and sourcing data from multiple tables. You can easily execute complicated database issues without an unusual uncertainty by creating statements with the fantastic characteristic of this software. Realistically, if we talk about user or database no one is perfect or 100% error-free or have the sense to identify forthcoming possible errors occurs throughout the regular activities.
The most well-known error occurs while manipulating data across multiple data tables is the ORA-02291.Now, we will have a brief discussion about ORA-02291: integrity constraint violated - parent key not found:-
" integrity constraint <constraint name> violated – parent key not found” is the standard message co-occurred with the ORA-02291.Which means someone used the primary key in order to execute a reference to a specific table but somehow, during this process specified column failed to match the primary key. As well as, the other reason for executing the error can be non-existence of primary key for the table in question.It is useful to secure a note of few important things related to the primary key before we proceed further.
In oracle, a primary key is a single field or combination of fields that defines a record in a unique way. Every field of the primary key must contain some value, it means, the value of any field can never be null and each table contains only one primary key. One last important thing about a primary key that it cannot hold more than thirty-two columns.
Now while we are familiar with all concepts of a primary key, it will not be difficult for us to determine the error and fix it with fewer efforts (ORA-02291: integrity constraint violated - parent key not found).
Usually, the error occurs when foreign key plays a parent-child relation between two tables. The process of foreign key states that each table shares the same value. overall, the referenced table is a parent table. On the other hand, the child table will take place where foreign key originates from. It is quite often that a foreign key in a child table will reference to a primary key in a parent table. The cause why the ORA-02291 trigger is when the corresponding parent is empty (does not have any value) and by the time, you still try to insert a value into a child table which includes a foreign key. Your this action disrupts the integrity of referential relationship, bother standard functionality and provoking oracle to produce an error message "ORA-02291: integrity constraint violated - parent key not found".
“The valid method to fix the error ORA-02291: integrity constraint violated - parent key not found “--- the primary table to insert the value will be the parent and obviously secondary table to insert value will be the child table. To elaborate, you should insert the value inside the parent table first and later, add it to the child table.
keep sharing :)
17.5.17
ORA-01033: ORACLE initialization or shutdown in progress
ORA-01033: ORACLE initialization or shutdown in progress
Cause: You are trying to access oracle database while database is either starting up or shuting down (ORA-01033: ORACLE initialization or shutdown in progress).
Action: For ORA-01033: ORACLE initialization or shutdown in progress you need to wait for some time. Then retry the operation again (connect after some time) to check if ORA-01033: ORACLE initialization or shutdown in progress is there or not.
What is ORA-01033: ORACLE initialization or shutdown in progress?
This is very common error and not occurs much. But you must have some idea if you face ORA-01033: ORACLE initialization or shutdown in progress occurs. As we know We can only set database to Shutdown or startup using SYSDBA privileges. So you must have SYSDBA privileges to troubleshoot. I suggest to wait for 5mins because may be your DBA has shutdown the Database and it's in progress.Or if you are a DBA then check the status of database do start if it is down. My case below:-
[server@****** ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.1.0 Production on Wed May 17 14:24:19 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL>conn test/test@test
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
The ORA-01033 error also happens when the database is not open. SO in this case below is the solution :
"alter database open"
Brief about ORA-01033: ORACLE initialization or shutdown in progress :-
Sometime when We stop our oracle database then if at the same time any user will try to access the database then that user will face the issue ORA-01033: ORACLE initialization or shutdown in progress .For work around to solve "The ORA-01033: ORACLE initialization or shutdown in progress" ask your user to wait for some time and check the status of the database. Also check the alert logs for error ORA-01033: ORACLE initialization or shutdown in progress.You need to check the status and see if your database is in the middle of startup or shutdown.
There may be other cause for error ORA-01033: ORACLE initialization or shutdown in progress is like when RAM regions held by the OS and that makes Oracle think that instance is already running. Then also Database will through the error ORA-01033: ORACLE initialization or shutdown in progress.
Solution for ORA-01033: ORACLE initialization or shutdown in progress provided by oracle. Check your database status and paas below command by SYSDBA if your database is not open.
1. wait for some time
2. check the status of DB
3. "alter database open"
Keep sharing :)
Cause: You are trying to access oracle database while database is either starting up or shuting down (ORA-01033: ORACLE initialization or shutdown in progress).
Action: For ORA-01033: ORACLE initialization or shutdown in progress you need to wait for some time. Then retry the operation again (connect after some time) to check if ORA-01033: ORACLE initialization or shutdown in progress is there or not.
What is ORA-01033: ORACLE initialization or shutdown in progress?
This is very common error and not occurs much. But you must have some idea if you face ORA-01033: ORACLE initialization or shutdown in progress occurs. As we know We can only set database to Shutdown or startup using SYSDBA privileges. So you must have SYSDBA privileges to troubleshoot. I suggest to wait for 5mins because may be your DBA has shutdown the Database and it's in progress.Or if you are a DBA then check the status of database do start if it is down. My case below:-
[server@****** ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.1.0 Production on Wed May 17 14:24:19 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL>conn test/test@test
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
The ORA-01033 error also happens when the database is not open. SO in this case below is the solution :
"alter database open"
Brief about ORA-01033: ORACLE initialization or shutdown in progress :-
Sometime when We stop our oracle database then if at the same time any user will try to access the database then that user will face the issue ORA-01033: ORACLE initialization or shutdown in progress .For work around to solve "The ORA-01033: ORACLE initialization or shutdown in progress" ask your user to wait for some time and check the status of the database. Also check the alert logs for error ORA-01033: ORACLE initialization or shutdown in progress.You need to check the status and see if your database is in the middle of startup or shutdown.
There may be other cause for error ORA-01033: ORACLE initialization or shutdown in progress is like when RAM regions held by the OS and that makes Oracle think that instance is already running. Then also Database will through the error ORA-01033: ORACLE initialization or shutdown in progress.
Solution for ORA-01033: ORACLE initialization or shutdown in progress provided by oracle. Check your database status and paas below command by SYSDBA if your database is not open.
1. wait for some time
2. check the status of DB
3. "alter database open"
Keep sharing :)
10.5.17
ORA-12714 invalid national character set specified
Error: ORA-12714
ORA-12714 invalid national character set specified
ORA-12714 invalid national character set specified is very common error and related to database parameters and sessions parameters.
Cause
Only UTF8 and AL16UTF16 are allowed to be used as the national character set. Check your NLS_NCHAR_CHARACTERSET which is set using:
select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';
it should return UTF8 Or AL16UTF16
Action
Ensure that the specified national character set is valid
Please aware that at session level, some parameters could be different. If you want to be sure, compare the results of:
select * from nls_database_parameters;
with:
select * from nls_session_parameters;
Bug
No :- 2834295
Cause :-
declaring cursor for a function that returns a table of NVARCHAR2
Fixed:
ORA-12714 invalid national character set specified
ORA-12714 invalid national character set specified is very common error and related to database parameters and sessions parameters.
Cause
Only UTF8 and AL16UTF16 are allowed to be used as the national character set. Check your NLS_NCHAR_CHARACTERSET which is set using:
select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';
it should return UTF8 Or AL16UTF16
Action
Ensure that the specified national character set is valid
Please aware that at session level, some parameters could be different. If you want to be sure, compare the results of:
select * from nls_database_parameters;
with:
select * from nls_session_parameters;
Please find the below information:-
Bug
No :- 2834295
Cause :-
declaring cursor for a function that returns a table of NVARCHAR2
Affects below products :
|
Product (Component)
|
Oracle Server (Rdbms)
|
|
Range of versions believed to be affected
|
Versions BELOW 11.1
|
|
Versions confirmed as being affected
|
|
|
Platforms affected
|
Generic (all / most platforms affected)
|
Fixed:
|
The fix for 2834295 is first included in
|
|
Please check for document
number on oracle support :- 2834295.8
Subscribe to:
Posts (Atom)