Home » RDBMS Server » Backup & Recovery » Cannot drop undotbs2 when restoring 10.2.0.4 rac db to 11.2.0.4 single instance db (11.2.0.4 SE, Redhat 7.3)
Cannot drop undotbs2 when restoring 10.2.0.4 rac db to 11.2.0.4 single instance db [message #667135] Sat, 09 December 2017 22:19 Go to next message
juniordbanewbie
Messages: 176
Registered: April 2014
Senior Member
Dear all,

based on HowTo Restore RMAN Disk backups of RAC Database to Single Instance On Another Node
(Doc ID 415579.1)

Quote:


11) Now you can remove the undo tablespaces of other instances and create a new temporary tablespace to complete
the activity.
I'm supposed to drop other instances undo tablespace

SELECT param2.inst_id, inst.instance_number, inst.instance_name, param2.value, param2.issys_modifiable, param2.ordinal FROM gv$parameter2 param2 JOIN gv$instance inst ON inst.inst_id=param2.inst_id
  2  WHERE name='undo_tablespace';

   INST_ID INSTANCE_NUMBER INSTANCE_N VALUE      ISSYS_MODI    ORDINAL
---------- --------------- ---------- ---------- ---------- ----------
         1               1 svcrmdb    UNDOTBS1   IMMEDIATE           1

SYS@11.2.0.4 svcrmdb>
SYS@11.2.0.4 svcrmdb>select tablespace_name from dba_tablespaces where contents='UNDO' ORDER BY tablespace_name;

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2




DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES
ORA-604 signalled during: DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES...
[orac

SYS@11.2.0.4 svcrmdb>DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1 
ORA-00942: table or view does not exist 

alert log shows the following:


DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES
ORA-604 signalled during: DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES...

how do i proceed from here?
thanks in advance
Re: Cannot drop undotbs2 when restoring 10.2.0.4 rac db to 11.2.0.4 single instance db [message #667138 is a reply to message #667135] Sun, 10 December 2017 02:08 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Have you upgraded the database?
Re: Cannot drop undotbs2 when restoring 10.2.0.4 rac db to 11.2.0.4 single instance db [message #667140 is a reply to message #667138] Sun, 10 December 2017 03:59 Go to previous messageGo to next message
juniordbanewbie
Messages: 176
Registered: April 2014
Senior Member
No

i try to ignore but
when I try to run utlu112i_11204_009.sql

when I search
"ORA-04023: Object SYS.STANDARD Could Not Be Validated or Authorized" during database upgrade (Doc ID 984511.1)

I realize that I need to run utlu112i_11204_009.sql on the source db which is in 10.2.0.4 home.

I did try to restore 10.2.0.4 RAC db to 10.2.0.5 single instance home, even without running the preupgrade script it never prompt me such error.

look like I learn a hard lesson.

does the error come because I did not run utlu112i_11204_009.sql on 10.2.0.4 home?

many thanks in advance!
Re: Cannot drop undotbs2 when restoring 10.2.0.4 rac db to 11.2.0.4 single instance db [message #667152 is a reply to message #667140] Mon, 11 December 2017 08:09 Go to previous messageGo to next message
juniordbanewbie
Messages: 176
Registered: April 2014
Senior Member
utlu102i_2.sql
and utlu112i_11204_009.sql
Dear all
what is the conseqences of running two preupgrade script?

Iniitally I plan the upgrade path to be 10.2.0.4 to 10.2.0.5 to 12.1.02
but because we cannot install 12.1.02 on redhat 7.3 we decided to upgrade to 10.2.0.5 then to 12.1.0.2 on redhat 5.8 before transferring the datafiles to redhat 7.3

but I decided to try 11.2.0.4 since 11.2.0.4 can be installed on redhat 7.3
please take look at
it could be wrong here.

where should I go from here now?

do I need to patch something to the 11.2.0.4 home before ugprading from 10.2.0.4?

here my init parameter


*.audit_file_dest='/u01/app/oracle/admin/svcrmdb/adump'
*.compatible='10.2.0.3.0'
*.control_files='/u02/app/oracle/oradata/svcrmdb/control01.ctl','/u03/app/oracle/oradata/svcrmdb/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/svcrmdb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_keep_cache_size=209715200
*.db_name='svcrmdb'
*.db_writer_processes=2
*.dispatchers='(PROTOCOL=TCP) (SERVICE=svcrmdbXDB)'
*.filesystemio_options='asynch'
*.large_pool_size=238435456
*.open_cursors=300
*.pga_aggregate_target=1947483648
*.processes=450
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=100
*.sessions=500
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
memory_target=64100m
log_archive_dest_1='location=/u03/app/oracle/formatted_archivelog/svcrmdb'
thanks

[Updated on: Mon, 11 December 2017 08:11]

Report message to a moderator

Re: Cannot drop undotbs2 when restoring 10.2.0.4 rac db to 11.2.0.4 single instance db [message #667153 is a reply to message #667152] Mon, 11 December 2017 08:17 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
juniordbanewbie wrote on Mon, 11 December 2017 06:09
utlu102i_2.sql
and utlu112i_11204_009.sql
Dear all
what is the conseqences of running two preupgrade script?

Iniitally I plan the upgrade path to be 10.2.0.4 to 10.2.0.5 to 12.1.02
but because we cannot install 12.1.02 on redhat 7.3 we decided to upgrade to 10.2.0.5 then to 12.1.0.2 on redhat 5.8 before transferring the datafiles to redhat 7.3

but I decided to try 11.2.0.4 since 11.2.0.4 can be installed on redhat 7.3
please take look at
it could be wrong here.

where should I go from here now?

do I need to patch something to the 11.2.0.4 home before ugprading from 10.2.0.4?

here my init parameter


*.audit_file_dest='/u01/app/oracle/admin/svcrmdb/adump'
*.compatible='10.2.0.3.0'
*.control_files='/u02/app/oracle/oradata/svcrmdb/control01.ctl','/u03/app/oracle/oradata/svcrmdb/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/svcrmdb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_keep_cache_size=209715200
*.db_name='svcrmdb'
*.db_writer_processes=2
*.dispatchers='(PROTOCOL=TCP) (SERVICE=svcrmdbXDB)'
*.filesystemio_options='asynch'
*.large_pool_size=238435456
*.open_cursors=300
*.pga_aggregate_target=1947483648
*.processes=450
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=100
*.sessions=500
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
memory_target=64100m
log_archive_dest_1='location=/u03/app/oracle/formatted_archivelog/svcrmdb'
thanks

what does above have to do with original problem post?
Re: Cannot drop undotbs2 when restoring 10.2.0.4 rac db to 11.2.0.4 single instance db [message #667154 is a reply to message #667153] Mon, 11 December 2017 08:51 Go to previous messageGo to next message
juniordbanewbie
Messages: 176
Registered: April 2014
Senior Member
I'm just pondering why the error comes that's why.

now I understand why am I getting such error again


BEGIN
*
ERROR at line 1:
ORA-04023: Object SYS.STANDARD could not be validated or authorized


Enter value for 2: undotbs2
SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name)
*
ERROR at line 1:
ORA-04023: Object SYS.DBMS_METADATA could not be validated or authorized


at the time utlu112i_11204_009.sql is run

SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
18067544834

but when I do a restore

column current_scn FORMAT 99,999,999,999

    CURRENT_SCN
---------------
 18,025,250,930 

which is much less.

what happens here is that the script has already run at 12000 hrs and I take the archivelog at 1800hrs for restoration. How do I know that changes will be propagated.

how do i make sure changes are propaged?
ALTER SYSTEM CHECKPOINT?
am I missing some here?

thanks a lot!
Re: Cannot drop undotbs2 when restoring 10.2.0.4 rac db to 11.2.0.4 single instance db [message #667155 is a reply to message #667152] Mon, 11 December 2017 09:07 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Quote:
but because we cannot install 12.1.02 on redhat 7.3
Why not?
Re: Cannot drop undotbs2 when restoring 10.2.0.4 rac db to 11.2.0.4 single instance db [message #667156 is a reply to message #667155] Mon, 11 December 2017 09:09 Go to previous message
juniordbanewbie
Messages: 176
Registered: April 2014
Senior Member
I made a mistake

it is we cannot install 10.2.0.5 on redhat 7.3

sorry for the mistake
Previous Topic: Restore 10.2.0.4 RAC Backup to 11.2.0.4 dbhome
Next Topic: RMAN on ORA11gR2 - CONFIGURE RETENTION POLICY TO REDUNDANCY 1
Goto Forum:
  


Current Time: Sat Jan 20 06:06:03 CST 2018

Total time taken to generate the page: 0.01647 seconds