1)Transactions with huge undo.
-------------------------------
It is obvious to see high undo usage when there are huge transactions. If that is going to be the case this growth should be expected behavior.
2)The Higher value setting of UNDO_RETENTION.
------------------------------------------------
The higher value of UNDO_RETENTION will increase much undo. As it can't be marked undo extents as EXPIRED till the duration of the UNDO_RETENTION.
3)Autoextensible mode of Undo data files.
--------------------------------------------------
Disabling the auto extensible mode of the datafiles of active undo tablespace will reuse the UNEXPIRED extents when undo tablespace suffer in space pressure.If they are set to auto extensible, it will not reuse the space for the new transaction. In such scenarios new transaction will allocate a space and your undo tablespace will start growing.
4)Undo tablespace is using Autoallocate option:
----------------------------------------------
UNDO is using auto allocate option of LMT. As the number of the extents goes up, the extent size will be increased too.When the number is extents reaches hundred the extent size will be bigger. If it reaches thousands it will be even more bigger.If you know the size of most of your transactions you can use UNIFORM rather than AUTO.
How Undo Allocation Done
Saturday, April 19, 2008
Swap Space in Solaris
Many one argues different way to identify of how to check swap space in Solaris and the result varies. Like they use df -h to check swap space. But to check swap space, and to get correct result the following query need to be issued.
swap -l reports total and free space for each of the swap partitions or files that are available to the system. Note that this number does not reflect total available virtual memory space, since physical memory is not reflected in the output.
swap -s reports the total available amount of virtual memory
If swap is mounted on /tmp via tmpfs, df -k /tmp will report on total available virtual memory space, both swap and physical.
swap -l reports total and free space for each of the swap partitions or files that are available to the system. Note that this number does not reflect total available virtual memory space, since physical memory is not reflected in the output.
swap -s reports the total available amount of virtual memory
If swap is mounted on /tmp via tmpfs, df -k /tmp will report on total available virtual memory space, both swap and physical.
Troubleshoot unusable Index in Oracle
If a procedural object, such as a stored PL/SQL function or a view, becomes invalid, the DBA does not necessarily have to do anything: the first time it is accessed, Oracle will attempt to recompile it, and this may well succeed.
But if an index becomes unusable for any reason, it must always be repaired explicitly before it can be used.
This is because an index consists of the index key values, sorted into order, each with the relevant
rowid. The rowid is the physical pointer to the location of the row to which the index key refers.
If the rowids of the table are changed, then the index will be marked as unusable. This could occur for a number of reasons. Perhaps the most common is that the table has been moved, with the ALTER TABLE...MOVE command. This will change the physical placement of all the rows, and therefore the index entries will be pointing to the wrong place. Oracle will be aware of this and will therefore not permit use of the index.
Identifying Unusable Indexes
---------------------------------
In earlier releases of the Oracle database, when executing SQL statements, if the session attempted to use an unusable index it would immediately return an error, and the statement would fail. Release 10g of the database changes this behavior. If a statement attempts to use an unusable index, the statement will revert to an execution plan that does not require the index.
Thus, statements will always succeed—but perhaps at the cost of greatly reduced performance. This behavior is controlled by the instance parameter SKIP_UNUSABLE_INDEXES, which defaults to TRUE. The exception to this is if the index is necessary to enforce a constraint: if the index on a primary key column becomes unusable, the table will be locked for DML.
To detect indexes which have become unusable, query the DBA_INDEXES view:
SQL> select owner, index_name from dba_indexes where status='UNUSABLE';
Repairing Unusable Indexes
----------------------------------------
To repair the index, it must be re-created with the ALTER INDEX...REBUILD command.
This will make a pass through the table, generating a new index with correct rowid pointers
for each index key. When the new index is completed, the original unusable index is dropped.
The syntax of the REBUILD command has several options. The more important ones are TABLESPACE, ONLINE, and NOLOGGING. By default, the index will be rebuilt within its current tablespace, but by specifying a table space with the TABLESPACE keyword, it can be moved to a different one.
Also by default, during the course of the rebuild the table will be locked for DML. This can be avoided by using the ONLINE keyword.
1)Create Table and insert row in it:
----------------------------------------
SQL> create table test ( a number primary key);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
2)Check the Index Status
--------------------------
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
SYS_C0044513 VALID
3)Move the Table and Check Status:
------------------------------------
SQL> alter table test move;
Table altered.
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
SYS_C0044513 UNUSABLE
4)Rebuild The Index:
-----------------------
SQL> alter index SYS_C0044514 rebuild online;
Index altered.
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
SYS_C0044514 VALID
But if an index becomes unusable for any reason, it must always be repaired explicitly before it can be used.
This is because an index consists of the index key values, sorted into order, each with the relevant
rowid. The rowid is the physical pointer to the location of the row to which the index key refers.
If the rowids of the table are changed, then the index will be marked as unusable. This could occur for a number of reasons. Perhaps the most common is that the table has been moved, with the ALTER TABLE...MOVE command. This will change the physical placement of all the rows, and therefore the index entries will be pointing to the wrong place. Oracle will be aware of this and will therefore not permit use of the index.
Identifying Unusable Indexes
---------------------------------
In earlier releases of the Oracle database, when executing SQL statements, if the session attempted to use an unusable index it would immediately return an error, and the statement would fail. Release 10g of the database changes this behavior. If a statement attempts to use an unusable index, the statement will revert to an execution plan that does not require the index.
Thus, statements will always succeed—but perhaps at the cost of greatly reduced performance. This behavior is controlled by the instance parameter SKIP_UNUSABLE_INDEXES, which defaults to TRUE. The exception to this is if the index is necessary to enforce a constraint: if the index on a primary key column becomes unusable, the table will be locked for DML.
To detect indexes which have become unusable, query the DBA_INDEXES view:
SQL> select owner, index_name from dba_indexes where status='UNUSABLE';
Repairing Unusable Indexes
----------------------------------------
To repair the index, it must be re-created with the ALTER INDEX...REBUILD command.
This will make a pass through the table, generating a new index with correct rowid pointers
for each index key. When the new index is completed, the original unusable index is dropped.
The syntax of the REBUILD command has several options. The more important ones are TABLESPACE, ONLINE, and NOLOGGING. By default, the index will be rebuilt within its current tablespace, but by specifying a table space with the TABLESPACE keyword, it can be moved to a different one.
Also by default, during the course of the rebuild the table will be locked for DML. This can be avoided by using the ONLINE keyword.
1)Create Table and insert row in it:
----------------------------------------
SQL> create table test ( a number primary key);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
2)Check the Index Status
--------------------------
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
SYS_C0044513 VALID
3)Move the Table and Check Status:
------------------------------------
SQL> alter table test move;
Table altered.
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
SYS_C0044513 UNUSABLE
4)Rebuild The Index:
-----------------------
SQL> alter index SYS_C0044514 rebuild online;
Index altered.
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------ --------
SYS_C0044514 VALID
Troubleshoot invalid objects in Oracle
Most of the procedural objects like procedures, functions, triggers, objects refer to data objects, such as table.
-If the code refers to a column, the column must exist or the code will not compile.
-If any of the data objects to which a procedural object refers change after the procedural object has been compiled, then the procedure will be marked INVALID.
-Procedural objects may also be invalid for more mundane reasons: perhaps the
programmer made a simple syntactical mistake.
The same situation can occur with views. When created they may be fine, but they will be invalidated if the tables on which they are based have their definitions changed.
Identifying Invalid Objects
--------------------------------
To identify all of the invalid objects , run the following query,
SQL> select owner,object_name,object_type from dba_objects where
status='INVALID';
How to Handle Invalid Objects:
-------------------------------
1)Compile the invalid objects:
-------------------------------
The first steps is to compile the invalid objects. Though, the first time an invalid object is accessed, Oracle will attempt to compile it automatically, but if the compilation fails, the user will receive an error. Clearly, it is better for the DBA to compile it first; then, if there is an error, he can try to fix it before a user notices.
2)If compilation is ok then there is no problem. But if it fails then check the object and object type. Suppose if the object type is procedure then you can use SHOW ERRORS to identify the the errors.
3)If the object type is view then SHOW ERRORS will not work then try to see the creation script of the view.
SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME='YOUR VIEW';
Generally you can use DBA_DEPENDENCIES object and query column REFERENCED_OWNER and REFERENCED_NAME.
4)There will be occasions when you are faced with the need to recompile hundreds or thousands of invalid objects. Typically, this occurs after an upgrade to an application, or perhaps after applying patches. Rather than recompiling them individually, use the supplied utility script. On Unix,
SQL> @?/rdbms/admin/utlrp
Workaround Example:
---------------------
1)Create one table, one invalid view and one valid view:
----------------------------------------------------------
SQL> create table first_table ( col1 number, col2 number);
Table created.
SQL> create force view first_view as select col3 from first_table;
Warning: View created with compilation errors.
SQL> create view second_view as select col2 from first_table;
View created.
2)Check Status:
----------------
SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW VALID
3)Drop Column col2 from Table and Check Status:
-----------------------------------------------
SQL> alter table first_table drop column col2;
Table altered.
SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW INVALID
4)Add column col3 and Check Status:
--------------------------------------
SQL> alter table FIRST_TABLE add (col3 number);
Table altered.
SQL> alter view FIRST_VIEW compile;
View altered.
SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW VALID
SECOND_VIEW VIEW INVALID
5)Troubleshoot Second View:
-----------------------------
SQL> select text from user_views where view_name='SECOND_VIEW';
TEXT
--------------------------------------------------------------------------------
select col2 from first_table
SQL> select REFERENCED_NAME,REFERENCED_TYPE from user_dependencies where name='SECOND_VIEW';
REFERENCED_NAME REFERENCED_TYPE
-------------------- -----------------
COL2 NON-EXISTENT
COL2 NON-EXISTENT
FIRST_TABLE TABLE
So col2 is non-existent or missing.
SQL> alter table FIRST_TABLE add col2 number;
Table altered.
SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW INVALID
Interestingly when I add col2 FIRST_VIEW becomes invalid.
And also when deleting a referenced column status of dependent objects become invalid. But when we add the status does not changes.
6)Now compile on second_view or any access will marked the dictionary ok.
SQL> select * from second_view;
no rows selected
SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW VALID
But First_view become INVALID.
7)Fix Error:
---------------
SQL> alter view FIRST_VIEW compile;
View altered.
SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW VALID
SECOND_VIEW VIEW VALID
SQL> select REFERENCED_NAME,REFERENCED_TYPE from user_dependencies where name='SECOND_VIEW';
REFERENCED_NAME REFERENCED_TYPE
-------------------- -----------------
FIRST_TABLE TABLE
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html
-If the code refers to a column, the column must exist or the code will not compile.
-If any of the data objects to which a procedural object refers change after the procedural object has been compiled, then the procedure will be marked INVALID.
-Procedural objects may also be invalid for more mundane reasons: perhaps the
programmer made a simple syntactical mistake.
The same situation can occur with views. When created they may be fine, but they will be invalidated if the tables on which they are based have their definitions changed.
Identifying Invalid Objects
--------------------------------
To identify all of the invalid objects , run the following query,
SQL> select owner,object_name,object_type from dba_objects where
status='INVALID';
How to Handle Invalid Objects:
-------------------------------
1)Compile the invalid objects:
-------------------------------
The first steps is to compile the invalid objects. Though, the first time an invalid object is accessed, Oracle will attempt to compile it automatically, but if the compilation fails, the user will receive an error. Clearly, it is better for the DBA to compile it first; then, if there is an error, he can try to fix it before a user notices.
2)If compilation is ok then there is no problem. But if it fails then check the object and object type. Suppose if the object type is procedure then you can use SHOW ERRORS to identify the the errors.
3)If the object type is view then SHOW ERRORS will not work then try to see the creation script of the view.
SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME='YOUR VIEW';
Generally you can use DBA_DEPENDENCIES object and query column REFERENCED_OWNER and REFERENCED_NAME.
4)There will be occasions when you are faced with the need to recompile hundreds or thousands of invalid objects. Typically, this occurs after an upgrade to an application, or perhaps after applying patches. Rather than recompiling them individually, use the supplied utility script. On Unix,
SQL> @?/rdbms/admin/utlrp
Workaround Example:
---------------------
1)Create one table, one invalid view and one valid view:
----------------------------------------------------------
SQL> create table first_table ( col1 number, col2 number);
Table created.
SQL> create force view first_view as select col3 from first_table;
Warning: View created with compilation errors.
SQL> create view second_view as select col2 from first_table;
View created.
2)Check Status:
----------------
SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW VALID
3)Drop Column col2 from Table and Check Status:
-----------------------------------------------
SQL> alter table first_table drop column col2;
Table altered.
SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW INVALID
4)Add column col3 and Check Status:
--------------------------------------
SQL> alter table FIRST_TABLE add (col3 number);
Table altered.
SQL> alter view FIRST_VIEW compile;
View altered.
SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW VALID
SECOND_VIEW VIEW INVALID
5)Troubleshoot Second View:
-----------------------------
SQL> select text from user_views where view_name='SECOND_VIEW';
TEXT
--------------------------------------------------------------------------------
select col2 from first_table
SQL> select REFERENCED_NAME,REFERENCED_TYPE from user_dependencies where name='SECOND_VIEW';
REFERENCED_NAME REFERENCED_TYPE
-------------------- -----------------
COL2 NON-EXISTENT
COL2 NON-EXISTENT
FIRST_TABLE TABLE
So col2 is non-existent or missing.
SQL> alter table FIRST_TABLE add col2 number;
Table altered.
SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW INVALID
Interestingly when I add col2 FIRST_VIEW becomes invalid.
And also when deleting a referenced column status of dependent objects become invalid. But when we add the status does not changes.
6)Now compile on second_view or any access will marked the dictionary ok.
SQL> select * from second_view;
no rows selected
SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW INVALID
SECOND_VIEW VIEW VALID
But First_view become INVALID.
7)Fix Error:
---------------
SQL> alter view FIRST_VIEW compile;
View altered.
SQL> select object_name, object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
FIRST_TABLE TABLE VALID
FIRST_VIEW VIEW VALID
SECOND_VIEW VIEW VALID
SQL> select REFERENCED_NAME,REFERENCED_TYPE from user_dependencies where name='SECOND_VIEW';
REFERENCED_NAME REFERENCED_TYPE
-------------------- -----------------
FIRST_TABLE TABLE
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html
Thursday, April 17, 2008
About Dynamic Performance Views
Oracle contains a set of underlying views that are maintained by the database server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance.
1)V$ Views:
---------------
Though many of us may already know that those name which prefixed by V$ are original dynamic performance view but they are not really views; they are public synonyms of the view. The actual dynamic performance views are identified by the prefix V_$. Database administrators and other users should access only the V$ objects, not the V_$ objects.
2)GV$ Views
---------------
For almost every V$ view, Oracle has a corresponding GV$ (global V$) view. In Real Application Clusters, querying a GV$ view retrieves the V$ view information from all qualified instances. In addition to the V$ information, each GV$ view contains an extra column named INST_ID of datatype NUMBER. The INST_ID column displays the instance number from which the associated V$ view information was obtained. For example, the following query retrieves the information from the V$LOCK view on instances 3 and 5:
SQL> SELECT * FROM GV$LOCK WHERE INST_ID = 3 OR INST_ID = 5;
1)V$ Views:
---------------
Though many of us may already know that those name which prefixed by V$ are original dynamic performance view but they are not really views; they are public synonyms of the view. The actual dynamic performance views are identified by the prefix V_$. Database administrators and other users should access only the V$ objects, not the V_$ objects.
2)GV$ Views
---------------
For almost every V$ view, Oracle has a corresponding GV$ (global V$) view. In Real Application Clusters, querying a GV$ view retrieves the V$ view information from all qualified instances. In addition to the V$ information, each GV$ view contains an extra column named INST_ID of datatype NUMBER. The INST_ID column displays the instance number from which the associated V$ view information was obtained. For example, the following query retrieves the information from the V$LOCK view on instances 3 and 5:
SQL> SELECT * FROM GV$LOCK WHERE INST_ID = 3 OR INST_ID = 5;
Types of Initialization Parameters in Oracle
There are different types of initialization parameters in oracle, such as,
1)Derived Parameters
2)Operating System-Dependent Parameters
3)Variable Parameters
1)Derived Parameters:
-----------------------
As the name indicates these parameter values are calculated from the values of other parameters. Normally, you should not alter values for derived parameters, but if you do, then the value you specify will override the calculated value.
For example, the default value of the SESSIONS parameter is derived from the value of the PROCESSES parameter. If the value of PROCESSES changes, then the default value of SESSIONS changes as well, unless you override it with a specified value.
2)Operating System-Dependent Parameters
--------------------------------------
The valid value of value ranges of these parameters are host Operating System dependent. For example, the value of the parameter DB_BLOCK_SIZE, has an operating system-dependent default value.
3)Variable Parameters
----------------------------
The variable initialization parameters offer the most potential for improving system performance.
Some variable parameters set capacity limits but do not affect performance. For example, when the value of OPEN_CURSORS is 10, a user process attempting to open its eleventh cursor receives an error. Other variable parameters affect performance but do not impose absolute limits.
1)Derived Parameters
2)Operating System-Dependent Parameters
3)Variable Parameters
1)Derived Parameters:
-----------------------
As the name indicates these parameter values are calculated from the values of other parameters. Normally, you should not alter values for derived parameters, but if you do, then the value you specify will override the calculated value.
For example, the default value of the SESSIONS parameter is derived from the value of the PROCESSES parameter. If the value of PROCESSES changes, then the default value of SESSIONS changes as well, unless you override it with a specified value.
2)Operating System-Dependent Parameters
--------------------------------------
The valid value of value ranges of these parameters are host Operating System dependent. For example, the value of the parameter DB_BLOCK_SIZE, has an operating system-dependent default value.
3)Variable Parameters
----------------------------
The variable initialization parameters offer the most potential for improving system performance.
Some variable parameters set capacity limits but do not affect performance. For example, when the value of OPEN_CURSORS is 10, a user process attempting to open its eleventh cursor receives an error. Other variable parameters affect performance but do not impose absolute limits.
Creating Multiple Tables and Views in a Single Operation
Sometimes you may need to do several ddl statements in a database.Suppose in a file you have list of DDL creation script. Now after creating some objects some DDL commands fails and it proceed further. As DDL tasks can't be rolled back so it may be cumbersome and difficult to identify/track which DDL command is actually failed. There is a command CREATE SCHEMA by which it can be ensured that either entire statement will successfully executed or entire statement will fail.
You can create several tables and views and grant privileges in one operation using the CREATE SCHEMA statement.
If an individual table, view or grant fails, the entire statement is rolled back. None of the objects are created, nor are the privileges granted.
The CREATE SCHEMA statement can include only CREATE TABLE, CREATE VIEW, and GRANT statements.
Remember by using statement CREATE SCHEMA you are not actually creating a schema, that is done when the user is created with a CREATE USER statement. Rather, you are populating the schema.
Example:
---------
With following example a table named table1 and a view named view1 will be created under ARJU schema.
SQL> conn arju/a
Connected.
SQL> CREATE SCHEMA AUTHORIZATION arju create table table1(a number primary key) create view view1 as select * from table1;
Schema created.
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html
You can create several tables and views and grant privileges in one operation using the CREATE SCHEMA statement.
If an individual table, view or grant fails, the entire statement is rolled back. None of the objects are created, nor are the privileges granted.
The CREATE SCHEMA statement can include only CREATE TABLE, CREATE VIEW, and GRANT statements.
Remember by using statement CREATE SCHEMA you are not actually creating a schema, that is done when the user is created with a CREATE USER statement. Rather, you are populating the schema.
Example:
---------
With following example a table named table1 and a view named view1 will be created under ARJU schema.
SQL> conn arju/a
Connected.
SQL> CREATE SCHEMA AUTHORIZATION arju create table table1(a number primary key) create view view1 as select * from table1;
Schema created.
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html
kccrsz: denied expansion of controlfile section 9 by 65535 record(s)
Problem Description:
-------------------------
The machine did not respond properly and in the alert log you the got the following.
kccrsz: denied expansion of controlfile section 9 by 65535 record(s)
the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
RECID #528019 Recno 3739 Record timestamp
04/13/08 16:26:16
Thread=1 Seq#=528019 Link-Recid=528018
Error Description:
----------------------
1)Check the default value of control file record keep time.
SQL> show parameter control_file_record_keep_time
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
2)Check the MAXLOGHISTORY database parameter.
SQL>alter database backup controlfile to trace as '/oradata/1.txt'
Check MAXLOGHISTORY value from /oradata/1.txt
Or, you can check RECORDS_TOTAL value from v$controlfile_record_section
SQL> select * from v$controlfile_record_section where type='LOG HISTORY' ;
Now the problem is occurred due to control_file_record_keep_time and MAXLOGHISTORY settings.
The MAXLOGHISTORY increases dynamically when the CONTROL_FILE_RECORD_KEEP_TIME is set to a value different from 0, but does not exceed MAXLOGHISTORY (65535).Once reached, the message appears in the alert log.
From above error it is seen the MAXLOGHISTORY parameter has already reached the maximum of 65535 and it cannot be increased anymore.
Solution of The problem:
---------------------------
1)Set the CONTROL_FILE_RECOED_KEEP_TIME to zero.
alter system set control_file_record_keep_time=0;
or.
2)Change the MAXLOGHISTORY parameter.
The compatibility level is set to a value that is earlier than 10.2.0, and you must create new new control file by CREATE CONTROLFILE command. If compatibility is 10.2.0 or later, you do not have to create new control files when you make such a change; the control files automatically expand, if necessary, to accommodate the new configuration information.
Caution:
-----------
Setting control_file_record_keep_time to zero is dangerous for making RMAN backups. In that case use recovery catalog as repository for RMAN backup instead of control file.
Related Link:
-------------
Create Controlfile
Controlfile Information
-------------------------
The machine did not respond properly and in the alert log you the got the following.
kccrsz: denied expansion of controlfile section 9 by 65535 record(s)
the number of records is already at maximum value (65535)
krcpwnc: following controlfile record written over:
RECID #528019 Recno 3739 Record timestamp
04/13/08 16:26:16
Thread=1 Seq#=528019 Link-Recid=528018
Error Description:
----------------------
1)Check the default value of control file record keep time.
SQL> show parameter control_file_record_keep_time
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
2)Check the MAXLOGHISTORY database parameter.
SQL>alter database backup controlfile to trace as '/oradata/1.txt'
Check MAXLOGHISTORY value from /oradata/1.txt
Or, you can check RECORDS_TOTAL value from v$controlfile_record_section
SQL> select * from v$controlfile_record_section where type='LOG HISTORY' ;
Now the problem is occurred due to control_file_record_keep_time and MAXLOGHISTORY settings.
The MAXLOGHISTORY increases dynamically when the CONTROL_FILE_RECORD_KEEP_TIME is set to a value different from 0, but does not exceed MAXLOGHISTORY (65535).Once reached, the message appears in the alert log.
From above error it is seen the MAXLOGHISTORY parameter has already reached the maximum of 65535 and it cannot be increased anymore.
Solution of The problem:
---------------------------
1)Set the CONTROL_FILE_RECOED_KEEP_TIME to zero.
alter system set control_file_record_keep_time=0;
or.
2)Change the MAXLOGHISTORY parameter.
The compatibility level is set to a value that is earlier than 10.2.0, and you must create new new control file by CREATE CONTROLFILE command. If compatibility is 10.2.0 or later, you do not have to create new control files when you make such a change; the control files automatically expand, if necessary, to accommodate the new configuration information.
Caution:
-----------
Setting control_file_record_keep_time to zero is dangerous for making RMAN backups. In that case use recovery catalog as repository for RMAN backup instead of control file.
Related Link:
-------------
Create Controlfile
Controlfile Information
Archive Destination Status
To obtain the current status and other information about each destination for an instance, query the V$ARCHIVE_DEST view.
Like, select dest_id, dest_name , status from v$archive_dest;
Archive Destination may have following status.
1)VALID: The user has properly initialized the destination, which is available for archiving.
2)INACTIVE: The user either has not provided or has deleted the destination information.
3)ERROR: An error occurred creating or writing to the destination file. If you see search for ERROR column for more detail.
4)FULL: Destination is full (no disk space).
5)DEFERRED: The user manually and temporarily disabled the destination.
6)DISABLED: The user manually and temporarily disabled the destination following an error; Look at ERROR column.
7)BAD PARAM: A parameter error occurred; refer to error column.
Related Link:
------------------
Change Archive Mode
Like, select dest_id, dest_name , status from v$archive_dest;
Archive Destination may have following status.
1)VALID: The user has properly initialized the destination, which is available for archiving.
2)INACTIVE: The user either has not provided or has deleted the destination information.
3)ERROR: An error occurred creating or writing to the destination file. If you see search for ERROR column for more detail.
4)FULL: Destination is full (no disk space).
5)DEFERRED: The user manually and temporarily disabled the destination.
6)DISABLED: The user manually and temporarily disabled the destination following an error; Look at ERROR column.
7)BAD PARAM: A parameter error occurred; refer to error column.
Related Link:
------------------
Change Archive Mode
Wednesday, April 16, 2008
Using ALTER SYSTEM to Change Initialization Parameter Values
Whenever you use ALTER SYSTEM SET parameter_name=value what will happened to the parameter? Will the setting is permanent or temporary or will the settings will be affected after restarting the database instance? I will try to make clear this point in my next section.
1)The first thing is need to remember that whenever we issue ALTER SYSTEM command nothing to do with pfile.That is if you start your database with pfile the settings using ALTER SYSTEM can never be permanent. From database pfile can't be modified.
2)If you use ALTER SYSTEM SET command only without SCOPE then an scope is automatically appended with the command.The settings of the SCOPE is as follows.
-Scope parameter can have three values, BOTH,MEMORY,SPFILE.
-The default is SCOPE=BOTH if a server parameter file was used to start up the instance.
-The default is SCOPE=MEMORY if a text initialization parameter file was used to start up the instance.
3)If you explicitly assign SCOPE value then their persistence is as follows.
SCOPE = SPFILE
-The change is applied in the server parameter file only. The effect is as follows:
-For dynamic parameters, the change is effective at the next startup and is persistent.
-For static parameters, the behavior is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.
SCOPE = MEMORY
-The change is applied in memory only.
-For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.
-For static parameters, this specification is not allowed.
SCOPE = BOTH
-The change is applied in both the server parameter file and memory.
-For dynamic parameters, the effect is immediate and persistent.
-For static parameters, this specification is not allowed.
4)SID
The SID clause is relevant only in a RAC environment. With ALTER SYSTEM SET SID clause is specified to indicate in which database the settings will be invoked.
-Specify SID = '*' if you want Oracle Database to change the value of the parameter for all instances.
-Specify SID = 'sid_of_a_database' if you want Oracle Database to change the value of the parameter only for the instance sid.
5)For dynamic parameters, you can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions. An optional COMMENT clause lets you associate a text string with the parameter update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the server parameter file.
6)If you start your database by SPFILE then you have three option to choose, but if you start your database by PFILE then you have the only option to choose SCOPE=MEMORY which is default.
Example
To set in ALL instances in RAC of sga_taget to 160M both in memory and spfile issue,
SQL> ALTER SYSTEM SET SGA_TARGET=160M scope=both SID='*';
To set sga_target for the current instances only in memory to 160M issue,
SQL> ALTER SYSTEM SET SGA_TARGET=160M scope=memory
SID='dbase';
To set audit_file_dest for the subsequent session use,
alter system set audit_file_dest='/oradata2' DEFERRED ;
Related Link:
------------------
Know Database Initialization Parameter
1)The first thing is need to remember that whenever we issue ALTER SYSTEM command nothing to do with pfile.That is if you start your database with pfile the settings using ALTER SYSTEM can never be permanent. From database pfile can't be modified.
2)If you use ALTER SYSTEM SET command only without SCOPE then an scope is automatically appended with the command.The settings of the SCOPE is as follows.
-Scope parameter can have three values, BOTH,MEMORY,SPFILE.
-The default is SCOPE=BOTH if a server parameter file was used to start up the instance.
-The default is SCOPE=MEMORY if a text initialization parameter file was used to start up the instance.
3)If you explicitly assign SCOPE value then their persistence is as follows.
SCOPE = SPFILE
-The change is applied in the server parameter file only. The effect is as follows:
-For dynamic parameters, the change is effective at the next startup and is persistent.
-For static parameters, the behavior is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.
SCOPE = MEMORY
-The change is applied in memory only.
-For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.
-For static parameters, this specification is not allowed.
SCOPE = BOTH
-The change is applied in both the server parameter file and memory.
-For dynamic parameters, the effect is immediate and persistent.
-For static parameters, this specification is not allowed.
4)SID
The SID clause is relevant only in a RAC environment. With ALTER SYSTEM SET SID clause is specified to indicate in which database the settings will be invoked.
-Specify SID = '*' if you want Oracle Database to change the value of the parameter for all instances.
-Specify SID = 'sid_of_a_database' if you want Oracle Database to change the value of the parameter only for the instance sid.
5)For dynamic parameters, you can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions. An optional COMMENT clause lets you associate a text string with the parameter update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the server parameter file.
6)If you start your database by SPFILE then you have three option to choose, but if you start your database by PFILE then you have the only option to choose SCOPE=MEMORY which is default.
Example
To set in ALL instances in RAC of sga_taget to 160M both in memory and spfile issue,
SQL> ALTER SYSTEM SET SGA_TARGET=160M scope=both SID='*';
To set sga_target for the current instances only in memory to 160M issue,
SQL> ALTER SYSTEM SET SGA_TARGET=160M scope=memory
SID='dbase';
To set audit_file_dest for the subsequent session use,
alter system set audit_file_dest='/oradata2' DEFERRED ;
Related Link:
------------------
Know Database Initialization Parameter
Managing Archive Destination LOG_ARCHIVE_DEST, LOG_ARCHIVE_DEST_n
You can choose whether to archive redo logs to a single destination or multiplex them. The LOG_ARCHIVE_DEST, LOG_ARCHIVE_DEST_n and DB_RECOVERY_FILE_DEST parameter specify where the archived redo log files will be stored.
1)If you want to archive only to a single destination, you specify that destination in the LOG_ARCHIVE_DEST initialization parameter.
2)If you want to multiplex the archived logs, you can choose whether to archive to up to ten locations (using the LOG_ARCHIVE_DEST_n parameters)
3)If you choose to archive only two local location you can choose a primary and a secondary destination (using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST).
4)Note that, LOG_ARCHIVE_DEST is incompatible with the LOG_ARCHIVE_DEST_n parameters. So you can't set LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_n at the same time. LOG_ARCHIVE_DEST must be defined as the null string ("") or (' ') when any LOG_ARCHIVE_DEST_n parameter has a value other than a null string.
Method 1: Using the LOG_ARCHIVE_DEST Parameter:
---------------------------------------------------
Only set LOG_ARCHIVE_DEST parameter.
Method 2: Using the LOG_ARCHIVE_DEST_n Parameter
---------------------------------------------------
Use the LOG_ARCHIVE_DEST_n parameter (where n is an integer from 1 to 10) to specify from one to ten different destinations for archival. Each numerically suffixed parameter uniquely identifies an individual destination.
Format: LOG_ARCHIVE_DEST_n='{LOCATION|SERVICE} destination'
If you use the LOCATION keyword, specify a valid path name for your operating system.
If you specify SERVICE, the database translates the net service name through the tnsnames.ora file to a connect descriptor.
Like, alter system set log_archive_dest_1= 'LOCATION=/oradata1';
alter session set log_archive_dest_3='LOCATION=/oradata2';
If you set DB_RECOVERY_FILE_DEST then LOG_ARCHIVE_DEST_10 is implicitly set unless LOG_ARCHIVE_DEST_n is specified.
Method 3: Using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST
------------------------------------------------------------
To specify a maximum of two locations, use the LOG_ARCHIVE_DEST parameter to specify a primary archive destination and the LOG_ARCHIVE_DUPLEX_DEST to specify an optional secondary archive destination.
All locations must be local.
To see archival destination issue,
archive log list and
select DEST_ID, DEST_NAME,DESTINATION from v$archive_dest;
show parameter LOG_ARCHIVE_DEST;
Related Documents
Handle 16018 and 16019 Error
Change database Archive mode
1)If you want to archive only to a single destination, you specify that destination in the LOG_ARCHIVE_DEST initialization parameter.
2)If you want to multiplex the archived logs, you can choose whether to archive to up to ten locations (using the LOG_ARCHIVE_DEST_n parameters)
3)If you choose to archive only two local location you can choose a primary and a secondary destination (using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST).
4)Note that, LOG_ARCHIVE_DEST is incompatible with the LOG_ARCHIVE_DEST_n parameters. So you can't set LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_n at the same time. LOG_ARCHIVE_DEST must be defined as the null string ("") or (' ') when any LOG_ARCHIVE_DEST_n parameter has a value other than a null string.
Method 1: Using the LOG_ARCHIVE_DEST Parameter:
---------------------------------------------------
Only set LOG_ARCHIVE_DEST parameter.
Method 2: Using the LOG_ARCHIVE_DEST_n Parameter
---------------------------------------------------
Use the LOG_ARCHIVE_DEST_n parameter (where n is an integer from 1 to 10) to specify from one to ten different destinations for archival. Each numerically suffixed parameter uniquely identifies an individual destination.
Format: LOG_ARCHIVE_DEST_n='{LOCATION|SERVICE} destination'
If you use the LOCATION keyword, specify a valid path name for your operating system.
If you specify SERVICE, the database translates the net service name through the tnsnames.ora file to a connect descriptor.
Like, alter system set log_archive_dest_1= 'LOCATION=/oradata1';
alter session set log_archive_dest_3='LOCATION=/oradata2';
If you set DB_RECOVERY_FILE_DEST then LOG_ARCHIVE_DEST_10 is implicitly set unless LOG_ARCHIVE_DEST_n is specified.
Method 3: Using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST
------------------------------------------------------------
To specify a maximum of two locations, use the LOG_ARCHIVE_DEST parameter to specify a primary archive destination and the LOG_ARCHIVE_DUPLEX_DEST to specify an optional secondary archive destination.
All locations must be local.
To see archival destination issue,
archive log list and
select DEST_ID, DEST_NAME,DESTINATION from v$archive_dest;
show parameter LOG_ARCHIVE_DEST;
Related Documents
Handle 16018 and 16019 Error
Change database Archive mode
ORA-16018 And ORA-16019 LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n
These two errors come whenever LOG_ARCHIVE_DEST is set as archival location and you want to set DB_RECOVERY_FILE_DEST (ORA-16019) or whenever DB_RECOVERY_FILE_DEST is set as archival location and you want to set log_archive_dest (ORA-16018).
With an example I will discuss the whole matter.
1)Before proceed issue ARCHIVE LOG LIST to see destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217
2)You can check your current archival location by issuing, show parameter DB_RECOVERY_FILE_DEST
SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata2
db_recovery_file_dest_size big integer 30G
3)Now you want to set log_archive_dest.
SQL> alter system set log_archive_dest='/oradata1';
alter system set log_archive_dest='/oradata1'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST
4)If you want to set log_archive_dest first reset DB_RECOVERY_FILE_DEST and then set .
SQL> alter system set DB_RECOVERY_FILE_DEST='';
System altered.
SQL> alter system set log_archive_dest='/oradata1';
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata1
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217
5)Also if you now want to set DB_RECOVERY_FILE_DEST ORA-16019: will occur.
SQL> alter system set DB_RECOVERY_FILE_DEST='/oradata2';
alter system set DB_RECOVERY_FILE_DEST='/oradata2'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16019: cannot use db_recovery_file_dest with LOG_ARCHIVE_DEST or
LOG_ARCHIVE_DUPLEX_DEST
6)To set DB_RECOVERY_FILE_DEST first reset LOG_ARCHIVE_DEST.
SQL> alter system set log_archive_dest='';
System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST='/oradata2';
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217
To set multiple location of archival destination set another log_archive_dest_n parameter like,
SQL> alter system set log_archive_dest_3='LOCATION=/oradata2';
System altered.
Related Documents
Change Database Archival Mode
Managing Archive Destination
With an example I will discuss the whole matter.
1)Before proceed issue ARCHIVE LOG LIST to see destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217
2)You can check your current archival location by issuing, show parameter DB_RECOVERY_FILE_DEST
SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata2
db_recovery_file_dest_size big integer 30G
3)Now you want to set log_archive_dest.
SQL> alter system set log_archive_dest='/oradata1';
alter system set log_archive_dest='/oradata1'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST
4)If you want to set log_archive_dest first reset DB_RECOVERY_FILE_DEST and then set .
SQL> alter system set DB_RECOVERY_FILE_DEST='';
System altered.
SQL> alter system set log_archive_dest='/oradata1';
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata1
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217
5)Also if you now want to set DB_RECOVERY_FILE_DEST ORA-16019: will occur.
SQL> alter system set DB_RECOVERY_FILE_DEST='/oradata2';
alter system set DB_RECOVERY_FILE_DEST='/oradata2'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16019: cannot use db_recovery_file_dest with LOG_ARCHIVE_DEST or
LOG_ARCHIVE_DUPLEX_DEST
6)To set DB_RECOVERY_FILE_DEST first reset LOG_ARCHIVE_DEST.
SQL> alter system set log_archive_dest='';
System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST='/oradata2';
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 215
Next log sequence to archive 217
Current log sequence 217
To set multiple location of archival destination set another log_archive_dest_n parameter like,
SQL> alter system set log_archive_dest_3='LOCATION=/oradata2';
System altered.
Related Documents
Change Database Archival Mode
Managing Archive Destination
Changing the Database Archiving Mode
1)See the current archiving mode of the database.
select log_mode from v$database;
2)Perform clean shutdown of the database.
shutdown immediate or,
shutdown transactional or,
shutdown normal
You cannot change the mode from ARCHIVELOG to NOARCHIVELOG if any datafiles need media recovery.
3)Backup the Database.
4)If you use pfile as initialization file then edit the archive destination parameter (like LOG_ARCHIVE_DEST) as your archival destination. If you use spfile ignore this step.
5)Mount the database but don't open.
STARTUP MOUNT
6)Change the archival mode and open the database.
ALTER DATABASE ARCHIVELOG
If you use spfile then you can use ALTER SYSTEM SET LOG_ARCHIVE_DEST='your location'
ALTER DATABASE OPEN;
7)Check the archival Location
archive log list
8)Shutdown and Backup the database.
SHUTDOWN IMMEDIATE
Related Documents. "ORA-16018 and ORA-16019"
select log_mode from v$database;
2)Perform clean shutdown of the database.
shutdown immediate or,
shutdown transactional or,
shutdown normal
You cannot change the mode from ARCHIVELOG to NOARCHIVELOG if any datafiles need media recovery.
3)Backup the Database.
4)If you use pfile as initialization file then edit the archive destination parameter (like LOG_ARCHIVE_DEST) as your archival destination. If you use spfile ignore this step.
5)Mount the database but don't open.
STARTUP MOUNT
6)Change the archival mode and open the database.
ALTER DATABASE ARCHIVELOG
If you use spfile then you can use ALTER SYSTEM SET LOG_ARCHIVE_DEST='your location'
ALTER DATABASE OPEN;
7)Check the archival Location
archive log list
8)Shutdown and Backup the database.
SHUTDOWN IMMEDIATE
Related Documents. "ORA-16018 and ORA-16019"
Redo Log File Status
To see the status of the redo log file there are two dynamic views are there.
A)V$LOG
B)V$LOGFILE
A)V$LOG:
----------
V$LOG displays redo log file information from the control file.
Status:
--------
1)UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
2)CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.
3)ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
4)CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
5)CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
6)INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
B)V$LOGFILE:
------------------
V$LOGFILE view contains information about redo log files.
1)INVALID - The file is corrupted or missing.
2)STALE -This redo log file member is new and has never been used.
3)DELETED -The file is no longer being used.
4) -The redo log file is in use and is not corrupted.
A)V$LOG
B)V$LOGFILE
A)V$LOG:
----------
V$LOG displays redo log file information from the control file.
Status:
--------
1)UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
2)CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.
3)ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
4)CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
5)CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
6)INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
B)V$LOGFILE:
------------------
V$LOGFILE view contains information about redo log files.
1)INVALID - The file is corrupted or missing.
2)STALE -This redo log file member is new and has never been used.
3)DELETED -The file is no longer being used.
4)
Relocating and Renaming Redo Log Members
1.Shut down the database.
SQL>conn / as sysdba
SQL>SHUTDOWN IMMEDIATE;
2.Copy the redo log files to the new location. You can use OS tools or you can use DBMS_FILE_TRANSFER package.
!mv /oradata1/redo1.log /oradata2/redo1.log
3.Start database in Mount stage but don't Open it.
SQL>Startup mount
4.Rename the redo log members.
ALTER DATABASE RENAME FILE '/oradata1/redo1.log' TO '/oradata2/redo1.log'
5.Open the database.
The redo log alterations take effect when the database is opened.
SQL>ALTER DATABASE OPEN;
SQL>conn / as sysdba
SQL>SHUTDOWN IMMEDIATE;
2.Copy the redo log files to the new location. You can use OS tools or you can use DBMS_FILE_TRANSFER package.
!mv /oradata1/redo1.log /oradata2/redo1.log
3.Start database in Mount stage but don't Open it.
SQL>Startup mount
4.Rename the redo log members.
ALTER DATABASE RENAME FILE '/oradata1/redo1.log' TO '/oradata2/redo1.log'
5.Open the database.
The redo log alterations take effect when the database is opened.
SQL>ALTER DATABASE OPEN;
Controlfile in Oracle Database.
Every database has a control file which is a binary file that records the physical structure of the database. Control file is called the heart of the database.
The control file includes:
• The database name.
• Names and locations of associated datafiles and redo log files.
• The timestamp of the database creation.
• The current log sequence number.
• Checkpoint information.
• Information of the tablespaces.
• Datafile offline ranges.
• The log history.
• Archived log information.
• Database backup information taken through RMAN.
• The current log sequence number.
• Datafile copy information.
How to see the control file Contents:
--------------------------------------
Control file creation sql command/syntax can be seen by,
alter database backup controlfile to trace as '/oradata/1.txt'
After issuing the command if you open /oradata/1.txt you will see the sql script of creation controlfile.
If you don't use trace suppose you use,
alter database backup controlfile as '/oradata/1.txt' then you will get a duplicate binary copy of the current control file.
The control file includes:
• The database name.
• Names and locations of associated datafiles and redo log files.
• The timestamp of the database creation.
• The current log sequence number.
• Checkpoint information.
• Information of the tablespaces.
• Datafile offline ranges.
• The log history.
• Archived log information.
• Database backup information taken through RMAN.
• The current log sequence number.
• Datafile copy information.
How to see the control file Contents:
--------------------------------------
Control file creation sql command/syntax can be seen by,
alter database backup controlfile to trace as '/oradata/1.txt'
After issuing the command if you open /oradata/1.txt you will see the sql script of creation controlfile.
If you don't use trace suppose you use,
alter database backup controlfile as '/oradata/1.txt' then you will get a duplicate binary copy of the current control file.
ORA-29702 error occurred in Cluster Group Service operation
Error Description:
-----------------------
Previously I had RAC setup environment on my database. Now I want to install non RAC database instance.When I create my database using DBCA it fails to create database by returning ORA-29702 error Cluster Group Service operation. Though I have not selected the option to create RAC database but the error produce.
This error also occurs whenever you startup a Non-RAC database.
Reason:
---------
Though RDBMS install is not running on RAC but still it is assuming it to be RAC instance.
Solution
-------------
Relink with the RAC OFF. In my Solaris environment I just relink RAC OFF by following commands.
1)cd $ORACLE_HOME/rdbms/lib
2)/usr/ccs/bin/make -f ins_rdbms.mk rac_off
3)make -f ins_rdbms.mk ioracle
4)Try to re-create database with dbca and you will see your problem has gone.
-----------------------
Previously I had RAC setup environment on my database. Now I want to install non RAC database instance.When I create my database using DBCA it fails to create database by returning ORA-29702 error Cluster Group Service operation. Though I have not selected the option to create RAC database but the error produce.
This error also occurs whenever you startup a Non-RAC database.
Reason:
---------
Though RDBMS install is not running on RAC but still it is assuming it to be RAC instance.
Solution
-------------
Relink with the RAC OFF. In my Solaris environment I just relink RAC OFF by following commands.
1)cd $ORACLE_HOME/rdbms/lib
2)/usr/ccs/bin/make -f ins_rdbms.mk rac_off
3)make -f ins_rdbms.mk ioracle
4)Try to re-create database with dbca and you will see your problem has gone.
What is Alert Log?
Each database has a special file named alert_sid.log. The alert log of a database is a chronological log of messages and errors, and includes the following items:
1)All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur.
2)Administrative operations, such as CREATE, ALTER, and DROP statements and STARTUP, SHUTDOWN, and ARCHIVELOG statements.
3)Messages and errors relating to the functions of shared server and dispatcher processes.
4)Errors occurring during the automatic refresh of a materialized view.
5)The values of all initialization parameters that had nondefault values at the time the database and instance start.
Oracle Database uses the alert log to record these operations as an alternative to displaying the information on an operator's console.
The alert log file destination is specified by BACKGROUND_DUMP_DEST.
1)All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur.
2)Administrative operations, such as CREATE, ALTER, and DROP statements and STARTUP, SHUTDOWN, and ARCHIVELOG statements.
3)Messages and errors relating to the functions of shared server and dispatcher processes.
4)Errors occurring during the automatic refresh of a materialized view.
5)The values of all initialization parameters that had nondefault values at the time the database and instance start.
Oracle Database uses the alert log to record these operations as an alternative to displaying the information on an operator's console.
The alert log file destination is specified by BACKGROUND_DUMP_DEST.
Tuesday, April 15, 2008
What is Server-Generated Alerts?
A server-generated alert is a notification from the Oracle Database server of an impending problem.
The notification may contain suggestions for correcting the problem.
Notifications are also provided when the problem condition has been cleared.
Server-generated alerts can be based on threshold levels or can issue simply because an event has occurred.
A)Threshold-based alerts:
-----------------------------
Threshold-based alerts can be triggered at both threshold warning and critical levels.
The value of these levels can be customer-defined or internal values, and some alerts have default threshold levels which you can change if appropriate.
For example, by default a server-generated alert is generated for tablespace space usage when the percentage of space usage exceeds either the 85% warning or 97% critical threshold level.
Threshold-based alerts are,
1)Physical Reads Per Second
2)User Commits Per Second
3)SQL Service Response Time
B)Event based Alerts:
----------------------------
Event based alerts are generated because an event has occurred.Some example of event based alerts are,
1)Snapshot Too Old
2)Resumable Session Suspended
3)Recovery Area Space Usage
An alert message is sent to the predefined persistent queue ALERT_QUE owned by the user SYS.
Oracle Enterprise Manager reads this queue and provides notifications about outstanding server alerts, and sometimes suggests actions for correcting the problem.
The alerts are displayed on the Enterprise Manager console and can be configured to send email or pager notifications to selected administrators.
If an alert cannot be written to the alert queue, a message about the alert is written to the Oracle Database alert log.
How to view and change threshold settings of Threshold based Alerts:
---------------------------------------------------------------------------
1)You can view and change threshold settings for the server alert metrics using the SET_THRESHOLD and GET_THRESHOLD procedures of the DBMS_SERVER_ALERTS PL/SQL package.
2)The DBMS_AQ and DBMS_AQADM packages provide procedures for accessing and reading alert messages in the alert queue.
Setting Threshold Levels
-----------------------------
The following example shows how to set thresholds with the SET_THRESHOLD procedure for CPU time for each user call for an instance:
DBMS_SERVER_ALERT.SET_THRESHOLD(
DBMS_SERVER_ALERT.CPU_TIME_PER_CALL, DBMS_SERVER_ALERT.OPERATOR_GE, '8000',
DBMS_SERVER_ALERT.OPERATOR_GE, '10000', 1, 2, 'arju',
DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE, 'arju.arjubd.com');
In this example, a warning alert is issued when CPU time exceeds 8000 microseconds for each user call and a critical alert is issued when CPU time exceeds 10,000 microseconds for each user call.
Retrieving Threshold Information
-----------------------------------------
SELECT metrics_name, warning_value, critical_value, consecutive_occurrences
FROM DBA_THRESHOLDS
WHERE metrics_name LIKE '%CPU Time%';
Related Documents
Stop Alert Notification for a specified period of time
The notification may contain suggestions for correcting the problem.
Notifications are also provided when the problem condition has been cleared.
Server-generated alerts can be based on threshold levels or can issue simply because an event has occurred.
A)Threshold-based alerts:
-----------------------------
Threshold-based alerts can be triggered at both threshold warning and critical levels.
The value of these levels can be customer-defined or internal values, and some alerts have default threshold levels which you can change if appropriate.
For example, by default a server-generated alert is generated for tablespace space usage when the percentage of space usage exceeds either the 85% warning or 97% critical threshold level.
Threshold-based alerts are,
1)Physical Reads Per Second
2)User Commits Per Second
3)SQL Service Response Time
B)Event based Alerts:
----------------------------
Event based alerts are generated because an event has occurred.Some example of event based alerts are,
1)Snapshot Too Old
2)Resumable Session Suspended
3)Recovery Area Space Usage
An alert message is sent to the predefined persistent queue ALERT_QUE owned by the user SYS.
Oracle Enterprise Manager reads this queue and provides notifications about outstanding server alerts, and sometimes suggests actions for correcting the problem.
The alerts are displayed on the Enterprise Manager console and can be configured to send email or pager notifications to selected administrators.
If an alert cannot be written to the alert queue, a message about the alert is written to the Oracle Database alert log.
How to view and change threshold settings of Threshold based Alerts:
---------------------------------------------------------------------------
1)You can view and change threshold settings for the server alert metrics using the SET_THRESHOLD and GET_THRESHOLD procedures of the DBMS_SERVER_ALERTS PL/SQL package.
2)The DBMS_AQ and DBMS_AQADM packages provide procedures for accessing and reading alert messages in the alert queue.
Setting Threshold Levels
-----------------------------
The following example shows how to set thresholds with the SET_THRESHOLD procedure for CPU time for each user call for an instance:
DBMS_SERVER_ALERT.SET_THRESHOLD(
DBMS_SERVER_ALERT.CPU_TIME_PER_CALL, DBMS_SERVER_ALERT.OPERATOR_GE, '8000',
DBMS_SERVER_ALERT.OPERATOR_GE, '10000', 1, 2, 'arju',
DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE, 'arju.arjubd.com');
In this example, a warning alert is issued when CPU time exceeds 8000 microseconds for each user call and a critical alert is issued when CPU time exceeds 10,000 microseconds for each user call.
Retrieving Threshold Information
-----------------------------------------
SELECT metrics_name, warning_value, critical_value, consecutive_occurrences
FROM DBA_THRESHOLDS
WHERE metrics_name LIKE '%CPU Time%';
Related Documents
Stop Alert Notification for a specified period of time
Shutdown Modes in Oracle
To shut down a database and instance, you must first connect as SYSOPER or SYSDBA. There are several modes for shutting down a database.
1)Shutdown NORMAL / Shutdown
-The NORMAL clause is optional.This is the default shutdown method if no clause is provided.
-No new connections are allowed after the statement is issued.
-Before the database is shut down, the database waits for all currently connected users to disconnect from the database.Until they press exit database waits.
2)SHUTDOWN IMMEDIATE
-No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
-Any uncommitted transactions are rolled back.
-The database implicitly rolls back active transactions and disconnects all connected users.
3)SHUTDOWN TRANSACTIONAL
-No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
-After all transactions have completed, any client still connected to the instance is disconnected.
4)SHUTDOWN ABORT
-No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
-Current client SQL statements being processed by Oracle Database are immediately terminated.
-Uncommitted transactions are not rolled back.
-Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users.
-The next startup of the database will require instance recovery procedures.
5)Shutdown Transactional LOCAL
The LOCAL mode specifies a transactional shutdown on the local instance only, so that it only waits on local transactions to complete, not all transactions.In RAC environment it is useful.
Shutdown Timeout
When we issue SHUTDOWN NORMAL database waits for users to disconnect or SHUTDOWN TRANSACTIONAL for transactions to complete have a limit on the amount of time that they wait. If all events blocking the shutdown do not occur within one hour, the shutdown command cancels with the following message:
ORA-01013: user requested cancel of current operation.
1)Shutdown NORMAL / Shutdown
-The NORMAL clause is optional.This is the default shutdown method if no clause is provided.
-No new connections are allowed after the statement is issued.
-Before the database is shut down, the database waits for all currently connected users to disconnect from the database.Until they press exit database waits.
2)SHUTDOWN IMMEDIATE
-No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
-Any uncommitted transactions are rolled back.
-The database implicitly rolls back active transactions and disconnects all connected users.
3)SHUTDOWN TRANSACTIONAL
-No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
-After all transactions have completed, any client still connected to the instance is disconnected.
4)SHUTDOWN ABORT
-No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
-Current client SQL statements being processed by Oracle Database are immediately terminated.
-Uncommitted transactions are not rolled back.
-Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users.
-The next startup of the database will require instance recovery procedures.
5)Shutdown Transactional LOCAL
The LOCAL mode specifies a transactional shutdown on the local instance only, so that it only waits on local transactions to complete, not all transactions.In RAC environment it is useful.
Shutdown Timeout
When we issue SHUTDOWN NORMAL database waits for users to disconnect or SHUTDOWN TRANSACTIONAL for transactions to complete have a limit on the amount of time that they wait. If all events blocking the shutdown do not occur within one hour, the shutdown command cancels with the following message:
ORA-01013: user requested cancel of current operation.
Options for Shuting Down a Database
1)Shutdown a Database Using SQL*Plus
-----------------------------------------------
$sqlplus /nolog
SQL>conn / as sysdba
SQL>stutdown immediate
2)Shutdown a Database Using Recovery Manager
---------------------------------------------------
1)oracle@neptune:~$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 15 18:08:04 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
2)RMAN> shutdown immediate
3)Shutdown Database Using Oracle Enterprise Manager
---------------------------------------------------------------
logon to Enterprise Manager.
Under Home there is general tab. select shutdown
Enter host and database credentials.
The default is shutdown immediate. From advance options you can choose another one.
Press yes.
-----------------------------------------------
$sqlplus /nolog
SQL>conn / as sysdba
SQL>stutdown immediate
2)Shutdown a Database Using Recovery Manager
---------------------------------------------------
1)oracle@neptune:~$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 15 18:08:04 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
2)RMAN> shutdown immediate
3)Shutdown Database Using Oracle Enterprise Manager
---------------------------------------------------------------
logon to Enterprise Manager.
Under Home there is general tab. select shutdown
Enter host and database credentials.
The default is shutdown immediate. From advance options you can choose another one.
Press yes.
Different ways of Starting Up a Database
Options for Starting Up a Database
-----------------------------------
1)Starting Up a Database Using SQL*Plus
-----------------------------------------------
$sqlplus /nolog
SQL>conn / as sysdba
SQL>startup
2)Starting Up a Database Using Recovery Manager
---------------------------------------------------
1)oracle@neptune:~$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 15 18:08:04 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
2)RMAN> startup
Oracle instance started
database mounted
database opened
3)Starting Up a Database Using Oracle Enterprise Manager
---------------------------------------------------------------
1)emctl start dbconsole
2)paste the url in to the browser.
3)Enter host credential and database credential as sysdba.
4)Press yes.
-----------------------------------
1)Starting Up a Database Using SQL*Plus
-----------------------------------------------
$sqlplus /nolog
SQL>conn / as sysdba
SQL>startup
2)Starting Up a Database Using Recovery Manager
---------------------------------------------------
1)oracle@neptune:~$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Apr 15 18:08:04 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
2)RMAN> startup
Oracle instance started
database mounted
database opened
3)Starting Up a Database Using Oracle Enterprise Manager
---------------------------------------------------------------
1)emctl start dbconsole
2)paste the url in to the browser.
3)Enter host credential and database credential as sysdba.
4)Press yes.
How to find current session ID
Way1:
------
select sid from v$session where audsid = sys_context('userenv','sessionid');
Way2:
------
select distinct sid from v$mystat;
Way3:
--------
If DBMS_SUPPORT is installed,
select dbms_support.mysid from dual;
Related Documents
Get IP Address from hostname within Oracle Database
How to find the User who is connected to Oracle
How to know which objects are being accessed by a user
------
select sid from v$session where audsid = sys_context('userenv','sessionid');
Way2:
------
select distinct sid from v$mystat;
Way3:
--------
If DBMS_SUPPORT is installed,
select dbms_support.mysid from dual;
Related Documents
Get IP Address from hostname within Oracle Database
How to find the User who is connected to Oracle
How to know which objects are being accessed by a user
How to Remove a File From PL/SQL
Way1:
----------
begin
dbms_backup_restore.deletefile('/oracle/a.txt');
end;
But don't use dbms_backup_restore.deletefile. It does not guarantee about deletion of the file. Whether the operation completed or not it always return PL/SQL procedure successfully completed.
Way2: Using Utl_file Package
------------------------------
1)Create directory where file resides.
SQL> !touch /export/home/oracle/Arju/1.txt
SQL> create directory ddel as '/export/home/oracle/Arju';
Directory created.
2)Grant permission to the user who will do the operation.
SQL> grant read,write on directory ddel to public;
Grant succeeded.
3)Execute the procedure.
SQL> exec utl_file.fremove('DDEL','1.txt');
PL/SQL procedure successfully completed.
SQL> !ls /export/home/oracle/Arju
----------
begin
dbms_backup_restore.deletefile('/oracle/a.txt');
end;
But don't use dbms_backup_restore.deletefile. It does not guarantee about deletion of the file. Whether the operation completed or not it always return PL/SQL procedure successfully completed.
Way2: Using Utl_file Package
------------------------------
1)Create directory where file resides.
SQL> !touch /export/home/oracle/Arju/1.txt
SQL> create directory ddel as '/export/home/oracle/Arju';
Directory created.
2)Grant permission to the user who will do the operation.
SQL> grant read,write on directory ddel to public;
Grant succeeded.
3)Execute the procedure.
SQL> exec utl_file.fremove('DDEL','1.txt');
PL/SQL procedure successfully completed.
SQL> !ls /export/home/oracle/Arju
How to See and Change Database Default Tablespace.
Have you seen that after we create a database manually (by default option) whenever we create a user the default tablespace is assigned automatically to SYSTEM tablespace.
But after we create a database by dbca whenever we create a user the default tablespace is assigned automatically to USERS tablespace.
Why they differs between these two. It is because of the settings of database default tablespace. Whenever we create a database manually (by default option) database default tablespace is assinged to SYSTEM tablespace.
But whenever we create a database by dbca database default tablespace is assigned to USERS tablespace.
We can see the default tablespace of the database by querying database_properties object.
Like,
SQL> select PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%TABLESPACE%';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE USERS
Now we can easily change the database default tablespace settings by,
alter database default tablespace
After setting new default tablespace any newly created user will be automatically assigned to new default tablespace.
Workaround Example:
------------------------
SQL> create user test1 identified by t;
User created.
SQL> select default_tablespace from dba_users where username='TEST1';
DEFAULT_TABLESPACE
------------------------------
USERS
SQL> alter database default tablespace DATA01;
Database altered.
SQL> create user test2 identified by t;
User created.
SQL> select default_tablespace from dba_users where username='TEST2';
DEFAULT_TABLESPACE
------------------------------
DATA01
But after we create a database by dbca whenever we create a user the default tablespace is assigned automatically to USERS tablespace.
Why they differs between these two. It is because of the settings of database default tablespace. Whenever we create a database manually (by default option) database default tablespace is assinged to SYSTEM tablespace.
But whenever we create a database by dbca database default tablespace is assigned to USERS tablespace.
We can see the default tablespace of the database by querying database_properties object.
Like,
SQL> select PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%TABLESPACE%';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE USERS
Now we can easily change the database default tablespace settings by,
alter database default tablespace
After setting new default tablespace any newly created user will be automatically assigned to new default tablespace.
Workaround Example:
------------------------
SQL> create user test1 identified by t;
User created.
SQL> select default_tablespace from dba_users where username='TEST1';
DEFAULT_TABLESPACE
------------------------------
USERS
SQL> alter database default tablespace DATA01;
Database altered.
SQL> create user test2 identified by t;
User created.
SQL> select default_tablespace from dba_users where username='TEST2';
DEFAULT_TABLESPACE
------------------------------
DATA01
Monday, April 14, 2008
An Stack of Problems while creating Repository using emca
In my database I tried to create repository and stopped several times while creating repository. I spend a significant amount of time to create repository and after analysis I got the following.
A)Manually Resolve
1)If you fail after invoking command
emca -config dbcontrol db -repos create see alert log file for more details. Also refer to another log file if it shows. Directly go to step 3)
2)However I see many errors like,
CONFIG: ORA-00955: name is already used by an existing object
CONFIG: ORA-01921: role name 'MGMT_USER' conflicts with another user or role name
and many severals errors.
3)Execute the following commands in SQL*plus and then execute emca command. I wish no more error will come. After several experiment I get this.
drop user sysman cascade;
drop public synonym SETEMVIEWUSERCONTEXT;
drop role MGMT_USER;
drop PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS;
drop user MGMT_VIEW;
And then,
$ emca -deconfig dbcontrol db
$ emca -config dbcontrol db -repos create
B)Using RepManager:
If I drop the Repository using RepManager like following error will not come while creating repository.
$ORACLE_HOME/sysman/admin/emdrep/bin/RepManager -action drop
This will not remove the configuration files, but only the repository related objects from the database.
But you need to remember when RepManager is ran the database will be put in Quiescing mode.
A)Manually Resolve
1)If you fail after invoking command
emca -config dbcontrol db -repos create see alert log file for more details. Also refer to another log file if it shows. Directly go to step 3)
2)However I see many errors like,
CONFIG: ORA-00955: name is already used by an existing object
CONFIG: ORA-01921: role name 'MGMT_USER' conflicts with another user or role name
and many severals errors.
3)Execute the following commands in SQL*plus and then execute emca command. I wish no more error will come. After several experiment I get this.
drop user sysman cascade;
drop public synonym SETEMVIEWUSERCONTEXT;
drop role MGMT_USER;
drop PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS;
drop user MGMT_VIEW;
And then,
$ emca -deconfig dbcontrol db
$ emca -config dbcontrol db -repos create
B)Using RepManager:
If I drop the Repository using RepManager like following error will not come while creating repository.
$ORACLE_HOME/sysman/admin/emdrep/bin/RepManager
This will not remove the configuration files, but only the repository related objects from the database.
But you need to remember when RepManager is ran the database will be put in Quiescing mode.
Change NLS_DATE_FORMAT Parmanently
The parameter NLS_DATE_FORMAT specifies the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY.
This parameter is modifiable and can be set through ALTER SESSION parameter. If you start your database with spfile then you can set this parameter by ALTER SYSTEM SET command.
So the setting of this parameter by ALTER SESSION is transient. If we want to make it permanent then then following steps should be performed.
1)Create pfile='1.txt' from spfile;
2)Edit pfile 1.txt and entry NLS_DATE_FORMAT as you wish the format.
3.start the database with pfile and create spfile from pfile.
4)Shutdown and Start the database.
5)Now query. Date will be shown as of format specified in NLS_DATE_FORMAT.
Example:
----------
1)SQL> create pfile='/oradata/1.txt' from spfile;
On unix system,
2)SQL> !vi /oradata/1.txt and entry,
*.nls_date_format='DD-MM-YY'
On windows just open pfile with any editor software like notepad and give an entry of nls_date_format.
3)SQL> startup pfile='/oradata/1.txt'
4)SQL> select sysdate from dual;
SYSDATE
--------
15-04-08
5)SQL> create spfile from pfile='/oradata/1.txt';
6)SQL> shutdown imemdiate;
7)SQL> startup
8)SQL> select sysdate from dual;
SYSDATE
--------
15-04-08
This parameter is modifiable and can be set through ALTER SESSION parameter. If you start your database with spfile then you can set this parameter by ALTER SYSTEM SET command.
So the setting of this parameter by ALTER SESSION is transient. If we want to make it permanent then then following steps should be performed.
1)Create pfile='1.txt' from spfile;
2)Edit pfile 1.txt and entry NLS_DATE_FORMAT as you wish the format.
3.start the database with pfile and create spfile from pfile.
4)Shutdown and Start the database.
5)Now query. Date will be shown as of format specified in NLS_DATE_FORMAT.
Example:
----------
1)SQL> create pfile='/oradata/1.txt' from spfile;
On unix system,
2)SQL> !vi /oradata/1.txt and entry,
*.nls_date_format='DD-MM-YY'
On windows just open pfile with any editor software like notepad and give an entry of nls_date_format.
3)SQL> startup pfile='/oradata/1.txt'
4)SQL> select sysdate from dual;
SYSDATE
--------
15-04-08
5)SQL> create spfile from pfile='/oradata/1.txt';
6)SQL> shutdown imemdiate;
7)SQL> startup
8)SQL> select sysdate from dual;
SYSDATE
--------
15-04-08
Running EMCA Fails To Accept the DBSNMP Password
Today when I tried to create DB Control on my computer I got an interesting problem.
It continues to invoke password until I press ctrl+C.
Though I was giving correct password it does not accept DBSNMP password. I got tried, I search through google but did not get any clue. Even to be more sure I have changed DBNMP password though database and then tried but no result , the problem continues.
Then what I did is I have given password inside "" Double quotes and that worked. :)
Problem Description:
oracle@neptune:/$ emca -config dbcontrol db -repos recreate
STARTED EMCA at Apr 15, 2008 11:05:06 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: ARJU
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user: ------Here I have given a
Invalid username/password.
Password for DBSNMP user:
Invalid username/password.
It continues to invoke password until I press ctrl+C.
Though I was giving correct password it does not accept DBSNMP password. I got tried, I search through google but did not get any clue. Even to be more sure I have changed DBNMP password though database and then tried but no result , the problem continues.
Then what I did is I have given password inside "" Double quotes and that worked. :)
oracle@neptune:/$ emca -config dbcontrol db -repos recreate
STARTED EMCA at Apr 15, 2008 11:10:29 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: arju
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user: ----Here I have given "A" or "a" Both worked.
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
.
.
.
How to Access Database using Database Control
We all know we can access database using SQL*plus or any other third party software like tora toad etc. Using Oracle Enterprise Manager we can also do the same task.
Oracle provides a web-based version of the SQL*Plus tool called iSQL*Plus.iSQL*Plus has a server-side listener process that must be started in order for clients to connect to a database through the browser interface.
In order to access database using iSQL*Plus we need to go through following steps.
1)Invoke lsnrctl status and ensure that listener service is running.
2)Invoke emctl status and ensure that Ddatabase Control agent is running. If not running then invoke emctl start dbconsole.
3)See the url after invoking emctl status.
4)Start the server-side listener process of iSQL*Plus.
isqlplusctl start
5)Enter the Enterprise Manager and use iSQL*Plus.
In general you can invoke the url found after invoking emctl status dbconsole
Or, http://machine_name.domain_name:port/isqlplus
The default port number is 5560.
6)Now eneter user name and password, a box will come where you can invoke regular SQL commands.
Oracle provides a web-based version of the SQL*Plus tool called iSQL*Plus.iSQL*Plus has a server-side listener process that must be started in order for clients to connect to a database through the browser interface.
In order to access database using iSQL*Plus we need to go through following steps.
1)Invoke lsnrctl status and ensure that listener service is running.
2)Invoke emctl status and ensure that Ddatabase Control agent is running. If not running then invoke emctl start dbconsole.
3)See the url after invoking emctl status.
4)Start the server-side listener process of iSQL*Plus.
isqlplusctl start
5)Enter the Enterprise Manager and use iSQL*Plus.
In general you can invoke the url found after invoking emctl status dbconsole
Or, http://machine_name.domain_name:port/isqlplus
The default port number is 5560.
6)Now eneter user name and password, a box will come where you can invoke regular SQL commands.
Sunday, April 13, 2008
About Oracle Certifications of 10g
With Oracle database administration Certification track there are three tires.
1)OCA: The first tier is the Oracle 10g Certified Associate (OCA). To obtain OCA certification, you
must pass the 1Z0-042 exam.
2)OCP: The second tier is the Oracle 10g Certified Professional (OCP), which builds on and
requires OCA certification. To obtain OCP certification, you must attend an approved
Oracle University hands-on class and pass the 1Z0-043 exam.
3)OCM: The third and highest tier is the Oracle 10g Certified Master (OCM), which builds on and requires OCP certification.
1)OCA: The first tier is the Oracle 10g Certified Associate (OCA). To obtain OCA certification, you
must pass the 1Z0-042 exam.
2)OCP: The second tier is the Oracle 10g Certified Professional (OCP), which builds on and
requires OCA certification. To obtain OCP certification, you must attend an approved
Oracle University hands-on class and pass the 1Z0-043 exam.
3)OCM: The third and highest tier is the Oracle 10g Certified Master (OCM), which builds on and requires OCP certification.
List of Oracle Database Version Release.
From Oracle1 to Oracle6 there was no release numbering.
Since version 7, Oracle's RDBMS release numbering has used the following codes:
Oracle7: 7.0.16 — 7.3.4
Oracle8 Database: 8.0.3 — 8.0.6
Oracle8i Database Release 1: 8.1.5.0 — 8.1.5.1
Oracle8i Database Release 2: 8.1.6.0 — 8.1.6.3
Oracle8i Database Release 3: 8.1.7.0 — 8.1.7.4
Oracle9i Database Release 1: 9.0.1.0 — 9.0.1.5
Oracle9i Database Release 2: 9.2.0.1 — 9.2.0.8
Oracle Database 10g Release 1: 10.1.0.2 — 10.1.0.5
Oracle Database 10g Release 2: 10.2.0.1 — 10.2.0.4
Oracle Database 11g Release 1: 11.1.0.6
Since version 7, Oracle's RDBMS release numbering has used the following codes:
Oracle7: 7.0.16 — 7.3.4
Oracle8 Database: 8.0.3 — 8.0.6
Oracle8i Database Release 1: 8.1.5.0 — 8.1.5.1
Oracle8i Database Release 2: 8.1.6.0 — 8.1.6.3
Oracle8i Database Release 3: 8.1.7.0 — 8.1.7.4
Oracle9i Database Release 1: 9.0.1.0 — 9.0.1.5
Oracle9i Database Release 2: 9.2.0.1 — 9.2.0.8
Oracle Database 10g Release 1: 10.1.0.2 — 10.1.0.5
Oracle Database 10g Release 2: 10.2.0.1 — 10.2.0.4
Oracle Database 11g Release 1: 11.1.0.6
Oracle Database Editions
Oracle Corporation subdivides its product into varying "editions" - apparently for marketing and license-tracking reasons. In approximate order of decreasing scale, the editions are listed below.
1)Enterprise Edition (EE): Includes more features than the 'Standard Edition', especially in the areas of performance and security. Oracle Corporation licenses this product on the basis of users or of processors, typically for servers running 4 or more CPUs. EE has no memory limits, and can utilize clustering using Oracle RAC software.
2)Standard Edition (SE): Contains base database functionality. Oracle Corporation licenses this product on the basis of users or of processors, typically for servers running from one to four CPUs. If the number of CPUs exceeds 4 CPUs, the user must convert to an Enterprise license. SE has no memory limits, and can utilize clustering with Oracle RAC at no additional charge.
3)Standard Edition One: Introduced with Oracle 10g, has some additional feature-restrictions. Oracle Corporation markets it for use on systems with one or two CPUs. It has no memory limitations.
4)Express Edition: ('Oracle Database XE'), introduced in 2005, offers Oracle 10g free to distribute on Windows and Linux platforms (with a footprint of only 150 MB and restricted to the use of a single CPU, a maximum of 4 GB of user data and 1 GB of memory). Support for this version comes exclusively through on-line forums and not through Oracle support.
5)Oracle Personal Edition: Provides the functionality of the "high end" Enterprise Edition but marketed to (and licensed for) single-user developers working on personal workstations.
6)Oracle Database Lite: Intended to run on mobile devices. The database, partially located on the mobile device, can synchronize with a server-based installation.
1)Enterprise Edition (EE): Includes more features than the 'Standard Edition', especially in the areas of performance and security. Oracle Corporation licenses this product on the basis of users or of processors, typically for servers running 4 or more CPUs. EE has no memory limits, and can utilize clustering using Oracle RAC software.
2)Standard Edition (SE): Contains base database functionality. Oracle Corporation licenses this product on the basis of users or of processors, typically for servers running from one to four CPUs. If the number of CPUs exceeds 4 CPUs, the user must convert to an Enterprise license. SE has no memory limits, and can utilize clustering with Oracle RAC at no additional charge.
3)Standard Edition One: Introduced with Oracle 10g, has some additional feature-restrictions. Oracle Corporation markets it for use on systems with one or two CPUs. It has no memory limitations.
4)Express Edition: ('Oracle Database XE'), introduced in 2005, offers Oracle 10g free to distribute on Windows and Linux platforms (with a footprint of only 150 MB and restricted to the use of a single CPU, a maximum of 4 GB of user data and 1 GB of memory). Support for this version comes exclusively through on-line forums and not through Oracle support.
5)Oracle Personal Edition: Provides the functionality of the "high end" Enterprise Edition but marketed to (and licensed for) single-user developers working on personal workstations.
6)Oracle Database Lite: Intended to run on mobile devices. The database, partially located on the mobile device, can synchronize with a server-based installation.
Oracle Services
In addition to software development, Oracle also offers a variety of technical support and consulting services.
Technical support is delivered primarily through Oracle’s MetaLink website and is available to all customers with current maintenance agreements.
In addition to this support, Oracle Services also offers consulting services to help customers select, install, and configure the Oracle technologies that best meet their needs.
List of Services:
Oracle Consulting
Oracle University
Oracle On Demand
Oracle Support
Oracle Financing
Technical support is delivered primarily through Oracle’s MetaLink website and is available to all customers with current maintenance agreements.
In addition to this support, Oracle Services also offers consulting services to help customers select, install, and configure the Oracle technologies that best meet their needs.
List of Services:
Oracle Consulting
Oracle University
Oracle On Demand
Oracle Support
Oracle Financing
Oracle Products
A)Technology products:
1)Oracle Databases:
The relational database management system (RDBMS) officially called Oracle Database.
2)Oracle Fusion Middleware:Oracle Fusion Middleware (OFM) is a portfolio of software products, produced by Oracle, that spans multiple services, including J2EE and developer tools, integration services, business intelligence, collaboration, and content management.
3)Oracle Enterprise Manager:Oracle Enterprise Manager (OEM) used by database administrators (DBAs) to manage the DBMS, and recently in version 10g, a web-based rewrite of OEM called "Oracle Enterprise Manager Database Control".
4)Oracle Secure Enterprise Search:
B)Application products:
1)Oracle eBusiness Suite:Oracle Corporation's E-Business Suite ("EB-Suite" or "EBS") consists of a collection of enterprise resource planning (ERP), customer relationship management (CRM), and supply-chain management (SCM) computer applications developed by Oracle.
2)PeopleSoft Enterprise:PeopleSoft, Inc. was a company that provided Human resource management systems (HRMS), customer relationship management, Manufacturing, Financials, Enterprise Performance Management, and Student Administration software solutions to large corporations, governments, and organizations.
3)Siebel:Siebel Systems, Inc. began in sales force automation software, then expanded into marketing and customer service applications, including customer relationship management (CRM).
4)JD Edwards EnterpriseOne:
5)JD Edwards World:
1)Oracle Databases:
The relational database management system (RDBMS) officially called Oracle Database.
2)Oracle Fusion Middleware:Oracle Fusion Middleware (OFM) is a portfolio of software products, produced by Oracle, that spans multiple services, including J2EE and developer tools, integration services, business intelligence, collaboration, and content management.
3)Oracle Enterprise Manager:Oracle Enterprise Manager (OEM) used by database administrators (DBAs) to manage the DBMS, and recently in version 10g, a web-based rewrite of OEM called "Oracle Enterprise Manager Database Control".
4)Oracle Secure Enterprise Search:
B)Application products:
1)Oracle eBusiness Suite:Oracle Corporation's E-Business Suite ("EB-Suite" or "EBS") consists of a collection of enterprise resource planning (ERP), customer relationship management (CRM), and supply-chain management (SCM) computer applications developed by Oracle.
2)PeopleSoft Enterprise:PeopleSoft, Inc. was a company that provided Human resource management systems (HRMS), customer relationship management, Manufacturing, Financials, Enterprise Performance Management, and Student Administration software solutions to large corporations, governments, and organizations.
3)Siebel:Siebel Systems, Inc. began in sales force automation software, then expanded into marketing and customer service applications, including customer relationship management (CRM).
4)JD Edwards EnterpriseOne:
5)JD Edwards World:
Related Documents
Subscribe to:
Posts (Atom)
Tag Cloud
10.2g
10g
11g
11gR2
Abasa
About Oracle
Administration
Adsense
Alerts
Archival
ASM
ASP.Net
Audit
Audit Vault
Backup
Bangladesh
Block Corruption
Blogger
Browser
Bug
Business
Clone
Clusterware
Comments
Concepts
Connection
Controlfiles
Crime
CSS
Data Block
Data Dictionary
Data Guard
Data Mining
Data Pump
Data Type
Database Administration
Database Vault
DBConsole
Developer
Economics
EM
Excel
Exercise
Explain plan
Export
External Table
Facebook
Firefox
Firmware
Flashback
Forum
Functions
Games
Globalization Support
Grid Control
Hardware
History
HTML
IE
Import
Indexes
initializaion parameter
initialization parameter
Installation
Internals
Internet
Interview
isql*plus
Java
JavaScript
Job
Joins
Joke
Limitation
Linux
Listener
Logminer
Magento
Mail
Materialized View
Medical
Memory
Mobile
Money
Multimedia
MySQL
Net Services
Network
OCP
Operators
Oracle
Oracle Concepts
Oracle Recovery
OS
Others
OUI
Package
Packages
Parameters
Partitioning
Patchset
Performance
Perl
Pfile
Photos
PHP
PL/SQL
Profile
Pseudocolumns
Puzzle
Quiz
Quota
RAC
RAC Installation
Recovery
Recovery Problems
Redo Log
Reports
RMAN
Scripts
Security
SEO
Server Administration
SGA
Shell Script
Smarty
Social Marketing
Solaris
Spfile
SQL
SQL Tuning
SQL*Loader
Sql*Plus
Startup Problem
Streams
SwingBench
System Analysis
Tablespaces
Technology
Temp
TNS Error
Tools
Troubleshooting
Tuning
Undo
UNIX
Upgradation
Utilities
Version
Views
Vmware
Windows
Wordpress
XML