| 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
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 #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
*.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'
[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 #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
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
