Upgrading to Oracle Database 11g

If there are no issues to resolve prior to the upgrade oracle 11g , Shut down the database and perform an offline backup before continuing with the upgrade process. Once you have a backup you can restore if needed, you are ready to proceed with the upgrade process. The process is detailed and script-based, so you should consult with the Oracle installation and upgrade documentation for your environment and version. You can choose one from following two upgrading methods.

Database Upgrade Assistant (DBUA)

Its recommended upgrade method is to use the Database Upgrade Assistant (DBUA), a GUI tool that performs all necessary prerequisite checks and operations before upgrading the specified instances. The DBUA can be started directly from the Oracle Universal Installer (OUI) or separately after the software installation is complete. In Microsoft Windows environments chose Database Upgrade Assistand from program list of typ "dbua" on comant prompt, installer welcome window will appear. Alternatively you may which to perform a manual upgrade which involves the following steps:

Manual Oracle 11g Database Upgrade

Execute script "$ORACLE_HOME/rdbms/admin/utlu111i.sql, it performs pre-update validation checks on an existing instance. The script checks a number of areas to make sure the instance is suitable for upgrade suchas Database version, initialization parameters, SYSAUX etc.

SQL> connect / as sysdba
SQL> spool /oradisk/11gdatabase_upgrade_checklist.txt

SQL> @utlu111i.sql

SQL> spool off

Resolved all issues indicated bebefore a manual upgrade is attempted. See 11gdatabase_upgrade_checklist.txt

1. Copy configuration files (init.ora, spfile.ora, password file) from their old location to the new Oracle software home directory.

2. Remove obsolete and deprecated initialization parameter from the configuration files identified in the Pre-Upgrade Information Tool. Update any initialization parameters to at least the minimum values specified in the Pre-Upgrade Information Tool report. Use full pathnames in the parameter files.

3. If you are upgrading a cluster database, set the CLUSTER_DATABASE initialization parameter to FALSE. After the upgrade, you must set this initialization parameter back to TRUE.

4. Shut down the instance.

5. If you are using Windows, stop the service associated with the instance and delete the
Oracle service at the command prompt. For Oracle 8.0, use the command

NET STOP OracleServiceName
ORADIM –DELETE –SID instance_name

Next, create the new Oracle Database 11g service using the ORADIM command, as shown here. The variables for this command are shown in the following table.

C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS -STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA

6. If your operating system is Unix or Linux, make sure the environment variables ORACLE_HOME and PATH point to the new release 11.1 directories, ORACLE_SID is set to the existing database’s SID, and the file /etc/oratab points to the new Oracle Database 11g home directory. In addition, any server or client-side scripts that set ORACLE_HOME must be changed to point to the new Oracle software home directory.

7. Log into the system as the owner of the Oracle Database 11g software.

8. Change your directory to the $ORACLE_HOME/rdbms/admin subdirectory under the Oracle software home directory.

9. Connect to SQL*Plus as a user with SYSDBA privileges.
10. Issue the startup upgrade command.
11. Use the spool command to log the results of the following steps.

12. Create a SYSAUX tablespace via the create tablespace command if you are upgrading from a release prior to 10.1. You should allocate SYSAUX between 500MB and 5GB of disk space, depending on the number of user objects. SYSAUX must be created with the following clauses: online, permanent, read write, extent management local, and segment space management auto. All those clauses except segment space management auto are the defaults. See the output from the Pre-Upgrade Information Tool for suggested sizing of the SYSAUX tablespace. Here’s an example:

create tablespace SYSAUX
datafile '/u01/oradata/db1/sysaux01.dbf' size 500m reuse
extent management local
segment space management auto online;

13. Run the script catupgrd.sql in the 11g environment. This script automatically determines which upgrade scripts must be run, runs them, and then shuts down the database.

14. Stop spooling (via spool off) and review the spool file for errors. Resolve any problems identified there. Restart the database at the SQL*Plus prompt using the startup command.

15. Run the utlu111s.sql file to upgrade Oracle components such as Oracle Text, Oracle Ultra Search, Oracle Application Express, and the Oracle Server itself. You run it as follows:
SQL> @utlu101s.sql

16. Oracle will then display the upgrade status of each component. The upgrade elements should all be listed with a status of “VALID.”

17. Run the catuppst.sql script, located in $ORACLE_HOME/rdbms/admin to perform upgrade steps that do not require the database to be in UPGRADE mode:
SQL> @rdbms/admin/catuppst.sql

18. Run the utlrp.sql script to recompile invalid packages:
SQL> @utlrp.sql

19. You can then verify that all packages and classes are valid by using the following SQL:

select count(*) from dba_invalid_objects;
select distinct object_name from dba_invalid_objects;

20. Shut down the database and perform an offline backup of the database; then restart the database. The upgrade is complete.

Fixing ORA-19815: WARNING: db_recovery_file_dest_size

To fix the problem, we need to either make the flash recovery area larger, or remove some files from it. If you have the enough disk space available, make the recovery area larger. we also have option for changing parameters (i.e archivelogs's locatin, RMAN backup location) which use db_recovery_file_dest to another location for avoiding this type errors, here first opeion just changing db_recovery_file_dest_size:

ORA-19815: WARNING: string of string bytes is string%% used, and has string remaining bytes available.
Cause: DB_RECOVERY_FILE_DEST is running out of disk space.
Action: One of the following:
1. Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
2. Backup files to tertiary device using RMAN.
3. Consider changing RMAN retention policy.
4. Consider changing RMAN archivelog deletion policy.
5. Delete files from recovery area using RMAN.

[root@redhat ~]# su - oracle
[oracle@redhat ~]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jun 8 17:59:49 2004
Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> connect /as sysdba
Connected.
SQL> show parameter db_recovery_file_dest_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 2G

SQL> alter system set db_recovery_file_dest_size=10G scope=both;
System altered.

SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 10G

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1523306496 bytes
Fixed Size 1300072 bytes
Variable Size 754977176 bytes
Database Buffers 754974720 bytes
Redo Buffers 12054528 bytes
Database mounted.
Database opened.

SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 10G
.

How Find User Information (Oracle)

In Oracle/PLSQL, the userenv function can be used to retrieve information about the current Oracle session. Although this function still exists in Oracle for backwards compatibility, it is recommended that you use the sys_context function insteadUSERENV is an Oracle provided namespace that describes the current session, We can find connect user information easly:

Syntax: SELECT sys_context('', '', );FROM dual;

Example: Find User information (host,terminal,IP,Operating User)

SELECT SYS_CONTEXT('USERENV','HOST') , SYS_CONTEXT('USERENV','TERMINAL'),SYS_CONTEXT('USERENV','IP_ADDRESS'), SYS_CONTEXT('USERENV','OS_USER')FROM DUAL

ACTION

Identifies the position in the module (application name) and is set through the DBMS_APPLICATION_INFO package or OCI.

SELECT sys_context('USERENV', 'ACTION') FROM dual;exec dbms_application_info.set_action('INSERTING');

SELECT sys_context('USERENV', 'ACTION') FROM dual;

CLIENT_IDENTIFIER

SELECT sys_context('USERENV', 'CLIENT_IDENTIFIER') FROM dual;exec dbms_session.set_identifier(USER ' ' SYSTIMESTAMP);SELECT sys_context('USERENV', 'CLIENT_IDENTIFIER') FROM dual;

CLIENT_INFO
Returns user session information that can be stored by an application using the DBMS_APPLICATION_INFO package.
SELECT sys_context('USERENV', 'CLIENT_INFO') FROM dual;exec dbms_application_info.set_client_info('TEST');SELECT sys_context('USERENV', 'CLIENT_INFO') FROM dual;

CURRENT_BIND
The bind variables for fine-grained auditing
CURRENT_EDITION_ID
The numeric identifier of the current edition
SELECT sys_context('USERENV', 'CURRENT_EDITION_ID') FROM dual;

CURRENT_EDITION_NAME
The name of the current edition
SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

CURRENT_SCHEMA
Name of the default schema being used in the current schema. This value can be changed during the session with an ALTER SESSION SET CURRENT_SCHEMA statement.
SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;

CURRENT_SCHEMAID
Identifier of the default schema being used in the current session.
SELECT sys_context('USERENV', 'CURRENT_SCHEMAID') FROM dual;SELECT user#FROM sys.user$WHERE name = USER;

CURRENT_SQL
Returns the first 4K bytes of the current SQL that triggered the fine-grained auditing event.
CURRENT_SQLn
CURRENT_SQLn attributes return subsequent 4K-byte increments, where n can be an integer from 1 to 7, inclusive. CURRENT_SQL1 returns bytes 4K to 8K; CURRENT_SQL2 returns bytes 8K to 12K, and so forth. You can specify these attributes only inside the event handler for the fine-grained auditing feature.

CURRENT_SQL_LENGTH
The length of the current SQL statement that triggers fine-grained audit or row-level security (RLS) policy functions or event handlers. Valid only inside the function or event handler.

DB_DOMAIN
Domain of the database as specified in the DB_DOMAIN initialization parameter.
SELECT sys_context('USERENV', 'DB_DOMAIN') FROM dual;

DB_NAME
Name of the database as specified in the DB_NAME initialization parameter.
SELECT sys_context('USERENV', 'DB_NAME') FROM dual;SELECT name, valueFROM gv$parameterwhere name LIKE 'db%name';

DB_UNIQUE NAME
Name of the database as specified in the DB_UNIQUE_NAME initialization parameter.
SELECT sys_context('USERENV', 'DB_UNIQUE_NAME') FROM dual;SELECT name, valueFROM gv$parameterwhere name LIKE 'db%name'

INSTANCE
The instance identification number of the current instance.
SELECT sys_context('USERENV', 'INSTANCE') FROM dual;

INSTANCE_NAME
The name of the instance.
SELECT sys_context('USERENV', 'INSTANCE_NAME') FROM dual;

IP_ADDRESS
IP address of the machine from which the client is connected.

ISDBA
TRUE if the session is SYS
SELECT sys_context('USERENV', 'ISDBA') FROM dual;

LANG
The ISO abbreviation for the language name, a shorter form than the existing 'LANGUAGE' parameter.
SELECT sys_context('USERENV', 'LANG') FROM dual;

LANGUAGE
The language and territory currently used by your session, along with the database character set, in the form:language_territory.characterset.
SELECT sys_context('USERENV', 'LANGUAGE') FROM dual;

MODULE
The application name (module) set through the DBMS_APPLICATION_INFO package or OCI.
SELECT sys_context('USERENV', 'MODULE') FROM dual;

NLS_CALENDAR
The current calendar of the current session.
SELECT sys_context('USERENV', 'NLS_CALENDAR') FROM dual;

NLS_CURRENCY
The currency of the current session.
SELECT sys_context('USERENV', 'NLS_CURRENCY') FROM dual;

NLS_DATE_FORMAT
The date format for the session.
SELECT sys_context('USERENV', 'NLS_DATE_FORMAT') FROM dual;

NLS_DATE_LANGUAGE
The language used for expressing dates.
SELECT sys_context('USERENV', 'NLS_DATE_LANGUAGE') FROM dual;

NLS_SORT
BINARY or the linguistic sort basis.
SELECT sys_context('USERENV', 'NLS_SORT') FROM dual;

NLS_TERRITORY
The territory of the current session.
SELECT sys_context('USERENV', 'NLS_TERRITORY') FROM dual;

OS_USER
Operating system username of the client process that initiated the database session.
SELECT sys_context('USERENV', 'OS_USER') FROM dual;

Finding number of sessions terminal whise

How find how much number of sessions for each terminal, Just execute following statement :

SELECT RPAD(terminal,15,'-')' ('count(sid)')' AS "Terminal"
FROM v$session
WHERE terminal IS NOT NULL
GROUP BY TERMINAL
ORDER BY COUNT(sid) DESC