Oracle Database 10g: Analyze Index Vs. Gather_Index_Stats

 One of the difference I noticed about analyze index command and gather_index_stats procedure is that when we use analyze index command it updates the index statistics with number of leaf blocks equals the number of leaf blocks below HWM.

However if we use gather_index_stats statistics shows number of leaf blocks equals number of leaf blocks that actually has the data.

Here is the illustration for the same.

SQL> select owner,index_name,table_name,BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS

from dba_indexes where _name='ID_TICKET_STOCK_MASTER'

OWNER	INDEX_NAME	TABLE_NAME	BLEVEL	LEAF_BLOCKS	DISTINCT_KEYS
AE1T3	STK_UKEY	ID_TICKET_STOCK_MASTER	2	10185	2515695

SQL> analyze index STK_UKEY estimate statistics;

SQL> select owner,index_name,table_name,BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS

from dba_indexes where _name='ID_TICKET_STOCK_MASTER'

OWNER	INDEX_NAME	TABLE_NAME	BLEVEL	LEAF_BLOCKS	DISTINCT_KEYS
AE1T3	STK_UKEY	ID_TICKET_STOCK_MASTER	2	10184	2515448

SQL> EXEC DBMS_STATS.gather_index_stats('AE1T3','STK_UKEY');

PL/SQL procedure successfully completed.

SQL> select owner,index_name,table_name,BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS

from dba_indexes where _name='ID_TICKET_STOCK_MASTER'

OWNER	INDEX_NAME	TABLE_NAME	BLEVEL	LEAF_BLOCKS	DISTINCT_KEYS
AE1T3	STK_UKEY	ID_TICKET_STOCK_MASTER	2	9428	2320245

Conclusion:

Action	INDEX_NAME	BLEVEL	LEAF_BLOCKS	DISTINCT_KEYS
SQL Query	STK_UKEY	2	10185	2515695
analyze index STK_UKEY estimate statistics;	STK_UKEY	2	10184	2515448
DBMS_STATS.gather_index_stats('AE1T3','STK_UKEY');	STK_UKEY	2	9428	2320245

Now if we see the numbers of leaf blocks reported are 9428. This plays quiet a big role for optimizer in creating plans for queries. If we don’t have correct stats for the index, it may lead to expensive explain plans.

So it’s better to use gather_index_stats rather than analyze index.

Hope this helps!!

Reclaiming Segment Space

alter table ae1t3.ID_TICKET_STOCK_MASTER enable row movement;
alter table ae1t3.ID_TICKET_STOCK_MASTER shrink space compact cascade;
alter table ae1t3.ID_TICKET_STOCK_MASTER disable row movement;

Drop 10g Database using RMAN

SQL> startup mount;

SQL> alter system enable restricted session;

$ rman target sys/dupdb@dupdb catalog rman/rman@catdb

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Apr 22 16:53:41 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: DUPDB (DBID=3693079167, not open)

connected to recovery catalog database

RMAN> DROP DATABASE INCLUDING BACKUPS;

database name is "DUPDB" and DBID is 3693079167

Do you really want to drop all backups and the database (enter YES or NO)? yes

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

List of Backup Pieces

BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

------- ------- --- --- ----------- ----------- ----------

251149  251145  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DRPROD_13_1_813321003

251150  251146  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DRPROD_14_1_813321069

251170  251167  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DRPROD_15_1_813321073

251244  251240  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DRPROD_16_1_813421585

251245  251241  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DRPROD_17_1_813421589

251246  251242  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DRPROD_18_1_813421655

251264  251261  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DRPROD_19_1_813421660

251490  251486  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DUPDB_1_1_813429205

251491  251487  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DUPDB_2_1_813429213

251492  251488  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DUPDB_3_1_813429215

251493  251489  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DUPDB_4_1_813429281

