Nothing but my learning and experience with Oracle..!: Col backup without redo log files and tempfiles - Is it really required to include redo and temp files in cold backup ?

Since temporary tablespaces don't hold any objects and are created in seconds, there's no reason to back them up. What about redo log files ?

A straight forward answer for the question will be, If you do not wish to do a RESETLOGS then, include redo logs in your cold backup.

In this example, I will take cold backup without my redo log files and tempfiles. Later I will restore my database using my cold backup.

Let's do some transaction to verify the data consistency after the restore operation.

SQL> select name from v$database ;

NAME
---------
ORCL

SQL> update emp set salary=10000;

107 rows updated.

SQL> commit;

Commit complete.

SQL> select sum(salary) from emp;

SUM(SALARY)
-----------
1070000

SQL>

SQL> select name from v$datafile ;

NAME
--------------------------------------------------------------------------------
E:\ORADATA\ORCL\SYSTEM01.DBF
E:\ORADATA\ORCL\EXAMPLE01.DBF
E:\ORADATA\ORCL\SYSAUX01.DBF
E:\ORADATA\ORCL\UNDOTBS01.DBF
E:\ORADATA\ORCL\USERS01.DBF

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
E:\ORADATA\ORCL\REDO03.LOG
E:\ORADATA\ORCL\REDO02.LOG
E:\ORADATA\ORCL\REDO01.LOG

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
E:\ORADATA\ORCL\CONTROL01.CTL
E:\FAST_RECOVERY_AREA\ORCL\CONTROL02.CTL

SQL> shut immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

E:\oradata>cd E:\ORADATA\

E:\oradata>mkdir ORADATA_BKP

In my cold backup,I am gonna to consider only my datafiles (but no temp files) and one of my controlfile from it's multiplexed location.

E:\oradata>copy E:\ORADATA\ORCL\SYSTEM01.DBF ORADATA_BKP\
1 file(s) copied.

E:\oradata>copy E:\ORADATA\ORCL\EXAMPLE01.DBF ORADATA_BKP\
1 file(s) copied.

E:\oradata>copy E:\ORADATA\ORCL\SYSAUX01.DBF ORADATA_BKP\
1 file(s) copied.

E:\oradata>copy E:\ORADATA\ORCL\UNDOTBS01.DBF ORADATA_BKP\
1 file(s) copied.

E:\oradata>copy E:\ORADATA\ORCL\USERS01.DBF ORADATA_BKP\
1 file(s) copied.

E:\oradata>copy E:\ORADATA\ORCL\CONTROL01.CT ORADATA_BKP\
The system cannot find the file specified.

E:\oradata>copy E:\ORADATA\ORCL\CONTROL01.CTL ORADATA_BKP\
1 file(s) copied.

E:\oradata>cd ORADATA_BKP\

E:\oradata\ORADATA_BKP>dir *
Volume in drive E is New Volume
Volume Serial Number is 0AF9-618D

Directory of E:\oradata\ORADATA_BKP

09/30/2017 05:34 PM <DIR> .
09/30/2017 05:34 PM <DIR> ..
09/30/2017 05:21 PM 10,141,696 CONTROL01.CTL
09/30/2017 05:21 PM 375,529,472 EXAMPLE01.DBF
09/30/2017 05:21 PM 786,440,192 SYSAUX01.DBF
09/30/2017 05:21 PM 859,840,512 SYSTEM01.DBF
09/30/2017 05:21 PM 760,225,792 UNDOTBS01.DBF
09/30/2017 05:21 PM 5,251,072 USERS01.DBF
6 File(s) 2,797,428,736 bytes
2 Dir(s) 42,555,953,152 bytes free

I have a cold backup of my database now ,So I will delete all my datafiles, redolog files and controlfiles.

E:\oradata\ORADATA_BKP>cd ../ORCL

E:\oradata\ORCL>dir
Volume in drive E is New Volume
Volume Serial Number is 0AF9-618D

Directory of E:\oradata\ORCL

