Category Archives: backup/recovery

12c, backup/recovery

Oracle 12c – Disable Flashback Database

Leave a comment

This procedure outlines the steps to disable flashback on an oracle 12c database.

 

 

  1. Logon to server as the oracle software owner.

 

[root@orcl-db1 devadmin]# su – oracle

Last login: Mon Jan 19 18:31:43 UTC 2016 on pts/0

mylinux#

 

 

  1. Open the oracle database in mount mode.

 

SQL> startup mount

ORACLE instance started.

Total System Global Area 5.1540E+10 bytes

Fixed Size                  2938792 bytes

Variable Size            3.4628E+10 bytes

Database Buffers         1.6777E+10 bytes

Redo Buffers              131276800 bytes

Database mounted.

SQL>

 

  1. Disable flashback for database with ALTER DATABASE command.

 

SQL> alter database flashback off;

Database altered.

SQL>

 

  1. Open the database.

 

SQL> alter database open;

Database altered.

SQL>

 

 

  1. This completes disabling flashback in Oracle 12c database.

 

Larry Catt

OCP

 

12c, backup/recovery, CDB, Multitenant, RMAN

RMAN Backup of CDB in Oracle 12c Multitenant Database

Leave a comment

RMAN backup of a CDB in Oracle 12c Multitenant Database can be performed from the Recovery Manager utility or EM Cloud Control.    This procedure shows the steps of performing a CDB backup from RMAN utility.

 

  1. Logon to your oracle database server as the oracle software owner.

 

[root@linux2 larry]# su – oracle

Last login: Thu Jan  3 11:02:25 EDT 2016 on pts/1

[oracle@linux2 ~]$

 

  1. Enter the RMAN utility.

 

[oracle@linux2 ~]$ rman

 

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Jan 15 12:26:22 2016

 

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

 

RMAN>

 

  1. Connect to your target CDB database with SYSBACKUP privilege.

 

RMAN> connect target ‘”sys/@cdb1 as sysbackup”‘

 

target database Password:

connected to target database: CDB1 (DBID=898165058)

 

RMAN>

 

  1. Validate the database you are connected to with SQL command: select name from v$database.

 

RMAN> select name from v$database;

 

using target database control file instead of recovery catalog

NAME

———

CDB1

 

RMAN>

 

  1. Now that you are connected to the database you can perform the following backups:
    • Example of entire CDB backup and all PDBs:

RMAN> BACKUP DATABASE;

  • Example of CDB backup and all PDBs, switch online redo logs and include archivelog.

RMAN>  BACKUP DATABASE PLUS ARCHIVELOG;

  • Backup just the root.

RMAN>  BACKUP DATABASE ROOT;

 

  1. This completes backup of CDB using RMAN utility.

 

Larry Catt

OCP

 

 

12c, backup/recovery, CDB, Multitenant, RMAN

Recovery of CDB in Oracle 12c Multitenant Database

Leave a comment

Oracle 12c multitenant database can use the Recovery Manager utility or EM Cloud control to recover a database from a previously taken backup set.   This procedure outlines the general steps to recover a database using the RMAN utility.

 

 

  1. Logon to your database server as the oracle software owner.

 

[root@linux2 etc]# su – oracle

Last login: Wed Nov 30 11:45:07 EST 2015 on pts/1

[oracle@linux2 ~]$

 

  1. Logon to RMAN utility with sysbackup privileges.

 

[oracle@linux2 ~]$ rman target ‘”/ as sysbackup”‘

Recovery Manager: Release 12.1.0.2.0 – Production on Thu Dec 1 09:26:07 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB1 (DBID=898155058)

 

RMAN>

 

  1. Place the database in mount mode.

 

RMAN> shutdown immediate

Oracle instance shut down

 

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     734003200 bytes

Fixed Size                     2928728 bytes

Variable Size                524292008 bytes

Database Buffers             201326592 bytes

