Skip to content

Oracle - 2. page

1错误1:ocrconfig.bin” does not exists or is not readable
1.1 现象
19c的RAC 环境,升级RU 从19.3 到 19.6,根据19.6 RU readme文档的操作,opatchauto的时候,报错:

[root@luda software]# export PATH=$PATH:/u01/app/19.3.0/grid/OPatch
[root@luda software]# opatchauto apply /u01/software/30501910

OPatchauto session is initiated at Thu Jan 29 21:18:26 2020

System initialization log file is /u01/app/19.3.0/grid/cfgtoollogs/opatchautodb/systemconfig2020-03-12_09-18-36PM.log.

Session log file is /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/opatchauto2020-03-12_09-21-01PM.log
The id for this session is 5Z4B

Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.3.0/grid

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.3.0/db_1
Patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/db_1

Patch applicability verified successfully on home /u01/app/19.3.0/grid

Verifying SQL patch applicability on home /u01/app/oracle/product/19.3.0/db_1
SQL patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/db_1

Preparing to bring down database service on home /u01/app/oracle/product/19.3.0/db_1
Successfully prepared home /u01/app/oracle/product/19.3.0/db_1 to bring down database service

Bringing down CRS service on home /u01/app/19.3.0/grid
CRS service brought down successfully on home /u01/app/19.3.0/grid

Performing prepatch operation on home /u01/app/oracle/product/19.3.0/db_1
Perpatch operation completed successfully on home /u01/app/oracle/product/19.3.0/db_1

Start applying binary patch on home /u01/app/oracle/product/19.3.0/db_1
Binary patch applied successfully on home /u01/app/oracle/product/19.3.0/db_1

Performing postpatch operation on home /u01/app/oracle/product/19.3.0/db_1
Postpatch operation completed successfully on home /u01/app/oracle/product/19.3.0/db_1

Start applying binary patch on home /u01/app/19.3.0/grid
Failed while applying binary patches on home /u01/app/19.3.0/grid

Execution of [OPatchAutoBinaryAction] patch action failed, check log for more details. Failures:
Patch Target : rac1->/u01/app/19.3.0/grid Type[crs]
Details: [
—————————Patching Failed———————————
Command execution failed during patching in home: /u01/app/19.3.0/grid, host: rac1.
Command failed: /u01/app/19.3.0/grid/OPatch/opatchauto apply /u01/software/30501910 -oh /u01/app/19.3.0/grid -target_type cluster -binary -invPtrLoc /u01/app/19.3.0/grid/oraInst.loc -jre /u01/app/19.3.0/grid/OPatch/jre -persistresult /u01/app/19.3.0/grid/OPatch/auto/dbsessioninfo/sessionresult_rac1_crs.ser -analyzedresult /u01/app/19.3.0/grid/OPatch/auto/dbsessioninfo/sessionresult_analyze_rac1_crs.ser
Command failure output:
==Following patches FAILED in apply:

Patch: /u01/software/30501910/30489227
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-03-12_21-45-54PM_1.log
Reason: Failed during Analysis: CheckNApplyReport Failed, [ Prerequisite Status: FAILED, Prerequisite output:
The details are:

Prerequisite check “CheckApplicable” failed.]

After fixing the cause of failure Run opatchauto resume

]
OPATCHAUTO-68061: The orchestration engine failed.
OPATCHAUTO-68061: The orchestration engine failed with return code 1
OPATCHAUTO-68061: Check the log for more details.
OPatchAuto failed.

OPatchauto session completed at Thu Jan 29 21:46:04 2020
Time taken to complete the session 27 minutes, 39 seconds

opatchauto failed with error code 42
[root@luda software]#
查看错误日志:

[Jan 29, 2020 10:36:54 PM] [INFO] Prereq checkPatchApplicableOnCurrentPlatform Passed for patch : 30489227
[Jan 29, 2020 10:36:57 PM] [WARNING]Action file /u01/app/19.3.0/grid/jlib/srvmasm.jar is in the jar list,OOP should be lanched
[Jan 29, 2020 10:36:58 PM] [INFO] Patch 30489227:
Copy Action: Source File “/u01/software/30501910/30489227/files/bin/ocrcheck.bin” does not exists or i
s not readable
‘oracle.has.crs, 19.0.0.0.0’: Cannot copy file from ‘ocrcheck.bin’ to ‘/u01/app/19.3.0/grid/bin/ocrche
ck.bin’
Copy Action: Source File “/u01/software/30501910/30489227/files/bin/ocrconfig.bin” does not exists or
is not readable
‘oracle.has.crs, 19.0.0.0.0’: Cannot copy file from ‘ocrconfig.bin’ to ‘/u01/app/19.3.0/grid/bin/ocrco
nfig.bin’
[Jan 29, 2020 10:36:58 PM] [INFO] Prerequisite check “CheckApplicable” failed.
The details are:

Patch 30489227:
Copy Action: Source File “/u01/software/30501910/30489227/files/bin/ocrcheck.bin” does not exists or i
s not readable
‘oracle.has.crs, 19.0.0.0.0’: Cannot copy file from ‘ocrcheck.bin’ to ‘/u01/app/19.3.0/grid/bin/ocrche
ck.bin’
Copy Action: Source File “/u01/software/30501910/30489227/files/bin/ocrconfig.bin” does not exists or
is not readable
‘oracle.has.crs, 19.0.0.0.0’: Cannot copy file from ‘ocrconfig.bin’ to ‘/u01/app/19.3.0/grid/bin/ocrco
nfig.bin’
[Jan 29, 2020 10:36:58 PM] [SEVERE] OUI-67073:UtilSession failed:
Prerequisite check “CheckApplicable” failed.
[Jan 29, 2020 10:36:58 PM] [INFO] Finishing UtilSession at Thu Jan 29 22:36:58 CST 2020
[Jan 29, 2020 10:36:58 PM] [INFO] Log file location: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-03-12_22-36-43PM
_1.log
[Jan 29, 2020 10:36:58 PM] [INFO] Stack Description: java.lang.RuntimeException:
Prerequisite check “CheckApplicable” failed.
at oracle.opatch.OPatchSessionHelper.runApplyPrereqs(OPatchSessionHelper.java:6548)
at oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:1002)
at oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:370)
at oracle.opatch.opatchutil.NApply.process(NApply.java:352)
at oracle.opatch.opatchutil.OUSession.napply(OUSession.java:1123)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at oracle.opatch.UtilSession.process(UtilSession.java:355)
at oracle.opatch.OPatchSession.main(OPatchSession.java:3985)
at oracle.opatch.OPatchSDK.NApply(OPatchSDK.java:1127)
at oracle.opatch.opatchsdk.OPatchTarget.NApplyReport(OPatchTarget.java:3964)
at oracle.opatch.opatchsdk.OPatchTarget.NApplyReportForAllPrereqs(OPatchTarget.java:4013)
at oracle.opatchauto.core.binary.action.AnalyzeReportGenerator.analyzePatch(AnalyzeReportGenerator
.java:186)
at oracle.opatchauto.core.binary.action.AnalyzeReportGenerator.execute(AnalyzeReportGenerator.java
:148)
at oracle.opatchauto.core.binary.action.LegacyPatchAction.execute(LegacyPatchAction.java:46)
at oracle.opatchauto.core.binary.OPatchAutoBinary.patchWithoutAnalyze(OPatchAutoBinary.java:519)
at oracle.opatchauto.core.binary.OPatchAutoBinary.applyWithoutAnalyze(OPatchAutoBinary.java:406)
at oracle.opatchauto.core.OPatchAutoCore.runOPatchAutoBinary(OPatchAutoCore.java:192)
at oracle.opatchauto.core.OPatchAutoCore.main(OPatchAutoCore.java:75)
Caused by: java.lang.RuntimeException:
Prerequisite check “CheckApplicable” failed.
… 21 more
Caused by: oracle.opatch.PrereqFailedException:
Prerequisite check “CheckApplicable” failed.
… 21 more
[Jan 29, 2020 10:36:58 PM] [INFO] EXITING METHOD: NApplyReport(OPatchPatch[] patches,OPatchNApplyOptions options)
[grid@luda OPatch]$
1.2 解决方法
这里的错误是:
“/u01/software/30501910/30489227/files/bin/ocrconfig.bin” does not exists or is not readable
从这里看像是权限问题,手工修改RU 补丁的权限:

[root@luda software]# chown grid:oinstall 30501910 -R
再次执行变成了另外的错误。

2 错误2:Copy failed from ‘…/files/bin/cssdagent’ to ‘…/bin/cssdagent’…
2.1 现象
再次执行,还是报错。如下:

2020-03-13 08:36:48,374 INFO [313] com.oracle.glcm.patch.auto.db.integration.controller.action.OPatchAutoBinaryAction – Opatchcore binary error message=
2020-03-13 08:36:48,375 INFO [313] com.oracle.glcm.patch.auto.db.integration.controller.action.OPatchAutoBinaryAction – Reading session result from /u01/app/19.3.0/grid/opatchautocfg/db/sessioninfo/sessionresult_rac1_crs.ser
2020-03-13 08:36:48,380 INFO [313] com.oracle.cie.common.util.reporting.CommonReporter – Reporting console output : Message{id=’null’, message=’Failed while applying binary patches on home /u01/app/19.3.0/grid
‘}
2020-03-13 08:36:48,381 SEVERE [311] com.oracle.glcm.patch.auto.action.PatchActionExecutor – Failed to execute patch action [com.oracle.glcm.patch.auto.db.integration.controller.action.OPatchAutoBinaryAction] on patch target [rac1->/u01/app/19.3.0/grid Type[crs]].
2020-03-13 08:36:48,382 INFO [311] com.oracle.cie.common.util.reporting.CommonReporter – Reporting console output : Message{id=’null’, message=’Execution of [OPatchAutoBinaryAction] patch action failed, check log for more details. Failures:
Patch Target : rac1->/u01/app/19.3.0/grid Type[crs]
Details: [
—————————Patching Failed———————————
Command execution failed during patching in home: /u01/app/19.3.0/grid, host: rac1.
Command failed: /u01/app/19.3.0/grid/OPatch/opatchauto apply /u01/software/30501910 -oh /u01/app/19.3.0/grid -target_type cluster -binary -invPtrLoc /u01/app/19.3.0/grid/oraInst.loc -jre /u01/app/19.3.0/grid/OPatch/jre -persistresult /u01/app/19.3.0/grid/opatchautocfg/db/sessioninfo/sessionresult_rac1_crs.ser -analyzedresult /u01/app/19.3.0/grid/opatchautocfg/db/sessioninfo/sessionresult_analyze_rac1_crs.ser
Command failure output:
==Following patches FAILED in apply:

Patch: /u01/software/30501910/30489227
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-03-13_08-31-51AM_1.log
Reason: Failed during Patching: oracle.opatch.opatchsdk.OPatchException: ApplySession failed in system modification phase… ‘ApplySession::apply failed: Copy failed from ‘/u01/software/30501910/30489227/files/bin/crsd.bin’ to ‘/u01/app/19.3.0/grid/bin/crsd.bin’…
Copy failed from ‘/u01/software/30501910/30489227/files/bin/cssdagent’ to ‘/u01/app/19.3.0/grid/bin/cssdagent’…
Copy failed from ‘/u01/software/30501910/30489227/files/bin/cssdmonitor’ to ‘/u01/app/19.3.0/grid/bin/cssdmonitor’…
Copy fa …

After fixing the cause of failure Run opatchauto resume

]’}
2020-03-13 08:36:48,407 SEVERE [41] com.oracle.cie.wizard.internal.engine.WizardControllerEngine – Wizard error cause
com.oracle.cie.wizard.tasks.TaskExecutionException: OPATCHAUTO-68128: Patch action execution failed.
OPATCHAUTO-68128: Failed to execute patch actions for goal offline:binary-patching
OPATCHAUTO-68128: Check the log for more information.
at com.oracle.glcm.patch.auto.wizard.silent.tasks.PatchActionTask.execute(PatchActionTask.java:106)
at com.oracle.cie.wizard.internal.cont.SilentTaskContainer$TaskRunner.run(SilentTaskContainer.java:102)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.oracle.glcm.patch.auto.OPatchAutoException: OPATCHAUTO-68067: Patch action execution failed.
OPATCHAUTO-68067: Failed to execute patch action [com.oracle.glcm.patch.auto.db.integration.controller.action.OPatchAutoBinaryAction
Patch Target : rac1->/u01/app/19.3.0/grid Type[crs]
Details: [
—————————Patching Failed———————————
Command execution failed during patching in home: /u01/app/19.3.0/grid, host: rac1.
Command failed: /u01/app/19.3.0/grid/OPatch/opatchauto apply /u01/software/30501910 -oh /u01/app/19.3.0/grid -target_type cluster -binary -invPtrLoc /u01/app/19.3.0/grid/oraInst.loc -jre /u01/app/19.3.0/grid/OPatch/jre -persistresult /u01/app/19.3.0/grid/opatchautocfg/db/sessioninfo/sessionresult_rac1_crs.ser -analyzedresult /u01/app/19.3.0/grid/opatchautocfg/db/sessioninfo/sessionresult_analyze_rac1_crs.ser
Command failure output:
==Following patches FAILED in apply:

Patch: /u01/software/30501910/30489227
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-03-13_08-31-51AM_1.log
Reason: Failed during Patching: oracle.opatch.opatchsdk.OPatchException: ApplySession failed in system modification phase… ‘ApplySession::apply failed: Copy failed from ‘/u01/software/30501910/30489227/files/bin/crsd.bin’ to ‘/u01/app/19.3.0/grid/bin/crsd.bin’…
Copy failed from ‘/u01/software/30501910/30489227/files/bin/cssdagent’ to ‘/u01/app/19.3.0/grid/bin/cssdagent’…
Copy failed from ‘/u01/software/30501910/30489227/files/bin/cssdmonitor’ to ‘/u01/app/19.3.0/grid/bin/cssdmonitor’…
Copy fa …

After fixing the cause of failure Run opatchauto resume

]]. Failures:
OPATCHAUTO-68067: Check the details to determine the cause of the failure.
at com.oracle.glcm.patch.auto.action.PatchActionExecutor.execute(PatchActionExecutor.java:172)
at com.oracle.glcm.patch.auto.wizard.silent.tasks.PatchActionTask.execute(PatchActionTask.java:102)
… 2 more
2020-03-13 08:36:48,516 INFO [1] com.oracle.glcm.patch.auto.db.integration.model.productsupport.DBBaseProductSupport – Space available after session: 44935 MB
2020-03-13 08:36:49,875 SEVERE [1] com.oracle.glcm.patch.auto.OPatchAuto – OPatchAuto failed.
com.oracle.glcm.patch.auto.OPatchAutoException: OPATCHAUTO-68061: The orchestration engine failed.
OPATCHAUTO-68061: The orchestration engine failed with return code 1
OPATCHAUTO-68061: Check the log for more details.
at com.oracle.glcm.patch.auto.OrchestrationEngineImpl.orchestrate(OrchestrationEngineImpl.java:40)
at com.oracle.glcm.patch.auto.OPatchAuto.orchestrate(OPatchAuto.java:858)
at com.oracle.glcm.patch.auto.OPatchAuto.orchestrate(OPatchAuto.java:398)
at com.oracle.glcm.patch.auto.OPatchAuto.orchestrate(OPatchAuto.java:344)
at com.oracle.glcm.patch.auto.OPatchAuto.main(OPatchAuto.java:212)
2020-03-13 08:36:49,875 INFO [1] com.oracle.cie.common.util.reporting.CommonReporter – Reporting console output : Message{id=’null’, message=’OPATCHAUTO-68061: The orchestration engine failed.
OPATCHAUTO-68061: The orchestration engine failed with return code 1
OPATCHAUTO-68061: Check the log for more details.’}
2020-03-13 08:36:49,875 INFO [1] com.oracle.cie.common.util.reporting.CommonReporter – Reporting console output : Message{id=’null’, message=’OPatchAuto failed.’}
这次错误变了:

Copy failed from ‘/u01/software/30501910/30489227/files/bin/cssdagent’ to ‘/u01/app/19.3.0/grid/bin/cssdagent’…

2.2 解决方法
上MOS搜了一下,有一篇文章和这个错误很类似:
opatch report “ERROR: Prereq checkApplicable failed.” when Applying Grid Infrastructure patch (Doc ID 1417268.1)

MOS里将了多种原因可能会触发这个错误,和我这里最像的是这一条:

D. The patch is not unzipped as grid user, often it is unzipped as root user
ls -l will show the files are owned by root user.
The solution is to unzip the patch as grid user into an empty directory outside of GRID_HOME, then retry the patch apply.
我的RU patch 虽然不在GRID_HOME目录下,但也是用root用户解压缩的。 删除原来的解压缩后,重新用grid用户解压缩一次,在打补丁,还是同样的错误:
[grid@luda tmp]$ unzip -d /tmp p30501910_190000_Linux-x86-64-GI.zip

[Mar 13, 2020 9:18:08 AM] [INFO] Copying file to “/u01/app/19.3.0/grid/srvm/lib/sprraw.o”
[Mar 13, 2020 9:18:08 AM] [INFO] The following actions have failed:
[Mar 13, 2020 9:18:08 AM] [WARNING] OUI-67124:Copy failed from ‘/tmp/30501910/30489227/files/bin/crsd.bin’ to ‘/u01/app/19.3.0/grid/bin/crsd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/cssdagent’ to ‘/u01/app/19.3.0/grid/bin/cssdagent’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/cssdmonitor’ to ‘/u01/app/19.3.0/grid/bin/cssdmonitor’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/evmd.bin’ to ‘/u01/app/19.3.0/grid/bin/evmd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/evmlogger.bin’ to ‘/u01/app/19.3.0/grid/bin/evmlogger.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/gipcd.bin’ to ‘/u01/app/19.3.0/grid/bin/gipcd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/gpnpd.bin’ to ‘/u01/app/19.3.0/grid/bin/gpnpd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/mdnsd.bin’ to ‘/u01/app/19.3.0/grid/bin/mdnsd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/ocssd.bin’ to ‘/u01/app/19.3.0/grid/bin/ocssd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/octssd.bin’ to ‘/u01/app/19.3.0/grid/bin/octssd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/ohasd.bin’ to ‘/u01/app/19.3.0/grid/bin/ohasd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/oraagent.bin’ to ‘/u01/app/19.3.0/grid/bin/oraagent.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/orarootagent.bin’ to ‘/u01/app/19.3.0/grid/bin/orarootagent.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/osysmond.bin’ to ‘/u01/app/19.3.0/grid/bin/osysmond.bin’…
[Mar 13, 2020 9:18:08 AM] [INFO] Do you want to proceed? [y|n]
[Mar 13, 2020 9:18:08 AM] [INFO] N (auto-answered by -silent)
[Mar 13, 2020 9:18:08 AM] [INFO] User Responded with: N
[Mar 13, 2020 9:18:08 AM] [WARNING] OUI-67124:ApplySession failed in system modification phase… ‘ApplySession::apply failed: Copy failed from ‘/tmp/30501910/30489227/f
iles/bin/crsd.bin’ to ‘/u01/app/19.3.0/grid/bin/crsd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/cssdagent’ to ‘/u01/app/19.3.0/grid/bin/cssdagent’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/cssdmonitor’ to ‘/u01/app/19.3.0/grid/bin/cssdmonitor’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/evmd.bin’ to ‘/u01/app/19.3.0/grid/bin/evmd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/evmlogger.bin’ to ‘/u01/app/19.3.0/grid/bin/evmlogger.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/gipcd.bin’ to ‘/u01/app/19.3.0/grid/bin/gipcd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/gpnpd.bin’ to ‘/u01/app/19.3.0/grid/bin/gpnpd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/mdnsd.bin’ to ‘/u01/app/19.3.0/grid/bin/mdnsd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/ocssd.bin’ to ‘/u01/app/19.3.0/grid/bin/ocssd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/octssd.bin’ to ‘/u01/app/19.3.0/grid/bin/octssd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/ohasd.bin’ to ‘/u01/app/19.3.0/grid/bin/ohasd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/oraagent.bin’ to ‘/u01/app/19.3.0/grid/bin/oraagent.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/orarootagent.bin’ to ‘/u01/app/19.3.0/grid/bin/orarootagent.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/osysmond.bin’ to ‘/u01/app/19.3.0/grid/bin/osysmond.bin’…

[Mar 13, 2020 9:18:08 AM] [INFO] Restoring “/u01/app/19.3.0/grid” to the state prior to running NApply…
[Mar 13, 2020 9:18:08 AM] [INFO] Restoring files: copy recurse from /u01/app/19.3.0/grid/.patch_storage/NApply/2020-03-13_09-15-07AM/backup to /u01/app/19.3.0/grid

#### Stack trace of processes holding locks ####

Time: 2020-03-13_09-14-13AM
Command: oracle/opatchauto/core/OPatchAutoCore apply /tmp/30501910 -oh /u01/app/19.3.0/grid -target_type cluster -binary -invPtrLoc /u01/app/19.3.0/grid/oraInst.loc -per
sistresult /u01/app/19.3.0/grid/opatchautocfg/db/sessioninfo/sessionresult_rac1_crs.ser -analyzedresult /u01/app/19.3.0/grid/opatchautocfg/db/sessioninfo/sessionresult_a
nalyze_rac1_crs.ser -customLogDir /u01/app/19.3.0/grid/cfgtoollogs
Lock File Name: /u01/app/oraInventory/locks/_u01_app_19.3.0_grid_writer.lock
StackTrace:
———–
java.lang.Throwable
at oracle.sysman.oii.oiit.OiitLockHeartbeat.writeStackTrace(OiitLockHeartbeat.java:193)
at oracle.sysman.oii.oiit.OiitLockHeartbeat.(OiitLockHeartbeat.java:173)
at oracle.sysman.oii.oiit.OiitTargetLocker.getWriterLock(OiitTargetLocker.java:346)
at oracle.sysman.oii.oiit.OiitTargetLocker.getWriterLock(OiitTargetLocker.java:238)
at oracle.sysman.oii.oiic.OiicStandardInventorySession.acquireLocks(OiicStandardInventorySession.java:564)
at oracle.sysman.oii.oiic.OiicStandardInventorySession.initAreaControl(OiicStandardInventorySession.java:359)
at oracle.sysman.oii.oiic.OiicStandardInventorySession.initSession(OiicStandardInventorySession.java:332)
at oracle.sysman.oii.oiic.OiicStandardInventorySession.initSession(OiicStandardInventorySession.java:294)
at oracle.sysman.oii.oiic.OiicStandardInventorySession.initSession(OiicStandardInventorySession.java:243)
at oracle.sysman.oui.patch.impl.HomeOperationsImpl.initialize(HomeOperationsImpl.java:107)
at oracle.glcm.opatch.common.api.install.HomeOperationsShell.initialize(HomeOperationsShell.java:117)
at oracle.opatch.ipm.IPMRWServices.addPatchCUP(IPMRWServices.java:134)
at oracle.opatch.ipm.IPMRWServices.add(IPMRWServices.java:146)
at oracle.opatch.ApplySession.apply(ApplySession.java:899)
at oracle.opatch.ApplySession.processLocal(ApplySession.java:4098)
at oracle.opatch.ApplySession.process(ApplySession.java:5080)
at oracle.opatch.ApplySession.process(ApplySession.java:4942)
at oracle.opatch.OPatchACL.processApply(OPatchACL.java:310)
at oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:1429)
at oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:370)
at oracle.opatch.opatchutil.NApply.process(NApply.java:352)
at oracle.opatch.opatchutil.OUSession.napply(OUSession.java:1123)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at oracle.opatch.UtilSession.process(UtilSession.java:355)
at oracle.opatch.OPatchSession.main(OPatchSession.java:3985)
at oracle.opatch.OPatchSDK.NApply(OPatchSDK.java:1127)
at oracle.opatch.opatchsdk.OPatchTarget.NApply(OPatchTarget.java:4169)
at oracle.opatchauto.core.binary.action.LegacyPatchAction.execute(LegacyPatchAction.java:76)
at oracle.opatchauto.core.binary.OPatchAutoBinary.patchWithoutAnalyze(OPatchAutoBinary.java:519)
at oracle.opatchauto.core.binary.OPatchAutoBinary.applyWithoutAnalyze(OPatchAutoBinary.java:406)
at oracle.opatchauto.core.OPatchAutoCore.runOPatchAutoBinary(OPatchAutoCore.java:192)
at oracle.opatchauto.core.OPatchAutoCore.main(OPatchAutoCore.java:75)
继续研究,有可能是bug:
Bug 13575478 : PATCH APLLICABLE/CONFLICT CHECK FAILED WITH ‘OPATCH AUTO’
A. Expected behaviour if GRID_HOME has not been unlocked

If GI home has not been unlocked with “rootcrs.pl -unlock”, checkapplicable will fail as many files are still owned by root user, this is expected behaviour. The solution is to use “opatch auto” or follow the patch readme step-by-step so the GI home gets unlocked first.
这里的方法和opatchauto有出入,所以尝试进行了analyze,提示有patch 已经打上了,但是通过opatch lspatches 并没有显示,所以对19.6 的RU执行了rollback。

[root@luda ~]# /u01/app/19.3.0/grid/OPatch/opatchauto apply /tmp/30501910 -analyze
[root@luda ~]# /u01/app/19.3.0/grid/OPatch/opatchauto rollback /tmp/30501910
再次执行,变成了另外错误3.

3 错误3:”CheckActiveFilesAndExecutables”
3.1 现象
这里的错误提示非常明显:

Following active executables are used by opatch process :
/u01/app/oracle/product/19.3.0/db_1/lib/libclntsh.so.19.1
/u01/app/oracle/product/19.3.0/db_1/lib/libsqlplus.so
[Mar 13, 2020 3:13:26 PM] [INFO] Prerequisite check “CheckActiveFilesAndExecutables” failed.
The details are:

Following active executables are not used by opatch process :

Following active executables are used by opatch process :
/u01/app/oracle/product/19.3.0/db_1/lib/libclntsh.so.19.1
/u01/app/oracle/product/19.3.0/db_1/lib/libsqlplus.so
[Mar 13, 2020 3:13:26 PM] [SEVERE] OUI-67073:UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.
[Mar 13, 2020 3:13:26 PM] [INFO] Finishing UtilSession at Fri Mar 13 15:13:26 CST 2020
[Mar 13, 2020 3:13:26 PM] [INFO] Log file location: /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2020-03-13_15-09-58PM_1.log
[root@luda ~]#
3.2 解决方法
有活动的sqlplus 窗口存在。 先执行rollback:
[root@luda ~]# /u01/app/19.3.0/grid/OPatch/opatchauto rollback /tmp/30501910

