Solved

8.1.6 cloned with 8.1.7 database; v$ views and tables missing or corrupt

Posted on 2004-04-17
33
953 Views
Last Modified: 2007-12-19
Hi,
We have 2 environments DEV and PROD.

DEV    8.1.6
PROD  8.1.7

DEV (target) was accidentally cloned with PROD (source). Now DEV's database is in a mess. I cannot run catalog.sql or catproc.sql to fix the system tables/views. They just hang. My questions:

1. Is there a way to fix system's tables/views ?

I tried to run hcheck diagnostic script and I get the following error:

SQL> execute hcheck.full
BEGIN hcheck.full; END;

*
ERROR at line 1:
ORA-06544: PL/SQL: internal error, arguments: [78601], [], [], [], [], [], [],
[]
ORA-06553: PLS-801: internal error [78601]
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.HOUT", line 18
ORA-06512: at "SYS.HCHECK", line 1078
ORA-06512: at line 1

2. Is there a way I can copy/clone DEV again or can I just replace DEV's  DB files with PROD's ?

I just want to fix DEV database so that I can upgrade the application from 11.5.3 to 11.5.7.

Thanks,
masaimara
0
Comment
Question by:masaimara
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 19
  • 13
33 Comments
 
LVL 2

Expert Comment

by:CallumT
ID: 10850987
Perhaps you need to create a new 8.1.7 ORACLE_HOME for your Dev database to run under, then try re-cloning from PROD again.

0
 

Author Comment

by:masaimara
ID: 10851108
Can you tell me how to go about doing this ? Do you know for sure that this will work ?
0
 
LVL 3

Expert Comment

by:taisk
ID: 10851227
The simplest way is to install Oracle 8.1.7 to the Dev environment or patch the current 8.1.6 install to 8.1.7.  Either way this will give you a working 8.1.7 environment in Dev.  Then you select the backup you want from PROD and restore into DEV.  Please consult your DBA.  This is a very simple operation.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:masaimara
ID: 10851340
We have no DBA. I am the DBA :(
Thanks for your input. I will get back to you as soon as I have something going. In the meantime if you know of any documents which have the step-by-step procedure to do this stuff, that would be great.
masaimara
0
 
LVL 2

Expert Comment

by:CallumT
ID: 10851575
Oracle installation guides should have the info you are after.

A quick work-around, provided the DEV and PROD servers are same hardware and OS, would be to copy the whole 8.1.7 Oracle Home from Prod to Dev.
Then re-link all Oracle executables - $ORACLE_HOME/bin/relink
Then repeat your clone process
0
 

Author Comment

by:masaimara
ID: 10854595
I am trying to take a backup of 8.1.6 Home (before copying 8.1.7) and it is taking too long because of the trc files under bdump. Are these files important ? Can I skip them ?
0
 

Author Comment

by:masaimara
ID: 10855173
Hi,
I got the copy of the tar file done but while trying to untar it it gave me a lot of errors saying "cannot create" and "cannot set time on: not owner". Is there something I am missing ?
Thanks,
masaimara
0
 
LVL 2

Expert Comment

by:CallumT
ID: 10856292
You can forget about the trace files - these will be specific to PROD database.

You need to have permission to create the directories on the DEV server. Ask your Unix admin person to either give you permissions ir get him/her to untar file for you.
BTW , you will need to create them as the ORACLE user
0
 

Author Comment

by:masaimara
ID: 10856671
I tried to do the upgrade using u080106.sql. It didn't work as the systems tables/views are corrupted. Is there a way I can replace this database with a new database ? May be copy data files, log files from other database ?
0
 

Author Comment

by:masaimara
ID: 10856708
I did the tar and untar as the ORACLE user with all the permissions - I think... I will check with the UNIX admin. Since no patch or sql are working against the database, the only hope I have is to replace this database with a new one. Could you please tell me if there is a way to replace files from another database ?
0
 
LVL 2

Expert Comment

by:CallumT
ID: 10858050
Sorry, I may have misinterpreted your original posting. I missed the 'accidentally' cloned bit.

However, it is good practice to have your Development database running under the same Oracle (and OS) versions as your Prod database. That way, there will be no surprises when you promote (move) application code from Dev to Prod. So if you have the 8.1.7 Oracle Home on your Dev Server, then to complete the copy process use the following steps.

1) On your Prod Database issue the following command
alter database backup controlfile to trace ; (This will produce a trace file in your UDUMP directory. )
0
 
