Tag: Osama blog

Upgrade Steps From 10g to 11gR2

Posted on by Osama Mustafa in Articles, Database
Please Follow the Below Steps For Upgrading 10g to 11gR2 , Before Doing Anything you need to take backup for the following Files :
1.      BACKUP BINARIES, DB,TNSNAMES,LISTENER,INIT,SPFILE,PWFILE THAT ARE BEING UPGRADED.
2.      COMPATIBLE PARAMETER MUST BE SET TO MINIMUM 10.0.0.0.  THE RECOMMENDED IS 11.2.0 FOR 11gR2 PARAMETER FILE.

Step-1 :
 Install  New Oracle Database Software & Apply Any Patches Necessary.

Step-2 :

Run The Pre-Upgrade Information Tools : (Mandatory) 


1.      Copy the Pre-Upgrade Information Tool (utlul12i.sql) from the Oracle Database 11gR2 directory: $ORACLE_HOME/rdbms/admin to a temporary directory /tmp.
2.      Set your environment to the one that is being upgraded.  Assuming 10g.
3.      Change directory to /tmp that you copied utlu112i.sql to in Step 1.
4.      Start SQL*Plus and login as ‘/ as sysdba’
5.      Spool the results to a log file:
o   SQL> SPOOL upgrade_info.log
6.      Run the Pre-Upgrade Information Tool:
o   SQL> @utlul12i.sql
o   SQL> SPOOL OFF 

Open The Spool File for Checking Purpose :
 Oracle recommends gathering stats before the upgrade:  EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
■ Deprecated CONNECT Role
■ Access Control to Network Utility Packages
■ Database Links with passwords
■ TIMESTAMP WITH TIME ZONE Data Type
■ Optimizer Statistics
■ Invalid Objects
■ Save Oracle Enterprise Manager Database Control Data
■ Complete Materialized View Refreshes
■ Ensure No Files Need Media Recovery
■ Ensure No Files Are in Backup Mode
■ Resolve Outstanding Distributed Transactions
■ Sync Standby Database with the Primary Database
■ Purging the Database Recycle Bin

Step-3 : 
Prepare 11gR2 Home :

1.      Copy configuration files (init file, tnsnames, listener, pwfile) from old Oracle Home to new.
2.      Update init files with new COMPATIBLE parameter, fix any deprecated ones, and adjust the values to at least the minimum values indicated by the Pre-Upgrade Tool.
3.      Update any relative path names in parameter file to fully path names.

Step-4 : 
After all the Above Steps , you will start the upgrade Database for 10g Home :

1.      Shutdown the database:
o   SQL> SHUTDOWN IMMEDIATE;
2.      Make sure the following checks:
o   The oratab file points to Oracle Database 11g Release 2 Oracle Home
o   The following environment variables point to the Oracle 11g Release 2 directories:
§  ORACLE_HOME
§  PATH
3.      Change to the $ORACLE_HOME/rdbms/admin directory and start SQL*Plus
o   sqlplus ‘/ as sysdba’
4.      Start the instance by issuing following command (you may get messages that parameters are obsolete, fix those and start up the db again):
o   SQL>  STARTUP UPGRADE;
o   SQL>  SPOOL upgrade.log
5.      Run the catupgrd.sql script:
o   SQL>  @catupgrd.sql
6.      Once completed, shutdown the database and restart it.
o   SQL>  SHUTDOWN IMMEDIATE;
o   SQL>  STARTUP;
7.      Run the Post-Upgrade Status Tool to provide a summary of the upgrade. (If there are any INVALID components, then check upgrade manual for fixes.)
o   SQL> @utlul12s.sql
8.      Run catuppst.sql to perform upgrade actions that do not require db to be in upgrade mode:
o   SQL> $ORACLE_HOME/rdbms/admin/catuppst.sql
9.      Run utlrp to recompile any remaining stored PL/SQL and other objects.
o   SQL> $ORACLE_HOME/rdbms/admin/utlrp.sql
10.  Verify that all objects are valid:
o   SQL> SELECT count(*) FROM dba_invalid_objects;
o   SQL> SELECT distinct object_name FROM dba_invalid_objects;
Thank you 
Osama mustafa

 


