Base de Conhecimento

Como restaurar um database Oracle

As seguintes instruções são referencias para a restauração de base de dados Oracle: 1. Instalar o AdentroOBM no Oracle database server. 2. Restaurar o Oracle database backup arquivos de AdentroOBS server. 3.Para restore de uma base existente, primeiro de um shutdown na database:

• Defina o ORACLE_SID de sua database.

Windows:
set ORACLE_SID=${database_SID}
Linux:
export ORACLE_SID=${database_SID}

• Conecte no database como SYSDBA

Connect to database as SYSDBA

• De um Shutdown na database.

SQL> shutdown immediate

Exemplo:
$ export ORACLE_SID=GDB1
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 – Production on Thu Nov 8
17:04:57 2007
Copyright © 1982, 2005, Oracle. All right reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 –
Production With the Partitioning, OLAP and Data Mining
options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

4. Para restaurar de um banco de dados não-existente, primeiro crie um arquivo de senha:

orapwd file=$ORACLE_HOME/dbs/orapw${database_SID}
password=${password}

Exemplo:
$ orapwd file=/oracle/OraHome1/dbs/orapwBGDB1
password=pwd123

5. Coloque os arquivos de backup restaurados para seu caminho do diretório original: Arquivos de controle, arquivos de dados e logs arquivados são armazenados em AdentroOBS junto com suas informações de caminho completo.

Por exemplo, com os dados restaurados para o diretório / obm_restore:

/obm_restore/Oracle Database
Server/oracle/product/10.2.0/db_1/admin/GDB1/
/obm_restore/Oracle Database
Server/oracle/product/10.2.0/db_1/dbs/initGDB1.ora
/obm_restore/Oracle Database
Server/oracle/product/10.2.0/db_1/dbs/spfileGDB1.ora
/obm_restore/Oracle Database
Server/oracle/product/10.2.0/db_1/flash_recovery_area/GDB1
/obm_restore/Oracle Database
Server/oracle/product/10.2.0/db_1/oradata/GDB1

Esses arquivos devem ser transferidos para:

/oracle/product/10.2.0/db_1/admin/GDB1/
/oracle/product/10.2.0/db_1/dbs/initGDB1.ora
/oracle/product/10.2.0/db_1/dbs/spfileGDB1.ora
/oracle/product/10.2.0/db_1/flash_recovery_area/GDB1/
/oracle/product/102.0/db_1/oradata/GDB1/

Renomear o database (banco de dados para restaurar para um novo local):

• Modifique o arquivo PFILE para atualizar o caminho do arquivo.

Modificar todos os caminhos de arquivo para refletir a mudança para o novo local, e em seguida salvar.

Por exemplo

Origem:
background_dump_dest = /oracle/OraHome1/admin/GDB2/bdump
control_files = (/oracle/OraHome1/oradata/GDB2/control01.ctl,
/oracle/OraHome1/oradata/GDB2/control02.ctl,
/oracle/OraHome1/oradata/GDB2/control03.ctl)
core_dump_dest = /oracle/OraHome1/admin/GDB2/cdump
user_dump_dest = /oracle/OraHome1/admin/GDB2/udump

Mudar para:
background_dump_dest =
/new_db_location/OraHome1/admin/GDB2/bdump
control_files =
(/new_db_location/OraHome1/oradata/GDB2/control01.ctl,
/new_db_location/OraHome1/oradata/GDB2/control02.ctl,
/new_db_location/OraHome1/oradata/GDB2/control03.ctl)
core_dump_dest = /new_db_location/OraHome1/admin/GDB2/cdump
user_dump_dest = /new_db_location/OraHome1/admin/GDB2/udump

• Indicar os valores para o Dispatchers:

Dispatchers = "(PROTOCOL=TCP) (SERVICE=GDB1XDB)"

• Delete o arquivo SPFILE.
• Set o Oracle_SID para seu identificador System Identifier(SID)

Windows:
set ORACLE_SID=${database_SID}
Linux:
export ORACLE_SID=${database_SID}

