Begain backup concepts

Following internal operations happened with immediate effect after triggering begin backup for tablespace

1. Partial check point (for all blocks part of the subjected tablespace)
2. Headers of datafiles would be freeze while datafile blocks would be available for normal access
3. The first time a block is changed in a datafile that is in hot backup mode, the entire block is written to the redo log files, not just the change vector. Normally only the changed bytes (a change vector) are written. In hot backup mode, the entire block is logged the first time (successive updates against that block would be logged as usual change vector only). This is because you can get into a situation where the process copying the datafile and DBWR are working on the same block simultaneously.
4. The datafile headers which contain the SCN of the last completed checkpoint are not updated while a file is in hot backup mode. This lets the recovery process understand what archive redo log files might be needed to fully recover this file.
5. Once you end backup the datafile headers would be unfreeze and synced with current SCN from control file.
let's confirm that the datafile header freeze  during hot backup

SQL> alter database begin backup;

alter database begin backup

*

ERROR at line 1:

ORA-01123: cannot start online backup; media recovery not enabled

Alter database begin backup ;

!cp /home/oracle/10.2.0/product/oradata/orcl/users01.dbf   /home/oracle/backup/users01.dbf

!cp /home/oracle/10.2.0/product/oradata/orcl/sysaux01.dbf   /home/oracle/backup/sysaux01.dbf

!cp /home/oracle/10.2.0/product/oradata/orcl/undotbs01.dbf  /home/oracle/backup/undotbs01.dbf

!cp /home/oracle/10.2.0/product/oradata/orcl/system01.dbf   /home/oracle/backup/system01.dbf

!cp /home/oracle/10.2.0/product/oradata/orcl/example01.dbf   /home/oracle/backup/example01.dbf

!cp /home/oracle/10.2.0/product/oradata/orcl/mytbs.dbf      /home/oracle/backup/mytbs.dbf

  

Can check  datafile backup status using below view

SQL>  select file#,status from v$backup;

     FILE# STATUS

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

         1      ACTIVE

         2      ACTIVE

         3      ACTIVE

         4      ACTIVE

         5      ACTIVE

         6      ACTIVE

6 rows selected.

 Now we connect to the user and run insert statement 

SQL> conn scott/tiger

Connected.

SQL> insert into abc select * from abc;

809232 rows created.

SQL> commit;

check the CHECKPOINT_CHANGE# from v$datafile_header

SQL>  select CREATION_CHANGE#, RESETLOGS_CHANGE#,CHECKPOINT_CHANGE# from v$datafile_header;

CREATION_CHANGE# RESETLOGS_CHANGE# CHECKPOINT_CHANGE#

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

               8            525876            1730264

          523586            525876            1730264

            6294            525876            1730264

           10169            525876            1730264

          547523            525876            1730264

         1720920            525876            1730264

6 rows selected.

SQL> alter system checkpoint;

System altered.

Check the CHECKPOINT_CHANGE# from v$datafile_header after fired checkpoint manually  but it's still same.

SQL>  select CREATION_CHANGE#, RESETLOGS_CHANGE#,CHECKPOINT_CHANGE# from v$datafile_header;

CREATION_CHANGE# RESETLOGS_CHANGE# CHECKPOINT_CHANGE#

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

               8            525876            1730264

          523586            525876            1730264

            6294            525876            1730264

           10169            525876            1730264

          547523            525876            1730264

         1720920            525876            1730264

6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL>  select CREATION_CHANGE#, RESETLOGS_CHANGE#,CHECKPOINT_CHANGE# from v$datafile_header;

CREATION_CHANGE# RESETLOGS_CHANGE# CHECKPOINT_CHANGE#

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

               8            525876            1730264

          523586            525876            1730264

            6294            525876            1730264

           10169            525876            1730264

          547523            525876            1730264

         1720920            525876            1730264

6 rows selected.

SQL> alter system checkpoint;

System altered.

Again we verified the  CHECKPOINT_CHANGE#   after fire checkpoint manually  but there is no change .

SQL>  select CREATION_CHANGE#, RESETLOGS_CHANGE#,CHECKPOINT_CHANGE# from v$datafile_header;

CREATION_CHANGE# RESETLOGS_CHANGE# CHECKPOINT_CHANGE#

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

               8            525876            1730264

          523586            525876            1730264

            6294            525876            1730264

           10169            525876            1730264

          547523            525876            1730264

         1720920            525876            1730264

6 rows selected.

After end backup when I chek again  now CREATION_CHANGE#  has been changed .

SQL> select CREATION_CHANGE#, RESETLOGS_CHANGE#,CHECKPOINT_CHANGE# from v$datafile_header;

CREATION_CHANGE# RESETLOGS_CHANGE# CHECKPOINT_CHANGE#

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

               8            525876            1736138

          523586            525876            1736138

            6294            525876            1736138

           10169            525876            1736138

          547523            525876            1736138

         1720920            525876            1736138

6 rows selected.

Change database in archive or unarchive mode

Unachieved database

Bring database in mount state first.

SQL>  shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>  startup  mount;