Redo Buffers                   5455872 bytes

 

RMAN>

 

  1. Issue the command RESTORE DATABASE;

 

RMAN> restore database;

 

Starting restore at 01-DEC-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=22 device type=DISK

 

skipping datafile 5; already restored to file /u01/oradata/cdb1/pdbseed/system01.dbf

skipping datafile 7; already restored to file /u01/oradata/cdb1/pdbseed/sysaux01.dbf

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/cdb1/system01.dbf

…………….

…………….

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:45

Finished restore at 01-DEC-15

 

RMAN>

 

  1. Issue the command RECOVER DATABASE;

 

RMAN> RECOVER DATABASE;

 

Starting recover at 01-DEC-15

using channel ORA_DISK_1

 

starting media recovery

media recovery complete, elapsed time: 00:00:04

 

Finished recover at 01-DEC-15

 

RMAN>

 

 

  1. You can remove any database archive redo logs after they are no longer needed using command: RECOVER DATABASE DELETE ARCHIVELOG;

 

RMAN> RECOVER DATABASE DELETE ARCHIVELOG;

 

Starting recover at 01-DEC-15

using channel ORA_DISK_1

 

starting media recovery

media recovery complete, elapsed time: 00:00:00

 

Finished recover at 01-DEC-15

 

RMAN>

 

  1. Open the database for normal operation with command: alter database open;

 

RMAN> alter database open;

 

Statement processed

 

RMAN>

 

 

  1. This completes recovery of CDB database with all PDBs.

 

Larry Catt

OCP

12c, backup/recovery, CDB, Multitenant, PDB, RMAN

Recovery of PDB in Oracle 12c Multitenant Database

Leave a comment

Oracle 12c multitenant database can use the Recovery Manager utility or EM Cloud control to recover a database from a previously taken backup set.   This procedure outlines the general steps to recover a PDB database using the RMAN utility.

 

 

  1. Logon to your database server as the oracle software owner.

 

[root@linux2 etc]# su – oracle

Last login: Wed Nov 30 11:45:07 EST 2015 on pts/1

[oracle@linux2 ~]$

 

  1. Logon to RMAN utility with sysbackup privileges.

 

[oracle@linux2 ~]$ rman target ‘”/ as sysbackup”‘

 

Recovery Manager: Release 12.1.0.2.0 – Production on Thu Dec 1 09:26:07 2015

 

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: CDB1 (DBID=898155058)

 

RMAN>

 

  1. Place the database in mount mode.

 

RMAN> shutdown immediate

Oracle instance shut down

 

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     734003200 bytes

Fixed Size                     2928728 bytes

Variable Size                524292008 bytes

Database Buffers             201326592 bytes

Redo Buffers                   5455872 bytes

 

RMAN>

 

  1. Issue the command; RESTORE PLUGGABLE DATABASE <PDB>;

 

RMAN> RESTORE PLUGGABLE DATABASE pdb1;

 

Starting restore at 01-DEC-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=22 device type=DISK

 

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00008 to /u01/oradata/cdb1/pdb1/system01.dbf

channel ORA_DISK_1: restoring datafile 00009 to /u01/oradata/cdb1/pdb1/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00010 to /u01/oradata/cdb1/pdb1/pdb1_users01.dbf

channel ORA_DISK_1: restoring datafile 00015 to /opt/app/oradata/pdb1_users.dbf

channel ORA_DISK_1: reading from backup piece /opt/app/oracle/fast_recovery_area/CDB1/3F4D938FE2151097E0534B0F1E0A4221/backupset/2015_12_01/o1_mf_nnndf_TAG20151201T092735_d40dy1qr_.bkp

channel ORA_DISK_1: piece handle=/opt/app/oracle/fast_recovery_area/CDB1/3F4D938FE2151097E0534B0F1E0A4221/backupset/2015_12_01/o1_mf_nnndf_TAG20151201T092735_d40dy1qr_.bkp tag=TAG20151201T092735

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:45

