Yesterday I got a requirement to restore one of my 10g database Rman backup , now all our 10g database servers were decommissed. So I have to restore it on a 11g host . PFB steps to restore and upgrade a 10.2 version database to 11.2 version :-
1. Copy the 10g Backup to 11G server
2. Create a Pfile for to start the instance
*.audit_file_dest='/opt/oracle/admin/amit/adump'
*.audit_trail='DB_EXTENDED'
*.compatible='11.2.0.0.0' #######s set compatible for 11.2 version######
*.control_files='+DATA_DG/amit/controlfile/control01.ctl','+DATA_DG/amit/controlfile/control02.ctl'#Set by RMAN
*.db_block_size=8192
*.db_create_file_dest='+DATA_DG'
*.db_create_online_log_dest_1='+DATA_DG'
*.db_domain='domain.com'
*.db_name='amit'#Reset to original value by RMAN
*.db_recovery_file_dest='+DATA_DG'
*.db_recovery_file_dest_size=7000M
*.diagnostic_dest='/opt/oracle'
*.open_cursors=300
*.pga_aggregate_target=2G
*.processes=550
*.sga_target=5G
*.undo_tablespace='UNDO' ##### as the backup is of 10g database , we have to keep it UNDO#####
3. Start the instance with this pfile
export ORACLE_SID=amit
sqlplus "/ as sysdba"
startup nomount pfile='/ora_backup/test/amit/initamit.ora'
create spfile from pfile='/ora_backup/test/amit/initamit.ora';
shu immediate
startup nomount
SQL> ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size 2262656 bytes
Variable Size 1040189824 bytes
Database Buffers 4278190080 bytes
Redo Buffers 24088576 bytes
4. Login to RMAN prompt and restore the controlfile
export ORACLE_SID=amit
rman log=/ora_backup/test/amit/restore.log << EOF
connect target /
restore controlfile from '/ora_backup/test/amit/ctl_c-1234324679-20140701-00';
Starting restore at 2015-07-24 06:17:21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=505 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA_DG/amit/controlfile/control01.ctl
output file name=+DATA_DG/amit/controlfile/control02.ctl
Finished restore at 2015-07-24 06:17:25
5. Mount the database with this control file
alter database mount;
database mounted
released channel: ORA_DISK_1
6. Catalog the 10g backup pieces
catalog start with '/ora_backup/test/amit/';
Starting implicit crosscheck backup at 2015-07-24 06:17:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=505 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=523 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=559 device type=DISK
allocated channel: ORA_DISK_4
.
.
.
File Name: /ora_backup/test/amit/hot_amit_30309_1_20140701
File Name: /ora_backup/test/amit/hot_amit_30310_1_20140701
File Name: /ora_backup/test/amit/hot_amit_30311_1_20140701
File Name: /ora_backup/test/amit/hot_amit_30312_1_20140701
7. Restore and recover the database using sequence number+1
run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
SET NEWNAME FOR DATAFILE 1 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 2 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 3 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 4 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 5 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 6 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 7 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 8 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 9 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 10 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 11 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 12 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 13 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 14 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 15 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 16 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 17 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 18 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 19 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 20 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 21 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 22 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 23 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 24 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 25 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 26 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 27 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 28 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 29 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 30 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 31 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 32 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 33 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 34 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 35 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 36 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 37 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 38 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 39 TO '+DATA_DG';
restore database until sequence 10123;
switch datafile all;
recover database until sequence 10123;
}
allocated channel: c1
channel c1: SID=505 device type=DISK
allocated channel: c2
channel c2: SID=523 device type=DISK
allocated channel: c3
channel c3: SID=559 device type=DISK
allocated channel: c4
channel c4: SID=577 device type=DISK
allocated channel: c5
channel c5: SID=613 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
.
.
.
Starting restore at 2015-07-24 06:18:01
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00005 to +DATA_DG
channel c1: restoring datafile 00006 to +DATA_DG
channel c1: restoring datafile 00021 to +DATA_DG
channel c1: restoring datafile 00038 to +DATA_DG
channel c1: reading from backup piece /ora_backup/test/amit/hot_amit_30307_1_20140701
channel c2: starting datafile backup set restore
.
.
.Starting recover at 2015-07-24 07:08:30
starting media recovery
channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=10123
.
.
.
media recovery complete, elapsed time: 00:00:08
Finished recover at 2015-07-24 07:11:21
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: c5
8. Rename the online log files according to the target
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
alter database rename file '/u02/oracle/amit/redo.g04.m01.log' to '+DATA_DG/amit/ONLINELOG/group41.log';
alter database rename file '/u03/oracle/amit/redo.g04.m02.log' to '+DATA_DG/amit/ONLINELOG/group42.log';
alter database rename file '/u02/oracle/amit/redo.g05.m01.log' to '+DATA_DG/amit/ONLINELOG/group51.log';
alter database rename file '/u03/oracle/amit/redo.g05.m02.log' to '+DATA_DG/amit/ONLINELOG/group52.log';
9 . open the database with upgrade option
ALTER DATABASE OPEN RESETLOGS UPGRADE;
10. Create Undo tablespace for 11g database and change the default to the new one
create undo tablespace UNDOTBS1 datafile '+DATA_DG' size 1g;
alter system set undo_tablespace=UNDOTBS1;
11. Create New temporary tablespace and change the default one
create temporary tablespace TEMP1 tempfile '+DATA_DG' size 1g;
alter database default temporary tablespace temp1;
drop tablespace temp including contents and datafiles;
12. Now run the Catupgrd.sql script, it will error out as the preupgrade tasks were not applied on 10g backup
shu immediate
startup upgrade
SQL> @?/rdbms/admin/catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The first time this script is run, there should be no error messages
DOC> generated; all normal upgrade error messages are suppressed.
DOC>
DOC> If this script is being re-run after correcting some problem, then
DOC> expect the following error which is not automatically suppressed:
DOC>
DOC> ORA-00001: unique constraint () violated
DOC> possibly in conjunction with
DOC> ORA-06512: at "", line NN
DOC>
DOC> These errors will automatically be suppressed by the Database Upgrade
DOC> Assistant (DBUA) when it re-runs an upgrade.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS. Disconnect
DOC> and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database server version is not correct for this script.
DOC> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", and use
DOC> a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database has not been opened for UPGRADE.
DOC>
DOC> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the Oracle Database Vault option is TRUE. Upgrades cannot
DOC> be run with the Oracle Database Vault option set to TRUE since
DOC> AS SYSDBA connections are restricted.
DOC>
DOC> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", relink
DOC> the server without the Database Vault option, and restart the server
DOC> using UPGRADE mode.
DOC>
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if Database Vault is installed in the database but the Oracle
DOC> Label Security option is FALSE. To successfully upgrade Oracle
DOC> Database Vault, the Oracle Label Security option must be TRUE.
DOC>
DOC> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT",
DOC> relink the server with the OLS option (but without the Oracle Database
DOC> Vault option) and restart the server using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if bootstrap migration is in progress and logminer clients
DOC> require utlmmig.sql to be run next to support this redo stream.
DOC>
DOC> Run utlmmig.sql
DOC> then (if needed)
DOC> restart the database using UPGRADE and
DOC> rerun the upgrade script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following error is generated if the pre-upgrade tool has not been
DOC> run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:
DOC>
DOC> SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC> o Action:
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC> Revert to the original oracle home and start the database.
DOC> Run pre-upgrade tool against the database.
DOC> Review and take appropriate actions based on the pre-upgrade
DOC> output before opening the datatabase in the new software version.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
Session altered.
Table created.
Table altered.
SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
*
ERROR at line 1:
ORA-01722: invalid number
To remove this error , Please see below link
http://amit7oracledba.blogspot.com/2015/06/ora-01722-during-upgrade-102-to-112_30.html
we have to disable the data vault option and update the timezone detail of current Database server to this Restored10g database.
13. Once both Errors removed , we will again run the Catupgrd.sql to upgrade the database , PFB output :-
...
.
.
200 -- Output final actions time
201 IF display_xml THEN
202 DBMS_OUTPUT.PUT_LINE (
203 '" upgradeTime="' || substr(time_result,5,8) ||
204 '">');
205 ELSE
206 DBMS_OUTPUT.PUT_LINE('.' ||
207 LPAD(' ',46) || ' ' ||
208 LPAD(' ',15) ||
209 LPAD(substr(time_result,5,8),10));
210 END IF;
211 END IF; -- actions_end_time is not null
212
213 IF up_end_time IS NOT NULL THEN
214 elapsed_time := up_end_time - up_start_time;
215 time_result := to_char(elapsed_time);
216 IF display_xml THEN
217 DBMS_OUTPUT.PUT_LINE('');
219 ELSE
220 DBMS_OUTPUT.PUT_LINE('Total Upgrade Time: ' ||
221 substr(time_result, 5,8));
222 END IF;
223 ELSE
224 IF display_xml THEN
225 DBMS_OUTPUT.PUT_LINE('');
226 ELSE
227 DBMS_OUTPUT.PUT_LINE('Upgrade Incomplete');
228 END IF;
229 END IF;
230 IF display_xml THEN
231 DBMS_OUTPUT.PUT_LINE('
');232 DBMS_OUTPUT.PUT_LINE('
');233 END IF;
234 END;
235 /
.
Oracle Database 11.2 Post-Upgrade Status Tool 07-27-2015 02:26:47
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:24:14
Final Actions
. 00:00:28
Total Upgrade Time: 00:24:47
PL/SQL procedure successfully completed.
SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON
SQL> commit;
Commit complete.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
1. Copy the 10g Backup to 11G server
2. Create a Pfile for to start the instance
*.audit_file_dest='/opt/oracle/admin/amit/adump'
*.audit_trail='DB_EXTENDED'
*.compatible='11.2.0.0.0' #######s set compatible for 11.2 version######
*.control_files='+DATA_DG/amit/controlfile/control01.ctl','+DATA_DG/amit/controlfile/control02.ctl'#Set by RMAN
*.db_block_size=8192
*.db_create_file_dest='+DATA_DG'
*.db_create_online_log_dest_1='+DATA_DG'
*.db_domain='domain.com'
*.db_name='amit'#Reset to original value by RMAN
*.db_recovery_file_dest='+DATA_DG'
*.db_recovery_file_dest_size=7000M
*.diagnostic_dest='/opt/oracle'
*.open_cursors=300
*.pga_aggregate_target=2G
*.processes=550
*.sga_target=5G
*.undo_tablespace='UNDO' ##### as the backup is of 10g database , we have to keep it UNDO#####
3. Start the instance with this pfile
export ORACLE_SID=amit
sqlplus "/ as sysdba"
startup nomount pfile='/ora_backup/test/amit/initamit.ora'
create spfile from pfile='/ora_backup/test/amit/initamit.ora';
shu immediate
startup nomount
SQL> ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size 2262656 bytes
Variable Size 1040189824 bytes
Database Buffers 4278190080 bytes
Redo Buffers 24088576 bytes
export ORACLE_SID=amit
rman log=/ora_backup/test/amit/restore.log << EOF
connect target /
restore controlfile from '/ora_backup/test/amit/ctl_c-1234324679-20140701-00';
Starting restore at 2015-07-24 06:17:21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=505 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA_DG/amit/controlfile/control01.ctl
output file name=+DATA_DG/amit/controlfile/control02.ctl
Finished restore at 2015-07-24 06:17:25
5. Mount the database with this control file
alter database mount;
database mounted
released channel: ORA_DISK_1
6. Catalog the 10g backup pieces
catalog start with '/ora_backup/test/amit/';
Starting implicit crosscheck backup at 2015-07-24 06:17:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=505 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=523 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=559 device type=DISK
allocated channel: ORA_DISK_4
.
.
.
File Name: /ora_backup/test/amit/hot_amit_30309_1_20140701
File Name: /ora_backup/test/amit/hot_amit_30310_1_20140701
File Name: /ora_backup/test/amit/hot_amit_30311_1_20140701
File Name: /ora_backup/test/amit/hot_amit_30312_1_20140701
7. Restore and recover the database using sequence number+1
run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
SET NEWNAME FOR DATAFILE 1 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 2 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 3 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 4 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 5 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 6 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 7 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 8 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 9 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 10 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 11 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 12 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 13 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 14 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 15 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 16 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 17 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 18 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 19 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 20 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 21 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 22 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 23 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 24 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 25 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 26 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 27 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 28 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 29 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 30 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 31 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 32 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 33 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 34 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 35 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 36 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 37 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 38 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 39 TO '+DATA_DG';
restore database until sequence 10123;
switch datafile all;
recover database until sequence 10123;
}
allocated channel: c1
channel c1: SID=505 device type=DISK
allocated channel: c2
channel c2: SID=523 device type=DISK
allocated channel: c3
channel c3: SID=559 device type=DISK
allocated channel: c4
channel c4: SID=577 device type=DISK
allocated channel: c5
channel c5: SID=613 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
.
.
.
Starting restore at 2015-07-24 06:18:01
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00005 to +DATA_DG
channel c1: restoring datafile 00006 to +DATA_DG
channel c1: restoring datafile 00021 to +DATA_DG
channel c1: restoring datafile 00038 to +DATA_DG
channel c1: reading from backup piece /ora_backup/test/amit/hot_amit_30307_1_20140701
channel c2: starting datafile backup set restore
.
.
.Starting recover at 2015-07-24 07:08:30
starting media recovery
channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=10123
.
.
.
media recovery complete, elapsed time: 00:00:08
Finished recover at 2015-07-24 07:11:21
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: c5
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
alter database rename file '/u02/oracle/amit/redo.g04.m01.log' to '+DATA_DG/amit/ONLINELOG/group41.log';
alter database rename file '/u03/oracle/amit/redo.g04.m02.log' to '+DATA_DG/amit/ONLINELOG/group42.log';
alter database rename file '/u02/oracle/amit/redo.g05.m01.log' to '+DATA_DG/amit/ONLINELOG/group51.log';
alter database rename file '/u03/oracle/amit/redo.g05.m02.log' to '+DATA_DG/amit/ONLINELOG/group52.log';
9 . open the database with upgrade option
ALTER DATABASE OPEN RESETLOGS UPGRADE;
10. Create Undo tablespace for 11g database and change the default to the new one
create undo tablespace UNDOTBS1 datafile '+DATA_DG' size 1g;
alter system set undo_tablespace=UNDOTBS1;
11. Create New temporary tablespace and change the default one
create temporary tablespace TEMP1 tempfile '+DATA_DG' size 1g;
alter database default temporary tablespace temp1;
drop tablespace temp including contents and datafiles;
12. Now run the Catupgrd.sql script, it will error out as the preupgrade tasks were not applied on 10g backup
shu immediate
startup upgrade
SQL> @?/rdbms/admin/catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The first time this script is run, there should be no error messages
DOC> generated; all normal upgrade error messages are suppressed.
DOC>
DOC> If this script is being re-run after correcting some problem, then
DOC> expect the following error which is not automatically suppressed:
DOC>
DOC> ORA-00001: unique constraint (
DOC> possibly in conjunction with
DOC> ORA-06512: at "
DOC>
DOC> These errors will automatically be suppressed by the Database Upgrade
DOC> Assistant (DBUA) when it re-runs an upgrade.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS. Disconnect
DOC> and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database server version is not correct for this script.
DOC> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", and use
DOC> a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database has not been opened for UPGRADE.
DOC>
DOC> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the Oracle Database Vault option is TRUE. Upgrades cannot
DOC> be run with the Oracle Database Vault option set to TRUE since
DOC> AS SYSDBA connections are restricted.
DOC>
DOC> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", relink
DOC> the server without the Database Vault option, and restart the server
DOC> using UPGRADE mode.
DOC>
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if Database Vault is installed in the database but the Oracle
DOC> Label Security option is FALSE. To successfully upgrade Oracle
DOC> Database Vault, the Oracle Label Security option must be TRUE.
DOC>
DOC> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT",
DOC> relink the server with the OLS option (but without the Oracle Database
DOC> Vault option) and restart the server using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if bootstrap migration is in progress and logminer clients
DOC> require utlmmig.sql to be run next to support this redo stream.
DOC>
DOC> Run utlmmig.sql
DOC> then (if needed)
DOC> restart the database using UPGRADE and
DOC> rerun the upgrade script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following error is generated if the pre-upgrade tool has not been
DOC> run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:
DOC>
DOC> SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC> o Action:
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC> Revert to the original oracle home and start the database.
DOC> Run pre-upgrade tool against the database.
DOC> Review and take appropriate actions based on the pre-upgrade
DOC> output before opening the datatabase in the new software version.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
Session altered.
Table created.
Table altered.
SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
*
ERROR at line 1:
ORA-01722: invalid number
http://amit7oracledba.blogspot.com/2015/06/ora-01722-during-upgrade-102-to-112_30.html
we have to disable the data vault option and update the timezone detail of current Database server to this Restored10g database.
13. Once both Errors removed , we will again run the Catupgrd.sql to upgrade the database , PFB output :-
...
.
.
200 -- Output final actions time
201 IF display_xml THEN
202 DBMS_OUTPUT.PUT_LINE (
203 '" upgradeTime="' || substr(time_result,5,8) ||
204 '">');
205 ELSE
206 DBMS_OUTPUT.PUT_LINE('.' ||
207 LPAD(' ',46) || ' ' ||
208 LPAD(' ',15) ||
209 LPAD(substr(time_result,5,8),10));
210 END IF;
211 END IF; -- actions_end_time is not null
212
213 IF up_end_time IS NOT NULL THEN
214 elapsed_time := up_end_time - up_start_time;
215 time_result := to_char(elapsed_time);
216 IF display_xml THEN
217 DBMS_OUTPUT.PUT_LINE('
219 ELSE
220 DBMS_OUTPUT.PUT_LINE('Total Upgrade Time: ' ||
221 substr(time_result, 5,8));
222 END IF;
223 ELSE
224 IF display_xml THEN
225 DBMS_OUTPUT.PUT_LINE('
226 ELSE
227 DBMS_OUTPUT.PUT_LINE('Upgrade Incomplete');
228 END IF;
229 END IF;
230 IF display_xml THEN
231 DBMS_OUTPUT.PUT_LINE('
');232 DBMS_OUTPUT.PUT_LINE('
');233 END IF;
234 END;
235 /
.
Oracle Database 11.2 Post-Upgrade Status Tool 07-27-2015 02:26:47
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:24:14
Final Actions
. 00:00:28
Total Upgrade Time: 00:24:47
PL/SQL procedure successfully completed.
SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON
SQL> commit;
Commit complete.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
We can see upgrade completed successfully in approx 24 mins and version is now 11.2.0.4.0
14. After Upgrade completed successfully, Oracle Instance is down, Start up the instance and check for any invalid objects :-
SQL> ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size 2262656 bytes
Variable Size 1040189824 bytes
Database Buffers 4278190080 bytes
Redo Buffers 24088576 bytes
Database mounted.
Database opened.
SQL>
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2015-07-27 02:27:12
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2015-07-27 02:27:56
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
AMIT READ WRITE
Restore of a 10G backup to a 11g host completed successfully.
I hope this article helped you.
Thanks
Amit Rath
No comments:
Post a Comment