Recovery przywracanie

Recover database; -odtwarza również dzienniki archivelog -jeśli tryb jest wyłączony  warto nie uwzględniać go w skrypcie komenda alter database open resetlogs; dopiero można wykonać.
W przypadku gdy korzystamy dzienników trzeba pamiętać o włączonym trybie archivelog
ponieważ będą problemy z odtwarzaniem bazy np do punktu czasu (restore point)


Recovery Manager: Release 11.2.0.2.0 - Production on îr Gru 25 14:48:28 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: XE (DBID=2712378990)

RMAN> set echo on;
2> startup nomount pfile=C:\Users\admin\AppData\Local\Temp\rman_dummy.ora force;

3>
echo set on

Oracle instance started

Total System Global Area     284565504 bytes

Fixed Size                     1383076 bytes
Variable Size                100664668 bytes
Database Buffers             176160768 bytes
Redo Buffers                   6356992 bytes

Recovery Manager complete.

Recovery Manager: Release 11.2.0.2.0 - Production on îr Gru 25 14:49:04 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: XE (not mounted)

RMAN> set echo on;
2> shutdown immediate;
3> startup nomount pfile="C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\
SPFILE2INIT.ORA";
4> restore (spfile from autobackup) (controlfile from autobackup);
5> startup mount force;
6> configure controlfile autobackup off;
7> restore database;
8>
echo set on

using target database control file instead of recovery catalog
Oracle instance shut down

connected to target database (not started)
Oracle instance started

Total System Global Area     376635392 bytes

Fixed Size                     1383692 bytes
Variable Size                289407732 bytes
Database Buffers              79691776 bytes
Redo Buffers                   6152192 bytes

Starting restore at 13/12/25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

recovery area destination: C:\oraclexe\app\oracle\fast_recovery_area
database name (or database unique name) used for search: XE
channel ORA_DISK_1: AUTOBACKUP C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\AUTO
BACKUP\2013_12_25\O1_MF_S_835109025_9COR8LSG_.BKP found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring spfile from AUTOBACKUP C:\ORACLEXE\APP\ORACLE\FAST
_RECOVERY_AREA\XE\AUTOBACKUP\2013_12_25\O1_MF_S_835109025_9COR8LSG_.BKP
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
failover to previous backup

recovery area destination: C:\oraclexe\app\oracle\fast_recovery_area
database name (or database unique name) used for search: XE
channel ORA_DISK_1: AUTOBACKUP C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\AUTO
BACKUP\2013_12_25\O1_MF_S_835109025_9COR8LSG_.BKP found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP C:\ORACLEXE\APP\ORACL
E\FAST_RECOVERY_AREA\XE\AUTOBACKUP\2013_12_25\O1_MF_S_835109025_9COR8LSG_.BKP
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF
Finished restore at 13/12/25

Oracle instance started
database mounted

Total System Global Area     376635392 bytes

Fixed Size                     1383692 bytes
Variable Size                293602036 bytes
Database Buffers              75497472 bytes
Redo Buffers                   6152192 bytes

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored

Starting restore at 13/12/25
Starting implicit crosscheck backup at 13/12/25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 13/12/25

Starting implicit crosscheck copy at 13/12/25
using channel ORA_DISK_1
Finished implicit crosscheck copy at 13/12/25

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2013_12_25\O1
_MF_1_1_9COR8T1T_.ARC
File Name: C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2013_12_25\O1
_MF_1_2_9COR9622_.ARC
File Name: C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\AUTOBACKUP\2013_12_25\O1
_MF_S_835106118_9COOLFW3_.BKP
File Name: C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\AUTOBACKUP\2013_12_25\O1
_MF_S_835109025_9COR8LSG_.BKP

using channel ORA_DISK_1

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 C:\ORACLEXE\APP\ORACLE\ORADATA\X
E\SYSTEM.DBF
channel ORA_DISK_1: restoring datafile 00002 to C:\ORACLEXE\APP\ORACLE\ORADATA\X
E\UNDOTBS1.DBF
channel ORA_DISK_1: restoring datafile 00003 to C:\ORACLEXE\APP\ORACLE\ORADATA\X
E\SYSAUX.DBF
channel ORA_DISK_1: restoring datafile 00004 to C:\ORACLEXE\APP\ORACLE\ORADATA\X
E\USERS.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLEXE\APP\ORACLE\FAST_RECOVE
RY_AREA\XE\BACKUPSET\2013_12_25\O1_MF_NNNDF_TAG20131225T144217_9COR5V3Q_.BKP
channel ORA_DISK_1: piece handle=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\BA
CKUPSET\2013_12_25\O1_MF_NNNDF_TAG20131225T144217_9COR5V3Q_.BKP tag=TAG20131225T
144217
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 13/12/25

