11g New Features
1.ADR = Automatic Diagnostic Repository
*in 11g
user_dump_dest,core_dump_dest,background_dump_dest are replace by
diagnostic_dest
Sub-dirs under
diagnostic_dest
Alert rdbms incidents
--Alert log file in two format
Log.xml
Alert_<SID>.log
2.Adrci Utility - to see the content of alert log or incident
details
$adrci
Adrci>show alert
Adrci>help
Adrci>show incident
Changes in SGA & PGA memory management
3.Memory related parameter
Memory_max_target (this is the upper limit
for sga and pga combined)
Memory_target (this is the actual size of sga
and pga combined)
4.In 11g by default password is
case sensitive, to disable it set the following parameter to false
Sec_case_sensitive_logon=false;
5.To make a table read only/ read write
SQL>alter table
emp read only;
SQL>alter table
emp read write;
SQL>select table_name,read_only from user_tables where
table_name='EMP';
6.Table compression
SQL>alter table
emp compress for all operations;
SQL>alter table
emp nocompress;
SQL>select
table_name ,compression from user_tables where table_name=’EMP’;
7.Making index invisible/visible
SQL>alter index
ind_emp invisible;
SQL>alter index
ind_emp invisible;
SQL>select
index_name,visibility from user_indexes where index_name=’IND_EMP’;
8.Shrinking temporary tablespace
SQL>alter
tablespace temp shrink space keep 100m;
Pending and publish statistics
9.By default statistics is published immediately so we
use dbms_stats package to change default behaviour
SQL>exec
dbms_stats.set_table_prefs('DEMO','SALES','PUBLISH','FALSE');
SQL> select dbms_stats.get_prefs('PUBLISH', 'DEMO', 'SALES' ) FROM DUAL;
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('DEMO','SALES');
SQL>SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='SALES';
10.To see the pending stats
SQL> SELECT TABLE_NAME,PARTITION_NAME ,LAST_ANALYZED FROM DBA_TAB_PENDING_STAT;
Then we have to test the stats
SQL> alter
session set optimizer_use_pending_statistics=true;
Check that stats is working fine or not if working
fine then publish;
SQL> EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('DEMO','SALES');
SQL> SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='SALES';
SQL> SELECT COUNT(*) FROM DBA_TAB_PENDING_STATS;
SQL> alter session set optimizer_use_pending_statistics=FALSE;
11.Flashback archive
Creatng Flashback Archive
SQL>create flashback archive flash_arc tablespace <tablespace_name> quota 1g Retention 1 year ;
SQL>alter table u1.emp flashback archive flash_arc;
Sql> flashback table emp to timestamp to_timestamp(’16:12:10 02-NOV-2010’ ,’hh24:mi:ss dd-mon-yyyy);12.Rman cloningRman cloning from active database$rman target sys/manager@totarget nocatalog auxiliary sys/manager@totestRman> duplicate target database to ‘test’ from active database;13.Configuring physical standby from active backup$rman target sys/manager@totarget nocatalog auxiliary sys/manager@tostdbyRman>duplicate target database for standby from active database;14.Converting physical standby to snapshot standby1.configure FRA (flashback recovery area) at standby sidesql>alter database recover managed standby database disconnect;sql>alter database recover managed standby database cancel;sql>alter database convert to snapshot standby;open the databasesql>select database_role from v$database;15.Converting snapshot standby database back to physical standbysql>startup mountsql>alter database convert to physical standby;sql>select database_role from v$database;16.Parallel backup through section (chunks)Rman>run{Allocate channel c1 device type disk;Allocate channel c2 device type disk;Allocate channel c3 device type disk;Backup section size 500m datafile 10;}Rman> list backup of datafile 10;Rman>list failure; (list block corruption and data failure)Rman>list failure <id>Rman>advise failure;Rman >repair failure;17.Faster backup compression using new algorithm ZLIBRman>CONFIGURE COMPRESSION ALGORITHM 'ZLIB';18.Virtual catalog- prod (catalog db), target db –> proda , prodbAt catalog side- create one more catalog user.(prodb)- From any target db (ex. proda)- $rman target / catalog rman/rman@tocatalog- Rman>grant catalog for database prodb to prodb;Rman > list db_unique_name all;From prodb database$rman target / catalog prodb/prodb@tocatalog;Rman>create virtual catalog;Rman > list db_unique_name all;Now for prodb database backup information will be store in virtual catalog as well as base catalog.19.ASM related new features:Asmcmd>lsdsk (shows the available no of disk)For Diskgroup meta data backupAsmcmd>md_backup –b <file_name> -g <disk_group_name>Restore diskgroup meta data
