分类目录归档：Oracle备份恢复

oracle dul 12.2正式版发布

发表于 2021 年 3 月 21 日由惜分飞

oracle官方dul 正式发布 12.2版本(在上次的测试中dul 12.2完美支持Oracle 19c恢复还是beta版本)

[root@iZbp1hx0enix3hix1kvyrxZ tmp]# ./dul      

Data UnLoader: 12.2.0.0.1 - Internal Only - on Sun Mar 21 13:55:39 2021
with 64-bit io functions and the decompression option

Copyright (c) 1994 2021 Bernard van Duijnen All rights reserved.

 Strictly Oracle Internal Use Only
DUL> show parameters;
_SLPE_DEBUG               = FALSE
ALLOW_CHECKSUM_MISMATCH   = FALSE
ALLOW_DBA_MISMATCH        = FALSE
ALLOW_OTHER_OBJNO         = FALSE
ALLOW_TRAILER_MISMATCH    = FALSE
ALLOW_ZERO_IN_DATE_COLUMNS = FALSE
ASM_DO_HARD_CHECKS        = TRUE
AUTO_UPDATE_CHECKSUM      = TRUE
AUTO_UPDATE_TRAILER       = TRUE
BUFFER                    = 104857600
CF_FILES                  = 1022
CF_TABLESPACES            = 64
COMPATIBLE                = 11
CONTROL_FILE              = control.txt
DB_BLOCK_SIZE             = 8192
DB_NAME                   = 
DB_ID                     = 0
DC_COLUMNS                = 2000000
DC_LOB_ENTRIES            = 327680
DC_EXTENTS                = 10000
DC_OBJECTS                = 1000000
DC_SEGMENTS               = 100000
DC_TABLES                 = 10000
DC_USERS                  = 400
DEFAULT_CHARACTER_SET     = 
DEFAULT_NATIONAL_CHARACTER_SET = 
EXPORT_MODE               = true
FEEDBACK                  = 10000
FILE                      = 
FILE_SIZE_IN_MB           = 0
LDR_ENCLOSE_CHAR          = |
LDR_OUTPUT_IN_UTF8        = FALSE
LDR_PHYS_REC_SIZE         = 0
LOGFILE                   = dul.log
MAX_OPEN_FILES            = 8
MAX_SCAN_ROWS             = 0
MAX_SAMPLE_ROWS           = 5
OSD_MAX_THREADS           = 1055
OSD_BIG_ENDIAN_FLAG       = false
OSD_DBA_FILE_BITS         = 10
OSD_FILE_LEADER_SIZE      = 0
OSD_C_STRUCT_ALIGNMENT    = 32
OSD_WORD_SIZE             = 32
PARSE_HEX_ESCAPES         = FALSE
RESET_LOGFILE             = FALSE
SCAN_DATABASE_SCANS_LOB_SEGMENTS = TRUE
SCAN_STEP_SIZE            = 512
TRACE_FLAGS               = 0
UNEXP_MAX_ERRORS          = 1000
UNEXP_VERBOSE             = FALSE
USE_LOB_FILES             = FALSE
USE_SCANNED_EXTENT_MAP    = FALSE
VERIFY_NUMBER_PRECISION   = TRUE
WARN_RECREATE_FILES       = TRUE
WRITABLE_DATAFILES        = FALSE
DUL> exit

Life is DUL without it
[root@iZbp1hx0enix3hix1kvyrxZ tmp]#

发表在 Oracle备份恢复 | 标签为 dul 12.2, dul正式版, oracle dul | 评论关闭

Oracle Recovery Tools恢复—ORA-00704 ORA-01555故障

发表于 2021 年 3 月 14 日由惜分飞

由于虚拟化环境使用了精简模式(预分配),后面出现分布式存储空间不足,导致虚拟化环境中的数据库服务器异常,通过一系列操作恢复好系统,发现数据库无法open,请求我们给予解决
通过我们的Oracle Database Recovery Check脚本分析,分析文件的checkpoint scn 有部分3月2日,还有一些是2月28日,是严重不一致,而且对应的归档也丢失

基于这样的情况,试试看强制打开库

