DBA 101 Bootcamp Backup 101

Database Administration

DBA 101 Bootcamp – Backup 101

Joseph S. Testa, Nationwide Insurance

Definitions

•  Archivelogmode : A mode of the database that enables the archiving(making a copy) of the online redo log when its full.

•  Noarchivelogmode: A mode of the database that disables the archiving of the online redo log when its full.

•  Shutdown normal: The NORMAL clause of the SQL*Plus SHUTDOWN command is optional because this is the default shutdown method. Normal database shutdown proceeds with the following conditions:

•  No new connections are allowed after the statement is issued.

•  Before the database is shut down, the database waits for all currently connected users to disconnect from the database.

•  The next startup of the database will not require any instance recovery procedures.

•  Shutdown immediate: Use immediate when you want to:

•  To initiate an automated and unattended backup

•  When a power shutdown is going to occur soon

•  When the database or one of its applications is functioning irregularly and you cannot contact users to ask them to log off or they are unable to log off

•  Immediate database shutdown proceeds with the following conditions:

•  No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.

•  Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)

•  Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.

•  The next startup of the database will not require any instance recovery procedures.

•  Shutdown transactional: Use when you want to perform a planned shutdown of an instance while allowing active transactions to complete first.

•  Transactional database shutdown proceeds with the following conditions:

•  No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.

•  After all transactions have completed, any client still connected to the instance is disconnected.

•  At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.

•  The next startup of the database will not require any instance recovery procedures.

•  A transactional shutdown prevents clients from losing work, and at the same time, does not require all users to log off.

•  Shutdown abort: You can shut down a database instantaneously by aborting the database instance. If possible, perform this type of shutdown only in the following situations:

•  The database or one of its applications is functioning irregularly and none of the other types of shutdown works.

•  You need to shut down the database instantaneously (for example, if you know a power shutdown is going to occur in one minute).

•  You experience problems when starting a database instance.

•  An aborted database shutdown proceeds with the following conditions:

•  No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.

•  Current client SQL statements being processed by Oracle Database are immediately terminated.

•  Uncommitted transactions are not rolled back.

•  Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users.

•  The next startup of the database will require automatic instance recovery procedures.

•  Consistent: A backup that you can open without performing media recovery. In other words, the backup does not require the application of redo to be made consistent.

•  Inconsistent: A backup in which some files in the backup contain changes made after the checkpoint. Unlike a consistent backup, an inconsistent backup requires media recovery to be made consistent.

•  Control File: The control file is the root file that Oracle Database uses to find database files and to manage the state of the database generally. A control file contains information such as the following:

•  The database name and database unique identifier (DBID)

•  The time stamp of database creation

•  Information about data files, online redo log files, and archived redo log files

•  Tablespace information

•  RMAN backups

•  Data Files: A physical file on disk that was created by Oracle Database and contains the data for a database. The data files can be located either in an operating system file system or Oracle ASM disk group.

•  Redo Logs: A set of files that protect altered database data in memory that has not been written to the data files. The redo log can consist of two parts: the online redo log and the archived redo log.

•  Archive Logs: A member of the online redo log that has been archived by Oracle Database. The archived redo log files can be applied to a database backup in media recovery.

•  Physical Backup: A backup of physical files. A physical backup contrasts with a logical backup such as a table export.

•  Logical Backup: A backup of database schema objects, such as tables. Logical backups are created and restored with the Oracle Data Pump Export utility. You can restore objects from logical backups using the Data Pump Import utility.

•  RMAN client: An Oracle Database executable that interprets commands, directs server sessions to execute those commands, and records its activity in the target database control file. The RMAN executable is automatically installed with the database and is typically located in the same directory as the other database executables. For example, the RMAN client on Linux is named rman and is located in $ORACLE_HOME/bin.

•  Target database: In an RMAN environment, the database to which you are connected as TARGET. The target database is the database on which you are performing RMAN operations.

•  Recovery catalog database: An Oracle database that contains a recovery catalog schema. You should not store the recovery catalog in the target database.

•  Recovery catalog schema: The recovery catalog database schema that contains the recovery catalog tables and views.

•  User Managed Backups: A backups made using a non-RMAN method, for example, using an operating system utility. For example, you can make a user-managed backup by running the cp command on Linux or the COPY command on Windows. A user-managed backup is also called an operating system backup.

•  Hot Backup: A backup of one or more datafiles taken while a database is open and the datafiles are online. When you make a user-managed backup while the database is open, you must put the tablespaces in backup mode by issuing an ALTER TABLESPACE BEGIN BACKUP command. (You can also use ALTER DATABASE BEGIN BACKUP to put all tablespaces in your database into backup mode in one step.) You should not put tablespaces in backup mode when performing backups with RMAN.

•  Cold Backup: A backup of one or more database files taken while the database is closed. Typically, closed backups are whole database backups. If you closed the database consistently, then all the files in the backup are consistent. Otherwise, the backups are inconsistent.

•  Flash/Fast recovery area: An optional disk location that you can use to store recovery-related files such as control file and online redo log copies, archived redo log files, flashback logs, and RMAN backups. Oracle Database and RMAN manage the files in the fast recovery area automatically. You can specify the disk quota, which is the maximum size of the fast recovery area.

User Managed Backups (Physical)

The user part of this should be emphasized. It is you who handles it all. You handle when the backups are done, where the backups are put to, what is backed up, how its backed up.

Cold backup (consistent, noarchivelogmode)

You will want to retrieve information from:

·  (g)v$datafile

·  (g)v$controlfile

Grab the locations of all of the datafiles and the location of one of the control files.

Shutdown (any mode EXCEPT ABORT)

Copy datafiles and 1 copy of controlfile to “other” location.

