Oracle DBA - Basic Commands

ADRCI

·       command line interface for diagnostic utility

·       viewing diagnostics data like listener log , alert log ,incident and cor dump etc

·       creating incident packages

Sl.No.

Command

Description

1.

adrci> show base | set base /u01/app/grid

Get/set current base location

2.

adrci> show home | adrci> set homepath /u02/app/oracle

List/Set ORACLE_HOME

3.

adrci> show alert
adrci> show alert -tail 100

View alert log

4.

adrci> purge -age 600 -type ALERT

adrci> purge -age 600 -type TRACE

adrci> purge -age 600 -type incident

adrci> purge -age 10080 -type cdump

Purge alerts and trace files

5.

adrci> show control — Get existing control policy

adrci> set control (SHORTP_POLICY = 240) 

adrci> set control (LONGP_POLICY = 600)   --in hrs

Set control policy for auto purge of files

LONGP_POLICY: to purge below data . Default value is 365 days.

• ALERT, INCIDENT, SWEEP, STAGE, HM

SHORTP_POLICY: to purge for below data Default value is 30 days.

• TRACE, CDUMP, UTSCDMP, IPS

6.

adrci> show incident

adrci>IPS CREATE PACKAGE INCIDENT

adrci> ips pack incident in /tmp

 

adrci>IPS CREATE PACKAGE -- Create empty package

adrci>IPS ADD INCIDENT incident_number PACKAGE 2

adrci>IPS ADD FILE /u01/app/oracle/alert_db.log PACKAGE 2

adrci>IPS GENERATE PACKAGE 2 IN /home/dbaclass/housekeeping

Create incident package

7.

adrci> ips unpack file ORA_98928.zip into /tmp/housekeeping

Unpack a ips file

8.

ips pack time '2019-05-01 12:00:00.00' to '2019-05-02 23:00:00.00'

Pack all incident files within a particular time frame

9.

9.

adrci> ips show package

adrci> ips show package 12 detail

View package information

10.

adrci> ips delete package 2

adrci > ips remove incident 2 package 7

adrci > ips remove problem 4 package 8

Delete the complete package:

Remove incidents from the packages

Remove the problem keys from packages

 

TFACTL

Sl.No.

Command

Description

1.

tfactl status

tfactl toolstatus

tfactl print config

tfactl access lsusers

tfactl access promote -user oracle

tfactl access add/remove -user rpdtro

tfactl set port=5001

tfactl stop/start/enable/disable/version

Check tfactl status with version:

Check tfactl tool status:

Get config details:

List of user having access to tfactl:

changing property of a user:

Adding or removing users from access list of tfactl:

change port number for tfactl

2.

tfactl diagcollect -all

tfactl get notificationAddress

tfactl set notificationAddress=oracle:admin@dbaclass.com

 

Collect diagnostic report pass the time of incident in YYYY-MM-DD HH24:MI:SS)

Get notificationaddress email:

Change notification address email:

3.

tfactl summary

tfactl summary -html

tfactl summary -patch -html

tfactl summary -asm -html

Generate summary report :

Genearate complete summary overview in html

Generate patching summary:

Generate asm summary

4.

tfactl print smtp

view smtp details

5.

tfactl managelogs -show usage

tfactl managelogs -purge -older 5d -dryrun

tfactl managelogs -purge -older 5d

tfactl managelogs -purge -gi 5d

tfactl run managelogs -purge -older 5d -gi

tfactl run managelogs -purge -older -5d -database

Manage logs using tfactl managelogs

This is just a dry run:

This will actually delete the logs older than 5 days

Delete only GI logs:

Delete only database logs:

6.

tfactl print repository

tfactl print components

tfactl print collections

tfactl sendmail support@dbaclass.com

Get repository location and usage:

Get component details:

Find diag collection details of tfactl:

Verify email/smtp configuration.

 

 

ASMCMD

Sl.No.

Command

Description

1.

lsdg

lsdg –discovery

lsdg -g --discovery

List all diskgroups

Include dismounted diskgroups

List diskgroups across all nodes of cluster

2.

lsdsk -k

lsdsk -k -G CDATA

lsdsk -p -G CDATA

lsdsk -t -G CDATA

lsdsk --candidate -k

lsdsk --candidate -p

List all asm disks
List disks of a diskgroup(CDATA) with free and total MB