C:\Users\XIFENFEI>sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期四 3月 11 23:51:39 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup mount pfile='d:/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area 1603411968 bytes
Fixed Size                  2281656 bytes
Variable Size             469765960 bytes
Database Buffers         1124073472 bytes
Redo Buffers                7290880 bytes
数据库装载完毕。
SQL> recover database until cancel;
ORA-00279: 更改 57834775 (在 02/28/2021 22:37:35 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\XIFENFEI\PRODUCT\11.2.0.4\DBHOME_1\RDBMS\ARC0000003072_1043082043.0001
ORA-00280: 更改 57834775 (用于线程 1) 在序列 #3072 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: 警告: RECOVER 成功但 OPEN RESETLOGS 将出现如下错误
ORA-01194: 文件 1 需要更多的恢复来保持一致性
ORA-01110: 数据文件 1: 'D:\BAIDUNETDISKDOWNLOAD\DATA\PROD\SYSTEM01.DBF'


ORA-01112: 未启动介质恢复


SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 10 with name
"_SYSSMU10_1197734989$" too small
进程 ID: 7928
会话 ID: 96 序列号: 3

在数据库open的过程中,报ORA-01555错误,这类问题比较明显以前写过类似文章:
在数据库open过程中常遇到ORA-01555汇总
 数据库open过程遭遇ORA-1555对应sql语句补充
 使用_allow_resetlogs_corruption导致ORA-00704/ORA-01555故障
这次尝试使用自己开发的小程序:Oracle Recovery Tools进行恢复

然后直接尝试打开数据库成功

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01113: ?? 1 ??????
ORA-01110: ???? 1: 'D:\BAIDUNETDISKDOWNLOAD\DATA\XFF\SYSTEM01.DBF'


SQL> recover database;
完成介质恢复。
SQL> alter database open;

数据库已更改。

这次证明,对于数据库open过程汇总报ORA-00704 ORA-01555故障,可以通过Oracle Recovery Tools工具一键式open库。
后续安排数据导出,对于个别导出报错的表利用dul进行处理,完成本次恢复任务
软件下载:OraRecovery下载
使用说明:使用说明

发表在 Oracle备份恢复, 小工具 | 标签为 asm ora-, open ora-01555, ORA-00704 ORA-00604 ORA-01555, ORA-00704 ORA-01555, Oracle Recovery Tools, vm预分配空间数据库异常 | 评论关闭

使用rman from service 搭建dataguard

发表于 2021 年 3 月 13 日由惜分飞

从oracle 12c开始提供了rman通过from service方式搭建dg,使用12c长期支持版19c(并打上最新的patch)
配置dataguard相关参数(主备库有稍微不同)

alter system set db_unique_name='XIFENFEI' scope=spfile;
alter system set service_names='XIFENFEI';
alter system set log_archive_config='dg_config=(XIFENFEI,XIFENFEIDG)';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST 
  valid_for=(all_logfiles,all_roles) db_unique_name=XIFENFEI';
alter system set log_archive_dest_2='service=XIFENFEIDG lgwr async 
 valid_for=(online_logfiles,primary_role) db_unique_name=XIFENFEIDG';
alter system set standby_file_management=auto;
alter system set db_file_name_convert='/u01/app/oracle/oradata/XIFENFEI/',
 '/u01/app/oracle/oradata/XIFENFEI/' scope=spfile;
alter system set log_file_name_convert='/u01/app/oracle/oradata/XIFENFEI/',
 '/u01/app/oracle/oradata/XIFENFEI/' scope=spfile;
alter system set fal_server=XIFENFEIDG;

配置tnsnames.ora

XIFENFEI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.238)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = XIFENFEI)
    )
  )

XIFENFEIDG =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.124)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = XIFENFEI)
    )
  )

拷贝主库密码文件到备库

[oracle@primary ~]$ scp $ORACLE_HOME/dbs/orapwXIFENFEI 192.168.0.124:$ORACLE_HOME/dbs/
The authenticity of host '192.168.0.124 (192.168.0.124)' can't be established.
ECDSA key fingerprint is SHA256:NI2952z4Bqc3M/B+AK7EJRiJNauROIyluvu1l4NSTX0.
ECDSA key fingerprint is MD5:1d:64:dd:ef:1c:ad:ed:cf:70:22:2d:4d:7c:90:5e:5e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.124' (ECDSA) to the list of known hosts.
oracle@192.168.0.124's password: 
orapwXIFENFEI                                                                   100% 2048     6.6MB/s   00:00    
[oracle@primary ~]$

备库启动到nomount状态

[oracle@standby ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 13 20:32:34 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile='/tmp/pfile';

File created.

SQL> startup nomount pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 4294963264 bytes
Fixed Size                  8904768 bytes
Variable Size             805306368 bytes
Database Buffers         3472883712 bytes
Redo Buffers                7868416 bytes
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

rman from service方式创建standby ctl和还原数据文件

[oracle@standby ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 13 20:34:37 2021
Version 19.10.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: XIFENFEI (not mounted)

RMAN> restore standby controlfile from service XIFENFEI;

Starting restore at 13-MAR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XIFENFEI
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/XIFENFEI/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/XIFENFEI/control02.ctl
Finished restore at 13-MAR-21

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> restore database from service XIFENFEI;

Starting restore at 13-MAR-21
Starting implicit crosscheck backup at 13-MAR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
Finished implicit crosscheck backup at 13-MAR-21

Starting implicit crosscheck copy at 13-MAR-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 13-MAR-21

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XIFENFEI
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/XIFENFEI/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XIFENFEI
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/XIFENFEI/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XIFENFEI
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/XIFENFEI/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service XIFENFEI
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/XIFENFEI/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 13-MAR-21

备库启动mrp进程

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

2021-03-13T20:54:08.075418+08:00
Attempt to start background Managed Standby Recovery process (XIFENFEI)
Starting background process MRP0
2021-03-13T20:54:08.086269+08:00
MRP0 started with pid=56, OS id=8182 
2021-03-13T20:54:08.087276+08:00
Background Managed Standby Recovery process started (XIFENFEI)
2021-03-13T20:54:13.104757+08:00
 Started logmerger process
2021-03-13T20:54:13.112058+08:00

IM on ADG: Start of Empty Journal 

IM on ADG: End of Empty Journal 
PR00 (PID:8188): Managed Standby Recovery starting Real Time Apply
2021-03-13T20:54:13.205668+08:00
Parallel Media Recovery started with 4 slaves
2021-03-13T20:54:13.216576+08:00
Stopping change tracking
PR00 (PID:8188): Media Recovery Waiting for T-1.S-25 (in transit)
2021-03-13T20:54:13.269138+08:00
Recovery of Online Redo Log: Thread 1 Group 12 Seq 25 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/XIFENFEI/s_redo12.log

至此dataguard基本上搭建完成

发表在 Data Guard, rman备份/恢复 | 标签为 rman dataguard, rman from service | 评论关闭

17813235971

QQ 咨询

分类目录归档：Oracle备份恢复

oracle dul 12.2正式版发布

Oracle Recovery Tools恢复—ORA-00704 ORA-01555故障

使用rman from service 搭建dataguard

文章分类

最近发表

分类目录归档：Oracle备份恢复

oracle dul 12.2正式版发布

Oracle Recovery Tools恢复—ORA-00704 ORA-01555故障

使用rman from service 搭建dataguard

标签云

文章分类

最近发表