oracle 双机冷备 脚本,Oracle冷备份脚本

一、原理

冷备份

数据库在关闭状态下完成所有物理系统文件拷贝的过程，也称脱机备份

适合于非归档模式下，数据库处于一致性状态

步骤

首先在运行的库中得到数据库运行的所有的物理文件位置，然后在计划内关闭数据库(shutdown)

再执行拷贝物理文家到备份路径或备份设备

备份完成后立即启动数据库让其提供正常的服务

总结 - 优点

冷备模式下概念易于理解，即将需要备份的文件复制到安全的位置

操作比较简单，不需要太多的干预

容易恢复到某个时间点上(只需将文件再拷贝回去)

能与归档方法相结合，作数据库“最新状态”的恢复。

4.总结 - 缺点

备份时，数据库必须处于一致性关闭状态

只能提供到某一时间点的恢复

备份时速度比较慢，尤其是数据量大性能影响比较大

不能实现基于表和用户级别的数据恢复

二、案例

首先应该在相关视图里查出数据库的数据文件，日志文件，控制文件，临时文件所在的位置。注意：不要直接把oradata下的cp就行了，因为生产库里各个文件通常分布在不同的磁盘，不同的地方，所以在去视图里获得真实路径 。

1).查看数据库是否为CDB

SYS@newtest>select name, decode(cdb, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option" , open_mode, con_id from v$database;

NAME Multitenant Option OPEN_MODE CON_ID

NEWTEST Multitenant Option enabled READ WRITE 0

YES表示该数据库是CDB,如果是NO表示是NO-CDB(普通数据库)

2).查看实例和数据库的相关信息

SYS@newtest>select instance_name,version,status,archiver,database_status from v$instance;

INSTANCE_NAME VERSION STATUS ARCHIVE DATABASE_STATUS

newtest 12.2.0.1.0 OPEN STARTED ACTIVE

SYS@newtest>select dbid,name,log_mode from v$database;

DBID NAME LOG_MODE

1779551310 NEWTEST ARCHIVELOG

SYS@newtest>set linesize 180

SYS@newtest>col guid format A40

SYS@newtest>col name format A20

SYS@newtest>select con_id, dbid, guid, name , open_mode from v$pdbs;

CON_ID DBID GUID NAME OPEN_MODE

2 603865257 32CAC9A05E614355926257BE671D9AFC PDB$SEED READ ONLY

3 3394996704 24549D14BBEB417BAB312BF19BC5259E PDBTEST READ WRITE

4 50957894 D4B2FCAAC2B944CC8AF5864EC7CB853C CLONEPDB_PLUG READ WRITE

3). 查看数据文件及状态信息

SYS@newtest>col file_name format A60

SYS@newtest>select file_name,tablespace_name,status,online_status from dba_data_files;

FILE_NAME TABLESPACENAME STATUS ONLINE

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\SYSTEM01.DBF SYSTEM AVAILABLE SYSTEM

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\SYSAUX01.DBF SYSAUX AVAILABLE ONLINE

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\UNDOTBS01.DBF UNDOTBS1 AVAILABLE ONLINE

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\USERS01.DBF USERS AVAILABLE ONLINE

SYS@newtest>col con_id format 99

SYS@newtest>col name format A80

SYS@newtest>select con_id,FILE#,NAME,status,enabled from v$datafile order by 1;

CON_ID FILE# NAME STATUS ENABLED

1 1 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\SYSTEM01.DBF SYSTEM READ WRITE

1 3 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\SYSAUX01.DBF ONLINE READ WRITE

1 5 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\UNDOTBS01.DBF ONLINE READ WRITE

1 7 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\USERS01.DBF ONLINE READ WRITE

2 2 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBSEED\SYSTEM01.DBF SYSTEM READ WRITE

2 4 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBSEED\SYSAUX01.DBF ONLINE READ WRITE

2 6 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBSEED\UNDOTBS01.DBF ONLINE READ WRITE

3 8 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\SYSTEM01.DBF SYSTEM READ WRITE

3 11 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\USERS01.DBF ONLINE READ WRITE

3 10 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\UNDOTBS01.DBF ONLINE READ WRITE

3 9 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\SYSAUX01.DBF ONLINE READ WRITE

CON_ID FILE# NAME STATUS ENABLED

4 42 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\UNDOTBS01.DBF ONLINE READ WRITE

4 41 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\SYSAUX01.DBF ONLINE READ WRITE

4 40 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\SYSTEM01.DBF SYSTEM READ WRITE

4 43 C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\USERS01.DBF ONLINE READ WRITE

15 rows selected.

4).查看临时文件

SYS@newtest>col name format A100

SYS@newtest>select name from v$tempfile;

NAME

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\TEMP01.DBF

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBSEED\TEMP012017-12-13_17-23-31-944-PM.DBF

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\TEMP012017-12-13_17-23-31-944-PM.DBF

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\TEMP012017-12-13_17-23-31-944-PM.DBF

