Oracle DBA Journal : Administration

Showing posts with label Administration. Show all posts

Tuesday, April 18, 2017

Drop an Oracle Database

To drop an Oracle Database.

You need to connect database as SYSDBA.

SQL> set pages 1000
SQL> set lines 1000
SQL>
SQL> select INSTANCE_NAME,HOST_NAME from v$instance;

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
ASHISH Hostname.domain.com

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount exclusive restrict;
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 2415920008 bytes
Database Buffers 1845493760 bytes
Redo Buffers 12107776 bytes
Database mounted.
SQL>

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
ASHISH Hostname.domain.com

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option
SQL>
SQL> exit

Good Luck

Friday, March 10, 2017

Export Oracle Schema Statistics

Hey,

We sometimes need to backup up the schema statistics. Mainly before

1.) Any major change and release at database.
2.) Before upgrade.
3.) Migration.

In any above event, you may some event like performance issue to execution plan changed, you need to import the old statistics.

A.] Create a stat table:

Syntax:

exec DBMS_STATS.CREATE_STAT_TABLE('<table owner>','<enter a name for the stats table>','<tablespace to store the stats table');

Example:

exec DBMS_STATS.CREATE_STAT_TABLE('ASHISH','ASHISH_SCHEMA_STATS_BACKUP','ASHISH_DATA');

You can also use:

exec DBMS_STATS.CREATE_STAT_TABLE('ASHISH','ASHISH_SCHEMA_STATS_BACKUP');

B.] Verify if you have statistics table in DBA_OBJECTS

SQL> select owner,OBJECT_NAME,CREATED, object_type from dba_objects where object_name='ENOVIA_SCHEMA_STATS_BACKUP';

OWNER OBJECT_NAME CREATED OBJECT_TYPE
----------- --------------------------------------------------- ------------------ -------------------
ASHISH ASHISH_SCHEMA_STATS_BACKUP 03-FEB-17 TABLE
ASHISH ASHISH_SCHEMA_STATS_BACKUP 03-FEB-17 INDEX

SQL> select count(*) from ASHISH.ASHISH_SCHEMA_STATS_BACKUP;

COUNT(*)
----------
0

C.] Export schema statistics to statistics table:

SQL> exec dbms_stats.export_schema_stats('ASHISH','ASHISH_SCHEMA_STATS_BACKUP');

PL/SQL procedure successfully completed.

SQL> select count(*) from ASHISH.ASHISH_SCHEMA_STATS_BACKUP;

COUNT(*)
----------
77953

Best of luck

Change AWR reports Snap Interval and retention.

Use below to check the current interval for AWR SNAP.

SQL> set lines 200
SQL> col SNAP_INTERVAL format a20
SQL> col RETENTION format a20
SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- ---------- ----------------------------
3275763 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

Here interval is set to 1 hour and retention is set to 8 days.

To change the retention and interval settings. Interval will be set to 15 minutes and retention to 35 days (35x24x60 = 50400)

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 15, retention => 50400)

PL/SQL procedure successfully completed.

SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- -------------------------------------
3275763 +00000 00:15:00.0 +00035 00:00:00.0 DEFAULT

Oracle Database full size

Below commands gives the full size of database.

Size includes datafiles, controlfiles, redologs and tempfiles.

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from dual;

Monday, March 6, 2017

What is OS Watcher ?

Oracle OS Watcher (OSWatcher) is a tool to help DBA's to trouble shoot Database performance, luster reboot, node eviction, DB server reboot, DB instance Crash related issues and many more.

As we know, OS stats like top, mpstat, netstat plays an important role in Database trouble shooting but there is no way to keep historical date for these stats. Here, OS Watcher is the only rescue for Database Administrator. Suppose Yesterday, There was some performance issue on Database Node but you were not aware about that and when you know that the issue was resolved itself.

Now, DBA can get Database related stats from AWR reports but not OS related stats for last day, To overcome this challenge Oracle introduce OS Watcher utility, which collects OS stats data at a frequency of five minutes and keep it for seven days (default settings). So Now, DBA need not to worry about historical OS stats.

To Trouble shoot Database performance related issues AWR, ADDM and OS Watcher logs are the first place to start for a Remote DBA. Where as for Cluster reboot, node eviction, DB server reboot Alter log files, OS Watcher and System messages (/var/log/messages) plays an important role.

How to Install OS Watcher Utility

