Shulou(Shulou.com)06/01 Report--

Data backup is of course preparation for data recovery. Oracle of the new environment has been backing up all the time, but there are no tests to verify the effectiveness of the backup, so the importance of this test is self-evident! The following is an example of RMAN offline recovery on WIN platform.

Source and sink

Target library

Operating system

WIN SVR 2008 R2

WIN SVR 2008 R2

Hostnam

Ora

ORATEST

IP

192.168.18.20

192.168.18.25

Database version

11.2.0.1.0

11.2.0.1.0

Storage mode

Single instance

Single instance

ORACLE_HOME

D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1

D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1

ORACLE_SID

HWPROD

HWPROD

Source database backup operation:

The relevant backup configurations and scripts are as follows:

RMAN > show all

The RMAN configuration parameters of the database for which db_unique_name is HWPROD are:

CONFIGURE RETENTION POLICY TO RECOVERYWINDOW OF 3 DAYS; # keep backup for 3 days

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON; # needs to turn on automatic backup

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FORDEVICE TYPE DISK TO'e:\ data_backup\

Ctl_%F.bak'; # specify backup control file and parameter file backup path format

CONFIGURE DEVICE TYPE DISK PARALLELISM 1BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICETYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FORDEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' ASOF RELEASE' DEFAULT' OPTIMIZE FOR LOA

D TRUE; # default

CONFIGURE ARCHIVELOG DELETION POLICY TONONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO'D:\ APP\ ADMINISTRATOR\ PRODUCT\ 11.2.0\ DBHO

ME_1\ DATABASE\ SNCFHWPROD.ORA'; # default

Full_backup.sql

Run {allocate channel D1 type disk; allocate channel d2 type disk;backup as compressed backupset fulldatabase format'e:\ data_backup\ full_%d_%s_%p_%u_%t.bak';sql 'alter system archive logcurrent'; backup archivelog all format'e:\ data_backup\ log_%d_%s_%p_%u_%t.bak' delete all input; release channel D1 boot release channel d2 political report obsolete; crosscheck backup; delete noprompt expired backup; delete noprompt obsolete;}

Full_backup.bat

Set oracle_sid=hwprodset dudes% datelus 5 5, 2% datelog 8, 2% echo= > e:\ rman_script\ log\ full_backup_%d%.log rman target / cmdfile=e:\ rman_script\ full_backup.sqlmsglog=e:\ rman_script\ log\ full_backup_%d%.log

Steps for RMAN recovery:

Initialize the database and install the same environment

Restore parameter file

Restore control file

Start the database to the MOUNT state and use the control file for data recovery

View archive log backup sequence

Restore-recover-alter database open ressetlogs

Validate

Set up snooping

1. After the system installation is complete, install the ORACLE software, choose to install only the software, and install the same path as the source database, and then copy the relevant backup files to the original backup path. In order to avoid unnecessary trouble, it is recommended that the settings be consistent with the source database.

2. Establish relevant password files, services and listeners, otherwise you cannot connect to DB

C:\ Users\ Administrator > D: d:\ > cd D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ BIN D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ BIN > orapwd file=D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ database\ pwdhwprod.ora password=oracle entries=5; D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ BIN > oradim-new-sid HWPROD-startmode m instance has been created. D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ BIN > set ORACLE_SID=HWPROD

D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ BIN >

D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ BIN > sqlplus/ as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Friday April 21 14 Production on 1948 2017

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

Connected to an idle routine.

SQL >

You can now connect to the DB.

3. Manually create a pfile file (to boot to the nomount state using the database) and place it in the D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ database directory. The inithwprod.ora content is as follows:

Db_name=HWPROD

Java_pool_size=4194304

Large_pool_size=4194304

Shared_pool_size=96468992

Use this pfile to start the database to the nomount state:

SQL > startuppfile='D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ database\ inithwprod.ora' nomount

The ORACLE routine has been started.

Total System Global Area 162873344 bytes

Fixed Size 2173800 bytes

Variable Size 104858776 bytes

Database Buffers 50331648 bytes

Redo Buffers 5509120 bytes

4. Switch to RMAN and SETDBID=3279461817 (source database DBID):

