Wednesday, August 10, 2016

Making a Hot Backup and Doing an Incomplete Recovery

This post shows how to do a hot backup followed by an incomplete recovery. I ran it on an Oracle 11.2.0.4 test database. First I checked that the database was in ARCHIVELOG mode:

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>


Then I decided where to copy the hot backup.

The directory listing below shows a sub-directory called backup. This contains a cold backup I made earlier in case the test goes horribly wrong and I need to start again.

After this, you can see the database’s 3 control files. In a real life situation, these would be on separate physical disks but this is only a test so having them in the same place is OK.

Next comes a directory called hot_backup. The hot backup will go in here.

Finally you can see the database’s redo logs and datafiles:


GBASRDB1 /database/ANDREW/DB1 > ls -l
total 6585680
drwxr-xr-x   2 oracle   dba         4096 Jun  3 17:51 backup
-rw-r-----   1 oracle   dba      11517952 Aug  9 16:59 control01.ctl
-rw-r-----   1 oracle   dba      11517952 Aug  9 16:59 control02.ctl
-rw-r-----   1 oracle   dba      11517952 Aug  9 16:59 control03.ctl
drwxr-xr-x   2 oracle   dba         4096 Jun  7 18:40 hot_backup
-rw-r-----   1 oracle   dba      52429312 Aug  9 16:50 redo01a.log
-rw-r-----   1 oracle   dba      52429312 Aug  9 16:50 redo02a.log
-rw-r-----   1 oracle   dba      52429312 Aug  9 16:59 redo03a.log
-rw-r-----   1 oracle   dba      796925952 Aug  9 16:55 sysaux01.dbf
-rw-r-----   1 oracle   dba      796925952 Aug  9 16:58 system01.dbf
-rw-r-----   1 oracle   dba      524296192 Aug  9 16:50 temp01.dbf
-rw-r-----   1 oracle   dba      524296192 Aug  9 16:55 undotbs01.dbf
-rw-r-----   1 oracle   dba      524296192 Aug  9 16:50 users01.dbf
GBASRDB1 /database/ANDREW/DB1 >


I identified the files to backup:

SQL> l
  1  select tablespace_name, file_name
  2  from dba_data_files
  3* order by 1,2
SQL> /

TABLESPACE_NAME FILE_NAME
--------------- -----------------------------------
SYSAUX          /database/ANDREW/DB1/sysaux01.dbf
SYSTEM          /database/ANDREW/DB1/system01.dbf
UNDOTBS1        /database/ANDREW/DB1/undotbs01.dbf
USERS           /database/ANDREW/DB1/users01.dbf

SQL>


... and checked the sequence number of the current redo log:

SQL> l
  1  select group#, status, sequence#
  2  from v$log
  3* order by 1
SQL> /

    GROUP# STATUS            SEQUENCE#
---------- ---------------- ----------
         1 INACTIVE               1423
         2 CURRENT                1424
         3 INACTIVE               1422

SQL>


I put the database into hot backup mode:

SQL> alter database begin backup
  2  /

Database altered.

SQL>


I copied the datafiles into the hot_backup directory:

GBASRDB1 /database/ANDREW/DB1 > cp sysaux01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 > cp system01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 > cp undotbs01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 > cp users01.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 >


I created a marker table:

SQL> create table system.andrew_was_here(col1 number)
  2  /

Table created.

SQL> insert into system.andrew_was_here values(1234567)
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL>


I took the database out of hot backup mode:

SQL> alter database end backup
  2  /

Database altered.

SQL>
 


I archived any outstanding redo and forced a log switch:

SQL> alter system archive log current
  2  /

System altered.

SQL>


I checked the sequence number of the current redo log:

SQL> l
  1  select group#, status, sequence#
  2  from v$log
  3* order by 1
SQL> /

    GROUP# STATUS            SEQUENCE#
---------- ---------------- ----------
         1 INACTIVE               1423
         2 ACTIVE                 1424
         3 CURRENT                1425

SQL>


All the redo needed for recovery should be in log no 1424 so I copied this into the hot_backup directory: 


GBASRDB1 /database/ANDREW/DB1 > cp 1_1424_913830195.dbf hot_backup
GBASRDB1 /database/ANDREW/DB1 >
 


Finally, I took a backup of the controlfile:

SQL> alter database backup controlfile
  2  to '/database/ANDREW/DB1/hot_backup/bkup.ctl'
  3  /