1. Download tar file from Oracle Support Article "OSWatcher Black Box.

2. Copy the file oswbb601.tar to the directory where oswbb is to be installed.

3. Extract tar file with “oracle” user
# tar xvf oswbb601.tar

4. Change to oswbb directory created.

5. Start OS Watcher utility using below command.

Example 1:

./startOSW.sh 60 10

This would start the tool and collect data at 60 second intervals and log the last 10 hours of data to archive files.

Example 2:

./startOSW.sh

This would use the default values of 30, 48 and collect data at 30 second intervals and log the last 48 hours
of data to archive files.

Example 3:

./startOSW.sh 20 24 gzip

This would start the tool and collect data at 20 second intervals and log the last 24 hours of data to archive files.
Each file would be compressed by running the gzip utility after creation.

STOPPING OSW:

To stop the OSW utility execute the stopOSW.sh command. This terminates all the processes associated with
the tool.

Example:

./stopOSW.sh

The default location of OS Watcher files is /opt/oracle.oswatcher/osw/archive. To collect OS Watcher files for a
particular day use below command.

# cd /opt/oracle.oswatcher/osw/archive

# find . -name '*25.05.16*' -print -exec zip /tmp/osw_`hostname`.zip {} \;
{where 16- year 05- Month 25-day}

Below are the list of sub folders created under archive folder

-bash-4.1$ ls

osw_ib_diagnostics oswiostat oswnetstat oswps oswvmstat
osw_rds_diagnostics oswmpstat oswprvtnet oswtop

Friday, August 5, 2016

Delete old .trc, .trm, .aud files older than X days +

Hi Folks,

Some time we need to delete old logs. And at the same time we want to keep files for last 2-3 days. Use below command at linux box.

For example, if you want to clear audit logs, please use below command.

find /u01/app/11.2.0.4/grid/rdbms/audit/ -mindepth 1 -type f -mtime +2 | xargs rm

Here :

"/u01/app/11.2.0.4/grid/rdbms/audit/ " is directory where find command will look
"-mtime + 2" is the current date - 2 days.

Thursday, July 14, 2016

System datafile added to filesystem location

Hi Folks,

Many time's this happens. Either due to typo error or accidently we add to filesystem and it was supposed to be in ASM file system.

Solution is to rename the datafile to new (ASM filesystem) location.

Here are steps to fix this and place the datafile back to ASM file system.

In my example, a system datafile was accidently added to dbs location. System datafile is different from non system datafile.

SQL> select file_name,file_id from dba_data_files where tablespace_name='SYSTEM';

FILE_NAME FILE_ID
------------------------------------------------ ---------------
+ASHISH_DATA/ORCL/datafile/system01.dbf 1
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ +ASHISH_DATA 5

Now to fix this:

1.) Start the database is mount mode.
2.) Using RMAN,
rman> copy datfile <datafile#> to '+DISKGROUP';
rman> switch datafile <datafile#> to copy;
3.) open the database;

Example:

You need know the datafile# of the file you want to rename.

Use below two statement to see datafile # and if any extents exists on that datafile

SQL> select count(*) from dba_extents where file_id=5;

COUNT(*)
----------
0

SQL> SQL> select file_id from dba_data_files where file_name='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ +ASHISH_DATA';

FILE_ID
----------
5

Now I have my datafile# as 5.

SQL> startup mount
ORACLE instance started.

Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 482345024 bytes
Database Buffers 243269632 bytes
Redo Buffers 2842624 bytes
Database mounted.
SQL>

Now database is in mount mode, connect to rman;

$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 14 06:08:58 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=3487895465, not open)

RMAN> copy datafile 5 to '+ASHISH_DATA';

Starting backup at 14-JUL-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=02 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/product/11.2.0.4/dbhome_1/ dbs/ +ASHISH_DATA
output file name=+ASHISH_DATA/ORCL/datafile/system.273.917057511 tag=TAG20160714T056450 RECID=1 STAMP=917057510
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 14-JUL-16

RMAN> switch datafile 5 to copy;

datafile 5 switched to datafile copy "+ASHISH_DATA/ORCL/datafile/system.273.917057511"

Now connect to sql prompt and open the database

SQL> alter database open;

Database altered.

SQL> select file_name,file_id from dba_data_files where tablespace_name='SYSTEM';

