PHYSICAL STANDBY DATABASE 11g R2

Target database : orcl (10.0.0.160) (primary database)

Auxiliary database: orclstby (10.0.0.161) (standby database)

Check that the primary database is in archivelog mode.

Sql > select log_mode from v$database;

If it is noarchivelog mode, switch it to archivelog mode.

Sql > SHUTDOWN IMMEDIATE

Sql > STARTUP MOUNT;

Sql > ALTER DATABASE ARCHIVELOG;

Sql > ALTER DATABASE OPEN;

Sql> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=/u01/orcl_archives’ SCOPE=SPFILE;

Enabled forced logging by issuing the following command

Sql > ALTER DATABASE FORCE LOGGING;

Add standby logfiles on primary database (it is recommended to create standby redo logs on primary database to make things easier during switchover and failover)

     
      Sql > ALTER DATABASE ADD STANDBY LOGFILE GROUP 4      

                 ‘/u01/app/oracle/oradata/orcl/redo04.log’  SIZE 15M; 

      Sql > ALTER DATABASE ADD STANDBY LOGFILE GROUP 5   

                 ‘/u01/app/oracle/oradata/orcl/redo05.log’  SIZE 15M; 

     Sql > ALTER DATABASE ADD STANDBY LOGFILE GROUP 6    

                 ‘/u01/app/oracle/oradata/orcl/redo06.log’  SIZE 15M; 

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value "DB11G_STBY".

   
      Sql > ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclstby)';

Set suitable remote archive log destinations.

      
      Sql > ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orclstby NOAFFIRM  ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstby';    

Sql > ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.

Sql > ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

Sql > ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;

Sql > ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and patch differences between the servers.

 
       Sql > ALTER SYSTEM SET FAL_SERVER=orclstby;

 Sql > ALTER SYSTEM SET DB_FILE_NAME_CONVERT='orclstby','orcl' SCOPE=SPFILE;

Sql > ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='orclstby','orcl'  SCOPE=SPFILE;

Sql > ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect.

PREPARE STANDBY DATABASE

·         Install oracle 11g R2 binaries on standby machine i.e on 10.0.0.161

·         Create directories on standby at the desired locations (refer to fig 1.2)

·         Copy password file to standby database(password file of my primary database is orapworcl ,so I copied it to standby database and changed its name to orapworclstby)

·         Configure tnsnames.ora and listener.ora on both primary (orcl) and standby (orclstby) database

Listener.ora of orcl (primary database)

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = orcl)

    ) )

          LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.160)(PORT = 1521))

    ))

      ADR_BASE_LISTENER = /u01/app/oracle

tnsnames.ora of orcl (primary database)

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.160)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

      (INSTANCE_NAME = orcl)

    ))

           ORCLSTBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.161)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orclstby)

      (INSTANCE_NAME = orclstby)

    ) )

      Listener.ora of orclstby (standby database)

   

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

       (GLOBAL_DBNAME = orclstby)

       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

       (SID_NAME = orclstby)

     ))

LISTENER =

   (DESCRIPTION_LIST =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      )

      (DESCRIPTION =

         (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.161)(PORT = 1521))

      ))

ADR_BASE_LISTENER = /u01/app/oracle

tnsnames.ora of orclstby (standby database)

ORCL =

  (DESCRIPTION =

   (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.160)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

      (INSTANCE_NAME = orcl)

    ))

ORCLSTBY =

  (DESCRIPTION =

   (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.161)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orclstby)

      (INSTANCE_NAME = orclstby)

    ))

·         Create pfile for standby database

On primary database, execute

Sql > CREATE PFILE='/u01/initorclstby.ora' FROM SPFILE;

 Alter the above pfile and transfer it to standby database

So , pfile (initorclstby.ora) of my standby database (orclstby) is :

orclstby.__db_cache_size=109051904

orclstby.__java_pool_size=4194304

orclstby.__large_pool_size=4194304

orclstby.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orclstby.__pga_aggregate_target=184549376

orclstby.__sga_target=251658240

orclstby.__shared_io_pool_size=0

orclstby.__shared_pool_size=125829120

orclstby.__streams_pool_size=0

audit_file_dest='/u01/app/oracle/admin/orclstby/adump'

audit_trail='db'

compatible='11.2.0.0.0'

control_files='/u01/app/oracle/oradata/orclstby/control01.ctl','/u01/app/oracle/flash_recovery_area/orclstby/control02.ctl'

db_block_size=8192

db_domain='localdomain'