Recovery Manager complete.

Recovery Manager: Release 11.2.0.2.0 - Production on îr Gru 25 14:51:06 2013

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

connected to target database: XE (DBID=271755747, not open)

RMAN> set echo on;
2> recover database;
3> alter database open resetlogs;
4>
echo set on

Starting recover at 13/12/25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file C:\ORACLEXE
\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2013_12_25\O1_MF_1_1_9COR8T1T_.ARC
archived log for thread 1 with sequence 2 is already on disk as file C:\ORACLEXE
\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2013_12_25\O1_MF_1_2_9COR9622_.ARC
archived log for thread 1 with sequence 3 is already on disk as file C:\ORACLEXE
\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_981B260Y_.LOG
archived log file name=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2
013_12_25\O1_MF_1_1_9COR8T1T_.ARC thread=1 sequence=1
archived log file name=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ARCHIVELOG\2
013_12_25\O1_MF_1_2_9COR9622_.ARC thread=1 sequence=2
archived log file name=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1
_MF_1_981B260Y_.LOG thread=1 sequence=3
media recovery complete, elapsed time: 00:00:02
Finished recover at 13/12/25

database opened

Recovery Manager complete.
Restore of the database succeeded.
Log file is at C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\OXE_RESTORE
.LOG.

BACKUP LOGICZNY EKSPORTY EXP

Eksport wybranych tabel

exp FILE="C:\bck\dump.dmp"
LOG="C:\bck\exp_dump.log"
TABLES=('HR.COUNTRIES', 'HR.DEPARTMENTS', 'HR.EMPLOYEES', 'HR.JOB_HISTORY', 'HR.JOBS', 'HR.LOCATIONS', 'HR.REGIONS')
FEEDBACK=1000

Eksport typu users np HR

exp FILE="C:\bck\dump.dmp"
LOG="C:\bck\exp_dump.log"
OWNER=('HR')
FEEDBACK=1000

Eksport bazy

exp FILE="C:\bck\dump.dmp"
LOG="C:\bck\exp_dump.log"
FULL=Y
FEEDBACK=1000


Eksport tablespace

exp FILE="C:\bck\dump.dmp"
LOG="C:\bck\exp_dump.log"
TRANSPORT_TABLESPACE=Y
TABLESPACES=('USERS' )

Podstawowe skrypty backup i restore

------------- Skrypt BACKUP kopia bazy

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RUN {
ALLOCATE CHANNEL SQLDEV_DISK_BACKUP DEVICE TYPE DISK;
RECOVER COPY OF DATABASE WITH TAG 'ORA_SQLDEV_LEVEL_0';
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE COPIES=1 FOR RECOVER OF COPY WITH TAG 'ORA_SQLDEV_LEVEL_0' DATABASE;
}
ALTER DATABASE OPEN;

--------------Skrypt RESTORE odtworzenie bazy

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RUN {
RESTORE DATABASE UNTIL TIME "TO_DATE('2014-06-01 18:18:57', 'YYYY-MM-DD HH24:MI:SS')";
}
ALTER DATABASE OPEN RESETLOGS;


---------------Skrypt RESTORE datafiles

RUN {
RESTORE DATAFILE  UNTIL TIME "TO_DATE('2014-06-01 18:21:02', 'YYYY-MM-DD HH24:MI:SS')";
}


---------------Skrypt RESTORE tablespace

RUN {
RESTORE TABLESPACE  UNTIL TIME "TO_DATE('2014-06-01 18:23:42', 'YYYY-MM-DD HH24:MI:SS')";
}


-------------Przyklad restore

Backup bez włączonego trybu archivelog

Jeśli mamy wyłączony tryb archivelog to wyświetli się komunikat

Warning: Log archiving (ARCHIVELOG mode) is currently disabled. If
you restore the database from this backup, any transactions that take
place between this backup and the next backup will be lost. It is
recommended that you enable ARCHIVELOG mode before proceeding so
that all transactions can be recovered upon restore. See the section
'Enabling ARCHIVELOG Mode...' in the online help for instructions.
Backup with log archiving disabled will shut down and restart the
database. Are you sure [Y/N]? y
Backup in progress...