ORA-12516: TNS:listener could not find available handler

Posted on by Osama Mustafa in Database
after the increasing the Number processes in the Oracle database, it solved the problem.

But Before this , i am gonna show you some steps to check the process via OS and Database LETS BEGIN :

  • check if the number of connections reaches the database’s  process parameter using following Unix command: 
ps -ef | grep oracleSID | grep -v grep | wc -l
or
ps aux | grep oracleSID | grep -v grep | wc -l
  •  check if the number of connections reaches the database’s  process parameter using following Database Command :
SQL> show parameter processes

NAME                TYPE        VALUE
------------------ ----------- -------
processes           integer     150

SQL> select count(*) from v$process;

  COUNT(*)
----------
       149
SQL> show parameter sessions

NAME                TYPE        VALUE
------------------ ----------- -------
Session              integer     150

SQL> select count(*) from v$session;

  COUNT(*)
----------
       149

Now We need To Increase the Both Parameter By :

SQL> alter system set processes=300 scope=spfile;

System altered.
 
SQL> alter system set Session=300 scope=spfile;

System altered.

If this Solution Not Work For you , Try this One :

SQL> alter system set local_listener=
“(ADDRESS=(PROTOCOL=tcp)(HOST=10.122.28.12)(PORT=1521))” sid=’ORCL1′;

System altered.

SQL> show parameter local

NAME                                 TYPE        VALUE
———————————— ———– ——————————
local_listener                       string      (ADDRESS=(PROTOCOL=tcp)(HOST=1
                                                 0.122.28.12)(PORT=1521))

Thank you 

Osama Mustafa

SCHEDULER[0x51B5]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Posted on by Osama Mustafa in Database
Oracle Database Resource Manager (the Resource Manager) : 
enables you to optimize resource allocation among the many concurrent database sessions.

When database resource allocation decisions are left to the operating system, you may encounter the following problems:

  • Excessive overhead.

Excessive overhead results from operating system context switching between Oracle Database server processes when the number of server processes is high.

  • Inefficient scheduling

 The operating system deschedules database servers while they hold latches, which is inefficient.

  • Inappropriate allocation of resources

The operating system distributes resources equally among all active processes and is unable to prioritize one task over another.

  • Inability to manage database-specific resources, such as parallel execution servers and active sessions

Check The Resource Manager On your Database :

SQL> show parameter resource_manager_plan

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string      SCHEDULER[0x3003]:DEFAULT_MAIN
                                                 TENANCE_PLAN

All this is Introduction about the Error that appear when you upgrade to 11g , you will getting the following messages in the alert.log.

Setting Resource Manager plan SCHEDULER[0x51B5]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Thu Feb 05 22:00:03 2009
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Thu Feb 05 22:00:39 2009
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"

Solution to  Disable the resource manager , Steps to Do this :

1-set the current resource manager plan to null

alter system set resource_manager_plan='' scope=both 

2-change the active windows to use the null resource manager plan 

    execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
    execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');

3-run :

SQL> execute dbms_scheduler.set_attribute('','RESOURCE_PLAN','');

You Can Also Disable SQL tuning By :

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/


Thank you
Osama Mustafa

Check who Use the same record you want to delete

Posted on by Osama Mustafa in Scripts
Just Run the Below Script and after this kill the session :

  select owner||’.’||object_name obj
 ,oracle_username||’ (‘||s.status||’)’ oruser
 ,os_user_name osuser
 ,machine computer
 ,l.process unix
 ,’||s.sid||’,’||s.serial#||’ ss
 ,r.name rs
 ,to_char(s.logon_time,’yyyy/mm/dd hh24:mi:ss’) time
 from       v$locked_object l
 ,dba_objects o
 ,v$session s
 ,v$transaction t
 ,v$rollname r
 where l.object_id = o.object_id