db_unique_name='orclstby'

db_file_name_convert='orcl','orclstby'

db_name='orcl'

db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

db_recovery_file_dest_size=4039114752

diagnostic_dest='/u01/app/oracle'

dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

fal_server='orcl'

log_archive_config='DG_CONFIG=(orcl,orclstby)'

log_archive_dest_1='location=/u01/orcl_archives'

log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

log_archive_dest_state_2='ENABLE'

log_archive_format='%t_%s_%r.arc'

log_archive_max_processes=30

log_archive_start=TRUE

log_file_name_convert='orcl','orclstby'

memory_target=433061888

open_cursors=300

processes=150

remote_login_passwordfile='EXCLUSIVE'

standby_file_management='AUTO'

undo_tablespace='UNDOTBS1'

TAKING BACKUP OF PRIMARY DATABASE USING RMAN

-bash-3.2$ rman

          Recovery Manager: Release 11.2.0.1.0 - Production on Thu Nov 24 14:41:20 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN >  connect target sys@orcl

target database Password:

connected to target database: ORCL (DBID=1295591331)

RMAN >  backup database include current controlfile for standby plus archivelog;

Starting backup at 24-NOV-11

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=101 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=3 RECID=1 STAMP=767885366

input archived log thread=1 sequence=4 RECID=2 STAMP=767885391

input archived log thread=1 sequence=5 RECID=3 STAMP=767886484

input archived log thread=1 sequence=6 RECID=4 STAMP=767886616

input archived log thread=1 sequence=7 RECID=6 STAMP=767899462

input archived log thread=1 sequence=8 RECID=5 STAMP=767899353

input archived log thread=1 sequence=9 RECID=7 STAMP=767906298

input archived log thread=1 sequence=10 RECID=8 STAMP=767907740

input archived log thread=1 sequence=11 RECID=9 STAMP=767908826

input archived log thread=1 sequence=12 RECID=10 STAMP=767908911

input archived log thread=1 sequence=13 RECID=11 STAMP=767916311

input archived log thread=1 sequence=14 RECID=12 STAMP=767917336

input archived log thread=1 sequence=15 RECID=13 STAMP=767950266

input archived log thread=1 sequence=16 RECID=14 STAMP=767986293

input archived log thread=1 sequence=17 RECID=15 STAMP=768003827

input archived log thread=1 sequence=18 RECID=16 STAMP=768036665

input archived log thread=1 sequence=19 RECID=17 STAMP=768059623

input archived log thread=1 sequence=20 RECID=18 STAMP=768060626

input archived log thread=1 sequence=21 RECID=19 STAMP=768062538

channel ORA_DISK_1: starting piece 1 at 24-NOV-11

channel ORA_DISK_1: finished piece 1 at 24-NOV-11

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_annnn_TAG20111124T144222_7dw2my5x_.bkp tag=TAG20111124T144222 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:38

Finished backup at 24-NOV-11

Starting backup at 24-NOV-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: starting piece 1 at 24-NOV-11

channel ORA_DISK_1: finished piece 1 at 24-NOV-11

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_nnndf_TAG20111124T144408_7dw2q1qv_.bkp tag=TAG20111124T144408 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:03:04

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including standby control file in backup set

channel ORA_DISK_1: starting piece 1 at 24-NOV-11

channel ORA_DISK_1: finished piece 1 at 24-NOV-11

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_ncnnf_TAG20111124T144408_7dw2ww2x_.bkp tag=TAG20111124T144408 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 24-NOV-11

Starting backup at 24-NOV-11

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=22 RECID=20 STAMP=768062838

channel ORA_DISK_1: starting piece 1 at 24-NOV-11

channel ORA_DISK_1: finished piece 1 at 24-NOV-11

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_annnn_TAG20111124T144718_7dw2x0bk_.bkp tag=TAG20111124T144718 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 24-NOV-11

Starting Control File Autobackup at 24-NOV-11

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_24/o1_mf_n_768062841_7dw2x23z_.bkp comment=NONE

Finished Control File Autobackup at 24-NOV-11

RMAN>

 COPY ALL BACKUP AND ARCHIVES FROM PRIMARY DATABASE TO STANDBY DATABASE 

Location of archives of orcl (primary database) on 10.0.0.160 is /u01/ and these archives are copied on standby 10.0.0.161 at exactly the same location (/u01/).

·        Location of backupset of orcl (primary) on 10.0.0.160 is /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_22/ , /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/

              and

             /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_22/ ,

             /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_24/

              And so, they are copied on standby 10.0.0.161 at exactly the same location i.e

