Linux冷备份shell脚本及备份对象 Oracle 063 2021-01-23

建议生产环境用standby,rac及异步日志还原架构，

以及在归档模式，有RMAN备份后，

可用DRA的list /advice/repair failure的建议去，手工还原恢复数据。

 

本方法只是有利于熟悉各备份工具备份对象及测试

物理冷备份（服务需关停）

适合冷备原则：同样的版本环境，同样路径，同样数据库名，同样表空间等等最适合了

先看通过参数看DB文件路径，做记录后再进行

1、shutdown immediate停服务

2、备份：依据数据库备忘录表进行负责文件备份

3、还原：用已备份的文件覆盖掉原本目录下同名文件

4、startup 启动服务

冷备--冷备脚本内容

 

自动执行脚本

vi bakee.sh

#!/bin/bash

sqlplus / as sysdba <<EOF

@/home/oracle/bakup/bakee.sql

EOF

后期可以sh bakee.sh 利用crontab -e 去周期性夜间执行备份

 

手动执行冷备份脚本

[oracle@663721868ee0 EE]$ bin/sqlplus / as sysdba;

SQL> @/home/oracle/bakup/bakee.sql

SQL>quit

 

备份代码文件脚本

主要思路：在mount或者open下才能正常selectselect 视图获取数据库各类需备份文件，之后得shutdown 再 cp；

 

[oracle@663721868ee0 EE]$ vi bakee.sql

spool /home/oracle/backup/bak.sql

define bakdir = '/home/oracle/backup'

define bakpwd = '${ORACLE_HOME}/dbs/orapw${ORACLE_SID}'



select 'ho cp '||name||' &bakdir' from v$controlfile

union all

select 'ho cp '||name||' &bakdir' from v$datafile

union all

select 'ho cp '||name||' &bakdir' from v$tempfile

union all

select 'ho cp '||member||' &bakdir' from v$logfile

union all

select 'ho cp '||name||' &bakdir' from v$archived_log where deleted='NO';

union all

select 'ho cp '||value||' &bakdir' from v$parameter where name='spfile';

/

create pfile='&bakdir/init${ORACLE_SID}.ora'  from spfile;

ho cp  &bakpwd &bakdir

spool off



shutdown immediate

ho sed -n '/^ho cp/p' &bakdir/bak.sql >&bakdir/bakstart.sql

start &bakdir/bakstart.sql

startup

 

检验数据脚本文件

表空间都统一用dbf结尾

dbv_all.sh

#!/bin/bash

DATADIR=$1

cd $DATADIR

ls *.dbf | while read FILE

do

        dbv file=$FILE

done

执行新旧检验路径

./dbv_all.sh /home/oracle/bakup/

 ./dbv_all.sh /home/oracle/backup2

 

 

结论：因为冷备份所有的文件controlfile,datafile,redo logfile全部restore后，oracle因为他们一致性，无须recover。

冷备--恢复到原本位置说明

     只要shutdown immediate，覆盖文件，startup即可。

冷备说明：因为覆盖各scn不变，无需

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-00264: no recovery required

 

 

若需要冷备--恢复到新位置，可以作为异地恢复还原办法。

1、新控制文件参考内容的生成

alter database backup controlfile to trace as '/home/oracle/backup2/newcontrol.sql';

2、将newcontrol.sql其中的NORESETLOGS块如下段复制成独立文件如noresetcontrol.sql

noresetlogs用如下方法段

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "EE" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/home/oracle/bakup/redo01.log'  SIZE 200M BLOCKSIZE 512,

  GROUP 2 '/home/oracle/bakup/redo02.log'  SIZE 200M BLOCKSIZE 512,

  GROUP 3 '/home/oracle/bakup/redo03.log'  SIZE 200M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/home/oracle/bakup/system01.dbf',

  '/home/oracle/bakup/sysaux01.dbf',

  '/home/oracle/bakup/undotbs01.dbf',

  '/home/oracle/bakup/benet.ora',

  '/home/oracle/bakup/users01.dbf'

CHARACTER SET AL32UTF8;

3、参数中各文件的路径修改，及内容修改（1\redo日志 2\数据文件）

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "EE" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/home/oracle/backup2/redo01.log'  SIZE 200M BLOCKSIZE 512,

  GROUP 2 '/home/oracle/backup2/redo02.log'  SIZE 200M BLOCKSIZE 512,

  GROUP 3 '/home/oracle/backup2/redo03.log'  SIZE 200M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/home/oracle/backup2/system01.dbf',

  '/home/oracle/backup2/sysaux01.dbf',

  '/home/oracle/backup2/undotbs01.dbf',

  '/home/oracle/backup2/benet.dbf',

  '/home/oracle/backup2/users01.dbf'

CHARACTER SET AL32UTF8;

 

将控制文件修改为新地址用起来，nomount下。

SQL> shutdown immediate;

SQL> startup nomount;

以下是备份后的

SQL> alter system set control_files='/home/oracle/backup2/control01.ctl','/home/oracle/backup2/control02.ctl' scope=spfile;

SQL> shutdown immediate后

SQL>@/home/oracle/backup/controlnoreset.sql

注意新控制参考文件并没有temp库，

SQL> select * from v$tempfile;

no rows selected

SQL> create temporary tablespace temp01 tempfile '/home/oracle/backup2/temp01.dbf' size 2048M;

Tablespace created.

SQL> select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

/home/oracle/backup2/temp01.dbf

SQL>alter database default temporary tablespace temp01;

即可。

            若默认temp文件没有会导致expdp不能导出，这都是小事，可以如上修正。

 

 

4、将参数各文件路径进行修改

将表空间文件及日志等文件修改为新路径用起来，mount下。

SQL> shutdown immediate;

SQL> startup mount;

[oracle@663721868ee0 EE]$ vi /home/oracle/backup2/0902rename.sql

alter database rename file '/home/oracle/bakup/system01.dbf' to '/home/oracle/backup2/system01.dbf';

alter database rename file '/home/oracle/bakup/undotbs01.dbf' to '/home/oracle/backup2/undotbs01.dbf';

alter database rename file '/home/oracle/bakup/sysaux01.dbf' to '/home/oracle/backup2/sysaux01.dbf';

alter database rename file '/home/oracle/bakup/users01.dbf' to '/home/oracle/backup2/users01.dbf';

alter database rename file '/home/oracle/bakup/benet.ora' to '/home/oracle/backup2/benet.dbf';

alter database rename file '/home/oracle/bakup/temp01.dbf' to '/home/oracle/backup2/temp01.dbf';

alter database rename file '/home/oracle/bakup/redo01.log' to '/home/oracle/backup2/redo01.log';

alter database rename file '/home/oracle/bakup/redo02.log' to '/home/oracle/backup2/redo02.log';

alter database rename file '/home/oracle/bakup/redo03.log' to '/home/oracle/backup2/redo03.log';

 SQL> @/home/oracle/backup2/0902rename.sql;

 

5、shutdown immediate;    

SQL> startup;

ORACLE instance started.

 

Total System Global Area 3221225472 bytes

Fixed Size                  8625856 bytes

Variable Size             771752256 bytes

Database Buffers         2432696320 bytes

Redo Buffers                8151040 bytes

Database mounted.

Database opened.

 

至此Oracle冷备份不同目录还原已成功完成

 

 

非归档下也可以用如下备份

[oracle@host01 backup]$ cat rmannoarchive.sh 
#!/bin/bash
rman target / <<EOF

shutdown immediate;
startup mount;
backup database;
alter database open;
exit;
EOF