Archive for 02/01/2009 - 03/01/2009
10.2.0.4 Upgrade with DataGuard
Upgrade was done from 10.2.0.3 to 10.2.0.4. Dataguard has physical standby configuration. Steps and commands may vary depending on your environment.
...... primary database commands
...... standby database commands
1. On both the primary and standby host uncompress the downloaded patchset file into a new directory.
2. On the active primary database instance, identify and record the current log thread and sequence number. Then, archive the current log:
SQL> SELECT THREAD#, SEQUENCE# FROM V$LOG WHERE STATUS='CURRENT';
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
3. Shut down the existing Oracle Database instance on the primary host with normal or immediate priority. Stop all listeners, agents and other processes running against the ORACLE_HOME
%lsnrctl stop
% emctl stop dbconsole
SQL> SHUTDOWN IMMEDIATE;
4. On the active standby instance that is running Redo Apply, query the V$LOG_HISTORY
view to verify that each log file archived in Step 2 has been received and applied to the standby database. For example:
SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
5. Once the last log has been applied stop Redo Apply cancel managed recovery on the standby database.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
6. Shutdown the standby instance on the standby host. Stop all listeners, agents and other processes running against the ORACLE_HOME.
% lsnrctl stop
% emctl stop dbconsole
SQL> SHUTDOWN IMMEDIATE;
7. Use 'runInstaller' to install the patchset. Do this on both the primary and standby ORACLE_HOME.
%./runInstaller
%./runInstaller
8. Once the patchset has been installed on on all hosts / nodes, startup the standby listener on the standby host first.
% lsnrctl start
9. Startup mount the standby database.
% sqlplus / as sysdba
SQL> startup mount
10. Place the standby database in managed recovery mode.
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE parallel 8 DISCONNECT FROM SESSION;
11. Startup the primary instance on the primary host.
% sqlplus / as sysdba
SQL> STARTUP UPGRADE
12. Ensure that remote archiving to the standby database is functioning correctly by switching logfiles on the primary and verifying that v$archive_dest.status is valid.
SQL> select dest_id, status from v$archive_dest;
SQL> alter system archive log current;
13. Check if the archived log is applied on the standby database
SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
14. Start the Database Upgrade Assistant and upgrade the primary database.
% cd /oracle/app/oracle/product/10.2.0/bin
% ./dbua
There should be a line in the /etc/oratab file for the database that will be upgraded. Otherwise you cannot see the database when you open Database Upgrade Assistant.
DBUA may give a warning about the invalid objects onthe database and you should note these invalid object before the upgrade operation. The script below can be used to see a list of invalid objects in the database.
break on c1 skip 2
set pages 999
col c1 heading 'owner' format a15
col c2 heading 'name' format a40
col c3 heading 'type' format a10
ttitle 'Invalid|Objects'
select
owner c1,
object_type c3,
object_name c2
from
dba_objects
where
status != 'VALID'
order by
owner,
object_type
;
15. Once DBUA completes make note of the current log sequence and archive the current log:
SQL> SELECT THREAD#, SEQUENCE# FROM V$LOG WHERE STATUS='CURRENT';
SQL> alter system archive log current;
16. Restart the primary database:
SQL> SHUTDOWN
SQL> STARTUP
17. Once all actions have been completed verify the standby database has been recovered to the last archive log produced by the primary. (Step 15)
On the primary:
SQL> select max(sequence#) from v$archived_log;
On the standby:
SQL> select max(sequence#) from v$log_history;
18. Run the script above again to get the list of invalid objects after database upgrade.
19. Check the success of upgrade with the following query
SQL>select comp_name, status, version from dba_registry;
20. Upgrade RMAN catalog
$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;
Note: After executing the query in step 19, i saw that Oracle Database Packages and Types component has the status INVALID, here is a work around to solve this issue:
SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql
http://forums.oracle.com/forums/thread.jspa?threadID=669838
Maintenance of Oracle in an HP Service Guard cluster
In /opt/cmcluster/toolkit/oracle directory you should set the MAINTENANCE_FLAG variable to "YES" in the configuraiton file (.conf) and restart the package.
After maintenance mode is enabled create a file named oracle.debug under the package directory (generally /etc/cmcluster/package_name)
#touch oracle.debug
After these steps you can manually stop/start Oracle and this doesn't cause the package to fail.
You can remove the oracle.debug file after the maintenance work is done.