5).查看日志文件

SYS@newtest>col member format A60

SYS@newtest>select member from v$logfile;

MEMBER

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\REDO03.LOG

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\REDO02.LOG

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\REDO01.LOG

6).查看控制文件

SYS@newtest>select name from v$controlfile;

NAME

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CONTROL01.CTL

C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CONTROL02.CTL

7).创建备份目录

SYS@newtest>ho mkdir c:\oracleback

SYS@newtest>select 'ho mkdir '|| ' &dir\' ||NAME from v$pdbs;

Enter value for dir: C:\oracleback

old 1: select 'ho mkdir '|| ' &dir\' ||NAME from v$pdbs

new 1: select 'ho mkdir '|| ' C:\oracleback\' ||NAME from v$pdbs

'HOMKDIR'||'C:\ORACLEBACK\'||NAME

ho mkdir C:\oracleback\PDB$SEED

ho mkdir C:\oracleback\PDBTEST

ho mkdir C:\oracleback\CLONEPDB_PLUG

8).使用连接符生成复制文件命令

SYS@newtest>select 'ho copy ' || name || ' &dir' from v$controlfile

2 union all

3 select 'ho copy ' || member || ' &dir' from v$logfile

4 union all

5 select 'ho copy ' || file_name|| ' &dir' from dba_data_files

6 union all

7 select 'ho copy ' || vd.name || ' &dir\'|| vp.name as command from v$datafile vd join v$pdbs vp using(con_id)

8 union all

9 select 'ho copy ' || vt.name || ' &dir\'|| vp.name as command from v$tempfile vt join v$pdbs vp using(con_id)

10 /

Enter value for dir: C:\oracleback

old 1: select 'ho copy ' || name || ' &dir' from v$controlfile

new 1: select 'ho copy ' || name || ' C:\oracleback' from v$controlfile

Enter value for dir: C:\oracleback

old 3: select 'ho copy ' || member || ' &dir' from v$logfile

new 3: select 'ho copy ' || member || ' C:\oracleback' from v$logfile

Enter value for dir: C:\oracleback

old 5: select 'ho copy ' || file_name|| ' &dir' from dba_data_files

new 5: select 'ho copy ' || file_name|| ' C:\oracleback' from dba_data_files

Enter value for dir: C:\oracleback

old 7: select 'ho copy ' || vd.name || ' &dir\'|| vp.name as command from v$datafile vd join v$pdbs vp using(con_id)

new 7: select 'ho copy ' || vd.name || ' C:\oracleback\'|| vp.name as command from v$datafile vd join v$pdbs vp using(con_id)

Enter value for dir: C:\oracleback

old 9: select 'ho copy ' || vt.name || ' &dir\'|| vp.name as command from v$tempfile vt join v$pdbs vp using(con_id)

new 9: select 'ho copy ' || vt.name || ' C:\oracleback\'|| vp.name as command from v$tempfile vt join v$pdbs vp using(con_id)

'HOCOPY'||NAME||'C:\ORACLEBACK'

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CONTROL01.CTL C:\oracleback

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CONTROL02.CTL C:\oracleback

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\REDO03.LOG C:\oracleback

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\REDO02.LOG C:\oracleback

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\REDO01.LOG C:\oracleback

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\SYSTEM01.DBF C:\oracleback

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\SYSAUX01.DBF C:\oracleback

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\UNDOTBS01.DBF C:\oracleback

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\USERS01.DBF C:\oracleback

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBSEED\SYSTEM01.DBF C:\oracleback\PDB$SEED

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBSEED\SYSAUX01.DBF C:\oracleback\PDB$SEED

'HOCOPY'||NAME||'C:\ORACLEBACK'

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBSEED\UNDOTBS01.DBF C:\oracleback\PDB$SEED

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\SYSTEM01.DBF C:\oracleback\PDBTEST

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\SYSAUX01.DBF C:\oracleback\PDBTEST

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\UNDOTBS01.DBF C:\oracleback\PDBTEST

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\USERS01.DBF C:\oracleback\PDBTEST

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\SYSTEM01.DBF C:\oracleback\CLONEPDB_PLUG

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\SYSAUX01.DBF C:\oracleback\CLONEPDB_PLUG

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\UNDOTBS01.DBF C:\oracleback\CLONEPDB_PLUG

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\USERS01.DBF C:\oracleback\CLONEPDB_PLUG

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBSEED\TEMP012017-12-13_17-23-31-944-PM.DBF C:\oracleback\PDB$SEED

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\PDBTEST\TEMP012017-12-13_17-23-31-944-PM.DBF C:\oracleback\PDBTEST

'HOCOPY'||NAME||'C:\ORACLEBACK'

ho copy C:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\NEWTEST\CLONEPDB_PLUG\TEMP012017-12-13_17-23-31-944-PM.DBF C:\oracleback\CLONEPDB_PLUG

23 rows selected.

9).编写相关脚本