Oracle Database physical file backup/recovery

Oracle Database physical file backup/recovery

I have written a similar article before. Although it is not a problem to restore the physical files of the Oracle database, I always feel that something is wrong. I learned later that I had some misunderstandings in the past, it is much easier to use this article again.

Environment: CentOS release 5.6 (Final)
Oracle Database Release 11.2.0.1.0

1. decompress the compressed database file to the corresponding directory.
[Root @ OATest oabak] # tar-jxvf testoa_20140702.tar.bz2

2. Restore a file
[Root @ OATest temp] # su-oracle
[Oracle @ OATest/] $ export ORACLE_SID = testoa
[Oracle @ OATest/] $ sqlplus "/as sysdba ";

OPEN_MODE
--------------------
READ WRITE
3. Shut down the database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

4. in SQL mode, directly store the physical files of the database to the corresponding directory (the path of various files is displayed first, and then copied to the corresponding path)
-- Display Control Files

Select name from v $ controlfile;
-- Display data files
Select name from v $ datafile;
-- Display the continuity of the initialization parameter file (continuity: online modification of the initialization parameter can be directly applied to the parameter file)
Show parameter spfile;
-- Display log files
Select member from v $ logfile;
-- Orapw $ ORACLE_SID password file: the password file path is $ ORACLE_HOME/dbs/orapw $ ORACLE_SID // which has little to do with backup and can be generated at any time.

[Oracle @ OATest temp] $ sqlplus "/as sysdba ";

Host cp/usr/oabak/temp/control01.ctl/app/oracle/oradata/testoa/control01.ctl

Host cp/usr/oabak/temp/control02.ctl/app/oracle/flash_recovery_area/testoa/control02.ctl
Host cp/usr/oabak/temp/system01.dbf/app/oracle/oradata/testoa/system01.dbf
Host cp/usr/oabak/temp/sysaux01.dbf/app/oracle/oradata/testoa/sysaux01.dbf
Host cp/usr/oabak/temp/undotbs01.dbf/app/oracle/oradata/testoa/undotbs01.dbf
Host cp/usr/oabak/temp/users01.dbf/app/oracle/oradata/testoa/users01.dbf
Host cp/usr/oabak/temp/redo03.log/app/oracle/oradata/testoa/redo03.log
Host cp/usr/oabak/temp/redo02.log/app/oracle/oradata/testoa/redo02.log
Host cp/usr/oabak/temp/redo01.log/app/oracle/oradata/testoa/redo01.log
Host cp/usr/oabak/temp/spfile $ {ORACLE_SID}. ora $ ORACLE_HOME/dbs/
Host cp/usr/oabak/temp/orapw $ {ORACLE_SID} $ ORACLE_HOME/dbs/
-- To delete temporary files, the system automatically creates temp01.dbf upon restart.

If you do not delete a temporary file, the following error occurs:
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: '/app/oracle/oradata/testoa/temp01.dbf'
The file is not deleted when it is restored. The old temporary tablespace data file (300 MB) cannot be created to create a new temporary tablespace file (500 MB) .. therefore, an error is reported.

5. Start the database
SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2220200 bytes
Variable Size 2113933144 bytes
Database Buffers 2147483648 bytes
Redo Buffers 12144640 bytes
Database mounted.
Database opened.
SQL> exit

6. Change the User Password
SQL> alter user oaadmin identified by oaadmin;
User altered.

7. Unlock an account
SQL> alter user oaadmin account unlock;
User altered.

You can also use a command to directly process the above steps:
Select 'host cp/u03/backup/'| substr (name, (instr (name,'/',-1) + 1) |
''| Name from v $ controlfile
Union all
Select 'host cp/u03/backup/'| substr (name, (instr (name,'/',-1) + 1) |
''| Name from v $ controlfile
Union all
Select 'host cp/u03/backup/'| substr (member, (instr (member,'/',-1) + 1 ))
| ''| Member from v $ logfile
Union all
Select 'host cp/u03/backup/spfile $ {ORACLE_SID}. ora $ ORACLE_HOME/dbs /'
From dual
Union all
Select 'host cp/u03/backup/orapw $ {ORACLE_SID} $ ORACLE_HOME/dbs /'
From dual;