LVL 2

Accepted Solution

by:
CallumT earned 250 total points
ID: 10858109
2) Edit this trace file to create a sql file that will create a control-file for your DEV database. E.G. call it controlfile.sql


Remove all the comments  (for example)


Dump file /db/arpt/dba/udump/arpt_ora_24395.trc
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
ORACLE_HOME = /opt/oracle/8.1.7.4
System name:    SunOS
Node name:      aklxd051
Release:        5.8
Version:        Generic_108528-22
Machine:        sun4u
Instance name: arpt
Redo thread mounted by this instance: 1
Oracle process number: 14
Unix process pid: 24395, image: oracle@aklxd051 (TNS V1-V3)

*** SESSION ID:(13.2756) 2004-04-19 22:07:51.763
*** 2004-04-19 22:07:51.763
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.

First line should be the STARTUP
Change the second line to read somethinglike :

CREATE CONTROLFILE SET DATABASE "XXXX" RESETLOGS NOARCHIVELOG  (where XXXX is the name of your DEV database)

Then change all the path-names for the database files to match what they will be called on your DEV server. SAve this File and then copy over to DEV Server.
So Control file will look something like..

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARPT" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 1022
    MAXINSTANCES 1
    MAXLOGHISTORY 32672
LOGFILE
  GROUP 1 '/db/arpt/redolog/arpt_redo_1a.dbf'  SIZE 4M,
  GROUP 2 '/db/arpt/redolog/arpt_redo_2a.dbf'  SIZE 4M,
  GROUP 3 '/db/arpt/redolog/arpt_redo_3a.dbf'  SIZE 4M,
  GROUP 4 '/db/arpt/redolog/arpt_redo_4a.dbf'  SIZE 4M
DATAFILE
  '/db/arpt/system/arpt_system_01.dbf',
  '/db/arpt/rollback/arpt_rollback_01.dbf',
  '/db/arpt/temp/arpt_temp_01.dbf',......
.......
  '/db/arpt/index/arpt_usage_raw_200402_ind_01.dbf',
  '/db/arpt/data/arpt_usage_raw_200402_data_01.dbf'
CHARACTER SET US7ASCII
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;


From the latest COLD backup of your PROD database (or if you can afford an outage - shutdown the PROD database) and copy all the files from PROD to DEV to the directories that have been defined in your newly edited controlfile.sql

Make sure your DEV environment is running under the correct ORACLE_HOME.
If necessary, copy the PROD pfile to DEV.

Start SQLPLUS and connect as sysdba, then execute your controlfile.sql.

So then you will have a copy of PROD running as DEV under the correct Oracle Home
0
 

Author Comment

by:masaimara
ID: 10861167
Thank you! This is what I needed. After I copy PROD files, I should delete DEV's old DATA and LOG files right ?

One other thing I saw was, when I check the value for $ORACLE_HOME it is a different directory - /u1/ora/devora/8.0.6, but the actual ORACLE_HOME is /u1/ora/devdb/8.1.6. Is this because of the APPS setup ? Do you know ?

Thanks again,
masaimara
0
 

Author Comment

by:masaimara
ID: 10861472
I forgot to tell you that in appreciation of your responses, I doubled the points.
0
 

Author Comment

by:masaimara
ID: 10862024
Sorry for posting so many questions. Disregard my previous 2 postings - this is a cumulative one...

Thank you for the step-by-step procedure that you've put out. You're awesome!

These are the questions I have:

1. After I copy PROD files, I should delete DEV's old DATA and LOG files right ?

2. Does it matter what PROD Backup (what date) I use ?

3. One other thing I saw was, when I check the value for $ORACLE_HOME it is a different directory - /u1/ora/devora/8.0.6, but the actual ORACLE_HOME is /u1/ora/devdb/8.1.6. Is this because of the APPS setup ? Do you know ?

FYI, in appreciation of your responses, I doubled the points.

masaimara


0
 
LVL 2

Expert Comment

by:CallumT
ID: 10864761
Hi.

1)  You would copy the PROD files over the top of ther Dev files (ensuring that the filenames - full pathname - match those in the controlfile.sql)
2)  Latest backup would be best
3) Sorry , not totally familiar with APPS set-up so unsure if Db Oracle Hoem should be same as APPS.


(Of course if you wanted to get back to square 1 - another option is to restore the DEV database from a backup)
0
 

Author Comment

by:masaimara
ID: 10880836
I am still in the process of doing it. Just wanted to keep you posted.
0
 

Author Comment

by:masaimara
ID: 10905545
CallumT,

I have the control files from DEV and PROD. They both look very different and so I would like your opinion on these. Can I email you these files so that you can look at them ? I don't mean to dump this stuff on you. I figured since you're an expert you might just need couple of mins to figure things out.

I really appreciate it.
masaimara
0
 
LVL 2

Expert Comment

by:CallumT
ID: 10905665
No problems.

But I don't think a comparison of the control files will help. What stage did you get up to with the clone of the database ???
Did you manage to start the DEV db ?
0
 

Author Comment

by:masaimara
ID: 10905710
I copied PROD's 8.1.7 Oracle Home and got the trace file. I wanted to take your opinion before proceeding further. What is your email address ?
0
 
LVL 2

Expert Comment

by:CallumT
ID: 10909307
I'd rather not post emal address in forums  ;-)
Howver, if you don'r mind posting yours I''ll contact you tomoorow(Monday New Zaland time) when I get into the office .
0
 
LVL 2

Expert Comment

by:CallumT
ID: 10915673
In the meantime your controlfile should look something like this after editing all the comments out ..


STARTUP NOMOUNT pfile=$DBA/pfile/initaasp.ora
CREATE CONTROLFILE SET DATABASE "AASP" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 1022
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/db/aasp/redo/aasp_redo_1a.dbf'  SIZE 10M,
  GROUP 2 '/db/aasp/redo/aasp_redo_2a.dbf'  SIZE 10M,
  GROUP 3 '/db/aasp/redo/aasp_redo_3a.dbf'  SIZE 10M,
  GROUP 4 '/db/aasp/redo/aasp_redo_4a.dbf'  SIZE 10M
DATAFILE
  '/db/aasp/system/aasp_system_01.dbf',
  '/db/aasp/rollback/aasp_rollback_01.dbf',
  '/db/aasp/temp/aasp_temp_01.dbf',
  '/db/aasp/data1/aasp_tools_01.dbf',
  '/db/aasp/data1/aasp_users_01.dbf',
  '/db/aasp/data1/aasp_aas_data_01.dbf',
  '/db/aasp/data1/aasp_aas_index_01.dbf'
CHARACTER SET AL24UTFFSS
;
ALTER DATABASE OPEN RESETLOGS;
0
 

Author Comment

by:masaimara
ID: 10919855
You are right. It is not a good idea to ask for email addresses - that was stupid of me :)

I am in the process of copying all of the dbf files (in backup controlfile) from PROD to DEV. Everything is going well as per your plan so far. I will let you know as soon as I get it done.

Thanks,
masaimara
0
 

Author Comment

by:masaimara
ID: 10934357
I just want to make sure that I have to say "CREATE CONTROLFILE SET DATABASE "AASP" RESETLOGS NOARCHIVELOG" and not REUSE DATABASE in the new controlfile. Please let me know.
Thanks.
0
 

Author Comment

by:masaimara
ID: 10935277
I used CREATE CONTROLFILE REUSE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG to create control files. I get the following error:

CREATE CONTROLFILE REUSE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01160: file is not a data file
ORA-01110: data file : '/u1/oradev/data/log01a.dbf'

This is what is see (below) after I copied all the files from PROD backup. How do I go about fixing this issue ?

Also, do I need the temp dbf files for the new db ?

lrwxrwxrwx   1 root     dba           23 Apr 26 17:01 log1a.dbf -> .log01a.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:32 log1b.dbf -> .log01b.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:32 log2a.dbf -> .log02a.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:37 log2b.dbf -> .log02b.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:37 log3a.dbf -> .log03a.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:37 log3b.dbf -> .log03b.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:37 log4a.dbf -> .log04a.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:37 log4b.dbf -> .log04b.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:37 log5a.dbf -> .log05a.dbf::cdev:vxfs:
lrwxrwxrwx   1 root     dba           23 Apr 26 17:37 log5b.dbf -> .log05b.dbf::cdev:vxfs:
-rwxr-xr-x  1 root     dba      104865792 Apr 23 21:10 temp1.dbf
-rw-r-----   1 root     dba      1153425408 Apr 23 21:10 temp2.dbf