09/30/2017 05:23 PM <DIR> .
09/30/2017 05:23 PM <DIR> ..
09/30/2017 05:21 PM 10,141,696 CONTROL01.CTL
09/30/2017 05:21 PM 375,529,472 EXAMPLE01.DBF
09/30/2017 05:21 PM 52,429,312 REDO01.LOG
09/30/2017 05:21 PM 52,429,312 REDO02.LOG
09/30/2017 05:21 PM 52,429,312 REDO03.LOG
09/30/2017 05:21 PM 786,440,192 SYSAUX01.DBF
09/30/2017 05:21 PM 859,840,512 SYSTEM01.DBF
09/30/2017 05:21 PM 67,117,056 TEMP01.DBF
09/30/2017 05:21 PM 760,225,792 UNDOTBS01.DBF
09/30/2017 05:21 PM 5,251,072 USERS01.DBF
10 File(s) 3,021,833,728 bytes
2 Dir(s) 42,555,953,152 bytes free

E:\oradata\ORCL>del *
E:\oradata\ORCL\*, Are you sure (Y/N)? Y

E:\oradata\ORCL>dir
Volume in drive E is New Volume
Volume Serial Number is 0AF9-618D

Directory of E:\oradata\ORCL

09/30/2017 05:37 PM <DIR> .
09/30/2017 05:37 PM <DIR> ..
0 File(s) 0 bytes
2 Dir(s) 45,577,797,632 bytes free

Deleted second copy of my control file.

E:\oradata\ORCL>del E:\FAST_RECOVERY_AREA\ORCL\CONTROL02.CTL

E:\oradata\ORCL>set ORACLE_SID=ORCL

E:\oradata\ORCL>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Sep 30 17:37:55 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2405760 bytes
Variable Size 507513472 bytes
Database Buffers 109051904 bytes
Redo Buffers 7356416 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Let's move my coldbackup to it's original location.

E:\oradata\ORCL>cd ../ORADATA_BKP

E:\oradata\ORADATA_BKP>dir
Volume in drive E is New Volume
Volume Serial Number is 0AF9-618D

Directory of E:\oradata\ORADATA_BKP

09/30/2017 05:34 PM <DIR> .
09/30/2017 05:34 PM <DIR> ..
09/30/2017 05:21 PM 10,141,696 CONTROL01.CTL
09/30/2017 05:21 PM 375,529,472 EXAMPLE01.DBF
09/30/2017 05:21 PM 786,440,192 SYSAUX01.DBF
09/30/2017 05:21 PM 859,840,512 SYSTEM01.DBF
09/30/2017 05:21 PM 760,225,792 UNDOTBS01.DBF
09/30/2017 05:21 PM 5,251,072 USERS01.DBF
6 File(s) 2,797,428,736 bytes
2 Dir(s) 45,587,349,504 bytes free

E:\oradata\ORADATA_BKP>move * ../ORCL
E:\oradata\ORADATA_BKP\CONTROL01.CTL
E:\oradata\ORADATA_BKP\EXAMPLE01.DBF
E:\oradata\ORADATA_BKP\SYSAUX01.DBF
E:\oradata\ORADATA_BKP\SYSTEM01.DBF
E:\oradata\ORADATA_BKP\UNDOTBS01.DBF
E:\oradata\ORADATA_BKP\USERS01.DBF
6 file(s) moved.

E:\oradata\ORADATA_BKP>cd ../ORCL

E:\oradata\ORCL>dir
Volume in drive E is New Volume
Volume Serial Number is 0AF9-618D

Directory of E:\oradata\ORCL

09/30/2017 05:41 PM <DIR> .
09/30/2017 05:41 PM <DIR> ..
09/30/2017 05:21 PM 10,141,696 CONTROL01.CTL
09/30/2017 05:21 PM 375,529,472 EXAMPLE01.DBF
09/30/2017 05:21 PM 786,440,192 SYSAUX01.DBF
09/30/2017 05:21 PM 859,840,512 SYSTEM01.DBF
09/30/2017 05:21 PM 760,225,792 UNDOTBS01.DBF
09/30/2017 05:21 PM 5,251,072 USERS01.DBF
6 File(s) 2,797,428,736 bytes
2 Dir(s) 45,587,349,504 bytes free

E:\oradata\ORCL>copy CONTROL01.CTL E:\FAST_RECOVERY_AREA\ORCL\CONTROL02.CTL
1 file(s) copied.

E:\oradata\ORCL>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Sep 30 17:42:52 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter database mount ;

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
E:\ORADATA\ORCL\CONTROL01.CTL
E:\FAST_RECOVERY_AREA\ORCL\CONTROL02.CTL

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3788
Session ID: 242 Serial number: 3

Alertlog clearly shows it can't identify the redo log member.

O/S-Error: (OS 2) The system cannot find the file specified.
Starting background process TMON
TMON started with pid=24, OS id=4300
LGWR: STARTING ARCH PROCESSES
Starting background process ARC0
ARC0 started with pid=25, OS id=4696
Errors in file E:\APP\MAHI\diag\rdbms\orcl\orcl\trace\orcl_m000_2880.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'E:\ORADATA\ORCL\REDO02.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
2017-09-30 17:43:42.981000 +05:30
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Errors in file E:\APP\MAHI\diag\rdbms\orcl\orcl\trace\orcl_lgwr_2464.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'E:\ORADATA\ORCL\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file E:\APP\MAHI\diag\rdbms\orcl\orcl\trace\orcl_lgwr_2464.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'E:\ORADATA\ORCL\REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file E:\APP\MAHI\diag\rdbms\orcl\orcl\trace\orcl_ora_3788.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: 'E:\ORADATA\ORCL\REDO01.LOG'
System state dump requested by (instance=1, osid=3788), summary=[abnormal instance termination].
System State dumped to trace file E:\APP\MAHI\diag\rdbms\orcl\orcl\trace\orcl_diag_4988.trc
ARC0: STARTING ARCH PROCESSES
2017-09-30 17:43:44.624000 +05:30
Dumping diagnostic data in directory=[cdmp_20170930174343], requested by (instance=1, osid=3788), summary=[abnormal instance termination].
2017-09-30 17:43:46.297000 +05:30
USER (ospid: 3788): terminating the instance due to error 313
2017-09-30 17:43:51.373000 +05:30
Instance terminated by USER, pid = 3788

So let's try to open our database in resetlogs mode.

E:\oradata\ORCL>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Sep 30 17:48:38 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2405760 bytes
Variable Size 507513472 bytes
Database Buffers 109051904 bytes
Redo Buffers 7356416 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

As we all know, it's not possible to open a database in resetlogs without doing a incomplete recovery. So try to mimic it.

SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL>

Yes, we could able to start our database in resetlogs mode. The resetlogs command recreate any redologs file that's physically not exist.

E:\oradata\ORCL>dir
Volume in drive E is New Volume
Volume Serial Number is 0AF9-618D

Directory of E:\oradata\ORCL

09/30/2017 05:51 PM <DIR> .
09/30/2017 05:51 PM <DIR> ..
09/30/2017 06:30 PM 10,141,696 CONTROL01.CTL
09/30/2017 05:51 PM 375,529,472 EXAMPLE01.DBF
09/30/2017 05:51 PM 52,429,312 REDO01.LOG
09/30/2017 05:51 PM 52,429,312 REDO02.LOG
09/30/2017 05:51 PM 52,429,312 REDO03.LOG
09/30/2017 05:51 PM 786,440,192 SYSAUX01.DBF
09/30/2017 05:51 PM 859,840,512 SYSTEM01.DBF
09/30/2017 05:51 PM 67,117,056 TEMP01.DBF
09/30/2017 05:51 PM 760,225,792 UNDOTBS01.DBF
09/30/2017 05:51 PM 5,251,072 USERS01.DBF
10 File(s) 3,021,833,728 bytes
2 Dir(s) 45,341,138,944 bytes free.

I learned something new today- by analyzing the alertlog file,I could see oracle recreated all missing TEMP files as part of resetlogs operation - Great.

ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: 'E:\ORADATA\ORCL\TEMP01.DBF'
File 201 not verified due to error ORA-01157
Re-creating tempfile E:\ORADATA\ORCL\TEMP01.DBF
Starting background process SMCO

SQL> select sum(salary) from emp;

SUM(SALARY)
-----------
1070000
SQL>

Nothing but my learning and experience with Oracle..!

Pages

Friday, September 29, 2017

Col backup without redo log files and tempfiles - Is it really required to include redo and temp files in cold backup ?

No comments:

Post a Comment

Popular Posts