AIX, Linux

Aix/Linux/Solaris commands for DBA

1) How many Oracle instances running on OS
ps -aef | grep pmon

2) Seting unix Prompt.
export PS1=”`hostname`:$ORACLE_SID:$PWD>”

PS1 – The value of this parameter is expanded (see PROMPTING below) and used as the primary prompt string. The default value is \s-\v\$ .
PS2 – The value of this parameter is expanded as with PS1 and used as the secondary prompt string. The default is >
PS3 – The value of this parameter is used as the prompt for the select command
PS4 – The value of this parameter is expanded as with PS1 and the value is printed before each command bash displays during an execution trace. The first character of PS4 is replicated multiple times, as necessary, to indicate multiple levels of indirection. The default is +

(ref : https://www.cyberciti.biz/tips/howto-linux-unix-bash-shell-setup-prompt.html )

3) Useful commands in Aix/Linux/Solaris.
Ekran Alıntısı1

Advertisements
Standard
Awr / Ash Report, Oracle

AWR Reports Rac and Single Oracle Database

1) AWR various Report.

SQL> @?/rdbms/admin/awrsqrpt.sql –> awr report for only single sql_id
SQL> @?/rdbms/admin/awrrpt.sql –> Traditional awr report for instance.

2) RAC Related awr Report

In 11gR2 there are two new scripts awrgrpt.sql AND awrgdrpt.sql for RAC

SQL> @?/rdbms/admin/awrgrpt.sql — AWR Global Report (RAC) (global report)
SQL> @?/rdbms/admin/awrgdrpt.sql — AWR Global Diff Report (RAC)

Other important scripts under $ORACLE_HOME/rdbms/admin

SQL> @?/rdbms/admin/spawrrac.sql — Server Performance RAC report
SQL> @?/rdbms/admin/awrsqrpt.sql — Standard SQL statement Report
SQL> @?/rdbms/admin/awrddrpt.sql — Period diff on current instance
SQL> @?/rdbms/admin/awrrpti.sql — Workload Repository Report Instance (RAC)

Standard
Oracle

Find Oracle users with DBA or different privileges

Hello, How do I identify all Oracle users who have been granted DBA, Deployer or Etc. privileges. This would include SYSDBA and the DBA role granted. What are some swcript examples for finding these users?

basic scripts.
SQL> select * from dba_role_privs where granted_role=’DBA’;
SQL> select * from dba_role_privs where granted_role=’Deployer’;

The v$pwfile_users view contains a list of all users who have been granted the SYSDBA or SYSOPER privilege.

SQL> select * from v$pwfile_users;

ref: http://rampant-books.com/book_0814_audit.htm

br. erman.

Standard
ASM

Administering ASM Diskgroups

erman

About Mirroring and Failure Groups
Mirroring protects data integrity by storing copies of data on multiple disks. When you create a disk group, you specify an Oracle ASM disk group type based on one of the following three redundancy levels:

Normal for 2-way mirroring

High for 3-way mirroring

External to not use Oracle ASM mirroring, such as when you configure hardware RAID for redundancy

How to query existing ASM disk groups and attributes
SELECT INSTNAME,
G.NAME,
sum(reads) reads,
sum(bytes_read),
sum(writes),
sum(bytes_written)
FROM v$asm_diskgroup g, v$asm_disk_iostat d
WHERE G.GROUP_NUMBER = D.GROUP_NUMBER
group by d.instname,g.name;

Viewing disk group attributes with V$ASM_ATTRIBUTE
SELECT dg.name AS diskgroup, SUBSTR(a.name,1,18) AS name,
SUBSTR(a.value,1,24) AS value, read_only FROM V$ASM_DISKGROUP dg,
V$ASM_ATTRIBUTE a WHERE dg.name = ‘DATAMARS’
AND dg.group_number = a.group_number;

Viewing the compatibility of a disk group with V$ASM_DISKGROUP
SELECT name AS diskgroup, compatibility AS asm_compat,
database_compatibility AS db_compat FROM V$ASM_DISKGROUP;

Viewing disks in disk groups with V$ASM_DISK
SELECT SUBSTR(d.name,1,16) AS asmdisk, d.mount_status, d.state,
dg.name AS diskgroup FROM V$ASM_DISKGROUP dg, V$ASM_DISK d
WHERE dg.group_number = d.group_number;

Standard
Troubleshooting

LRM-00109: could not open parameter file

Hello Everyone,

When trying to start the instance , getting this ..
Enter user-name: sys as sysdba;
Enter password:
Connected to an idle instance.
SQL> startup;

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/db_1/dbs/initDemo.ora’

solutuon one :
Check the Oracle SID name.  Wrong settings in bash profile.

Solution two :

I used to face this issue.

I realized that the error can be caused by the pfile not being found. You have to start the Oracle instance using a fully referenced pfile by issuing the following command:

SQL> startup pfile=<your pfile full path here>

path location: cd $ORACLE_HOME/db/dbs/init<SID>.ora

demo : /oracle/product/11.2.0.4/db/dbs/initDEMO.ora

SQL> startup pfile=’/oracle/product/11.2.0.4/db/dbs/initDEMO.ora’;

then sql>create spfile from pfile;

have a good day.

Standard
Oracle, Troubleshooting

Reset Oracle SYS password

I have list my SYS password in Oracle and I need to recover the SYS password, or at least restore the password.  How can I recover my lost SYS password?

sqlplus /nolog
connect / as sysdba
ALTER USER sys IDENTIFIED BY “new_password”;

demo :

[oracle@mezon01 ~]$ . profile_db_PDEV
[oracle@mezon01 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 1 17:19:52 2018

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

SQL> connect / as sysdba
Connected.
SQL> ALTER USER sys IDENTIFIED BY System_2018;

User altered.

SQL>
SQL>
SQL>
SQL> exit

Standard