# Backup und Recovery

# Exportieren einer bestimmten Tabelle

Mit dem Befehl „exp“ läßt sich eine bestimmte Tabelle in eine Datei exportieren. Es handelt sich allerdings um ein Binärformat, welches nur mit dem „imp“-Tool wieder in eine Oracle-Datenbank imporiert werden kann.

```
[oracle@oradb]$ /oracle/product/9.2.0/bin/exp user/pass file=dumpfile.dmp log=dumpfile.log tables=TABELLE1
```

# Recovery eines Controlfiles

1. CTL-File aus Backup wiederherstellen
2. Datenbank in mount-Phase starten:  
    `SQL> startup mount`
3. Recover der Datenbank durchführen:  
    `SQL> recover database until cancel using backup controlfile;`  
    → Recovery schlägt fehl, da Oracle im Archivelog sucht, aber die benötigten Infos in den Redo-Logs zu finden sind  
    → manuelle Angabe des Filenamens der Redo-Logs (evtl. müssen alle nacheinander durchprobiert werden!)
4. Wenn erfolgreich kann die Datenbank geöffnet werden:  
    `SQL> alter databse open resetlogs;`

# Recovery der Redo-Logs

<div id="bkmrk-verlust-eines-online"><div>- <div>Verlust eines online Redo-Logs (nicht das current und inactive):</div>

</div></div>kann einfach aus einem Backup wiederhergestellt werden, danach ein

```
alter database clear logfile '<filename>';
```

- Verlust des current Redo-Logs: Worst Case: Es kann kein Recovery dieses Redo-Logs durchgeführt werden, einzig möglicher Weg ist die Wiederherstellung der kompletten Datenbank aus einem Backup.

# Allgemeines zu RMAN

Alle RMAN-Einstellungen anzeigen:

```
RMAN> show all;
```

Automatisches Backup der Controlfiles einschalten:

```
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
```

Schnellverbindung zu RMAN und zur Katalog-Datenbank:

```
[oracle@student3 ~]$ rman target / catalog rman/rman@repo
```

RMAN konfigurieren und einen Recovery-Katalog einrichten:

1\. User anlegen und berechtigen in ext. Oracle-Datenbank für den RMAN-Catalog

```
SQL> create user rman identified by rman default tablespace rman_ts;
SQL> grant connect,resource,recovery_catalog_owner to rman;
```

2\. Verbinden zur lokalen Datenbank und zum entfernten Katalog-Server:

```
[oracle@server ~]$ rman target / catalog rman/rman@repo
...
connected to target database: ORCL (DBID=1153117770)
connected to recovery catalog database
```

3\. Katalog erzeugen und Datenbank mit dem Katalog registrieren:

```
RMAN> create catalog;

recovery catalog created

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
```

# RMAN - Aufruf und Connect

