oracle-从带库异地恢复数据库

bestinter2010

已于 2022-09-15 14:04:02 修改

阅读量434

点赞数

分类专栏： oracle 文章标签：数据库 oracle java

于 2022-07-29 10:35:00 首次发布

本文链接：https://blog.csdn.net/bestinter2010/article/details/126048622

版权

oracle 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

1.第一步先自己手动创建一个pfile文件，用这个pfile文件将数据启动至nomount状态

以下这些可以根据实际情况调整

*.db_name='orcl'
*.db_unique_name='orcl'
*.cluster_database=false
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/orcl/controlfile01.ctl'
*.db_block_size=8192
*.db_files=10240
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/data/orcl'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=2681208832
*.processes=1500
*.sessions=1655
*.sga_target=8043626496
*.undo_tablespace='UNDOTBS1'
*.log_file_name_convert='+orclDATA/orcl/onlinelog','/data/orcl'

2.恢复控制文件

run {
 allocate channel ch00 type 'SBT_TAPE';
 SEND
 'NB_ORA_CLIENT=client_name, NB_ORA_POLICY=nb_policy_name, NB_ORA_SERV=nbu_ser_name;
restore controlfile;
release channel ch00;
 }

3.确认带库上面备份的最新的归档日志的scn

注意如果是rac节点，就找到比较每个节点最后一个归档日志scn最小的那一个

数据库能恢复到的scn也就是这个

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
31549377 5.03G      SBT_TAPE    00:01:55     29-JUL-22      
        BP Key: 31549379   Status: AVAILABLE  Compressed: NO  Tag: TAG20220729T070138
        Handle: al_10699_1_1111302102   Media: @aaaak

  List of Archived Logs in backup set 31549377
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    130868  9555754550719 29-JUL-22 9555784212673 29-JUL-22
  1    130869  9555784212673 29-JUL-22 9555813703027 29-JUL-22
  1    130870  9555813703027 29-JUL-22 9555843196172 29-JUL-22
  1    130871  9555843196172 29-JUL-22 9555858754772 29-JUL-22
  1    130872  9555858754772 29-JUL-22 9555875427235 29-JUL-22
  1    130873  9555875427235 29-JUL-22 9555875526079 29-JUL-22
  1    130874  9555875526079 29-JUL-22 9555875527393 29-JUL-22
  2    59610   9555754552458 29-JUL-22 9555813722729 29-JUL-22
  2    59611   9555813722729 29-JUL-22 9555858765356 29-JUL-22
  2    59612   9555858765356 29-JUL-22 9555858811933 29-JUL-22
  2    59613   9555858811933 29-JUL-22 9555875427417 29-JUL-22
  2    59614   9555875427417 29-JUL-22 9555875527417 29-JUL-22

4.Restore database&Recover database

根据步骤三得到scn号recover

RUN {
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
SEND
 ' NB_ORA_CLIENT=client_name, NB_ORA_POLICY=nb_policy_name, NB_ORA_SERV=nbu_ser_name;'
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
SEND
 ' NB_ORA_CLIENT=client_name, NB_ORA_POLICY=nb_policy_name, NB_ORA_SERV=nbu_ser_name;';
alter database mount;
set newname for database to '/data/SID/%b';
restore database;
switch datafile all;
recover database until  scn  xxxxxxxxxxx(需要恢复到特定时间点的scn号);
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
};

5.resetlogs打开数据库

alter database open resetlogs

打开过程中可能会遇到的问题

select * from v$logfile;

如果logfile路径还是asm路径，用

alter database rename file '+orclDATA/orcl/onlinelog/redo01.log' to '/data/orcl/redo01.log'

修改成正确路径

如果此方法无法修改成功，将控制文件备份成trace文件：

alter database backup controlfile to trace as '/home/oracle/con.ctl'

编辑trace文件，将正确路径修改，然后用以下sql重建控制文件

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 9348
LOGFILE
  GROUP 1 '/data/orcl/group_1.262.1078996569'  SIZE 500M BLOCKSIZE 512,
  GROUP 6 '/data/orcl/group_6.265.1078996569'  SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/data/orcl/system.257.1078996463',
  '/data/orcl/ees_config_data_ts_1.dbf',
  '/data/orcl/sysaux.258.1078996499',
  '/data/orcl/undotbs1.259.1078996513',
  '/data/orcl/undotbs2.267.1078996951',
  '/data/orcl/users.260.1078996515',
  '/data/orcl/data01.dbf'
CHARACTER SET AL32UTF8
;

7.如果rac迁移至单节点，用以下方式删除node2的redo

SQL> alter database disable thread 2;

SQL> alter database drop logfile group 3; （如果状态是inactive的，直接删除日志组就行）

SQL> alter database clear unarchived logfile group 4; （如果状态是unused的，先设置日志组不能归档，再删除）

SQL> alter database drop logfile group 4;

如果遇到ORA-00392，用alter database clear logfile group 5;

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 5 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 5 thread 1: '/data/orcl/redo05.log'

SQL> alter database clear logfile group 5;

Database altered.

SQL> alter database open resetlogs;

Database altered.