Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use RMAN backup to rebuild the database, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Sometimes the database needs to be completely destroyed and rebuilt for some reason, and the previous RMAN backup exists, hoping to make use of it, such as the system is rebuilt and the database is rebuilt, or the database needs to be restored to another machine after backup.

I. preparation before reconstruction

1. Prepare the test table

Create test tables under RMES users, insert and submit data

Create table rmes.r_rmantest_t (a varchar2 (30)) tablespace rmes

Insert into rmes.r_rmantest_t values ('Before rman backup')

Commit

Do a checkpoint operation to ensure that the data is written to the data file

Alter system checkpoint

2. Prepare the backup path

Create the data file backup path e:\ rman_bak\ mes

Create backup path for control file and initialization parameter file e:\ rman_bak\ mes\ control_bak

Create an online log file backup path e:\ rman_bak\ mes\ redolog

3. Configure RMAN backup parameters

Log in to RMAN to view and configure backup parameters for RMAN

Show all

Automatic backup of control files and spfile files is set to on, and backup path is configured

CONFIGURE CONTROLFILE AUTOBACKUP ON

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'e:\ rman_bak\ mes\ control_bak\% F'

Configure data file backup path

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT'e:\ rman_bak\ mes\% dudes% upright% cations% T'

Confirm the change of backup parameters

Show all

4. Full backup

Backup as compressed backupset database plus archivelog

5. Update the test table

Insert a record into the test table after the backup is completed

Insert into rmes.r_rmantest_t values ('After rman backup')

Commit

Do not checkpoint, so that the data is only recorded in the online log and is not immediately written to the data file.

6. Back up online log files

Copy the online log files to the backup directory e:\ rman_bak\ mes\ redolog under the operating system

All backups are complete, and you can now destroy the database with DBCA on your local machine, or transfer the backup files to another machine for database reconstruction.

II. Rebuilding the database

1. Rebuild the instance service

Execute the oradim command from the operating system command prompt, create a service named mes, and set the environment variable

C:\ > oradim-new-sid mes

C:\ > set oracle_sid=mes

2. Restore the server parameter file and rebuild the missing directory

Log in to RMAN

C:\ Users\ Administrator > rman target /

Recovery Manager: Release 11.2.0.4.0-Production on Thursday May 3 16:55:47 2018

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

Connected to the target database (not started)

Restore the spfile server parameter file in unloaded mode using the previous backup

RMAN > startup nomount

Startup failed: ORA-01078: failure in processing system parameters

LRM-00109:? 'C:\ ORACLE\ PRODUCT\ 11.2.0\ DBHOME_1\ DATABASE\ INITMES.ORA'

Start an Oracle instance without a parameter file to retrieve spfile

Oracle instance has been started

The total system global area is 1068937216 bytes

Fixed Size 2288080 byt

Variable Size 285214256 byt

Database Buffers 775946240 byt

Redo Buffers 5488640 byt

RMAN > restore spfile from'e:\ rman_bak\ mes\ control_bak\ C Mel 2056489697-20180503-01'

Start restore at 16:58:49 on 2018-05-03

Replace the recovery directory with the target database control file

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: SID=396 device type = DISK

Channel ORA_DISK_1: restoring spfile from AUTOBACKUP e:\ rman_bak\ mes\ control_bak\ Cmur2056489697-20180503-01

Channel ORA_DISK_1: restore SPFILE from AUTOBACKUP completed

Completion of restore at 16:58:50 in 2018-05-03

Create a pfile file

RMAN > sql'create pfile from spfile'

Sql statement: create pfile from spfile

The default location of this file in Windows is% ORACLE_HOME%\ database\ INITmes.ORA

Open the pfile text file and create the directory mentioned in the file, as in this case you need to make sure that the following directory exists

C:\ Oracle\ admin\ mes\ adump\

D:\ ORADATA\ MES\

E:\ FAST_RECOVERY_AREA\ MES\

E:\ fast_recovery_area\ mes\ archivelog\

3. Restore control files

Restart the instance to non-load mode with the restored server parameter file, and restore the control file

RMAN > shutdown abort

Oracle instance is closed

RMAN > startup nomount

Connected to the target database (not started)

Oracle instance has been started

The total system global area is 1286066176 bytes

Fixed Size 2280896 byt

Variable Size 771752512 byt

Database Buffers 503316480 byt

Redo Buffers 8716288 byt

RMAN > restore controlfile from'e:\ rman_bak\ mes\ control_bak\ C Mel 2056489697-20180503-01'

Start restore at 17:06:41 on 2018-05-03

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: SID=156 device type = DISK

Channel ORA_DISK_1: restoring control files

Channel ORA_DISK_1: restore completed. Time: 00:00:02

Output file name = D:\ ORADATA\ MES\ CONTROL01.CTL

Output file name = E:\ FAST_RECOVERY_AREA\ MES\ CONTROL02.CTL

Completion of restore at 17:06:43 in 2018-05-03

4. Restore the database

Load the database with the restored control file

RMAN > alter database mount

The database is mounted

Released channel: ORA_DISK_1