and s.sid=l.session_id
and s.taddr=t.addr
and t.xidusn=r.usn
order by osuser, ss, obj ;

To kill the session : 

Alter System kill Session ‘Sid,Serail#’ immediate ;

Thank you
Osama mustafa

Check Your Database Size

Posted on by Osama Mustafa in Scripts
Datafiles : 

select sum(bytes)/1048576 “DATAFILES_SIZE_MB” from dba_data_files;

Tempfiles:

select sum(bytes)/1048576 “TEMPFILES_SIZE_MB” from dba_temp_files;

Your redo logs can also use up a large amount of disk space – especially if your database has more than the minimum number of 2 redo log groups. (You may also have several members within each group).

Redologs:

select sum(bytes)/1048576 “REDOLOGS_SIZE_MB” from v$log;

The database obviously needs controlfiles to record information such as which datafiles belong to the database.  If your CONTROL_FILE_RECORD_KEEP_TIME is set to a large value, then your controlfiles can become quite large.

Controlfiles:


select round(sum(block_size*file_size_blks)/1048576,2) “CONTROLFILESIZE_MB” from v$controlfile;

From 10g onwards, flashback database is not enabled by default, but if it is, then this area can grow rapidly over time.

Flash Recovery Area:


select * from v$recovery_file_dest; 
select * from v$flash_recovery_area_usage;  

These views will show sizing details and free space available.

Note: If your backups are held outside of the flash recovery area, then you’ll also need to allow space for these. This will depend on your backup strategy and backup retention policy. (Export/datapump export dumpfiles also need to be planned for).
If you are using RMAN incremental backups and have block change tracking enabled, then include this file:

Block change tracking file


select filename, nvl(bytes/1048576,0) “BLOCK_CT_SIZE_MB” from v$block_change_tracking;

Files referenced by database directories or the utl_file_dir parameter:


Your application may read from, or write to external files via database directories or the utl_file_dir parameter.

Other examples of using external directories are for

(a) External tables –

select a.owner||’.’||a.table_name||’ stored in directory ‘||b.directory_path “EXTERNAL_TABLES”
from dba_external_locations a, dba_directories b
where a.directory_owner=b.owner
and a.directory_name=b.directory_name;

(b) If you are storing multiple versions of the same tablespace within a file group repository. (i.e. tablespace versioning).

select a.tablespace_name, a.version, a.file_group_owner, a.file_group_name,
b.file_name, b.file_directory
from dba_file_group_tablespaces a, dba_file_group_files b
where a.file_group_owner=b.file_group_owner
and a.file_group_name=b.file_group_name;

Thank you 
osama Mustafa

Limit Access to your Database

Posted on by Osama Mustafa in Database
Its Simple Easy Way to Limit Access for your Database to Prevent People to miss Around , we all know there’s File Called “sqlnet.ora” All you Have to do is Follow The Below Steps and Add what you want :

Sqlnet.ora : $ORACLE_HOME/network/admin

TCP.EXCLUDED_NODES

Purpose
Use the parameter TCP.EXCLUDED_NODES to specify which clients are denied access to the database.

Example
TCP.EXCLUDED_NODES=(finance.us.acme.com, mktg.us.acme.com, 144.25.5.25)

TCP.INVITED_NODES

Purpose
Use the parameter TCP.INVITED_NODES to specify which clients are allowed access to the database.
 This list takes precedence over the TCP.EXCLUDED_NODES parameter if both lists are present.

Example
TCP.INVITED_NODES=(sales.us.acme.com, hr.us.acme.com, 144.185.5.73)

TCP.VALIDNODE_CHECKING

 Purpose
Use the parameter TCP.VALIDNODE_CHECKING to check for the TCP.INVITED_NODES and TCP.
EXCLUDED_NODES to determine which clients to allow or deny access.