FILE_NAME FILE_ID
----------------------------------------------------- --------------
+ASHISH_DATA/ORCL/datafile/system01.dbf 1
+ASHISH_DATA/ORCL/datafile/system.273.917057511 5

Best of luck !!!!!!!!!!!!!

Monday, May 23, 2016

How to check Database is in restricted mode or not

Hi All,

Users are not allowed to login in restricted mode. You get ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Login to database and

SQL> select logins from v$instance;
.

Example:

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
TEST

SQL> Select logins from v$instance;

LOGINS
----------
RESTRICTED

SQL>

It should be ALLOWED.

Monday, May 11, 2015

ORA-01000: maximum open cursors exceeded

Many times we may see this error. This means we have crossed the limit of open cursor. You can check the value assigned for cursor

show parameter open_cursor

Solution 1:

Increase the value for open_cursor

ALTER SYSTEM SET OPEN_CURSORS=500 SID='*' SCOPE=BOTH;

Solution 2:

Tune the sql statement to limit it for creating multiple sessions. Below sql will help you to check the number of cursor open by sessions.

select sum(a.value) total_cur, avg(a.value) avg_cur,
max(a.value) max_cur, s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursor '
group by s.username, s.machine order by 1 desc;

Monday, September 1, 2014

Auditing functions and stored procedures in Oracle

How to know that a specific procedure or function has been executed by which user and when?

Lot many times we would like to track the execution of a fuction or SP. For example, who executed the fuction / SP? What time this was executed? and many more. You need to do that with the help of auditing database. Here I have shown an example of how to enable audit on function.

Please go through the below steps. Hope this helps you.

C:\Users\Ashish>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 7 15:17:34 2014

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

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

SQL> show parameter audit

NAME                                 TYPE                 VALUE
----------------------------- -----------          ------------------------------
audit_file_dest                   string             C:ASHISH\ADMIN\PRIMARY\ADUMP
audit_sys_operations         boolean          FALSE
audit_trail                          string              DB
SQL> set pages 1000
SQL> set lines 180
SQL>
SQL>
SQL> conn ashish/ashish
Connected.
SQL>
SQL>
SQL> create or replace function TEST_AUDIT_BY_ACCESS
2 return date
3 is
4 begin
5 return sysdate;
6 end;
7 /

Function created.

SQL>
SQL>
SQL>
SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL> audit execute on ashish.TEST_AUDIT_BY_ACCESS by access;

Audit succeeded.

SQL> select os_username,username,obj_name,owner from dba_audit_trail where obj_name='TEST_AUDIT_BY_ACCESS';

no rows selected

SQL> conn ashish/ashish
Connected.

SQL> select TEST_AUDIT_BY_ACCESS from dual;

TEST_AUDI
---------
07-AUG-14

SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
SQL> select os_username,username,obj_name,owner from dba_audit_trail where obj_name='TEST_AUDIT_BY_ACCESS';

OS_USERNAME        USERNAME         OBJ_NAME                                         OWNER
---------------                    -------------        ---------------------          -------------
Ashish                            ASHISH          TEST_AUDIT_BY_ACCESS                ASHISH

SQL>

You can fetch more columns from dba_audit_trail as per your requirement.

SQL> desc dba_audit_trail
Name                                                                                                  Null?    Type
----------------------------------------------------------------------------------------------------- --------

--------------------------------------------------------------------
OS_USERNAME                                                                                                    VARCHAR2(255)
USERNAME                                                                                                       VARCHAR2(30)
USERHOST                                                                                                       VARCHAR2(128)
TERMINAL                                                                                                       VARCHAR2(255)
TIMESTAMP                                                                                                      DATE
OWNER                                                                                                          VARCHAR2(30)
OBJ_NAME                                                                                                       VARCHAR2(128)
ACTION                                                                                                NOT NULL NUMBER
ACTION_NAME                                                                                                    VARCHAR2(28)
NEW_OWNER                                                                                                      VARCHAR2(30)
NEW_NAME                                                                                                       VARCHAR2(128)
OBJ_PRIVILEGE                                                                                                  VARCHAR2(16)
SYS_PRIVILEGE                                                                                                  VARCHAR2(40)
ADMIN_OPTION                                                                                                   VARCHAR2(1)
GRANTEE                                                                                                        VARCHAR2(30)
AUDIT_OPTION                                                                                                   VARCHAR2(40)
SES_ACTIONS                                                                                                    VARCHAR2(19)
LOGOFF_TIME                                                                                                    DATE
LOGOFF_LREAD                                                                                                   NUMBER
LOGOFF_PREAD                                                                                                   NUMBER
LOGOFF_LWRITE                                                                                                  NUMBER
LOGOFF_DLOCK                                                                                                   VARCHAR2(40)
COMMENT_TEXT                                                                                                   VARCHAR2(4000)
SESSIONID                                                                                             NOT NULL NUMBER
ENTRYID                                                                                               NOT NULL NUMBER
STATEMENTID                                                                                           NOT NULL NUMBER
RETURNCODE                                                                                            NOT NULL NUMBER
PRIV_USED                                                                                                      VARCHAR2(40)
CLIENT_ID                                                                                                      VARCHAR2(64)
ECONTEXT_ID                                                                                                    VARCHAR2(64)
SESSION_CPU                                                                                                    NUMBER
EXTENDED_TIMESTAMP                                                                                             TIMESTAMP(6) WITH TIME

