To backup all datafiles online with user-managed mode, you can force the all datafiles to be consistent and stop writing changes into data blocks by having all datafiles into the active backup mode.
Check backup status of all datafiles before entering backup mode.
SQL> column tb_name format a10; SQL> column df# format 99; SQL> column df_name format a50; SQL> column status format a10; SQL> SELECT t.name AS "TB_NAME", 2 d.file# AS "DF#", 3 d.name AS "DF_NAME", 4 b.status 5 FROM V$DATAFILE d, 6 V$TABLESPACE t, 7 V$BACKUP b 8 WHERE d.TS#=t.TS# 9 AND b.FILE#=d.FILE#;
TB_NAME DF# DF_NAME STATUS ---------- --- -------------------------------------------------- ---------- SYSTEM 1 /u01/app/oracle/oradata/smalldb/system01.dbf NOT ACTIVE SYSAUX 2 /u01/app/oracle/oradata/smalldb/sysaux01.dbf NOT ACTIVE UNDOTBS1 3 /u01/app/oracle/oradata/smalldb/undotbs01.dbf NOT ACTIVE USERS 4 /u01/app/oracle/oradata/smalldb/users01.dbf NOT ACTIVE
Alter database with BEGIN BACKUP to enter the active backup mode.
SQL> alter database begin backup;
Database altered.
SQL> SELECT t.name AS "TB_NAME", 2 d.file# AS "DF#", 3 d.name AS "DF_NAME", 4 b.status 5 FROM V$DATAFILE d, 6 V$TABLESPACE t, 7 V$BACKUP b 8 WHERE d.TS#=t.TS# 9 AND b.FILE#=d.FILE#;
TB_NAME DF# DF_NAME STATUS ---------- --- -------------------------------------------------- ---------- SYSTEM 1 /u01/app/oracle/oradata/smalldb/system01.dbf ACTIVE SYSAUX 2 /u01/app/oracle/oradata/smalldb/sysaux01.dbf ACTIVE UNDOTBS1 3 /u01/app/oracle/oradata/smalldb/undotbs01.dbf ACTIVE USERS 4 /u01/app/oracle/oradata/smalldb/users01.dbf ACTIVE
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch Oldest online log sequence 21 Next log sequence to archive 23 Current log sequence 23
Make a user-managed backup by copying all datafiles.
[oracle@primary01 smalldb]$ sqlplus / as sysdba ... SQL> alter database end backup;
Database altered.
SQL> column tb_name format a10; SQL> column df# format 99; SQL> column df_name format a50; SQL> column status format a10; SQL> SELECT t.name AS "TB_NAME", 2 d.file# AS "DF#", 3 d.name AS "DF_NAME", 4 b.status 5 FROM V$DATAFILE d, 6 V$TABLESPACE t, 7 V$BACKUP b 8 WHERE d.TS#=t.TS# 9 AND b.FILE#=d.FILE#;
TB_NAME DF# DF_NAME STATUS ---------- --- -------------------------------------------------- ---------- SYSTEM 1 /u01/app/oracle/oradata/smalldb/system01.dbf NOT ACTIVE SYSAUX 2 /u01/app/oracle/oradata/smalldb/sysaux01.dbf NOT ACTIVE UNDOTBS1 3 /u01/app/oracle/oradata/smalldb/undotbs01.dbf NOT ACTIVE USERS 4 /u01/app/oracle/oradata/smalldb/users01.dbf NOT ACTIVE
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch Oldest online log sequence 21 Next log sequence to archive 23 Current log sequence 23
Do some switch logfile to force the log sequence number incremented.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered. ... SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch Oldest online log sequence 36 Next log sequence to archive 38 Current log sequence 38
Restore all datafiles from the user-managed backup by copying all the datafiles back to the database.
Open the database, but it failed as expected, because all the datafiles are not consistent with the controlfile.
[oracle@primary01 smalldb]$ srvctl start database -d smalldb PRCR-1079 : Failed to start resource ora.smalldb.db ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/u01/app/oracle/oradata/smalldb/system01.dbf'
CRS-2674: Start of 'ora.smalldb.db' on 'primary01' failed CRS-2632: There are no more servers to try to place resource 'ora.smalldb.db' on that would satisfy its placement policy
Recover the database
[oracle@primary01 smalldb]$ srvctl start database -d smalldb -o mount [oracle@primary01 smalldb]$ sqlplus / as sysdba .... SQL> recover database; ORA-00279: change 1092395 generated at 02/05/2013 15:21:36 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_23_799174213.dbf ORA-00280: change 1092395 for thread 1 is in sequence #23
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 1092820 generated at 02/05/2013 15:29:13 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_24_799174213.dbf ORA-00280: change 1092820 for thread 1 is in sequence #24
ORA-00279: change 1092823 generated at 02/05/2013 15:29:14 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_25_799174213.dbf ORA-00280: change 1092823 for thread 1 is in sequence #25
ORA-00279: change 1092826 generated at 02/05/2013 15:29:17 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_26_799174213.dbf ORA-00280: change 1092826 for thread 1 is in sequence #26
ORA-00279: change 1092829 generated at 02/05/2013 15:29:17 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_27_799174213.dbf ORA-00280: change 1092829 for thread 1 is in sequence #27
ORA-00279: change 1092833 generated at 02/05/2013 15:29:22 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_28_799174213.dbf ORA-00280: change 1092833 for thread 1 is in sequence #28
ORA-00279: change 1092836 generated at 02/05/2013 15:29:22 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_29_799174213.dbf ORA-00280: change 1092836 for thread 1 is in sequence #29
ORA-00279: change 1092839 generated at 02/05/2013 15:29:25 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_30_799174213.dbf ORA-00280: change 1092839 for thread 1 is in sequence #30
ORA-00279: change 1092842 generated at 02/05/2013 15:29:25 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_31_799174213.dbf ORA-00280: change 1092842 for thread 1 is in sequence #31
ORA-00279: change 1092853 generated at 02/05/2013 15:29:35 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_32_799174213.dbf ORA-00280: change 1092853 for thread 1 is in sequence #32
ORA-00279: change 1092856 generated at 02/05/2013 15:29:35 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_33_799174213.dbf ORA-00280: change 1092856 for thread 1 is in sequence #33
ORA-00279: change 1092859 generated at 02/05/2013 15:29:38 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_34_799174213.dbf ORA-00280: change 1092859 for thread 1 is in sequence #34
ORA-00279: change 1092862 generated at 02/05/2013 15:29:38 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch/1_35_799174213.dbf ORA-00280: change 1092862 for thread 1 is in sequence #35
Log applied. Media recovery complete.
Open the database.
SQL> alter database open;
Database altered.
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch Oldest online log sequence 36 Next log sequence to archive 38 Current log sequence 38