• Rode o SQL Plus e conecte no database como SYSDBA.

sqlplus "/ as sysdba"

• Monte e inicie o database.

SQL> startup mount

• Crie um backup do control file para o trace.

SQL> alter database backup controlfile to trace as
‘New_DB_Location/CONTROL.TRC’ reuse;

• Renomeie cada arquivo datafile, log file e temp file

Abra o arquivo recém-criado do trace e verifique se o nome de cada arquivo de dados, arquivo de log e tempfile. Renomeie cada um desses arquivos:

SQL> alter database rename file ‘xxx’ to ‘yyy’;
Where xxx is the old filename found in the trace f
yyy is the new filename with the updated path.

Exemplo:
SQL> alter database rename file
/oracle/product/10.2.0/db_1/oradata/GDB1/system01.dbf' to
/new_db_location/oradata/GDB1/system01.dbf';
SQL> alter database rename file
/oracle/product/10.2.0/db_1/oradata/GDB1/undotbs01.dbf' to
/new_db_location/oradata/GDB1/undotbs01.dbf';
SQL> alter database rename file
/oracle/product/10.2.0/db_1/oradata/GDB1/sysaux01.dbf' to
/new_db_location/oradata/GDB1/sysaux01.dbf';
SQL> alter database rename file
/oracle/product/10.2.0/db_1/oradata/GDB1/users01.dbf' to
/new_db_location/oradata/GDB1/users01.dbf'; 
SQL> alter database rename file
'/oracle/product/10.2.0/db_1/oradata/GDB1/TS1' to
'/new_db_location/oradata/GDB1/TS1';
SQL> alter database rename file
'/oracle/product/10.2.0/db_1/oradata/GDB1/redo01.log' to
'/new_db_location/oradata/GDB1/redo01.log';
SQL> alter database rename file
'/oracle/product/10.2.0/db_1/oradata/GDB1/redo02.log' to
'/new_db_location/oradata/GDB1/redo02.log';
SQL> alter database rename file
'/oracle/product/10.2.0/db_1/oradata/GDB1/redo03.log' to
'/new_db_location/oradata/GDB1/redo03.log';
SQL> alter database rename file
'/oracle/product/10.2.0/db_1/oradata/GDB1/temp01.dbf' to
'/new_db_location/oradata/GDB1/temp01.dbf';

Exemplo:
$ export ORACLE_SID=GDB1
$ sqlplus "/ as sysdba"
SQL*PLUS: Release 10.2.0.1.0 – Production on Fri Nov 9
17:50:30 2007
Copyright © 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database backup controlfile to trace as
'/new_db_location/control.trc' reuse;
Database altered.
SQL> alter database rename file
'/oracle/product/10.2.0/db_1/oradata/GDB1/system01.dbf' to
'/new_db_location/oradata/GDB1/system01.dbf';
Database altered.
SQL> alter database rename file
'/oracle/product/10.2.0/db_1/oradata/GDB1/undotbs01.dbf'
'/new_db_location/oradata/GDB1/undotbs01.dbf';
Database altered.
SQL> alter database rename file
'/oracle/product/10.2.0/db_1/oradata/GDB1/sysaux01.dbf' t
'/new_db_location/oradata/GDB1/sysaux01.dbf';
Database altered.
SQL> alter database rename file
'/oracle/product/10.2.0/db_1/oradata/GDB1/users01.dbf' to
'/new_db_location/oradata/GDB1/users01.dbf';
Database altered.
SQL> alter database rename file
'/oracle/product/10.2.0/db_1/oradata/GDB1/TS1' to
'/new_db_location/oradata/GDB1/TS1';
Database altered.
SQL> alter database rename file
'/oracle/product/10.2.0/db_1/oradata/GDB1/redo01.log' to
'/new_db_location/oradata/GDB1/redo01.log';
Database altered.
SQL> alter database rename file
'/oracle/product/10.2.0/db_1/oradata/GDB1/redo02.log' to
'/new_db_location/oradata/GDB1/redo02.log';
Database altered.
SQL> alter database rename file
'/oracle/product/10.2.0/db_1/oradata/GDB1/redo03.log' to
'/new_db_location/oradata/GDB1/redo03.log';
Database altered.
SQL> alter database rename file
'/oracle/product/10.2.0/db_1/oradata/GDB1/temp01.dbf' to
'/new_db_location/oradata/GDB1/temp01.dbf';
Database altered.