ZONE
PROXY_SESSIONID                                                                                                NUMBER
GLOBAL_UID                                                                                                     VARCHAR2(32)
INSTANCE_NUMBER                                                                                                NUMBER
OS_PROCESS                                                                                                     VARCHAR2(16)
TRANSACTIONID                                                                                                  RAW(8)
SCN                                                                                                            NUMBER
SQL_BIND                                                                                                       NVARCHAR2(2000)
SQL_TEXT                                                                                                       NVARCHAR2(2000)
OBJ_EDITION_NAME                                                                                               VARCHAR2(30)
DBID                                                                                                           NUMBER

Monday, February 24, 2014

Unable to drop ASM diskgroup getting ORA-15027

Hello,

Recently I came across such situations that I was unable to drop the ASM diskgroup. I was getting ORA-15027 . Later I realized that the spfile fot the ASM instance is available in the diskgroup.

To solve this, first we need to move the spfile to different location. Once that is done, we can delete the desired diskgroup.

State	Type	Rebal	AU	Total_MB	Free_MB	Name
MOUNTED	EXTERN	N	1048576	1488042	1487967	DATA

Here is the desired, diskgroup to be dropped.

ASMCMD> dropdg -r DATA
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "DATA" precludes its dismount (DBD ERROR: OCIStmtExecute)
ASMCMD> spget
+DATA/asm/asmparameterfile/registry.253.840344755
ASMCMD> spmove '+DATA/asm/asmparameterfile/registry.253.840344755' '/ora/app/oracle/product/11.2.0.3/grid/dbs/spfile+ASM.ora';
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/asm/asmparameterfile/registry.253.840344755' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
ASMCMD> spget
/ora/app/oracle/product/11.2.0.3/grid/dbs/spfile+ASM.ora;
ASMCMD> exit
-bash-3.2$ sqlplus "/as sysasm"

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 24 08:31:24 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option

SQL> shut immediate
ASM diskgroups volume disabled
ASM diskgroups dismounted
ASM instance shutdown
SQL> startup
ASM instance started

Total System Global Area 413372416 bytes
Fixed Size                  2228904 bytes
Variable Size             385977688 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
ASM diskgroups volume enabled
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /ora/app/oracle/product/11.2.0
                                                 .3/grid/dbs/spfile+ASM.ora;
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
-bash-3.2$ asmcmd
ASMCMD> lsdg
State    Type    Rebal Sector Block       AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N         512   4096 1048576   1488042 1487967                0         1487967              0             N DATA/
ASMCMD> spget
/ora/app/oracle/product/11.2.0.3/grid/dbs/spfile+ASM.ora;
ASMCMD> dropdg -r DATA
ASMCMD> lsdg
ASMCMD> exit

Good luck !!!!!!!!!!!!!!!!!!!!

Move asm spfile from file system to asm filesystem

Hello ,

Here is the below step to copy the asm spfile from non asm filesystem to asm filesystem.

1.) Make sure you have pfile in grid home

bash-3.2$ ls -ltr
total 24
-rw-r--r-- 1 oracle oinst 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinst 1536 Mar 29 05:06 orapw+ASM
-rw-rw---- 1 oracle oinst 1200 Mar 29 08:31 ab_+ASM.dat
-rw-rw---- 1 oracle oinst 1544 Mar 29 09:02 hc_+ASM.dat
-rw-r--r-- 1 oracle oinst 359 Mar 29 09:06 init+ASM.ora