251514  251512  1   1   AVAILABLE   DISK        /zone/catdb/backup/drprod__DUPDB_5_1_813429285

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DRPROD_13_1_813321003 recid=13 stamp=813321007

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DRPROD_14_1_813321069 recid=14 stamp=813321069

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DRPROD_15_1_813321073 recid=15 stamp=813321074

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DRPROD_16_1_813421585 recid=16 stamp=813421586

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DRPROD_17_1_813421589 recid=17 stamp=813421593

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DRPROD_18_1_813421655 recid=18 stamp=813421655

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DRPROD_19_1_813421660 recid=19 stamp=813421661

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DUPDB_1_1_813429205 recid=1 stamp=813429206

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DUPDB_2_1_813429213 recid=2 stamp=813429214

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DUPDB_3_1_813429215 recid=3 stamp=813429216

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DUPDB_4_1_813429281 recid=4 stamp=813429281

deleted backup piece

backup piece handle=/zone/catdb/backup/drprod__DUPDB_5_1_813429285 recid=5 stamp=813429286

Deleted 12 objects

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

List of Archived Log Copies

Key     Thrd Seq     S Low Time  Name

------- ---- ------- - --------- ----

251481  1    694     A 21-APR-13 /zone/drprod/arch1/drprod_1_694_800019715.arc

251482  1    695     A 21-APR-13 /zone/drprod/arch1/drprod_1_695_800019715.arc

251483  1    696     A 22-APR-13 /zone/drprod/arch1/drprod_1_696_800019715.arc

251484  1    697     A 22-APR-13 /zone/drprod/arch1/drprod_1_697_800019715.arc

251460  1    1       A 21-APR-13 /zone/dupdb/arch1/dupdb_1_1_813327814.arc

251467  1    2       A 21-APR-13 /zone/dupdb/arch1/dupdb_1_2_813327814.arc

251473  1    3       A 22-APR-13 /zone/dupdb/arch1/dupdb_1_3_813327814.arc

251485  1    4       A 22-APR-13 /zone/dupdb/arch1/dupdb_1_4_813327814.arc

deleted archive log

archive log filename=/zone/drprod/arch1/drprod_1_694_800019715.arc recid=4 stamp=813429204

deleted archive log

archive log filename=/zone/drprod/arch1/drprod_1_695_800019715.arc recid=5 stamp=813429204

deleted archive log

archive log filename=/zone/drprod/arch1/drprod_1_696_800019715.arc recid=6 stamp=813429204

deleted archive log

archive log filename=/zone/drprod/arch1/drprod_1_697_800019715.arc recid=7 stamp=813429204

deleted archive log

archive log filename=/zone/dupdb/arch1/dupdb_1_1_813327814.arc recid=1 stamp=813327994

deleted archive log

archive log filename=/zone/dupdb/arch1/dupdb_1_2_813327814.arc recid=2 stamp=813429138

deleted archive log

archive log filename=/zone/dupdb/arch1/dupdb_1_3_813327814.arc recid=3 stamp=813429201

deleted archive log

archive log filename=/zone/dupdb/arch1/dupdb_1_4_813327814.arc recid=8 stamp=813429283

Deleted 8 objects

database name is "DUPDB" and DBID is 3693079167

database dropped

database name is "DUPDB" and DBID is 3693079167

database unregistered from the recovery catalog

RMAN>

Alert.log will write the below logs

DROP DATABASE

Mon Apr 22 16:54:02 2013

Deleted file /u01/app/oracle/oradata/dupdb/system01.dbf

Deleted file /u01/app/oracle/oradata/dupdb/undotbs01.dbf

Deleted file /u01/app/oracle/oradata/dupdb/sysaux01.dbf

Deleted file /u01/app/oracle/oradata/dupdb/users

Deleted file /u01/app/oracle/oradata/dupdb/redo01.log

Deleted file /u01/app/oracle/oradata/dupdb/redo02.log

Deleted file /u01/app/oracle/oradata/dupdb/redo03.log

Deleted file /u01/app/oracle/oradata/dupdb/redo04.log

Instance terminated by USER, pid = 20258

Deleted file /u01/app/oracle/oradata/dupdb/control01.ctl

Deleted file /u01/app/oracle/oradata/dupdb/control02.ctl

Deleted file /u01/app/oracle/oradata/dupdb/control03.ctl

Completed: DROP DATABASE

Mon Apr 22 16:54:08 2013

Shutting down instance (abort)

License high water mark = 3

Clone / Duplicate a database with RMAN

1.  create parameter file.

SQL> create pfile from spfile;

$ cp initdrprod.ora initadil.ora

2. edit parameter file and replace OLD SID with NEW SID

$ vi initadil.ora

adil.__db_cache_size=176160768 adil.__java_pool_size=4194304 adil.__large_pool_size=4194304 adil.__shared_pool_size=411041792 adil.__streams_pool_size=8388608 *.audit_file_dest='/u01/app/oracle/admin/adil/adump' *.background_dump_dest='/u01/app/oracle/admin/adil/bdump' *.compatible='10.2.0.3.0' *.control_files='/u01/app/oracle/oradata/adil/control01.ctl',                               '/u01/app/oracle/oradata/adil/control02.ctl',                               '/u01/app/oracle/oradata/adil/control03.ctl' *.core_dump_dest='/u01/app/oracle/admin/adil/cdump' *.db_block_size=8192 *.db_domain='omeir.global' *.db_file_multiblock_read_count=16 *.db_name='adil' *.dispatchers='(PROTOCOL=TCP) (SERVICE=adilXDB)' *.job_queue_processes=10 *.local_listener='LISTENER_ADIL' *.log_archive_dest_1='location=/zone/archive/adil/' *.log_archive_format='adil_%t_%s_%r.arc' *.open_cursors=300 *.pga_aggregate_target=203423744 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=612368384 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/adil/udump' *.db_file_name_convert='/u01/app/oracle/oradata/drprod/','/u01/app/oracle/oradata/adil/' *.log_file_name_convert='/u01/app/oracle/oradata/drprod/','/u01/app/oracle/oradata/adil/'

3. Create password file:

orapwd file=orapw$ORACLE_SID password=adil entries=50;

====

4. Create dump files:

$ cd /u01/app/oracle/admin/adil/

$ mkdir adump bdump cdump udump dpdump pfile  -- dump directory location

$ cd /u01/app/oracle/oradata/

$ mkdir adil                                                       -- controlfiles/datafiles/relofiles location

$ cd /zone

$ mkdir adil/arch                                             -- archive location.

====

5. Update listener and tnsnames.ora files

====

6.

set ORACLE_SID and start the database in nomount state.

===

7. execute the following duplicate database command ( run from auxiliary db console ‘oracle_sid’)

connect target sys/drprod@drprod

connect catalog rman/rman@catdb

connect auxiliary sys/adil@adil 

run {

set newname for datafile 1 to '/u01/app/oracle/oradata/adil/system01.dbf';

set newname for datafile 2 to '/u01/app/oracle/oradata/adil/undotbs01.dbf';

set newname for datafile 3 to '/u01/app/oracle/oradata/adil/sysaux01.dbf';

set newname for datafile 4 to '/u01/app/oracle/oradata/adil/users';

allocate channel c2 type disk;

set until sequence 2 thread 1;

duplicate target database to adil

logfile

  GROUP 1 ('/u01/app/oracle/oradata/adil/redo01.log') SIZE 5M  REUSE,

  GROUP 2 ('/u01/app/oracle/oradata/adil/redo02.log') SIZE 5M  REUSE,

  GROUP 3 ('/u01/app/oracle/oradata/adil/redo03.log') SIZE 5M  REUSE,

  GROUP 4 ('/u01/app/oracle/oradata/adil/redo04.log') SIZE 5M  REUSE;

}

8. Change passwords of sys/system/sysman/dbsnmp (if required)

9. Register database with catalog ( if required)