Finished restore at 01-DEC-15

 

RMAN>

 

  1. Issue the command RESTORE PLUGGABLE DATABASE <PDB>;

 

RMAN> RESTORE PLUGGABLE DATABASE pdb1;

 

Starting restore at 01-DEC-15

using channel ORA_DISK_1

 

skipping datafile 8; already restored to file /u01/oradata/cdb1/pdb1/system01.dbf

skipping datafile 9; already restored to file /u01/oradata/cdb1/pdb1/sysaux01.dbf

skipping datafile 10; already restored to file /u01/oradata/cdb1/pdb1/pdb1_users01.dbf

skipping datafile 15; already restored to file /opt/app/oradata/pdb1_users.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 01-DEC-15

 

RMAN>

 

 

  1. You can remove any database archive redo logs after they are no longer needed using command: RECOVER DATABASE DELETE ARCHIVELOG;

 

RMAN> RECOVER DATABASE DELETE ARCHIVELOG;

 

Starting recover at 01-DEC-15

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:03

 

Finished recover at 01-DEC-15

 

RMAN>

 

 

  1. Examine RMAN logs for errors and if no errors open root and PDBs.

 

ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE ALL OPEN;

 

RMAN> ALTER DATABASE OPEN;

Statement processed

 

RMAN> ALTER PLUGGABLE DATABASE ALL OPEN;

Statement processed

 

RMAN>

 

  1. This completes recovery of PDB database.

 

Larry Catt

OCP

12c, backup/recovery, RMAN

Oracle 12c – Detect and repair data failures with Data Recovery Advisor

Leave a comment

In Oracle 12c the Data Recovery Advisor is a part of Support Workbench which provides data corruption repair, database health checks, and RMAN functions.  It can perform the following:

  • Display data corruption problems.
  • Assess extent and impact.
  • Recommend repair options.
  • Automate repair processes.

Health checks are diagnostic procedures that assess the health of the database, they are executed reactively by an error occurring or manually.   The Data Recovery Advisor can diagnosis the following failures:

  • Datafiles or control files not accessible to database.
  • Physical corruption of datafiles.
  • Inconsistent datafile timestamps.
  • I/O failures such as hardware, OS drivers, exceeding OS resource limits.
  • Logical corruptions will require support interaction in general.

Failures are a persistent data corruption detected by a health check, normally found reactively when a problem is encountered and recorded in ADR.  The Data Recovery Advisor only after it occurs and assigns a priority of:

  • Critical – immediate action required to avoid database failure and corrupt.
  • High – importance may make some of database unavailable or corrupt.
  • Low – Failure can be ignored and will not cause database failure.

DRA Repairs are options which may include block media recovery, datafile media recovery, or Oracle Flashback Database.   DRA normally will provide both automated and manual recovery operations.   In automated repair DRA performance fix, verifies and clears error.

 

RMAN utility provides the ability to LIST, ADVISE and REPAIR failures.  RMAN is extracting this information from the ADR activities running in the database.  Commands used in RMAN for displaysing, advising, repairing, and changing failure check variables.

 

 

  • LIST FAILURE

 

RMAN> list failure;

using target database control file instead of recovery catalog

Database Role: PRIMARY

no failures found that match specification

RMAN>

 

  • ADVISE FAILURE – Displays repair options for failures.

 

RMAN> advise failure;

Database Role: PRIMARY

no failures found that match specification

RMAN>

 

  • REPAIR FAILURE – Allows you to repair failure with options, if there are automatic repairs from advise failure.

 

  • CHANGE FAILURE – allows you to set different priority to failure

 

RMAN> Change Failure 3 priority low;

 

 

Larry Catt

OCP

12c, backup/recovery, flashback, RMAN

Oracle 12c – Steps to perform a Flashback Database Operation

Leave a comment