bash-3.2$ sqlplus "/as sysasm"

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 24 09:11:12 2014

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

Connected to an idle instance.

SQL> startup pfile='init+ASM.ora'
ASM instance started

Total System Global Area 413372416 bytes
Fixed Size                  2228904 bytes
Variable Size             385977688 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
ASM diskgroups volume enabled
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

2.) Create spfile from pfile

SQL> create spfile='+DATA' from pfile='init+ASM.ora';

File created.

SQL> shut immediate
ASM diskgroups volume disabled
ASM diskgroups dismounted
ASM instance shutdown
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
bash-3.2$ sqlplus "/as sysasm"

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 24 09:17:00 2014

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

Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area 413372416 bytes
Fixed Size                  2228904 bytes
Variable Size             385977688 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
ASM diskgroups volume enabled
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/asm/asmparameterfile/registry.253.849870843

That's all.

Error you may encounter is :

ORA-15124: ASM file name contains an invalid alias name

Solution is ==> Don't try to give absolute name for spfile, just mention the diskgroup name as above example.

Thursday, December 5, 2013

Apply latest PSU patch on Oracle 11gr2

Hello Guys,

Here are the below step to apply latest PSU patch to you databases running on ASM file system. Note, this is not a clustered environment. This is single instance database with ASM file system.

Please download the latest PSU patch from metalink. In my case latest patch id is 17272731 and I have downloaded the file as p17272731_112030_Linux-x86-64.zip

I have copied this zip file to "/oradata/PSUOCT2013/" directory location in my server. And then unzip the file downloaded from metalink.

Here are few details before we proceed:

Oracle Version: 11.2.0.3.0
OS Version: Red Hat Enterprise Linux Server release 5.10 (x86_64)
Oracle Doc ID: 1494646.1
ORACLE_HOME: /oradata/app/oracle/product/11.2.0.3/db/
GRID_HOME: /oradata/app/oracle/product/11.2.0.3/grid/

Note : Take full backup of all the database before starting the activity.

Step 1: Stop all the Oracle services running.

If this is a GI Home environment, as the database home owner execute:
Stop the ASM Instance and Databases
#crsctl stop resources

Step 2: Backing up the Grid Home and RDBMS Home

Make a tar backup for both the homes.
tar -zcvf grid-rdbms-home_ddmmyy.tar.gz /oradata/app/oracle

Step3: Run the pre root script

As the root user execute:
# /oradata/app/oracle/product/11.2.0.3/grid/crs/install/rootcrs.pl -unlock

If this is an Oracle Restart Home, as the root user execute:
# /oradata/app/oracle/product/11.2.0.3/grid/crs/install/roothas.pl –unlock

Step 4: Apply the CRS patch

As the GI home owner execute:
$/oradata/app/oracle/product/11.2.0.3/grid/OPatch/opatch napply -oh /oradata/app/oracle/product/11.2.0.3/grid –local /oradata/PSUOCT2013/17076717

As the GI home owner execute:
$/oradata/app/oracle/product/11.2.0.3/grid/OPatch/opatch apply -oh /oradata/app/oracle/product/11.2.0.3/grid -local /oradata/PSUOCT2013/16902043

Step 5: Run the pre script for DB component of the patch

As the database home owner execute:
$/oradata/PSUOCT2013/17076717/custom/server/17076717/custom/scripts/prepatch.sh -dbhome /oradata/app/oracle/product/11.2.0.3/db

Step 6: Apply the DB patch

As the database home owner execute:
$/oradata/app/oracle/product/11.2.0.3/db/OPatch/opatch napply –oh /oradata/app/oracle/product/11.2.0.3/db –local /oradata/PSUOCT2013/17076717/custom/server/17076717

$/oradata/app/oracle/product/11.2.0.3/db/OPatch/opatch apply -oh /oradata/app/oracle/product/11.2.0.3/db -local /oradata/PSUOCT2013/16902043

Step 7: Run the post script for DB component of the patch.

As the database home owner execute:
/oradata/PSUOCT2013/17076717/custom/server/17076717/custom/scripts/postpatch.sh -dbhome /oradata/app/oracle/product/11.2.0.3/db

Step 8: Run the post script

If this is an Oracle Restart Home, as the root user execute:
# /oradata/app/oracle/product/11.2.0.3/grid/crs/install/roothas.pl -patch

Step 9: Start the Oracle services

