Tag: dba

Where is the DBA in the DevOps tools ?

Posted on by Osama Mustafa in Database

  • Database administrator job has been changed and it’s not like before any more, In the past The DBA responsibility was limited within database which mean troubleshooting, backup , Performance tuning, high availability .. etc after few years new layer has been added to the responsibility which is application server layer for example weblogic ,Oracle embedded the weblogic with most of their products such as enterprise manager cloud, E-business suits, as i remember after three years another layer added to DBA knowledge which is virtualization and regarding to this virtualization  made the DBA life more easier and not like before, solution providing another layer of backup and server management.

However now everything has been changed and the market working under new term which is CLOUD including different vendor AWS, Azure, and Oracle, As DBA your knowledge shouldn’t stop by only Database, within cloud there is new tools that can help the DBA with their daily Job and it’s called Devops.

DBA usually review each change request to ensure that it is well thought out,They are in charge of monitoring their databases and keeping them available and high-performing, manage access to and the overall security of the platform.

Database automation frees the DBA from the bottleneck of the past that delayed application releases since there is different environment production, Pre-Production, Development or Testing, the DevOps Automation relieved of the pressures of constantly having to juggle and merge various teams’ database changes, are now free to help their organisations take bigger steps forward in ongoing innovation.

But what is the Devops ? And How can i use to make my Job Easier

DevOps is the combination of cultural philosophies, practices, and tools that increases an organization’s ability to deliver applications and services at high velocity: evolving and improving products at a faster pace than organizations using traditional software development and infrastructure management processes. This speed enables organizations to better serve their customers and compete more effectively in the market.

Benefits of DevOps
  • Speed
  • Rapid Delivery
  • Reliability
  • Scale
  • Security

DevOps Practices

  • Continuous Integration
  • Continuous Delivery
  • Microservices
  • Infrastructure as Code
  • Monitoring and Logging
  • Communication and Collaboration
DevOps practices for DBA and Data Team focus on the Tools, practices and techniques that are useful for the Data team to use for Development activities, Testing setup and execution, Collaboration techniques and Deployment tools and techniques. The aim of this site is to further the practice and help the DBA’s and Data team to use these practices to become productive and become part of the delivery team and embrace the devops movement.
DevOps Tools
  • Docker
  • Jenkins
  • Ansible
  • Puppet
  • Nagios
  • Monit
  • Behat
  • Chef
There is more tools for sure each one of these tools having it’s own purpose for example Docker is one of the powerful container solutions, A container image is a lightweight, stand-alone, executable package of a piece of software that includes everything needed to run it: code, runtime, system tools, system libraries, settings Available for both Linux and Windows based apps, containerized software will always run the same, regardless of the environment. Containers isolate software from its surroundings, for example differences between development and staging environments and help reduce conflicts between teams running different software on the same infrastructure.
Cheers
Osama Mustafa 

What Is $ADMIN_SCRIPT_HOME ???

Posted on by Osama Mustafa in Application
As We All Know if you need to start/shutdown Oracle Apps you need go $ADMIN_SCRIPT_HOME In R12 Or $CONTEXT_HOME in R11

This Article Explain What These Scripts Do : 

adstrtal.sh

Master script to start all components/services of middle tier or application tier. This script will use Service Control API to start all services which are enabled after checking them in context file (SID_HOSTNAME.xml or CONTEXT_NAME.xml)

adstpall.sh

Master script to stop all components/services of middle tier or application tier


adalnctl.sh

Script to start / stop apps listener (FNDFS and FNDFS). This listener will file will be in 10.1.2 ORACLE_HOME (i.e. Forms & Reports Home)
listener.ora file will be in $INST_TOP/apps/$CONTEXT_NAME/ora/10.1.2/network/admin directory
(Mostly similar to one in 11i with only change in ORACLE_HOME i.e. from 8.0.6 to 10.1.2 )

adapcctl.sh

Script to start/stop Web Server or Oracle HTTP Server. This script uses opmn (Oracle Process Manager and Notification Server) with syntax similar to opmnctl [startstop]proc ohslike opmnctl stopproc ohs .