OPatchauto session is initiated at Fri Mar 13 15:18:58 2020

System initialization log file is /u01/app/19.3.0/grid/cfgtoollogs/opatchautodb/systemconfig2020-03-13_03-19-01PM.log.

Session log file is /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/opatchauto2020-03-13_03-21-09PM.log
The id for this session is U2H9

Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.3.0/grid

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.3.0/db_1
Patch applicability verified successfully on home /u01/app/19.3.0/grid

Patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/db_1

Verifying SQL patch applicability on home /u01/app/oracle/product/19.3.0/db_1
SQL patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/db_1

Preparing to bring down database service on home /u01/app/oracle/product/19.3.0/db_1
Successfully prepared home /u01/app/oracle/product/19.3.0/db_1 to bring down database service

Performing prepatch operation on home /u01/app/oracle/product/19.3.0/db_1
Perpatch operation completed successfully on home /u01/app/oracle/product/19.3.0/db_1

Start rolling back binary patch on home /u01/app/oracle/product/19.3.0/db_1
Binary patch rolled back successfully on home /u01/app/oracle/product/19.3.0/db_1

Performing postpatch operation on home /u01/app/oracle/product/19.3.0/db_1
Postpatch operation completed successfully on home /u01/app/oracle/product/19.3.0/db_1

Preparing home /u01/app/oracle/product/19.3.0/db_1 after database service restarted
No step execution required………

Trying to roll back SQL patch on home /u01/app/oracle/product/19.3.0/db_1
SQL patch rolled back successfully on home /u01/app/oracle/product/19.3.0/db_1

OPatchAuto successful.

——————————–Summary——————————–

Patching is completed successfully. Please find the summary as follows:

Host:rac1
CRS Home:/u01/app/19.3.0/grid
Version:19.0.0.0.0
Summary:

==Following patches were SKIPPED:

Patch: /tmp/30501910/30489227
Reason: This Patch does not exist in the home, it cannot be rolled back.

Patch: /tmp/30501910/30489632
Reason: This Patch does not exist in the home, it cannot be rolled back.

Patch: /tmp/30501910/30655595
Reason: This Patch does not exist in the home, it cannot be rolled back.

Patch: /tmp/30501910/30557433
Reason: This Patch does not exist in the home, it cannot be rolled back.

Host:rac1
RAC Home:/u01/app/oracle/product/19.3.0/db_1
Version:19.0.0.0.0
Summary:

==Following patches were SKIPPED:

Patch: /tmp/30501910/30489632
Reason: This patch is not applicable to this specified target type – “rac_database”

Patch: /tmp/30501910/30655595
Reason: This patch is not applicable to this specified target type – “rac_database”

Patch: /tmp/30501910/30557433
Reason: Patch /tmp/30501910/30557433 is not applied as part of bundle patch 30501910

==Following patches were SUCCESSFULLY rolled back:

Patch: /tmp/30501910/30489227
Log: /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatchauto/core/opatch/opatch2020-03-13_15-23-36PM_1.log

Patching session reported following warning(s):
_________________________________________________

[WARNING] The database instance ‘cndba1’ from ‘/u01/app/oracle/product/19.3.0/db_1′, in host’rac1’ is not running. SQL changes, if any, will not be rolled back.
To roll back. the SQL changes, bring up the database instance and run the command manually from any one node (run as oracle).
Refer to the readme to get the correct steps for applying the sql changes.

OPatchauto session completed at Fri Mar 13 15:24:58 2020
Time taken to complete the session 6 minutes, 1 second
[root@luda ~]#
再次apply,依旧报错。

4错误4:OUI-67073:UtilSession failed: ApplySession failed in system modification phase(这种解决方法不要尝试)
4.1 现象
这里的错误和错误2是一致的。 其实这个错误才是最根本的,影响19c 打RU的现象,只是MOS上没有找到合理的解释。 现在可行的方法就是使用 nonrolling的方式,分别对GI和DB 进行RU的升级。

————————————

[Mar 13, 2020 3:54:57 PM] [INFO] Removed patch “30489227” with UPI + “23305624” from OUI inventory memory..
[Mar 13, 2020 3:54:57 PM] [INFO] Stack Description: java.lang.RuntimeException: OUI session not initialized
at oracle.sysman.oui.patch.impl.HomeOperationsImpl.saveInventory(HomeOperationsImpl.java:372)
at oracle.glcm.opatch.common.api.install.HomeOperationsShell.saveInventory(HomeOperationsShell.java:204)
at oracle.opatch.ipm.IPMRWServices.saveInstallInventory(IPMRWServices.java:854)
at oracle.opatch.OPatchSession.restorePatchesInventory(OPatchSession.java:1434)
at oracle.opatch.MergedPatchObject.restoreOH(MergedPatchObject.java:1234)
at oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:1465)
at oracle.opatch.opatchutil.NApply.legacy_process(NApply.java:370)
at oracle.opatch.opatchutil.NApply.process(NApply.java:352)
at oracle.opatch.opatchutil.OUSession.napply(OUSession.java:1123)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at oracle.opatch.UtilSession.process(UtilSession.java:355)
at oracle.opatch.OPatchSession.main(OPatchSession.java:3985)
at oracle.opatch.OPatchSDK.NApply(OPatchSDK.java:1127)
at oracle.opatch.opatchsdk.OPatchTarget.NApply(OPatchTarget.java:4169)
at oracle.opatchauto.core.binary.action.LegacyPatchAction.execute(LegacyPatchAction.java:76)
at oracle.opatchauto.core.binary.OPatchAutoBinary.patchWithoutAnalyze(OPatchAutoBinary.java:519)
at oracle.opatchauto.core.binary.OPatchAutoBinary.applyWithoutAnalyze(OPatchAutoBinary.java:406)
at oracle.opatchauto.core.OPatchAutoCore.runOPatchAutoBinary(OPatchAutoCore.java:192)
at oracle.opatchauto.core.OPatchAutoCore.main(OPatchAutoCore.java:75)
[Mar 13, 2020 3:54:57 PM] [SEVERE] OUI-67115:OPatch failed to restore OH ‘/u01/app/19.3.0/grid’. Consult OPatch document to restore the home manually before proceeding.
[Mar 13, 2020 3:54:57 PM] [WARNING] OUI-67124:
NApply was not able to restore the home. Please invoke the following scripts:
– restore.[sh,bat]
– make.txt (Unix only)
to restore the ORACLE_HOME. They are located under
“/u01/app/19.3.0/grid/.patch_storage/NApply/2020-03-13_15-52-41PM”
[Mar 13, 2020 3:54:58 PM] [SEVERE] OUI-67073:UtilSession failed: ApplySession failed in system modification phase… ‘ApplySession::apply failed: Copy failed from ‘/tmp/30501910/30489227/files/bin/crsd.bin’ to ‘/u01/app/19.3.0/grid/bin/crsd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/cssdagent’ to ‘/u01/app/19.3.0/grid/bin/cssdagent’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/cssdmonitor’ to ‘/u01/app/19.3.0/grid/bin/cssdmonitor’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/evmd.bin’ to ‘/u01/app/19.3.0/grid/bin/evmd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/evmlogger.bin’ to ‘/u01/app/19.3.0/grid/bin/evmlogger.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/gipcd.bin’ to ‘/u01/app/19.3.0/grid/bin/gipcd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/gpnpd.bin’ to ‘/u01/app/19.3.0/grid/bin/gpnpd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/mdnsd.bin’ to ‘/u01/app/19.3.0/grid/bin/mdnsd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/ocssd.bin’ to ‘/u01/app/19.3.0/grid/bin/ocssd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/octssd.bin’ to ‘/u01/app/19.3.0/grid/bin/octssd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/ohasd.bin’ to ‘/u01/app/19.3.0/grid/bin/ohasd.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/oraagent.bin’ to ‘/u01/app/19.3.0/grid/bin/oraagent.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/orarootagent.bin’ to ‘/u01/app/19.3.0/grid/bin/orarootagent.bin’…
Copy failed from ‘/tmp/30501910/30489227/files/bin/osysmond.bin’ to ‘/u01/app/19.3.0/grid/bin/osysmond.bin’…
4.2 解决方法
按日志提示,执行脚本:

[root@luda 2020-03-13_15-52-41PM]# ls
backup make.txt patchlist.txt restore.sh
[root@luda 2020-03-13_15-52-41PM]# ./restore.sh
This script is going to restore the Oracle Home to the previous state.
It does not perform any of the following:
– Running init/pre/post scripts
– Oracle binary re-link
– Customized steps performed manually by user
Please use this script with supervision from Oracle Technical Support.
About to modify Oracle Home( /u01/app/19.3.0/grid )
Do you want to proceed? [Y/N]
y
User responded with : Y
Restore script completed.
[root@luda 2020-03-13_15-52-41PM]#
4.3 分析过程1
因为之前不能copy文件都是crs的,所以在进行到RU 进行到停CRS时,我手工的执行了停CRS的操作:

[root@luda 2020-03-13_16-45-11PM]# crsctl stop crs -f
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘rac1’
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘rac1’
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on server ‘rac1’
CRS-2673: Attempting to stop ‘ora.chad’ on ‘rac1’
CRS-2673: Attempting to stop ‘ora.cndba.db’ on ‘rac1’
CRS-2677: Stop of ‘ora.cndba.db’ on ‘rac1’ succeeded
CRS-33673: Attempting to stop resource group ‘ora.asmgroup’ on server ‘rac1’
CRS-2673: Attempting to stop ‘ora.OCR.dg’ on ‘rac1’
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘rac1’
CRS-2673: Attempting to stop ‘ora.MGMT.dg’ on ‘rac1’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘rac1’
CRS-2677: Stop of ‘ora.DATA.dg’ on ‘rac1’ succeeded
CRS-2677: Stop of ‘ora.MGMT.dg’ on ‘rac1’ succeeded
CRS-2677: Stop of ‘ora.OCR.dg’ on ‘rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘rac1’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.rac1.vip’ on ‘rac1’
CRS-2677: Stop of ‘ora.rac1.vip’ on ‘rac1’ succeeded
CRS-2677: Stop of ‘ora.asm’ on ‘rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.ASMNET1LSNR_ASM.lsnr’ on ‘rac1’
CRS-2677: Stop of ‘ora.chad’ on ‘rac1’ succeeded
CRS-2677: Stop of ‘ora.ASMNET1LSNR_ASM.lsnr’ on ‘rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.asmnet1.asmnetwork’ on ‘rac1’
CRS-2677: Stop of ‘ora.asmnet1.asmnetwork’ on ‘rac1’ succeeded
CRS-33677: Stop of resource group ‘ora.asmgroup’ on server ‘rac1’ succeeded.
CRS-2672: Attempting to start ‘ora.rac1.vip’ on ‘rac2’
CRS-2676: Start of ‘ora.rac1.vip’ on ‘rac2’ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘rac1’
CRS-2677: Stop of ‘ora.ons’ on ‘rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘rac1’
CRS-2677: Stop of ‘ora.net1.network’ on ‘rac1’ succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘rac1’ has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.storage’ on ‘rac1’
CRS-2673: Attempting to stop ‘ora.crf’ on ‘rac1’
CRS-2673: Attempting to stop ‘ora.drivers.acfs’ on ‘rac1’
CRS-2673: Attempting to stop ‘ora.mdnsd’ on ‘rac1’
CRS-2677: Stop of ‘ora.crf’ on ‘rac1’ succeeded
CRS-2677: Stop of ‘ora.drivers.acfs’ on ‘rac1’ succeeded
CRS-2677: Stop of ‘ora.storage’ on ‘rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘rac1’
CRS-2677: Stop of ‘ora.mdnsd’ on ‘rac1’ succeeded
CRS-2677: Stop of ‘ora.asm’ on ‘rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘rac1’
CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘rac1’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘rac1’
CRS-2677: Stop of ‘ora.ctssd’ on ‘rac1’ succeeded
CRS-2677: Stop of ‘ora.evmd’ on ‘rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘rac1’
CRS-2677: Stop of ‘ora.cssd’ on ‘rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.driver.afd’ on ‘rac1’
CRS-2673: Attempting to stop ‘ora.gipcd’ on ‘rac1’
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘rac1’
CRS-2677: Stop of ‘ora.driver.afd’ on ‘rac1’ succeeded
CRS-2677: Stop of ‘ora.gpnpd’ on ‘rac1’ succeeded
CRS-2677: Stop of ‘ora.gipcd’ on ‘rac1’ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘rac1’ has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@luda 2020-03-13_16-45-11PM]#
4.4 停完CRS之后,RU打成功
[root@luda tmp]# /u01/app/19.3.0/grid/OPatch/opatchauto apply /tmp/30501910

OPatchauto session is initiated at Fri Mar 13 17:17:26 2020

System initialization log file is /u01/app/19.3.0/grid/cfgtoollogs/opatchautodb/systemconfig2020-03-13_05-17-50PM.log.

Session log file is /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/opatchauto2020-03-13_05-20-03PM.log
The id for this session is FPKL

Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.3.0/grid

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.3.0/db_1
Patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/db_1

Patch applicability verified successfully on home /u01/app/19.3.0/grid

Verifying SQL patch applicability on home /u01/app/oracle/product/19.3.0/db_1
SQL patch applicability verified successfully on home /u01/app/oracle/product/19.3.0/db_1

Preparing to bring down database service on home /u01/app/oracle/product/19.3.0/db_1
Successfully prepared home /u01/app/oracle/product/19.3.0/db_1 to bring down database service

Bringing down CRS service on home /u01/app/19.3.0/grid
CRS service brought down successfully on home /u01/app/19.3.0/grid

Start applying binary patch on home /u01/app/19.3.0/grid
Binary patch applied successfully on home /u01/app/19.3.0/grid

Starting CRS service on home /u01/app/19.3.0/grid
CRS service started successfully on home /u01/app/19.3.0/grid

Preparing home /u01/app/oracle/product/19.3.0/db_1 after database service restarted
No step execution required………

OPatchAuto successful.

——————————–Summary——————————–

Patching is completed successfully. Please find the summary as follows:

Host:rac1
RAC Home:/u01/app/oracle/product/19.3.0/db_1
Version:19.0.0.0.0
Summary:

==Following patches were SKIPPED:

Patch: /tmp/30501910/30489632
Reason: This patch is not applicable to this specified target type – “rac_database”

Patch: /tmp/30501910/30655595
Reason: This patch is not applicable to this specified target type – “rac_database”

Patch: /tmp/30501910/30489227
Reason: This patch is already been applied, so not going to apply again.

Patch: /tmp/30501910/30557433
Reason: This patch is already been applied, so not going to apply again.

Host:rac1
CRS Home:/u01/app/19.3.0/grid
Version:19.0.0.0.0
Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /tmp/30501910/30489227
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-03-13_17-23-27PM_1.log

Patch: /tmp/30501910/30489632
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-03-13_17-23-27PM_1.log

Patch: /tmp/30501910/30557433
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-03-13_17-23-27PM_1.log

Patch: /tmp/30501910/30655595
Log: /u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-03-13_17-23-27PM_1.log

OPatchauto session completed at Fri Mar 13 17:45:20 2020
Time taken to complete the session 27 minutes, 56 seconds
[root@luda tmp]#
4.5 遗留问题
这种在打RU过程中手工停crs的方式可以让RU 打成功,但是会导致很多权限问题,CRS 无法启动:

[root@luda lib]# crsctl start crs
CRS-41053: checking Oracle Grid Infrastructure for file permission issues
PRVG-2031 : Owner of file “/u01/app/grid/cfgtoollogs” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVH-0124 : Path “/etc/oracle/maps” with permissions “rwxr-xr-x” does not have write permissions for the file’s group and others on node “rac1”.
PRVH-0100 : Restricted deletion flag is not set for path “/etc/oracle/maps” on node “rac1”.
PRVG-2031 : Owner of file “/u01/app/grid” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/admin” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVH-0111 : Path “/etc/init.d/ohasd” with permissions “rwxr-x—” does not have read permissions for others on node “rac1”.
PRVH-0113 : Path “/etc/init.d/ohasd” with permissions “rwxr-x—” does not have execute permissions for others on node “rac1”.
PRVG-2031 : Owner of file “/etc/oracleafd.conf” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2032 : Group of file “/etc/oracleafd.conf” did not match the expected value on node “rac1”. [Expected = “oinstall(54321)” ; Found = “asmadmin(54329)”]
PRVH-0124 : Path “/var/tmp/.oracle” with permissions “rwxr-xr-x” does not have write permissions for the file’s group and others on node “rac1”.
PRVH-0100 : Restricted deletion flag is not set for path “/var/tmp/.oracle” on node “rac1”.
PRVG-2031 : Owner of file “/u01/app/grid/diag/ofm” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/lsnrctl” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/netcman” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/audit” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/checkpoints” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/crsdata/rac1/olr/rac1_19.olr” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVH-0100 : Restricted deletion flag is not set for path “/u01/app/grid/crsdata/rac1/shm” on node “rac1”.
PRVG-2031 : Owner of file “/u01/app/grid/crsdata/rac1/shm” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/crsdata/rac1/cvu” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/crsdata/rac1/olr” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/crsdata” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/crsdata/rac1/ocr” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/kfod” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/asmtool” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/crs” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/dps” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/em” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/diagtool” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/gsm” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/ios” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/rdbms” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/apx” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/tnslsnr” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/asmcmd” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/clients” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/asm” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/grid/diag/afdboot” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-11960 : Set user ID bit is not set for file “/u01/app/19.3.0/grid/bin/jssu” on node “rac1”.
PRVH-0147 : Set group ID bit is not set for file “/u01/app/19.3.0/grid/bin/extproc” on node “rac1”.
PRVG-11960 : Set user ID bit is not set for file “/u01/app/19.3.0/grid/bin/extjob” on node “rac1”.
PRVG-11960 : Set user ID bit is not set for file “/u01/app/19.3.0/grid/bin/oradism” on node “rac1”.
PRVG-11960 : Set user ID bit is not set for file “/u01/app/19.3.0/grid/bin/oracle” on node “rac1”.
PRVH-0147 : Set group ID bit is not set for file “/u01/app/19.3.0/grid/bin/oracle” on node “rac1”.
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/HASLoad.pm” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2032 : Group of file “/u01/app/19.3.0/grid/crs/install/cmdllroot.sh” did not match the expected value on node “rac1”. [Expected = “oinstall(54321)” ; Found = “root(0)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/crsconfig_params.sbs” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/crsconvert.pm” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/install_gi.excl” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/paramfile.crs” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/oracle-ohasd.service” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/oracle-ohasd.conf” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/installRemove.excl” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/install.incl” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/install.excl” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/inittab” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/crstfa.pm” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/crsconvtoext.pm” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/crsconfig_addparams.sbs” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/CLSR.pm” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/dropdb.pl” did not match the expected value on node “rac1”. [Expected = “root(0)|root(0)” ; Found = “grid(54322)”]
PRVH-0109 : Path “/u01/app/19.3.0/grid/lib/libacfs19.so” with permissions “rwxr-xr-x” does not have write permissions for the file’s group on node “rac1”.
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/tfa_setup” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/roothas.sh” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/rootcrs.sh” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVH-0109 : Path “/u01/app/19.3.0/grid/crs/install/rhpdata” with permissions “rwxr-xr-x” does not have write permissions for the file’s group on node “rac1”.
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/rhpdata” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/post_gimr_ugdg.pl” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVG-2031 : Owner of file “/u01/app/19.3.0/grid/crs/install/perlhasgen.pm” did not match the expected value on node “rac1”. [Expected = “root(0)” ; Found = “grid(54322)”]
PRVH-0109 : Path “/u01/app/19.3.0/grid/lib/libedtn19.a” with permissions “rwxr-xr-x” does not have write permissions for the file’s group on node “rac1”.
PRVH-0109 : Path “/u01/app/19.3.0/grid/lib/libskgxpcompat.so” with permissions “rwxr-xr-x” does not have write permissions for the file’s group on node “rac1”.
CRS-4124: Oracle High Availability Services startup failed.
CRS-4000: Command Start failed, or completed with errors.
[root@luda lib]#
根据错误提示逐个手工修改权限:
[root@luda grid]# chown root:oinstall admin -R
[root@luda grid]# pwd
/u01/app/grid
[root@luda grid]# chown root:oinstall * -R
[root@luda grid]# cd /u01/app/19.3.0/grid/crs/install/
[root@luda install]# ll

Oracle 19c RAC 环境升级 19.6 RU OPatch Prerequisite check “CheckApplicable” failed

An Oracle Data Guard far sync instance is a remote Oracle Data Guard destination that accepts redo from the primary database and then ships that redo to other members of the Oracle Data Guard configuration. A far sync instance manages a control file, receives redo into standby redo logs (SRLs), and archives those SRLs to local archived redo logs, but that is where the similarity with standbys ends. A far sync instance does not have user data files, cannot be opened for access, cannot run redo apply, and can never function in the primary role or be converted to any type of standby database.

Active Data Guard Far Sync是Oracle 12c的新特性(也称为Far Sync Standby),Far Sync功能的实现是通过在距离主库(Primary Database)相对较近的地点配置Far Sync实例,主库(Primary Database) 同步(synchronous)传输redo到Far Sync实例,然后Far Sync实例再将redo异步(asynchronous)传输到终端备库(Standby Database)。这样既可以保证零数据丢失又可以降低主库压力。Far Sync实例只有密码文件,init参数文件和控制文件,而没有数据文件。所以无法打开用于访问。

Far Sync配置对于Data Guard 角色转换(role transitions)是透明的,即switchover/failover命令方式与12c之前相同。

2 实验-Far Sync安装配置
创建Far Sync实例类似于创建物理备库,但数据文件在Far Sync实例中不存在。因此不需要拷贝数据文件并还原数据文件。一旦Far Sync实例创建了,那么就默认运行在Maximum Availability模式,那么REDO是实时同步传输的。

2.1 主库配置和操作
2.1.1 创建Far Sync实例的控制文件
SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS ‘/tmp/control01.ctl’;
Database altered.
将上面生成的Far Sync的控制文件拷贝到Far Sync所在的主机上。

SQL> !scp /tmp/control01.ctl 192.168.1.173://u01/app/oracle/oradata/cndba_far/

[Expect-le@ www.cndba.cn]$ pwd
/u01/app/oracle/oradata/cndba_far
2.1.2 修改配置,指向Far Sync实例
SQL> alter system set LOG_ARCHIVE_CONFIG =’DG_CONFIG=(cndba_p,cndba_far_sync,cndba_s)’ scope=both;
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2=’SERVICE=cndba_far_sync ASYNC NOAFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cndba_far_sync’ scope=both;
System altered.
2.2 备库修改配置
SQL> alter system set LOG_ARCHIVE_CONFIG =’DG_CONFIG=(cndba_p,cndba_far_sync,cndba_s)’ scope=both;
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2=’SERVICE=cndba_p ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cndba_p’ scope=both;
System altered.
2.3 Far Sync实例配置
2.3.1 修改PFILE
cndba_s是物理备库,cndba_far_sync是Far Sync实例的DB_UNIQUE_NAME。如果日志文件路径需要修改,也要修改DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT。

DB_UNIQUE_NAME=cndba_far_sync
CONTROL_FILES=’/u01/app/oracle/oradata/cndba_far/control01.ctl’
FAL_SERVER=cndba_p
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(cndba_p,cndba_far_sync,cndba_s)’
LOG_ARCHIVE_DEST_1=’LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=cndba_far_sync’
LOG_ARCHIVE_DEST_2=’SERVICE=cndba_s ASYNC
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=cndba_s’
2.3.2 将Far Sync实例启动到mount
SQL> create spfile from pfile=’/u01/app/oracle/product/12.1.0.2/db_1/dbs/initcndba_far.ora’;
File created.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 2348810240 bytes
Fixed Size 2927048 bytes
Variable Size 1409287736 bytes
Database Buffers 922746880 bytes
Redo Buffers 13848576 bytes
Database mounted.
2.3.3 查看Far Sync实例状态
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE
——————– —————- ——————– ——————–
MAXIMUM PERFORMANCE FAR SYNC MAXIMUM PERFORMANCE MOUNTED
2.3.4 创建standby redo log(可选,最好创建)
语法:

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4(‘/u01/app/oracle/oradata/cndba_far/standbyredo11.log’) SIZE 52428800;
2.4 主备库和Far Sync添加TNSNAME
CNDBA_FAR_SYNC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.173)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cndba)
)
)
2.5 检查配置
SQL> select * from V$DATAGUARD_CONFIG;
DB_UNIQUE_NAME PARENT_DBUN DEST_ROLE CURRENT_SCN CON_ID
—————————— —————————— —————– ———– ———-
cndba_p NONE PRIMARY DATABASE 2154617 0
cndba_far_sync cndba_p FAR SYNC INSTANCE 2151372 0
cndba_s cndba_far_sync PHYSICAL STANDBY 2151372 0
从上面可以看出,配置没有问题。

Cndba_p -> cndba_far_sync -> cndba_s

2.6 测试日志是否正常传输
–查看当前日志序列号

主库:

SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
————–
56
Far Sync实例:

SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
————–
56
备库:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
————–
56
–手动切换日志

SQL> alter system switch logfile;
System altered.
–再次查看备库和Far Sync实例的日志序列号
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
————–
57
至此搭建Far Sync结束了。

3 总结
1. 由于Far Sync存在单点故障,所以建议搭建两个及以上的Far Sync实例。默认只启用其中一个,只有当一个挂掉了,才会自动启用另一个。

2. 主库建议配置一个备用的LOG_ARCHIVE_DEST_3,直接指向备库。在LOG_ARCHIVE_DEST_2不可用时,会启用LOG_ARCHIVE_DEST_3直接传输redo到备库。

3. Far Sync可大大减少主库的压力,特别是在一主多备的情况下。

4. switchover/failover对于Far Sync是透明的,不需要特殊配置,按正常切换即可。

Active DataGuard Far sync在dataguard中的应用(12c,18c,19c)

1  说明

In previous releases, when creating a Data Guard configuration using the SQL command line, the default configuration was to apply redo from archived log files on the standby database. In Oracle Database 12c Release 1 (12.1), the default configuration is to use real-time apply so that redo is applied directly from the standby redo log file.

在之前版本中启用MRP默认是从归档日志文件中应用redo日志。从12c开始,默认是通过读取standby redo日志文件来启用real-time redo应用。

Recovery time is shortened at failover given that there is no backlog of redo waiting to be applied to the standby database if a failover is required. An active Data Guard user also sees more current data. This enhancement eliminates additional manual configuration (and the requirement that the administrator be aware of the default setting) that was required in past releases. It also makes the default SQL*Plus configuration identical to the default configuration used by the Data Guard broker.

2  实验

2.1   主备库状态

主库

SQL> select protection_mode,database_role,protection_level,open_mode from v$database;
PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM AVAILABILITY PRIMARY	      MAXIMUM AVAILABILITY READ WRITE

备库

SQL> select protection_mode,database_role,protection_level,open_mode from v$database;
PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM AVAILABILITY PHYSICAL STANDBY MAXIMUM AVAILABILITY READ ONLY WITH APPLY

2.2   启用MRP

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
或
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;

在备库配置了standby redo log,并处于归档模式,那么默认情况下会上面语句会自动启用real-time redo应用。

2.3   主库创建表

SQL> create table cndba as select * from dba_users;
Table created.
 
SQL> select count(*) from cndba;
COUNT(*)
----------
36

注意:不要切换日志,看看备库是否实时同步。

2.4   备库查询数据

SQL> select count(*) from cndba;
  COUNT(*)
----------
36

数据同步过来了。所以Real-Time应用是默认设置。

Real-Time Apply Default For Data Guard (12c,18c,19c)

在前阵子有一个客户的Mysql数据库被恶意删除,通过一晚努力恢复回来了。这里介绍下mysql的特殊恢复工具 undrop for innodb。

 

官网:

http://twindb.com/undrop-tool-for-innodb/

 

首先这是一个开源的工具,在github上有更新,在官网上也有对各个场景的使用介绍,这里不做多的介绍,我这里列举一个问题就是sys_parse工具的编译,官网提供的方法以及read me里的方式在我的环境里是编译不过去的,客户的数据库版本是5.1的,所在的系统平台是Cent os,但是需要引起注意的undrop for innodb有建议使用的操作系统版本,我使用的是Centos 6.5,Mysql 5.7版。下面是这次的恢复过程,请注意我改变的编译语法。

 

CentOs 6.5 Mysql 5.7.23

./stream_parser -f /mysqldata/ibdata1
[root@localhost undrop-for-innodb-develop]# ./stream_parser -f /mysqldata/ibdata1
Opening file: /mysqldata/ibdata1
File information:
ID of device containing file:        64512
inode number:                       652164
protection:                         100755 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36912
time of last access:            1559729794 Wed Jun  5 18:16:34 2019
time of last modification:      1559281768 Fri May 31 13:49:28 2019
time of last status change:     1559729794 Wed Jun  5 18:16:34 2019
total size, in bytes:             18874368 (18.000 MiB)
Size to process:                  18874368 (18.000 MiB)
All workers finished in 0 sec
[root@localhost FIL_PAGE_INDEX]# ls -al
total 1812
drwxr-xr-x 2 root root   4096 Jun  5 18:23 .
drwxr-xr-x 4 root root   4096 Jun  5 18:23 ..
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000001.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000002.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000003.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000004.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000005.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000006.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000011.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000012.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000013.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 0000000000000014.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000043.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000044.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000045.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000046.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000047.page
-rw-r–r– 1 root root 262144 Jun  5 18:23 0000000000000048.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000049.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000050.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000051.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000052.page
-rw-r–r– 1 root root 147456 Jun  5 18:23 0000000000000053.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000054.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000055.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000056.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000057.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000058.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000059.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000060.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000061.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000062.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000063.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000064.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000065.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000066.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000067.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000068.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000069.page
-rw-r–r– 1 root root  81920 Jun  5 18:23 0000000000000070.page
-rw-r–r– 1 root root  32768 Jun  5 18:23 0000000000000071.page
-rw-r–r– 1 root root  49152 Jun  5 18:23 0000000000000072.page
-rw-r–r– 1 root root  16384 Jun  5 18:23 18446744069414584320.page
mkdir -p dumps/default
恢复sys_tables
[root@localhost undrop-for-innodb-develop]# ./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql -o ./dumps/default/SYS_TABLES  -l ./dumps/default/SYS_TABLES.sql
[root@localhost undrop-for-innodb-develop]# cd dumps
[root@localhost dumps]# ls default/
SYS_TABLES  SYS_TABLES.sql
[root@localhost dumps]# ls
default
[root@localhost dumps]# cd default/
[root@localhost default]# ls
SYS_TABLES  SYS_TABLES.sql
[root@localhost default]# cat SYS_TABLES
— Page id: 8, Format: REDUNDANT, Records list: Invalid, Expected records: (0 2)
000000007B04    1A000001F80DAA  SYS_TABLES      “car\_doc/t\_order”     43      16      1       0       0       “”      0
000000000638    36000001890480  SYS_TABLES      “car\_doc/t\_auth\_config”      14      8       1       0       0       “”      0
000000007B0E    1F0000021E0610  SYS_TABLES      “car\_doc/t\_car\_type” 33      18      1       0       0       “”      0
000000007B14    22000001FA0E47  SYS_TABLES      “car\_doc/t\_role”      44      6       1       0       0       “”      0
000000007B06    1B0000025C0256  SYS_TABLES      “car\_doc/t\_dict”      35      3       1       0       0       “”      0
00000000063C    380000018B0256  SYS_TABLES      “car\_doc/t\_dict”      18      3       1       0       0       “”      0
000000007B12    21000002110484  SYS_TABLES      “car\_doc/t\_user”      46      9       1       0       0       “”      0
00000000063E    390000018C028B  SYS_TABLES      “car\_doc/t\_log”       20      5       1       0       0       “”      0
000000007B10    200000021607A6  SYS_TABLES      “car\_doc/t\_advert”    30      13      1       0       0       “”      0
000000007B1A    2500000212062C  SYS_TABLES      “car\_doc/t\_book\_type”        32      17      1       0       0       “”      0
000000007B02    190000020F0740  SYS_TABLES      “car\_doc/t\_auth\_config”      31      8       1       0       0       “”      0
000000000636    3500000188028B  SYS_TABLES      “car\_doc/t\_meals”     24      5       1       0       0       “”      0
000000000640    3A0000018D03FE  SYS_TABLES      “car\_doc/t\_news”      25      12      1       0       0       “”      0
00000000063A    370000018A04D2  SYS_TABLES      “car\_doc/t\_order”     26      16      1       0       0       “”      0
000000007B08    1C000002270633  SYS_TABLES      “car\_doc/t\_log”       37      5       1       0       0       “”      0
000000007B16    23000001BF0406  SYS_TABLES      “car\_doc/t\_count”     34      8       1       0       0       “”      0
000000007B0A    1D0000021006DE  SYS_TABLES      “car\_doc/t\_news”      42      12      1       0       0       “”      0
000000007B0C    1E000001F90C68  SYS_TABLES      “car\_doc/t\_feedback”  36      4       1       0       0       “”      0
000000007B20    28000001C20977  SYS_TABLES      “car\_doc/t\_manager”   38      9       1       0       0       “”      0
000000007B1C    26000001FB0A24  SYS_TABLES      “car\_doc/t\_manager\_authbak”  39      7       1       0       0       “”      0
000000007B1E    27000001C105F9  SYS_TABLES      “car\_doc/t\_manager\_role”     40      3       1       0       0       “”      0
000000007B00    180000022607CE  SYS_TABLES      “car\_doc/t\_meals”     41      5       1       0       0       “”      0
000000007B18    24000001C00479  SYS_TABLES      “car\_doc/t\_role\_auth”        45      3       1       0       0       “”      0
000000007F01    02000001860817  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING” 48      4       1       0       0       “”      0
000000007B29    30000001C406EA  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00231”   48      4       1       0       0       “”      0
000000007B2C    32000001FE0C34  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00232”   47      4       1       0       0       “”      0
— Page id: 8, Found records: 26, Lost records: YES, Leaf page: YES
— Page id: 8, Format: REDUNDANT, Records list: Invalid, Expected records: (0 2)
000000007B04    1A000001F80DAA  SYS_TABLES      “car\_doc/t\_order”     43      16      1       0       0       “”      0
000000000638    36000001890480  SYS_TABLES      “car\_doc/t\_auth\_config”      14      8       1       0       0       “”      0
000000007B0E    1F0000021E0610  SYS_TABLES      “car\_doc/t\_car\_type” 33      18      1       0       0       “”      0
000000007B14    22000001FA0E47  SYS_TABLES      “car\_doc/t\_role”      44      6       1       0       0       “”      0
000000007B06    1B0000025C0256  SYS_TABLES      “car\_doc/t\_dict”      35      3       1       0       0       “”      0
00000000063C    380000018B0256  SYS_TABLES      “car\_doc/t\_dict”      18      3       1       0       0       “”      0
000000007B12    21000002110484  SYS_TABLES      “car\_doc/t\_user”      46      9       1       0       0       “”      0
00000000063E    390000018C028B  SYS_TABLES      “car\_doc/t\_log”       20      5       1       0       0       “”      0
000000007B10    200000021607A6  SYS_TABLES      “car\_doc/t\_advert”    30      13      1       0       0       “”      0
000000007B1A    2500000212062C  SYS_TABLES      “car\_doc/t\_book\_type”        32      17      1       0       0       “”      0
000000007B02    190000020F0740  SYS_TABLES      “car\_doc/t\_auth\_config”      31      8       1       0       0       “”      0
000000000636    3500000188028B  SYS_TABLES      “car\_doc/t\_meals”     24      5       1       0       0       “”      0
000000000640    3A0000018D03FE  SYS_TABLES      “car\_doc/t\_news”      25      12      1       0       0       “”      0
00000000063A    370000018A04D2  SYS_TABLES      “car\_doc/t\_order”     26      16      1       0       0       “”      0
000000007B08    1C000002270633  SYS_TABLES      “car\_doc/t\_log”       37      5       1       0       0       “”      0
000000007B16    23000001BF0406  SYS_TABLES      “car\_doc/t\_count”     34      8       1       0       0       “”      0
000000007B0A    1D0000021006DE  SYS_TABLES      “car\_doc/t\_news”      42      12      1       0       0       “”      0
000000007B0C    1E000001F90C68  SYS_TABLES      “car\_doc/t\_feedback”  36      4       1       0       0       “”      0
000000007B20    28000001C20977  SYS_TABLES      “car\_doc/t\_manager”   38      9       1       0       0       “”      0
000000007B1C    26000001FB0A24  SYS_TABLES      “car\_doc/t\_manager\_authbak”  39      7       1       0       0       “”      0
000000007B1E    27000001C105F9  SYS_TABLES      “car\_doc/t\_manager\_role”     40      3       1       0       0       “”      0
000000007B00    180000022607CE  SYS_TABLES      “car\_doc/t\_meals”     41      5       1       0       0       “”      0
000000007B18    24000001C00479  SYS_TABLES      “car\_doc/t\_role\_auth”        45      3       1       0       0       “”      0
000000007F01    02000001860817  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING” 48      4       1       0       0       “”      0
000000007B29    30000001C406EA  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00231”   48      4       1       0       0       “”      0
000000007B2C    32000001FE0C34  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00232”   47      4       1       0       0       “”      0
— Page id: 8, Found records: 26, Lost records: YES, Leaf page: YES
— Page id: 8, Format: REDUNDANT, Records list: Invalid, Expected records: (0 3)
000000007B04    1A000001F80DAA  SYS_TABLES      “car\_doc/t\_order”     43      16      1       0       0       “”      0
000000000638    36000001890480  SYS_TABLES      “car\_doc/t\_auth\_config”      14      8       1       0       0       “”      0
000000007B0E    1F0000021E0610  SYS_TABLES      “car\_doc/t\_car\_type” 33      18      1       0       0       “”      0
000000007B14    22000001FA0E47  SYS_TABLES      “car\_doc/t\_role”      44      6       1       0       0       “”      0
000000007B06    1B0000025C0256  SYS_TABLES      “car\_doc/t\_dict”      35      3       1       0       0       “”      0
00000000063C    380000018B0256  SYS_TABLES      “car\_doc/t\_dict”      18      3       1       0       0       “”      0
000000007B12    21000002110484  SYS_TABLES      “car\_doc/t\_user”      46      9       1       0       0       “”      0
00000000063E    390000018C028B  SYS_TABLES      “car\_doc/t\_log”       20      5       1       0       0       “”      0
000000007B10    200000021607A6  SYS_TABLES      “car\_doc/t\_advert”    30      13      1       0       0       “”      0
000000007B1A    2500000212062C  SYS_TABLES      “car\_doc/t\_book\_type”        32      17      1       0       0       “”      0
000000007B02    190000020F0740  SYS_TABLES      “car\_doc/t\_auth\_config”      31      8       1       0       0       “”      0
000000000636    3500000188028B  SYS_TABLES      “car\_doc/t\_meals”     24      5       1       0       0       “”      0
000000000640    3A0000018D03FE  SYS_TABLES      “car\_doc/t\_news”      25      12      1       0       0       “”      0
00000000063A    370000018A04D2  SYS_TABLES      “car\_doc/t\_order”     26      16      1       0       0       “”      0
000000007B08    1C000002270633  SYS_TABLES      “car\_doc/t\_log”       37      5       1       0       0       “”      0
000000007B16    23000001BF0406  SYS_TABLES      “car\_doc/t\_count”     34      8       1       0       0       “”      0
000000007B0A    1D0000021006DE  SYS_TABLES      “car\_doc/t\_news”      42      12      1       0       0       “”      0
000000007B0C    1E000001F90C68  SYS_TABLES      “car\_doc/t\_feedback”  36      4       1       0       0       “”      0
000000007B20    28000001C20977  SYS_TABLES      “car\_doc/t\_manager”   38      9       1       0       0       “”      0
000000007B1C    26000001FB0A24  SYS_TABLES      “car\_doc/t\_manager\_authbak”  39      7       1       0       0       “”      0
000000007B1E    27000001C105F9  SYS_TABLES      “car\_doc/t\_manager\_role”     40      3       1       0       0       “”      0
000000007B00    180000022607CE  SYS_TABLES      “car\_doc/t\_meals”     41      5       1       0       0       “”      0
000000007B18    24000001C00479  SYS_TABLES      “car\_doc/t\_role\_auth”        45      3       1       0       0       “”      0
000000007B29    30000001C406EA  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00231”   48      4       1       0       0       “”      0
000000007B2C    32000001FE0C34  SYS_TABLES      “PLEASE\_READ\_ME\_VVV/WARNING@00232”   47      4       1       0       0       “”      0
— Page id: 8, Found records: 25, Lost records: YES, Leaf page: YES
./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql -o ./dumps/SYS_TABLES  -l ./dumps/SYS_TABLES.sql
./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql -o ./dumps/SYS_INDEXES  -l ./dumps/SYS_INDEXES.sql
./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t dictionary/SYS_COLUMNS.sql -o ./dumps/SYS_COLUMNS  -l ./dumps/SYS_COLUMNS.sql
./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page -t dictionary/SYS_FIELDS.sql -o ./dumps/SYS_FIELDS  -l ./dumps/SYS_FIELDS.sql
create database dictionary;
use dictionary;
mysql> source  /undropmysql/dictionary/SYS_TABLES.sql
mysql> source  /undropmysql/dictionary/SYS_INDEXES.sql
mysql> source  /undropmysql/dictionary/SYS_FIELDS.sql
mysql> source  /undropmysql/dictionary/SYS_COLUMNS.sql
mysql> source  /undropmysql/dictionary/SYS_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 (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.01 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 (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> .
    ->
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘.’ at line 1
mysql> source  /undropmysql/dictionary/SYS_INDEXES.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 (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 (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> /undropmysql/dictionary/SYS_FIELDS.sql
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘/undropmysql/dictionary/SYS_FIELDS.sql’ at line 1
mysql> source  /undropmysql/dictionary/SYS_FIELDS.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 (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 (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> source  /undropmysql/dictionary/SYS_COLUMNS.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 (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 (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)
#执行前面生成的LOAD DATA语句导入恢复的记录
 mysql -h127.0.0.1 -uroot  -p dictionary <SYS_TABLES.sql
 mysql -h127.0.0.1 -uroot  -p dictionary <SYS_COLUMNS.sql
 mysql -h127.0.0.1 -uroot  -p dictionary <SYS_INDEXES.sql
 mysql -h127.0.0.1 -uroot  -p dictionary <SYS_FIELDS.sql
 要用mysql_config的绝对路径
 make sys_parser /usr/bin/mysql_config cc -o sys_parser sys_parser.c ‘mysql_config –cflags’ ‘mysql_config –libs’  ===>测试不行
 make sys_parser /usr/bin/mysql_config cc ‘mysql_config –cflags’ ‘mysql_config –libs’ -o sys_parser sys_parser.c  ===>测试不行
  gcc sys_parser.c -o sys_parser $(/usr/bin/mysql_config –libs –cflags) ==》重新修改代码后按照c语言编译的方法改写通过
 mysql> desc SYS_TABLES
    -> ;
+————–+———————+——+—–+———+——-+
| Field        | Type                | Null | Key | Default | Extra |
+————–+———————+——+—–+———+——-+
| NAME         | varchar(255)        | NO   | PRI |         |       |
| ID           | bigint(20) unsigned | NO   |     | 0       |       |
| N_COLS       | int(10)             | YES  |     | NULL    |       |
| TYPE         | int(10) unsigned    | YES  |     | NULL    |       |
| MIX_ID       | bigint(20) unsigned | YES  |     | NULL    |       |
| MIX_LEN      | int(10) unsigned    | YES  |     | NULL    |       |
| CLUSTER_NAME | varchar(255)        | YES  |     | NULL    |       |
| SPACE        | int(10) unsigned    | YES  |     | NULL    |       |
+————–+———————+——+—–+———+——-+
8 rows in set (0.00 sec)
mysql> select * from SYS_TABLES;
+———————————-+—-+——–+——+——–+———+————–+——-+
| NAME                             | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+———————————-+—-+——–+——+——–+———+————–+——-+
| car_doc/t_advert                 | 30 |     13 |    1 |      0 |       0 |              |     0 |
| car_doc/t_auth_config            | 31 |      8 |    1 |      0 |       0 |              |     0 |(空表)
| car_doc/t_book_type              | 32 |     17 |    1 |      0 |       0 |              |     0 |
| car_doc/t_car_type               | 33 |     18 |    1 |      0 |       0 |              |     0 |
| car_doc/t_count                  | 34 |      8 |    1 |      0 |       0 |              |     0 |
| car_doc/t_dict                   | 18 |      3 |    1 |      0 |       0 |              |     0 |
| car_doc/t_feedback               | 36 |      4 |    1 |      0 |       0 |              |     0 |
| car_doc/t_log                    | 37 |      5 |    1 |      0 |       0 |              |     0 |
| car_doc/t_manager                | 38 |      9 |    1 |      0 |       0 |              |     0 |
| car_doc/t_manager_authbak        | 39 |      7 |    1 |      0 |       0 |              |     0 |
| car_doc/t_manager_role           | 40 |      3 |    1 |      0 |       0 |              |     0 |
| car_doc/t_meals                  | 41 |      5 |    1 |      0 |       0 |              |     0 |
| car_doc/t_news                   | 42 |     12 |    1 |      0 |       0 |              |     0 |
| car_doc/t_order                  | 26 |     16 |    1 |      0 |       0 |              |     0 |
| car_doc/t_role                   | 44 |      6 |    1 |      0 |       0 |              |     0 |
| car_doc/t_role_auth              | 45 |      3 |    1 |      0 |       0 |              |     0 |
| car_doc/t_user                   | 46 |      9 |    1 |      0 |       0 |              |     0 |
| PLEASE_READ_ME_VVV/WARNING       | 48 |      4 |    1 |      0 |       0 |              |     0 |
| PLEASE_READ_ME_VVV/WARNING@00231 | 48 |      4 |    1 |      0 |       0 |              |     0 |
| PLEASE_READ_ME_VVV/WARNING@00232 | 47 |      4 |    1 |      0 |       0 |              |     0 |
+———————————-+—-+——–+——+——–+———+————–+——-+
20 rows in set (0.00 sec)
 gcc sys_parser.c -o sys_parser $(/usr/bin/mysql_config –libs –cflags)
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_advert
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_auth_config
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_book_type
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_car_type
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_count
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_dict
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_feedback
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_log
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_manager
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_manager_authbak
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_manager_role
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_meals
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_news
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_order
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_role
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_role_auth
./sys_parser -h127.0.0.1 -u root -p root -d dictionary car_doc/t_user
./sys_parser -h127.0.0.1 -u root -p root -d dictionary PLEASE_READ_ME_VVV/WARNING
./sys_parser -h127.0.0.1 -u root -p root -d dictionary PLEASE_READ_ME_VVV/WARNING@00231
./sys_parser -h127.0.0.1 -u root -p root -d dictionary PLEASE_READ_ME_VVV/WARNING@00232
mysql>  create database car_doc;
Query OK, 1 row affected (0.00 sec)
mysql> use car_doc
Database changed
mysql> source /undropmysql/car_doc/t_advert.sql
Query OK, 0 rows affected (0.04 sec)
mysql> source /undropmysql/dumps/default/t_advert.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 30 rows affected (0.07 sec)
Records: 30  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select * from car_doc;
ERROR 1146 (42S02): Table ‘car_doc.car_doc’ doesn’t exist
mysql> select * from t_advert;
+—–+—————-+——————+———————+——–+————+————+————-+———-+———+————-+—————–+————-+
| id  | url            | title            | createor            | status | createTime | updateTime | sort        | updateor | picture | releaseTime | approvalHistory | offlineTime |
+—–+—————-+——————+———————+——–+————+————+————-+———-+———+————-+—————–+————-+
|  24 | 12016051209173 | 3094e08e0738c422 | 51007424128@qq.co   | m      | 0          |            | -1300234240 |          | NULL    |             | NULL            |             |
|  25 | 12016051210135 | 2dd39aef34bf64a7 | 8568144450@qq.co    | m      | 0          |            | -1300234240 |          | NULL    |             | NULL            |             |
|  26 | 12016061312503 | 19864b15399af40d | 51007424128@qq.co   | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
| 119 | 12017100914075 | 290c508ebb5434bd | 8dfylxxgk@163.co    | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
| 120 | 12017100914084 | 1f9fad68aa8404ae | 31007424128@qq.co   | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
| 121 | 12017100914093 | 6bbabcaa806f0400 | 1zhufeicai@163.co   | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
| 122 | 12018051611300 | 852199cab75a745f | e2042213325@qq.co   | m      | 0          |            | -1317011456 |          | NULL    |             | NULL            |             |
| 123 | 12018052315465 | 3b5ff7ba13b784f9 | e2042213325@qq.co   | m      | 0          |            | -1300234240 |          | NULL    |             | NULL            |             |
| 124 | 12018081413501 | 254bdc81e991a495 | bberling@livemail.t | w      | 0          |            | -1300234240 |          | NULL    |             | NULL            |             |
| 125 | 12018082911062 | 85f7a36723107478 | 384558040@qq.co     | m      | 0          |            | -1317011456 |          | NULL    |             | NULL            |             |
| 126 | 12018111208521 | 1aeafacfb1013418 | azhufeicai@163.co   | m      | 0          |            | -1249902592 |          | NULL    |             | NULL            |             |
+—–+—————-+——————+———————+——–+————+————+————-+———-+———+————-+—————–+————-+
11 rows in set (0.00 sec)
mysql>
======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000016.page -t car_doc/t_auth_config.sql  > dumps/default/t_auth_config 2> dumps/default/t_auth_config.sql
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000047.page -t car_doc/t_auth_config.sql  > dumps/default/t_auth_config 2> dumps/default/t_auth_config.sql
source /undropmysql/car_doc/t_auth_config.sql
source /undropmysql/dumps/default/t_auth_config.sql
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_auth_config.sql
Query OK, 0 rows affected (0.02 sec)
mysql> desc t_auth_config
    -> ;
+————-+————-+——+—–+———+——-+
| Field       | Type        | Null | Key | Default | Extra |
+————-+————-+——+—–+———+——-+
| id          | bigint(20)  | NO   | PRI | NULL    |       |
| name        | varchar(40) | NO   |     | NULL    |       |
| url         | varchar(60) | NO   |     | NULL    |       |
| status      | int(11)     | NO   |     | NULL    |       |
| pid         | bigint(20)  | NO   |     | NULL    |       |
| createtime  | datetime    | NO   |     | NULL    |       | 类型改为varchar
| operator    | varchar(45) | YES  |     | NULL    |       |
| operatetime | datetime    | YES  |     | NULL    |       |
+————-+————-+——+—–+———+——-+ 类型改为varchar
8 rows in set (0.01 sec)
mysql> source /undropmysql/car_doc/t_auth_config.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_auth_config.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 67 rows affected (0.01 sec)
Records: 34  Deleted: 33  Skipped: 0  Warnings: 0
======重要 表数据为如下 ==== 因为时间导出有问题,导入mysql有异常故截取记录 以供查询(有33条记录标记为被正常删除过的) =========
— Page id: 415, Format: COMPACT, Records list: Valid, Expected records: (18 18)
000000180000    20001180000001  t_auth_config   6442450944      “”      “”      0       -9223371929413474304    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:26”
000000190400    28001180000001  t_auth_config   6442450944      “”      “”      0       -9223371925185613824    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:27”
0000001A0000    30001180000001  t_auth_config   6442450944      “”      “”      0       -9223371920890644480    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:28”
0000001B0000    38001180000001  t_auth_config   6442450944      “”      “”      0       -9223371916595675136    “0789-56-88 90:47:04”   “”      NULL
0000001C0000    40001180000001  t_auth_config   6442450944      “”      “”      0       -9223371912233596928    “0789-56-88 90:47:04”   NULL    NULL
0000001D0400    48001180000001  t_auth_config   6442450944      “”      “”      0       -9223371908005736448    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:31”
0000001E0000    50001180000001  t_auth_config   6442450944      “”      “”      0       -9223371903710767104    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:35”
0000001F0000    58001180000001  t_auth_config   6442450944      “”      “”      0       -9223371886530895872    “0789-56-88 90:47:04”   “”      NULL
000000230000    60001180000001  t_auth_config   6442450944      “”      “”      0       -9223371882168817664    “0789-56-88 90:47:04”   NULL    NULL
000000240400    68001180000001  t_auth_config   6442450944      “”      “”      0       -9223371877940957184    “0789-56-88 90:47:04”   NULL    NULL
000000250000    70001180000001  t_auth_config   6442450944      “”      “”      0       -9223371873645987840    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:44”
000000260000    78001180000001  t_auth_config   6442450944      “”      “”      0       -9223371847876182016    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:47”
0000002C0000    80001180000001  t_auth_config   6442450944      “”      “”      0       -9223371834991278080    “0789-56-88 90:47:04”   “”      NULL
0000002F0000    88001180000001  t_auth_config   6442450944      “”      “”      0       -9223371830696308736    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:49”
000000300000    90001180000001  t_auth_config   6442450944      “”      “”      0       -9223371826401339138    “5150-81-54 22:05:44”   NULL    NULL
000000310000    98FED280000001  t_auth_config   6442450944      “”      “”      0       -9223371822106411008    “0000-00-00 00:00:00”   “”      “0000-00-00 00:00:00”
000000320000    00000000000000  t_auth_config   6442450944      “”      “”      -2147483648     -9223372036854775808    “0000-00-00 00:00:00”   NULL    “0000-00-00 00:00:00”
— Page id: 415, Found records: 17, Lost records: YES, Leaf page: YES
— Page id: 415, Format: COMPACT, Records list: Valid, Expected records: (18 18)
000000180000    20001180000001  t_auth_config   6442450944      “”      “”      0       -9223371929413474304    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:26”
000000190400    28001180000001  t_auth_config   6442450944      “”      “”      0       -9223371925185613824    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:27”
0000001A0000    30001180000001  t_auth_config   6442450944      “”      “”      0       -9223371920890644480    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:28”
0000001B0000    38001180000001  t_auth_config   6442450944      “”      “”      0       -9223371916595675136    “0789-56-88 90:47:04”   “”      NULL
0000001C0000    40001180000001  t_auth_config   6442450944      “”      “”      0       -9223371912233596928    “0789-56-88 90:47:04”   NULL    NULL
0000001D0400    48001180000001  t_auth_config   6442450944      “”      “”      0       -9223371908005736448    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:31”
0000001E0000    50001180000001  t_auth_config   6442450944      “”      “”      0       -9223371903710767104    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:35”
0000001F0000    58001180000001  t_auth_config   6442450944      “”      “”      0       -9223371886530895872    “0789-56-88 90:47:04”   “”      NULL
000000230000    60001180000001  t_auth_config   6442450944      “”      “”      0       -9223371882168817664    “0789-56-88 90:47:04”   NULL    NULL
000000240400    68001180000001  t_auth_config   6442450944      “”      “”      0       -9223371877940957184    “0789-56-88 90:47:04”   NULL    NULL
000000250000    70001180000001  t_auth_config   6442450944      “”      “”      0       -9223371873645987840    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:44”
000000260000    78001180000001  t_auth_config   6442450944      “”      “”      0       -9223371847876182016    “0789-56-88 90:47:04”   NULL    “0000-00-00 00:00:47”
0000002C0000    80001180000001  t_auth_config   6442450944      “”      “”      0       -9223371834991278080    “0789-56-88 90:47:04”   “”      NULL
0000002F0000    88001180000001  t_auth_config   6442450944      “”      “”      0       -9223371830696308736    “0789-56-88 90:47:04”   “”      “0000-00-00 00:00:49”
000000300000    90001180000001  t_auth_config   6442450944      “”      “”      0       -9223371826401339138    “5150-81-54 22:05:44”   NULL    NULL
000000310000    98FED280000001  t_auth_config   6442450944      “”      “”      0       -9223371822106411008    “0000-00-00 00:00:00”   “”      “0000-00-00 00:00:00”
000000320000    00000000000000  t_auth_config   6442450944      “”      “”      -2147483648     -9223372036854775808    “0000-00-00 00:00:00”   NULL    “0000-00-00 00:00:00”
— Page id: 415, Found records: 17, Lost records: YES, Leaf page: YES
=====
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000048.page -t car_doc/t_book_type.sql  > dumps/default/t_book_type 2> dumps/default/t_book_type.sql
mysql> source /undropmysql/dumps/default/t_book_type.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 554 rows affected (0.12 sec)
Records: 554  Deleted: 0  Skipped: 0  Warnings: 0
修改了 t_book_type表的字段approvalHistory的属性text为varchar(20000)
====
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000049.page -t car_doc/t_car_type.sql  > dumps/default/t_car_type 2> dumps/default/t_car_type.sql
source /undropmysql/car_doc/t_car_type.sql
source /undropmysql/dumps/default/t_car_type.sql
mysql>
mysql>
mysql> source /undropmysql/car_doc/t_car_type.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_car_type.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 38 rows affected (0.00 sec)
Records: 38  Deleted: 0  Skipped: 0  Warnings: 0
====
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000050.page -t car_doc/t_count.sql  > dumps/default/t_count 2> dumps/default/t_count.sql
source /undropmysql/car_doc/t_count.sql
source /undropmysql/dumps/default/t_count.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000050.page -t car_doc/t_count.sql  > dumps/default/t_count 2> dumps/default/t_count.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_count.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_count.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 30 rows affected (0.01 sec)
Records: 30  Deleted: 0  Skipped: 0  Warnings: 0
======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000023.page -t car_doc/t_dict.sql  > dumps/default/t_dict 2> dumps/default/t_dict.sql
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000051.page -t car_doc/t_dict.sql  > dumps/default/t_dict 2> dumps/default/t_dict.sql
source /undropmysql/car_doc/t_dict.sql
source /undropmysql/dumps/default/t_dict.sql
空表
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000023.page -t car_doc/t_dict.sql  > dumps/default/t_dict 2> dumps/default/t_dict.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_dict.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_dict.sql
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘stat: No such file or directory
Can’t stat /undropmysql/pages-ibdata1/FIL_PAGE_I’ at line 1
mysql> source /undropmysql/dumps/default/t_dict.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 14 rows affected (0.00 sec)
Records: 14  Deleted: 0  Skipped: 0  Warnings: 0
======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000052.page -t car_doc/t_feedback.sql  > dumps/default/t_feedback 2> dumps/default/t_feedback.sql
source /undropmysql/car_doc/t_feedback.sql
source /undropmysql/dumps/default/t_feedback.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000052.page -t car_doc/t_feedback.sql  > dumps/default/t_feedback 2> dumps/default/t_feedback.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_feedback.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_feedback.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 20 rows affected (0.01 sec)
Records: 20  Deleted: 0  Skipped: 0  Warnings: 0
======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000025.page -t car_doc/t_log.sql  > dumps/default/t_log 2> dumps/default/t_log.sql
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000053.page -t car_doc/t_log.sql  > dumps/default/t_log 2> dumps/default/t_log.sql
source /undropmysql/car_doc/t_log.sql
source /undropmysql/dumps/default/t_log.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000053.page -t car_doc/t_log.sql  > dumps/default/t_log 2> dumps/default/t_log.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_log.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_log.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 1138 rows affected (0.05 sec)
Records: 1138  Deleted: 0  Skipped: 0  Warnings: 0
========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000055.page -t car_doc/t_manager.sql  > dumps/default/t_manager 2> dumps/default/t_manager.sql
source /undropmysql/car_doc/t_manager.sql
source /undropmysql/dumps/default/t_manager.sql
空表无数据(或者数据丢失)
[root@localhost default]# cat t_manager
— Page id: 328, Format: COMPACT, Records list: Valid, Expected records: (1 1)
— Page id: 328, Found records: 0, Lost records: YES, Leaf page: YES
— Page id: 328, Format: COMPACT, Records list: Valid, Expected records: (1 1)
— Page id: 328, Found records: 0, Lost records: YES, Leaf page: YES
[root@localhost default]# cat t_manager.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE ‘/undropmysql/dumps/default/t_manager’ REPLACE INTO TABLE `t_manager` CHARACTER SET UTF8 FIELDS TERMINATED BY ‘\t’ OPTIONALLY ENCLOSED BY ‘”‘ LINES STARTING BY ‘t_manager\t’ (`id`, `account`, `password`, `isdisable`);
— STATUS {“records_expected”: 2, “records_dumped”: 0, “records_lost”: true} STATUS END
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_manager.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_manager.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000058.page -t car_doc/t_manager_authbak.sql  > dumps/default/t_manager_authbak 2> dumps/default/t_manager_authbak.sql
source /undropmysql/car_doc/t_manager_authbak.sql
source /undropmysql/dumps/default/t_manager_authbak.sql
倒数第二个字段有乱码,无法识别因此保留数据给予手工录入
— Page id: 342, Format: COMPACT, Records list: Valid, Expected records: (18 18)
00000000066D    DB0000015D0110  t_manager_authbak       1       61      28      1       2016-04-09 01:06:10     huanad  2016-04-09 01:06:10
00000000066D    DB0000015D0120  t_manager_authbak       2       61      24      1       2016-04-09 13:37:08     huanad  2016-04-09 13:37:08
00000000066D    DB0000015D0130  t_manager_authbak       3       61      25      1       2016-04-09 13:37:25     huanad  2016-04-09 13:37:25
00000000066D    DB0000015D0140  t_manager_authbak       4       61      26      1       2016-04-09 13:37:43     huanad  2016-04-09 13:37:43
00000000066D    DB0000015D0150  t_manager_authbak       97      61      29      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0160  t_manager_authbak       99      61      30      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0170  t_manager_authbak       101     61      31      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0180  t_manager_authbak       103     61      35      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0190  t_manager_authbak       105     61      36      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01A0  t_manager_authbak       107     61      37      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01B0  t_manager_authbak       109     61      38      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01C0  t_manager_authbak       111     61      43      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01D0  t_manager_authbak       113     61      44      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01E0  t_manager_authbak       115     61      47      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01F0  t_manager_authbak       117     61      48      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0200  t_manager_authbak       119     61      49      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0210  t_manager_authbak       121     61      50      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0220  t_manager_authbak       123     61      59      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
— Page id: 342, Found records: 18, Lost records: NO, Leaf page: YES
— Page id: 342, Format: COMPACT, Records list: Valid, Expected records: (18 18)
00000000066D    DB0000015D0110  t_manager_authbak       1       61      28      1       2016-04-09 01:06:10     huanad  2016-04-09 01:06:10
00000000066D    DB0000015D0120  t_manager_authbak       2       61      24      1       2016-04-09 13:37:08     huanad  2016-04-09 13:37:08
00000000066D    DB0000015D0130  t_manager_authbak       3       61      25      1       2016-04-09 13:37:25     huanad  2016-04-09 13:37:25
00000000066D    DB0000015D0140  t_manager_authbak       4       61      26      1       2016-04-09 13:37:43     huanad  2016-04-09 13:37:43
00000000066D    DB0000015D0150  t_manager_authbak       97      61      29      1       2016-01-12 16:59:25     Shuanad  2016-01-12 16:59:25
00000000066D    DB0000015D0160  t_manager_authbak       99      61      30      1       2016-01-12 16:59:25     Shuanad  2016-01-12 16:59:25
00000000066D    DB0000015D0170  t_manager_authbak       101     61      31      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0180  t_manager_authbak       103     61      35      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0190  t_manager_authbak       105     61      36      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01A0  t_manager_authbak       107     61      37      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01B0  t_manager_authbak       109     61      38      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01C0  t_manager_authbak       111     61      43      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01D0  t_manager_authbak       113     61      44      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01E0  t_manager_authbak       115     61      47      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D01F0  t_manager_authbak       117     61      48      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0200  t_manager_authbak       119     61      49      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0210  t_manager_authbak       121     61      50      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
00000000066D    DB0000015D0220  t_manager_authbak       123     61      59      1       2016-01-12 16:59:25     Shuanad 2016-01-12 16:59:25
— Page id: 342, Found records: 18, Lost records: NO, Leaf page: YES
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000062.page -t car_doc/t_manager_role.sql  > dumps/default/t_manager_role 2> dumps/default/t_manager_role.sql
source /undropmysql/car_doc/t_manager_role.sql
source /undropmysql/dumps/default/t_manager_role.sql
Database changed
mysql> source /undropmysql/car_doc/t_manager_role.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_manager_role.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0
==========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000063.page -t car_doc/t_meals.sql  > dumps/default/t_meals 2> dumps/default/t_meals.sql
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000035.page -t car_doc/t_meals.sql  > dumps/default/t_meals 2> dumps/default/t_meals.sql
source /undropmysql/car_doc/t_meals.sql
source /undropmysql/dumps/default/t_meals.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/dumps/default/t_meals.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0
==========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000064.page -t car_doc/t_news.sql  > dumps/default/t_news 2> dumps/default/t_news.sql
source /undropmysql/car_doc/t_news.sql
source /undropmysql/dumps/default/t_news.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000064.page -t car_doc/t_news.sql  > dumps/default/t_news 2> dumps/default/t_news.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_news.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_news.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 8 rows affected (0.01 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0
==========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000065.page -t car_doc/t_order.sql  > dumps/default/t_order 2> dumps/default/t_order.sql
source /undropmysql/car_doc/t_order.sql
source /undropmysql/dumps/default/t_order.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000065.page -t car_doc/t_order.sql  > dumps/default/t_order 2> dumps/default/t_order.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_order.sql
Query OK, 0 rows affected (0.03 sec)
mysql> source /undropmysql/dumps/default/t_order.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 318 rows affected (0.01 sec)
Records: 318  Deleted: 0  Skipped: 0  Warnings: 0
===========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000066.page -t car_doc/t_role.sql  > dumps/default/t_role 2> dumps/default/t_role.sql
source /undropmysql/car_doc/t_role.sql
source /undropmysql/dumps/default/t_role.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000066.page -t car_doc/t_role.sql  > dumps/default/t_role 2> dumps/default/t_role.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> source /undropmysql/car_doc/t_role.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_role.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 14 rows affected (0.01 sec)
Records: 14  Deleted: 0  Skipped: 0  Warnings: 0
=======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000067.page -t car_doc/t_role_auth.sql  > dumps/default/t_role_auth 2> dumps/default/t_role_auth.sql
source /undropmysql/car_doc/t_role_auth.sql
source /undropmysql/dumps/default/t_role_auth.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000067.page -t car_doc/t_role_auth.sql  > dumps/default/t_role_auth 2> dumps/default/t_role_auth.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 64
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> source /undropmysql/car_doc/t_role_auth.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_role_auth.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 103 rows affected (0.01 sec)
Records: 103  Deleted: 0  Skipped: 0  Warnings: 0
=======
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000070.page -t car_doc/t_user.sql  > dumps/default/t_user 2> dumps/default/t_user.sql
source /undropmysql/car_doc/t_user.sql
source /undropmysql/dumps/default/t_user.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000070.page -t car_doc/t_user.sql  > dumps/default/t_user 2> dumps/default/t_user.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 66
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use car_doc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/car_doc/t_user.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/t_user.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 271 rows affected (0.01 sec)
Records: 271  Deleted: 0  Skipped: 0  Warnings: 0
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000072.page -t PLEASE_READ_ME_VVV/WARNING.sql  > dumps/default/WARNING 2> dumps/default/WARNING.sql
source /undropmysql/PLEASE_READ_ME_VVV/WARNING.sql
source /undropmysql/dumps/default/WARNING.sql
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use PLEASE_READ_ME_VVV
Database changed
mysql> source /undropmysql/PLEASE_READ_ME_VVV/WARNING.sql
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”WARNING'(
        ‘id’ INT NOT NULL,
        ‘warning’ TEXT,
        ‘Bitcoin_A’ at line 1
mysql> source /undropmysql/PLEASE_READ_ME_VVV/WARNING.sql
Query OK, 0 rows affected (0.02 sec)
mysql> source /undropmysql/dumps/default/WARNING.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000072.page -t PLEASE_READ_ME_VVV/WARNING@00231.sql  > dumps/default/WARNING@00231 2> dumps/default/WARNING@00231.sql
source /undropmysql/PLEASE_READ_ME_VVV/WARNING@00231.sql
source /undropmysql/dumps/default/WARNING@00231.sql
[root@localhost undropmysql]# ./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000072.page -t PLEASE_READ_ME_VVV/WARNING@00231.sql  > dumps/default/WARNING@00231 2> dumps/default/WARNING@00231.sql
[root@localhost undropmysql]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
[root@localhost undropmysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 72
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use PLEASE_READ_ME_VVV
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/PLEASE_READ_ME_VVV/WARNING@00231.sql
ERROR 1050 (42S01): Table ‘WARNING@00231’ already exists
mysql> source /undropmysql/dumps/default/WARNING@00231.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
=========
./c_parser -6f /undropmysql/pages-ibdata1/FIL_PAGE_INDEX/0000000000000071.page -t PLEASE_READ_ME_VVV/WARNING@00232.sql  > dumps/default/WARNING@00232 2> dumps/default/WARNING@00232.sql
source /undropmysql/PLEASE_READ_ME_VVV/WARNING@00232.sql
source /undropmysql/dumps/default/WARNING@00232.sql
mysql> use PLEASE_READ_ME_VVV
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /undropmysql/dumps/default/WARNING@00232.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
恢复完成

==================================================================

==========================

 

 

Mysql data unload – undrop for innodb

Automatic indexing是Oracle Database 19c开始新增加的特性,依据应用负载的变化自动/动态地进行索引的管理任务,比如创建(create index)、重构(rebuild index)和删除(drop index),从而提高数据库性能,这个特性也是Oracle 自治数据库云服务自我优化的一个基础。
Automatic indexing 主要功能
1)定期在预定义的时间间隔内在后台运行自动索引过程
2)分析应用程序工作负载,并根据分析报告相应地创建必要的新的索引,并删除现有耗费性能的

Automatic Indexing相关的数据字典
DBA_AUTO_INDEX_CONFIG –描述当前自动索引的配置
DBA_INDEXES/ALL_INDEXES/USER_INDEXES –新增加的AUTO列标识是自动索引(YES)还是手动索引(NO)
DBA_AUTO_INDEX_EXECUTIONS –显示历史自动索引任务执行
DBA_AUTO_INDEX_STATISTICS –显示与自动索引相关的统计信息
DBA_AUTO_INDEX_IND_ACTIONS –显示在自动索引上执行的操作
DBA_AUTO_INDEX_SQL_ACTIONS –显示在SQL上执行的验证自动索引的操作

DBMS_AUTO_INDEX.CONFIGURE包相关参数
AUTO_INDEX_DEFAULT_TABLESPACE –指定自动索引创建所存储的表空间
AUTO_INDEX_MODE –指定自动索引的模式(开关),当前3个值,默认OFF,表示特性关闭;IMPLEMENT表示自动创建创建、测试、并报告,最终索引是visible状态; REPORT ONLY 会创建索引但是invisible,不会影响SQL,只是意图生成报告。
AUTO_INDEX_REPORT_RETENTION –自动索引报告历史保留的天数 默认31天
AUTO_INDEX_RETENTION_FOR_AUTO — 自动创建的索引从上次使用后多少天不再使用的索引可以删除,默认373天
AUTO_INDEX_RETENTION_FOR_MANUAL — 手动创建的索引从上次使用后多少天不再使用的索引可以删除,默认永远
AUTO_INDEX_SPACE_BUDGET — 自动索引可以使用表空间大小的百分比,默认 50%

Automatic Indexing工作原理
索引管理后台进程TASK调用,可以自动的create, rebuild , drop 索引。后台进程是每15分钟调用一次,(是有j001进程执行_AUTO_INDEX_TASK_INTERVAL参数控制15分钟)。
也是基于传统手动优化SQL的思路,基于SQL中的列使用识别可以创建的索引,然后验证自动索引对性能的影响,然后按预设的值去创建索引,只不过整个过程是自动的,并且整个过程都有审核报告。

Automatic Indexing测试体验
1,查看Oracle数据库版本
[oracle19@source ~]$ sqlplus / as sysdba

Version 19.3.0.0.0

Copyright ? 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

SQL> select banner_full from v$version;

BANNER_FULL
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

SQL> select * from DBA_AUTO_INDEX_CONFIG;

PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED MODIFIED_BY

AUTO_INDEX_DEFAULT_TABLESPACE

AUTO_INDEX_MODE OFF —并未启用该参数

AUTO_INDEX_REPORT_RETENTION 31

AUTO_INDEX_RETENTION_FOR_AUTO 373

AUTO_INDEX_RETENTION_FOR_MANUAL

AUTO_INDEX_SCHEMA

AUTO_INDEX_SPACE_BUDGET 50

2,开启该特性,可以在CDB也可以在PDB
12:29:43 SYS@ludadbhost(source)> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
1
2 PDB$SEED READ ONLY NO
3 ludadbhostPDB READ WRITE NO
1
2
12:29:58 SYS@ludadbhost(source)> alter session set container=ludadbhostpdb;

Session altered.

12:30:29 SYS@ludadbhost(source)> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,‘IMPLEMENT’);
BEGIN DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,‘IMPLEMENT’); END;

ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.DBMS_AUTO_INDEX_INTERNAL”, line 9180
ORA-06512: at “SYS.DBMS_AUTO_INDEX”, line 283
ORA-06512: at line 1
通过MOS查询需要开启一个隐患参数并重启数据库服务
12:33:45 SYS@ludadbhost(source)> alter system set “_exadata_feature_on”=true scope=spfile;

System altered.

12:33:48 SYS@ludadbhost(source)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
12:34:22 SYS@ludadbhost(source)> startup
ORACLE instance started.

Total System Global Area 2147483552 bytes
Fixed Size 9146272 bytes
Variable Size 1090519040 bytes
Database Buffers 1023410176 bytes
Redo Buffers 24408064 bytes
Database mounted.
Database opened.
12:34:47 SYS@ludadbhost(source)> EXEC DBMS_AUTO_INDEX.CONFIGURE(‘AUTO_INDEX_MODE’,‘IMPLEMENT’);

PL/SQL procedure successfully completed. —在PDB中进行完成
12:36:12 SYS@ludadbhost(source)> col PARAMETER_VALUE for a20
12:36:19 SYS@ludadbhost(source)> /

PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED

AUTO_INDEX_COMPRESSION OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE IMPLEMENT 07-JAN-20 12.35.14.000000 PM
AUTO_INDEX_REPORT_RETENTION 31
AUTO_INDEX_RETENTION_FOR_AUTO 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET 50

8 rows selected.
3,创建用户以及对应数据表空间
10:55:52 SYS@ludadbhost(source)> alter session set container=ludadbhostpdb;

Session altered.

10:55:59 SYS@ludadbhost(source)> select file_name from dba_data_files;

/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/system01.dbf
/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/sysaux01.dbf
/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/undotbs01.dbf
/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/users01.dbf

10:56:16 SYS@ludadbhost(source)> CREATE TABLESPACEluda_data DATAFILE ‘/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/LUDA_data01.dbf’ SIZE 5G AUTOEXTEND Off;
Tablespace created.

10:58:33 SYS@ludadbhost(source)> 10:58:33 SYS@ludadbhost(source)> CREATE TABLESPACEluda_idx DATAFILE ‘/data/u19/app/oracle/oradata/ludadbhost/ludadbhostpdb/LUDA_idx01.dbf’ SIZE 2G AUTOEXTEND Off;
Tablespace created.

10:58:45 SYS@ludadbhost(source)> 10:58:45 SYS@ludadbhost(source)> create userluda IDENTIFIED BYluda ACCOUNT UNLOCK DEFAULT TABLESPACEluda_data TEMPORARY TABLESPACE TEMP;
User created.

10:58:55 SYS@ludadbhost(source)> grant connect,resource toluda;
Grant succeeded.

10:59:00 SYS@ludadbhost(source)> grant select any table toluda;
Grant succeeded.

4,创建数据
11:37:34luda@ludadbhost(source)> create table index_test as select * from dba_objects;

Table created.

11:37:47luda@ludadbhost(source)> select count(*) from index_test;

COUNT(*)
72406

11:38:50luda@ludadbhost(source)> insert into index_test select * from index_test;

72406 rows created.

11:39:03luda@ludadbhost(source)> insert into index_test select * from index_test;

144812 rows created.

11:39:07luda@ludadbhost(source)> insert into index_test select * from index_test;

289624 rows created.

11:39:09luda@ludadbhost(source)> insert into index_test select * from index_test;

579248 rows created.

11:39:13luda@ludadbhost(source)> insert into index_test select * from index_test;

1158496 rows created.

11:39:16luda@ludadbhost(source)> insert into index_test select * from index_test;

2316992 rows created.

11:39:22luda@ludadbhost(source)> insert into index_test select * from index_test;

4633984 rows created.

11:39:33luda@ludadbhost(source)> select count(*) from index_test;

COUNT(*)
9267968
11:39:45luda@ludadbhost(source)> insert into index_test select * from index_test;

9267968 rows created.

11:40:13luda@ludadbhost(source)> 11:40:13luda@ludadbhost(source)> select count(*) from index_test;

COUNT(*)
18535936

11:41:49luda@ludadbhost(source)> update index_test set object_id=rownum;

18535936 rows updated.

11:45:06luda@ludadbhost(source)> commit;

5,模拟日常查询并查看执行计划
13:59:06luda@ludadbhost(source)> select object_type from index_test where object_id=5555;

OBJECT_TYPE
VIEW

13:59:21luda@ludadbhost(source)> explain plan for SELECT OBJECT_NAME FROM INDEX_TEST WHERE OBJECT_ID=1;

Explained.

13:59:35luda@ludadbhost(source)> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

Plan hash value: 356488860

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 40 | 392 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| INDEX_TEST | 1 | 40 | 392 (1)| 00:00:01 |

Predicate Information (identified by operation id):

1 – filter(“OBJECT_ID”=1)

13 rows selected.
该访问路径是全表扫描的方式;我们多次执行以下语句,并静等15分钟查看相关试图是否有结果
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=5559;
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=50;
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=51;
14:05:03luda@ludadbhost(source)> select object_type from index_test where object_id=52;
14:05:03luda@ludadbhost(source)> select created from index_test where object_id=345;
15:38:33luda@ludadbhost(source)> select * from DBA_AUTO_INDEX_EXECUTIONS;

15:55:34luda@ludadbhost(source)> select index_name,table_name,command,statement from DBA_AUTO_INDEX_IND_ACTIONS where execution_name=‘SYS_AI_2020-01-07/15:40:57’ order by action_id;

INDEX_NAME TABLE_NAME COMMAND STATEMENT

SYS_AI_66825yg9wksv1 INDEX_TEST CREATE INDEX CREATE INDEX “LUDA”.“SYS_AI_66825yg9wksv1” ON “LUDA”.“INDEX_TEST”(“OBJECT_ID”) T
SYS_AI_66825yg9wksv1 INDEX_TEST REBUILD INDEX ALTER INDEX “LUDA”.“SYS_AI_66825yg9wksv1” REBUILD ONLINE
SYS_AI_66825yg9wksv1 INDEX_TEST ALTER INDEX VISIBLE ALTER INDEX “LUDA”.“SYS_AI_66825yg9wksv1” VISIBLE
分了三步,第一创建一个索引,第二步设置online属性,第三步设置为可见状态;

6,检查该SYS_AI索引是否真实存在
15:56:43luda@ludadbhost(source)> explain plan for select object_name from index_test where object_id=1;

案例参考csdn

Oracle 19c Automatic indexing