5. Create flash_recovery_area and ORADATA directories, otherwise an error ORA-01263: Name given for file destination directory is invalid will be reported when restoring data.

Set up relevant catalogs:

6. Restore the spfile file:

Restore SPfile file, find the files related to the source spfile backup to restore:

RMAN > restore spfile from'e:\ data_backup\ CTL_C-3279461817-20180116-00.BAK'

After restoring the parameter file, shutdown database, exit RMAN, find the recovered parameter file, copy and modify the relevant parameters to inithwprod.ora file (here, it should be noted that the source database may have a large space, and if it is not set up properly, errors such as ORA-27102 out of memory will be reported when starting the database to nomount, so sga,pga and so on will be modified according to the target database as appropriate)

The following is the contents of the modified inithwprod.ora file:

Hwprod.__java_pool_size=33554432hwprod.__large_pool_size=33554432hwprod.__oracle_base='d:\ app\ Administrator'#ORACLE_BASE set from environmenthwprod.__pga_aggregate_target=536870912hwprod.__sga_target=536870912hwprod.__shared_io_pool_size=0hwprod.__shared_pool_size=536870912hwprod.__streams_pool_size=16777216*.audit_file_dest='d:\ app\ Administrator\ admin\ hwprod\ adump'*.audit_trail='db'*.compatible='11.2.0.0.0 '* .control _ files='d:\ app\ Administrator\ oradata\ hwprod\ control01.ctl' 'd:\ app\ Administrator\ flash_recovery_area\ hwprod\ control02.ctl'*.db_block_size=8192*.db_domain='hwprod_test'*.db_name='hwprod'*.db_recovery_file_dest='d:\ app\ Administrator\ flash_recovery_area'*.db_recovery_file_dest_size=2147483648*.diagnostic_dest='d:\ app\ Administrator'*.dispatchers=' (PROTOCOL=TCP) (SERVICE=hwprodXDB)'* .log _ archive_format='ARC%S_%R.% T'*.memory_target=1073741824*.nls_language='SIMPLIFIED CHINESE'*.nls_territory='CHINA'*.open_cursors=100*.processes=500*.remote_login_passwordfile='EXCLUSIVE'*.undo_tablespace='UNDOTBS1'

After the modification is completed, enter the RMAN and change the database startup nomount status

SQL > startup nomount pfile='D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ database\ inithwprod.ora'

7. At this time, you can restore the control files and find the files related to the backup of the source control files for recovery:

RMAN > restore controlfile from'E:\ data_backup\ CTL_C-3279461817-20170427-01'

After the control file is restored, then change the database to MOUNT state:

8. Check the status of archive log backups. The maximum sequence is 18437.

RMAN > list backup of archivelog all

9. Restore data files:

RMAN > run {2 > set until sequence 18437 [3 > restore database;4 >}

RMAN > run {2 > set until sequence 18437 [3 > recover database;4 >}

10. Resetlogs, open the database:

RMAN > alter database open resetlogs

11. Check the database status:

12. Add registry SID:

HKEY_LOCAL_MACHINE\ SOFTWARE\ ORACLE\ KEY_OraDb11g_home1 "ORACLE_SID" = "HWPROD"

13. Create SPfile parameter file

SQL > create spfile from pfile

You can see that the relevant path has created a new parameter file:

14. Restart the database and whether to start it with SPFILE:

SQL > select decode (count (*), 1 from v$spparameter where rownum=1 and isspecified from v$spparameter where rownum=1 and isspecified = 'TRUE'')

15. Modify listener.ora,tnsnames.ora

Listener.ora

# listener.ora Network Configuration File:D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ NETWORK\ ADMIN\ listener.ora# Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (GLOBAL_DBNAME = HWPROD) (ORACLE_HOME = D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1) (SID_NAME = HWPROD)) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = ORATEST) (PORT = 1521)) ADR_BASE_LISTENER = D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ log

Tnsnames.ora

# tnsnames.ora Network Configuration File:D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ NETWORK\ ADMIN\ tnsnames.ora# Generated by Oracle configuration tools. HWPROD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.18.25) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = HWPROD)

View the monitoring status:

Verify the data, OK!

Updated on 2018-01-16

Reference: http://blog.itpub.net/29119536/viewspace-1171894/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.