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

If you will do some maintenance operation that needs db restart on an Oracle database which works inside a Service Guard cluster, you should make the following configurations. Normally if you shutdown the database in the active node, the package halts.

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.

Recover Deleted Oracle Files


Do we have a chance to recover if our datafile or redolog file is accidentally deleted? If we are on a linux environment, yes we have.
“Most people know that on linux and unixes a file keeps on living (=available) in the context of a process as long as the file is kept open by the process, despite deletion by another process. This file is gone for all processes that try to access it after the deletion, but the deleted files will only get really disposed once all processes that have the file opened close the the connection (file descriptor) to it.”
Yes it seems we have chance to do that. But how? Linux has the proc file system which has sub-directories named with process numbers and under these directories we can find file descriptors for that process in fd directories. For example consider we have a database with SID “ORCL” that is running on a linux machine. With the following command we can see the process number of database writer process.
ps -ef | grep dbw0_ORCL
oracle 20013 1 0 Dec 24 ? 459:23 ora_dbw0_ORCL
You can list your deleted datafile with
ls -l /proc/20013/fd | grep deleted
lrwx------ 1 oracle dba 64 Feb 16 14:40 15 -> /oracle/app/oracle/datafile/”lost_data_file”.dbf (deleted)
And with a symbolic link you can access the datafile that you deleted before.
ln -s /proc/20013/fd/15 /oracle/app/oracle/datafile/”lost_data_file”.dbf
After this step you shuold restore the file because if your database goes down you’ll lose the datafile. You can use RMAN for this purpose with the following script
rman target /
backup as copy datafile YOUR_DATAFILE_NUMBER format '/location_of_your_database/new_name_for_File.dbf';
sql 'alter database datafile YOUR_DATAFILE_NUMBER offline';
switch datafile YOUR_DATAFILE_NUMBER to copy;
recover datafile YOUR_DATAFILE_NUMBER;
sql 'alter database datafile YOUR_DATAFILE_NUMBER online';
exit;
You can find details and scenarios on these links: 1 2
Powered by Blogger.

Page Views

- Copyright © Emre Baransel - Oracle Blog -Metrominimalist- Powered by Blogger - Designed by Johanes Djogan -