adcmctl.sh

Script to start / stop concurrent manager,

adformsctl.sh

Script to start / stop Forms OC4J from 10.1.3 Oracle_Home. This script will also use opmnctl to start/stop Forms OC4J like
opmnctl stopproc type=oc4j instancename=forms

adformsrvctl.sh

This script is used only if you wish to start forms in socket mode. Default forms connect method in R12 is servlet.
If started this will start frmsrv executable from 10.1.2 Oracle_Home in Apps R12

adoacorectl.sh

This script will start/stop oacore OC4J in 10.1.3 Oracle_Home. This scripts will also use opmnctl (similar to adapcctl & adformsctl) to start oacore instance of OC4J like
opmnctl startproc type=oc4j instancename=oacore

adoafmctl.sh

 This script will start/stop oafm OC4J in 10.1.3 Oracle_Home. This scripts will also use opmnctl (similar to above) to start oacore instance of OC4J like
opmnctl startproc type=oc4j instancename=oafm

adopmnctl.sh

This script will start/stop opmn service in 10.1.3 Oracle_Home. opmn will control all services in 10.1.3 Oracle_Home like web server or various oc4j instances. If any services are stopped abnormally opmn will/should start them automatically.

Thank You 
Osama Mustafa 
     

Useful Query To check Some Privileges

Posted on by Osama Mustafa in Scripts
Check for whether user has full database export/import privilege 


Query : 



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;

Check for user has create table or create session privilege.


Query  

SQL>SET lines 100 pages 100
COL privilege FOR a40
SELECT grantee, privilege 
  FROM dba_sys_privs 
 WHERE (grantee IN ('TEST', 'PUBLIC') 
        OR grantee IN (SELECT granted_role FROM dba_role_privs 
                        WHERE grantee IN ('TEST', 'PUBLIC'))) 
   AND privilege IN ('CREATE SESSION', 'CREATE TABLE') 
 ORDER BY 1,2;

Check for granted privileges on directory objects.


Query : 

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;

Thank You
osama Mustafa

The difference between NOT IN and NOT EXISTS

Posted on by Osama Mustafa in Others
There is a popular misconception that NOT IN and NOT EXISTS are two ways to filter out rows present in one table and not in another table, with both methods being usable interchangeably. Popular wisdom is that the difference between the two is only in terms of performance (being on the basis of whether the larger table is the “outer” or the “inner” in the query) but that the results are the same.

However, we must remember that such an operation being an Anti-Join fails when NULLs are involved. In Oracle a NULL cannot be compared to any other value, not even another NULL. Therefore, a NOT IN operation would fail if the result set being probed returns a NULL. In such a case, the results of a NOT IN query is 0 rows while a NOT EXISTS query would still show the rows present in the one table but not in the other table.

Here is a simple demonstration :


SQL> -- create the two test tables
SQL>
SQL> drop table results_table purge;

Table dropped.

SQL> drop table query_table purge;

Table dropped.

SQL>
SQL> create table results_table
 2  as select owner,object_name,object_type from dba_objects where owner in ('HEMANT','DBSNMP','OUTLN');

Table created.

SQL> create table query_table
 2  as select owner,object_name,object_type from dba_objects where owner in ('DBSNMP','OUTLN');

Table created.

SQL>
SQL> -- a NOT IN query
SQL> select r.owner, r.object_name from results_table r
 2  where r.owner not in (select q.owner from query_table q)
 3  order by 1,2;

OWNER                          OBJECT_NAME                                                                                         
------------------------------ ------------------------------                                                                      
HEMANT                         DUPDB                                                                                               
HEMANT                         MY_T_A                                                                                              
HEMANT                         RESULTS_TABLE                                                                                       
HEMANT                         SOURCE_TABLE                                                                                        
HEMANT                         TEST_APPEND                                                                                         
HEMANT                         TRACE_USER_SESSIONS                                                                                 
HEMANT                         TRACE_USER_SESSIONS_BEGIN                                                                           
HEMANT                         TRACE_USER_SESSIONS_END                                                                             

