Shulou(Shulou.com)06/02 Report--

Recently, the production system is required from 10.2.0.5. I did a lot of preparatory work to upgrade to 11.2.0.2.0. I built my own test environment at the weekend and practiced following the upgrade steps I had prepared. In addition to the basic check, download the latest psu from Metalink, confirm with the company's senior dba, and provide it to the customer. In this way, the database is upgraded to 11.2.0.2.10

There are mainly the following steps: 1.new ORACLE_HOME (11g), old ORACLE_HOME (10g)-these need to be provided to the customer in advance as the basic contract 2.install oracle software 11.2.0.2.0 on production-there are a total of 7 DB and time is tight So prepare for full 3.apply DB patch additional if needed-- patch details in Note:16056267.8 11.2.0.2.10 (Apr 2013) Database Patch Set Update (PSU) Patch:16056267 4.init parameter tuning-- this is also required, there are three main aspects of 11g parameters that may be out of date, such as user_dump. Some of the new parameters in 11g, such as diag...., need to be tuned. Whether the implicit tuning parameters in 10g need to be retained... 5.OS kernel tuning-system-level tuning is also necessary. You need to find a special team to provide advice. 6.PET and Production compare-preparation for quasi-production environment requires sufficient preliminary testing. 7.confirm details with App team if have any concern. -- if you can't confirm some of the problems in the upgrade process, you need to find a developer or other team to coordinate.

8.full backup or cold backup-this depends on the specific implementation of the environment, ensuring adequate backup is very important, the production system to do cold backup should be very critical.

9.check if there are crontab running or scheduled. -- this needs to be considered in advance. General projects will be monitored by the system to ensure that unnecessary effects are eliminated during the upgrade process. If there is goldengate synchronization and so on, it also needs to be coordinated in advance to ensure that it will not be affected. 10.check if all database components are valid-- you can use the following sql to select substr (comp_name,1,40) comp_name, status, substr (version,1,10) version from dba_registry order by comp_name;-- the build should all be in the valid state. 11.check if all objects are valid-check that the object status should also be valid. If there are component or objects invalid, need to use utlrp.sql to recompile 12.check if duplicate objects owned by SYS and SYSTEM Schema-query from dba_objects to ensure that there are no duplicate object, under sys,system. The following is the desired result. If there is anything else, you need to check the metalink document to deal with it. Note,1030426.6

OBJECT_NAME OBJECT_TYPE

-AQ$_SCHEDULES TABLE AQ$_SCHEDULES_PRIMARY INDEX DBMS_REPCAT_AUTH PACKAGE DBMS_REPCAT_AUTH PACKAGE BODY

13.disable custom triggers-in order to eliminate the ddl effects caused by trigger, it is recommended that disable custom trigger. 14.Copy Pre-upgrade Information script. To a local folder-create a temporary folder to copy the scripts needed for the upgrade. For example, the script rdbms/admin/utlu112i.sql needs to be copied in advance from rdbms/admin under 11ghome. 15.Run Pre-upgrade Information Tool on target database (10g)-spool to do the pre-upgrade check. Sqlplus / as sysdba spool pre_upgrade_info.log @ utlu112i.sql spool off 16.Check the output of the Pre-Upgrade Information Tool-here you need to pay attention to the warning message, clear the recyclebin and make sure that the timezone file must be v4, or the upgrade will fail. Also pay attention to some parameters and modify them according to the prompts. 17.backup / etc/oratab,tnsnames.ora,listener.ora sqlnet.ora,password file, pfile,spfile,profile-- back up the necessary files as a backup of rollback. 18.Copy Network files to 11g Home and modify the home in listener-copy tnsnames.ora,listener.ora to the corresponding directory of 11g home. 19.Copy password file to 11g Home-- copy the password file 20.Prepare a separate .bash _ profile to set 11g environment variables-- profile file needs to be modified accordingly.

21.Collect dictionary stats-use the following package to EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; 22.Stop Listener services of the target database. -- stop listening and start upgrading 23.Shutdown the target database (10g)-- down off the database and clear the cache, so that running scripts will cause a lot of interference and speed up. 24.Set working environment to 11g ORACLE_HOME. Make sure multiple ORACLE HOMEs are not in the PATH variable. 25.Copy spfile to 11g home. 26.stop source (10g) DB,Start the db in nomount,mount mode, and make necessary changes/adjustments in spfile with scope=spfile. -- in this step, you need to modify the compatible parameters and adjust some parameters. (prepared for parameter tuning in the previous steps) finally create spfile from pfile; 27.Shutdown and startup the db in upgrade mode. -- start the database, use spfile,-- startup upgrade 28.Run upgrade script. Exit the session after upgrade. This is the most critical script, and it takes longer to run. When the script is finished, it will automatically shutfdown immediate spool upgrade11g2.log @? / rdbms/admin/catupgrd.sql Spool off Exit 29.Verify the log for errors. Ignore "table or view not found" errors-if there are some errors, you need to check, and if some errors such as memory issue cannot be ignored, you need to rerun the script 30.Startup the db in normal mode and run post upgrade information tool. Verify the status of each db component and address the failures. -- start the database and run the following script. Spool post_upgrade_info.txt @? / rdbms/admin/utlu112s.sql Spool 31.Continue upgrade (new in 11g)-- continue to execute the script, which belongs to the post upgrade script. Spool upgrade11g2.log @? / rdbms/admin/catuppst.sql Spool off 32.Recompile invalid objects 33.enable custom triggers-- trigger 34.update / etc/oratab with 11g home of disable before enable-- changing this configuration will be used in some applications. 35.rename spfile/pfile from 10g home-guarantee that 10g parameters will not be used and that no incorrect operation will cause unnecessary trouble. 36.Start Listener services of the target database with 11g HOME. -- start monitoring after confirmation. 37.DBA sanity check (check if mv is able to refresh,db components are valid,check if there are ORA erros from logs)-- DBA first do some simple sanity test. Ensure that there are no basic mistakes before the environment is handed over to the customer. 38.Check the connectivity from client-- then test from the client to see if the connection is working. 39.Backup of DB after upgrade-- make the necessary backups. 40.upgrade rman catalog if necessary-- if you use rman for backup and recovery, you need to upgrade catalog 41.change crontab-- you can modify crontab to make it effective.

-- generally speaking, there are so many steps mentioned above. I also hope that everyone will clap the bricks and provide amendments. :)

-2013-0714: add: upgrade timezone file after the upgrade. SQL > select * from v$timezone_file

FILENAME VERSION-timezlrg_4.dat 4 needs to be upgraded to 14, or there may be some problems. Refer to document Note:977512.1:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.