List disks of a diskgroup(CDATA) with group and disk number

List disks with disk creation date

List candidiate disks only

List member disks only

3.

lsattr -lm

lsattr -lm -G DMARCH

lsattr -lm %au_size%

List attribute of all diskgroups:

List attribute of specific diskgroup(DMARCH)

List attributes with specific pattern

4.

umount -a

umount ARCH

unmount all diskgroups

unmount specific diskgroup(ARCH)

5.

mount -a

mount ARCH

mount all diskgroups on local node

mount a specific diskgroup on local node 

6.

rebal --power 8 ARCH

lsop

Rebalance a diskgroup - asm_power_limit is 8 and diskgroup is ARCH

Monitor progress

7.

ASMCMD> pwget --dbuniquename DBACLASS

ASMCMD> pwget –asm

Spget

Get password file of database

 

Get password file of asm

Get asm spfile location

8.

Showclustermode

Showclusterstate

showversion

Check whether flex asm is enabled or not

Check cluster state

View asm version

9.

ASMCMD> spbackup +<path_name_spfile> /home/oracle/asmspfile.ora

copy backup of spfile to a specific location

10.

lsct DMARCH

Find clients connected to a diskgroup

11.

Dsget

Lspwusr

Get asm diskstring

List asm users with password

12.

lsof -G ARCH

lsof --dbname DBACLASS

Open files of a diskgroup ( ARCH)

Open files of a database( DBACLASS)

13.

afd_state

afd_lsdsk

afd_dsget

Check filter driver is enabled or not

List filter driver disks

Get filter driver asm diskstring

 

CRSCTL

Sl.No.

Command

Description

1.

crsctl stop/start crs

crsctl enable/disable crs

cemutlo -n or
olsnodes -c

crsctl query crs softwareversion <hostname> or
crsctl query crs softwareversion host-dbaclass1

crsctl query crs activeversion

crsctl stop/start/enable/disable has

crsctl check/config has

crsctl query has releaseversion/ softwareversion

Start/stop

Enable/disable

Cluster name

 

Find grid version

 

Check active version of cluster

2.

crsctl stat res -t -init

crsctl check crs

crsctl check cssd

crsctl check crsd

crsctl check evmd

crsctl check cluster -all

crsctl get cluster mode status

crsctl config crs

crsctl get cluster configuration

crsctl get node role status -all

check cluster component status

Check CRS status of local node

 

 

 

 

Check whether standard or flex ASM

Check CRS configuration

Find cluster configuration information

Find node roles in cluster

 

3.

crsctl query css votedisk

ocrcheck

crsctl get css disktimeout

crsctl get css miscount

crsctl replace votedisk +OCRVD

crsctl add/delete css votedisk

ocrconfig -showbackup

 

Find voting disk location

Find OCR location

Disk Network latency in the node interconnect (Misscount)

timeout from node to voting disk(disktimeout)

Move voting disk to another diskgroup

Add/delete voting disk

Get ocr disk backup details

 

4.

oifcfg getif

Find cluster interconnect details

 

 

SRVCTL

Sl. No.

Command

Description

1.

 srvctl stop database -d db_name [-o stop_options]

srvctl start database -d db_name [-o start_options]

srvctl stop instance -d db_unique_name [-i “instance_name_list”]} [-o stop_options] [-f]

srvctl start instance -d db_unique_name [-i “instance_name_list”} [-o start_options]

stop_options: normal/immediate(default)/transactional/abort

start_option: nomount/mount/open(default)

2.

srvctl remove database -d db_unique_name [-f] [-y] [-v]

srvctl add database -d db_unique_name -o ORACLE_HOME [-p spfile]

srvctl remove instance -d DB_UNIQUE_NAME -i INSTANCE_NAME

srvctl add instance –d db_unique_name –i inst_name -n node_name

Add/remove DB/Instance

3.

srvctl enable/disable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME

srvctl enable/disable database -d DB_UNIQUE_NAME

Enable/disable DB/Instance

4.

srvctl add servicec -d {DB_NAME} -s {SERVICE_NAME} -r {“preferred_list”} -a {“available_list”} [-P {BASIC | NONE | PRECONNECT}]

SYNTAX – srvctl remove service -d {DB_NAME} -s {SERVICE_NAME}

srvctl start/stop/enable/disable servicec -d {DB_NAME} -s {SERVICE_NAME}