8 rows selected.

SQL>
SQL> -- a NOT EXISTS query
SQL> select r.owner, r.object_name from results_table r
 2  where not exists (select '1' from  query_table q where r.owner=q.owner)
 3  order by 1,2;

OWNER                          OBJECT_NAME                                                                                         
------------------------------ ------------------------------                                                                      
HEMANT                         DUPDB                                                                                               
HEMANT                         MY_T_A                                                                                              
HEMANT                         RESULTS_TABLE                                                                                       
HEMANT                         SOURCE_TABLE                                                                                        
HEMANT                         TEST_APPEND                                                                                         
HEMANT                         TRACE_USER_SESSIONS                                                                                 
HEMANT                         TRACE_USER_SESSIONS_BEGIN                                                                           
HEMANT                         TRACE_USER_SESSIONS_END 



8 rows selected.

SQL>
SQL> ---
SQL> REM  So far, NOT IN  and  NOT EXISTS  have presented the same results
SQL>
SQL> REM  What happens if there is a row with a NULL value ?
SQL>
SQL> insert into query_table values (NULL,'ABCDEFGH','TABLE');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> -- retry the NOT IN query
SQL> select r.owner, r.object_name from results_table r
 2  where r.owner not in (select q.owner from query_table q)
 3  order by 1,2;

no rows selected

SQL>
SQL> -- retry the NOT EXISTS query
SQL> select r.owner, r.object_name from results_table r
 2  where not exists (select '1' from  query_table q where r.owner=q.owner)
 3  order by 1,2;

OWNER                          OBJECT_NAME                                                                                         
------------------------------ ------------------------------                                                                      
HEMANT                         DUPDB                                                                                               
HEMANT                         MY_T_A                                                                                              
HEMANT                         RESULTS_TABLE                                                                                       
HEMANT                         SOURCE_TABLE                                                                                        
HEMANT                         TEST_APPEND                                                                                         
HEMANT                         TRACE_USER_SESSIONS                                                                                 
HEMANT                         TRACE_USER_SESSIONS_BEGIN                                                                           
HEMANT                         TRACE_USER_SESSIONS_END                                                                             

8 rows selected.

SQL>
SQL> ---
SQL> REM  Surprise ?!   The NOT IN returned 0 rows !
SQL> REM  Why ?  Because of the presence of a NULL in the query_table !
SQL> REM
SQL> REM  REMEMBER : A "NOT IN"  anti-join fails because a NULL returned cannot be compared !
SQL>
SQL> --
SQL> REM One "workaround" is to filter out rows which contain NULLs
SQL> REM     .... but think carefully before you do so. Are you sure you want to exclude them ?
SQL>
SQL> REM In the ideal world, such columns should be defined as NOT NULL columns !
SQL> REM That would be the right schema design !
SQL>
SQL> -- test the suggested workaround
SQL> select r.owner, r.object_name from results_table r
 2  where r.owner not in (select q.owner from query_table q WHERE OWNER IS NOT NULL)
 3  order by 1,2;


OWNER                          OBJECT_NAME                                                                                         
------------------------------ ------------------------------                                                                      
HEMANT                         DUPDB                                                                                               
HEMANT                         MY_T_A                                                                                              
HEMANT                         RESULTS_TABLE                                                                                       
HEMANT                         SOURCE_TABLE                                                                                        
HEMANT                         TEST_APPEND                                                                                         
HEMANT                         TRACE_USER_SESSIONS                                                                                 
HEMANT                         TRACE_USER_SESSIONS_BEGIN                                                                           
HEMANT                         TRACE_USER_SESSIONS_END                                                                             

8 rows selected.

SQL> 
 
 
 
Finally I would thank Hemant for this amazing Article .
I posted to make it more popular and useful for the people who wants to learn
something new .
 
Osama mustafa

How to recreate DBA roles if accidentally drop?