ORACLE instance started.

Total System Global Area  910163968 bytes

Fixed Size                  2024976 bytes

Variable Size             255855088 bytes

Database Buffers          645922816 bytes

Redo Buffers                6361088 bytes

Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     37

Current log sequence           39

Achieved database

Bring database in mount state first.

SQL>  startup  mount;

ORACLE instance started.

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     37

Current log sequence           39

SQL> alter database archivelog;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     37

Next log sequence to archive   39

Current log sequence           39

SQL> alter database open;

Set archive destination

SQL> alter  system  set LOG_ARCHIVE_DEST_1='location=/home/oracle';

System altered.

You can also set FRA as archive destination

SQL>  alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

System altered.

If you writing syntax wrong

LOG_ARCHIVE_DEST_1=USE_DB_RECOVERY_FILE_DEST scope=both;

 alter  system  set LOG_ARCHIVE_DEST_1=USE_DB_RECOVERY_FILE_DEST scope=both

*

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

You can check FRA used and free size using below query

SQL>  SELECT FILE_TYPE "Type",

  2  PERCENT_SPACE_USED "% Used",

  3  PERCENT_SPACE_RECLAIMABLE "% Reclaim",

  4  NUMBER_OF_FILES "# Files"

  5  FROM V$FLASH_RECOVERY_AREA_USAGE;

Type             % Used  % Reclaim    # Files

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

CONTROLFILE           0          0          0

ONLINELOG             0          0          0

ARCHIVELOG        11.47          0         10

BACKUPPIECE           0          0          0

IMAGECOPY             0          0          0

FLASHBACKLOG          0          0          0

View to see archiving related  information’s.

V$DATABASE

Identifies whether the database is in ARCHIVELOG or NOARCHIVELOG mode and whether MANUAL (archiving mode) has been specified.

V$ARCHIVED_LOG

Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.

V$ARCHIVE_DEST

Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.

V$ARCHIVE_PROCESSES

Displays information about the state of the various archive processes for an instance.

V$BACKUP_REDOLOG

Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.

V$LOG

Displays all redo log groups for the database and indicates which need to be archived.

V$LOG_HISTORY

Contains log history information such as which logs have been archived and the SCN range for each archived log.

v$log , v$logfile, v$log_history DD views

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

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

         1          1         38   52428800          1 NO  INACTIVE               1678937 07-APR-13

         2          1         39   52428800          1 NO  CURRENT                1708375 08-APR-13

         3          1         37   52428800          1 NO  INACTIVE               1641414 07-APR-13

GROUP#                    Log group 

THREAD#                  Log thread 

SEQUENCE#             Log sequence 

BYTES                       Size of the log (in bytes)

MEMBERS                  of members in the log group

ARCHIVED    VARCHAR2(3)           Archive status (YES or NO)

STATUS                      Log status:

                 UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or  just after a RESETLOGS, when it is not the current redo log.

                CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.

                ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for   block recovery. It may or may not be archived.

                 CLEARING - Log is being re-created as an empty log after an

 ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.

                CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there                 is some failure in the switch such as an I/O error writing the new log header.

              INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or                might not be archived.

             INVALIDATED - Archived the current redo log without a log switch.

              FIRST_CHANGE#              Lowest system change   (SCN) in the log

              FIRST_TIME            DATE  Time of the first SCN in the log

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                                                                IS_

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

         3         ONLINE  /home/oracle/10.2.0/product/oradata/orcl/redo03.log                                                   NO

         2         ONLINE  /home/oracle/10.2.0/product/oradata/orcl/redo02.log                                                   NO

         1         ONLINE  /home/oracle/10.2.0/product/oradata/orcl/redo01.log                                                   NO

GROUP#                     Redo log group identifier number

STATUS                      Status of the log member:

                        INVALID - File is inaccessible

                       STALE - File's contents are incomplete

                     DELETED - File is no longer used

                    null - File is in use

TYPE               Type of the logfile:

               ONLINE

               STANDBY

MEMBER                    R      edo log member name

IS_RECOVERY_DEST_FILE                        Indicates whether the file was created in the flash recovery area (YES) or not (NO)

SQL> select * from v$log_history;

     RECID      STAMP    THREAD#  SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS

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

         1  810903173          1          1        525876 24-MAR-13       556543            525876 24-MAR-13

         2  811006590          1          2        556543 24-MAR-13       615576            525876 24-MAR-13

         3  811020025          1          3        615576 25-MAR-13       636527            525876 24-MAR-13

         4  811029938          1          4        636527 25-MAR-13       656691            525876 24-MAR-13

RECID             Control file record ID

STAMP                       Control file record stamp

THREAD#               Thread   of the archived log

SEQUENCE#  Sequence   of the archived log

FIRST_CHANGE#     Lowest system change   (SCN) in the log

FIRST_TIME  Time of the first entry (lowest SCN) in the log

NEXT_CHANGE#      Highest SCN in the log

RESETLOGS_CHANGE#      Resetlogs change   of the database when the log was written

RESETLOGS_TIME               Resetlogs time of the database when the log was written