Nov 22, 2019

Table Fragmentation and re-organisation in Oracle Database

How to Find and Remove Table Fragmentation?
Let us discuss.

What is Table Fragmentation?
In simple language you can say, table has less data but size is too high which is not expected at all.

Brief explanation:

When rows are inserted into the table, the high watermark(used blocks) of the table moved forward in order to accommodate new rows into the table. But during delete operation, oracle doesn’t allow high watermark to shift backward in order to decrease table size and release the free space.
Ideally once we delete the data from the table, the free space should be released or reused but additionally oracle acquire new blocks to accommodate the new rows for insertion which causes hole into the table.

How to remove table fragmentation in oracle?

There are different ways to remove fragmentation like table export/import, moving tables to same or different tablespace and table recreation. But here we will discuss most easy and common ways of removing fragmentation.

Steps To Check and Remove Fragmentation:

#1 :  Gather table statistics

In order to find the exact difference between the total size and actual size of the table from dba_segments and dba_tables views. You can also check the last analysed date of a table. If table has recently analysed and stale_stats is no then you can skip this step.

select table_name,last_analyzed,stale_stats from user_tab_statistics where table_name='&TABLE_NAME';

exec dbms_stats.gather_table_stats(ownname => 'TABLE_OWNER', tabname => 'TABLE_NAME',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,granularity =>'AUTO', method_opt=> 'for all indexed columns size skewonly', degree => 8, cascade => TRUE );


#2 : Check table size

select sum(bytes)/1024/1024/1024 "size_GB" from dba_segments where segment_name='&TABLE_NAME' and owner='&OWNER';


#3 :  Check actual table size, fragmented size and percentage of fragmentation in a table
(Use 8 in place of 16 if your block size is 8k)

SELECT table_name,
  avg_row_len,
  ROUND(((blocks*16/1024)),2)  ||'MB' "TOTAL_SIZE",
  ROUND((num_rows*avg_row_len/1024/1024),2)  ||'MB' "ACTUAL_SIZE",
  ROUND(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)  ||'MB' "FRAGMENTED_SPACE",
  (ROUND(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/ROUND(((blocks*16/1024)),2))*100 "percentage"
FROM all_tables
WHERE table_name='&TABLE_NAME' and owner='&OWNER';

e.g.,

TABLE_NAME                    |AVG_ROW_LEN|TOTAL_SIZE|ACTUAL_SIZ|FRAGMENTED|percentage
------------------------------|-----------|----------|----------|----------|----------
SALES_ORDERS                  |       3754|444.64MB  |54.67MB   |389.97MB  |87.7046599


 If you find more than 20% fragmentation then you can proceed for de-fragmentation. You can also de-fragment based on size you are going to reclaim from the above mentioned  query.


#4 : Check the indexes on the table

select index_name from dba_indexes where table_name='&TABLE_NAME' and owner='&OWNER';


#5 : Remove fragmentation by moving tables to same tablespace. You can also move tables to different tablespace.

alter table <table_name> move;

For moving tables into different tablespace

alter table <table_name> enable row movement;
alter table <table_name> move tablespace <new_tablespace>;


#6 : Rebuild the indexes.

alter index index_name rebuild online;

#7 : Gather the statistics:

SQL> exec dbms_stats.gather_table_stats(ownname => 'TABLE_OWNER', tabname => 'TABLE_NAME',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,granularity =>'AUTO', method_opt=> 'for all indexed columns size skewonly', degree => 8, cascade => TRUE );


#8: Finally check the table size and percentage of fragmentation:
(Use 8 in place of 16 if your block size is 8k)

SELECT table_name,
  avg_row_len,
  ROUND(((blocks*16/1024)),2) ||'MB' "TOTAL_SIZE",
  ROUND((num_rows*avg_row_len/1024/1024),2) ||'MB' "ACTUAL_SIZE",
  ROUND(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
  (ROUND(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/ROUND(((blocks*16/1024)),2))*100 "percentage"
FROM all_tables
WHERE table_name='&TABLE_NAME' and owner='&OWNER';

Note: If table move is not helping to avoid fragmentation, then proceed to drop and re-create table with downtime. Make sure you should take care of all FK constraints.  

Nov 17, 2019

Backup & Recovery plans for VLDBs

Backup and recovery is a crucial and important job for a DBA to protect business data.
Especially if database size is too big, then backup and recovery plan must be tested to define RPO and RTO.

Recovery Time Objective (RTO) is the time duration in which you want to be able to recover your data.

A Recovery Point Objective, or RPO, is the maximum amount of data that can be lost before causing detrimental harm to the organization. RPO indicates the data loss tolerance of a business process or an organization in general.

An efficient backup and recovery strategy for very large databases to reduce the overall resources necessary to support backup and recovery by using some special characteristics that differentiate data warehouses from OLTP systems.

A DBA should initially approach the task of data warehouse backup and recovery by applying the same techniques that are used in OLTP systems: the DBA must decide what information to protect and quickly recover when media recovery is required, prioritizing data according to its importance and the degree to which it changes. However, the issue that commonly arises for data warehouses is that an approach that is efficient and cost-effective for a 100 GB OLTP system may not be viable for a 10 TB data warehouse. The backup and recovery may take 100 times longer or require 100 times more storage.

Oracle Backup and Recovery:

In general, backup and recovery refers to the various strategies and procedures involved in protecting your database against data loss and reconstructing the database after any kind of data loss.

A backup is a representative copy of data. This copy can include important parts of a database such as the control file, archived redo logs, and data files. A backup protects data from application error and acts as a safeguard against unexpected data loss, by providing a way to restore original data.

1) Architecture backup environment:
Use VTL ( virtual Tape libary) rather than actual tape drive. VTL are fast and random accessible as compare to actual tape library.

2) Evaluate Backup tools:
- Evaluate Full, incremental and archive-log backups
- Evaluate file level backups - helpful for logical backups
- Verify support related to multiplex / Parallel features
- Verify PDB/ CDB backups from Oracle 12c on wards

3) Flashback technologies
- Evaluate to take whole database with snapshot basis
- Evaluate CDP ( continuous data protection) capabilities

