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: 209
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: 7356
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: 209
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: 209
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: 25948
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: 209
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: 7356
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 messageGo to next message
juniordbanewbie
Messages: 209
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
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 Go to previous message
juniordbanewbie
Messages: 209
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
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

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
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:
  


Current Time: Thu Apr 19 04:56:02 CDT 2018

Total time taken to generate the page: 0.01640 seconds