Recovery Manager: Release 11.2.0.2.0 - Production on îr Gru 25 13:55:09 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: XE (DBID=2790037099)

RMAN> set echo on;
2> shutdown immediate;
3> startup mount;
4> configure controlfile autobackup format for device type disk clear;
5> configure retention policy to redundancy 2;
6> configure controlfile autobackup on;
7> sql "create pfile=''C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SPF
ILE2INIT.ORA'' from spfile";
8> backup as backupset device type disk database;
9> configure controlfile autobackup off;
10> alter database open;
11> delete noprompt obsolete;
12>
echo set on

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     376635392 bytes

Fixed Size                     1383692 bytes
Variable Size                289407732 bytes
Database Buffers              79691776 bytes
Redo Buffers                   6152192 bytes

RMAN configuration parameters are successfully reset to default value

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

sql statement: create pfile=''C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATAB
ASE\SPFILE2INIT.ORA'' from spfile

Starting backup at 13/12/25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1
.DBF
input datafile file number=00001 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.D
BF
input datafile file number=00004 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DB
F
input datafile file number=00003 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.D
BF
channel ORA_DISK_1: starting piece 1 at 13/12/25
channel ORA_DISK_1: finished piece 1 at 13/12/25
piece handle=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\BACKUPSET\2013_12_25\O
1_MF_NNNDF_TAG20131225T135543_9COOGJOP_.BKP tag=TAG20131225T135543 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:04
Finished backup at 13/12/25

Starting Control File and SPFILE Autobackup at 13/12/25
piece handle=C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\AUTOBACKUP\2013_12_25\
O1_MF_S_835106118_9COOLFW3_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 13/12/25

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored

database opened

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
using channel ORA_DISK_1
no obsolete backups found

Recovery Manager complete.
Backup of the database succeeded.
Log file is at C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\OXE_BACKUP_
CURRENT.LOG.

PEŁNA KULTURA :) BACKUP RMAN

Pełny backup zaczynamy:

rman target \
CONFIGURE CONTROLFILE AUTOBACKUP ON;# Dla wszystkich backupów zawiera plik kontrolny i spfile
CONFIGURE DEFAULT DEVICE TYPE TO DISK;# zmiana nośnika podać nazwę np DH1
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO "C:\bck\%F.bck"; # Nazwa pliku i jego lokalizacja dot. pliku kontrolnego
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT "C:\bck\%d-%Y-%M-%D-%p-%s.bck"; # Nazwa pliku i lokalizacja kopii bazy -recovery catalog
CROSSCHECK ARCHIVELOG ALL;# sprawdza czy istnieją dzienniki
BACKUP DATABASE;# kopia bazy zawierająca pliki kontrolne spfile Uwaga baza musi być w trybie Archivelog

#BACKUP INCREMENTAL LEVEL 1 DATABASE;# W przypadku przyrostowego backupu
BACKUP ARCHIVELOG ALL NOT BACKED UP 2 TIMES;          
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 3'; # usuwa starsze dzienniki niż 3 dni
                                                      
EXIT;

Problem z dostępem do bazy

Mamy problem z dostępem do bazy

C:\Users\admin>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on N Cze 15 20:23:10 2014
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup;
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file 'C:\oraclexe\app\oracle\product\11.2.0\serv
er\dbs/spfileXE.ora'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) Nie mo?na odnale?? okre?lonego pliku.
SQL> startup pfile='C:\oraclexe\app\oracle\admin\XE\pfile\initXE.ora'; lub startup nomount pfile='...
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1382120 bytes
Variable Size              92277016 bytes
Database Buffers           50331648 bytes
Redo Buffers                2482176 bytes
Database mounted.
Database opened.
SQL>

nagle odłączenie instancji

shutdown abort;
startup nomount;

Utworzenie pliku spfile na podstawie pliku pfile

CREATE SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs\initXE.ora'FROM PFILE='C:\oraclexe\app\oracle\admin\XE\pfile\initXE.ora';


Pamiętajmy po zmianie parametru init.ora wykonujemy polecenie

create spfile from pfile;

Pomocne zapytania dla admina

Z jaką wersją Oracle mamy do czynienia

SELECT banner FROM v$version WHERE banner LIKE 'Oracle%'; 

Sprawdzenie czy istnieje restore point (odtwarzanie tylko w wersji Enterprise)

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT