ptreves
asked on
Bringing Tablespaces Back ONLINE PBs
Hello,
I did a fresh import of my database.
I put my user_data and alcieind tablespaces offline and renamed 2 user datafiles and renamed and moved a 3rd datafile as follows:
-----
1) alter tablespace user_data offline normal;
2) alter tablespace user_data rename datafile '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/U01AP P01ORACLEL NORADATAOR ACLELNUSR1 ORCL.ORA' to '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/USR1O RCL.ORA';
3) alter tablespace user_data rename datafile '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/U01AP P01ORACLEL NORADATAOR ACLELNUSR2 ORCL.ORA' to '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/USR2O RCL.ORA';
-----
------
Now, I want to rename a 3rd datafile and changes its location, I already made a copy at the OS level. I type the following command:
-----
4) alter database rename datafile '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/DISK1 ORADATAALC IND01.ORA' to '/DISK1/ORADATA/ALCIND01.O RA';
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
-----
I also try to bring my 2 renamed datafiles online as follows:
5) alter database recover datafile '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/USR1O RCL.ORA';
6) alter database recover datafile '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/USR2O RCL.ORA';
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 11: '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/USR1O RCL.ORA'
ORA-01122: database file 11 failed verification check
ORA-01110: data file 11: '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/USR1O RCL.ORA'
ORA-01251: Unknown File Header Version read for file number 11
=====
1) How can I get my database back online normally ?
I tried commands such as;
1) alter database recover datafile '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/USR1O RCL.ORA';
2) alter database recover datafile '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/USR2O RCL.ORA';
3) alter database recover datafile '/DISK1/ORADATA/ALCIND01.O RA'
4) alter database datafile '/DISK1/ORADATA/ALCIND01.O RA' online;
but get error messages....
What should I do ?
PT
I did a fresh import of my database.
I put my user_data and alcieind tablespaces offline and renamed 2 user datafiles and renamed and moved a 3rd datafile as follows:
-----
1) alter tablespace user_data offline normal;
2) alter tablespace user_data rename datafile '/u01/app01/oracleln/produ
3) alter tablespace user_data rename datafile '/u01/app01/oracleln/produ
-----
------
Now, I want to rename a 3rd datafile and changes its location, I already made a copy at the OS level. I type the following command:
-----
4) alter database rename datafile '/u01/app01/oracleln/produ
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
-----
I also try to bring my 2 renamed datafiles online as follows:
5) alter database recover datafile '/u01/app01/oracleln/produ
6) alter database recover datafile '/u01/app01/oracleln/produ
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 11: '/u01/app01/oracleln/produ
ORA-01122: database file 11 failed verification check
ORA-01110: data file 11: '/u01/app01/oracleln/produ
ORA-01251: Unknown File Header Version read for file number 11
=====
1) How can I get my database back online normally ?
I tried commands such as;
1) alter database recover datafile '/u01/app01/oracleln/produ
2) alter database recover datafile '/u01/app01/oracleln/produ
3) alter database recover datafile '/DISK1/ORADATA/ALCIND01.O
4) alter database datafile '/DISK1/ORADATA/ALCIND01.O
but get error messages....
What should I do ?
PT
to bring the data files back online:
you DON'T need to recover them...you can bring them back online just as the way you offline them:
alter tablespace user_data online;
you DON'T need to recover them...you can bring them back online just as the way you offline them:
alter tablespace user_data online;
ASKER
Hello,
This is the error I get:
-----
SQL> alter tablespace user_data online;
alter tablespace user_data online
*
ERROR at line 1:
ORA-01122: database file 11 failed verification check
ORA-01110: data file 11: '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/USR1O RCL.ORA'
ORA-01251: Unknown File Header Version read for file number 11
-----
SQL> alter tablespace alcieind online;
alter tablespace alcieind online
*
ERROR at line 1:
ORA-01122: database file 13 failed verification check
ORA-01110: data file 13: '/DISK1/ORADATA/ALCIND01.O RA'
ORA-01251: Unknown File Header Version read for file number 13
-----
1) What should I do now to get theses tablespaces back online ?
PT
This is the error I get:
-----
SQL> alter tablespace user_data online;
alter tablespace user_data online
*
ERROR at line 1:
ORA-01122: database file 11 failed verification check
ORA-01110: data file 11: '/u01/app01/oracleln/produ
ORA-01251: Unknown File Header Version read for file number 11
-----
SQL> alter tablespace alcieind online;
alter tablespace alcieind online
*
ERROR at line 1:
ORA-01122: database file 13 failed verification check
ORA-01110: data file 13: '/DISK1/ORADATA/ALCIND01.O
ORA-01251: Unknown File Header Version read for file number 13
-----
1) What should I do now to get theses tablespaces back online ?
PT
You have corrupted the files by doing RECOVERY command on those files.
After import, you should do this to rename files:
1. copy the files to the desired disks.
2. STARTUP MOUNT the database instead of open database.
3. rename the files using "ALTER DATABASE RENAME FILE " command instead of offline tablespace first....
After import, you should do this to rename files:
1. copy the files to the desired disks.
2. STARTUP MOUNT the database instead of open database.
3. rename the files using "ALTER DATABASE RENAME FILE " command instead of offline tablespace first....
ASKER
Hello,
Given the situation now,
Should I delete the existing datafiles at the OS level ? + restart a new import ?
PT
Given the situation now,
Should I delete the existing datafiles at the OS level ? + restart a new import ?
PT
Yes.
recreate new ones before fresh import...
recreate new ones before fresh import...
ASKER
Hello,
I tried to rename the datafiles using Alter database rename file......, before coping the Datafiles at the OS level after doing a successfull import. I tried to shutdown the DB and then copy my OS datafiles + startup again. I get the following messages:
-----
SQL> connect system/manager1 as sysdba
Connected to an idle instance.
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted
S
-----
What now ?
PT
I tried to rename the datafiles using Alter database rename file......, before coping the Datafiles at the OS level after doing a successfull import. I tried to shutdown the DB and then copy my OS datafiles + startup again. I get the following messages:
-----
SQL> connect system/manager1 as sysdba
Connected to an idle instance.
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted
S
-----
What now ?
PT
use "Shutdown immediate" instead.
ASKER
Hello,
I did try that, but I still get the same error message:
-----
SQL> shutdown
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted
SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted
What now ?
PT
I did try that, but I still get the same error message:
-----
SQL> shutdown
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted
SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01090: shutdown in progress - connection is not permitted
What now ?
PT
ok, something fell through the crack during the process...
force the database down by shutdown the machine.
1. create the empty database.
2. do the import fresh.
3. copy the database data files to the desired locations.
4. STARTUP MOUNT the database , NOT open database;
5. rename the data files.
5. alter database open;
I don't see how possibly this can go wrong...
force the database down by shutdown the machine.
1. create the empty database.
2. do the import fresh.
3. copy the database data files to the desired locations.
4. STARTUP MOUNT the database , NOT open database;
5. rename the data files.
5. alter database open;
I don't see how possibly this can go wrong...
ASKER
Hello,
When I first invoke sqlplus, I type STARTUP, I guess I should type STARTUP MOUNT.
Is that it ?
PT
When I first invoke sqlplus, I type STARTUP, I guess I should type STARTUP MOUNT.
Is that it ?
PT
ASKER
HEllo,
I did all that.
When I do the last command : Alter database open
I get the following output:
-----
SQL> alter database rename file '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/U01AP P01ORACLEL NORADATAOR ACLELNUSR2 ORCL.ORA' to '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/USR2O RCL.ORA';
Database altered.
SQL> alter database rename file '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/DISK1 ORADATAALC IND01.ORA' to '/DISK1/ORADATA/ALCIND01.O RA';
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 11 failed verification check
ORA-01110: data file 11: '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/USR1O RCL.ORA'
ORA-01203: wrong incarnation of this file - wrong creation SCN
=====
1) What now ?
Your toughts ...
PT
I did all that.
When I do the last command : Alter database open
I get the following output:
-----
SQL> alter database rename file '/u01/app01/oracleln/produ
Database altered.
SQL> alter database rename file '/u01/app01/oracleln/produ
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 11 failed verification check
ORA-01110: data file 11: '/u01/app01/oracleln/produ
ORA-01203: wrong incarnation of this file - wrong creation SCN
=====
1) What now ?
Your toughts ...
PT
ASKER
HEllo,
Shutingdown at starting up:
I get:
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01122: database file 11 failed verification check
ORA-01110: data file 11: '/u01/app01/oracleln/produ ct/9.2.0.4 /dbs/USR1O RCL.ORA'
ORA-01203: wrong incarnation of this file - wrong creation SCN
What now ?
PT
Shutingdown at starting up:
I get:
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01122: database file 11 failed verification check
ORA-01110: data file 11: '/u01/app01/oracleln/produ
ORA-01203: wrong incarnation of this file - wrong creation SCN
What now ?
PT
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ptreves---->
Now, I want to rename a 3rd datafile and changes its location, I already made a copy at the OS level. I type the following command:
-----
4) alter database rename datafile '/u01/app01/oracleln/produ
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
-----
the correct command should be:
alter database rename file '/u01/app01/oracleln/produ