Powered By Blogger

Wednesday, July 27, 2011

Online patching in 11.2.0.1 database on windows 32 bit machine

For applying the below patch i didnt shutdown my database, this is tested on a test database on windows 32bit platform, But not sure how it behaves on a production database.

D:\app\nav6cob\product\11.2.0\dbhome_1>cd D:\siva\database\11g_patch_win32bit\10073948

D:\siva\database\11g_patch_win32bit\10073948>opatch apply
Invoking OPatch 10.2.0.2.1

Oracle interim Patch Installer version 10.2.0.2.1
Copyright (c) 2005, Oracle Corporation.  All rights reserved..


Oracle Home       : d:\app\nav6cob\product\11.2.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 10.2.0.2.1
OUI version       : 11.2.0.1.0
OUI location      : d:\app\nav6cob\product\11.2.0\dbhome_1\oui
Log file location : d:\app\nav6cob\product\11.2.0\dbhome_1\cfgtoollogs\opatch\opatch-2011_Jul_27_12-34-00-IST_Wed.log

ApplySession applying interim patch '10073948' to OH 'd:\app\nav6cob\product\11.2.0\dbhome_1'
ApplySession: Optional component(s) [ oracle.sysman.console.db, 11.1.0.7.0, higher version 11.2.0.1.0 found. ]  not present in the Oracle Home or a higher version is
d.

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.


Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = 'd:\app\nav6cob\product\11.2.0\dbhome_1')

Is the local system ready for patching?

Do you want to proceed? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '10073948' for restore. This might take a while...
Backing up files affected by the patch '10073948' for rollback. This might take a while...

Patching component oracle.sysman.console.db, 11.2.0.1.0...
Copying file to "d:\app\nav6cob\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\uix2-install.zip"
Copying file to "d:\app\nav6cob\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\WEB-INF\lib\uix2.jar"
ApplySession adding interim patch '10073948' to inventory

Verifying the update...
Inventory check OK: Patch ID 10073948 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 10073948 are present in Oracle Home.
Execution of 'cmd /C D:\siva\database\11g_patch_win32bit\10073948\custom\scripts\post.bat -apply 10073948 ':

Return Code = 0

The local system has been patched and can be restarted.


OPatch succeeded.

D:\siva\database\11g_patch_win32bit\10073948>cd d:\app\nav6cob\product\11.2.0\dbhome_1\OPatch

D:\app\nav6cob\product\11.2.0\dbhome_1\OPatch>opatch lsinventory
Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation.  All rights reserved.


Oracle Home       : d:\app\nav6cob\product\11.2.0\dbhome_1
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : d:\app\nav6cob\product\11.2.0\dbhome_1\oui
Log file location : d:\app\nav6cob\product\11.2.0\dbhome_1\cfgtoollogs\opatch\opatch2011-07-27_12-38-44PM.log

Patch history file: d:\app\nav6cob\product\11.2.0\dbhome_1\cfgtoollogs\opatch\opatch_history.txt

Lsinventory Output file location : d:\app\nav6cob\product\11.2.0\dbhome_1\cfgtoollogs\opatch\lsinv\lsinventory2011-07-27_12-38-44PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.1.0
There are 1 products installed in this Oracle Home.


Interim patches (1) :

Patch  10073948     : applied on Wed Jul 27 12:35:44 IST 2011
Unique Patch ID:  13741704
   Created on 1 Sep 2010, 02:44:28 hrs PST8PDT
   Bugs fixed:
     10073948



--------------------------------------------------------------------------------

OPatch succeeded.

D:\app\nav6cob\product\11.2.0\dbhome_1\OPatch>

D:\app\nav6cob\product\11.2.0\dbhome_1\BIN>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 27 12:36:42 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: sys/database as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pages 100
SQL> set lines 1000
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             255853008 bytes
Database Buffers          272629760 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.
SQL>
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>

SQL> select comp_name,version,status from dba_registry;
COMP_NAME                          VERSION       STATUS
--------------------------------- ------------ -----------
OWB                                11.2.0.1.0      VALID
Oracle Application Express         3.2.1.00.10     VALID
Oracle Enterprise Manager          11.2.0.1.0      VALID
OLAP Catalog                       11.2.0.1.0      VALID
Spatial                            11.2.0.1.0      VALID
Oracle Multimedia                  11.2.0.1.0      VALID
Oracle XML Database                11.2.0.1.0      VALID
Oracle Text                        11.2.0.1.0      VALID
Oracle Expression Filter           11.2.0.1.0      VALID
Oracle Rules Manager               11.2.0.1.0      VALID
Oracle Workspace Manager           11.2.0.1.0      VALID
Oracle Database Catalog Views      11.2.0.1.0      VALID
Oracle Database Packages and Types 11.2.0.1.0      VALID
JServer JAVA Virtual Machine       11.2.0.1.0      VALID
Oracle XDK                         11.2.0.1.0      VALID
Oracle Database Java Packages      11.2.0.1.0      VALID
OLAP Analytic Workspace            11.2.0.1.0      VALID
Oracle OLAP API                    11.2.0.1.0      VALID

18 rows selected.
Regards,
Navaneeth

Wednesday, July 13, 2011

Flashback Data Archive in 11g

SQL> CREATE TABLESPACE FDB DATAFILE 'D:\APP\NAV6COB\ORADATA\ORCL11G\FDB_DATA_1.DBF' SIZE 10m;

Tablespace created.

SQL> CREATE FLASHBACK ARCHIVE DEFAULT FDA_1 TABLESPACE fdb quota unlimited retention 1 year;
CREATE FLASHBACK ARCHIVE DEFAULT FDA_1 TABLESPACE fdb quota unlimited retention 1 year
                                                            *
ERROR at line 1:
ORA-55603: Invalid Flashback Archive command


SQL> CREATE FLASHBACK ARCHIVE DEFAULT FDA_1 TABLESPACE fdb retention 1 year;

Flashback archive created.

SQL>
SQL> select * from dba_flashback_archive;

OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------
SYS        FDA_1                    1               365 13-JUL-11 12.27.06.000000000 PM 13-JUL-11 12.27.06.000000000 PM            DEFAULT
SQL>
SQL> select * from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
------------------------------------------------------------    
EMP1     SCOTT             FDA_1  SYS_FBA_HIST_73694   ENABLED

SQL> alter table emp1 flashback archive;

Table altered.

SQL>
SQL>
SQL> update emp1 set ename='NAVANEETH' where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

SQL> drop table emp1;
drop table emp1
           *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

Managing Flashback archive:


SQL> ALTER FLASHBACK ARCHIVE fda_1 MODIFY RETENTION 2 YEAR;

Flashback archive altered.

SQL> alter flashback archive fda_1 purge all;

Flashback archive altered.

SQL> drop flashback archive fda_1;

Flashback archive dropped.

SQL>