Oracle DG备库发现Oracle Home目录使用空间巨增的问题处理

同事巡检查发现Oracle RAC DG备库的Oracle Home目录使用空间巨增,执行命令发现oracle目录占用了87G,这肯定不正常。

[root@dgdb1 app]# du -sh *
11G     11.2.0
8.0K    asmpfile.ora
3.1G    grid
87G     oracle
4.0M    oraInventory

进一步检查发现ORACLE_HOME目录下的dbs目录占用了61G。

[root@dgdb1 db_1]# du -sh *
177M    apex
303M    assistants
459M    bin
58M     ccr
8.0K    cdata
2.9M    cfgtoollogs
68K     clone
4.0K    config
5.9M    crs
24K     csmig
236K    css
296M    ctx
3.3M    cv
61G     dbs
12K     dc_ocm
396K    deinstall
1.1M    demo
16K     diagnostics
6.9M    dv
40K     emcli
8.0K    EMStage
1.9M    has
40K     hs
11M     ide
7.4M    install
1.5M    instantclient
215M    inventory
60M     j2ee
118M    javavm
26M     jdbc
520K    jdev
186M    jdk
43M     jlib
2.7M    ldap
768M    lib
64K     log
33M     md
96K     mesg
908K    mgw
8.3M    network
33M     nls
326M    oc4j
1.7M    odbc
13M     olap
5.5M    OPatch
528K    opmn
40M     oracore
4.0K    oraInst.loc
7.4M    ord
29M     oui
545M    owb
1.7M    owm
60M     perl
1.6M    plsql
5.1M    precomp
316K    racg
127M    rdbms
124K    relnotes
4.0K    root.sh
12K     scheduler
20K     slax
82M     sqldeveloper
4.8M    sqlj
484K    sqlplus
9.6M    srvm
21M     suptools
220M    sysman
112K    timingframework
512K    ucp
3.7M    uix
800K    usm
8.0K    utl
556K    wwg
19M     xdk

检查dbs目录发现多了很多broken*这样的文件,并且在是三月2号8:36分到9:49分之间生成的

[root@dgdb1 dbs]# ls -lrt
total 63341528
-rw-r--r-- 1 oracle oinstall       2851 May 15  2009 init.ora
-rw-rw---- 1 oracle asmadmin       1544 Nov  9  2016 hc_rlzy1.dat
-rw-r----- 1 oracle oinstall       1536 Nov 11  2016 orapwRLZY1
-rw-r--r-- 1 oracle oinstall         60 Nov 20  2016 initRLZY1.ora
-rw-r--r-- 1 oracle oinstall         60 Nov 24  2016 initRLZY1.ora.bk
-rw-r--r-- 1 oracle oinstall       1922 Nov 24  2016 initCAIWU1.ora.bk
-rw-r--r-- 1 oracle oinstall         60 Nov 24  2016 initCAIWU1.ora
-rw-r----- 1 oracle oinstall       1536 Nov 24  2016 orapwCAIWU1
-rw-rw---- 1 oracle asmadmin       1544 Nov 24  2016 hc_chdyldg.dat
-rw-r--r-- 1 oracle oinstall       1979 Nov 24  2016 initchdyl1.ora.bk
-rw-r--r-- 1 oracle oinstall         61 Nov 24  2016 initchdyl1.ora
-rw-r----- 1 oracle oinstall       1536 Nov 24  2016 orapwchdyl1
-rw-r--r-- 1 oracle oinstall       2019 Nov 28  2016 initsjjh1.ora.bk
-rw-r--r-- 1 oracle oinstall         60 Nov 28  2016 initsjjh1.ora.bak.dgdb1
-rw-r----- 1 oracle oinstall       1536 Nov 28  2016 orapwsjjh1.bk
-rw-r--r-- 1 oracle oinstall         83 Nov 29  2016 initsjjh1.ora
-rw-rw---- 1 oracle asmadmin       1544 Apr  1  2017 hc_dbdb1.dat
-rw-r--r-- 1 oracle oinstall       1783 Apr  1  2017 initdadb1.ora.bk
-rw-r--r-- 1 oracle oinstall         53 Apr  1  2017 initdadb1.ora.bak.dgdb1
-rw-r----- 1 oracle oinstall       1536 Apr  1  2017 orapwdadb1
-rw-r--r-- 1 oracle oinstall         76 Apr  5  2017 initdadb1.ora
-rw-rw---- 1 oracle asmadmin       1544 Apr 22  2019 hc_chdyl1.dat
-rw-rw---- 1 oracle asmadmin       1544 Apr 22  2019 hc_dadb1.dat
-rw-rw---- 1 oracle asmadmin       1544 Apr 22  2019 hc_CAIWU1.dat
-rw-rw---- 1 oracle asmadmin       1544 Jul 29  2019 hc_RLZY1.dat
-rw-r----- 1 oracle oinstall       1536 Aug  6  2019 orapwsjjh
-rw-rw---- 1 oracle asmadmin       1544 Aug  6  2019 hc_sjjh.dat
-rw-r----- 1 oracle asmadmin   10797056 Oct 14  2019 snapcf_sjjh1.f
-rw-r----- 1 oracle oinstall       1536 Mar  1 18:31 orapwsjjh1
-rw-r----- 1 oracle asmadmin 1262486016 Mar  2 08:36 broken0
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:36 broken1
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:36 broken2
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:36 broken3
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:36 broken4
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:36 broken5
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:37 broken6
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:37 broken7
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:37 broken8
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:37 broken9
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:37 broken10
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:37 broken11
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:38 broken12
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:38 broken13
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:38 broken14
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:38 broken15
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:38 broken16
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:38 broken17
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:38 broken18
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:39 broken19
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:39 broken20
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:39 broken21
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:39 broken22
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:39 broken23
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:39 broken24
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:40 broken25
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:40 broken26
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:40 broken27
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:40 broken28
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:40 broken29
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:48 broken59
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:48 broken33
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:48 broken35
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:48 broken34
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:48 broken36
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:49 broken37
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:49 broken39
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:49 broken38
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:49 broken40
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:49 broken41
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:49 broken42
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:49 broken43
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:50 broken44
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:50 broken45
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:50 broken46
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:50 broken47
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:50 broken48
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:50 broken49
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:51 broken51
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:51 broken50
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:51 broken52
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:51 broken53
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:51 broken54
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:51 broken55
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:52 broken57
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:52 broken56
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 08:52 broken58
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 09:17 broken32
-rw-r----- 1 oracle asmadmin 1073742336 Mar  2 09:33 broken31
-rw-r----- 1 oracle asmadmin 1262486016 Mar  2 09:49 broken30
-rw-rw---- 1 oracle asmadmin       1544 Mar  2 10:02 hc_sjjh1.dat
-rw-r----- 1 oracle asmadmin   48316416 Jul 21 03:00 snapcf_RLZY1.f

查看文件broken0发现是sjjh数据库与日志文件相关的错误

查看实例的alert.log文件的内容,果然是在写日志文件时出现了错误:

Successful mount of redo thread 1, with mount id 4246975374
Physical Standby Database mounted.
Lost write protection disabled
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken0'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken0'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken1'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken1'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken2'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken2'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken3'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken3'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken4'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken4'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 6 of thread 1
ORA-00312: online log 6 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken5'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 6 of thread 1
ORA-00312: online log 6 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken5'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 7 of thread 1
ORA-00312: online log 7 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken6'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 7 of thread 1
ORA-00312: online log 7 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken6'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 8 of thread 1
ORA-00312: online log 8 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken7'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 8 of thread 1
ORA-00312: online log 8 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken7'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 9 of thread 1
ORA-00312: online log 9 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken8'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 9 of thread 1
ORA-00312: online log 9 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken8'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 10 of thread 1
ORA-00312: online log 10 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken9'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 10 of thread 1
ORA-00312: online log 10 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken9'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 11 of thread 1
ORA-00312: online log 11 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken10'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 11 of thread 1
ORA-00312: online log 11 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken10'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 12 of thread 1
ORA-00312: online log 12 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken11'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 12 of thread 1
ORA-00312: online log 12 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken11'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 13 of thread 1
ORA-00312: online log 13 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken12'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 13 of thread 1
ORA-00312: online log 13 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken12'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 14 of thread 1
ORA-00312: online log 14 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken13'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 14 of thread 1
ORA-00312: online log 14 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken13'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 15 of thread 1
ORA-00312: online log 15 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken14'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 15 of thread 1
ORA-00312: online log 15 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken14'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 16 of thread 1
ORA-00312: online log 16 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken15'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 16 of thread 1
ORA-00312: online log 16 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken15'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 17 of thread 1
ORA-00312: online log 17 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken16'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 17 of thread 1
ORA-00312: online log 17 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken16'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 18 of thread 1
ORA-00312: online log 18 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken17'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 18 of thread 1
ORA-00312: online log 18 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken17'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 19 of thread 1
ORA-00312: online log 19 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken18'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 19 of thread 1
ORA-00312: online log 19 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken18'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 20 of thread 1
ORA-00312: online log 20 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken19'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 20 of thread 1
ORA-00312: online log 20 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken19'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 21 of thread 1
ORA-00312: online log 21 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken20'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 21 of thread 1
ORA-00312: online log 21 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken20'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 22 of thread 1
ORA-00312: online log 22 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken21'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 22 of thread 1
ORA-00312: online log 22 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken21'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 23 of thread 1
ORA-00312: online log 23 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken22'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 23 of thread 1
ORA-00312: online log 23 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken22'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 24 of thread 1
ORA-00312: online log 24 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken23'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 24 of thread 1
ORA-00312: online log 24 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken23'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 25 of thread 1
ORA-00312: online log 25 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken24'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 25 of thread 1
ORA-00312: online log 25 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken24'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 26 of thread 1
ORA-00312: online log 26 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken25'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 26 of thread 1
ORA-00312: online log 26 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken25'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 27 of thread 1
ORA-00312: online log 27 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken26'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 27 of thread 1
ORA-00312: online log 27 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken26'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 28 of thread 1
ORA-00312: online log 28 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken27'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 28 of thread 1
ORA-00312: online log 28 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken27'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 29 of thread 1
ORA-00312: online log 29 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken28'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 29 of thread 1
ORA-00312: online log 29 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken28'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 30 of thread 1
ORA-00312: online log 30 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken29'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 30 of thread 1
ORA-00312: online log 30 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken29'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 31 of thread 1
ORA-00312: online log 31 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken30'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 31 of thread 1
ORA-00312: online log 31 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken30'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 32 of thread 1
ORA-00312: online log 32 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken31'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 32 of thread 1
ORA-00312: online log 32 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken31'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 33 of thread 1
ORA-00312: online log 33 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken32'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 33 of thread 1
ORA-00312: online log 33 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken32'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 34 of thread 1
ORA-00312: online log 34 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken33'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 34 of thread 1
ORA-00312: online log 34 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken33'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 35 of thread 1
ORA-00312: online log 35 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken34'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 35 of thread 1
ORA-00312: online log 35 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken34'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 36 of thread 1
ORA-00312: online log 36 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken35'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 36 of thread 1
ORA-00312: online log 36 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken35'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 37 of thread 1
ORA-00312: online log 37 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken36'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 37 of thread 1
ORA-00312: online log 37 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken36'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 38 of thread 1
ORA-00312: online log 38 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken37'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 38 of thread 1
ORA-00312: online log 38 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken37'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 39 of thread 1
ORA-00312: online log 39 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken38'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 39 of thread 1
ORA-00312: online log 39 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken38'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 40 of thread 1
ORA-00312: online log 40 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken39'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 40 of thread 1
ORA-00312: online log 40 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken39'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 41 of thread 1
ORA-00312: online log 41 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken40'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 41 of thread 1
ORA-00312: online log 41 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken40'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 42 of thread 1
ORA-00312: online log 42 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken41'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 42 of thread 1
ORA-00312: online log 42 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken41'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 43 of thread 1
ORA-00312: online log 43 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken42'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 43 of thread 1
ORA-00312: online log 43 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken42'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 44 of thread 1
ORA-00312: online log 44 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken43'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 44 of thread 1
ORA-00312: online log 44 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken43'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 45 of thread 1
ORA-00312: online log 45 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken44'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 45 of thread 1
ORA-00312: online log 45 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken44'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 46 of thread 1
ORA-00312: online log 46 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken45'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 46 of thread 1
ORA-00312: online log 46 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken45'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 47 of thread 1
ORA-00312: online log 47 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken46'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 47 of thread 1
ORA-00312: online log 47 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken46'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 48 of thread 1
ORA-00312: online log 48 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken47'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 48 of thread 1
ORA-00312: online log 48 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken47'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 49 of thread 1
ORA-00312: online log 49 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken48'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 49 of thread 1
ORA-00312: online log 49 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken48'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 50 of thread 1
ORA-00312: online log 50 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken49'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 50 of thread 1
ORA-00312: online log 50 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken49'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 51 of thread 1
ORA-00312: online log 51 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken50'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 51 of thread 1
ORA-00312: online log 51 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken50'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 52 of thread 1
ORA-00312: online log 52 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken51'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 52 of thread 1
ORA-00312: online log 52 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken51'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 53 of thread 1
ORA-00312: online log 53 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken52'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 53 of thread 1
ORA-00312: online log 53 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken52'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 54 of thread 1
ORA-00312: online log 54 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken53'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 54 of thread 1
ORA-00312: online log 54 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken53'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 55 of thread 1
ORA-00312: online log 55 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken54'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 55 of thread 1
ORA-00312: online log 55 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken54'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 56 of thread 1
ORA-00312: online log 56 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken55'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 56 of thread 1
ORA-00312: online log 56 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken55'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 57 of thread 1
ORA-00312: online log 57 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken56'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 57 of thread 1
ORA-00312: online log 57 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken56'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 58 of thread 1
ORA-00312: online log 58 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken57'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 58 of thread 1
ORA-00312: online log 58 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken57'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 59 of thread 1
ORA-00312: online log 59 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken58'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 59 of thread 1
ORA-00312: online log 59 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken58'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 60 of thread 1
ORA-00312: online log 60 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken59'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/sjjhdg/sjjh1/trace/sjjh1_lgwr_54343.trc:
ORA-00313: open failed for members of log group 60 of thread 1
ORA-00312: online log 60 thread 1: '/u01/app/oracle/product/11.2.0/db_1/dbs/broken59'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Completed: ALTER DATABASE MOUNT
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Mon Mar 02 08:35:50 2020
alter database open
AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access
查看主库的alert.log文件发现主库不能将日志同步到备库,原因是因为主备库之间网络故障
Mon Mar 02 01:17:22 2020
Thread 1 cannot allocate new log, sequence 3357
Private strand flush not complete
Current log# 11 seq# 3356 mem# 0: /oradata/sjjh/redo11.log
LGWR: Standby redo logfile selected for thread 1 sequence 3357 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3357 (LGWR switch)
Current log# 12 seq# 3357 mem# 0: /oradata/sjjh/redo12.log
Mon Mar 02 01:17:29 2020
Archived Log entry 6342 added for thread 1 sequence 3356 ID 0xfc687662 dest 1:
Mon Mar 02 02:00:00 2020
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Mon Mar 02 02:33:30 2020
Thread 1 cannot allocate new log, sequence 3358
Private strand flush not complete
Current log# 12 seq# 3357 mem# 0: /oradata/sjjh/redo12.log
LGWR: Standby redo logfile selected for thread 1 sequence 3358 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3358 (LGWR switch)
Current log# 13 seq# 3358 mem# 0: /oradata/sjjh/redo13.log
Mon Mar 02 02:33:38 2020
Archived Log entry 6344 added for thread 1 sequence 3357 ID 0xfc687662 dest 1:
Mon Mar 02 03:45:12 2020
Thread 1 cannot allocate new log, sequence 3359
Private strand flush not complete
Current log# 13 seq# 3358 mem# 0: /oradata/sjjh/redo13.log
LGWR: Standby redo logfile selected for thread 1 sequence 3359 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3359 (LGWR switch)
Current log# 14 seq# 3359 mem# 0: /oradata/sjjh/redo14.log
Mon Mar 02 03:45:19 2020
Archived Log entry 6346 added for thread 1 sequence 3358 ID 0xfc687662 dest 1:
Mon Mar 02 08:24:54 2020
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Error 3113 for archive log file 14 to 'jhk_st'
LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'jhk_st'
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
Thread 1 cannot allocate new log, sequence 3360
Private strand flush not complete
Current log# 14 seq# 3359 mem# 0: /oradata/sjjh/redo14.log
LGWR: Failed to archive log 14 thread 1 sequence 3359 (3113)
Thread 1 advanced to log sequence 3360 (LGWR switch)
Current log# 15 seq# 3360 mem# 0: /oradata/sjjh/redo15.log
Mon Mar 02 08:25:01 2020
Archived Log entry 6347 added for thread 1 sequence 3359 ID 0xfc687662 dest 1:
Mon Mar 02 08:30:31 2020
Thread 1 cannot allocate new log, sequence 3361
Private strand flush not complete
Current log# 15 seq# 3360 mem# 0: /oradata/sjjh/redo15.log
Mon Mar 02 08:30:52 2020
LGWR: Standby redo logfile selected for thread 1 sequence 3361 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3361 (LGWR switch)
Current log# 16 seq# 3361 mem# 0: /oradata/sjjh/redo16.log
Mon Mar 02 08:30:52 2020
Archived Log entry 6349 added for thread 1 sequence 3360 ID 0xfc687662 dest 1:
Mon Mar 02 08:30:52 2020
ARC0: Standby redo logfile selected for thread 1 sequence 3360 for destination LOG_ARCHIVE_DEST_2
Mon Mar 02 08:54:23 2020
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (272)
LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Error 272 for archive log file 16 to 'jhk_st'
Mon Mar 02 08:55:04 2020
Thread 1 cannot allocate new log, sequence 3362
Private strand flush not complete
Current log# 16 seq# 3361 mem# 0: /oradata/sjjh/redo16.log
LGWR: Failed to archive log 16 thread 1 sequence 3361 (272)
Thread 1 advanced to log sequence 3362 (LGWR switch)
Current log# 17 seq# 3362 mem# 0: /oradata/sjjh/redo17.log
Mon Mar 02 08:55:07 2020
Archived Log entry 6351 added for thread 1 sequence 3361 ID 0xfc687662 dest 1:
Mon Mar 02 09:00:04 2020
ARC3: Standby redo logfile selected for thread 1 sequence 3361 for destination LOG_ARCHIVE_DEST_2
Mon Mar 02 09:00:04 2020
Thread 1 cannot allocate new log, sequence 3363
Private strand flush not complete
Current log# 17 seq# 3362 mem# 0: /oradata/sjjh/redo17.log
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected for thread 1 sequence 3363 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3363 (LGWR switch)
Current log# 18 seq# 3363 mem# 0: /oradata/sjjh/redo18.log
Mon Mar 02 09:00:08 2020
Archived Log entry 6353 added for thread 1 sequence 3362 ID 0xfc687662 dest 1:
ARC3: Standby redo logfile selected for thread 1 sequence 3362 for destination LOG_ARCHIVE_DEST_2
Mon Mar 02 09:13:31 2020
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Error 3113 for archive log file 18 to 'jhk_st'
LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'jhk_st'
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
Thread 1 cannot allocate new log, sequence 3364
Private strand flush not complete
Current log# 18 seq# 3363 mem# 0: /oradata/sjjh/redo18.log
LGWR: Failed to archive log 18 thread 1 sequence 3363 (3113)
Thread 1 advanced to log sequence 3364 (LGWR switch)
Current log# 19 seq# 3364 mem# 0: /oradata/sjjh/redo19.log
Mon Mar 02 09:13:36 2020
Archived Log entry 6355 added for thread 1 sequence 3363 ID 0xfc687662 dest 1:
Mon Mar 02 09:14:42 2020
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
Mon Mar 02 09:14:43 2020
ARC3: Standby redo logfile selected for thread 1 sequence 3363 for destination LOG_ARCHIVE_DEST_2
Mon Mar 02 09:14:45 2020
Thread 1 cannot allocate new log, sequence 3365
Private strand flush not complete
Current log# 19 seq# 3364 mem# 0: /oradata/sjjh/redo19.log
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected for thread 1 sequence 3365 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3365 (LGWR switch)
Current log# 20 seq# 3365 mem# 0: /oradata/sjjh/redo20.log
Mon Mar 02 09:14:48 2020
Archived Log entry 6357 added for thread 1 sequence 3364 ID 0xfc687662 dest 1:
Mon Mar 02 09:16:14 2020
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (272)
LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Error 272 for archive log file 20 to 'jhk_st'
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
Thread 1 cannot allocate new log, sequence 3366
Private strand flush not complete
Current log# 20 seq# 3365 mem# 0: /oradata/sjjh/redo20.log
LGWR: Failed to archive log 20 thread 1 sequence 3365 (272)
Thread 1 advanced to log sequence 3366 (LGWR switch)
Current log# 21 seq# 3366 mem# 0: /oradata/sjjh/redo21.log
Mon Mar 02 09:16:19 2020
Archived Log entry 6359 added for thread 1 sequence 3365 ID 0xfc687662 dest 1:
Mon Mar 02 09:22:07 2020
Thread 1 cannot allocate new log, sequence 3367
Private strand flush not complete
Current log# 21 seq# 3366 mem# 0: /oradata/sjjh/redo21.log
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected for thread 1 sequence 3367 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3367 (LGWR switch)
Current log# 22 seq# 3367 mem# 0: /oradata/sjjh/redo22.log
Mon Mar 02 09:22:10 2020
Archived Log entry 6361 added for thread 1 sequence 3366 ID 0xfc687662 dest 1:
Mon Mar 02 09:29:22 2020
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Error 3113 for archive log file 22 to 'jhk_st'
LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'jhk_st'
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
Thread 1 cannot allocate new log, sequence 3368
Private strand flush not complete
Current log# 22 seq# 3367 mem# 0: /oradata/sjjh/redo22.log
LGWR: Failed to archive log 22 thread 1 sequence 3367 (3113)
Thread 1 advanced to log sequence 3368 (LGWR switch)
Current log# 23 seq# 3368 mem# 0: /oradata/sjjh/redo23.log
Mon Mar 02 09:29:26 2020
Archived Log entry 6363 added for thread 1 sequence 3367 ID 0xfc687662 dest 1:
Mon Mar 02 09:30:04 2020
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
Mon Mar 02 09:30:05 2020
ARC3: Standby redo logfile selected for thread 1 sequence 3367 for destination LOG_ARCHIVE_DEST_2
Mon Mar 02 09:30:05 2020
Thread 1 cannot allocate new log, sequence 3369
Private strand flush not complete
Current log# 23 seq# 3368 mem# 0: /oradata/sjjh/redo23.log
Suppressing further error logging of LOG_ARCHIVE_DEST_2.
FAL[server, ARC3]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance sjjh - Archival Error. Archiver continuing.
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected for thread 1 sequence 3369 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3369 (LGWR switch)
Current log# 24 seq# 3369 mem# 0: /oradata/sjjh/redo24.log
Mon Mar 02 09:30:08 2020
Archived Log entry 6365 added for thread 1 sequence 3368 ID 0xfc687662 dest 1:
ARC3: Standby redo logfile selected for thread 1 sequence 3368 for destination LOG_ARCHIVE_DEST_2
Mon Mar 02 09:31:27 2020
Suppressing further error logging of LOG_ARCHIVE_DEST_2.
Suppressing further error logging of LOG_ARCHIVE_DEST_2.
Suppressing further error logging of LOG_ARCHIVE_DEST_2.
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
Thread 1 cannot allocate new log, sequence 3370
Private strand flush not complete
Current log# 24 seq# 3369 mem# 0: /oradata/sjjh/redo24.log
LGWR: Failed to archive log 24 thread 1 sequence 3369 (272)
Thread 1 advanced to log sequence 3370 (LGWR switch)
Current log# 25 seq# 3370 mem# 0: /oradata/sjjh/redo25.log
Mon Mar 02 09:31:33 2020
Archived Log entry 6367 added for thread 1 sequence 3369 ID 0xfc687662 dest 1:
Mon Mar 02 09:37:09 2020
Thread 1 cannot allocate new log, sequence 3371
Private strand flush not complete
Current log# 25 seq# 3370 mem# 0: /oradata/sjjh/redo25.log
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected for thread 1 sequence 3371 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3371 (LGWR switch)
Current log# 26 seq# 3371 mem# 0: /oradata/sjjh/redo26.log
Mon Mar 02 09:37:12 2020
Archived Log entry 6369 added for thread 1 sequence 3370 ID 0xfc687662 dest 1:
Mon Mar 02 09:37:12 2020
ARC3: Standby redo logfile selected for thread 1 sequence 3370 for destination LOG_ARCHIVE_DEST_2
Mon Mar 02 09:42:26 2020
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
Thread 1 cannot allocate new log, sequence 3372
Private strand flush not complete
Current log# 26 seq# 3371 mem# 0: /oradata/sjjh/redo26.log
LGWR: Failed to archive log 26 thread 1 sequence 3371 (3113)
Thread 1 advanced to log sequence 3372 (LGWR switch)
Current log# 27 seq# 3372 mem# 0: /oradata/sjjh/redo27.log
Mon Mar 02 09:42:31 2020
Archived Log entry 6371 added for thread 1 sequence 3371 ID 0xfc687662 dest 1:
Mon Mar 02 09:44:23 2020
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
Mon Mar 02 09:44:23 2020
ARC0: Standby redo logfile selected for thread 1 sequence 3371 for destination LOG_ARCHIVE_DEST_2
Mon Mar 02 09:44:25 2020
Thread 1 cannot allocate new log, sequence 3373
Private strand flush not complete
Current log# 27 seq# 3372 mem# 0: /oradata/sjjh/redo27.log
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected for thread 1 sequence 3373 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3373 (LGWR switch)
Current log# 28 seq# 3373 mem# 0: /oradata/sjjh/redo28.log
Mon Mar 02 09:44:28 2020
Archived Log entry 6373 added for thread 1 sequence 3372 ID 0xfc687662 dest 1:
Mon Mar 02 09:44:28 2020
ARC3: Standby redo logfile selected for thread 1 sequence 3372 for destination LOG_ARCHIVE_DEST_2
Mon Mar 02 09:45:34 2020
Thread 1 cannot allocate new log, sequence 3374
Private strand flush not complete
Current log# 28 seq# 3373 mem# 0: /oradata/sjjh/redo28.log
LGWR: Standby redo logfile selected for thread 1 sequence 3374 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3374 (LGWR switch)
Current log# 29 seq# 3374 mem# 0: /oradata/sjjh/redo29.log
Mon Mar 02 09:45:35 2020
Archived Log entry 6376 added for thread 1 sequence 3373 ID 0xfc687662 dest 1:
Mon Mar 02 13:45:51 2020
LGWR: Standby redo logfile selected for thread 1 sequence 3375 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3375 (LGWR switch)
Current log# 30 seq# 3375 mem# 0: /oradata/sjjh/redo30.log
Mon Mar 02 13:45:55 2020
Archived Log entry 6378 added for thread 1 sequence 3374 ID 0xfc687662 dest 1:
Mon Mar 02 13:47:12 2020
Thread 1 cannot allocate new log, sequence 3376
Private strand flush not complete
Current log# 30 seq# 3375 mem# 0: /oradata/sjjh/redo30.log
Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION
Thread 1 advanced to log sequence 3376 (LGWR switch)
Current log# 1 seq# 3376 mem# 0: /oradata/sjjh/redo01.log
Mon Mar 02 13:47:19 2020
Archived Log entry 6380 added for thread 1 sequence 3375 ID 0xfc687662 dest 1:
Mon Mar 02 22:00:00 2020
Setting Resource Manager plan SCHEDULER[0x32D9]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Mon Mar 02 22:00:00 2020
Starting background process VKRM
Mon Mar 02 22:00:00 2020
VKRM started with pid=47, OS id=81983
Mon Mar 02 22:00:02 2020
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Mon Mar 02 22:01:00 2020
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Tue Mar 03 00:38:04 2020
Thread 1 cannot allocate new log, sequence 3377
Private strand flush not complete
Current log# 1 seq# 3376 mem# 0: /oradata/sjjh/redo01.log
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected for thread 1 sequence 3377 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3377 (LGWR switch)
Current log# 2 seq# 3377 mem# 0: /oradata/sjjh/redo02.log
Tue Mar 03 00:38:12 2020
Archived Log entry 6382 added for thread 1 sequence 3376 ID 0xfc687662 dest 1:
Tue Mar 03 00:40:10 2020
LGWR: Standby redo logfile selected for thread 1 sequence 3378 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3378 (LGWR switch)
Current log# 3 seq# 3378 mem# 0: /oradata/sjjh/redo03.log

明白原因之后现在可以删除dbs目录中的broker*文件来释放空间

使用mysqldump以文本分隔格式来dump数据

这里将介绍如何使用mysqldump来创建文本分隔格式的dump文件。在调用mysqldump时使用–tab=dir_name选项时,使用dir_name作为输出目录并且在输出目录是为每个表生成两个文件。表名是这些文件的基础名称。对表t,文件名为t.sql和t.txt。其中.sql文件包含的是表的 create table语句。.txt文件包含的是表数据,每一条记录一行。下面的命令将数据库mysql的内容dump到/mysqldata/tmp目录中

在用mysqldump备份时候遇到1290的错误

从提示看到是因为mysql服务启用了--secure-file-priv,所以才无法执行。这个选项或系统变量用被来限制通过load data和select ... into outfile语句和load_file()函数所执行导入和导出操作的数据量。

secure_file_priv有以下设置:
.如果为空,变量不生效。没有安全设置。
.如果设置为一个目录名,服务器对导入和导出操作只对这个目录中的文件生效,但目录必须存在,服务器不会创建目录。
.如果设置NULL,服务器禁止导入和导出操作。

查看数据库当前设置:

mysql> show global variables like '%secur%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.01 sec)

看到secure_file_priv当前设置为NULL,说明限制导入和导出操作。

查看配置文件my.cnf发现没有设置secure_file_priv参数,那么这个参数默认应该是为NULL。

[mysql@localhost mysql]$ cat my.cnf


[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql
bind-address=*
user=mysql
port=3306
log-error=/mysqldata/mysql/mysql.err
pid-file=/mysqldata/mysql/mysqld.pid
socket = /mysqlsoft/mysql/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true
innodb_flush_method=O_DIRECT
binlog_format = mixed
log-bin=/mysqldata/mysql/binlog
max_binlog_size = 100m
binlog_cache_size = 4m
server-id=1

修改配置文件my.cnf参加secure_file_priv=

[mysql@localhost mysql]$ vi my.cnf
.....
secure-file-priv=

重启mysql数据库

[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!

再次检查secure_file_priv参数设置

mysql> show global variables like '%secur%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         |       |
+--------------------------+-------+
3 rows in set (0.00 sec)

再执行导出操作成功

[mysql@localhost ~]$ mysqldump -uroot -pxxzx7817600 --tab=/mysqldata/tmp  mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

数据库服务器会生成包含数据内容的.txt文件,因此它是属于运行数据库服务的系统账号。服务器使用select … into outfile来写这个文件,因此你必须有file权限才能执行这个操作,如果一个特定的.txt文件已经存在的话会出错。

数据库服务器为被dump的表发送create定义语句来mysqldump,它将被写入到.sql文件中。因此这些文件属于执行mysqldump命令的用户。

–tab选项最好只在本地服务器上执行dump。如果将它用于远程服务器,–tab选项所指定的目录必须在本地服务器和远程服务器上都要存在,燕且.txt文件会被服务器写入远程目录(数据库服务器所在主机),而.sql文件将被mysqldump写入本地目录(客户端所在主机)。

对于mysqldump –tab,服务器默认情况下将表数据写入.txt文件,每行表记录一行,列值之间使用制表符,列值周围不使用引号,换行符作为行结束符。

为了能在写入数据文件时使用不同的格式,mysqldump支持以下选项:
.–fields-terminated-by=str
列值分隔符(默认为:tab制表符)

.–fields-enclosed-by=char
将列值括在其中的字符(默认为:没有)

.–fields-optionally-enclosed-by=char
用来括起非数值列值的字符(默认为:没有)

.–fields-escaped-by=char
用于转义特殊字符的字符(默认为:没有转义)
.–lines-terminated-by=str
行终止符(默认为:新行)

根据你所为这些选项指定的值,为了让命令解释器能正确处理可能需要在命令行中为这些值使用引号或转义。另外可以以十六进制格式来指定。假设你想用双引号引用列值。为了达到这个目的应该为–fields-enclosed-by选项指定双引号。但双引号通常对于命令解释器有特定含义并且必须特定对待。例如,在Unix中,可以使用引号来指定双引号:
–fields-enclosed-by='”‘

在任何平台中,可以指定十六进制值:
–fields-enclosed-by=0x22

同时使用几个数据格式化选项是很常见的。例如,为了使用通过回车/换行符对(\r\n)作为行终止符的逗号分隔值格式来dump表,可以执行以下命令:

[mysql@localhost tmp]$ mysqldump -uroot -pxxzx7817600 --tab=/mysqldata/tmp --fields-terminated-by=, --fields-enclosed-by='"'  --lines-terminated-by=0x0d0a mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[mysql@localhost tmp]$ ls -lrt
总用量 3128
-rw-r--r--. 1 mysql mysql    1871 7月   9 17:54 rewrite_rules.sql
-rw-r--r--. 1 mysql mysql    1876 7月  10 15:37 columns_priv.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 columns_priv.txt
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 count.txt
-rw-r--r--. 1 mysql mysql    1298 7月  10 15:37 count.sql
-rw-rw-rw-. 1 mysql mysql       7 7月  10 15:37 cs.txt
-rw-r--r--. 1 mysql mysql    1359 7月  10 15:37 cs.sql
-rw-r--r--. 1 mysql mysql    2917 7月  10 15:37 db.sql
-rw-rw-rw-. 1 mysql mysql     198 7月  10 15:37 db.txt
-rw-rw-rw-. 1 mysql mysql     114 7月  10 15:37 engine_cost.txt
-rw-r--r--. 1 mysql mysql    1630 7月  10 15:37 engine_cost.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 event.txt
-rw-r--r--. 1 mysql mysql    3350 7月  10 15:37 event.sql
-rw-r--r--. 1 mysql mysql    1557 7月  10 15:37 func.sql
-rw-rw-rw-. 1 mysql mysql     398 7月  10 15:37 func.txt
-rw-r--r--. 1 mysql mysql    1578 7月  10 15:37 gtid_executed.sql
-rw-rw-rw-. 1 mysql mysql     964 7月  10 15:37 help_category.txt
-rw-r--r--. 1 mysql mysql    1563 7月  10 15:37 help_category.sql
-rw-r--r--. 1 mysql mysql    1471 7月  10 15:37 help_keyword.sql
-rw-rw-rw-. 1 mysql mysql    9748 7月  10 15:37 help_keyword.txt
-rw-r--r--. 1 mysql mysql    1486 7月  10 15:37 help_relation.sql
-rw-rw-rw-. 1 mysql mysql   10771 7月  10 15:37 help_relation.txt
-rw-r--r--. 1 mysql mysql    1592 7月  10 15:37 help_topic.sql
-rw-rw-rw-. 1 mysql mysql  712538 7月  10 15:37 help_topic.txt
-rw-rw-rw-. 1 mysql mysql      32 7月  10 15:37 imptest.txt
-rw-r--r--. 1 mysql mysql    1337 7月  10 15:37 imptest.sql
-rw-r--r--. 1 mysql mysql    1879 7月  10 15:37 innodb_index_stats.sql
-rw-rw-rw-. 1 mysql mysql    2647 7月  10 15:37 innodb_index_stats.txt
-rw-rw-rw-. 1 mysql mysql     395 7月  10 15:37 innodb_table_stats.txt
-rw-r--r--. 1 mysql mysql    1745 7月  10 15:37 innodb_table_stats.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 ndb_binlog_index.txt
-rw-r--r--. 1 mysql mysql    1842 7月  10 15:37 ndb_binlog_index.sql
-rw-rw-rw-. 1 mysql mysql      76 7月  10 15:37 person.txt
-rw-r--r--. 1 mysql mysql    1571 7月  10 15:37 person.sql
-rw-r--r--. 1 mysql mysql    1420 7月  10 15:37 plugin.sql
-rw-rw-rw-. 1 mysql mysql      53 7月  10 15:37 plugin.txt
-rw-r--r--. 1 mysql mysql    3067 7月  10 15:37 proc.sql
-rw-rw-rw-. 1 mysql mysql     806 7月  10 15:37 proc.txt
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 procs_priv.txt
-rw-r--r--. 1 mysql mysql    1970 7月  10 15:37 procs_priv.sql
-rw-rw-rw-. 1 mysql mysql      60 7月  10 15:37 proxies_priv.txt
-rw-r--r--. 1 mysql mysql    1862 7月  10 15:37 proxies_priv.sql
-rw-rw-rw-. 1 mysql mysql     297 7月  10 15:37 server_cost.txt
-rw-r--r--. 1 mysql mysql    1530 7月  10 15:37 server_cost.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 servers.txt
-rw-r--r--. 1 mysql mysql    1730 7月  10 15:37 servers.sql
-rw-r--r--. 1 mysql mysql    4150 7月  10 15:37 slave_master_info.sql
-rw-r--r--. 1 mysql mysql    2406 7月  10 15:37 slave_relay_log_info.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 slave_worker_info.txt
-rw-r--r--. 1 mysql mysql    2221 7月  10 15:37 slave_worker_info.sql
-rw-r--r--. 1 mysql mysql    1342 7月  10 15:37 t.sql
-rw-rw-rw-. 1 mysql mysql      52 7月  10 15:37 t.txt
-rw-rw-rw-. 1 mysql mysql       3 7月  10 15:37 t1.txt
-rw-r--r--. 1 mysql mysql    1291 7月  10 15:37 t1.sql
-rw-r--r--. 1 mysql mysql    1306 7月  10 15:37 t2.sql
-rw-rw-rw-. 1 mysql mysql       4 7月  10 15:37 t2.txt
-rw-rw-rw-. 1 mysql mysql     162 7月  10 15:37 tables_priv.txt
-rw-r--r--. 1 mysql mysql    2068 7月  10 15:37 tables_priv.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 test.txt
-rw-r--r--. 1 mysql mysql    1304 7月  10 15:37 test.sql
-rw-r--r--. 1 mysql mysql    1487 7月  10 15:37 time_zone.sql
-rw-rw-rw-. 1 mysql mysql   11122 7月  10 15:37 time_zone.txt
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 time_zone_leap_second.txt
-rw-r--r--. 1 mysql mysql    1493 7月  10 15:37 time_zone_leap_second.sql
-rw-r--r--. 1 mysql mysql    1435 7月  10 15:37 time_zone_name.sql
-rw-rw-rw-. 1 mysql mysql   41245 7月  10 15:37 time_zone_name.txt
-rw-r--r--. 1 mysql mysql    1548 7月  10 15:37 time_zone_transition.sql
-rw-rw-rw-. 1 mysql mysql 1998482 7月  10 15:37 time_zone_transition.txt
-rw-r--r--. 1 mysql mysql    1670 7月  10 15:37 time_zone_transition_type.sql
-rw-rw-rw-. 1 mysql mysql  161600 7月  10 15:37 time_zone_transition_type.txt
-rw-rw-rw-. 1 mysql mysql     878 7月  10 15:37 user.txt
-rw-r--r--. 1 mysql mysql    4390 7月  10 15:37 user.sql
-rw-r--r--. 1 mysql mysql    1576 7月  10 15:37 general_log.sql
-rw-r--r--. 1 mysql mysql    1757 7月  10 15:37 slow_log.sql


mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)


[mysql@localhost tmp]$ cat t.sql
-- MySQL dump 10.13  Distrib 5.7.26, for Linux (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       5.7.26-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `t`
--

DROP TABLE IF EXISTS `t`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-07-10 15:37:05

[mysql@localhost tmp]$ cat t.txt
"1","jingyong",\N
"2","yeyali",\N
"3","huangyan",\N
"4","wenyao",\N

如果使用任何数据格式化选项来转储表数据,则在稍后重新加载数据文件时需要指定相同的格式,以确保正确地解释文件内容。

重新加载文本分隔格式的备份文件
对于使用mysqldump –tab命令所生成的备份,每个表在输出目录中都有一个包含create table语句的.sql文件和一个包含表数据的.txt文件。为了重新加载表,首先进入到输出目录。然后使用msyql命令来处理.sql文件创建一个空表,再执行mysqlimport来处理.txt文件加载数据:

[mysql@localhost tmp]$ mysql -uroot -pxxzx7817600  mysql < t.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

[mysql@localhost tmp]$ mysqlimport -uroot -pxxzx7817600  --fields-terminated-by=, --fields-enclosed-by='"'  --lines-terminated-by=0x0d0a mysql /mysqldata/tmp/t.txt
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
mysql.t: Records: 4  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

另一种替mysqlimport来加载数据文件的方式是使用load data infile语句:

mysql> load data infile '/mysqldata/tmp/t.txt' into table t fields terminated by ',' enclosed by '"' lines terminated by 0x0d0a;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

使用mysqldump以SQL格式来dump数据

Using mysqldump for Backups
使用mysqldump工具可以以以下几种方式来生成dump文件:
.作为备份在数据丢失的情况下来执行数据恢复
.作为源数据用来配置复制从服务器
.作为源数据用来进行实验:
-为了复制数据库而不用改变源数据
-为了测试潜在的升级后不兼容的问题

mysqldump生成两种类型的输出,这依赖于是否使用–tab选项:
.不使用–tab选项,mysqldump将输出SQL语句到标准输出。这类输出由创建dump对象(数据库,表,存储过程等)的create语句组成,并且包含向表加载数据的insert语句。输出可以被保存大文件中并且之后可以使用mysql来重新加载来重新创建被dump的对象。有一些选项可用于修改SQL语句的格式,以及控制转储哪些对象。

.使用–tab选项,mysqldump将为每个被dump的对象生成两个输出文件。服务器以制表符分隔的文本形式写入一个文件用来存储表记录,每条记录一行。这个文件名为tbl_name.txt被保存输出目录中。服务器还会在输出目录中生成一个名为tbl_name.sql的文件用来存储create table语句。

使用mysqldump以SQL格式来dump数据
默认情况下,mysqldump以SQL语句来作为标准输出。可以将输出保存到文件中:

为了dump所有数据库,可以使用–all-databases选项来调用mysqldump

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234 --all-databases > all_db_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

为了dump指定的数据库,可以使用–databases选项来指定

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234 --databases mysql query_rewrite > dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

–databases选项会让命令行中的所有名字作为数据库名字来对待。不使用这个选项,mysqldump将第一个名字作为数据库名后面的作为表名对待。

使用–all-databases或–databases选项,mysqldump在dump每个数据库之前会写入create database和use语句。这确保了当dump文件被重新加载时,如果不存在数据库时会创建数据库并使它作为默认数据库使用数据库的内容被加载到相同的数据库中。如果想让dump文件在重建数据之前强制删除每个数据库,可以使用–add-drop-database选项。在这种情况下,mysqldump会在每个create database语句之前写一个drop database语句。

为了dump单个数据库,在命令行中指定数据库名:

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234 --databases mysql > dump_mysql.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

在dump单个数据库的情况下,可以忽略–databases选项:

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234  mysql > dump_mysql_1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.



[mysql@localhost ~]$ more dump_mysql.sql
-- MySQL dump 10.13  Distrib 5.7.26, for Linux (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       5.7.26-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `mysql`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE `mysql`;

--
-- Table structure for table `columns_priv`
--

[mysql@localhost ~]$ more dump_mysql_1.sql
-- MySQL dump 10.13  Distrib 5.7.26, for Linux (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       5.7.26-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `columns_priv`
--

上面两个命令之间的差异就在于不使用–databases选项时,dump输出不会包含create database或use语句。这有几种含义:
.当重新加载dump文件时,你必须指定一个默认数据库名因此服务器就会知道那个数据库会被加载。
.为了重新加载,可以指定一个不同于原始数据库名的数据库名,这能让你加载数据到不同的数据库中。
.如果被加载的数据库不存在,你必须先创建数据库。
.因为输出将不会包含create database语句,–add-drop-database选项没有影响。如果使用它,将不会生成drop database语句。

为了dump一个数据库中的指定表,在命令行中指定数据库名后指定表名:

[mysql@localhost ~]$ mysqldump -uroot -pabcd1234  mysql t t1 t2 > dump_tables.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[mysql@localhost ~]$ cat dump_tables.sql
-- MySQL dump 10.13  Distrib 5.7.26, for Linux (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       5.7.26-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `t`
--

DROP TABLE IF EXISTS `t`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t`
--

LOCK TABLES `t` WRITE;
/*!40000 ALTER TABLE `t` DISABLE KEYS */;
INSERT INTO `t` VALUES (1,'jingyong',NULL),(2,'yeyali',NULL),(3,'huangyan',NULL),(4,'wenyao',NULL);
/*!40000 ALTER TABLE `t` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `c1` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t1`
--

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES ('xy');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t2`
--

DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `i` int(10) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t2`
--

LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
INSERT INTO `t2` VALUES (0),(1);
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-07-10 17:19:42

重新加载SQL格式备份
为了重新加载由mysqldump工具生成的由SQL语句组成的dump文件,使用这个dump文件作为mysql客户端的输入。如果dump文件是由使用–all-databases或–databases选项的mysqldump命令所生成,它将包含create database和use语句并且不需要为加载的数据指定默认数据库。

[mysql@localhost ~]$ mysql -uroot -pabcd1234  mysql  < dump_tables.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

一种替代的方式是通过在mysql命令提示符下使用source命令:

mysql> source /var/lib/mysql/dump_tables.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

mysql> select * from t1;
+------+
| c1   |
+------+
| xy   |
+------+
1 row in set (0.00 sec)

mysql> select * from t2;
+---+
| i |
+---+
| 0 |
| 1 |
+---+
2 rows in set (0.00 sec)

如果dump文件是单数据库dump且不包含create database和use语句,那么首先需要创建数据库(必须的):

shell> mysqladmin create db1

然后在加载dump文件时指定数据库名:

shell> mysql db1 < dump.sql

另一种方式,在mysql提示符中,创建数据库,选它作为默认数据库,然后加载dump文件:

mysql> create database if not exists db1;
mysql> use db1;
mysql> source /var/lib/mysql/dump_tables.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysqldump Got error 1290

在用mysqldump备份时候遇到1290的错误

[mysql@localhost ~]$ mysqldump -uroot -pxxzx7817600 --tab=/mysqldata/tmp  query_rewrite
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

从提示看到是因为mysql服务启用了–secure-file-priv,所以才无法执行。这个选项或系统变量用被来限制通过load data和select … into outfile语句和load_file()函数所执行导入和导出操作的数据量。

secure_file_priv有以下设置:
.如果为空,变量不生效。没有安全设置。
.如果设置为一个目录名,服务器对导入和导出操作只对这个目录中的文件生效,但目录必须存在,服务器不会创建目录。
.如果设置NULL,服务器禁止导入和导出操作。

查看数据库当前设置:

mysql> show global variables like '%secur%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.01 sec)

看到secure_file_priv当前设置为NULL,说明限制导入和导出操作。

查看配置文件my.cnf发现没有设置secure_file_priv参数,那么这个参数默认应该是为NULL。

[mysql@localhost mysql]$ cat my.cnf


[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql
bind-address=*
user=mysql
port=3306
log-error=/mysqldata/mysql/mysql.err
pid-file=/mysqldata/mysql/mysqld.pid
socket = /mysqlsoft/mysql/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true
innodb_flush_method=O_DIRECT
binlog_format = mixed
log-bin=/mysqldata/mysql/binlog
max_binlog_size = 100m
binlog_cache_size = 4m
server-id=1

修改配置文件my.cnf参加secure_file_priv=

[mysql@localhost mysql]$ vi my.cnf
.....
secure-file-priv=

重启mysql数据库

[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!

再次检查secure_file_priv参数设置

mysql> show global variables like '%secur%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         |       |
+--------------------------+-------+
3 rows in set (0.00 sec)

再执行导出操作成功

[mysql@localhost ~]$ mysqldump -uroot -pxxzx7817600 --tab=/mysqldata/tmp  mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

DM7 RAC数据库恢复成单机数据库

DM7 RAC数据库恢复成单机数据库
1) 启动RMAN备份数据库,保证服务器处于脱机状态。

RMAN> backup database '/dm7/data/rac0_config/dm.ini' full backupset '/dm7/backup/db_rac_full_bak_recover_dbmagic';
backup database '/dm7/data/rac0_config/dm.ini' full backupset '/dm7/backup/db_rac_full_bak_recover_dbmagic';
file dm.key not found, use default license!
checking if the RAC database under system path [+DMDATA/data/rac] is running....
EP [0] is checking....
EP [1] is checking....
EP[0] max_lsn: 76986
EP[0] adjust cur_lsn from [76986] to [77001]
BACKUP DATABASE [rac], execute......
CMD CHECK LSN......
BACKUP DATABASE [rac], collect dbf......
CMD CHECK ......
DBF BACKUP SUBS......
total 1 packages processed...
total 2 packages processed...
total 3 packages processed...
total 4 packages processed...
total 5 packages processed...
DBF BACKUP MAIN......
BACKUPSET [/dm7/backup/db_rac_full_bak_recover_dbmagic] END, CODE [0]......
META GENERATING......
total 9 packages processed...
total 9 packages processed!
CMD END.CODE:[0]
backup successfully!
time used: 3887.714(ms)

RMAN> show backupset '/dm7/backup/db_rac_full_bak_recover_dbmagic';
show backupset '/dm7/backup/db_rac_full_bak_recover_dbmagic';




system path:           +DMDATA/data/rac
db magic:              -1632052820
permanent magic:       450923536
rac node:              2
page check:            0
rlog encrypt:          0
external cipher[id/name]:  0/
external hash[id/name]:  0/
length in char:        0
use new hash:          1
page size:             8 KB
extent size:           16
case sensitive:        1
log page size:         512 B
unicode_flag/charset:  0
data version:          0x7000A
sys version:           V7.1.6.46-Build(2018.02.08-89107)ENT
enable policy:         0
archive flag:          1
blank_pad_mode:        0
crc_check:             TRUE
backupset sig:         BA
backupset version:     4009
database name:         rac
backup name:           DB_FULL_rac_20200616_154323_000689
backupset description:
backupset ID :         1145407476
parent backupset ID:     -1
META file size :       74240
compressed level:      0
encrypt type:          0
parallel num:          1
backup range:          database
mpp_timestamp:         1592293401
ddl_clone:             FALSE
mpp_flag:              FALSE
backup level:          offline
backup type:           full
without log:           FALSE
START_LSN:             76987
START_SEQ:             309
END_LSN:               77001
END_SEQ:               308
base START_LSN:        -1
base END_LSN:          -1
base name:
base backupset:
backup time:           2020-06-16 15:43:25
min trx start lsn:     76987
min exec ver:          0x0701060C
pkg size:              0x02000000



RAC EP:            0
RAC status:          OK
RAC begin lsn:       76987
RAC begin seq:       309
RAC end lsn:         77001
RAC end seq:         308
RAC base begin lsn:  -1
RAC base begin seq:  -1
RAC base end lsn:    -1
RAC base end seq:    -1

RAC EP:            1
RAC status:          OK
RAC begin lsn:       77002
RAC begin seq:       308
RAC end lsn:         77001
RAC end seq:         307
RAC base begin lsn:  -1
RAC base begin seq:  -1
RAC base end lsn:    -1
RAC base end seq:    -1


backupset directory: /dm7/backup/db_rac_full_bak_recover_dbmagic
backupset name:        db_rac_full_bak_recover_dbmagic
backup data file num:  5
backup piece num:      1


$file_seq |$size(KB) |$pos_desc                                               |$content_type
0         |26103     |db_rac_full_bak_recover_dbmagic.bak                     |DATA


$file_seq |$group_id |$group_name                      |$file_id  |$file_path                       |$mirror_path                     |$file_len
1         |0         |SYSTEM                           |0         |+DMDATA/data/rac/system.dbf      |                                 |134217728
2         |1         |ROLL                             |0         |+DMDATA/data/rac/roll.dbf        |                                 |134217728
3         |4         |MAIN                             |0         |+DMDATA/data/rac/main.dbf        |                                 |134217728
4         |5         |TS_FOR_DBF                       |0         |+DMDATA/data/rac/ts_for_dbf_01.dbf|                                 |134217728
5         |5         |TS_FOR_DBF                       |1         |+DMDATA/data/rac/ts_for_dbf_02.dbf|                                 |134217728


$file_seq |$file_path                       |$file_len           |$begin_lsn          |$begin_seqno        |$begin_rpag_off     |$end_lsn            |$end_seqno          |$create_time        |$close_time


show backupsets successfully.
time used: 10.655(ms)

2) 准备目标库,可以使用备份库,也可以重新生成库。这里是在别一台服务器上创建新库,重新生成库操作如下:

[dmdba@dmks ~]$ dminit path=/dm_home/dmdbms/data db_name=dameng_for_recover auto_overwrite=1 port_num=5336
initdb V7.1.6.46-Build(2018.02.08-89107)ENT
db version: 0x7000a
file dm.key not found, use default license!
License will expire in 14 day(s) on 2020-06-30

 log file path: /dm_home/dmdbms/data/dameng_for_recover/dameng_for_recover01.log


 log file path: /dm_home/dmdbms/data/dameng_for_recover/dameng_for_recover02.log

write to dir [/dm_home/dmdbms/data/dameng_for_recover].
create dm database success. 2020-06-16 16:03:18


[root@dmks root]# ./dm_service_installer.sh  -i /dm_home/dmdbms/data/dameng_for_recover/dm.ini -p dmrc -t dmserver
Move the service script file(/dm_home/dmdbms/bin/DmServicedmrc to /etc/rc.d/init.d/DmServicedmrc)
Finished to create the service (DmServicedmrc)

[root@dmks root]# service DmServicedmrc start
Starting DmServicedmrc: [ OK ]

[dmdba@dmks ~]$ disql SYSDBA/SYSDBA@localhost:5336

Server[localhost:5336]:mode is normal, state is open
login used time: 10.727(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT
Connected to: DM 7.1.6.46
SQL> select permanent_magic;

LINEID     PERMANENT_MAGIC
---------- ---------------
1          -1238913195

used time: 0.793(ms). Execute id is 809.
SQL> select db_magic from v$rlog;

LINEID     DB_MAGIC
---------- -----------
1          1319824724

used time: 0.984(ms). Execute id is 810.

3)将备份集和归档日志文件复制到目标库所在主机上

[dmdba@dmrac1 backup]$ scp -r db_rac_full_bak_recover_dbmagic dmdba@10.10.10.187:/dm_home/dmdbms/backup/
The authenticity of host ‘10.10.10.187 (10.10.10.187)’ can’t be established.
RSA key fingerprint is 34:9a:d7:67:6d:be:20:45:21:ce:96:82:23:e5:e9:11.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘10.10.10.187’ (RSA) to the list of known hosts.
dmdba@10.10.10.187’s password:
db_rac_full_bak_recover_dbmagic.meta 100% 73KB 72.5KB/s 00:00
db_rac_full_bak_recover_dbmagic.bak 100% 25MB 25.5MB/s 00:00

[dmdba@dmrac1 data]$ scp -r local_arch dmdba@10.10.10.187:/dm_home/dmdbms/backup/
dmdba@10.10.10.187’s password:
ARCHIVE_LOCAL1_20200528171255857_0.log 100% 157KB 156.5KB/s 00:00
[dmdba@dmrac1 data]$ scp -r rac0_arch dmdba@10.10.10.187:/dm_home/dmdbms/backup/
dmdba@10.10.10.187’s password:
ARCHIVE_LOCAL1_20200615164953273_0.log 100% 160KB 160.0KB/s 00:00
ARCHIVE_LOCAL1_20200615165648166_0.log 100% 157KB 157.0KB/s 00:00
ARCHIVE_LOCAL1_20200528202150715_0.log 100% 277KB 277.0KB/s 00:00
ARCHIVE_LOCAL1_20200608151836879_0.log 100% 158KB 157.5KB/s 00:00
ARCHIVE_LOCAL1_20200609150852829_0.log 100% 157KB 157.0KB/s 00:00
ARCHIVE_LOCAL1_20200608152638617_0.log 100% 159KB 158.5KB/s 00:00
ARCHIVE_LOCAL1_20200609170732487_0.log 100% 161KB 160.5KB/s 00:00
ARCHIVE_LOCAL1_20200615172117341_0.log 100% 159KB 158.5KB/s 00:00
ARCHIVE_LOCAL1_20200615171042444_0.log 100% 159KB 159.0KB/s 00:00
ARCHIVE_LOCAL1_20200605154214367_0.log 100% 157KB 157.0KB/s 00:00
[dmdba@dmrac1 data]$ scp -r rac1_arch dmdba@10.10.10.187:/dm_home/dmdbms/backup/
dmdba@10.10.10.187’s password:
ARCH_REMOTE1_20200608152641970_1.log 100% 158KB 158.0KB/s 00:00
ARCH_REMOTE1_20200609170737070_1.log 100% 159KB 159.0KB/s 00:00
ARCH_REMOTE1_20200609150857056_1.log 100% 158KB 158.0KB/s 00:00
ARCH_REMOTE1_20200615172121038_1.log 100% 158KB 158.0KB/s 00:00
ARCH_REMOTE1_20200608151838201_1.log 100% 5632 5.5KB/s 00:00
ARCH_REMOTE1_20200528202146001_1.log 100% 158KB 158.0KB/s 00:00
ARCH_REMOTE1_20200615164957743_1.log 100% 158KB 158.0KB/s 00:00
ARCH_REMOTE1_20200605154211189_1.log 100% 159KB 159.0KB/s 00:00
ARCH_REMOTE1_20200615165652504_1.log 100% 158KB 158.0KB/s 00:00
ARCH_REMOTE1_20200615171046600_1.log 100% 158KB 158.0KB/s 00:00
[dmdba@dmrac1 data]$

4)检验备份

RMAN> check backupset '/dm_home/dmdbms/backup/db_rac_full_bak_recover_dbmagic';
check backupset '/dm_home/dmdbms/backup/db_rac_full_bak_recover_dbmagic';
CMD END.CODE:[0]
check backupset successfully.
time used: 5.877(ms)

5)还原数据库

RMAN> restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_rac_full_bak_recover_dbmagic';
restore database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' from backupset '/dm_home/dmdbms/backup/db_rac_full_bak_recover_dbmagic';
file dm.key not found, use default license!
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info.
RESTORE DATABASE  CHECK......
RESTORE DATABASE , dbf collect......
RESTORE DATABASE , dbf refresh ......
RESTORE BACKUPSET [/dm_home/dmdbms/backup/db_rac_full_bak_recover_dbmagic] START......
total 5 packages processed...
RESTORE DATABASE , UPDATE ctl file......
RESTORE DATABASE , REBUILD key file......
RESTORE DATABASE , CHECK db info......
RESTORE DATABASE , UPDATE db info......
total 7 packages processed!
CMD END.CODE:[0]
restore successfully.
time used: 16226.253(ms)

6)恢复数据库

RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini'  update db_magic ;
recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' update db_magic;
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info.
EP[0] max_lsn: 76986
[-8308]:forbid updating database magic, need redo log

这里使用update db_magic选项出错了,提示需要重做日志文件。下面去掉update db_magic选项,因为每次还原恢复时也是一定会更新db_magic值。

RMAN> recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch','/dm_home/dmdbms/backup/rac1_arch';
recover database '/dm_home/dmdbms/data/dameng_for_recover/dm.ini' with archivedir '/dm_home/dmdbms/backup/rac0_arch' , '/dm_home/dmdbms/backup/rac1_arch';
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[4].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[3].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[2].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[1].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running...[0].
checking if the database under system path [/dm_home/dmdbms/data/dameng_for_recover] is running, write dmrman info.
EP[0] max_lsn: 76986
min_ckpt_lsn =
The RAC recover total redo 0 ptx
EP(0) slot ctl page(1, 0, 16) trxid = 9986
EP(1) slot ctl page(1, 0, 17) trxid = 8457
EP[0] Recover LSN from 76987 to 77001.
EP[1] Recover LSN from 77002 to 77001.
Recover from archive log finished, time used:0.006s.
recover successfully!
time used: 7027.075(ms)

7)检查数据,查询表t1的数据可以验证数据恢复成功

[dmdba@dmks ~]$ disql SYSDBA/SYSDBA@localhost:5336

Server[localhost:5336]:mode is normal, state is open
login used time: 10.727(ms)
disql V7.1.6.46-Build(2018.02.08-89107)ENT
Connected to: DM 7.1.6.46
SQL> desc t1;

LINEID     NAME TYPE$       NULLABLE
---------- ---- ----------- --------
1          ID   INTEGER     Y
2          NAME VARCHAR(20) Y

used time: 20.311(ms). Execute id is 3.
SQL> select * from t1;

LINEID     ID          NAME
---------- ----------- ----
1          1           jy
2          2           hy

used time: 1.138(ms). Execute id is 4.

8)检查db_magic,还原恢复后db_magic从-1632052820变成了1009602608,

SQL> select permanent_magic;

LINEID     PERMANENT_MAGIC
---------- ---------------
1          450923536

used time: 1.042(ms). Execute id is 5.
SQL> select db_magic from v$rlog;

LINEID     DB_MAGIC
---------- -----------
1          1009602608

used time: 1.332(ms). Execute id is 6.
Proudly powered by WordPress | Indrajeet by Sus Hill.