Backup and Recovery in window environment

C:\Documents and Settings\xuzhengzhu>sqlplus/nolog

Sql*plus:release 10.2.0.1.0-production on Thu January 29 17:02:04 2015

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

Sql> Conn sys/[email protected] as Sysdba
is connected.
Sql> Shutdwon
sp2-0042: Unknown Command "Shutdwon"-the remaining rows are ignored.
sql> shutdown
The database is closed.
The database has been uninstalled.
The ORACLE routine has been closed.
Sql> Startup Mount
The ORACLE routine has been started.

Total System Global area 1073741824 bytes
Fixed Size 1253072 bytes
Variable Size 142606640 bytes
Database buffers 922746880 bytes
Redo buffers 7135232 bytes
The database is loaded.
sql> ALTER DATABASE Archivelog;

The database has changed.

sql> ALTER DATABASE open;

The database has changed.

Sql> alter system archive log current;

The system has changed.

Sql> set Linesize 1500
Sql> select File_name,tablespace_name from Dba_data_files;

file_name
----------------------------------------------------------------------------------
------------- ------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01. Dbf
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01. Dbf
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01. Dbf
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01. Dbf
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST01. Dbf
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST02. Dbf
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\MM. Dbf

7 rows have been selected.

sql> alter TABLESPACE system begin backup;

Table space has changed.

Sql>
Sql> host copy D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01. DBF E:\oraclebak
1 files have been copied.

sql> alter TABLESPACE system end backup;

Table space has changed.

sql> alter tablespace USERS begin backup;

Table space has changed.

Sql> host copy D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01. DBF E:\oraclebak
1 files have been copied.

Sql> alter tablespace USERS end backup;

Table space has changed.

sql> alter tablespace Sysaux begin backup;

Table space has changed.

Sql> host copy D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01. DBF E:\oraclebak
1 files have been copied.

Sql> alter tablespace Sysaux end backup;

Table space has changed.

sql> alter tablespace UNDOTBS1 begin backup;

Table space has changed.

Sql> host copy D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01. DBF E:\oraclebak
1 files have been copied.

Sql> alter tablespace UNDOTBS1 end backup;

Table space has changed.

sql> alter tablespace TEST begin backup;

Table space has changed.

Sql> host copy D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST01. DBF E:\oraclebak
1 files have been copied.

Sql> host copy D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST02. DBF E:\oraclebak
1 files have been copied.

Sql> alter tablespace TEST end backup;

Table space has changed.

sql> alter tablespace WZ begin backup;

Table space has changed.

Sql> host copy D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\MM. DBF E:\oraclebak
1 files have been copied.

Sql> alter tablespace WZ end backup;

Table space has changed.

sql> ALTER DATABASE backup Controlfile to ' E:\oraclebak\controlfile.bak ';

The database has changed.

sql> ALTER DATABASE backup Controlfile to trace;

The database has changed.

Sql>
Sql> alter system archive log current;

The system has changed.

sql> alter system switch logfile;

The system has changed.

sql> shutdown immediate;
The database is closed.
The database has been uninstalled.
The ORACLE routine has been closed.
Sql>
Sql> host del D:\oracle\product\10.2.0\oradata\orcl\TEST01. Dbf

Sql> Startup
The ORACLE routine has been started.

Total System Global area 1073741824 bytes
Fixed Size 1253072 bytes
Variable Size 150995248 bytes
Database buffers 914358272 bytes
Redo buffers 7135232 bytes
The database is loaded.
ORA-01157: Unable to identify/lock data file 5-see DBWR trace file
ORA-01110: Data file 5: ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST01. DBF '

Sql> select * from V$recover_file;

file# ONLINE online_ ERROR change# time
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------ --
5 Online online FILE not FOUND 0

Sql>
Sql> host copy E:\oraclebak\TEST01. DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL
1 files have been copied.

sql> Recover datafile 5
Complete media recovery.
sql> ALTER DATABASE datafile 5 online;

The database has changed.

sql> ALTER DATABASE open;

The database has changed.

System Tablespace data file or a large number of data files are damaged

Repeat the above hot backup operation, and then:

sql> shutdown immediate;
The database is closed.
The database has been uninstalled.
The ORACLE routine has been closed.
Sql> host del D:\oracle\product\10.2.0\oradata\orcl\SYSTEM01. Dbf

Sql> Startup
The ORACLE routine has been started.

Total System Global area 1073741824 bytes
Fixed Size 1253072 bytes
Variable Size 155189552 bytes
Database buffers 910163968 bytes
Redo buffers 7135232 bytes
The database is loaded.
ORA-01157: Unable to identify/lock data file 1-see DBWR trace file
ORA-01110: Data file 1: ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01. DBF '

Sql> host copy E:\oraclebak\SYSTEM01. DBF D:\oracle\product\10.2.0\oradata\orcl\

sql> Recover Database
Complete media recovery.
sql> ALTER DATABASE open;

The database has changed.

Sql> Connect sys/[email protected] as Sysdba
is connected.
Sql> Conn lcam_atm_0106/[email protected]
is connected.

Sql> select * from Test2;

A
----------
1
2

Sql> select Name,status from V$controlfile;

NAME
--------------------------------------------------------------------------------------------------------------- ------------------------
------------- -------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01. Ctl
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02. Ctl
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03. Ctl

Sql> host del D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01. Ctl
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01. Ctl
Another program is using this file and the process cannot access it.

sql> shutdown
The database is closed.
The database has been uninstalled.
The ORACLE routine has been closed.
Sql> host del D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01. Ctl

Sql> Startup
The ORACLE routine has been started.

Total System Global area 1073741824 bytes
Fixed Size 1253072 bytes
Variable Size 167772464 bytes
Database buffers 897581056 bytes
Redo buffers 7135232 bytes
ORA-00205:?????????,??????,???????

Sql> host copy D:\oracle\product\10.2.0\oradata\orcl\CONTROL02. CTL D:\oracle\product\10.2.0\oradata\orcl\CONTROL01. Ctl
1 files have been copied.

Sql> ALTER DATABASE Mount;

The database has changed.

Sql> ALTER DATABASE Open
2;

The database has changed.

Sql>

Backup and Restore under Window environment