$ rman target sys/adil@adil catalog rman/rman@catdb

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Apr 22 16:09:37 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ADIL (DBID=2245812828)

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN> backup database plus archivelog;

10 Create Enterprise manager Db controle

$ emca -repos drop

$ emca -repos create

$ emca -config dbcontrol db

Encrypting Data in Oracle 10g: Transparent Data Encryption (TDE)

SOURCE:

 http://oracleinsider.wordpress.com/2009/05/09/encrypting-data-in-oracle-10g-transparent-data-encryption-ted/

Encrypting Data in Oracle 10g: Transparent Data Encryption (TDE)

The Transparent Data Encryption (TDE) feature introduced in Oracle 10g Database Release 2 allows sensitive data to be encrypted within the datafiles to prevent access to it from the operating system.

Using any HEX editor e.g. Ultraedit software allows the portion of data to be viewed present in the datafiles which ultimately breaches the security of sensitive company information. The short guide presented below provides steps for setting up a simple encryption strategy on sensitive data columns to prevent access of data from operating system e.g. using hex editors etc.

Steps of setting up Data Encryption

1. Setting up a Wallet
         a. Creating and Opening a Wallet
         b. Closing a Wallet to prevent access
2. Encrypting the desired Data Column
          a. Encrypting with Salt
          b. Encrypting with Non-Default Algorithm
          c. Encrypt for External Tables
          d. Generating a New key for the column
          e. Turning Off Column Encryption
3. Additional Resources

1. Setting up a Wallet

Creating and Opening a Wallet

In order to hold the encryption key of the data columns, a wallet must be created before any column could be encrypted. The parameter ENCRYPTION_WALLET_LOCATION in sqlnet.ora file provides the location of where the wallet is to be found. The default location of the wallet is $ORACLE_BASE/admin/$ORACLE_SID/wallet.

The following command creates and opens up the wallet (using sys account)

• Connect sys/pwd as sysdba
• ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY “AnyPassword”;

To reopen the wallet, issue the following command
           • ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY “AnyPassword”;

Closing the Wallet to prevent Access

In order to prevent access to sensitive data, the wallet must be closed using the following command

              • ALTER SYSTEM SET WALLET CLOSE;

2. Encrypting the desired Data Column

The default encryption method used in oracle is AES192. In order to encrypt the desired columns, use the ENCRYPT clause after every column definition as below:

Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN number ENCRYPT
) tablespace employees;

If one requires to use any encryption method other than ‘AES192’, then use ‘ENCRYPT USING’ clause within the column definition as below:

Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN number ENCRYPT USING ‘3DES168’
) tablespace employees;

ENCRYPT with SALT

For increased encryption security, use the ‘ENCRYPT SALT’ for Salt or ‘ENCRYPT NO SALT’ for No Salt command to specify random encrypted string in the generated key for the desired column.

Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT NO SALT,
Pwd varchar2(10) ENCRYPT SALT,
SSN number ENCRYPT
) tablespace employees;

ENCRYPT for External Tables

Specify Encrypt identification key by using ‘ENCRYPT IDENTIFIED BY’ clause in the column definition as below:

Create table Employee
(
EmpNo number primary key,
EmpName varchar2(100),
Login varchar2(100) ENCRYPT,
Pwd varchar2(10) ENCRYPT,
SSN varchar2(11) ENCRYPT IDENTIFIED BY ‘xc7If3t9’
) tablespace employees;

Generating a New key for the column

To re-generate a fresh key for the column, use the following command

• ALTER TABLE employee REKEY
OR
• ALTER TABLE employee REKEY USING ‘3DES168’;

Turning Off Column Encryption

By using the ‘DECRYPT’ clause, the column encryption can later be turned off as below:

• Alter table employees modify(ssn varchar2(11) DECRYPT);

3. For more information visit

• http://www.oracle-base.com/articles/10g/TransparentDataEncryption_10gR2.php
• http://download-uk.oracle.com/docs/cd/B19306_01/network.102/b14268/asotrans.htm

Hope this effort helps in any manner!