No question at this time
The DBA-Village forum
as RSS feed
Site Statistics| Ever registered users | 48757 | | Total active users | 1320 | | Act. users last 24h | 0 | | Act. users last hour | 0 | | Registered user hits last week | 24 | | Registered user hits last month | 1443 |
|
Go up
Restore of database
| Message |
Score |
Author |
Date |
   Hi Oracle gurus,
There is a requirement from de...... |
|
Balaji M |
Jun 16, 2022, 07:50 |
| Hi Balaji,
Bad news.
Block corruption means ...... |
 |
Philip Wisse |
Jun 16, 2022, 13:25 |
| Hi Philip,
Yes, its very bad news for me.
An...... |
|
Balaji M |
Jun 16, 2022, 15:31 |
| Hello.
I cannot stress highly enough that you s...... |
|
Adam Hudspith |
Jun 17, 2022, 11:22 |
| ok... |
|
Balaji M |
Jun 17, 2022, 13:51 |
| Subject: |
Restore of database |
| Author: |
Balaji M, India |
| Date: |
Jun 16, 2022, 07:50, 466 days ago |
| Os info: |
Red Hat Enterprise Linux |
| Oracle info: |
oracle 12.2.0.1.0 |
| Message: |
Hi Oracle gurus,
There is a requirement from developer to perform complete backup and restore of 2 databases
ITDEV and ITBCK with same version. Source and target server details were also mentioned.
I performed export and import for this activity.
When I perform full export for the DB ITDEV in the source server, I found the below errors.
ORA-39369: some rows may not be exported for "MWARZ"."TABLE_METADATA" due to block corruption
ORA-31693: Table data object "TWARD"."TABLE_METADATA" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01578: ORACLE data block corrupted (file # 4, block # 25221)
ORA-01110: data file 4: '/opt/oracle/app/ITBCK/oradata/ITDEV_users01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-39369: some rows may not be exported for "DEV_SPECTRUM_EXCHANGE"."TABLE_METADATA" due to block corruption
ORA-31693: Table data object "EXCHANGE"."TABLE_METADATA" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01578: ORACLE data block corrupted (file # 18, block # 24709)
ORA-01110: data file 18: '/opt/oracle/app/ITBCK/oradata/ITDEV_DEV_SPECTRUM_EXCHANGE01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-39369: some rows may not be exported for "TEST_SPECTRUM_EXCHANGE"."TABLE_METADATA" due to block corruption
ORA-31693: Table data object "TEST_SPECTRUM"."TABLE_METADATA" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01578: ORACLE data block corrupted (file # 16, block # 517)
ORA-01110: data file 16: '/opt/oracle/app/ITBCK/oradata/ITDEV_TEST_SPECTRUM_EXCHANGE01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-31693: Table data object "DEV_SPECTRUM"."ALSC_TABLE_METADATA_HIST" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01578: ORACLE data block corrupted (file # 18, block # 71813)
ORA-01110: data file 18: '/opt/oracle/app/ITBCK/oradata/ITDEV_DEV_SPECTRUM_EXCHANGE01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
When I try to import the dumpfile into the target DB, I get below errors.
Failing sql is:
ALTER TABLE "DEV"."BIN$2234OycsBCrgU3Z0zQoTFA==$0" MODIFY ("ID" GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1
MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH LIMIT VALUE CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE )
ORA-39083: Object type IDENTITY_COLUMN:"LOCAL4"."BIN$xyfeNJ0pdDrgU3Z0zQrYgw==$0" failed to create with error:
ORA-00942: table or view does not exist
Job "SYS"."SYS_IMPORT_FULL_01" completed with 363 error(s) at Fri Jun 10 14:14:14 2022 elapsed 0 00:19:04
ORA-39112: Dependent object type PASSWORD_HISTORY:"IMXAG" skipped, base object type USER:"IMXAG" creation failed
ORA-39112: Dependent object type PASSWORD_HISTORY:"PSYCR" skipped, base object type USER:"PSYCR" creation failed
Source server
Source db size is 148 GB
oracle version -- 12.2.0.1.0
OS version -- Red Hat Enterprise Linux Server release 6.10
Object counts in source db is 31 rows
Target server
Target db size is 132 GB ( after import)
oracle version -- 12.2.0.1.0
OS version -- Red Hat Enterprise Linux Server release 7.9
Object counts in source db is 42 rows
Could you please either suggest me easy method to perform this activity or request to provide solutions for this export and import errors.
Regards,
Balaji
|
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: Restore of database |
| Author: |
Philip Wisse, Netherlands |
| Date: |
Jun 16, 2022, 13:25, 466 days ago |
| Score: |
|
| Message: |
Hi Balaji,
Bad news.
Block corruption means there are one or more blocks with failed checksum.
You can figure out which tables are affected and try to save any data.
Easiest way of 'repairing' is truncate table.
Or if you have older backups maybe one of them is consistant.
If you have backups and ALL of the redologs ever since the corruption started you can reconstruct the data using the redologs, however the message 'NOLOGGING' means this is not possible.
Cause is hardware failure.
There are 2 types of backups, physical (rman) and logical (export). Block corruption may (can) not be detected by physical backups because all the blocks are copied without checking the checksum. Advice is therefore to perform a logical backup on a regular basis just to detect block corruption.
Regards, Philip |
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: Restore of database |
| Author: |
Balaji M, India |
| Date: |
Jun 16, 2022, 15:31, 466 days ago |
| Message: |
Hi Philip,
Yes, its very bad news for me.
Anyway thank you for your information and solutions.
Regards,
Balaji |
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: Restore of database |
| Author: |
Adam Hudspith, United Kingdom |
| Date: |
Jun 17, 2022, 11:22, 465 days ago |
| Score: |
|
| Message: |
Hello.
I cannot stress highly enough that you should repair the production [target] database before messing around with copies of the data for your devs.
the utility DBV should be able to give you the range of blocks affected. Havent used it in years tho - and I vaguely recall it only works against a closed database. Check the documentation. |
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: Restore of database |
| Author: |
Balaji M, India |
| Date: |
Jun 17, 2022, 13:51, 465 days ago |
| Message: |
ok |
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
|