No question at this time
DBA Top 10
1 M. Cadot 7200
2 B. Vroman 3100
3 J. PĂ©ran 2100
4 A. Kavsek 1400
5 P. Tsongayinwe 1300
6 P. Wisse 600
7 D. Walgude 400
7 J. Schnackenberg 400
9 J. Alcroft 200
10 A. Hudspith 100
10 D. Johnson 100
10 B. B 100
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48684
Total active users1329
Act. users last 24h2
Act. users last hour0
Registered user hits last week44
Registered user hits last month235
Go up

Restore of database
Next thread: Oracle High Availability Service Target is Showing Down in OEM
Prev thread: High swap utilization

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 ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts 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...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Adam Hudspith Jun 17, 2022, 11:22
ok... Balaji M Jun 17, 2022, 13:51

Follow up by mail Click here


Subject: Restore of database
Author: Balaji M, India
Date: Jun 16, 2022, 07:50, 62 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, 62 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
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, 62 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, 61 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
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, 61 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