Location of backupset of orclstby (standby) on 10.0.0.161 is /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_22/ , /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/

              and

              /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_22/ ,

              /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/

So, directories  location on primary i.e orcl (10.0.0.160) are:

Fig 1.1

And directories location on standby i.e orclstby (10.0.0.161) are:

Fig.1.2

Notice: in above figures the path i.e /u01/app/oracle/flash_recovery_area/ORCL/autobackup/ on primary (orcl 10.0.0.160) and /u01/app/oracle/flash_recovery_area/ORCL/backupset/ on standby (orclstby 10.0.0.161) are exactly same.

CREATE STANDBY DATABASE (orclstby) USING DUPLICATE COMMAND

On standby database , orclstby (i.e at 10.0.0.161)

-bash-3.2$ export ORACLE_SID=orclstby

-bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 24 13:47:15 2011

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

Enter user-name: sys@orclstby as sysdba

Enter password:

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorclstby.ora';

ORACLE instance started.

Total System Global Area  435224576 bytes

Fixed Size                  1337044 bytes

Variable Size             318769452 bytes

Database Buffers          109051904 bytes

Redo Buffers                6066176 bytes

-bash-3.2$ rman

RMAN> connect target

RMAN>  duplicate target database for standby nofilenamecheck dorecover;

Starting Duplicate Db at 24-NOV-11

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:

{

   set until scn  1041555;

   restore clone standby controlfile;

}

executing Memory Script

executing command: SET until clause

Starting restore at 24-NOV-11

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_24/o1_mf_n_768062841_7dw2x23z_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_24/o1_mf_n_768062841_7dw2x23z_.bkp tag=TAG20111124T144721

channel ORA_AUX_DISK_1: restored backup piece 1

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

output file name=/u01/app/oracle/oradata/orclstby/control01.ctl

output file name=/u01/app/oracle/flash_recovery_area/orclstby/control02.ctl

Finished restore at 24-NOV-11

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set until scn  1041555;

   set newname for tempfile  1 to

 "/u01/app/oracle/oradata/orclstby/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

"/u01/app/oracle/oradata/orclstby/system01.dbf";

   set newname for datafile  2 to

 "/u01/app/oracle/oradata/orclstby/sysaux01.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/orclstby/undotbs01.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/orclstby/users01.dbf";

   set newname for datafile  5 to

 "/u01/app/oracle/oradata/orclstby/example01.dbf";

   restore

   clone database

   ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/orclstby/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 24-NOV-11

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

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

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orclstby/system01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orclstby/sysaux01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orclstby/undotbs01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orclstby/users01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orclstby/example01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_nnndf_TAG20111124T144408_7dw2q1qv_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_nnndf_TAG20111124T144408_7dw2q1qv_.bkp tag=TAG20111124T144408

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:07

Finished restore at 24-NOV-11

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=3 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=4 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=6 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=7 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/example01.dbf

contents of Memory Script:

{

   set until scn  1041555;

   recover

   standby

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 24-NOV-11

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 22 is already on disk as file /u01/orcl_archives1_22_767810598.arc

archived log for thread 1 with sequence 23 is already on disk as file /u01/orcl_archives1_23_767810598.arc

archived log for thread 1 with sequence 24 is already on disk as file /u01/orcl_archives1_24_767810598.arc

archived log file name=/u01/orcl_archives1_22_767810598.arc thread=1 sequence=22

archived log file name=/u01/orcl_archives1_23_767810598.arc thread=1 sequence=23

archived log file name=/u01/orcl_archives1_24_767810598.arc thread=1 sequence=24

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

Finished recover at 24-NOV-11

Finished Duplicate Db at 24-NOV-11

RMAN>

Test Log Transport by executing command ALTER SYSTEM SWITCH LOGFILE on primary database and then checking SEQUENCE# at standby database

So , on standby database ::

SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;

SEQUENCE# FIRST_TIM NEXT_TIME

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

        25 24-NOV-11 24-NOV-11

        26 24-NOV-11 24-NOV-11

        27 24-NOV-11 24-NOV-11

SQL> select protection_mode from v$database;

PROTECTION_MODE

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

MAXIMUM PERFORMANCE

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

MOUNTED              PHYSICAL STANDBY

SQL> shu immediate

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  435224576 bytes

Fixed Size                  1337044 bytes

Variable Size             318769452 bytes

Database Buffers          109051904 bytes

Redo Buffers                6066176 bytes

Database mounted.

Database opened.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

READ ONLY            PHYSICAL STANDBY

Start the apply process on standby server

SQL>  alter database recover managed standby database disconnect from session;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

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

READ ONLY WITH APPLY PHYSICAL STANDBY

Basic explanation of COLD BACKUP and HOT BACKUP

A database is in CONSISTENT STATE when it shut down normally .

If database ARCHIVING is not enabled(NOARCHIVELOG) you must take a CONSISTENT backup

A database is in INCONSISTENT STATE when it is open and there are ongoing transactions AND it is shutdown abnormally .

If database ARCHIVING is enabled(NOARCHIVELOG) you take an INCONSISTENT backup.

------------A HOT BACKUP is one that is taken when database is OPEN and is possible only if the database is in ARCHIVELOG mode.

(PROBLEM)---HOT BACKUPS are performed at TABLESPACE level only.

EXAMPLE

SQL>ALTER TABLESPACE USERS BEGIN backup;

SQL> host cp /u01/app/oracle/oradata/orcl/users01.db /home/oracle/hotbackup

SQL> host cp /u01/app/oracle/oradata/orcl/users02.db  /home/oracle/hotbackup

SQL>ALTER TABLESPACE USERS END BACKUP;

---Next, force a log switch so Oracle will archive the current redo log file.(for redo generated during the backup)

          SQL>Alter system switch logfile;

Backup the CONTROL FILE  using the following command:

alter database backup controlfile to 'file_name';

-------------A COLD BACKUP is taken when database is CLOSED and is possible in both NOARCHIVELOG and ARCHIVELOG mode.

a))) To take a COLD BACKUP,database must be shutdown in a consistent manner.....by issuing one of        the following commands:

1)shutdown normal

2)shutdown transactional

If the database is shutdown with the IMMEDIATE or ABORT option, it should be restarted in RESTRICT mode and then shutdown with the NORMAL option.

b))) DETERMINE LOCATIONS (while database is startup)

       SQL> select value from v$parameter where name = 'spfile'; 

       SQL> select name from v$controlfile;

       SQL> select name from v$datafile;

After shutting down the database,copy the DATAFILES,CONTROL FILES,REDO LOG FILES, PASSWORD FILE and PARAMETER FILE of the database to your backup device using os 'copy' command.

     

c))) start up the database..

Block Change Tracking feature of RMAN

Optimized Incremental Backups

·         

     When you enable block change tracking, Oracle tracks the physical location of all database changes.

·        

     RMAN automatically uses the change tracking file to determine which blocks need to be read during an incremental backup and directly accesses that block to back it up.

·        

             When block change tracking is not enabled, then the entire datafile is read during each incremental backup to find and backup only the changed blocks, even if just a very small part of that file has changed since the previous backup. Use the following command to enable block change tracking.

·         

     The fact that block change tracking is available only with the enterprise edition.

·        

     Starting with Oracle 10g RMAN can take incremental backups without having to read the entire datafiles in order to find out which blocks have changed since the last backup.

·        

     This new feature is called FAST INCREMENTAL BACKUP.

·        

     The new technology used for this feature is called BLOCK CHANGE TRACKING.

·        

     When using Oracle block change tracking we see this procedure.  As data blocks change, the Change Tracking Writer (CTWR) background process tracks the changed blocks in a private area of memory. When a commit is issued against the data block, the block change tracking information is copied to a shared area in Large Pool called the CTWR buffer. During the checkpoint, the CTWR process writes the information from the CTWR RAM buffer to the change-tracking file.

·         

     After enabling change tracking, the first level 0 incremental backup still has to scan the entire datafile , as the change tracking file does not yet reflect the status of the blocks. Subsequent incremental backup that use this level 0 as parent will take advantage of the change tracking file.

SQL>show parameter create;

----check db_create_file_dest

mkdir -p /home/oracle/file_create

SQL> alter system set db_create_file_dest=’/home/oracle/file_create’;

System altered.

 ----Now we can enable block change tracking

SQL> alter database enable block change tracking;

Database altered.

Sql>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

SQL> desc v$block_change_tracking

SQL> select * from v$block_change_tracking;

By making incremental backups and the change tracking file part of your backup strategy you can

•  reduce the amount of time needed for daily backups.

•  save network bandwidth when backing up over a network.

•  reduce the backup file storage.  Depending on the number of      updated    blocks and the frequency of backups, an incremental backup will be smaller than a full database backup and consumes less storage.

•  enable fast backups of changed blocks.