Restore database

RMAN > restore database

Start restore at 17:19:26 on 2018-05-03

Use channel ORA_DISK_1

Channel ORA_DISK_1: starting to restore data file backup set

Channel ORA_DISK_1: specifying data files to restore from the backup set

Channel ORA_DISK_1: restore the data file 00001 to D:\ ORADATA\ MES\ SYSTEM01.DBF

Channel ORA_DISK_1: restore the data file 00002 to D:\ ORADATA\ MES\ SYSAUX01.DBF

Channel ORA_DISK_1: restore the data file 00003 to D:\ ORADATA\ MES\ UNDOTBS01.DBF

Channel ORA_DISK_1: restore the data file 00004 to D:\ ORADATA\ MES\ USERS01.DBF

Channel ORA_DISK_1: restore the data file 00005 to D:\ ORADATA\ MES\ EXAMPLE01.DBF

Channel ORA_DISK_1: restore the data file 00006 to D:\ ORADATA\ MES\ CMES01.DBF

Channel ORA_DISK_1: restore the data file 00007 to D:\ ORADATA\ MES\ RMES01.DBF

Channel ORA_DISK_1: restore the data file 00008 to D:\ ORADATA\ MES\ HMES01.DBF

Channel ORA_DISK_1: restore the data file 00009 to D:\ ORADATA\ MES\ INDX01.DBF

Channel ORA_DISK_1: restore the data file 00010 to D:\ ORADATA\ MES\ FDA01.DBF

Channel ORA_DISK_1: reading backup fragment E:\ RMAN_BAK\ MES\ MES_85T1VQN6_1_20180503

Channel ORA_DISK_1: segment handle = E:\ RMAN_BAK\ MES\ MES_85T1VQN6_1_20180503 tag = TAG20180503T163757

Channel ORA_DISK_1: backup fragment 1 restored

Channel ORA_DISK_1: restore completed. Time: 00:00:56

Completion of restore at 17:20:23 in 2018-05-03

During this period, you may encounter an error prompt that ORA-19693: backup fragments have been included, which is due to repeated cataloging of backup information in the control file. You can rename the backup file path first, then cross-check the crosscheck backup, delete and log out the backup information with delete expired backup, and then re-catalog and register the backup catalog start with '...'. After this processing, restore restore should solve the problem.

5. Copy back the online log files

Confirm where the online log files should be stored

17:16:07 SYS @ mes AS SYSDBA > select member from v$logfile

MEMBER

D:\ ORADATA\ MES\ REDO03.LOG

D:\ ORADATA\ MES\ REDO02.LOG

D:\ ORADATA\ MES\ REDO01.LOG

Copy the online log files backed up in e:\ rman_bak\ mes\ redolog under the operating system back to the specified online log directory.

6. Restore and open the database

If you have previously used block change tracking, you should cancel block change tracking before performing a restore, because the DBF data file for block change tracking no longer exists and will not be backed up during RMAN backup

Alter database disable block change tracking

Restore the database

RMAN > recover database

Start recover at 17:21:18 on 2018-05-03

Use channel ORA_DISK_1

Starting media recovery

The archive log of thread 1 sequence 7 already exists on disk as a file D:\ ORADATA\ MES\ REDO01.LOG

The archive log of thread 1 sequence 8 already exists on disk as a file D:\ ORADATA\ MES\ REDO02.LOG

Archive log file name = D:\ ORADATA\ MES\ REDO01.LOG thread = 1 sequence = 7

Archive log file name = D:\ ORADATA\ MES\ REDO02.LOG thread = 1 sequence = 8

Media recovery complete, time: 00:00:01

Completion of recover at 17:21:23 in 2018-05-03

Open the database and reset the online log sequence

RMAN > alter database open resetlogs

The database is open

7. Verify the recovery of data

Check the test table to verify that the data is fully restored

17:18:45 SYS @ mes AS SYSDBA > select * from rmes.r_rmantest_t

A

-

Before rman backup

After rman backup

At this point, the re-creation and recovery of the mes database are completed.

8. Problems needing attention in remote recovery of RMAN

According to the location of the directory where the backup file is stored, modify the direction and then restore it.

Catalog start with'e:\ rman_bak\ mes'

Restore database

Note when recovering from 10g to 11g environment:

1) execute the pre-upgrade tool script utlirp.sql before recovery

2) restore the database

3) Open the database in startup upgrade mode

4) execute the upgrade script catupgrd.sql

If the pre-upgrade script is not executed in advance, an error will be reported when executing the above script because the table registry$database is missing the TZ_VERSION field. The solution is to modify the table registry$database, add the tz_version field, number type, and reinsert the record

Truncate table registry$database

Insert into registry$database

(platform_id,platform_name,edition,tz_version)

Values

((select platform_id from v$database))

(select platform_name from v$database)

Null

(select version from v$timezone_file))

There will be no problem with executing the upgrade script catupgrd.sql after that.

Thank you for reading this article carefully. I hope the article "how to use RMAN backup to rebuild database" shared by the editor will be helpful to everyone. At the same time, I also hope you can support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.