Oracle Database Administration: July 2014

Software information

Patch 13390677: 11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER

Product Oracle Database - Enterprise Edition

Oracle Server - Standard Edition

Hide Release Oracle 11.2.0.4.0

Platform IBM AIX on POWER Systems (64-bit)

Size 7.3 GB

Download software from oracle support

1. Oracle 11.2.0.4 Database Software Installation

2. Navigate to directory where database software was unzipped

3. As root user, run rootpre.sh

4. As Oracle user, ensure all ORCALE* and TNS* variables are unset

5. Create required install directory

6. Ensure Xwindows settings are correct

7. Run the OUI, ./runInstaller

8. Leave blank and click next. This will cause a new error dialogue box to appear, click yes to continue

9. Select skip software updates and click next.

10. Select install database software only and click next. (Upgrade to database will be a manual process covered in the next section)

11. Ensure English is selected and click next

12. Select Enterprise edition and click next

13. Specify Install location for new database OHOME and click next

14. Click next. The installer will now perform some pre requisite checks. If server has been configured correctly there should be no failures/warnings reported

15. Check summary screen and click install

16. Once the software has installed ok, you will be asked to run a script as the root user, 11g-OHOME/root.sh.

17. Oracle 11.2.0.4 Database Software Installation completed

check the last night backup of the database and archive logs

Run dbupgdiag.sql script and follow and recomendations e.g. Pre process audit records via Audit_Pre_Process.sql

SQL> EXECUTE dbms_stats.gather_dictionary_stats

SQL> PURGE DBA_RECYCLEBIN

SQL> @$11g_ORACLE_HOME/rdbms/admin/utlrp

SQL> @$11g_ORACLE_HOME/rdbms/admin/utlu112i.sql

Spool off and check output file for any issues, fix issues and run suggest routines

"startup database in mount mode, Create restore point:

SQL> create restore point BEFORE_UPGRADE guarantee flashback database;

Start db upgrade assistant @$11g_ORACLE_HOME/bin/dbua

Confirm no warning are displayed from pre-check script, act on any warnings

select database to be upgraded

select degree of parallelism for recompile

select upgrade Timezone version and Timestamp with Time Zone data

Accept and proceed with upgrade

Monitor upgrade process for any run time errors, validate completion report

UPGRADE COMPLETE

SQL> drop restore point BEFORE_UPGRADE

set compatible parameter to 11.2.0.4

Analyze all schemas - Objects analyzed with 100% Sample set and system stats removed

You need to perform an out of place upgrade.

1. Install 11.2.0.4 on a different home

2. run the pre-upgrade tool on the existing 11.2.0.1 database

3. peform the pre-requisities mentioned

4. Bring down the database from 11.2.0.1 and start the database in upgrade mode from 11.2.0.4 home by copying the pfile/spfile of 11.2.0.1 database to $11.2.0.4_HOME/dbs path

5. startup upgrade and run the catupgrd.sql

Make sure you look out for the invalid objects.

Refer *Complete checklist for out-of-place manual upgrade from previous 11.2.0.N version to the latest 11.2.0.N patchset. [ID 1276368.1]

and

Database Upgrade Path Reference List(Doc ID 730365.1)

. Install 11.2.0.4.0 rdbms software

11.2.0.4 patch set is a full release. There is no need to install 11.2.0.1 software

See Note 1189783.1 Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2

Beginning with the release 11.2.0.2 patch set, you have two ways to apply a patch set :

- Out-of-place upgrade (Recommended)

- In-place upgrade

See Note 1276368.1 : Complete checklist for out-of-place manual upgrade from previous 11.2.0.N version to the latest 11.2.0.N patchset

The software can be downloaded from My Oracle support: Patch:13390677

Please select your platform before downloading (Click on "Platform or Language" drop down menu)

if the platform name contains (32-bit) then it's 32-bit 11.2.0.4 client version

To install 11.2.0.4, you must download both p13390677_112040_<platform>_1of7.zip

and p13390677_112040_<platform>_2of7.zip

<platform> = your platform( ie for linux x86, download p13390677_112040_LINUX_1of7.zip and

p13390677_112040_LINUX_2of7.zip )

Note 1194734.1 : Where do I find that on My Oracle Support (MOS) [Video]

Note 549617.1 : How To Verify The Integrity Of A Patch/Software Download? [Video]

Note 169706.1 : Oracle Database Installation and Configuration Requirements Quick Reference (8.0.5 to 11.2)

Note 551141.1 : Database Server Upgrade/Downgrade Compatibility Matrix

2. Finish the post installation steps as per the patchset README (readme.html).

REFERENCE:

11.2.0.4 Patch Set - Availability and Known Issues Note:1562139.1

11.2.0.4 Patch Set - List of Bug Fixes by Problem TypeNote 1562142.1

Actions For DST Updates When Upgrading To Or Applying The 11.2.0.4 Patchset Note 1579838.1

http://gavinsoorma.com/2009/07/script-monitor-flashback-logs/

PROMPT How Far Back Can We Flashback To (Time)?
PROMPT
select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time"
 from v$flashback_database_log;

PROMPT
PROMPT How Far Back Can We Flashback To (SCN)?
PROMPT
col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;

PROMPT
PROMPT Flashback Area Usage
SELECT * FROM   V$FLASH_RECOVERY_AREA_USAGE;

PROMPT
col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999
col round(space_used/1048576) heading "Space Used (MB)" format 99999
col name Heading "Flashback Location" format a40

select name, round(space_limit/1048576),round(space_used/1048576)
 from  v$RECOVERY_FILE_DEST;

Oracle Database Administration

Friday, July 11, 2014

Upgrade oracle 11.2.0.1 to 11.2.0.4

Friday, July 4, 2014

Flashback_log

About Me