Oracle SQL Tips

1. show datafilename :
SQL> col NAME for a60
SQL> set lines 200
SQL> select file#, name, status, enabled from v$datafile;

2. show logfile and status logfile :
SQL> col MEMBER for a60
SQL> set lines 200
SQL> select * from v$logfile;

GROUP# STATUS MEMBER
---------- ------- ------------------------------------------------------------
1 /oravl02/ORACLE/XLTST8I/log/logXLTST8I_A1.dbf
1 /oravl02/ORACLE/XLTST8I/log/logXLTST8I_A2.dbf
2 /oravl03/ORACLE/XLTST8I/log/logXLTST8I_B1.dbf
2 /oravl03/ORACLE/XLTST8I/log/logXLTST8I_B2.dbf
3 /oravl04/ORACLE/XLTST8I/log/logXLTST8I_C1.dbf
3 /oravl04/ORACLE/XLTST8I/log/logXLTST8I_C2.dbf

6 rows selected.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 11630 10485760 2 YES INACTIVE 7648163507 17-FEB-05
2 1 11632 10485760 2 NO CURRENT 7648179695 17-FEB-05
3 1 11631 10485760 2 YES ACTIVE 7648165902 17-FEB-05

3. show controlfile :
SQL> select * from v$controlfile;

STATUS NAME
------- ------------------------------------------------------------
/oravl02/ORACLE/XLTST8I/ctl/cntrlXLTST8I_1.dbf
/oravl03/ORACLE/XLTST8I/ctl/cntrlXLTST8I_2.dbf


4. show sga :
SQL> select * from v$sga;

NAME VALUE
------------------------------------------------------------ ----------
Fixed Size 73888 <=== in bytes
Variable Size 193605632 <=== in bytes
Database Buffers 286720000 <=== in bytes
Redo Buffers 2113536 <=== in bytes

show hotbackup status :
SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 7105462196 18-FEB-05
2 NOT ACTIVE 7105462195 18-FEB-05
3 NOT ACTIVE 7105462197 18-FEB-05
4 NOT ACTIVE 7105462187 18-FEB-05
5 NOT ACTIVE 7105462189 18-FEB-05
6 NOT ACTIVE 7105462188 18-FEB-05
7 NOT ACTIVE 7105462186 18-FEB-05
8 NOT ACTIVE 7105462190 18-FEB-05
9 NOT ACTIVE 7105462191 18-FEB-05
10 NOT ACTIVE 7105462194 18-FEB-05
11 NOT ACTIVE 7105462192 18-FEB-05

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
12 NOT ACTIVE 7105462193 18-FEB-05

12 rows selected.


5. sample put in hotbackup mode scripting (shell tcsh) :
su - oracle <<*EOF*
source ~oracle/.env_ora_setup ${SID}
/oravl01/oracle/8.1.7.3/bin/sqlplus -s "/ as sysdba" <set pagesize 0;
set feed off;
spool /backup/script/begin_backup_${SID}.sql
select distinct 'alter tablespace ' || tablespace_name || ' begin backup ;' from dba_data_files;
spool off
@/backup/script/begin_backup_${SID}.sql
exit
TABSQL
exit
*EOF*

6. sample out of hotbackup mode scripting (shell tcsh) :

su - oracle <<*EOF*
source ~oracle/.env_ora_setup ${SID}
/oravl01/oracle/8.1.7.3/bin/sqlplus -s "/ as sysdba" <set pagesize 0;
set feed off;
spool /backup/script/end_backup_${SID}.sql
select distinct 'alter tablespace ' || tablespace_name || ' end backup;' from dba_data_files;
spool off
@/backup/script/end_backup_${SID}.sql
exit
TABSQL
exit
*EOF*


7. check whether has archive log or not ( login / as sysdba ) :
XLTST9I /oravl01/oracle > sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.5.0 - Production on Fri Feb 18 13:30:00 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Enabled
Archive destination /oravl01/oracle/adm/XLTST9I/arc/
Oldest online log sequence 265
Current log sequence 266
SQL>


8. Check who take CPU load more in oracle :
eisprod:DWHSUN:/oracle $ prstat -a
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
535 oradw 6366M 5987M cpu6 30 0 0:21.49 10% sortcl-8.1.3/14
4090 oradw 1320K 944K cpu7 14 2 0:03.06 9.9% gzip/1
28348 oradw 2022M 2002M cpu15 50 0 0:41.17 9.9% oracle/1
eisprod:DWHSUN:/oracle $ sqlplus "/ as sysdba"

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Apr 27 07:48:03 2005

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> select sid, prev_hash_value, sql_hash_value from v$session where paddr = (select addr from v$process where spid = 4789);

