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;

tablespace managment

Tablespace management:

>Table space consists of one or more data files.
>Data files belongs to only one table space.
>Oracle stores the data logically in table space and physically in data files.
>The database is divided into logical stored units called table space, which can be used to group related logical structure to gather.
>You can also create big file table space, which is a table space with a single data file but very large (up to 8000TB).
>Small file table spaces are default, which contains multiple data files but files can not be as large as a big file table space.
>You can place the tables and indexes associated with an application into a set of table space in order to manage that data more easily.
>Another common use for table space is performance related you can physically separate table from indexes on to separate disk drives with all using logical volume manager.
>Big file table space are intended to very large database (if we are going with small files, oracle has thousands of read and write data files, operations that must update the all data files header such as ckpt can take long time which performance degrades.
> One small file table space can contain 1024 data file.
>System and sysaux table space always created in small file table space.
>You cannot make system table space offline, by default it is online.
>We cannot make undo table space from read only.
>We cannot drop tablespace which is have active segment.
>We cannot decrease the size of the datafile beyond the used space.
>A tablespace is containing one or more datafiles ,where as one datafile is again divided into some set of blocks.

>BLOCK: It is smallest space allocation in oracle(generally reads/writ to disk).
>The block size from database is 2^nk. Where n is rang from(n=1,2,3,4,5) that is 2k,4k,8k,16k,32k.
>The default block size 2k, from 10g onwards the default block size is 8k.

> Once you create a tablespace we can also alter the tablespace
       1)Add one or more data file
       2)Resize the datafiles.
       3)Drop the datafiles.
       4)Make tablespace online(default)/ offline.
       5)Make tablespace read only/read write (default).
       6)We can also the rename the datafiles.       
       7)We can also the rename the tablespace from 10g onwards.
       8)We can drop the tablespace also.

       >We can chose the extent management

                                I. You can use the tablespace with either local extent management or dictionary extent                                      management.

                              II. Up to oracle 8,it support only DMTS.

                            III.  But from oracle 8i we have DMTS(default) and LMTS(optional).

                            IV.   From oracle 9i we have DMTS(optional) and LMTS(dfault).

          DMTS extents information:

           Free and used extents information will be stored in data dictionary

          LMTS extents information:

           Database tracks extents through the use of bitmap

          AUTO EXTEND ON:

           While tells the database to automatically enlarge a data file which the tablespace runs of free space.                 This will apply to individual data file not to the tablespace. 

                                I.            Alter database datafile < file _id> autoextend on;

                              II.            Move it to target location by using os level command.

                            III.            Rename it to new name at database level.

                            IV.            Sync the datafile header with the rest of level.

                              V.            Bring the tablespace to online

        Once we create a brand new table space  by default

                                I.            Online

                              II.            Read/Write

                            III.            Logging=yes (LWGR write data into redo log files)

                            IV.            Plugged in=No(it will same data base)

                              V.            Perment objects

                            VI.            Locally managed table space
              VII.            Small file table space

Database creation(manual)

 

                                             Database creation(manual)

Database can be created using any of the following methods.

1.At the time of installation of oracle software.

2.Using dbca.

3.Manually.

env |grep ORA(to know the environment variable)

. .bash_profile 

1.Create directory structure

cd /oradata02

mkdir dev & cd dev

mkdir control data log arch adump

2.Make the parameter file

oracle$cd $ORACLE_HOME/dbs

oracle$vi initdev.ora

db_name=dev

control_files = '/oradata02/dev/control/control1.ctl','/oradata02/dev/control/control2.ctl'

undo_management = Auto

undo_tablespace = UNDOTBS1

db_block_size = 8192

compatible = 11.2.0

memory_max_target = 500m

memory_target = 500m

pga_aggregate_target= 100m

log_archive_dest_1='LOCATION=/oradata02/dev/arch'

:wq!

cd  /oradata02/dev

oracle$vi dbcreation.sql

CREATE DATABASE dev

SET TIME_ZONE='Asia/Calcutta'

   USER SYS IDENTIFIED BY Oracle123

   USER SYSTEM IDENTIFIED BY Oracle123

   LOGFILE GROUP 1 ('/oradata02/dev/log/redo01a.log','/oradata02/dev/log/redo01b.log') SIZE 50M BLOCKSIZE 512,

           GROUP 2 ('/oradata02/dev/log/redo02a.log','/oradata02/dev/log/redo02b.log') SIZE 50M BLOCKSIZE 512,

           GROUP 3 ('/oradata02/dev/log/redo03a.log','/oradata02/dev/log/redo03b.log') SIZE 50M BLOCKSIZE 512

   MAXLOGFILES 5

   MAXLOGMEMBERS 5

   MAXLOGHISTORY 1

   MAXDATAFILES 100

   CHARACTER SET US7ASCII

   NATIONAL CHARACTER SET AL16UTF16

   EXTENT MANAGEMENT LOCAL

   DATAFILE '/oradata02/dev/data/system01.dbf' SIZE 325M REUSE

   SYSAUX DATAFILE '/oradata02/dev/data/sysaux01.dbf' SIZE 325M REUSE

   DEFAULT TABLESPACE users

      DATAFILE '/oradata02/dev/data/users01.dbf'

      SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

   DEFAULT TEMPORARY TABLESPACE tempts1

      TEMPFILE '/oradata02/dev/data/temp01.dbf'

      SIZE 20M REUSE

   UNDO TABLESPACE undotbs1

      DATAFILE '/oradata02/dev/data/undotbs01.dbf'

      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

:wq!

export ORACLE_SID=dev

sqlplus / as sysdba

sql>create spfile from pfile;

sql>startup nomount

sql>@dbcreation.sql

you must see database creation statement here.

Once you see database created message, run the following scripts to create the data dictionary.

sql> @?/rdbms/admin/catalog.sql

Then run cataproc.sql and pupbld.sql

sql> @?/rdbms/admin/catproc.sql

sql> conn system/Oracle123

sql> @?/sqlplus/admin/pupbld.sql