BPavan DBA's Blog: New Features

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>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 cloning 

Rman cloning from active database


$rman target sys/manager@totarget nocatalog auxiliary sys/manager@totest


Rman> duplicate target database to ‘test’ from active database;





13.Configuring physical standby from active backup

$rman target sys/manager@totarget nocatalog auxiliary sys/manager@tostdby

Rman>duplicate target database for standby from active database;

14.Converting physical standby to snapshot standby

1.configure FRA (flashback recovery area) at standby side

sql>alter database recover managed standby database disconnect;

sql>alter database recover managed standby database cancel;

sql>alter database convert to snapshot standby;

open the database 

sql>select database_role from v$database;




15.Converting snapshot standby database back to physical standby

sql>startup mount

sql>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 ZLIB


Rman>CONFIGURE COMPRESSION ALGORITHM 'ZLIB';



18.Virtual catalog 

-         prod (catalog db),  target db –> proda , prodb

 At 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  backup 

Asmcmd>md_backup –b <file_name> -g <disk_group_name>

Restore diskgroup meta data

Asmcmd>md_restore –b <file_name>



Join Facebook