Oracle Database restoration example

Solution to the loss of tablespace In the Oracle9i rollback segment:

Example of restoring a database with implicit parameters:

The procedure is as follows:

First, change the automatic management in the initial init. ora file to manual management, and then add the implicit parameter:
# Undo_management = auto
Undo_tablespace = undotbs
_ Syssmu1 $, _ syssmu2 $, _ syssmu3 $, _ syssmu4 $, _ syssmu5 $, _ syssmu6 $, _ syssmu7 $, _ syssmu8 $, _ syssmu9 $, _ syssmu10 $)

SQL> startup Mount (the database is started to mount)
SQL> alter database datafile 'd: \ oracle \ oradata \ orcl \ undotbs01.dbf' offline drop;
Database altered.

SQL> alter database open;
Database opened.
SQL> show parameter undo

Name type value
--------------------------------------------------------
Undo_management string Manual
Undo_retention integer 900
Undo_suppress_errors Boolean false
Undo_tablespace string undotbs

SQL> drop tablespace undotbs including contents;
Tablespace dropped.

Recreate the undotbs tablespace:
SQL> Create undo tablespace undotbs datafile 'd: \ oracle \ oradata \ orcl \ undotbs01.dbf'
Size 100 m;
Tablespace created.

SQL> shutdown immediate (shut down the database)
Database closed.
Database dismounted.
Oracle instance shut down.

Edit the init. ora initialization parameter file, remove the implicit parameter, and set
Undo_management = auto
Undo_tablespace = undotbs
Save the init. ora file and run
SQL> startup Mount
Oracle instance mounted.
Total system global area 114061244 bytes
Fixed size 282556 bytes
Variable Size 79691776 bytes
Database buffers 33554432 bytes
Redo buffers 532480 bytes
Database mounted.

SQL> alter database datafile 'd: \ oracle \ oradata \ orcl \ undotbs01.dbf 'online;
Database altered.

SQL> alter database open;
Database opened.
SQL> show parameter undo

Name type value
-----------------------------------------------------------------------------
Undo_management string auto
Undo_retention integer 900
Undo_suppress_errors Boolean false
Undo_tablespace string undotbs

A database restoration method has been summarized through experiments recently, which is helpful for future work:

The database is not in the archive state, and only backup of data files a week ago, with no redolog, archive log, and controlfile. In this case, if a database failure occurs, the database can only be completely recovered, all data from the time when the backup was made one week ago to the time when the fault occurred will be lost. The specific recovery method is as follows:

The operating system is solaris8, with 2 GB memory and 2 CPUs.

Tutorial steps:
$ Sqlplus/nolog
SQL> connect/As sysdba
SQL> archive log list
Database Log mode No archive Mode
Automatic Archival Enabled
Archive destination/opt/Oracle/ARCH/orcl
Oldest online log sequence 895
Current Log sequence 897

SQL> select * from V $ logfile;

Group # status
-----------------
Member
--------------------------------------------------------------------------------
3
/Opt/Oracle/DB04/oradata/orcl/redo03.log

2
/Opt/Oracle/db03/oradata/orcl/redo02.log

1
/Opt/Oracle/DB02/oradata/orcl/redo01.log

SQL> select * from V $ controlfile;

Status
-------
Name
--------------------------------------------------------------------------------

/Opt/Oracle/DB02/oradata/orcl/control01.ctl

/Opt/Oracle/db03/oradata/orcl/control02.ctl

/Opt/Oracle/DB04/oradata/orcl/control03.ctl

SQL> alter Database Backup controlfile to trace;
(Backup control file. The trace file is generated in the $ oracle_base/admin/orcl/udump directory)

SQL> shutdown immediate (close the current database)
Database closed.
Database dismounted.
Oracle instance shut down.
SQL> quit
Disconnected

Simulate data loss:

Delete all data files, control files, and redolog files:

$ Rm-RF? /Opt/Oracle/DB02/oradata/orcl /*

$ RM/opt/Oracle/db03/oradata/orcl/redo02.log

$ RM/opt/Oracle/db03/oradata/orcl/control02.ctl

$ RM/opt/Oracle/DB04/oradata/orcl/redo03.log

$ RM/opt/Oracle/DB04/oradata/orcl/control03.ctl

Copy back the data file backed up a week ago. The directory structure is the same as before. However, because there is no redolog or controlfile file, the database can only be started to nomount:

Edit the orcl_ora_7140.trc file generated under the udump directory, paste the part of the controlfile created in it and put it in SQL for execution:
(The resetlogs method must be used to recreate the control file. After resetlogs is used, a new redolog is generated and the sequence of the current redofile is set to 1. Otherwise, the creation of the control file will fail ):
SQL> startup nomount
SQL> Create controlfile reuse Database "orcl" resetlogs noarchivelog
2 maxlogfiles 32
3 maxlogmembers 2
4 maxdatafiles 254
5 maxinstances 8
6 maxloghistory 907
7. logfile
8 Group 1'/opt/Oracle/DB02/oradata/orcl/redo01.log' size 50000 K,
9 Group 2 '/opt/Oracle/db03/oradata/orcl/redo02.log' size 50000 K,
10 group 3'/opt/Oracle/DB04/oradata/orcl/redo03.log' size 50000 K
11 datafile
12'/opt/Oracle/DB02/oradata/orcl/system01.dbf ',
13 '/opt/Oracle/DB02/oradata/orcl/tools01.dbf ',
14'/opt/Oracle/DB02/oradata/orcl/rbs01.dbf ',
15'/opt/Oracle/DB02/oradata/orcl/temp01.dbf ',
16'/opt/Oracle/DB02/oradata/orcl/users01.dbf ',
17 '/opt/Oracle/DB02/oradata/orcl/indx01.dbf ',
18'/opt/Oracle/DB02/oradata/orcl/drsys01.dbf ',
19 '/opt/Oracle/DB02/oradata/orcl/wacos. dbf ',
20'/opt/Oracle/DB02/oradata/orcl/wacos01.dbf ',
21 '/opt/Oracle/DB02/oradata/orcl/wacos02.dbf ',
22 '/opt/Oracle/DB02/oradata/orcl/wacos03.dbf ',
23'/opt/Oracle/DB02/oradata/orcl/wacos04.dbf ',
24 '/opt/Oracle/DB02/oradata/orcl/wacos05.dbf ',
25'/opt/Oracle/DB02/oradata/orcl/wacos06.dbf ',
26'/opt/Oracle/DB02/oradata/orcl/NMS. dbf ',
27 '/opt/Oracle/DB02/oradata/orcl/test. dbf'
28 Character Set we8iso8859p1;

Control File Created.

SQL> alter database open resetlogs; (open the database in resetlogs Mode)
Database altered.

SQL> select status from V $ instance; (check the database status)
Status
-------
Open

SQL> select * from V $ logfile; (check the status of logfile)
Group # status
-----------------
Member
--------------------------------------------------------------------------------
3
/Opt/Oracle/DB04/oradata/orcl/redo03.log

2
/Opt/Oracle/db03/oradata/orcl/redo02.log

1
/Opt/Oracle/DB02/oradata/orcl/redo01.log

SQL> select * from V $ controlfile; (check the Control File status)

Status
-------
Name
--------------------------------------------------------------------------------

/Opt/Oracle/DB02/oradata/orcl/control01.ctl

/Opt/Oracle/db03/oradata/orcl/control02.ctl

/Opt/Oracle/DB04/oradata/orcl/control03.ctl

This article is reproduced