The flashback database feature allows you to reset the database to earlier point in time to resolve logical corruption.    It is faster than point in time recovery and does not require backup and restore operations.  Flashback database operations is available from RMAN or SQL with command: FLASHBACK DATABASE.   To enable flashback database the FAST RECOVERY AREA must be configured and FLASHBACK RETENTION TARGET set.   The DB_FLASHBACK_RETENTION_TARGET states how far in the past the database can be recovered to and Flashback must be setup in advance of need. (See article Enabling Flashback Database)  The database periodically copies blocks from every datafile into recovery area and maintains logs of changes to those blocks.  Additionally flashback logs, redo logs must be available for entire span of retention target.   FLASHBACK DATABASE window is the time that is covered by flashback logs.  FLASHBACK will delete oldest logs first if it runs low on space.

 

FLASHBACK has these limits:

  1. It can only undo changes to datafile made by Oracle Database, not media failure or deletion of file.
  2. It cannot undo shrink operations.
  3. If control file is recovered or recreated all flashback data is invalid.
  4. IF NOLOGGING is used, block corruption is likely if flashback is used.

 

Restore points can be used instead of time or SCN, Restore points are alias of scn or time.

Commands that support restore point are RECOVER DATABASE and FLASHBACK DATABASE in RMAN   and FLASHBACK TABLE in SQL.  Guaranteed restore points never age out of control file and must be explicitly dropped, it ensures that you can use flashback database to a single SCN even if flashback logging is not enabled.  When enabled it enforces retention of flashback logs all the way to guaranteed SCN.  This article will demonstrate database flashback in RMAN.

 

  1. Logon to server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Thu Jan  5 10:45:29 EST 2015 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$

 

  1. Start RMAN and connect to your database.

 

[oracle@linux2 ~]$ rman

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Jan 10 09:18:41 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: ORCL (DBID=1457738340)

RMAN>

 

  1. Use timestamp_to_scn function to get SCN for 48 hours ago.

 

RMAN> select timestamp_to_scn(systimestamp-2) from dual;

TIMESTAMP_TO_SCN(SYSTIMESTAMP-2)

——————————–

                         4236799

RMAN>

 

  1. Shutdown the database with immediate option if open.

 

RMAN> shutdown immediate

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

RMAN>

 

  1. Startup the database with mount.

 

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     549453824 bytes

Fixed Size                     2926616 bytes

Variable Size                272631784 bytes

Database Buffers             268435456 bytes

Redo Buffers                   5459968 bytes

RMAN>

 

  1. Execute the FLASHBACK DATABASE command with SCN retrieved above.

 

flashback database to scn 4236799;

RMAN> flashback database to scn 4236799;

Starting flashback at 10-JAN-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=22 device type=DISK

 

starting media recovery

 

archived log for thread 1 with sequence 2761 is already on disk as file /u04/oradata/ORCL/flash_recovery_area/ORCL/archivelog/2015_01_08/o1_mf_1_2761_d74og7tj_.arc

media recovery complete, elapsed time: 00:00:02

Finished flashback at 10-JAN-17

 

RMAN>

 

  1. Open the database with command ‘ALTER DATABASE OPEN READ ONLY;’

 

RMAN> alter database open read only;

Statement processed

RMAN>

 

  1. If not errors, shutdown the database with immediate option.

 

RMAN> shutdown immediate

database closed

database dismounted

Oracle instance shut down

RMAN>

 

  1. Startup the database with mount option.

 

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

 

Total System Global Area     549453824 bytes

Fixed Size                     2926616 bytes

Variable Size                272631784 bytes

Database Buffers             268435456 bytes

Redo Buffers                   5459968 bytes

 

RMAN>

 

  1. Open the database with resetlogs option: ‘ALTER DATABASE OPEN RESETLOGS;’

 

RMAN> ALTER DATABASE OPEN RESETLOGS;

Statement processed

RMAN>

 

  1. You should take a full backup at the end of any recovery.
  2. This completes flashing back a database in RMAN.

 

