How to Enter BEGIN BACKUP Mode

  • by

ALTER DATABASE BEGIN BACKUP

From Oracle 10g onward, you can make the whole database enter BEGIN BACKUP mode in one statement in order to get a consistent full backup of the database:

SQL> alter database begin backup;

Database altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                2829689 20-DEC-16
         2 ACTIVE                2829689 20-DEC-16
         3 ACTIVE                2829689 20-DEC-16
         4 ACTIVE                2829689 20-DEC-16
         5 ACTIVE                2829689 20-DEC-16
         6 ACTIVE                2829689 20-DEC-16
         7 ACTIVE                2829689 20-DEC-16
         8 ACTIVE                2829689 20-DEC-16

8 rows selected.

SQL> alter database end backup;

Database altered.

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

That's easy and will be no problem. All data files stay at the same SCN, i.e. 2829689 in this case.

ALTER TABLESPACE BEGIN BACKUP

But for 8i or 9i, there's no database-level BACKUP mode. You have to do it on tablespace-level one by one. That is to say, the time entering BEGIN BACKUP mode will be different among tablespaces. This will become a drawback for those who wants to perform user-managed backups or OS snapshots. I will talk about it later.

Here in this post, I would like to introduce some scripts to facilitate our maintenance tasks for Oracle 9i database. With shell scripts' help, we can do it automatically or put it in the crontab.

Scripts Equivalent to ALTER DATABASE BEGIN BACKUP

First of all, we compose a SQL statement for checking backup mode.

[oracle@oracle9i ~]$ cat check_backup_mode.sql
set heading on time off timing off
column tablespace format a20;
column file# format 999;
column status format a10;
column change# format 9999999;
select t.name tablespace, d.file#, b.status, b.change# from v$backup b inner join v$datafile d on b.file# = d.file# inner join v$tablespace t on d.ts# = t.ts#;

Next, we compose a script to make the database enter BEGIN BACKUP mode.

[oracle@oracle9i ~]$ cat begin_backup.sh
#!/bin/bash
sqlplus -s /nolog << EOF
conn / as sysdba
set hea off echo off feedback off pagesize 0
spool begin_backup.sql
select 'alter tablespace ' || tablespace_name || ' begin backup;' from dba_tablespaces where contents <> 'TEMPORARY';
spool off
set echo on feedback on time on timing on
@begin_backup.sql
@check_backup_mode.sql
EOF

Third, we compose a script to make the database out of this mode by END BACKUP.

[oracle@oracle9i ~]$ cat end_backup.sh
#!/bin/bash
sqlplus -s /nolog << EOF
conn / as sysdba
set hea off echo off feedback off pagesize 0
spool end_backup.sql
select 'alter tablespace ' || tablespace_name || ' end backup;' from dba_tablespaces where contents <> 'TEMPORARY';
spool off
set echo on feedback on time on timing on
@end_backup.sql
@check_backup_mode.sql
EOF

Let's test and run the scripts. To make the whole database enter BEGIN BACKUP mode, we execute begin_backup.sh.

[oracle@oracle9i ~]$ ./begin_backup.sh
Connected.
alter tablespace SYSTEM begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace CWMLITE begin backup;
alter tablespace DRSYS begin backup;
alter tablespace EXAMPLE begin backup;
alter tablespace INDX begin backup;
alter tablespace ODM begin backup;
alter tablespace TOOLS begin backup;
alter tablespace USERS begin backup;
alter tablespace XDB begin backup;

Tablespace altered.

Elapsed: 00:00:00.02

Tablespace altered.

Elapsed: 00:00:00.00

Tablespace altered.

Elapsed: 00:00:00.01

Tablespace altered.

Elapsed: 00:00:00.01

Tablespace altered.

Elapsed: 00:00:00.01

Tablespace altered.

Elapsed: 00:00:00.01

Tablespace altered.

Elapsed: 00:00:00.00

Tablespace altered.

Elapsed: 00:00:00.01

Tablespace altered.

Elapsed: 00:00:00.00

Tablespace altered.

Elapsed: 00:00:00.01
SYSTEM                   1 ACTIVE       971380
UNDOTBS1                 2 ACTIVE       971384
CWMLITE                  3 ACTIVE       971388
DRSYS                    4 ACTIVE       971392
EXAMPLE                  5 ACTIVE       971396
INDX                     6 ACTIVE       971399
ODM                      7 ACTIVE       971402
TOOLS                    8 ACTIVE       971405
USERS                    9 ACTIVE       971408
XDB                     10 ACTIVE       971411

10 rows selected.

Did you see that? The SCN among data files are all different, this is what we expect, but not what we want. It will leave the data files inconsistent with each other because their SCN are different according to their time of entering BEGIN BACKUP mode.

To make the whole database back to normal, we execute end_backup.sh.

[oracle@oracle9i ~]$ ./end_backup.sh
Connected.
alter tablespace SYSTEM end backup;
alter tablespace UNDOTBS1 end backup;
alter tablespace CWMLITE end backup;
alter tablespace DRSYS end backup;
alter tablespace EXAMPLE end backup;
alter tablespace INDX end backup;
alter tablespace ODM end backup;
alter tablespace TOOLS end backup;
alter tablespace USERS end backup;
alter tablespace XDB end backup;

Tablespace altered.

Elapsed: 00:00:00.01

Tablespace altered.

Elapsed: 00:00:00.00

Tablespace altered.

Elapsed: 00:00:00.01

Tablespace altered.

Elapsed: 00:00:00.00

Tablespace altered.

Elapsed: 00:00:00.01

Tablespace altered.

Elapsed: 00:00:00.00

Tablespace altered.

Elapsed: 00:00:00.01

Tablespace altered.

Elapsed: 00:00:00.00

Tablespace altered.

Elapsed: 00:00:00.01

Tablespace altered.

Elapsed: 00:00:00.00
SYSTEM                   1 NOT ACTIVE   971380
UNDOTBS1                 2 NOT ACTIVE   971384
CWMLITE                  3 NOT ACTIVE   971388
DRSYS                    4 NOT ACTIVE   971392
EXAMPLE                  5 NOT ACTIVE   971396
INDX                     6 NOT ACTIVE   971399
ODM                      7 NOT ACTIVE   971402
TOOLS                    8 NOT ACTIVE   971405
USERS                    9 NOT ACTIVE   971408
XDB                     10 NOT ACTIVE   971411

10 rows selected.

How can we deal with those inconsistent data files? I recommend that you flush an online redo log into an archived log once your backup was complete.

SQL> alter system archive log current;

And then backup this archive log as well. Because you may need to recover a little further to make data files consistent once you needed them. In my case, I sometimes restore them to a test database. The database always needs to be recovered a little bit.

Leave a Reply

Your email address will not be published. Required fields are marked *