11G Enhancements

                                          11G Enhancements

1) Data recovery adviser

2) Proactive health check

3) Zlib compression

4) Parallel backup of the file.

1) Data recovery adviser:

From Oracle 11g rman advises the solution for the databases related problems. This is done using the data recovery adviser.

-> rman use different commands to list the failures and advisers.

-> where there is a problem with the database, it list all the files with the recovery is needed.

-> it provides solution along with the rman code to solve the problem.

-> the problem can be solved manually or automatically.

list failure -> list of the missing files.

advise failure -> solution of the failure

repair failure -> automatically repairs the files.

2) Proactive health check:

instead of DBA checking each datafile of the health check, Oracle checks the database health time to time and stores the result in the ADR location.

-> when there is a problem with the datafiles (logical or physically corrupted) advisors provide the information using health check monitor.

Note: the catalog information is also stored in the target controlfile.

3) Compression using zlib:

Before zlib compression we used to use bzip2 compression, zlib has overcome bzip2 disadvantages.

bzip2: CPU utilization high, recovery data is very slow, compression ratio high.

zlib: cpu utilization low, recover data is very high, compression raio low.

4)Parallel backup of the files:

->  Till oracle 10g the parallelism is based on the file.

-> only a single process performs operations on a single datafile.

-> if we maintain parallelism 2, then the rman takes the backup of two files at a time with each process on a single datafiles.

-> This could be a problem whenever the datafile size is high.

-> From Oracle 11g parallelism based on the sessions.

-> while backup of the datafiles each file is divided into sessions and parallelism applied based on the sessions.

-> The session size must of less than the datafile size otherwise parallelism will be applied based on the files.

Demo:

make sure we have the backup before we test it:

backup database plus archivelog;

SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test.dbf' size 20m;

Tablespace created.

SQL> create user test identified by test default tablespace test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> connect test/test

Connected.

SQL> create table jb (name varchar(20));

Table created.

SQL>

SQL> insert into jb values ('SRIKANTH');

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL>

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME

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

JB                             TEST

SQL> host

[oracle@rac1 ~]$ cd /u01/app/oracle/oradata/orcl/

[oracle@rac1 orcl]$ ls

control01.ctl  redo02.log    system01.dbf  undotbs01.dbf

example01.dbf  redo03.log    temp01.dbf    users01.dbf

redo01.log     sysaux01.dbf  test.dbf

[oracle@rac1 orcl]$ rm -rf test.dbf

[oracle@rac1 orcl]$ exit

exit

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

JB                             TABLE

SQL> select count(*) from jb;

  COUNT(*)

----------

         8

SQL> create table srikanth as select * from jb;

create table srikanth as select * from jb

                                  *

ERROR at line 1:

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/test.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL> host more /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2254609139.hm

   # restore and recover datafile

   sql 'alter database datafile 6 offline';

   restore datafile 6;

   recover datafile 6;

   sql 'alter database datafile 6 online';

SQL>

RMAN> backup  database plus archivelog;

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 11/16/2015 11:35:56

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/test.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

RMAN> list failure;

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

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

142        HIGH     OPEN      16-NOV-15     One or more non-system datafiles are missing

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status    Time Detected Summary

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

142        HIGH     OPEN      16-NOV-15     One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time

using channel ORA_DISK_1

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file /u01/app/oracle/oradata/orcl/test.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

========================

Option Repair Description

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

1      Restore and recover datafile 6

  Strategy: The repair includes complete media recovery with no data loss

  Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2254609139.hm

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2254609139.hm

contents of repair script:

   # restore and recover datafile

   sql 'alter database datafile 6 offline';

   restore datafile 6;

   recover datafile 6;

   sql 'alter database datafile 6 online';

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2254609139.hm

contents of repair script:

   # restore and recover datafile

   sql 'alter database datafile 6 offline';

   restore datafile 6;

   recover datafile 6;

   sql 'alter database datafile 6 online';

Do you really want to execute the above repair (enter YES or NO)? YES

executing repair script

sql statement: alter database datafile 6 offline

Starting restore at 16-NOV-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/test.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_11_16/o1_mf_nnndf_TAG20151116T112912_c4lw5kns_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2015_11_16/o1_mf_nnndf_TAG20151116T112912_c4lw5kns_.bkp tag=TAG20151116T112912

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

Finished restore at 16-NOV-15

Starting recover at 16-NOV-15

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 16-NOV-15

sql statement: alter database datafile 6 online

repair failure complete

RMAN>

Steps:

rman> list failure;

rman> advise failure;

rman> repair failure preview;

rman> repair failure;

Demo on proactive health check:

1) validate database;

2) validate tablespace;

3) validate datafile;

zlib compression demo:

1) configure compression algorithm 'zlib';

show all;

backup as compressed backupset database;

Demo on parallel backup of the files:

backup section size 1m database;