Example
TCP.VALIDNODE_CHECKING=yes
TCP.VALIDNODE_CHECKING=no

Simple Way to keep your database Clean . you maybe need to restart your Listener after this

Thank you
Osama Mustafa

Oracle Real Application Cluster Managment

Posted on by Osama Mustafa in Database
1. CRSCTL: 

Cluster Control utility performs various administrative operation of oracle clusterware. It is located in $ORA_CRS_HOME/bin and must be executed by the “root” user.

a. To check the current state of all oracle clusterware daemon:

[root@rac1 bin]# ./crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

b.You can also check the state of individual oracle clusterware daemon:

[root@rac1 bin]# ./crsctl check cssd
CSS appears healthy

[root@rac1 bin]# ./crsctl check crsd
CRS appears healthy

[root@rac1 bin]# ./crsctl check evmd
EVM appears healthy

c. To start oracle clusterware

[root@rac1 bin]# ./crsctl start crs

Attempting to start CRS stack
The CRS stack will be started shortly

d. To stop oracle clusterware

[root@rac1 bin]# ./crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.

e. To disable  oracle clusterware:

[root@rac1 bin]# ./crsctl disable crs

f.  To enable  oracle clusterware:

[root@rac1 bin]# ./crsctl enable crs

g. To get current value of CSS parameter

[root@rac1 bin]# ./crsctl get css
for  example: to get value of misscount parameter
 [root@rac1 bin]# ./crsctl get css misscount
60

h. To set a new value of CSS parameter

[root@rac1 bin]# ./crsctl set css
for  example: to set value of misscount parameter

[root@rac1 bin]# ./crsctl set css misscount 120
Configuration parameter misscount is now set to 120.

i. To unset CSS parameter value

[root@rac1 bin]# ./crsctl unset css
for  example: to unset value of misscount parameter

[root@rac1 bin]# ./crsctl unset css misscount
Configuration parameter misscount is now undefined.

j. To list the module for debugging in CSS

[root@rac2 bin]# ./crsctl lsmodules css

The following are the CSS modules ::
CSSD
COMMCRS
COMMNS

2. CRS_STAT:
 It reports the current state of resources configured in the OCR.

[oracle@rac1 bin]$ ./crs_stat -t
Name                      Type              Target       State         Host
———————————————————————————–
ora….C1.inst             application    ONLINE    ONLINE          rac1
ora….C2.inst             application    ONLINE    ONLINE          rac2
ora….AC1.srv            application    ONLINE    ONLINE          rac1
ora.RAC.abc.cs          application    ONLINE    ONLINE          rac1
ora.RAC.db                application    ONLINE    ONLINE         rac2
ora….AC1.srv            application    ONLINE    ONLINE          rac1
ora….ice2.cs             application    ONLINE    ONLINE          rac1
ora….AC1.srv            application    ONLINE    ONLINE          rac1

3. CRS_STOP:

 This command used to stop resource or cluster member.

[root@rac1 bin]# ./crs_stop ora.rac1.ons
Attempting to stop `ora.rac1.ons` on member `rac1`
Stop of `ora.rac1.ons` on member `rac1` succeeded.

4.  CRS_START:

 This command used to start resource or cluster member.

[root@rac1 bin]# ./crs_start ora.rac1.ons
Attempting to start `ora.rac1.ons` on member `rac1`
Start of `ora.rac1.ons` on member `rac1` succeeded.

5.  OCRDUMP :

 It dumps the contents of OCR into a text file.

 [root@rac1 bin]# ./ocrdump /home/oracle/ocr.dmp

6. OCRCHECK :

 It verifies the integrity of the OCR.

[root@rac2 bin]# ./ocrcheck

Status of Oracle Cluster Registry is as follows :
Version                                :    2
Total space (kbytes)         :    5237072
Used space (kbytes)         :    9360
Available space (kbytes)  :    5227712
ID                                        :    794527192
Device/File Name             :    /apps/oracle/oradata/ocr
Device/File integrity check succeeded
Cluster registry integrity check succeeded