4) Evaluate Compression features
- RMAN Backup level
- file level

6) Backup startergies
- Weekly level 0 (full) and daily level 1 at night
- Periodically archive log backups

Case-study: VLDB deployments

1) Enable block change tracking:
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '<path_to_block_change_file_name>';
e.g.,
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u02/oradata/PROD/bct_prod.dbf';
-- incase of OMF/ RAC
SQL> alter database enable block change tracking;
2) Configure the preferred RMAN Recovery Window:
e.g.,
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
3) enable auto-backup of control-file in RMAN:
e.g.,
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
$) Enable/ configure parallelism:
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4  BACKUP TYPE TO BACKUPSET; # use parallelism as per core availability
   Note: In case of tape/ 3rd party console, enable parallelism at console level
4) Performing the Incremental / Full backups:

-- To Tape
run {
allocate channel c1 type 'SBT_TAPE';
allocate channel c2 type 'SBT_TAPE';
sql 'alter system archive log current';
BACKUP CURRENT CONTROLFILE format '%d_%T_%s_%p_full_ctl_%U.bkp' tag 'Inc_CF';
backup incremental level 0 database format '%d_%T_%s_%p_full_%U.bkp' tag 'Inc_BKP';
sql 'alter system archive log current';
backup archivelog all format '%d_%T_%s_%p_full_al_%U.bkp' tag 'Arc_BKP;
DELETE FORCE NOPROMPT OBSOLETE;
release channel c1;
release channel c2;

e.g.,

-- List backup

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
22269123 B  F  A SBT_TAPE    17-NOV-19       1       1       NO         INC_CF
22269184 B  1  A SBT_TAPE    17-NOV-19       1       1       NO         INC_BKP
22269185 B  1  A SBT_TAPE    17-NOV-19       1       1       NO         INC_BKP
22269255 B  A  A SBT_TAPE    17-NOV-19       1       1       NO         ARC_BKP
22269256 B  A  A SBT_TAPE    17-NOV-19       1       1       NO         ARC_BKP
22269257 B  A  A SBT_TAPE    17-NOV-19       1       1       NO         ARC_BKP
22269258 B  A  A SBT_TAPE    17-NOV-19       1       1       NO         ARC_BKP
22269511 B  F  A SBT_TAPE    17-NOV-19       1       1       NO         TAG20191117T103335

RMAN>

-- Disk backup
run
{
allocate channel c1 type disk format '/u03/backup/%d_%T_%s_%p_%U.bkp' maxpiecesize 100G;
allocate channel c2 type disk format '/u03/backup/%d_%T_%s_%p_%U.bkp' maxpiecesize 100G;
allocate channel c3 type disk format '/u03/backup/%d_%T_%s_%p_%U.bkp' maxpiecesize 100G;
allocate channel c4 type disk format '/u03/backup/%d_%T_%s_%p_%U.bkp' maxpiecesize 100G;
sql 'alter system archive log current';
BACKUP CURRENT CONTROLFILE format '%d_%T_%s_%p_full_ctl_%U.bkp' tag 'Inc_CF';
backup  as compressed backupset incremental level 0  check logical database plus archivelog;
release channel c1 ;
release channel c2 ;
release channel c3 ;
release channel c4 ;
}

5) Perform Restore and recover to evaluate RTO:

6) ASM Best Practices for VLDBs
- Large allocation units
- Between 1MB and 64MB (4,8,16,32 and 64)
- Use 64MB for a very large data warehousing system, which will reduce ASM
- memory requirements, and also improve ASM instance startup time
Disk groups with an External Redundancy

I hope this document may help to define backups in your environment.

Nov 13, 2019

VIP showing INTERMEDIATE and FAILED OVER


We received complain from application team, that we are not able to do connect database. But when we checked, database is up and running fine. One database configured in one of the 2-Node RAC (11gR2 RAC cluster). From verification found VIP of that node is in "INTERMEDIATE and FAILED OVER" state. Listeners are running fine in both nodes.

So, the issue is, VIP was showing as INTERMEDIATE and FAILED OVER in one of RAC node 

Checked crs the status and here is the sample output:

$ crsctl status resource -t

......
ora.example1a.vip
      1        ONLINE  ONLINE       example1a                              
ora.example1b.vip
      1        ONLINE  INTERMEDIATE example1a          FAILED OVER         


-- Now Check that particular VIP status

[oracle@example1b ~]$ crsctl status resource ora.example1b.vip
NAME=ora.example1b.vip
TYPE=ora.cluster_vip_net1.type
TARGET=ONLINE
STATE=INTERMEDIATE on example1a

[oracle@example1b ~]$ 


-- Now stop the VIP

crsctl stop resource ora.example1b.vip

ora.example1a.vip
      1        ONLINE  ONLINE       example1a                              
ora.example1b.vip
      1        OFFLINE OFFLINE 

-- Start the VIP
crsctl start resource ora.example1b.vip

[oracle@example1b ~]$ crsctl start resource ora.example1b.vip
CRS-2672: Attempting to start 'ora.example1b.vip' on 'example1b'
CRS-2676: Start of 'ora.example1b.vip' on 'example1b' succeeded
CRS-2672: Attempting to start 'ora.LISTENER.lsnr' on 'example1b'
CRS-2676: Start of 'ora.LISTENER.lsnr' on 'example1b' succeeded
[oracle@example1b ~]$ 

-- Check the status now

ora.exmaple1a.vip
      1        ONLINE  ONLINE       exmaple1a                              
ora.exmaple1b.vip
      1        ONLINE  ONLINE       exmaple1b 


Now it back to online.


Nov 11, 2019

ORA-19913: unable to decrypt backup - solution

While restoring database, I received below error.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/11/2019 04:53:47
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-19870: error while restoring backup piece /u02/work/038_08-Nov-2019_Gouranga_arisg51d_refresh/rman/arisg51p_03-Nov-2019_13-39-15_full_cf.bkp
ORA-19913: unable to decrypt backup


From verification found, this backup was taken with encryption enabled database and there is wallet configure also.

So while you restore, then start the database in 'nomount' state and see the below status. If it is closed, then you can't restore. So follow the below steps to start restore database.

-- start in nmount 
[oracle@example01 ~]$ sqlplus / as sysdba


SQL> select status from v$encryption_wallet;

STATUS
------------------------------------------------------------------------
CLOSED

SQL> quit


status must be 'open'


-- fix/ Solution

copy the wallets files and configure in sqlnet.ora also, then verify the status

e.g.,

coped below wallet files to your location something like this:

/u01/app/oracle/admin/<your_db_name>/wallet

Then, point your wallet in 'sqlnet.ora' file.

$ cd $ORACLE_HOME/network/admin
$ cat sqlnet.ora
ENCRYPTION_WALLET_LOCATION =
  (SOURCE = (METHOD = FILE)
     (METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/$ORACLE_UNQNAME/wallet)
     )
  )


SQL> select status from v$encryption_wallet;

STATUS
-------------------------------------------------
OPEN


But ensure, to enable encryption while restore the database like what is enabled during backup.

e.g.,

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

-- Encryption enabled with key AES256
RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;

new RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES256';

new RMAN configuration parameters:
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
new RMAN configuration parameters are successfully stored

Note that you can enable the encryption only after restoring the controlfile.

Translate >>