Larry Catt

OCP

12c, backup/recovery, RMAN

Oracle 12c -RMAN Backup Overview

Leave a comment

Oracle provides the Recovery Manager utility (RMAN) to perform two types of database backups: Image Backup and Backup Set.    RMAN command BACKUP AS COPY command creates an image copy or bit for bit copy of the data files, archived redo log and controlfiles.

 

RMAN> BACKUP AS COPY DEVICE TYPE DISK DATABASE;

 

You can set the default backup to image copy with CONFIGURE DEVICE TYPE command

 

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY;

 

Backup sets are the other type of RMAN backups, each backup set has one or more binary files called backup pieces, in proprietary format that can be restored by RMAN.  You can limit the size of size of backup pieces by the MAXPIECESIZE option.   Use the RMAN command BACKUP AS BACKUPSET to create backup copy of database.

 

RMAN> BACKUP AS BACKUPSET DATABASE.

 

You can set the default backup to backup set with the CONIFGURE DEVICE TYPE command.

 

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;

 

You can backup the entire database with the command:

 

RMAN> BACKUP DATABASE;

 

Add PLUS ARCHIVELOGS clause will cause a log switch that allows for full media recovery to the point of starting the backup.

 

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

 

Incremental Backups are part of a backup set that only contains changes to the database since the last full backup.   The benefit of an incremental backup is size of the backup and time required to tack the backup.   Incremental Backups are only good if combined with the last full backup and any incremental backup between the last full backup.

 

You can create an incremental backup with the RMAN command BACKUP INCREMENTAL in three ways

 

Level 0 – Identical to full back

Level 1 Differential – backups all changed blocks since most recent incremental backup.

Level 1 Cumulative – backups all changed block since most recent Level 0 incremental backup

 

Restoring incremental backup use level 0 at start and apply level 1 cumulative or all differential.

 

Example of incremental Level 1:

 

RMAN> backup incremental level 1 cumulative database;

 

RMAN> backup incremental level 1 database;

 

 

Larry Catt

OCP

12c, backup/recovery, CDB, flashback, Multitenant

Perform Flashback for a CDB in Oracle 12c

Leave a comment

Oracle 12c provides the ability to flashback the database to a previous state.  This procedures demonstrates the use of this process to flashback the database 15 minutes.   NOTE:  Four states must exist for flashback to work:

  1. Must be in archivelog mode.
  2. Database must have flashback on.
  3. Control files must contain flashback logs, thus not recreated.
  4. No tablespaces exist with flashback off set.

 

 

  1. Logon to your Oracle database server as the Oracle software owner and logon to SQLPLUS.

 

[root@linux2 etc]# su – oracle

Last login: Thu Jul  1 09:25:23 EST 2015 on pts/1

[oracle@linux2 ~]$

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 1 13:47:01 2015

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

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>

 

  1. Determine SCN of database 15 min ago by using the timestamp_to_scn().

 

SQL> select timestamp_to_scn(sysdate-1/96) from dual;

TIMESTAMP_TO_SCN(SYSDATE-1/96)

——————————

                       5067892

SQL>

 

  1. Now using SCN extracted by the previous step, shutdown the database, startup in mount mode and execute the flashback command.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  734003200 bytes

Fixed Size                  2928728 bytes

Variable Size             524292008 bytes

Database Buffers          201326592 bytes

Redo Buffers                5455872 bytes

Database mounted.

SQL> FLASHBACK DATABASE TO SCN 5067892;

 

Flashback complete.

 

SQL>

 

  1. Startup the database for normal use with resetlogs option and see that all transactions of the last 15 minutes are gone.

 

SQL> alter database open resetlogs;

Database altered.

SQL>

 

 

  1. NOTE: You should take a full backup of the database as soon after flashback as possible to ensure future recovery.

 

  1. This completes the use of Flashback in Oracle 12c.

 

 

Larry Catt

OCP