7. OCRCONFIG:

 It perform various administrative operation on the OCR.

How to check whether user has datapump privilege

Posted on by Osama Mustafa in Scripts 
SQL>SET lines 100 
COL privilege FOR a50 
SELECT grantee, granted_role, default_role 
FROM dba_role_privs 
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE') 
ORDER BY 1,2; 
GRANTEE                        GRANTED_ROLE                   DEF
------------------------------ ------------------------------ ---
ARJU                           DBA                            YES
DBA                            EXP_FULL_DATABASE              YES
DBA                            IMP_FULL_DATABASE              YES
FARUK                          DBA                            YES
MOMIN                          DBA                            YES
SYS                            DBA                            YES
SYS                            EXP_FULL_DATABASE              YES
SYS                            IMP_FULL_DATABASE              YES
SYSMAN                         DBA                            YES
SYSTEM                         DBA                            YES
TEST                           EXP_FULL_DATABASE              YES

 Check for granted privileges on directory objects

SET lines 110
COL privilege FOR a12
COL grantee FOR a25
COL owner FOR a25
SELECT p.grantee, p.privilege, p.owner, d.directory_name 
  FROM dba_tab_privs p, dba_directories d 
 WHERE p.table_name=d.directory_name 
   AND (grantee IN ('TEST', 'PUBLIC') 
        OR grantee IN (SELECT granted_role FROM dba_role_privs 
                        WHERE grantee IN ('TEST', 'PUBLIC'))) 
 ORDER BY 4,3,2; 
A typical output is 
GRANTEE              PRIVILEGE  OWNER                DIRECTORY_NAME
-------------------- ---------- -------------------- -----------------
TEST                 READ       SYS                  D
TEST                 WRITE      SYS                  D
EXP_FULL_DATABASE    READ       SYS                  DATA_PUMP_DIR
EXP_FULL_DATABASE    WRITE      SYS                  DATA_PUMP_DIR

Directory Name : TEST 

Check for tablespace quota
 
SET lines 100 numwidth 12
SELECT q.username, q.tablespace_name, q.bytes, q.max_bytes 
  FROM dba_ts_quotas q, dba_users u 
 WHERE q.username=u.username AND q.username in ('TEST'); 
USERNAME                       TABLESPACE_NAME                       BYTES    MAX_BYTES
------------------------------ ------------------------------ ------------ ------------
TEST                           SYSTEM                                65536     10485760

OCR And Voting In 11gR2

Posted on by Osama Mustafa in Database
OCR: It created at the time of Grid Installation. It’s store information to manage Oracle cluster-ware and it’s component such as RAC database, listener, VIP,Scan IP & Services.
Minimum 1 and maximum 5 copy of OCR is possible.

Voting Disk: It manage information about node membership. Each voting disk must be accessible by all nodes in the cluster.If any node is not passing heat-beat across other note or voting disk, then that node will be evicted by Voting disk.
Minimum 1 and maximum 15 copy of voting disk is possible.

New Facts:
  • We can store OCR And Voting disk on ASM or certified cluster file system.
  • We can dynamically add or replace voting disk & OCR.
  • Backup of Voting disk using “dd” command not supported.
  • Voting disk and OCR can be keep in same disk-group or different disk-group
  • Voting disk and OCR automatic backup kept together in a single file.
  • Automatic backup of Voting disk and OCR happen after every four hours, end of the day, end of the week
  • You must have root or sudo privilege account to manage it.

To find current location of Voting disk:

[oracle@rsingle ~]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
–  —–    —————–                ——— ———
1. ONLINE   6a60a2c3510c4fbfbff62dcdc279b247 (ORCL:DATA1) [DATA]

Relocate or multiplexing Voting disk to another disk-group (With normal redundancy)