Database altered.

SQL>


As a first example, I decided to restore to this hot backup so I deleted the database’s datafiles, control files and online redo logs. Obviously, this loses any changes made after the backup:

GBASRDB1 /database/ANDREW/DB1 > rm *
rm: backup is a directory
rm: hot_backup is a directory
GBASRDB1 /database/ANDREW/DB1 > ls
backup      hot_backup
GBASRDB1 /database/ANDREW/DB1 >
 


Then I closed the database:

GBASRDB1 /database/ANDREW/DB1 > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 10 17:39:59 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL>


I restored the files from the hot_backup directory and made sure there were 3 control files again:

GBASRDB1 /database/ANDREW/DB1 > cp hot_backup/* .
GBASRDB1 /database/ANDREW/DB1 > ls
1_1424_913830195.dbf  bkup.ctl              sysaux01.dbf          undotbs01.dbf
backup                hot_backup            system01.dbf          users01.dbf
GBASRDB1 /database/ANDREW/DB1 > mv bkup.ctl control01.ctl
GBASRDB1 /database/ANDREW/DB1 > cp control01.ctl control02.ctl
GBASRDB1 /database/ANDREW/DB1 > cp control01.ctl control03.ctl
GBASRDB1 /database/ANDREW/DB1 >


I mounted the database:

GBASRDB1 /database/ANDREW/DB1 > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 10 18:02:36 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2252448 bytes
Variable Size             322961760 bytes
Database Buffers          188743680 bytes
Redo Buffers                7979008 bytes
Database mounted.
SQL>


I recovered the database using the backup controlfile, applying all the archived redo at my disposal:

SQL> recover database using backup controlfile until cancel
ORA-00279: change 4101278 generated at 08/09/2016 17:47:29 needed for thread 1
ORA-00289: suggestion : /database/ANDREW/DB1/1_1424_913830195.dbf
ORA-00280: change 4101278 for thread 1 is in sequence #1424

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 4102126 generated at 08/09/2016 18:06:23 needed for thread 1
ORA-00289: suggestion : /database/ANDREW/DB1/1_1425_913830195.dbf
ORA-00280: change 4102126 for thread 1 is in sequence #1425
ORA-00278: log file '/database/ANDREW/DB1/1_1424_913830195.dbf' no longer
needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>


I opened the database:

SQL> alter database open resetlogs
  2  /

Database altered.

SQL>
 


... and checked that my table was still there: 

SQL> select * from system.andrew_was_here
  2  /

      COL1
----------
   1234567

SQL>
 


In the process, Oracle recreated the online redo log files for me:

SQL> select group#, sequence#, status
  2  from v$log
  3  /

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          0 UNUSED
         3          0 UNUSED

SQL>

Monday, July 18, 2016

ORA-39710 and ORA-00704

I tried to use dbua to upgrade a database from Oracle 11.2.0.4 to Oracle 12.1.0.2. Part way through, my PC lost connection with the UNIX server hosting the database. I tried to connect to the database but got an ORA-39710 so I forced the database to close with shutdown abort:

NLFINUT1 /export/home/oracle > sqlplus /

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 18 14:03:08 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-39710: only connect AS SYSDBA is allowed when OPEN in UPGRADE mode

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL>


A colleague restored the database for me. dbua had updated /var/opt/oracle/oratab to show the new database version so I changed it back to 11.2.0.4:

NLFINUT1:/oracle/app/oracle/product/11.2.0.4:N

However, I forgot to rerun . oraenv to pick up the old Oracle software so when I tried to open the database, I got an ORA-00704:

NLFINUT1 /export/home/oracle > sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 18 14:13:38 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  650117120 bytes
Fixed Size                  2917400 bytes
Variable Size             230693864 bytes
Database Buffers          411041792 bytes
Redo Buffers                5464064 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier
Process ID: 21064
Session ID: 212 Serial number: 60960

SQL>


I reran . oraenv then I was able to open the database:

NLFINUT1 /export/home/oracle > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 18 14:52:08 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  622338048 bytes
Fixed Size                  2184112 bytes
Variable Size             327158864 bytes
Database Buffers          285212672 bytes
Redo Buffers                7782400 bytes
Database is aangekoppeld.
Database is geopend.
SQL>


(It is used in the Netherlands so some of the messages are in Dutch.)