no rows selected

SQL> select sid, prev_hash_value, sql_hash_value from v$session where paddr = (select addr from v$process where spid = 28348);

SID PREV_HASH_VALUE SQL_HASH_VALUE
---------- --------------- --------------
7 803032596 803032596

SQL> select sid, machine, process, osuser, to_char(logon_time, 'DD-MON-YY HH24:MI:SS'), program from v$session where sid = 7;

SID MACHINE
---------- ----------------------------------------------------------------
PROCESS OSUSER TO_CHAR(LOGON_TIME
--------- ------------------------------ ------------------
PROGRAM
------------------------------------------------
7 WORKGROUP\EISPROD
5536:3336 SYSTEM 27-APR-05 06:59:34

Comments

Post a Comment

Popular posts from this blog

OSS RC : learning by doing (new beginning)

CIF Self Management: #/opt/ericsson/nms_cif_sm/bin/smtool [option] [MC] Option [stop|start|online|offline|coldrestart|warmrestart|cancel|config|help|exit] i.e : #./smtool online Activity Manager, #./smtool –list Log : Log [-print] [-type error|event|command|nwstat|security] [-number number] [-host host] [-filter filterfile[:number]] i.e #/opt/ericsson/nms_cif_sm/bin/log –type error –number 10 |more #./log –filter myfilterfile.txt:2 (means: second row of myfilterfile.txt as filter criteria) See errors as they come in, use: #/opt/ericsson/nms_cif_sm/bin/newLogRecordsViewer.sh Openfusion #cd /opt/inprise/openfusion/bin/ Environment file: # ls –la .javaenv Check version: #./version Start manager: #./manager Log is on duty: administrative state is unlocked Log will never become full : wrap Log full: halt (until persistent storage is exhausted) Err&system log: #/opt/inprise/openfusion/domains/OpenFusion/loca...
Read more

Link sudoers in linux (centos7) with Active Directory group

Image
Assignment: create Active Directory group for linux administrator who need a root privilege (sudo su) with no password required. 1. Create linuxadm group at Active Directory server with group type: security. 2. Add Active Directory user account into linuxadm group (e.g bruce wayne). 3. Add Active Directory group into sudoers profile (/etc/sudoers) using visudo on linux server. [root@labnfs ~]# visudo %BATMAN.LOCAL\\linuxadm         ALL=(ALL)       NOPASSWD: ALL 4.  check UID  before/after run "sudo su" on Linux server using Active Directory user account to confirm it. aghiel@aghiel-mbproi9 ~ % ssh bruce@labnfs bruce@labnfs's password:  [bruce@labnfs ~]$  [bruce@labnfs ~]$ id uid=215401106(bruce) gid=215400513(domain users) groups=215400513(domain users),215401120(linuxadm) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023 [bruce@labnfs ~]$ sudo su [root@labnfs bruce]# id uid=0(root) gid=0(root) groups=0(root) contex...
Read more

RSYNC via SSH on solaris 10

How to use rsync via ssh on solaris 10: 1. install rsync package on both (server & client) and follow the instruction : # pkgadd -d rsync-2.6.9-sol10-sparc-local (sparc) #pkgadd -d rsync-3.0.7-sol10-x86-local (x86) don't forget to install libcap also if it doesn't exist on servers (#pkginfo |grep -i libcap) : #pkgadd -d libpcap-1.1.1-sol10-sparc-local (sparc) #pkgadd -d libpcap-1.1.1-sol10-x86-local (x86) Voila! you have rsync package installed!: -bash-3.00$ which rsync /usr/local/bin/rsync -bash-3.00$ which ssh/usr/bin/ssh 2. to check whether it is worked using ssh or not : #rsync -avn -e ssh --rsync-path=/usr/local/bin/rsync remoteuser@remotehost:/dirtobecopied/ /localdir/destination 3. to try synchronize the data with option Z (compressed using gzip) : #rsync -avz -e ssh --rsync-path=/usr/local/bin/rsync remoteuser@remotehost:/dirtobecopied/ /localdir/destination 4. to limit transfer speed/bandwidth (e.g 1MBps): #rsync -avz --bwlimit=1024 --rsync-path=/usr/local/bin/rs...
Read more