• Set o ORACLE_SID para seu database System Identifier(SID)

Windows:
set ORACLE_SID=${database_SID}
Linux:
export ORACLE_SID=${database_SID}

• Execute o Oracle Recovery Manager (rman) e conecte o database alvo.

rman target /

• Start e monte o database

RMAN> startup mount

• Aplique as transactions do archived log para a próxima sequence.

RMAN> recover database until sequence=4 thread=1;

O comando acima assume que o número de seqüência do último log arquivado é 3. Os números de seqüência podem ser encontrados no nome do arquivo do archived log:

${Oracle_Home}/dbs/ol_mf_1_2_3xxxxxxx_.arc
${Oracle_Home}/dbs/ol_mf_1_3_3xxxxxxx_.arc

• Abre o database

RMAN> alter database open resetlogs;

Exemplo:
$ export ORACLE_SID=GDB1
$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Nov 8
17:46:27 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 113247824 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes
RMAN> recover database until sequence=4 thread=1;
Starting recover at 08-NOV-07
Starting implicit crosscheck backup at 08-NOV-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
Finished implicit crosscheck backup at 08-NOV-07
Starting implicit crosscheck copy at 08-NOV-07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 08-NOV-07
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name:
/oracle/product/10.2.0/db_1/flash_recovery_area/GDB1/archivelo
g/2007_11_08/o1_mf_1_3_3m5h1yby_.arc
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file
/oracle/product/10.2.0/db_1/flash_recovery_area/GDB1/archivelo
g/2007_11_08/o1_mf_1_2_3m5h1svs_.arc
archive log thread 1 sequence 3 is already on disk as file
/oracle/product/10.2.0/db_1/flash_recovery_area/GDB1/archivelo
g/2007_11_08/o1_mf_1_3_3m5h1yby_.arc
archive log
filename=/oracle/product/10.2.0/db_1/flash_recovery_area/GDB1/
archivelog/2007_11_08/o1_mf_1_2_3m5h1svs_.arc thread=1
sequence=2
archive log
filename=/oracle/product/10.2.0/db_1/flash_recovery_area/GDB1/
archivelog/2007_11_08/o1_mf_1_3_3m5h1yby_.arc thread=1
sequence=3
media recovery complete, elapsed time: 00:00:01
Finished recover at 08-NOV-07
RMAN> alter database open resetlogs;
database opened

Opcional - Crie o Net Service Name e Database Service Listener:

Para criar o Net Service Name' Start Net Manager:

Windows Clique no [Menu Inciar], selecione [Programas]. Selecione [Oracle - ${Home_Name}], em [Configuration and Migration Tools], Selecione [Net Manager].

Linux Rode netmgr de ${Oracle_Home}/bin:

netmgr

Use [Oracle Net Configuration], [Local].

• Selecione [Service Naming] e abra abra a árvore na esquerda.

• O [Net Service Name] wizard serpa iniciado para proceder com a criação

do net service name.

Nota: Certifique-se que a configuração foi salva No menu, clique em [File], depois [Save Network Configuration].

Criando o Database Service Listener Inicie Net Manager:

Windows Clique em [Start Menu], selecione [Programs]. Selecione [Oracle - ${Home_Name}], em [Configuration and Migration Tools], selecione [Net Manager]

Linux Rode netmgr from ${Oracle_Home}/bin:

netmgr

Expanda [Oracle Net Configuration], [Local].

• Em [Listeners] selecione [LISTENER].
• Selecione [Database Services] no menu, e clique [AddDatabase].

Entre com o Global Database Name e SID.

Nota: Tenha certeza que as configurações foram salvas com exito. No menu, clique em [File], depois [Save Network Configuration].