OraFAQ Forum: Backup & Recovery » Cannot drop undotbs2 when restoring 10.2.0.4 rac db to 11.2.0.4 single instance db

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

juniordbanewbie
Messages: 250
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

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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

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 #667138 is a reply to message #667135]

Sun, 10 December 2017 02:08

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Have you upgraded the database?

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 #667140 is a reply to message #667138]

Sun, 10 December 2017 03:59

juniordbanewbie
Messages: 250
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!

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 #667152 is a reply to message #667140]

Mon, 11 December 2017 08:09

juniordbanewbie
Messages: 250
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

Code: [Select all] [Show/ hide]

*.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

BlackSwan
Messages: 26766
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
Code: [Select all] [Show/ hide]
*.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?

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 #667154 is a reply to message #667153]

Mon, 11 December 2017 08:51

juniordbanewbie
Messages: 250
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

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
18067544834

but when I do a restore

Code: [Select all] [Show/ hide]

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?

Code: [Select all] [Show/ hide]

ALTER SYSTEM CHECKPOINT?

am I missing some here?

thanks a lot!

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 #667155 is a reply to message #667152]

Mon, 11 December 2017 09:07

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Quote:

but because we cannot install 12.1.02 on redhat 7.3

Why not?

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 #667156 is a reply to message #667155]

Mon, 11 December 2017 09:09

juniordbanewbie
Messages: 250
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

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 #667952 is a reply to message #667156]

Thu, 25 January 2018 18:28

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear all,

I discover the main issues here is not so much why I cannot drop undotbs2. I have to drop undotbs2 only after upgrading to 11.2.0.4 successfully.

the main show stopper for not being able to upgrade is because I not able to recover up to the SCN point 28067544834 where I run utlu112i_11204_009

this is from the source db where utlu112i_11204_009 is run

Code: [Select all] [Show/ hide]

Recommendations                                                                                     
**********************************************************************                              
Oracle recommends gathering dictionary statistics prior to                                          
upgrading the database.                                                                             
To gather dictionary statistics execute the following command                                       
while connected as SYSDBA:                                                                          
                                                                                                    
    EXECUTE dbms_stats.gather_dictionary_stats;                                                     
                                                                                                    
**********************************************************************                              
SQL> 
SQL> 
SQL> SELECT TO_CHAR(CURRENT_SCN) FROM V$DATABASE;
28067544834                                                                                         
SQL> 
SQL> 
SQL> SELECT TO_CHAR(dbms_flashback.get_system_change_number) FROM dual;
28067546468                                                                                         
SQL> 
SQL> 
SQL> spool off;

but problem is I only able to recover up to 28025350039 ,i.e 28025350039-1 but I need to recover up to 28067546468

Code: [Select all] [Show/ hide]

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 2 and starting SCN of 28025350039
RMAN-11003: failure during parse/execution of SQL statement: alter database recover continue
ORA-00279: change 28025350039 generated at 12/11/2017 21:55:45 needed for thread 1
ORA-00289: suggestion : /u03/app/oracle/formatted_archivelog/dwhdb/1_2_962487502.dbf
ORA-00280: change 28025350039 for thread 1 is in sequence #2
ORA-00278: log file '/u03/app/oracle/formatted_archivelog/dwhdb/1_1_962487502.dbf' no longer needed for this recovery

Recovery Manager complete.

=>ORA-00278: log file '/u03/app/oracle/formatted_archivelog/dwhdb/1_1_962487502.dbf' no longer needed for this recovery
=>this restore archivelog probably means that the database have be set to resetlog operation and will be restore to another incarnation again.
=>this is correct. because I did a db restore to this server and the SCN correspond to the time that I last restore the db to this server.
=>my first restore is done way before I run utlu112i_11204_009

I simply move all the restore archivelog to another directory and do the following again:

transfer the backup
catalog the backup
do the restore and recover again.

this time I'm able to do the restore and recover up to 28067546468

thanks

Report message to a moderator

Previous Topic:	Is there anyway that I can append dbid at the end of a controlfile trace
Next Topic:	Can restoring backup from tape to disk help me to restore spilfw

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 05:21:26 CDT 2025