Thanks,
masaimara
0
 

Author Comment

by:masaimara
ID: 10935874
Sorry for all these comments. I did manage to get rid of the links and create physical files but I still get this error with REUSE SET and just SET:

CREATE CONTROLFILE REUSE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01160: file is not a data file
ORA-01110: data file : '/u1/oradev/data/log1a.dbf'


SQL> @initDEV.sql
CREATE CONTROLFILE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01160: file is not a data file
ORA-01110: data file : '/u1/oradev/data/log1a.dbf'

FYI
When I do $ file log1a.dbf
this is what I see
log1a.dbf:     data

Any ideas ?
0
 
LVL 2

Expert Comment

by:CallumT
ID: 10936272
It looks like you have got symbolic links pointing to the real redo logs. If you are restorng from Cold Backup, then you don't need the redo logs. The create_controlfile will create them for you.

Can you post the first few lines of youer controlfile.sql (include a few lines after the DATAFILE line)

0
 
LVL 2

Expert Comment

by:CallumT
ID: 10936280
..and yes you will need the tempfiles too.

There should be a bit in the conrtrolfile.sql that references the TEMP data-files
0
 

Author Comment

by:masaimara
ID: 10939513
You mean I can delete the log files from the directory ?

Create controlfile attached below. Please let me know how I should proceed from here.

STARTUP NOMOUNT pfile=/u3/oracle/devdb/8.1.7/dbs/initTEST.ora
CREATE CONTROLFILE SET DATABASE "DEV" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 500
    MAXINSTANCES 1
    MAXLOGHISTORY 1815
LOGFILE
  GROUP 1 (
    '/u3/oracle/devdata/log01a.dbf',
    '/u3/oracle/devdata/log01b.dbf'
  ) SIZE 20979200,
  GROUP 2 (
    '/u3/oracle/devdata/log02a.dbf',
    '/u3/oracle/devdata/log02b.dbf'
  ) SIZE 20979200,
  GROUP 3 (
    '/u3/oracle/devdata/log03a.dbf',
    '/u3/oracle/devdata/log03b.dbf'
  ) SIZE 20979200,
  GROUP 4 (
    '/u3/oracle/devdata/log04a.dbf',
    '/u3/oracle/devdata/log04b.dbf'
  ) SIZE 20979200,
  GROUP 5 (
    '/u3/oracle/devdata/log05a.dbf',
    '/u3/oracle/devdata/log05b.dbf'
  ) SIZE 20979200
DATAFILE
  '/u3/oracle/devdata/system01.dbf',
  '/u3/oracle/devdata/system02.dbf',
  '/u3/oracle/devdata/system03.dbf',
  '/u3/oracle/devdata/system04.dbf',
  '/u3/oracle/devdata/system05.dbf',
  '/u5/oracle/devdata/abmd01.dbf',
  '/u5/oracle/devdata/abmx01.dbf',
  ...
  ...
CHARACTER SET WE8ISO8859P1
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
0
 

Author Comment

by:masaimara
ID: 10940922
FYI, In the error above it might show '/u1/oradev/data/log1a.dbf'. I changed it to the right directory later on '/u3/oracle/devdata/log1a.dbf' and I still get the error.
0
 
LVL 2

Expert Comment

by:CallumT
ID: 10943790
Check that the db_block_size in the DEV file is the same as the db_block_size of your PROD database.

Copy the redologs from th Cold-Backup to the directory specified in the control_file.sql (just to be on the safe side0
0
 

Author Comment

by:masaimara
ID: 10979190
CallumT,

It worked !!! I had one of the logfiles under the Datafiles section of the Create Controlfile script. I could create the controlfile once I got rid of that. Even though DEV was restored from a cold backup it asked for arch files during recovery. I applied the required logs and it was recovered.

Thanks and Regards,
masaimara
0
 
LVL 2

Expert Comment

by:CallumT
ID: 10981726
Great..

One last thing..make sure you get a good backup of the new Dev environment   ;-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question