RENAME ASM DISK GROUP:
11gR2 introduced renamedg utility to rename diskgroups. Before you use the command, ensure that ASM diskgroup is dismounted.
Below is syntax for the command.
renamedg
[phase={ one|two |both } ] dgname=diskgroup
newdgname=newdiskgroup [config=configfile]
[ asm_diskstring=discoverystring, discoverystring ... ]
[ clean={true|false} ] [ check={true|false} ]
[ confirm={true|false}] [ verbose={ true|false} ]
[ keep_voting_files={true|false}]
Basically we have two phases of using renamedg command,
phase 1 à generates a configuration file to be used by phase 2.
In phase 2 à actual renaming of diskgroup is done.
Example:
renamedg dgname=data newdgname=dg asm_diskstring='/dev/oracleasm/disks/VOL*' verbose=true
CASE sensitive passwords:
In Oracle 11g passwords are case sensitive by default. We can enable or disable this feature by setting the SEC_CASE_SENSITIVE_LOGON parameter.
The SEC_CASE_SENSITIVE_LOGON parameter is a dynamic parameter. In the earlier version of the oracle like 10g & 9i passwords were not case sensitive
SYS@orcl11g> show parameter SEC_CASE_SENSITIVE_LOGON
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
sec_case_sensitive_logon boolean TRUE
Creating spfile/pfile from memory
Now we can even create a spfile/pfile from memory. It is a new feature in the 11g. Prior to 11g, we were able to create spfile from pfile or pfile from spfile.
Syntax:
Syntax:
CREATE SPFILE FROM MEMORY;
CREATE PFILE FROM MEMORY;
CREATE PFILE FROM MEMORY;
Examples:
SYS@orcl11g>CREATE SPFILE='D:\backup\ORCL11G\spfile_bkp_18Jan2010.ora' FROM MEMORY;
File created.
SYS@orcl11g>CREATE PFILE='D:\backup\ORCL11G\pfile_bkp_18Jan2010.ora' FROM MEMORY;
File created.
File created.
SYS@orcl11g>CREATE PFILE='D:\backup\ORCL11G\pfile_bkp_18Jan2010.ora' FROM MEMORY;
File created.
Read-Only Table :
In Oracle 11g we can make a table read-only. It is a new feature in the Oracle 11g. Earlier we were able to make a non-system tablespace read-only. The Oracle 11g goes one step further and allows us to make a table read-only.
We have a new column called 'read_only' in dba_tables, user_tables and all_tables views which tells whether a table is read only or not.
We will not be able to execute DML statements like INSERT, UPDATE and DELETE on a read-only table. And all so we cannot add/remove columns.
Syntax:
We have a new column called 'read_only' in dba_tables, user_tables and all_tables views which tells whether a table is read only or not.
We will not be able to execute DML statements like INSERT, UPDATE and DELETE on a read-only table. And all so we cannot add/remove columns.
Syntax:
ALTER TABLE table_name READ ONLY;
ALTER TABLE table_name READ WRITE;
Examples:
SCOTT@orcl11g>DROP TABLE test_tbl;
Table dropped.
SCOTT@orcl11g>CREATE TABLE test_tbl AS SELECT * FROM dual;
Table created.
SCOTT@orcl11g>SELECT * FROM test_tbl;
D
-
X
SCOTT@orcl11g>INSERT INTO test_tbl VALUES ('A');
1 row created.
SCOTT@orcl11g>ALTER TABLE test_tbl READ ONLY;
Table altered.
SCOTT@orcl11g>SELECT table_name,read_only FROM user_tables WHERE table_name='TEST_TBL';
TABLE_NAME READ_ONLY
------------------------------ ----------
TEST_TBL YES
SCOTT@orcl11g>INSERT INTO test_tbl VALUES ('A');
INSERT INTO test_tbl values ('A')
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"
SCOTT@orcl11g>UPDATE test_tbl SET dummy='Y' WHERE dummy='X';
UPDATE test_tbl SET dummy='Y' WHERE dummy='X'
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"
SCOTT@orcl11g>DELETE FROM test_tbl;
DELETE FROM test_tbl
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"
SCOTT@orcl11g>ALTER TABLE test_tbl ADD (name varchar2(10));
ALTER TABLE test_tbl ADD (name varchar2(10))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"
SCOTT@orcl11g>ALTER TABLE test_tbl DROP COLUMN name;
ALTER TABLE test_tbl DROP COLUMN name
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TEST_TBL"
SCOTT@orcl11g>ALTER TABLE test_tbl READ WRITE;
Table altered.
SCOTT@orcl11g>INSERT INTO test_tbl VALUES('A');
1 row created.
SCOTT@orcl11g>
Invisible index :
Invisible index is a new feature of Oracle 11g. If we create an invisible index Oracle optimizer will not use it. This can be used to test a query without effecting the application. We can also make an index invisible/visible by using ALTER INDEX index_name [visible/invisible].
Syntax:
Syntax:
CREATE INDEX index_name ON table_name(column_name) INVISIBLE;
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;
Examples:
SYSTEM@orcl11g>show parameter optimizer_use_invisible_indexes
NAME TYPE VALUE
------------------------------------ ----------- --------------------
optimizer_use_invisible_indexes boolean FALSE
SYSTEM@orcl11g> desc test
Name Null? Type
----------------------------------------------------- -------- ------------
N NUMBER
NAME VARCHAR2(10)
SYSTEM@orcl11g>create index idx1 on test(n) invisible;
Index created.
SYSTEM@orcl11g>select owner,index_name,visibility from dba_indexes where visibility = 'INVISIBLE';
OWNER INDEX_NAME VISIBILIT
--------------- --------------------- ---------
SYSTEM IDX1 INVISIBLE
SYSTEM@orcl11g> select * from test where n=5;
N NAME
------ ----------
5 H
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 20 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
SYSTEM@orcl11g>alter index idx1 visible;
Index altered.
SYSTEM@orcl11g> select * from test where n=5;
N NAME
---------- ----------
5 H
Execution Plan
----------------------------------------------------------
Plan hash value: 1175071277
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Use 1: Adding an index
You think adding an index will improve a query but you are not sure. Just create an invisible index. Test the query and decide.
Use 2: Dropping an index
You want to drop an index but don’t know how it will affect the application. Make that index invisible and analyze the performance. If it is not affecting the performance of the application, you can drop it.
crs_stat has been deprecated in 11gR2
While reading whats new and whats deprecated in Oracle 11gR2, I came across of the following:
Command crs_stat has been deprecated in 11gR2, do not use it anymore, to find out all user resource state use, $GRID_HOME/bin/crsctl stat res -t
By the way, in 11gR2 or above, you can use the below command to find out the clusterware process state: $GRID_HOME/bin/crsctl stat res -t -init
See also : Alert log & Trace file location in 11gr2
