完全备份后创建新的表空间之后没有备份,删除所有的控制文件,同时删除删除该表空间的文件,但是归档日志和在线重做日志的都是正常的
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_test02
datafile '/u01/app/oracle/oradata/slnngk/tps_test02.dbf'
size 100M autoextend on next 10m MAXSIZE unlimited;
3.创建用户和表并写入数据
Create User hxl Identified By oracle;
Grant Dba To hxl;
Create Table tb_test02(Id Number,Name Varchar2(32)) tablespace tps_test02;
Insert Into tb_test01 Values(1,'name1');
Insert Into tb_test01 Values(1,'name1');
Insert Into tb_test01 Values(1,'name1');
Insert Into tb_test01 Values(1,'name1');
Insert Into tb_test01 Values(1,'name1');
Insert Into tb_test01 Values(1,'name1');
Insert Into tb_test01 Values(1,'name1');
Insert Into tb_test01 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_test02.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.
SQL> shutdown abort
ORACLE instance shut down.
6.手工创建控制文件
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SLNNGK" RESETLOGS 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
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'
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_test02.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.进行数据库恢复
RMAN> restore database;
RMAN> recover database;
archived log thread=1 sequence=1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/23/2020 15:53:19
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1 and starting SCN of 3366372
直接打开数据库
RMAN> alter database open resetlogs
9.发现后面创建的tps_test02.dbf没有创建
Media Recovery Log /u01/app/oracle/archlog/1_5_1035815090.dbf
File #8 added to control file as 'UNNAMED00008'. Originally created as:'/u01/app/oracle/oradata/slnngk/tps_test02.dbf'
10.重新命名8号文件
从视图Select * From v$datafile中获取unname文件名,该文件物理上实际是不存在的
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
alter database rename file '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00008' to '/u01/app/oracle/oradata/slnngk/tps_test02.dbf';
alter database datafile 8 online;
好像恢复不了
SQL> alter database create datafile '/u01/app/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00008' as '/u01/app/oracle/oradata/slnngk/tps_test02.dbf';
recover datafile 8;
10.临时表空间添加数据文件
alter tablespace temp add tempfile '/u01/app/oracle/oradata/slnngk/temp01.dbf' reuse;
-- The End --