Here is a script (unix/linux version) to do just that, run it from within sqlplus, this assumes /backup is your backup destination:

connect / as sysdba

set feedback off

set linesize 200

set pagesize 0

set heading off

spool /tmp/backup.sql

select 'shutdown immediate;' from dual;

select 'host cp '||name||' /backup' from gv$datafile;

select 'host cp '||name||' /backup' from v$controlfile where rownum=1;

select 'startup;' from dual;

spool off

set echo on

@/tmp/backup.sql

set echo off

set pagesize 14

set feedback on

set linesize 80

Cold backup (consistent, archivelogmode)

You will want to retrieve information from:

·  (g)v$datafile

·  (g)v$controlfile

·  (g)v$archived_log

Grab the locations of all of the datafiles, the location of one of the control files and all of the archive logs.

Shutdown (any mode EXCEPT ABORT)

Copy datafiles, 1 copy of controlfile and archivelogs to “other” location.

Here is a script (unix/linux version) to do just that, run it from within sqlplus, this assumes /backup is your backup destination:

-- this implies archivelogmode is in effect

connect / as sysdba;

set feedback off

set linesize 200

set pagesize 0

set heading off

spool /tmp/backup.sql

select 'alter system archive log current;' from dual;

select 'shutdown immediate;' from dual;

select 'host cp '||name||' /backup' from gv$datafile;

select 'host cp '||name||' /backup' from gv$controlfile where rownum=1;

select distinct 'host cp '||substr(name,1,instr(name,'/',-1))||'* /backup' from gv$archived_log where name is not null;

select 'startup;' from dual;

spool off

set echo on

@/tmp/backup.sql

set echo off

set pagesize 14

set feedback on

set linesize 80

Hot backup (inconsistent, archivelogmode)

Now for hotbackups, the database stays up and running while you’re making the backup, this implies archivelogmode, could you do it in non-archivelogmode? Sure but don’t expect to be able to recover it. The big difference here is the putting of tablespaces in “backup mode”. You’ll see most of everything else is the same as above except for that part, we also backup the controlfile to trace since the database is open, we will just dump the contents of the controlfile to a text file.

connect / as sysdba;

set feedback off

set linesize 200

set pagesize 0

set heading off

spool /tmp/backup.sql

-- put ts in backup mode

select 'alter tablespace '||tablespace_name||' begin backup;' from dba_tablespaces where contents != 'TEMPORARY';

-- copy files to backup directory

select 'host cp '||file_name||' /backup' from dba_data_files;

-- take ts out of backup mode

select 'alter tablespace '||tablespace_name||' end backup;' from dba_tablespaces where contents != 'TEMPORARY';

-- switch logfile

select 'alter system archive log current;' from dual;

-- backup archive logs

--select 'host cp select distinct substr(name,1,instr(name,'||''''||'/'||''''||',-1)) from gv$archived_log;'||' /backup' from dual;

select distinct 'host cp '||substr(name,1,instr(name,'/',-1))||'* /backup' from gv$archived_log where name is not null;

-- backup controlfile to backup directory

select 'alter database backup controlfile to '||''''||'/backup/controlfile_bkup.ctl'||''''||' reuse;' from dual;

spool off

set echo on

@/tmp/backup.sql

set echo off

set pagesize 14

set feedback on

set linesize 80

User Managed Backups (Logical)

Export

It shouldn’t be used anymore, it’s been replaced with data pump.

Data pump

The “new” export. To talk much about datapump would require a full presentation in itself.

Recovery Manager (RMAN) Backups

Rman is the best and most robust Oracle product for doing backups. Its come along way from when I first started using it in version 8. Let’s look at some of the options, keeping in mind this is a beginner’s course.

Repository/Recovery Catalog

An RMAN repository is not only a good idea but it’s the LAW, ok not really the law but its more than just a good idea. You want to be able to store all of your backup metadata in one place so when you have to start doing restores, you got it. The information will still be in the control file, hence when you’re doing a backup, you want to backup the control file also at the END, that way the latest backup you just did is in there. There is excellent documentation on how to create a repository in the Backup Recover Users Guide, the chapter on creating a recovery catalog.

Connecting

There are two potential connection strings, first is to connect to a repository if you have one, second is to connect to the database you want to do the backup for.

·  Connect catalog userid/password@______

·  Connect target / or userid/password@______

Types of backups

·  Full – This is like is says, a full backup of the database.

·  Backup database [plus archivelog];

·  Incremental – A level 0 of this kind, is the same as a full above, EXCEPT, it can be used to “partial” backups later of only changed blocks.

·  Backup incremental level 0 database;

·  Backup incremental level 1 database;

·  Archivelog - Backups up archive logs, notice the delete input, you can backup them up and have rman remove them from disk if you choose.

·  Backup archivelog all [delete input];

·  Control File – Make a backup copy of your controlfile, remember that all rman backups write information, aka metadata about the backup to the controlfile, even IF you have a repository.

·  Backup current controlfile;

·  Tablespace – Backup a single tablespace, since a tablespace is a logical object, it will backup all datafiles associated with that tablespace.

·  Backup tablespace tsname;

·  Data File – backup datafile, either the number of the datafile or the full path is required.

·  Backup datafile number;

·  Backup datafile “full path”;

Reporting

·  List backup – use this command within RMAN to show what backups have occurred.

·  Delete backup – used to delete backup metadata (and possibly, the physical files).

Pros/Cons

User Managed

·  Pros

·  You control all of it, control freaks, is this you?

·  It’s easy to just copy a file from a backup place to its original place

·  Cons

·  You control all of it.

·  You have to maintain repository of when things were backed up last, etc.

User Managed

·  Pros

·  Oracle manages it all, you barely have to think about it