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.