das Folgende wurde durch [Hermann Brunner](http://www.brunner-consulting.de/ "http://www.brunner-consulting.de") für unseren Oracle DBA-Kurs zusammengefasst:

### Aufruf / Connect

<div id="bkmrk-%24-rman-rman%3E-connect"><div>```
$ rman
RMAN> connect target /
RMAN> connect catalog user/password@catdb
```

</div></div>**oder direkt beim Aufruf**

<div id="bkmrk-%24-rman-target-%2F-%24-rm">```
$ rman target /
$ rman target / catalog user/password@catdb
$ rman target / log='/pfad/zum/log.log' [append] (mit Angabe einer Logdatei)
```

</div>### Einstellungen

<div id="bkmrk-rman%3E-show-all%3B-rman"><div>```
RMAN> show all;
RMAN> configure [...]
```

</div></div>**Syntax wie in der SHOW Anzeige. Beispiele:**

<div id="bkmrk-rman%3E-configure-rete"><div>```
RMAN> configure retention policy to redundancy 3;
RMAN> configure controlfile autobackup on;
```

</div></div>**komplexere Beispiele:**

<div id="bkmrk-rman%3E-configure-devi"><div>```
RMAN> configure device type disk parallelism 3 backup type backupset;
RMAN> configure datafile backup copies for device type sbt to 2;
```

</div></div>**Channels vorkonfigurieren:**

<div id="bkmrk-rman%3E-configure-chan"><div>```
RMAN> configure channel device type disk format '/pfad/%U';
RMAN> configure channel n device type disk format '/home/backup_m/%d_%t_%s.bck'
```

</div></div>**Einstellungen auf default zurücksetzen:**

<div id="bkmrk-rman%3E-configure-rete-0">```
RMAN> configure retention policy clean;
RMAN> configure controlfile autobackup clear;
```

</div>### Recovery Catalog

**Recovery Catalog einrichten:**

<div id="bkmrk-auf-dem-katalog-serv"><div>```
auf dem Katalog-Server:
SQL> create tablespace ts_rman datafile '/pfad/rman01.dbf' size 100m; ## Richtgröße 100MB pro Zieldatenbank)
SQL> create user rman identified by 'passwd' default tablespace ts_rman;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> grant select any dictionary to rman;

auf dem Ursprungs-Server:
$ rman target / catalog rman/rman@catdb ## (evtl. tnsnames.ora anpassen)
RMAN> create catalog;
RMAN> register database;

RMAN> report schema;
RMAN> report schema at time 'sysdate -3' ## (wie war die DB-Struktur vor 3 Tagen?)
```

</div></div>**Maintenance commands für den Katalog:**

<div id="bkmrk-sql%3E-connect-rman%2Frm">```
SQL> connect rman/rman@catdb;
SQL> select * from cat;

RMAN> resync catalog;
RMAN> unregister database;
RMAN> upgrade catalog; ## Katalog auf den Stand des rman-Clients bringen
RMAN> drop catalog;

RMAN> catalog backuppiece 'filename';
RMAN> catalog controlfilecopy 'filename';
RMAN> catalog datafilecopy 'filename';
RMAN> catalog archivelog 'filename';
RMAN> catalog recovery area noprompt;
RMAN> catalog start with '/u01/fra/archive' ## Start-Pfad, ab dem gesucht werden soll
```

</div>### Backups erstellen

<div id="bkmrk-rman%3E-backup-databas"><div>```
RMAN> backup database;
RMAN> backup tablespace ts_name;
RMAN> backup datafile n;
RMAN> backup datafile df_name;
RMAN> backup current controlfile;
RMAN> backup spfile;
RMAN> backup archivelog [from sequence=nnn] [delete {ALL} input];

RMAN> backup as backupset [...];
RMAN> backup as compressed backupset [...];
RMAN> backup as copy [...];
RMAN> backup validate [...]; ## füllt die v$database_block_corruption Liste
RMAN> backup check logical [...];

RMAN> backup [...] plus archivelog;
RMAN> backup [...] include controlfile;
RMAN> backup [...] format '/pfad/zum/backup';
RMAN> backup [...] tag='freier Text';
RMAN> backup [...] not backed up nnn times;
RMAN> backup [...] duration hh:mm minimize load;
RMAN> backup [...] delete [ALL] input;
```

</div></div>**Alte Version 8i/9i Syntax:**

<div id="bkmrk-rman%3E-run-%7B-allocate"><div>```
RMAN> run { 
            ALLOCATE CHANNEL c1 DEVICE TYPE disk FORMAT='/home/oracle/backup1/datafile_5.bck';
            BACKUP DATAFILE 5;
            RELEASE CHANNEL c1; }  ## nur in rman8 nötig
```

</div></div>**Sinnvolle Technik für Full Backup mit anschließender Sicherung der Archive-Logs:**

<div id="bkmrk-rman%3E-backup-databas-0"><div>```
RMAN> backup database;
RMAN> sql 'alter system archive log current';
RMAN> backup archivelog all;
```

</div></div>**Seit Oracle 9i gehts einfacher:**

<div id="bkmrk-rman%3E-backup-databas-1"><div>```
RMAN> backup database plus archivelog; ## alternativ mit Option: delete [all] input
```

</div></div>**Zweistufiges Backup - z.B. Sicherung aller DISK Backups auf Band - Variante 1:**

<div id="bkmrk-rman%3E-backup-device-"><div>```
RMAN> backup device type disk as backupset database;
Optional:
RMAN> [...] plus archivelog;
RMAN> [...] delete input;
```

</div></div>**Zweistufiges Backup - z.B. Sicherung aller DISK Backups auf Band - weitere Varianten:**

<div id="bkmrk-rman%3E-backup-backups"><div>```
RMAN> backup backupset completed before 'sysdate -1';
RMAN> backup copy of database;
```

</div></div>**Trickreichere rman Commands:**

<div id="bkmrk-rman%3E-configure-devi-0"><div>```
RMAN> configure device type disk parallelism 4 backup type to copy;
RMAN> backup database; ## erzeugt einzelne Datafiles in fra/datafile...
```

</div></div>**Incrementelles Backup:**

<div id="bkmrk-rman%3E-backup-%5Bas-bac"><div>```
RMAN> backup [as backupset] incremental level 0 [database|tablespace|datafile];
RMAN> backup [as backupset] incremental level n [cumulative] [database|tablespace|datafile];
```

</div></div>**Incrementelles Updating einer Copy** die mit &lt;&gt; gekennzeichneten Optionen sind nur in Oracle 9 nötig!

<div id="bkmrk-rman%3E-backup-as-copy">```
RMAN> backup as copy <incremental level 0> [database|tablespace|datafile] <format='somewhere/somefile' tag='TEST'>;
RMAN> backup incremental level 1 <for recover of copy with tag='TEST'> [database|tablespace|datafile];
RMAN> recover copy of [database|tablespace|datafile] <with tag='TEST'>;
```

</div>### Restore / Recover Commands

<div id="bkmrk-rman%3E-restore-%5Bdatab"><div>```
RMAN> restore [database|tablespace|datafile];
RMAN> restore [...] from tag 'xxx';
RMAN> restore [...] to '/somewhere/somefile';
RMAN> restore validate [database|tablespace|datafile]; ## prüft ob korrekte Backups vorhanden sind

RMAN> recover database;
RMAN> recover [tablespace|datafile]; ## database muss open, tablespace oder datafile muss offline sein
```

</div></div>**Weitere Recovery-Optionen** Für Recovery ist nur beschränkter Platz für Archivelogs vorhanden:

<div id="bkmrk-rman%3E-recover-%5B...%5D-"><div>```
RMAN> recover [...] delete archivelog maxsize 100M; ## braucht nur max. 100MB
```

</div></div>Recovery an einen anderen Ort, weil Original-Location nicht erreichbar (z.B. weil neue Disk):

<div id="bkmrk-rman%3E-set-newname-fo"><div>```
RMAN> set newname for datafile n to '/pfad/file.dbf';
RMAN> restore datafile n;
RMAN> switch datafile all; ## führt das SET NEWNAME im Katalog durch, Datenbank wird auf die neuen Datafile umgestellt
RMAN> recover datafile n;
RMAN> sql 'alter database datafile n online';
```

</div></div>**Unvollständiges Recovery** Prinzip im rman: „UNTIL“-Klausel muss VOR dem RESTORE gesetzt werden!

<div id="bkmrk-rman%3E-run-%7B-set-unti">```
RMAN> run { set until time 'time'; ## oder: set until change 'scn' | until sequence 'seq#' thread 1
            restore database;
            recover database;
            alter database open resetlogs; }
```

</div>### Alles Futsch? --&gt; komplettes Recovery einer Datenbank

Alle zur Datenbank gehörenden Dateien sind verloren…&lt;br /&gt; Prinzip:&lt;br /&gt;

<div id="bkmrk-rman%3E-recover-spfile"><div>```
RMAN> recover spfile from autobackup; ## nomount Phase
RMAN> recover controlfile from autobackup; ## nomount Phase
RMAN> restore database; ## mount Phase
RMAN> recover database; ## mount Phase
RMAN> alter database open resetlogs; ## Datenbank sollte jetzt wieder laufen
```

</div></div>im Detail:

<div id="bkmrk-rman%3E-startup-nomoun">```
RMAN> startup nomount force; ## startup mit default-Parametern. Geht nur im RMAN!
RMAN> restore spfile from autobackup; ## funktioniert nur, wenn an default-locations zu finden)
oder:
RMAN> set dbid=1234567890
RMAN> restore spfile from 'location_of_autobackups';

dann weiter:
RMAN> shutdown abort;
RMAN> startup nomount; ## Datenbank startet wieder mit eigenem spfile
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;

ab hier sind viele LIST-Commands möglich:
RMAN> list incarnation;
RMAN> list backup of database; ## etc..

also einfach den Rest restoren und recovern:
RMAN> restore database;
RMAN> recover database; ## führt zu Fehler bei letzter Log-Seq#, da das online Redo-Log ja futsch ist
daher am einfachsten/besten:
SQL> recover database until cancel using backup controlfile; ## bis zum Schluss, falls zufällig eine Kopie des online Redo-Logs vorhanden ist kann das auch noch gefüttert werden
SQL> alter database open resetlogs;

Fertig! An dieser Stelle sollte dann natürlich, wegen der neuen Datenbank-Incarnation, nochmal ein neues Backup erstellt werden:
RMAN> backup database;
```

</div>### Umgang mit RMAN-Scripts

<div id="bkmrk-rman%3E-create-%5Bglobal">```
RMAN> create [global] script name {backup database;};
RMAN> create [global] script name from file 'file mit rman-commands';

RMAN> print script name;
RMAN> print script name to file 'filename';
RMAN> list [global] script names;

RMAN> replace script name { new script commands; }

RMAN> run { execute script name; }
```

</div>### Report, List und Maintenance Commands

<div id="bkmrk-rman%3E-report-schema%3B">```
RMAN> report schema;
RMAN> report obsolete;
RMAN> report obsolete orphan; ## aus der vorletzten incarnation oder älter
RMAN> report need backup [redundancy nn | days nn];
RMAN> report unrecoverable [...];

RMAN> list backup of [database|tablespace|datafile];
RMAN> list backup of [archivelog|controlfile|spfile];
RMAN> list backup of [...] summary; ## zeigt übersichtliches Kurzformat
RMAN> list copy of [database|tablespace|datafile];
RMAN> list incarnation [of database];

RMAN> crosscheck [backup of database|archivelog]; ## setzt nicht vorhandene Backupsets|Archivelogs auf "expired"
RMAN> delete [noprompt] expired backupset; ## löscht expired Eintragungen aus dem Katalog
RMAN> delete expired archivelog;
RMAN> delete [noprompt] obsolete;
RMAN> delete obsolete [redundancy n];
RMAN> delete obsolete [orphan];
RMAN> delete backupset nnn;
```

</div>### Weitere Tips und Tricks mit RMAN

**Direkte SQL-Commands, die RMAN selbst 'kann':**

<div id="bkmrk-rman%3E-startup%3B-rman%3E"><div>```
RMAN> startup;
RMAN> shutdown [...];
RMAN> alter database open [resetlogs];
```

</div></div>**Alle SQL Commands, die keinen Output benötigen, können über die SQL-Klausel gestartet werden**

<div id="bkmrk-rman%3E-sql-%27alter-sys"><div>```
RMAN> sql 'alter system switch logfile';
RMAN> sql 'alter system archive log all';
```

</div></div>**Wenn man NLS\_DATE\_FORMAT außerhalb von RMAN setzt, kann man innerhalb von RMAN bessere Anzeigen von Backup-Zeitpunkten bekommen (z.B. in LIST Kommandos):**

<div id="bkmrk-%24-export-nls_lang%3Dge"><div>```
$ export NLS_LANG=german_germany ## für RMAN in 10g nicht mehr nötig
$ export NLS_DATE_FORMAT=yyy-mm-dd:hh24:mi:ss
RMAN> list backup of database;
```

</div></div>**Wenn man mit Hausmitteln die CTL-Files ausgetauscht hat und RMAN nicht durchblickt, weil die restaurierte CTL-File aus einer alten Incarnation stammt:**

<div id="bkmrk-rman%3E-reset-incarnat">```
RMAN> reset incarnation to 2; ## ohne catalog DB
RMAN> reset database to incarnation 2; ## mit catalog DB
```

</div>

# RMAN Format Codes

**Restrictions and Usage Notes** Any name that is legal as a sequential filename on the platform is allowed, so long as each backup piece or copy has a unique name. If backing up to disk, then any legal disk filename is allowed, provided it is unique.

**Keywords and Parameters**

<div id="bkmrk-syntax-element-descr"><table class="inline"><thead><tr class="row0"><th class="col0" style="width: 30px;">Syntax Element

</th><th class="col1" style="width: 779px;">Description</th></tr></thead><tbody><tr class="row1"><td class="col0" style="width: 30px;">%a</td><td class="col1" style="width: 779px;">Specifies the activation ID of the database</td></tr><tr class="row2"><td class="col0" style="width: 30px;">%c</td><td class="col1" style="width: 779px;">Specifies the copy number of the backup piece within a set of duplexed backup pieces. If you did not duplex a backup, then this variable is 1 for backup sets and 0 for proxy copies. If one of these commands is enabled, then the variable shows the copy number. The maximum value for %c is 256.</td></tr><tr class="row3"><td class="col0" style="width: 30px;">%d</td><td class="col1" style="width: 779px;">Specifies the name of the database</td></tr><tr class="row4"><td class="col0" style="width: 30px;">%D</td><td class="col1" style="width: 779px;">Specifies the current day of the month from the Gregorian calender in format DD</td></tr><tr class="row5"><td class="col0" style="width: 30px;">%e</td><td class="col1" style="width: 779px;">Specifies the archived log sequence number</td></tr><tr class="row6"><td class="col0" style="width: 30px;">%f</td><td class="col1" style="width: 779px;">Specifies the absolute file number</td></tr><tr class="row7"><td class="col0" style="width: 30px;">%F</td><td class="col1" style="width: 779px;">Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name. This variable translates into **c-IIIIIIIIII-YYYYMMDD-QQ**, where **IIIIIIIIII** stands for the DBID. The DBID is printed in decimal so that it can be easily associated with the target database. **YYYYMMDD** is a time stamp in the Gregorian calendar of the day the backup is generated and **QQ** is the sequence in hexadecimal number that starts with 00 and has a maximum of FF (256)</td></tr><tr class="row8"><td class="col0" style="width: 30px;">%h</td><td class="col1" style="width: 779px;">Specifies the archived redo log thread number</td></tr><tr class="row9"><td class="col0" style="width: 30px;">%I</td><td class="col1" style="width: 779px;">Specifies the DBID</td></tr><tr class="row10"><td class="col0" style="width: 30px;">%M</td><td class="col1" style="width: 779px;">Specifies the month in the Gregorian calendar in format MM</td></tr><tr class="row11"><td class="col0" style="width: 30px;">%N</td><td class="col1" style="width: 779px;">Specifies the tablespace name</td></tr><tr class="row12"><td class="col0" style="width: 30px;">%n</td><td class="col1" style="width: 779px;">Specifies the name of the database, padded on the right with x characters to a total length of eight characters. For example, if the prod1 is the database name, then the padded name is prod1xxx.</td></tr><tr class="row13"><td class="col0" style="width: 30px;">%p</td><td class="col1" style="width: 779px;">Specifies the piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1 as each backup piece is created. If you specify PROXY, then the %p variable must be included in the FORMAT string either explicitly or implicitly within %U</td></tr><tr class="row14"><td class="col0" style="width: 30px;">%s</td><td class="col1" style="width: 779px;">Specifies the backup set number. This number is a counter in the control file that is incremented for each backup set. The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup control file, then duplicate values can result. Also, CREATE CONTROLFILE initializes the counter back to 1</td></tr><tr class="row15"><td class="col0" style="width: 30px;">%t</td><td class="col1" style="width: 779px;">Specifies the backup set time stamp, which is a 4-byte value derived as the number of seconds elapsed since a fixed reference time. The combination of %s and %t can be used to form a unique name for the backup set</td></tr><tr class="row16"><td class="col0" style="width: 30px;">%T</td><td class="col1" style="width: 779px;">Specifies the year, month and day in the Gregorian calendar in this format: &lt;tt&gt;YYYYMMDD&lt;/tt&gt;</td></tr><tr class="row17"><td class="col0" style="width: 30px;">%u</td><td class="col1" style="width: 779px;">Specifies an 8-character name constituted by compressed representations of the backup set or image copy number and the time the backup set or image copy war created</td></tr><tr class="row18"><td class="col0" style="width: 30px;">%U</td><td class="col1" style="width: 779px;">Specifies a system-generated unique filename (default). The meaning of %U is different vor image copies and backup pieces. For a backup piece %U specifies a conveniant shorthand for %u\_%p\_%c that guarantees uniqueness in generated backup filenames. If you do not specify a format when making a backup, then RMAN uses %U by default. For an image copy of a datafile %U means **data-D-%d\_id-%I\_TS-%N\_FNO-%f\_%u**. For an image copy of an archived redo log %U means **arch-D\_%d-id-%I\_S-%e\_T-%h\_A-%a\_%u**. For an image copy of a control file %U means **cf-D\_%d-id-%I\_%u**</td></tr><tr class="row19"><td class="col0" style="width: 30px;">%Y</td><td class="col1" style="width: 779px;">Specifies the year in the format **YYYY**</td></tr><tr class="row20"><td class="col0" style="width: 30px;">% %</td><td class="col1" style="width: 779px;">Specifies the '%' character. For example, %%Y translates to the string %Y</td></tr></tbody></table>

</div>

# Vollständiges Recovery

Ach du Scheiße! Alles in oradata (ctl-, db-, redo-files) ist weg! Aber zum Glück haben wir ja ein Backup:

```code
RMAN> connect target /
RMAN> connect catalog rman/rman@repo
RMAN> startup nomount [force]
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
```

Der letzte Befehl bricht mit Fehler ab. Die Archivlogs sind zwar vorhanden und wurden bis zum letzten logswitch wiederhergestellt. Die online Redo-Logs sind leider futsch, d.h. wir müssen die Datenbank mit 'resetlogs' öffnen:

```code
RMAN> alter database open resetlogs;
```