Add/remove services

5.

srvctl relocate service -d {database_name} -s {service_name} -i {old_inst_name} -r {new_inst_name}

srvctl config service -d {database_name} -s {service_name}

Relocate a service

6.

srvctl config scan_listener

srvctl modify scan_listener -p {new-SCAN-port}

$GRID_HOME/bin/srvctl stop scan_listener

$GRID_HOME/bin/srvctl start scan_listener

Alter system set remote_listener='orcl-scan.stc.com.sa:1523' scope=both sid='*';

 

Modify Scan_listener port  

7.

SRVM_TRACE=true

export SRVM_TRACE

srvctl setenv database -db ORCL -env

"TNS_ADMIN=/oracle/app/oracle/product/12.1.0.2/dbhome_1/network/admin"

srvctl getenv database -db ORCL

Enable trace for srvctl commands

 

 

8.

srvctl stop/start home -oraclehome /oracle/product/12.1.0.2/dbhome_1 -statefile /home/oracle/state.txt -node dbhost-1

Stop and start services running from ORACLE_HOME

9.

srvctl add service -db ORCLDB -service TAF_ORCL -preferred ORCLDB1 -available

ORCLDB2 -tafpolicy BASIC -failovertype SELECT

srvctl start service -db OMPREDB -service TAF_ORCL

Create a TAF policy

 

Comments

Post a Comment

Popular posts from this blog

 Exadata  Oracle Exadata Smart Flash Cache * Exadata cell -> Flash LUN -> Cell Disk -> Flash Cache/Flash Log  * CELL_FLASH_CACHE - – DEFAULT (LRU), KEEP, NONE * ALTER TABLE <TABLE_NAME> STORAGE(FLASH_CACHE [NONE|DEFAULT|KEEP]) * ALTER INDEX <INDEX NAME> STORAGE(FLASH_CACHE [NONE|DEFAULT|KEEP]) Smart Flash Cache Mode: 1. Write Through Flash Cache Mode (Default):  Write on Disk -> Copy to Flash Cache  * excellent for absorbing repeated random reads  * commonly found in OLTP applications. * dcli -g cell_servers -l celladmin cellcli -e "list cell attributes flashcachemode" * Steps to enable Write-Through Mode CellCLI> ALTER FLASHCACHE ALL FLUSH CellCLI> DROP FLASHCACHE CellCLI> ALTER CELL SHUTDOWN SERVICES CELLSRV CellCLI> ALTER CELL flashCacheMode=WriteThrough CellCLI> ALTER CELL STARTUP SERVICES CELLSRV CellCLI> CREATE FLASHCACHE ALL 2. Write Back Flash Cache Mode:  Write on Flash -> Copy to Disk  * best ...
Read more

Database Network : Configuration Parameters

LSNRCTL> command [listener_name] lsnrctl @file_name  #to execute in batch mode. REM or # to identify comments in the batch script Listener.ora file parameters: ·          PASSWORDS_listener_name  #an unencrypted password ·          LSNRCTL> change_password #to establish an encrypted password ·          LSNRCTL> SAVE_CONFIG  #Any changes are stored in listener.ora ·          LSNRCTL> SET PASSWORD  #If a password is set, then issue SET PASSWORD command before changing the password. Use the command SET PASSWORD prior to privileged Listener Control utility commands ·          Reload : the database services, instances, service handlers, and listening endpoints that were dynamically registered with the listener will be unregistered and subseque...
Read more

Undo Segments : Drop Problematic Undo Segments

ERROR about to recover undo segment 98 status:6 inst:0 mark undo segment 98 as available status:6 ret:1  about to recover undo segment 46 status:6 inst:0 mark undo segment 46 as available status:6 ret:1   SQL> select * from V$FAST_START_TRANSACTIONS; USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS CON_ID ---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------- ---------- ---------- 46 46 2313064 RECOVERING 505 24992423 77 5586 0 0 0 10001000684B2300 0000000000000000 1 0 98 25 1352150 RECOVERING 0 226231 78 5586 0 0 0 30001900D6A11400 0000000000000000 1 0   SQL> SELECT segment_name, tablespace_name  FROM dba_rollback_segs  WHERE segment_id IN (98, 46); SEGMENT_NAME               TABLESPACE_NAME ------------------------------ ----------------------------...
Read more