If this is an Oracle Restart Home environment, as the database home owner execute:
#crsctl start resource -all

Start the ASM Instance and Databases

Step 10:

For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT

Good luck !!!!!!!!!!

Monday, September 16, 2013

Not able to see password value in Oracle 11g

Many of us are habitual of resetting the user password by values. If you will fire the command in Oracle 11g and try to query DBA_USERS. You will find no values for password. See below.

SQL> select username,password from dba_users where username='SCOTT';

USERNAME                       PASSWORD
------------------------------ ------------------------------
SCOTT

To get the password value in Oracle 11g , you need to query USER$ table as given below.

SQL> select NAME,PASSWORD from SYS.USERS$ where name='SCOTT';

NAME                           PASSWORD
------------------------------ ------------------------------
SCOTT                          A654888Z34402S74

Now you can change the password using values.

Oracle 18c onwards,

SQL> select spare4 from sys.user$ where name='GSMUSER';

SPARE4
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
S:D3258850C99652C76A8D12BB8F5764249179BCD83E2A94B8AE24D1E3EC65;T:3E0EF13A60C6511DA27BA714B9A99B5D77E94ADD2AB9E76A9C36F935DF8714DFF3E956756E49AEECD1C35AA949C8DE548264602AB6706B037BFE8A2378BEECA0D4330F8EE0B26E4B0792CDF0D5BB5D6A

SQL>alter user GSMUSER identified by values 'S:D3258850C99652C76A8D12BB8F5764249179BCD83E2A94B8AE24D1E3EC65;T:3E0EF13A60C6511DA27BA714B9A99B5D77E94ADD2AB9E76A9C36F935DF8714DFF3E956756E49AEECD1C35AA949C8DE548264602AB6706B037BFE8A2378BEECA0D4330F8EE0B26E4B0792CDF0D5BB5D6A';

Tuesday, August 6, 2013

Listener not starting on Oracle 11g Transparent gateway server

Oracle transparent gateway is a product that can be used to access data from non-Oracle databases (eg. MS-SQL,DB2,etc), and even non-relational data sources.

Transparent Gateways are most commonly used and enable access to non-Oracle data sources as if they were Oracle databases. They allow you to interact with these foreign databases without regard to differences in SQL implementation and with the added ability to perform distributed processing.

Some of the available transparent gateways:

Microsoft SQL Server

Informix

Sybase

IBM DB2

IBM DB2/400

IBM DRDA

EDA SQL

Please see below link for solution:

http://onelineroracledba.blogspot.in/2013/06/listener-not-starting-on-oracle11g.html

Good luck !!!!

Create physical standby database using RMAN duplicate command from active database feature

This is new feature in Oracle 11g. We can create a standby database using RMAN duplicate command.

Please refer below link for step by step method:

http://onelineroracledba.blogspot.in/2013/06/standby-database-using-11g-rman.html

Good luck !!!!

Friday, July 26, 2013

Longest running session in Oracle database

This script helps in identifying the session which is running on Oracle database from long time. In various performance issue scenario, DBA and application developer is keen to know about the session which is taking long time.

set pages 10000
set lines 180
col USERNAME for a25
col SESSION_RUNNING_FROM for a50
SELECT s.SID,s.SERIAL#,s.USERNAME,s.STATUS,s.SQL_ID,floor((sysdate-s.logon_time)*24) || ' HOURS ' || mod(floor((sysdate-s.logon_time)*24*60),60) || ' MINUTES ' || mod(floor((sysdate-s.logon_time)*24*60*60),60)|| ' SECS ' Session_running_since FROM v$session s where s.username<>'SYS' order by SESSION_RUNNING_SINCE desc;

SID	SERIAL#	USERNAME	STATUS	SQL_ID	SESSION_RUNNING_SINCE
158	30	ASHISH	ACTIVE	90ya7z9gh6cx1	0 HOURS 48 MINUTES 44 SECS
152	34	ASHISH	INACTIVE		0 HOURS 40 MINUTES 20 SECS

With the above output, it becomes easier to identify the session which is running from long time. Session_running_since column will show the session running in Hours:Minutes:Seconds(HH:MM:SS) format. Also sid and sql_id is available to investigate further.

Tuesday, July 23, 2013

ORA-00980: synonym translation is no longer valid

As the name suggests, we can get an idea that while synonym called it is not accessible. There are more than one possibility for this error.

