备份后新增表空间数据文件,然后删除所有的控制文件和新增的数据文件

完全备份后创建新的表空间之后没有备份,删除所有的控制文件,同时删除删除该表空间的文件,但是归档日志和在线重做日志的都是正常的

1.完全备份
#!/bin/sh
backup_date=`date +%Y-%m-%d`

rman target / log=/home/oracle/scripts/logs/rmanbackup_log_${backup_date}.log<<EOF
run
{
allocate channel ch1 device type disk;
backup as compressed backupset full filesperset 5 database format '/u01/rman_backup/db_fullbackup_%d_%s_%p_%T';
sql 'alter system archive log current';
backup as compressed backupset archivelog all delete input format '/u01/rman_backup/arch_%d_%s_%p_%T';
backup current controlfile format '/u01/rman_backup/ctl_%d_%s_%p_%T';
backup spfile format '/u01/rman_backup/spfile_%d_%s_%p_%T';
release channel ch1;
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
}
EOF

2.创建表空间
create tablespace tps_test04
datafile '/u01/app/oracle/oradata/slnngk/tps_test04.dbf'
size 100M autoextend on next 10m MAXSIZE unlimited;



3.创建用户和表并写入数据
Create User hxl Identified By oracle;

Grant Dba To hxl;

Create Table tb_test04(Id Number,Name Varchar2(32)) tablespace tps_test04;
Insert Into tb_test04 Values(1,'name1');
Insert Into tb_test04 Values(1,'name1');
Insert Into tb_test04 Values(1,'name1');
Insert Into tb_test04 Values(1,'name1');
Insert Into tb_test04 Values(1,'name1');
Insert Into tb_test04 Values(1,'name1');
Insert Into tb_test04 Values(1,'name1');
Insert Into tb_test04 Values(1,'name1');


4.删除所有的控制文件和刚才创建的表空间对应的文件
SQL> Select Name From v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/slnngk/control01.ctl
/u01/app/oracle/fast_recovery_area/slnngk/control02.ctl

rm /u01/app/oracle/oradata/slnngk/control01.ctl
rm /u01/app/oracle/fast_recovery_area/slnngk/control02.ctl
rm /u01/app/oracle/oradata/slnngk/tps_test04.dbf

5.关闭数据库启动到nomount
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/slnngk/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort
ORACLE instance shut down.


6.手工创建控制文件

为了方便可以采用 alter database backup controlfile to trace 生成trc文件获取到创建控制文件的脚本

这里采用NORESETLOGS部分

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SLNNGK" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/slnngk/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/slnngk/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/slnngk/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/slnngk/system01.dbf',
  '/u01/app/oracle/oradata/slnngk/sysaux01.dbf',
  '/u01/app/oracle/oradata/slnngk/undotbs01.dbf',
  '/u01/app/oracle/oradata/slnngk/users01.dbf',
  '/u01/app/oracle/oradata/slnngk/tps_goldengate01.dbf',
  '/u01/app/oracle/oradata/slnngk/tps_test01.dbf',
  '/u01/app/oracle/oradata/slnngk/tps_test0101.dbf',
  '/u01/app/oracle/oradata/slnngk/tps_test02.dbf',
  '/u01/app/oracle/oradata/slnngk/tps_test03.dbf'
CHARACTER SET ZHS16GBK
;

将上面的脚本保存到文件中,命名为:recreate_ctl.sql
SQL> connect / as sysdba
Connected to an idle instance.
SQL> @/home/oracle/scripts/recreate_ctl.sql

ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'/u01/app/oracle/oradata/slnngk/tps_test04.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

这里报错误因为我们删除了tps_test02.dbf文件,尝试修改recreate_ctl.sql文件,去掉该数据文件

重新执行
SQL> @/home/oracle/scripts/recreate_ctl.sql

Control file created.

控制文件创建后会在spfile参数control_files指定的路径下创建相应的控制文件,同时实例在mount状态

7.重新注册备份集和归档日志
rman> catalog start with '/u01/rman_backup/';

SQL模式(数据库处于mount状态)
alter database register physical logfile '/u01/app/oracle/archlog/1_1_1035815090.dbf';
alter database register physical logfile '/u01/app/oracle/archlog/1_2_1035815090.dbf';
alter database register physical logfile '/u01/app/oracle/archlog/1_3_1035815090.dbf';
alter database register physical logfile '/u01/app/oracle/archlog/1_4_1035815090.dbf';
alter database register physical logfile '/u01/app/oracle/archlog/1_5_1035815090.dbf';


8.进行数据库恢复

SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10:
'/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00010'
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10:
'/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00010'

手工创建

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00010' as '/u01/app/oracle/oradata/slnngk/tps_test04.dbf';

Database altered.

SQL> RECOVER DATABASE;
Media recovery complete.
SQL> alter database open;

Database altered.

9.临时表空间添加数据文件
alter tablespace temp add tempfile '/u01/app/oracle/oradata/slnngk/temp01.dbf' reuse;

 -- The End --