Posted on by Osama Mustafa in Scripts
VERSION 7.X:   From Server Manager, logged in as internal or sys, run the following commands:

         SVRMGRL > create role dba;
        SVRMGRL > grant all privileges to dba with admin option;

VERSION 8.X:   From SQL*Plus or Server Manager, logged in as internal or sys, run the   following commands:   

SQL > create role dba;       
SQL > grant all privileges to dba with admin option;       
SQL > grant select_catalog_role to dba with admin option;       
SQL > grant execute_catalog_role to dba with admin option;       
SQL > grant delete_catalog_role to dba with admin option;

Explanation:  ============    
The DBA role is created at database creation time by the “sql.bsq” script. (The “sql.bsq” script is typically found in the $ORACLE_HOME/dbs or the $ORACLE_HOME/rdbms/admin directory.)  Additional grants are made to dba through other scripts in the admin directory.  For example, exp_full_database  and imp_full_database in catexp.sql.

Note : Version 8.X will works On 10g , 11g

Thank you
Osama mustafa

Upgrade 11.1.0.6 to 11.1.0.7 – APPS DBA

Posted on by Osama Mustafa in Application
On each application tier server node,
shut down all server processes or services.

On the database tier server node,
shut down the database listener in the old Oracle home.

On the database server node,
as the owner of the Oracle 11g file system and database instance, unzip and extract the 11.1.0.7 patch set file for your platform.

Use the runInstaller in the patchset to start the OUI.

Once the OUi starts from the LOV s in the Oracle_home list choose the appropriate one to be upgraded and the location also.

The present oracle_home and its location will appear if the environment is set correctly.

Proceed with the next steps on the OUI screen.

On next Screen it will Say Configuration Assistant has failed.Skip this Step since its not required for Oracle E-Business Suite .

MOST IMPORTANT STEP IN UPGRADE .
After Installation you need to Apply the RDBMS patches7486407 and 7684818

Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node.

$ sqlplus / as sysdba
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> shutdown normal;
SQL> startup;
SQL> exit;

Thank You
Osama mustafa

Check Rac Status

Posted on by Osama Mustafa in Database

hi All Again ,

As Oracle Database administrator its your job to check rac status on your server, i know its little creepy when we heard about RAC,DG but its all about the command and how , when to use it ? !!!

Sure you need to be careful when you are using commands such as “crs , Srvtl” and stuff like that .

I will make it easy for you save this command in the notepad (you can find in the end of this article)
so everytime you need to check RAC status and you need to do is choose your command :

Let start

*To check RAC STATUS ON OS :
Use :
crs_stat -t

*Check Node Availability :
Status nodeapps -n

*Check your ASM :
srvctl status asm -n

ok but i need to check database status how ?
*for Database status :
srvctl status database -d esxrac

And what about service for this database is it work fineeeeee :
* Database Services :
srvctl status service -d esxrac

And Finally
* Cluster Status :
crsctl check crs

As we see its all about command if you how to use it RAC will be easy otherwise don’t use it if you don’t know anything about it because it maybe destroy everything . BE CAREFUL

Where you Go its not the end , i need to check RAC inside the Database (SQL) :
lets Begin 🙂

I need to check Rac Instance if it works fine in normal database we use v$instance
here in rac just add gv$instance .
** select instance_name, host_name, archiver, thread#, status
from gv$instance ;

What About datafiles :
**select file_name, bytes/1024/1024 from dba_data_files ;

Getting the status of all the groups, type, membership :
**select group#, type, member, is_recovery_dest_file from v$logfile order by group#

But , Wait what about ASM we need to check it too :
To check ASM Disk :
**select group_number, name,allocation_unit_size alloc_unit_size,state,type,total_mb,usable_file_mb
from v$asm_diskgroup;

Information About ASM instance :
select group_number, file_number, bytes/1024/1024/1024 GB, type, striped, modification_date from v$asm_file
where TYPE != ‘ARCHIVELOG’

Thank You very Much , just Bookmark my blog all i keep post everything useful for DBA’s

And you can Download notepad from HERE