1.) The base table, on which synonym is created, is deleted.
2.) The entire schema of the base table is dropped and we forget to drop the synonym.
3.) If synonym created on remote database, that is, via dblink. Then please check if dblink is working.

Below query will all the synonyms in the database.

select * from dba_synonyms where table_owner not in ('SYS','SYSTEM');

To avoid looking at the large number of synonyms , you can specify synonym name to look for the table name.

select * from dba_synonyms where table_owner not in ('SYS','SYSTEM') and synonym_name='TEST';

Saturday, July 20, 2013

Change Database Name and DBID Using NID

NID is a utility to change the database name and database ID. There are many scenario where we need to restore the database backup and we required to change the name of the database. Recreating a controlfile is another option, but nid makes it much more simpler.

You can find this utility in $ORACLE_HOME/bin/nid

The syntax is :

nid TARGET=sys@"connection_entry" DBNAME="NewDBName"

Example:

nid TARGET=sys@ASHISHDB DBNAME=ASHIUAT

===================================================

Below is the example to change the database name:

C:\Users\ashish>sqlplus sys as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Oct 5 19:38:51 2012

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME

---------

ASHISHDB

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\ashish>nid TARGET=sys@ASHISHDB DBNAME=ASHIUAT

DBNEWID: Release 11.1.0.6.0 - Production on Fri Oct 5 19:39:06 2012

Password:

Connected to database ASHISHDB (DBID=356825129)

Connected to server version 11.1.0

Control Files in database:

E:\DATA\DATAFILE\ASHISHDB\CONTROL001.CTL

E:\DATA\DATAFILE\ASHISHDB\CONTROL002.CTL

E:\DATA\DATAFILE\ASHISHDB\CONTROL003.CTL

Change database ID and database name ASHISHDB to ASHIUAT? (Y/[N]) => Y

Note: If you do not want to change DBID press N.

Proceeding with operation

Changing database ID from 356825129 to 2425101660

Changing database name from ASHISHDB to ASHIUAT

Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL001.CTL - modified

Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL002.CTL - modified

Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL003.CTL - modified

Datafile E:\DATA\DATAFILE\ASHISHDB\SYSTEM.DBF - dbid changed, wrote new name

Datafile E:\DATA\DATAFILE\ASHISHDB\USR01.DBF - dbid changed, wrote new name

Datafile E:\DATA\DATAFILE\ASHISHDB\SYSAUX.DBF - dbid changed, wrote new name

Datafile E:\DATA\DATAFILE\ASHISHDB\UNDOTBS1.DBF - dbid changed, wrote new name

Datafile E:\DATA\DATAFILE\ASHISHDB\DATA01.DBF - dbid changed, wrote new name

Datafile E:\DATA\DATAFILE\ASHISHDB\TEMP01.DBF - dbid changed, wrote new name

Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL001.CTL - dbid changed, wrote new name

Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL002.CTL - dbid changed, wrote new name

Control File E:\DATA\DATAFILE\ASHISHDB\CONTROL003.CTL - dbid changed, wrote new name

Instance shut down

Database name changed to ASHIUAT.

Modify parameter file and generate a new password file before restarting.

Database ID for database ASHIUAT changed to 2425101660.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

Now modify the pfile according to new database name:

db_name=ASHIUAT

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1073131520 bytes

Fixed Size 2121800 bytes

Variable Size 658509752 bytes

Database Buffers 406847488 bytes

Redo Buffers 5652480 bytes

Database mounted.

SQL> select name from v$database;

NAME

---------

ASHIUAT

SQL> alter database open resetlogs;

Database altered.

Thats all. Here are the steps to change the database name and database ID. Dont forget to modify the listener.ora and tnsnames.ora.

ORA-29278: SMTP transient error: 421 4.3.2 Service not available, closing transmission channel

While setting up access control list in Oracle 11g you may face this error. This error is purely linked to the smtp server.

Firstly, you can query the dictionaryDBA_NETWORK_ACLS and check if you have added correct entry under host. Host column will be displaying your SMTP server.

For example:

select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;

ACL HOST LOWER_PORT UPPER_PORT

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

/sys/acls/SMTP_SERVER.xml 10.67.65.188

SELECT * FROM TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('10.67.65.188'));

COLUMN_VALUE

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

10.67.65.188

10.67.65.*

10.67.*

10.*

If the smtp host entry is ok. Then contact sysadmin team to check the SMTP server services.