[root@rsingle ~]# /u01/app/11.2.0/grid/bin/crsctl replace votedisk +CRS
Successful addition of voting disk afb77b2693a24f1ebfe876784103e82a.
Successful addition of voting disk 3e2542c5b1154ffdbfc8b6dea7dce390.
Successful addition of voting disk 8e0f3c5921cc4f93bf223de1465d83cc.
Successful deletion of voting disk 6a60a2c3510c4fbfbff62dcdc279b247.
Successfully replaced voting disk group with +CRS.
CRS-4266: Voting file(s) successfully replaced

New location of Voting disk:

[root@rsingle ~]# /u01/app/11.2.0/grid/bin/crsctl query css votedisk

##  STATE    File Universal Id                File Name Disk group
–  —–    —————–                ——— ———
1. ONLINE   afb77b2693a24f1ebfe876784103e82a (ORCL:DATA2) [CRS]
2. ONLINE   3e2542c5b1154ffdbfc8b6dea7dce390 (ORCL:DATA3) [CRS]
3. ONLINE   8e0f3c5921cc4f93bf223de1465d83cc (ORCL:DATA4) [CRS]
Located 3 voting disk(s).

To find location of Corrent OCR:

[oracle@rsingle ~]$ ocrcheck

Status of Oracle Cluster Registry is as follows :
Version                  :          3
Total space (kbytes)     :     262120
Used space (kbytes)      :       2964
Available space (kbytes) :     259156
ID                       : 1390115973
Device/File Name         :      +DATA
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user

Create mirror copy of OCR online

[oracle@rsingle ~]$ sudo ocrconfig -add +CRS
Password:

Check location of OCR after mirror copy creation:

[root@rsingle ~]# /u01/app/11.2.0/grid/bin/ocrcheck

Status of Oracle Cluster Registry is as follows :
Version                  :          3
Total space (kbytes)     :     262120
Used space (kbytes)      :       2964
Available space (kbytes) :     259156
ID                       : 1390115973
Device/File Name         :      +DATA
Device/File integrity check succeeded
Device/File Name         :       +CRS
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded

Another file to find location of OCR:

[root@rsingle ~]# cat /etc/oracle/ocr.loc
#Device/file  getting replaced by device +CRS
ocrconfig_loc=+DATA
ocrmirrorconfig_loc=+CRS

Share it for knowledge

Thank you atual
 Osama mustafa

Scripts For Backup

Posted on by Osama Mustafa in Scripts
This Script Will Let you choose One Of three Type Of backup , All you have to do right the backup scripts to make the below script read it and run it :

#!/bin/bash
ORACLE_SID=ORCL;export ORACLE_SID
echo $ORACLE_SID
echo “Please Specify the kind of backup you want to take”
echo “1) COLD BACKUP”
echo “2) HOT BACKUP”
echo “3) EXPORT BACKUP”
echo “Enter your option”
read option
 while [ $option -gt 3 ]||[ $option -le 0 ]
do
echo “Please Enter the correct option”
read option
done
case $option in
1|2) echo “You are taking rman backup of DB”
   rman target sys/sys @/oracle/product/11g/rman_backup_$option.txt;exit;;
3) echo “You are taking export backup of DB”
   exp system/sys file=/oracle/exp_dat.dmp log=/oracle/exp_dat.log full=y;
   exit;;
esac
exit

 The above script can call anyone of the following rman script depending upon the user who wants take cold or hot backup

The content of rman_backup_1.txt

run {
   shutdown immediate;
   startup mount;
   allocate channel dup1 device type disk;
   allocate channel dup2 device type disk;
   backup format ‘/oracle/%U’ database;
   release channel dup1;
   release channel dup2;
   alter database open;
  }

The content of rman_backup_2.txt

run {
allocate channel dup1 device type disk;
allocate channel dup2 device type disk;
backup format ‘/oracle/%U’ database;
backup format ‘/oracle/arch_%U’ archivelog all;
backup format ‘/oracle/ctl_%U’ current controlfile;
release channel dup1;
release channel dup2;
}

Thank you
Osama mustafa