Shell Script to generate Hourly AWR Report

This summary is not available. Please click here to view the post.

Making a Table Readonly

In this example, we’ll create a table, insert data in it, and then create trigger to prevent any insert, update or delete statement on table

SQL> CREATE TABLE tbl_read_only (id NUMBER);

Table created.

SQL> INSERT INTO tbl_read_only VALUES(1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM tbl_read_only;

        ID
———-
         1

SQL> CREATE TRIGGER trg_tbl_read_only BEFORE DELETE OR INSERT OR UPDATE
  2  ON tbl_read_only
  3  BEGIN
  4  RAISE_APPLICATION_ERROR(-20202,’Table Status: READ ONLY!!!’);
  5  END;
  6  /

Trigger created.

SQL> INSERT INTO tbl_read_only VALUES(2);
INSERT INTO tbl_read_only VALUES(2)
            *
ERROR at line 1:
ORA-20202: Table Status: READ ONLY!!!ORA-06512: at “KAMRAN.TRG_TBL_READ_ONLY”, line 2
ORA-04088: error during execution of trigger ‘KAMRAN.TRG_TBL_READ_ONLY’
SQL>

How to connect to different user in Oracle

How to connect to different user in Oracle

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

Step 1:

SQL> connect / as sysdba

Connected.

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

PASSWORD

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

F894844C34402B67

SQL> alter user scott identified by abc123;

User altered.

SQL> connect scott/abc123

Connected.

SQL> create table t...

SQL> connect / as sysdba

Connected.

SQL> alter user scott identified by values 'F894844C34402B67';

User altered.

Step 2:

SQL> alter session set current_schema=scott;

Session altered.

Step 3:

SQL> create user su identified by secret123;

User created.

SQL> alter user scott grant connect through su;

User altered.

SQL> connect su[scott]/secret123;

Connected.

Oracle RAC Commands

Oracle RAC Commands

CLSCFG: -- Oracle cluster configuration tool, in Oracle Real Application Cluster(RAC)
clscfg -help or clscfg -h
clscfg -install -- creates a new configuration
clscfg -add     -- adds a node to the configuration
clscfg -delete -- deletes a node from the configuration
clscfg -upgrade     -- upgrades an existing configuration
clscfg -downgrade -- downgrades an existing configuration
clscfg -local       -- creates a special single-node configuration for ASM
clscfg -concepts -- brief listing of terminology used in the other modes
clscfg -trace       -- used in conjunction with any mode above for tracing

CLUVFY: Cluster Verification Utility
cluvfy [-help] or cluvfy -h
cluvfy stage {-pre|-post} stage_name stage_specific_options [-verbose]
Valid stage options and stage names are:
        -post hwos    :  post-check for hardware and operating system
        -pre  cfs       :  pre-check for CFS setup
        -post cfs       :  post-check for CFS setup
        -pre  crsinst  :  pre-check for CRS installation
        -post crsinst  :  post-check for CRS installation
        -pre  hacfg   :  pre-check for HA configuration
        -post hacfg   :  post-check for HA configuration
        -pre  dbinst   :  pre-check for database installation
        -pre  acfscfg  :  pre-check for ACFS Configuration.
        -post acfscfg  :  post-check for ACFS Configuration.
        -pre  dbcfg   :  pre-check for database configuration
        -pre  nodeadd :  pre-check for node addition.
        -post nodeadd :  post-check for node addition.
        -post nodedel  :  post-check for node deletion.
cluvfy stage -post hwos -n node_list [-verbose]
./runcluvfy.sh stage -post hwos -n node1,node2 -verbose
-- Installation checks after hwos - Hardware and Operating system installation
cluvfy stage -pre cfs -n node_list [-verbose]
cluvfy stage -post cfs -n node_list [-verbose]
-- Installation checks before/after Cluster File System
cluvfy stage -pre crsinst -n node_list [-c ocr_location] [-r {10gR1|10gR2|11gR1|11gR2}] [-q voting_disk] [-osdba osdba_group] [-orainv orainventory_group] [-verbose]
cluvfy stage -pre crsinst -n node1,node2,node3
./runcluvfy.sh stage -pre crsinst -n all -verbose
cluvfy stage -post crsinst -n node_list [-verbose]
-- Installation checks before/after CRS installation
cluvfy stage -pre dbinst -n node_list [-r {10gR1|10gR2|11gR1|11gR2}] [-osdba osdba_group] [-orainv orainventory_group] [-verbose]
cluvfy stage -pre dbcfg -n node_list -d oracle_home [-verbose]
-- Installation checks before/after DB installation/configuration

cluvfy comp component_name component_specific_options [-verbose]
Valid components are:
        nodereach : checks reachability between nodes
        nodecon    : checks node connectivity
        cfs         : checks CFS integrity
        ssa        : checks shared storage accessibility
        space     : checks space availability
        sys        : checks minimum system requirements
        clu         : checks cluster integrity
        clumgr   : checks cluster manager integrity
        ocr        : checks OCR integrity
        olr        : checks OLR integrity
        ha        : checks HA integrity
        crs        : checks CRS integrity
        nodeapp   : checks node applications existence
        admprv    : checks administrative privileges
        peer        : compares properties with peers
        software  : checks software distribution
        asm        : checks ASM integrity
        acfs        : checks ACFS integrity
        gpnp       : checks GPnP integrity
        gns         : checks GNS integrity
        scan        : checks SCAN configuration
        ohasd      : checks OHASD integrity
        clocksync  : checks Clock Synchronization
        vdisk        : check Voting Disk Udev settings
cluvfy comp nodereach -n node_list [-srcnode node] [-verbose]
cluvfy comp nodecon -n node_list [-i interface_list] [-verbose]
cluvfy comp nodecon -n node1,node2,node3 –i eth0 -verbose
cluvfy comp nodeapp [-n node_list] [-verbose]
cluvfy comp peer [-refnode node] -n node_list [-r {10gR1|10gR2|11gR1|11gR2}] [-orainv orainventory_group] [-osdba osdba_group] [-verbose]
cluvfy comp peer -n node1,node2 -r 10gR2 -verbose
cluvfy comp crs [-n node_list] [-verbose]
cluvfy comp cfs [-n node_list] -f file_system [-verbose]
cluvfy comp cfs -f /oradbshare –n all -verbose
cluvfy comp ocr [-n node_list] [-verbose]
cluvfy comp clu -n node_list -verbose
cluvfy comp clumgr [-n node_list] [-verbose]
cluvfy comp sys [-n node_list] -p {crs|database} [-r {10gR1|10gR2|11gR1|11gR2}] [-osdba osdba_group] [-orainv orainventory_group] [-verbose]
cluvfy comp sys -n node1,node2 -p crs -verbose
cluvfy comp admprv [-n node_list] [-verbose] |-o user_equiv [-sshonly] |-o crs_inst [-orainv orainventory_group] |-o db_inst [-orainv orainventory_group] [-osdba osdba_group] |-o db_config -d oracle_home
cluvfy comp ssa [-n node_list] [-s storageID_list] [-verbose]
cluvfy comp space [-n node_list] -l storage_location -z disk_space{B|K|M|G} [-verbose]
cluvfy comp space -n all -l /home/dbadmin/products –z 2G -verbose
cluvfy comp olr

CRSCTL - Cluster Ready Service Control
$crsctl -- to get help
$crsctl query crs activeversion
$crsctl query crs softwareversion [node_name]
#crsctl start crs
#crsctl stop crs
(or)
#/etc/init.d/init.crs start
#/etc/init.d/init.crs stop

#crsctl enable crs
#crsctl disable crs
(or)
#/etc/init.d/init.crs enable
#/etc/init.d/init.crs disable

$crsctl check crs
$crsctl check cluster [-node node_name] -- Oracle RAC 11g command, checks the viability of CSS across nodes
#crsctl start cluster -n HostName -- 11g R2
#crsctl stop cluster -n HostName -- 11g R2
#crsctl stop cluster -all  -- 11g R2

$crsctl check cssd
$crsctl check crsd
$crsctl check evmd
$crsctl check oprocd
$crsctl check ctss

#/etc/init.d/init.cssd stop
#/etc/init.d/init.cssd start

#/etc/rc.d/init.d/init.evmd
#/etc/rc.d/init.d/init.cssd
#/etc/rc.d/init.d/init.crsd

#mv /etc/rc3.d/S96init.cssd /etc/rc3.d/_S96init.cssd -- to stop cssd from autostarting after reboot

#crsctl check css votedisk
#crsctl query css votedisk -- lists the voting disks used by CSS

#crsctl add css votedisk PATH
#crsctl add css votedisk PATH -force -- if Clusterware is not running
#crsctl delete css votedisk PATH
#crsctl delete css votedisk PATH -force -- if Clusterware is not running

#crsctl set css parameter_name value -- set parameters on OCR
#crsctl set css misscount 100
#crsctl unset css parameter_name -- sets CSS parameter to its default
#crsctl unset css misscount
#crsctl get css parameter_name -- gets the value of a CSS parameter
#crsctl get css disktimeout
#crsctl get css misscount
#crsctl get css reboottime

#crsctl start resources -- starts Clusterware resources
./crsctl start resource ora.DATA.dg
#crsctl stop resources -- stops Clusterware resources

$crsctl status resource
$crsctl status resource -t
$crsctl stat resource -t

$crsctl lsmodules crs -- lists CRS modules that can be used for debugging
CRSUI
CRSCOMM
CRSRTI
CRSMAIN
CRSPLACE
CRSAPP
CRSRES
CRSCOMM
CRSOCR
CRSTIMER
CRSEVT
CRSD
CLUCLS
CSSCLNT
COMMCRS
COMMNS

$crsctl lsmodules css -- lists CSS modules that can be used for debugging
CSSD
COMMCRS
COMMNS

$crsctl lsmodules evm -- lists EVM modules that can be used for debugging
EVMD
EVMDMAIN
EVMCOMM
EVMEVT
EVMAPP
EVMAGENT
CRSOCR
CLUCLS
CSSCLNT
COMMCRS
COMMNS
$crsctl start has   (HAS - High Availability Services)
$crsctl stop has
$crsctl check has

OCR Modules -- cannot be listed with crsctl lsmodules command
OCRAPI
OCRCLI
OCRSRV
OCRMAS
OCRMSG
OCRCAC
OCRRAW
OCRUTL
OCROSD

#crsctl debug statedump crs -- dumps state info for crs objects
#crsctl debug statedump css -- dumps state info for css objects
#crsctl debug statedump evm -- dumps state info for evm objects

#crsctl debug log crs [module:level]{,module:level} ...
-- Turns on debugging for CRS
#crsctl debug log crs CRSEVT:5,CRSAPP:5,CRSTIMER:5,CRSRES:5,CRSRTI:1,CRSCOMM:2
#crsctl debug log css [module:level]{,module:level} ...
-- Turns on debugging for CSS
#crsctl debug log css CSSD:1
#crsctl debug log evm [module:level]{,module:level} ...
-- Turns on debugging for EVM
#crsctl debug log evm EVMCOMM:1

#crsctl debug trace crs -- dumps CRS in-memory tracing cache
#crsctl debug trace css -- dumps CSS in-memory tracing cache
#crsctl debug trace evm -- dumps EVM in-memory tracing cache

#crsctl debug log res resource_name:level -- turns on debugging for resources
#crsctl debug log res "ora.lnx04.vip:1"

#crsctl trace all_the_above_commands -- tracing by adding a "trace" argument.
#crsctl trace check css

#crsctl backup -h
#crsctl backup css votedisk

Here is the list of the options for CRSCTL in 11gR2:
       crsctl add       - add a resource, type or other entity
       crsctl backup    - back up voting disk for CSS
       crsctl check     - check a service, resource or other entity
       crsctl config    - output autostart configuration
       crsctl debug     - obtain or modify debug state
       crsctl delete    - delete a resource, type or other entity
       crsctl disable   - disable autostart
       crsctl discover  - discover DHCP server
       crsctl enable    - enable autostart
       crsctl get       - get an entity value
       crsctl getperm   - get entity permissions
       crsctl lsmodules - list debug modules
       crsctl modify    - modify a resource, type or other entity
       crsctl query     - query service state
       crsctl pin       - Pin the nodes in the nodelist
       crsctl relocate  - relocate a resource, server or other entity
       crsctl replace   - replaces the location of voting files
       crsctl release   - release a DHCP lease
       crsctl request   - request a DHCP lease
       crsctl setperm   - set entity permissions
       crsctl set       - set an entity value
       crsctl start     - start a resource, server or other entity
       crsctl status    - get status of a resource or other entity
       crsctl stop      - stop a resource, server or other entity
       crsctl unpin     - unpin the nodes in the nodelist
       crsctl unset     - unset a entity value, restoring its default

crsctl add resource resource_name -type resource_type [-file file_path | -attr "attribute_name=attribute_value,attribute_name=attribute_value,..."] [-i] [-f]
crsctl add resource r1 -type test_type1 -attr "PATH_NAME=/tmp/r1.txt"
crsctl add resource app.appvip -type app.appvip.type -attr "RESTART_ATTEMPTS=2, START_TIMEOUT=100,STOP_TIMEOUT=100,CHECK_INTERVAL=10,USR_ORA_VIP=172.16.0.0, START_DEPENDENCIES=hard(ora.net1.network)pullup(ora.net1.network), STOP_DEPENDENCIES=hard(ora.net1.network)"
crsctl add type type_name -basetype base_type_name {-attr "ATTRIBUTE=attribute_name | -file file_path,TYPE={string | int} [,DEFAULT_VALUE=default_value][,FLAGS=[READONLY][|REQUIRED]]"}
crsctl add type test_type1 -basetype cluster_resource -attr "ATTRIBUTE=FOO,TYPE=integer,DEFAULT_VALUE=0"
crsctl add crs administrator -u user_name [-f]
crsctl add crs administrator -u scott
crsctl add css votedisk path_to_voting_disk [path_to_voting_disk ...] [-purge]
crsctl add css votedisk /stor/grid/ -purge
crsctl add serverpool server_pool_name {-file file_path | -attr "attr_name=attr_value[,attr_name=attr_value[,...]]"} [-i] [-f]
crsctl add serverpool testsp -attr "MAX_SIZE=5"
crsctl add serverpool sp1 -file /tmp/sp1_attr
crsctl check cluster [-all | [-n server_name [...]]
crsctl check cluster -all
crsctl check crs
crsctl check css
crsctl check ctss    -- Cluster Time Synchronization services
crsctl check evm
crsctl check resource {resource_name [...] | -w "filter" } [-n node_name] [-k cardinality_id] [-d degree_id] }
crsctl check resource appsvip
crsctl config crs
crsctl delete crs administrator -u user_name [-f]
crsctl delete crs administrator -u scott
crsctl delete resource resource_name [-i] [-f]
crsctl delete resource myResource
crsctl delete type type_name [-i]
crsctl delete type app.appvip.type
crsctl delete css votedisk voting_disk_GUID [voting_disk_GUID [...]]
crsctl delete css votedisk 61f4273ca8b34fd0bfadc2531605581d
crsctl delete node -n node_name
crsctl delete node -n node06
crsctl delete serverpool server_pool_name [server_pool_name [...]] [-i]
crsctl delete serverpool sp1
crsctl disable crs
crsctl discover dhcp -clientid clientid [-port port]
crsctl discover dhcp -clientid dsmjk252clr-dtmk01-vip
crsctl enable crs
crsctl get hostname
crsctl get clientid dhcp -cluname cluster_name -viptype vip_type [-vip vip_res_name] [-n node_name]
crsctl get clientid dhcp -cluname dsmjk252clr -viptype HOSTVIP -n tmjk01
crsctl get css parameter
crsctl get css disktimeout
crsctl get css ipmiaddr
crsctl get nodename
crsctl getperm resource resource_name [ {-u user_name | -g group_name} ]
crsctl getperm resource app.appvip
crsctl getperm resource app.appvip -u oracle
crsctl getperm resource app.appvip -g dba
crsctl getperm type resource_type [-u user_name] | [-g group_name]
crsctl getperm type app.appvip.type
crsctl getperm serverpool server_pool_name [-u user_name | -g group_name]
crsctl getperm serverpool sp1
crsctl lsmodules {mdns | gpnp | css | crf | crs | ctss | evm | gipc}
crsctl lsmodules evm
 mdns: Multicast domain name server
 gpnp: Grid Plug and Play service
 css: Cluster Synchronization Services
 crf: Cluster Health Monitor
 crs: Cluster Ready Services
 ctss: Cluster Time Synchronization Service
 evm: Event Manager
 gipc: Grid Interprocess Communication
crsctl modify resource resource_name -attr "attribute_name=attribute_value" [-i] [-f] [-delete]
crsctl modify resource appsvip -attr USR_ORA_VIP=10.1.220.17 -i
crsctl modify type type_name -attr "ATTRIBUTE=attribute_name,TYPE={string | int} [,DEFAULT_VALUE=default_value [,FLAGS=[READONLY][| REQUIRED]]" [-i] [-f]]
crsctl modify type myType.type -attr "ATTRIBUTE=FOO,DEFAULT_VALUE=0 ATTRIBUTE=BAR,DEFAULT_VALUE=baz"
crsctl modify serverpool server_pool_name -attr "attr_name=attr_value [,attr_name=attr_value[, ...]]" [-i] [-f]
crsctl modify serverpool sp1 -attr "MAX_SIZE=7"
crsctl pin css -n node_name [ node_name [..]]
crsctl pin css -n node2
crsctl query crs administrator
crsctl query crs activeversion
crsctl query crs releaseversion
crsctl query crs softwareversion node_name
crsctl query css ipmiconfig
crsctl query css ipmidevice
crsctl query css votedisk
crsctl query dns {-servers | -name name [-dnsserver DNS_server_address] [-port port] [-attempts number_of_attempts] [-timeout timeout_in_seconds] [-v]}
crsctl release dhcp -clientid clientid [-port port]
crsctl release dhcp -clientid spmjk662clr-spmjk03-vip
crsctl relocate resource {resource_name | resource_name | -all -s source_server | -w "filter"} [-n destination_server] [-k cid] [-env "env1=val1,env2=val2,..."] [-i] [-f]
crsctl relocate resource myResource1 -s node1 -n node3
crsctl relocate server server_name [...] -c server_pool_name [-i] [-f]
crsctl relocate server node6 node7 -c sp1
crsctl replace discoverystring 'absolute_path[,...]'
crsctl replace discoverystring "/oracle/css1/*,/oracle/css2/*"
crsctl replace votedisk [+asm_disk_group | path_to_voting_disk [...]]
crsctl replace votedisk +diskgroup1
crsctl replace votedisk /mnt/nfs/disk1 /mnt/nfs/disk2
crsctl request dhcp -clientid clientid [-port port]
crsctl request dhcp -clientid tmj0462clr-tmjk01-vip
crsctl set css parameter value
crsctl set css ipmiaddr ip_address
crsctl set css ipmiaddr 192.0.2.244
crsctl set css ipmiadmin ipmi_administrator_name
crsctl set css ipmiadmin scott
crsctl set log {[crs | css | evm "component_name=log_level, [...]"] | [all=log_level]}
crsctl set log crs "CRSRTI=1,CRSCOMM=2"
crsctl set log evm all=2
crsctl set log res "myResource1=3"
crsctl set {log | trace} module_name "component:debugging_level [,component:debugging_level][,...]"
crsctl set log crs "CRSRTI:1,CRSCOMM:2"
crsctl set log crs "CRSRTI:1,CRSCOMM:2,OCRSRV:4"
crsctl set log evm "EVMCOMM:1"
crsctl set log res "resname:1"
crsctl set log res "resource_name=debugging_level"
crsctl set log res "ora.node1.vip:1"
crsctl set log crs "CRSRTI:1,CRSCOMM:2" -nodelist node1,node2
crsctl set trace "component_name=tracing_level,..."
crsctl set trace "css=3"
crsctl setperm resource resource_name {-u acl_string | -x acl_string | -o user_name | -g group_name}
crsctl setperm resource myResource -u user:scott:rwx
crsctl setperm type resource_type_name {-u acl_string | -x acl_string | -o user_name | -g group_name}
crsctl setperm type resType -u user:scott:rwx
crsctl setperm serverpool server_pool_name {-u acl_string | -x acl_string | -o user_name | -g group_name}
crsctl setperm serverpool sp3 -u user:scott.tiger:rwx
crsctl start cluster [-all | -n server_name [...]]
crsctl start cluster -n node1 node2
crsctl start crs
crsctl start ip -A {IP_name | IP_address}/netmask/interface_name
crsctl start ip -A 192.168.29.220/255.255.252.0/eth0
crsctl start resource {resource_name [...] | -w filter | -all} [-n server_name] [-k cid] [-d did] [-env "env1=val1,env2=val2,..."] [-i] [-f]
crsctl start resource myResource -n server1
crsctl start testdns [-address address [-port port]] [-once] [-v]
crsctl start testdns -address 192.168.29.218 -port 63 -v
crsctl status resource {resource_name [...] | -w "filter"} [-p | -v [-e]] | [-f | -l | -g] [[-k cid | -n server_name] [-d did]] | [-s -k cid [-d did]] [-t]
crsctl status resource ora.stai14.vip
crsctl stat res -w "TYPE = ora.scan_listner.type"
crsctl status type resource_type_name [...]] [-g] [-p] [-f]
crsctl status type ora.network.type
crsctl status ip -A {IP_name | IP_address}
crsctl status server [-p | -v | -f]
crsctl status server { server_name [...] | -w "filter"} [-g | -p | -v | -f]
crsctl status server node2 -f
crsctl status serverpool [-p | -v | -f]
crsctl status serverpool [server_pool_name [...]] [-w] [-g | -p | -v | -f]
crsctl status serverpool sp1 -f
crsctl status serverpool
crsctl status serverpool -p
crsctl status serverpool -w "MAX_SIZE > 1"
crsctl status testdns [-server DNS_server_address] [-port port] [-v]
crsctl stop cluster [-all | -n server_name [...]] [-f]
crsctl stop cluster -n node1
crsctl stop crs [-f]
crsctl stop crs
crsctl stop resource {resource_name [...] | -w "filter" | -all} [-n server_name] [-k cid] [-d did] [-env "env1=val1,env2=val2,..."] [-i] [-f]
crsctl stop resource -n node1 -k 2
crsctl stop ip -A {IP_name | IP_address}/interface_name
crsctl stop ip -A MyIP.domain.com/eth0
crsctl stop testdns [-address address [-port port]] [-domain GNS_domain] [-v]
crsctl unpin css -n node_name [node_name [...exit]]
crsctl unpin css -n node1 node4
crsctl unset css parameter
crsctl unset css reboottime
crsctl unset css ipmiconfig
HAS (High Availability Service)
crsctl check has
crsctl config has
crsctl disable has
crsctl enable has
crsctl query has releaseversion
crsctl query has softwareversion
crsctl start has
crsctl stop has [-f]

How do I identify the voting disk/file location?
#crsctl query css votedisk
How to take backup of voting file/disk?
crsctl backup css votedisk

dd         -- dataset definition, useful for taking backup of votedisks/Voting Disks
Arguments:
  if -- input file
  of -- output file
  bs -- block size
dd if=/u02/ocfs2/vote/VDFile_0 of=$ORACLE_BASE/bkp/VDFile_0
dd if=/dev/hdb2 of=/dev/sda5 bs=8192
dd if=/dev/sda2 of=/dev/sdb2 bs=4096 conv=notrunc,noerror
dd if=/dev/zero of=/abc bs=1 count=1 conv=notrunc,ucase
dd if=/dev/cdrom of=/home/satya/myCD.iso bs=2048 conv=sync,notrunc,lcase
dd if=/dev/hda3 skip=9200 of=/home/satya/backup_set_3.img bs=1M count=4600
dd if=/home/satya/1Gb.file bs=64k | dd of=/dev/null

DIAGCOLLECTION:Tool to collect diagnosis information, from Oracle cluster home, Oracle home and Oracle base.

#$ORA_CRS_HOME/bin/diagcollection.pl

#$ORA_CRS_HOME/bin/diagcollection.pl --collect

#$ORA_CRS_HOME/bin/diagcollection.pl --collect --crs $ORA_CRS_HOME

#$ORA_CRS_HOME/bin/diagcollection.pl --collect --oh $ORACLE_HOME


#$ORA_CRS_HOME/bin/diagcollection.pl --collect --ob $ORACLE_BASE

#$ORA_CRS_HOME/bin/diagcollection.pl --collect --all

#$ORA_CRS_HOME/bin/diagcollection.pl --coreanalysis
--- analyze all the trace & log files and summarizes in a text file

#$ORA_CRS_HOME/bin/diagcollection.pl --clean   --- cleans gathered info

GSDCTL: - Global Service Daemon Control
gsdctl start  -- start the GSD service
gsdctl stop  -- stop the GSD service
gsdctl stat   -- To obtain the status of the GSD service
$ gsdctl start
$ gsdctl stop
$ gsdctl stat
Log file will be at $ORACLE_HOME/srvm/log/gsdaemon_node_name.log
Oracle RAC O2CB Cluster Service Commands
/etc/init.d/o2cb start
/etc/init.d/o2cb start ocfs2
/etc/init.d/o2cb stop
/etc/init.d/o2cb stop ocfs2
/etc/init.d/o2cb restart
/etc/init.d/o2cb restart ocfs2
/etc/init.d/o2cb status       -- Check status
/etc/init.d/o2cb load    -- Loads all OCFS2 modules
/etc/init.d/o2cb unload    -- Unloads all OCFS2 modules
/etc/init.d/o2cb online ocfs2 -- Brings the cluster online
/etc/init.d/o2cb offline ocfs2  -- Takes the cluster offline
/etc/init.d/o2cb configure  -- Configuring the O2CB driver
/etc/init.d/o2cb enable
#chkconfig --add o2cb
#chkconfig --list o2cb
o2cb            0:off   1:off   2:on    3:on    4:on    5:on    6:off
#chkconfig --del o2cb
service o2cb status

OCRCHECK: -- Displays health of OCR (Oracle Cluster Registry).
$ocrcheck -help or ocrcheck -h
$ocrcheck
Version : 2
Total space (kbytes) : 262144
Used space (kbytes) : 16256
Available space (kbytes) : 245888
ID : 1918913332
Device/File Name : /dev/raw/raw1 Device/File integrity check succeeded
Device/File Name : /dev/raw/raw2 Device/File integrity check succeeded
Cluster registry integrity check succeeded

#ocrcheck -local
Status of Oracle Cluster Registry is as follows :
Version  :  3
Total space (kbytes) : 262132
Used space (kbytes) : 9200
Available space (kbytes) : 252932
ID :  604793089
Device/File Name : /u02/crs/cdata/localhost/lnx6.olr  Device/File integrity check succeeded
Local OCR integrity check succeeded

$ocrcheck -local -config

Log file will be $ORACLE_HOME/log/node_name/client/ocrcheck_pid.log

Debugging can be controlled through $ORA_CRS_HOME/srvm/admin/ocrlog.ini

How do I identify the OCR file location?
#ocrcheck
OCRCONFIG: -- OCR (Oracle Cluster Registry) CONFIGuration tool#ocrconfig -help or ocrconfig -h
#ocrconfig -showbackup [auto|manual]
-- default location is $ORA_CRS_HOME/cdata/cluster_name
#ocrconfig -showbackup
#ocrconfig -backuploc dir_name    -- change OCR autobackup directory location
#ocrconfig -backuploc /u02/backups

#ocrconfig -manualbackup      -- Oracle RAC 11g command, to perform OCR backup manually
#ocrconfig -restore backup_file.ocr    -- recovering from autobackup file
#ocrconfig -restore /u02/backups/backup00.ocr

#ocrconfig -export file_name.dmp [-s online]
-- exports OCR content to a file
#ocrconfig -export /tmp/ocr_exp
#ocrconfig -import file_name.dmp
-- recover OCR logically, must be done on all nodes
#ocrconfig -import /tmp/ocr_exp

#ocrconfig -replace ocr [file_name]   -- adding/replacing an OCR file
#ocrconfig -replace ocrmirror [file_name]

#ocrconfig -repair ocr file_name
#ocrconfig -repair ocrmirror file_name
#ocrconfig -repair -replace current_OCR_location -replacement target_OCR_location

#ocrconfig -upgrade [user [group]]   -- upgrades OCR
#ocrconfig -downgrade [-version version_string]   -- downgrades OCR

#ocrconfig -overwrite

#ocrconfig –local –import file_name
#ocrconfig –local –manualbackup
#ocrconfig -local -backuploc new_olr_backup_path
#ocrconfig -local -restore file_name

#ocrconfig -add +new_disk_group
#ocrconfig -delete +unused_disk_group

#ocrconfig -add file_location
#ocrconfig -add /dev/sdd1
#ocrconfig -delete old_storage_location
#ocrconfig -delete /dev/raw/raw2

Log file will be $ORACLE_HOME/log/node_name/client/ocrconfig_pid.log

Debugging can be controlled through $ORA_CRS_HOME/srvm/admin/ocrlog.ini
How to take backup of OCR file?
#ocrconfig -manualbackup
#ocrconfig -export file_name.dmp
How to recover OCR file?
#ocrconfig -restore backup_file.ocr
#ocrconfig -import file_name.dmp

OCRDUMP: -- dumps OCR (Oracle Cluster Registry) contents to a file

#ocrdump -help or ocrdump -h

#ocrdump [file_name|-stdout] [-backupfile backup_filename] [-keyname key_name] [-xml] [-noheader]

#ocrdump               -- default filename is OCRDUMPFILE
#ocrdump MYFILE
#ocrdump ${HOST}_OCRDUMP
#ocrdump -backupfile my_file
#ocrdump -stdout -keyname SYSTEM
#ocrdump -stdout -xml
$ocrdump -local olr.lst                 --> Normal Text Format
$ocrdump -local -xml olr_xml.lst  --> XML format
$ocrdump -local -backupfile olr_backup_file_name

Log file will be $ORACLE_HOME/log/node_name/client/ocrdump_pid.log

Debugging can be controlled through $ORA_CRS_HOME/srvm/admin/ocrlog.ini
How to take backup of OCR file?
#ocrdump -backupfile my_file
OIFCFG: -- Oracle Interface Configuration tool

A command line tool for both single instance Oracle databases and RAC databases that enables us to allocate and deallocate network interfaces to components, direct components to use specific network interfaces, and retrieve component configuration information.

oifcfg [-help] -- will give help
$ ./oifcfg -help
$ ./oifcfg

oifcfg iflist [-p [-n]]
-p includes description of the subnet, -n includes subnet mask

oifcfg iflist          -- display a list of current subnets
etho 147.43.1.60
ethl 192.168.1.150

oifcfg iflist -p -n
etho 147.43.1.60 UNKNOWN 255.255.255.0 (public interfaces are UNKNOWN)
ethl 192.168.1.150 PRIVATE 255.255.255.0

oifcfg getif [-node node_name|-global] [-if if_name[/subnet] [-type {cluster_interconnect|public|storage}]]
-- To display a list of networks

oifcfg getif
eth1 192.168.1.150 global cluster_interconnect
eth0 192.168.0.150 global public

oifcfg setif {-node node_name|-global} {if_name/subnet:{cluster_interconnect|public|storage}}...
oifcfg setif -global eth0/10.50.99.0:public
oifcfg setif -global eth0/172.19.141.0:cluster_interconnect

oifcfg delif [-node node_name|-global] [if_name[/subnet]]
oifcfg delif -global
oifcfg delif -global eth0
oifcfg delif -global eth1/172.21.65.0

olsnodes commands in Oracle RAC OLSNODES:

Provides the list of nodes and other information for all nodes participating in the cluster

#olsnodes [node_name] [-g] [-i] [-l] [-n] [-p] [-s] [-t] [-v]
node_name -- displays information for the particular node

g -- more details
i -- with VIP
l -- local node name
n -- with node number
p -- private interconnect
s -- status of the node (ACTIVE or INACTIVE)
t -- type of the node (PINNED or UNPINNED)
v -- verbose

How to find out the nodes in Oracle RAC cluster?
#olsnodes             -- will list the nodes in the cluster

#olsnodes -n

#olsnodes node44 -v
#olsnodes -n -p -i
node1-pub 1 node1-prv node1-vip
node2-pub 2 node2-prv node2-vip

#olsnodes -i
node1 178.192.1.1
node2 178.192.2.1
node3 178.192.3.1
node4 178.192.4.1

ONSCTL: -- to manage ONS (Oracle Notification Service)
ONS - A publish and subscribe service for communicating information about all FAN events.
onsctl or onsctl help or onsctl -h
onsctl start -- to start ONS
onsctl stop -- to stop ONS
onsctl ping -- to find out the status of ONS
onsctl debug -- to display debug information for the ons daemon
onsctl reconfig -- to reload the ons configuration
onsctl detailed -- to print a verbose syntax description
from 11g release 2
onsctl command [options]
onsctl or onsctl help or onsctl -h
onsctl start -- to start ONS
onsctl shutdown -- to shutdown ONS
onsctl ping [max-retry] -- to ping local ons
onsctl debug [attr=val ...] -- to display ons server debug information
onsctl reload -- to trigger ons to reread its configuration file
onsctl set [attr=val ...] -- to set ons log parameters
onsctl query [attr=val] -- to query ons log parameters
onsctl usage [command] -- to print detailed usage description

srvconfig [options]srvconfig or srvconfig -help or srvconfig -?

srvconfig -exp file_name
-- exports the contents of the configuration information/cluster registry
srvconfig -imp file_name
-- imports the configuration information/cluster registry

srvconfig -init -- initialize cluster registry (if not already initialized)
srvconfig -init -f -- force initialization of configuration even if initialized

srvconfig -upgrade -dbname db_name -orahome ORACLE_HOME
-- upgrade the database configuration
srvconfig -downgrade -dbname db_name -orahome ORACLE_HOME -version ver_str
-- downgrade the database configuration

SRVCTL: (Server Control utility)srvctl command target [options]
commands: enable|disable|start|stop|relocate|status|add|remove|modify|getenv|setenv|unsetenv|config
targets: database/db|instance/inst|service/serv|nodeapps|asm|listener
targets: database/db|instance/inst|service/serv|nodeapps|asm|listener |diskgroup|home|ons|eons|filesystem|gns|oc4j|scan|scan_listener |srvpool|server|VIP -- From Oracle 11g R2

srvctl -help or srvctl -v
srvctl -V -- prints version
 srvctl version: 10.2.0.0.0 (or) srvctl version: 11.2.0.1.0
srvctl -h -- print usage
srvctl status service –h

Database:
--------------------------------------------------------------------------------
srvctl add database -d db_name -o ORACLE_HOME [-m domain_name][-p spfile] [-A name|ip/netmask]
[-r {PRIMARY|PHYSICAL_STANDBY|LOGICAL_STANDBY|SNAPSHOT_STANDBY}]
[-s start_options] [-n db_name] [-y {AUTOMATIC|MANUAL}]
srvctl add database -d prod -o /u01/oracle/product/102/prod

srvctl remove database -d db_name [-f]
srvctl remove database -d prod

srvctl start database -d db_name [-o start_options] [-c connect_str|-q]
srvctl start database -d db_name [-o open]
srvctl start database -d db_name -o nomount
srvctl start database -d db_name -o mount

srvctl start db -d prod
srvctl start database -d apps -o open

srvctl stop database -d db_name [-o stop_options] [-c connect_str|-q]
srvctl stop database -d db_name [-o normal]
srvctl stop database -d db_name -o transactional
srvctl stop database -d db_name -o immediate
srvctl stop database -d db_name -o abort
srvctl stop db -d crm -o immediate

srvctl status database -d db_name [-f] [-v] [-S level]
srvctl status database -d db_name -v service_name
srvctl status database -d hrms
srvctl enable database -d db_name
srvctl enable database -d vis
srvctl disable database -d db_name
srvctl disable db -d vis
srvctl config database
srvctl config database -d db_name [-a] [-t]
srvctl config database
srvctl config database -d HYD -a

srvctl modify database -d db_name [-n db_name] [-o ORACLE_HOME] [-m domain_name] [-p spfile]
[-r {PRIMARY|PHYSICAL_STANDBY|LOGICAL_STANDBY|SNAPSHOT_STANDBY}] [-s start_options] [-y {AUTOMATIC|MANUAL}]
srvctl modify database -d hrms -r physical_standby
srvctl modify db -d RAC -p /u03/oradata/RAC/spfileRAC.ora -- moves p file
srvctl modify database –d HYD –o /u01/app/oracle/product/11.1/db –s open

srvctl getenv database -d db_name [-t name_list]
srvctl getenv database -d prod

srvctl setenv database -d db_name {-t name=val[,name=val,...]|-T name=val}
srvctl setenv database –d HYD –t “TNS_ADMIN=/u01/app/oracle/product/11.1/asm/network/admin”
srvctl setenv db -d prod -t LANG=en

srvctl unsetenv database -d db_name [-t name_list]
srvctl unsetenv database -d prod -t CLASSPATH
In 11g Release 2, some command's syntax has been changed:
srvctl add database -d db_unique_name -o ORACLE_HOME [-x node_name] [-m domain_name] [-p spfile] [-r {PRIMARY|PHYSICAL_STANDBY|LOGICAL_STANDBY|SNAPSHOT_STANDBY}] [-s start_options] [-t stop_options] [-n db_name] [-y {AUTOMATIC|MANUAL}] [-g server_pool_list] [-a "diskgroup_list"]
srvctl add database -d prod -o /u01/oracle/product/112/prod -m foo.com -p +dg1/prod/spfileprod.ora -r PRIMARY -s open -t normal -n db2 -y AUTOMATIC -g svrpool1,svrpool2 -a "dg1,dg2"
srvctl remove database -d db_unique_name [-f] [-y] [-v]
srvctl remove database -d prod -y
srvctl stop database -d db_unique_name [-o stop_options] [-f]
srvctl stop database -d dev -f
srvctl status database -d db_unique_name [-f] [-v]
srvctl status db -d sat -v
srvctl enable database -d db_unique_name [-n node_name]
srvctl enable database -d vis -n lnx01
srvctl disable database -d db_unique_name [-n node_name]
srvctl disable db -d vis -n lnx03
srvctl config database [-d db_unique_name [-a]]
srvctl config db -d db_erp -a
srvctl modify database -d db_unique_name [-n db_name] [-o ORACLE_HOME] [-u oracle_user] [-m domain] [-p spfile] [-r {PRIMARY|PHYSICAL_STANDBY|LOGICAL_STANDBY|SNAPSHOT_STANDBY}] [-s start_options] [-t stop_options] [-y {AUTOMATIC|MANUAL}] [-g "server_pool_list"] [-a "diskgroup_list"|-z]
srvctl modify db -d prod -r logical_standby
srvctl modify database -d racTest -a "SYSFILES,LOGS,OLTP"
srvctl modify database -d ronedb -e rac1,rac2

srvctl relocate database -d db_unique_name {[-n target_node] [-w timeout] | -a [-r]} [-v]
srvctl relocate database -d rontest -n node2
srvctl relocate database -d rone2db -n lnxrac2 -w 120 -v

srvctl convert database -d ....
srvctl convert database -d ronedb -c RAC -n rac1
srvctl convert database -d ronedb -c RACONENODE -i RoneDB

Instance:
-----------
srvctl add instance –d db_name –i inst_name -n node_name
srvctl add instance -d prod -i prod01 -n linux01
StartDatabaseLevelingTargets

srvctl remove instance –d db_name –i inst_name [-f]
srvctl remove instance -d prod -i prod01
srvctl start instance -d db_name -i inst_names [-o start_options] [-c connect_str|-q]
srvctl start instance –d db_name –i inst_names [-o open]
srvctl start instance –d db_name –i inst_names -o nomount
srvctl start instance –d db_name –i inst_names -o mount
srvctl start instance –d dev -i dev2
srvctl stop instance -d db_name -i inst_names [-o stop_options] [-c connect_str|-q]
srvctl stop instance –d db_name –i inst_names [-o normal]
srvctl stop instance –d db_name –i inst_names -o transactional
srvctl stop instance –d db_name –i inst_names -o immediate
srvctl stop instance –d db_name –i inst_names -o abort
srvctl stop inst –d vis -i vis
srvctl status instance –d db_name –i inst_names [-f] [-v] [-S level]
srvctl status inst –d racdb -i racdb2
srvctl enable instance –d db_name –i inst_names
srvctl enable instance -d prod -i "prod1,prod2"
srvctl disable instance –d db_name –i inst_names
srvctl disable inst -d prod -i "prod1,prod3"
srvctl modify instance -d db_name -i inst_name {-s asm_inst_name|-r} -- set dependency of instance to ASM
srvctl modify instance -d db_name -i inst_name -n node_name -- move the instance
srvctl modify instance -d db_name -i inst_name -r -- remove the instance
srvctl getenv instance –d db_name –i inst_name [-t name_list]
srvctl setenv instance –d db_name [–i inst_name] {-t "name=val[,name=val,...]" | -T "name=val"}
srvctl unsetenv instance –d db_name [–i inst_name] [-t name_list]
In 11g Release 2, some command's syntax has been changed:
srvctl start instance -d db_unique_name {-n node_name -i "instance_name_list"} [-o start_options]
srvctl start instance -d prod -n node2
srvctl start inst -d prod -i "prod2,prod3"
srvctl stop instance -d db_unique_name {[-n node_name]|[-i "instance_name_list"]} [-o stop_options] [-f]
srvctl stop inst -d prod -n node1
srvctl stop instance -d prod -i prod1
srvctl status instance -d db_unique_name {-n node_name | -i "instance_name_list"} [-f] [-v]
srvctl status instance -d prod -i "prod1,prod2" -v
srvctl modify instance -d db_unique_name -i instance_name {-n node_name|-z}
srvctl modify instance -d prod -i prod1 -n mynode
srvctl modify inst -d prod -i prod1 -z

Service:
--------
srvctl add service -d db_name -s service_name -r pref_insts [-a avail_insts] [-P TAF_policy]
srvctl add service -d db_name -s service_name -u {-r "new_pref_inst" | -a "new_avail_inst"}
srvctl add service -d RAC -s PRD -r RAC01,RAC02 -a RAC03,RAC04
srvctl add serv -d CRM -s CRM -r CRM1 -a CRM3 -P basic
srvctl remove service -d db_name -s service_name [-i inst_name] [-f]
srvctl remove serv -d dev -s sales
srvctl remove service -d dev -s sales -i dev01,dev02
srvctl start service -d db_name [-s service_names [-i inst_name]] [-o start_options]
srvctl start service -d db_name -s service_names [-o open]
srvctl start service -d db_name -s service_names -o nomount
srvctl start service -d db_name -s service_names -o mount
srvctl start serv -d dwh -s dwh
srvctl stop service -d db_name [-s service_names [-i inst_name]] [-f]
srvctl stop serv -d dwh -s dwh
srvctl status service -d db_name [-s service_names] [-f] [-v] [-S level]
srvctl status service -d dev -s dev
srvctl enable service -d db_name -s service_names [–i inst_name]
srvctl enable service -d apps -s apps1
srvctl disable service -d db_name -s service_names [–i inst_name]
srvctl disable serv -d dev -s dev -i dev1
srvctl config service -d db_name [-s service_name] [-a] [-S level]
srvctl config service -d db_name -a -- -a shows TAF configuration
srvctl config service -d TEST -s test PREF:TST1 AVAIL:TST2
srvctl modify service -d db_name -s service_name -i old_inst_name -t new_inst_name [-f]
srvctl modify service -d db_name -s service_name -i avail_inst_name -r [-f]
srvctl modify service -d db_name -s service_name -n -i preferred_list [-a available_list] [-f]
srvctl modify service -d db_name -s service_name -i old_inst_name -a avail_inst -P TAF_policy
srvctl modify serv -d PROD -s DWH -n -i I1,I2,I3,I4 -a I5,I6
srvctl relocate service -d db_name -s service_name –i old_inst_name -t target_inst [-f]
srvctl getenv service -d db_name -s service_name -t name_list
srvctl setenv service -d db_name [-s service_name] {-t "name=val[,name=val,...]" | -T "name=val"}
srvctl unsetenv service -d db_name -s service_name -t name_list

In 11g Release 2, some command's syntax has been changed:srvctl add service -d db_unique_name -s service_name [-l [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]] [-y {AUTOMATIC|MANUAL}] [-q {true|false}] [-j {SHORT|LONG}] [-B {NONE|SERVICE_TIME|THROUGHPUT}][-e {NONE|SESSION|SELECT}] [-m {NONE|BASIC}][-z failover_retries] [-w failover_delay]
srvctl add service -d rac -s rac1 -q TRUE -m BASIC -e SELECT -z 180 -w 5 -j LONG
srvctl add service -d db_unique_name -s service_name -u {-r preferred_list | -a available_list}
srvctl add service -d db_unique_name -s service_name
-g server_pool [-c {UNIFORM|SINGLETON}] [-k network_number]
[-l [PRIMARY|PHYSICAL_STANDBY|LOGICAL_STANDBY|SNAPSHOT_STANDBY]
[-y {AUTOMATIC|MANUAL}] [-q {TRUE|FALSE}] [-j {SHORT|LONG}]
[-B {NONE|SERVICE_TIME|THROUGHPUT}] [-e {NONE|SESSION|SELECT}]
[-m {NONE|BASIC}] [-P {BASIC|NONE|PRECONNECT}] [-x {TRUE|FALSE}]
[-z failover_retries] [-w failover_delay]
srvctl add service -d db_unique_name -s service_name -r preferred_list [-a available_list] [-P {BASIC|NONE|PRECONNECT}]
[-l [PRIMARY|PHYSICAL_STANDBY|LOGICAL_STANDBY|SNAPSHOT_STANDBY]
[-y {AUTOMATIC|MANUAL}] [-q {TRUE|FALSE}] [-j {SHORT|LONG}]
[-B {NONE|SERVICE_TIME|THROUGHPUT}] [-e {NONE|SESSION|SELECT}]
[-m {NONE|BASIC}] [-x {TRUE|FALSE}] [-z failover_retries] [-w failover_delay]
srvctl add serv -d dev -s sales -r dev01,dev02 -a dev03 -P PRECONNECT
srvctl start service -d db_unique_name [-s "service_name_list" [-n node_name | -i instance_name]] [-o start_options]
srvctl start serv -d dev -s dev
srvctl start service -d dev -s dev -i dev2
srvctl stop service -d db_unique_name [-s "service_name_list"] [-n node_name | -i instance_name] [-f]
srvctl stop service -d dev -s dev
srvctl stop serv -d dev -s dev -i dev2
srvctl status service -d db_unique_name [-s "service_name_list"] [-f] [-v]
srvctl status service -d dev -s dev -v
srvctl enable service -d db_unique_name -s "service_name_list" [-i instance_name | -n node_name]
srvctl enable service -d dev -s dev
srvctl enable serv -d dev -s dev -i dev1
srvctl disable service -d db_unique_name -s "service_name_list" [-i instance_name | -n node_name]
srvctl disable service -d dev -s "dev,marketing"
srvctl disable serv -d dev -s dev -i dev1
srvctl config service -d db_unique_name [-s service_name] [-a]
srvctl config service -d dev -s dev
srvctl modify service -d db_unique_name -s service_name
[-c {UNIFORM|SINGLETON}] [-P {BASIC|PRECONNECT|NONE}]
[-l {[PRIMARY]|[PHYSICAL_STANDBY]|[LOGICAL_STANDBY]|[SNAPSHOT_STANDBY]} [-q {TRUE|FALSE}] [-x {TRUE|FALSE}] [-j {SHORT|LONG}] [-B {NONE|SERVICE_TIME|THROUGHPUT}] [-e {NONE|SESSION|SELECT}] [-m {NONE|BASIC}] [-z failover_retries] [-w failover_delay] [-y {AUTOMATIC|MANUAL}]
srvctl modify service -d db_unique_name -s service_name -i old_instance_name -t new_instance_name [-f]
srvctl modify service -d db_unique_name -s service_name -i avail_inst_name -r [-f]
srvctl modify service -d db_unique_name -s service_name -n -i preferred_list [-a available_list] [-f]
srvctl modify service -d dev -s dev -i dev1 -t dev2
srvctl modify serv -d dev -s dev -i dev1 -r
srvctl modify service -d dev -s dev -n -i dev1 -a dev2
srvctl relocate service -d db_unique_name -s service_name {-c source_node -n target_node|-i old_instance_name -t new_instance_name} [-f]
srvctl relocate service -d dev -s dev -i dev1 -t dev3

Nodeapps:
------------
#srvctl add nodeapps -n node_name -o ORACLE_HOME -A name|ip/netmask[/if1[|if2|...]]
#srvctl add nodeapps -n lnx02 -o $ORACLE_HOME -A 192.168.0.151/255.255.0.0/eth0

#srvctl remove nodeapps -n node_names [-f]

#srvctl start nodeapps -n node_name     -- Starts GSD, VIP, listener & ONS

#srvctl stop nodeapps -n node_name [-r] -- Stops GSD, VIP, listener & ONS

#srvctl status nodeapps -n node_name

#srvctl config nodeapps -n node_name [-a] [-g] [-o] [-s] [-l]
-a Display VIP configuration
-g Display GSD configuration
-s Display ONS daemon configuration
-l Display listener configuration

#srvctl modify nodeapps -n node_name [-A new_vip_address]
#srvctl modify nodeapps -n lnx06 -A 10.50.99.43/255.255.252.0/eth0

#srvctl getenv nodeapps -n node_name [-t name_list]

#srvctl setenv nodeapps -n node_name {-t "name=val[,name=val,...]"|-T "name=val"}
#srvctl setenv nodeapps –n adcracdbq3 –t “TNS_ADMIN=/u01/app/oracle/product/11.1/asm/network/admin”

#srvctl unsetenv nodeapps -n node_name [-t name_list]
In 11g Release 2, some command's syntax has been changed:srvctl add nodeapps -n node_name -A {name|ip}/netmask[/if1[|if2|...]] [-m multicast_ip_address] [-p multicast_port_number] [-l ons_local_port] [-r ons_remote-port] [-t host[:port][,host[:port],...]] [-v]
srvctl add nodeapps -S subnet/netmask[/if1[|if2|...]] [-d dhcp_server_type] [-m multicast_ip_address] [-p multicast_port_number] [-l ons_local_port] [-r ons_remote-port] [-t host[:port][,host[:port],...]] [-v]
#srvctl add nodeapps -n devnode1 -A 1.2.3.4/255.255.255.0
srvctl remove nodeapps [-f] [-y] [-v]
srvctl remove nodeapps
srvctl start nodeapps [-n node_name] [-v]
srvctl start nodeapps
srvctl stop nodeapps [-n node_name] [-r] [-v]
srvctl stop nodeapps
srvctl status nodeapps
srvctl enable nodeapps [-g] [-v]
srvctl enable nodeapps -g -v
srvctl disable nodeapps [-g] [-v]
srvctl disable nodeapps -g -v
srvctl config nodeapps [-a] [-g] [-s] [-e]
srvctl config nodeapps -a -g -s -e
srvctl modify nodeapps [-n node_name -A new_vip_address] [-S subnet/netmask[/if1[|if2|...]] [-m multicast_ip_address] [-p multicast_port_number] [-e eons_listen_port] [-l ons_local_port] [-r ons_remote_port] [-t host[:port][,host:port,...]] [-v]
srvctl modify nodeapps -n mynode1 -A 100.200.300.40/255.255.255.0/eth0
srvctl getenv nodeapps [-a] [-g] [-s] [-e] [-t "name_list"] [-v]
srvctl getenv nodeapps -a
srvctl setenv nodeapps {-t "name=val[,name=val][...]" | -T "name=val"} [-v]
srvctl setenv nodeapps -T "CLASSPATH=/usr/local/jdk/jre/rt.jar" -v
srvctl unsetenv nodeapps -t "name_list" [-v]
srvctl unsetenv nodeapps -t "test_var1,test_var2"

ASM:
------
srvctl add asm -n node_name -i asminstance -o ORACLE_HOME [-p spfile]

srvctl remove asm -n node_name [-i asminstance] [-f]
srvctl remove asm -n db6

srvctl start asm -n node_name [-i asminstance] [-o start_options] [-c connect_str|-q]
srvctl start asm -n node_name [-i asminstance] [-o open]
srvctl start asm -n node_name [-i asminstance] -o nomount
srvctl start asm -n node_name [-i asminstance] -o mount
srvctl start asm -n linux01

srvctl stop asm -n node_name [-i asminstance] [-o stop_options] [-c connect_str|-q]
srvctl stop asm -n node_name [-i asminstance] [-o normal]
srvctl stop asm -n node_name [-i asminstance] -o transactional
srvctl stop asm -n node_name [-i asminstance] -o immediate
srvctl stop asm -n node_name [-i asminstance]-o abort
srvctl stop asm -n racnode1
srvctl stop asm -n devnode1 -i +asm1

srvctl status asm -n node_name
srvctl status asm -n racnode1

srvctl enable asm -n node_name [-i asminstance]
srvctl enable asm -n lnx03 -i +asm3

srvctl disable asm -n node_name [-i asminstance]
srvctl disable asm -n lnx02 -i +asm2
srvctl config asm -n node_name
srvctl config asm -n lnx08

srvctl modify asm -n node_name -i asminstance [-o ORACLE_HOME] [-p spfile]
srvctl modify asm –n rac6 -i +asm6 –o /u01/app/oracle/product/11.1/asm
In 11g Release 2, some command's syntax has been changed:
srvctl add asm [-l lsnr_name] [-p spfile] [-d asm_diskstring]
srvctl add asm
srvctl add asm -l LISTENERASM -p +dg_data/spfile.ora
srvctl remove asm [-f]
srvctl remove asm -f
srvctl start asm [-n node_name] [-o start_options]
srvctl start asm -n devnode1
srvctl stop asm [-n node_name] [-o stop_options] [-f]
srvctl stop asm -n devnode1 -f
srvctl status asm [-n node_name] [-a]
srvctl status asm -n devnode1 -a
srvctl enable asm [-n node_name]
srvctl enable asm -n devnode1
srvctl disable asm [-n node_name]
srvctl disable asm -n devnode1
srvctl config asm [-a]
srvctl config asm -a
srvctl modify asm [-l lsnr_name] [-p spfile] [-d asm_diskstring]
srvctl modify asm [-n node_name] [-l listener_name] [-d asm_diskstring] [-p spfile_path_name]
srvctl modify asm -l lsnr1
srvctl getenv asm [-t name[, ...]]
srvctl getenv asm
srvctl setenv asm {-t "name=val [,...]" | -T "name=value"}
srvctl setenv asm -t LANG=en
srvctl unsetenv asm -t "name[, ...]"
srvctl unsetenv asm -t CLASSPATH

Listener:
--------------------------------------------------------------------------------
srvctl add listener -n node_name -o ORACLE_HOME [-l listener_name]  -- 11g R1 command

srvctl remove listener -n node_name [-l listener_name] -- 11g R1 command

srvctl start listener -n node_name [-l listener_names]
srvctl start listener -n node1

srvctl stop listener -n node_name [-l listener_names]
srvctl stop listener -n node1
srvctl status listener [-n node_name] [-l listener_names] -- 11g R1 command
srvctl status listener -n node2

srvctl config listener -n node_name
srvctl modify listener -n node_name [-l listener_names] -o ORACLE_HOME               -- 11g R1 command
srvctl modify listener -n racdb4 -o /u01/app/oracle/product/11.1/asm -l "LISTENER_RACDB4"
In 11g Release 2, some command's syntax has been changed:
srvctl add listener [-l lsnr_name] [-s] [-p "[TCP:]port[, ...][/IPC:key][/NMP:pipe_name][/TCPS:s_port] [/SDP:port]"] [-k network_number] [-o ORACLE_HOME]
srvctl add listener -l LISTENERASM -p "TCP:1522" -o $ORACLE_HOME
srvctl add listener -l listener112 -p 1341 -o /ora/ora112
srvctl remove listener [-l lsnr_name|-a] [-f]
srvctl remove listener -l lsnr01
srvctl stop listener [-n node_name] [-l lsnr_name] [-f]
srvctl enable listener [-l lsnr_name] [-n node_name]
srvctl enable listener -l listener_dev -n node5
srvctl disable listener [-l lsnr_name] [-n node_name]
srvctl disable listener -l listener_dev -n node5
srvctl config listener [-l lsnr_name] [-a]
srvctl config listener
srvctl modify listener [-l listener_name] [-o oracle_home] [-u user_name] [-p "[TCP:]port_list[/IPC:key][/NMP:pipe_name][/TCPS:s_port][/SDP:port]"] [-k network_number]
srvctl modify listener -n node1 -p "TCP:1521,1522"
srvctl getenv listener [-l lsnr_name] [-t name[, ...]]
srvctl getenv listener
srvctl setenv listener [-l lsnr_name] {-t "name=val [,...]" | -T "name=value"}
srvctl setenv listener -t LANG=en
srvctl unsetenv listener [-l lsnr_name] -t "name[, ...]"
srvctl unsetenv listener -t "TNS_ADMIN"
New srvctl commands in 11g Release 2

Diskgroup:
--------------------------------------------------------------------------------
srvctl remove diskgroup -g diskgroup_name [-n node_list] [-f]
srvctl remove diskgroup -g DG1 -f

srvctl start diskgroup -g diskgroup_name [-n node_list]
srvctl start diskgroup -g diskgroup1 -n node1,node2

srvctl stop diskgroup -g diskgroup_name [-n node_list] [-f]
srvctl stop diskgroup -g ASM_FRA_DG
srvctl stop diskgroup -g dg1 -n node1,node2 -f

srvctl status diskgroup -g diskgroup_name [-n node_list] [-a]
srvctl status diskgroup -g dg_data -n node1,node2 -a

srvctl enable diskgroup -g diskgroup_name [-n node_list]
srvctl enable diskgroup -g diskgroup1 -n node1,node2

srvctl disable diskgroup -g diskgroup_name [-n node_list]
srvctl disable diskgroup -g dg_fra -n node1, node2

Home:
-------
srvctl start home -o ORACLE_HOME -s state_file [-n node_name]
srvctl start home -o /u01/app/oracle/product/11.2.0/db_1 -s ~/state.txt

srvctl stop home -o ORACLE_HOME -s state_file [-t stop_options] [-n node_name] [-f]
srvctl stop home -o /u01/app/oracle/product/11.2.0/db_1 -s ~/state.txt

srvctl status home -o ORACLE_HOME -s state_file [-n node_name]
srvctl status home -o /u01/app/oracle/product/11.2.0/db_1 -s ~/state.txt

ONS (Oracle Notification Service):
-------------------------------------
srvctl add ons [-l ons-local-port] [-r ons-remote-port] [-t host[:port][,host[:port]...]] [-v]
srvctl add ons -l 6200

srvctl remove ons [-f] [-v]
srvctl remove ons -f

srvctl start ons [-v]
srvctl start ons -v

srvctl stop ons [-v]
srvctl stop ons -v

srvctl status ons

srvctl enable ons [-v]
srvctl enable ons

srvctl disable ons [-v]
srvctl disable ons

srvctl config ons

srvctl modify ons [-l ons-local-port] [-r ons-remote-port] [-t host[:port][,host[:port]...]] [-v]
srvctl modify ons

EONS (E Oracle Notification Service):
---------------------------------------
srvctl add eons [-p portnum] [-m multicast-ip-address] [-e eons-listen-port] [-v]
#srvctl add eons -p 2018

srvctl remove eons [-f] [-v]
srvctl remove eons -f

srvctl start eons [-v]
srvctl start eons

srvctl stop eons [-f] [-v]
srvctl stop eons -f

srvctl status eons

srvctl enable eons [-v]
srvctl enable eons

srvctl disable eons [-v]
srvctl disable eons

srvctl config eons

srvctl modify eons [-m multicast_ip_address] [-p multicast_port_number] [-e eons_listen_port] [-v]
srvctl modify eons -p 2018

FileSystem:
--------------------------------------------------------------------------------
srvctl add filesystem -d volume_device -v volume_name -g diskgroup_name [-m mountpoint_path] [-u user_name]
srvctl add filesystem -d /dev/asm/d1volume1 -v VOLUME1 -d RAC_DATA -m /oracle/cluster1/acfs1

srvctl remove filesystem -d volume_device_name [-f]
srvctl remove filesystem -d /dev/asm/racvol1

srvctl start filesystem -d volume_device_name [-n node_name]
srvctl start filesystem -d /dev/asm/racvol3

srvctl stop filesystem -d volume_device_name [-n node_name] [-f]
srvctl stop filesystem -d /dev/asm/racvol1 -f

srvctl status filesystem -d volume_device_name
srvctl status filesystem -d /dev/asm/racvol2

srvctl enable filesystem -d volume_device_name
srvctl enable filesystem -d /dev/asm/racvol9

srvctl disable filesystem -d volume_device_name
srvctl disable filesystem -d /dev/asm/racvol1
srvctl config filesystem -d volume_device_path

srvctl modify filesystem -d volume_device_name -u user_name
srvctl modify filesystem -d /dev/asm/racvol1 -u sysadmin

SrvPool (Server Pool):
--------------------------------------------------------------------------------
srvctl add srvpool -g server_pool [-i importance] [-l min_size] [-u max_size] [-n node_list] [-f]
srvctl add srvpool -g SP1 -i 1 -l 3 -u 7 -n node1,node2

srvctl remove srvpool -g server_pool
srvctl remove srvpool -g srvpool1

srvctl status srvpool [-g server_pool] [-a]
srvctl status srvpool -g srvpool2 -a

srvctl config srvpool [-g server_pool]
srvctl config srvpool -g dbpool

srvctl modify srvpool -g server_pool [-i importance] [-l min_size] [-u max_size] [-n node_name_list] [-f]
srvctl modify srvpool -g srvpool4 -i 0 -l 2 -u 4 -n node3, node4

Server:
--------------------------------------------------------------------------------
srvctl status server -n "server_name_list" [-a]
srvctl status server -n server11 -a

srvctl relocate server -n "server_name_list" -g server_pool_name [-f]
srvctl relocate server -n "linux1, linux2" -g sp2

Scan (Single Client Access Name):
----------------------------------
srvctl add scan -n scan_name [-k network_number] [-S subnet/netmask[/if1[|if2|...]]]
#srvctl add scan -n scan.mycluster.example.com

srvctl remove scan [-f]
srvctl remove scan
srvctl remove scan -f

srvctl start scan [-i ordinal_number] [-n node_name]
srvctl start scan
srvctl start scan -i 1 -n node1

srvctl stop scan [-i ordinal_number] [-f]
srvctl stop scan
srvctl stop scan -i 1

srvctl status scan [-i ordinal_number]
srvctl status scan
srvctl status scan -i 1

srvctl enable scan [-i ordinal_number]
srvctl enable scan
srvctl enable scan -i 1

srvctl disable scan [-i ordinal_number]
srvctl disable scan
srvctl disable scan -i 3

srvctl config scan [-i ordinal_number]
srvctl config scan
srvctl config scan -i 2

srvctl modify scan -n scan_name
srvctl modify scan
srvctl modify scan -n scan1

srvctl relocate scan -i ordinal_number [-n node_name]
srvctl relocate scan -i 2 -n node2

ordinal_number=1,2,3

Scan_listener:
--------------
srvctl add scan_listener [-l lsnr_name_prefix] [-s] [-p "[TCP:]port_list[/IPC:key][/NMP:pipe_name][/TCPS:s_port] [/SDP:port]"]
#srvctl add scan_listener -l myscanlistener

srvctl remove scan_listener [-f]
srvctl remove scan_listener
srvctl remove scan_listener -f

srvctl start scan_listener [-n node_name] [-i ordinal_number]
srvctl start scan_listener
srvctl start scan_listener -i 1

srvctl stop scan_listener [-i ordinal_number] [-f]
srvctl stop scan_listener -i 3

srvctl status scan_listener [-i ordinal_number]
srvctl status scan_listener
srvctl status scan_listener -i 1

srvctl enable scan_listener [-i ordinal_number]
srvctl enable scan_listener
srvctl enable scan_listener -i 2

srvctl disable scan_listener [-i ordinal_number]
srvctl disable scan_listener
srvctl disable scan_listener -i 1

srvctl config scan_listener [-i ordinal_number]
srvctl config scan_listener
srvctl config scan_listener -i 3

srvctl modify scan_listener {-p [TCP:]port[/IPC:key][/NMP:pipe_name] [/TCPS:s_port][/SDP:port] | -u }
srvctl modify scan_listener -u

srvctl relocate scan_listener -i ordinal_number [-n node_name]
srvctl relocate scan_listener -i 1

ordinal_number=1,2,3

GNS (Grid Naming Service):
------------------------------
srvctl add gns -i ip_address -d domain
srvctl add gns -i 192.124.16.96 -d cluster.mycompany.com

srvctl remove gns [-f]
srvctl remove gns

srvctl start gns [-l log_level] [-n node_name]
srvctl start gns

srvctl stop gns [-n node_name [-v] [-f]
srvctl stop gns

srvctl status gns [-n node_name]
srvctl status gns

srvctl enable gns [-n node_name]
srvctl enable gns
srvctl disable gns [-n node_name]
srvctl disable gns -n devnode2

srvctl config gns [-a] [-d] [-k] [-m] [-n node_name] [-p] [-s] [-V] [-q name] [-l] [-v]
srvctl config gns -n lnx03

srvctl modify gns [-i ip_address] [-d domain]
srvctl modify gns -i 192.000.000.007

srvctl relocate gns [-n node_name]
srvctl relocate gns -n node2

VIP (Virtual Internet Protocol):
--------------------------------
srvctl add vip -n node_name -A {name|ip}/netmask[/if1[if2|...]] [-k network_number] [-v]
#srvctl add vip -n node96 -A 192.124.16.96/255.255.255.0 -k 2

srvctl remove vip -i "vip_name_list" [-f] [-y] [-v]
srvctl remove vip -i "vip1,vip2,vip3" -f -y -v

srvctl start vip {-n node_name|-i vip_name} [-v]
srvctl start vip -i dev1-vip -v

srvctl stop vip {-n node_name|-i vip_name} [-r] [-v]
srvctl stop vip -n node1 -v

srvctl status vip {-n node_name|-i vip_name}
srvctl status vip -i node1-vip

srvctl enable vip -i vip_name [-v]
srvctl enable vip -i prod-vip -v

srvctl disable vip -i vip_name [-v]
srvctl disable vip -i vip3 -v

srvctl config vip {-n node_name|-i vip_name}
srvctl config vip -n devnode2

srvctl getenv vip -i vip_name [-t "name_list"] [-v]
srvctl getenv vip -i node1-vip

srvctl setenv vip -i vip_name {-t "name=val[,name=val,...]" | -T "name=val"}
srvctl setenv vip -i dev1-vip -t LANG=en

srvctl unsetenv vip -i vip_name -t "name_list" [-v]
srvctl unsetenv vip -i myvip -t CLASSPATH

OC4J (Oracle Container for Java):
-----------------------------------
srvctl add oc4j [-v]
srvctl add oc4j

srvctl remove oc4j [-f] [-v]
srvctl remove oc4j

srvctl start ocj4 [-v]
srvctl start ocj4 -v

srvctl stop oc4j [-f] [-v]
srvctl stop oc4j -f -v

srvctl status oc4j [-n node_name]
srvctl status oc4j -n lnx01

srvctl enable oc4j [-n node_name] [-v]
srvctl enable oc4j -n dev3

srvctl disable oc4j [-n node_name] [-v]
srvctl disable oc4j -n dev1

srvctl config oc4j

srvctl modify oc4j -p oc4j_rmi_port [-v]
srvctl modify oc4j -p 5385

srvctl relocate oc4j [-n node_name] [-v]
srvctl relocate oc4j -n lxn06 -v

How to Delete SQL PLAN BASELINE in Oracle 11g

Steps to delete SQL_PLAN_BASELIN in oracle 11g


SQL> select SQL_HANDLE,SQL_TEXT,PLAN_NAME,CREATOR,CREATED,LAST_MODIFIED,LAST_EXECUTED,LAST_VERIFIED,ENABLED,ACCEPTED,OPTIMIZER_COST MODULE,ACTION from dba_sql_plan_baselines;

SQL_HANDLE                     SQL_TEXT                                                                         PLAN_NAME                      CREATOR                        CREATED                                                                     LAST_MODIFIED                                                               LAST_EXECUTED                                                               LAST_VERIFIED                                                               ENA ACC     MODULE ACTION
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- --- --- ---------- ----------------------------------------------------------------
SQL_c595019517ca2fba           WITH SUBQRY (REGIONID,TERRITORYID,LANGUAGEID,MEDIAID) AS (SELECT /*+ index(y X   SQL_PLAN_cb581knbwnbxu5537e8e9 SYS                            18-JUN-15 12.22.00.000000 PM                                                18-JUN-15 12.22.00.000000 PM                                                18-JUN-15 01.11.49.000000 PM                                                                                                                            YES YES         63
                               IE
SQL> set serveroutput on
SQL> declare
 2  ret_value pls_integer;
 3  sql_handle_in varchar2(30);
 4  cursor c1 is
 5  select sql_handle from dba_sql_plan_baselines
 6  where sql_text like 'WITH SUBQRY (REGIONID,TERRITORYID,LANGUAGEID,MEDIAID) AS %' ;
 7  begin
 8  open c1 ;
 9  loop
10  fetch c1 into sql_handle_in;
11  exit when c1%notfound;
12  ret_value := dbms_spm.drop_sql_plan_baseline(sql_handle=>sql_handle_in,plan_name=>NULL);
13  dbms_output.put_line(ret_value);
14  end loop;
15  end;
16  /
1

PL/SQL procedure successfully completed.

SQL> select SQL_HANDLE,SQL_TEXT,PLAN_NAME,CREATOR,CREATED,LAST_MODIFIED,LAST_EXECUTED,LAST_VERIFIED,ENABLED,ACCEPTED,OPTIMIZER_COST MODULE,ACTION from dba_sql_plan_baselines;

no rows selected

SQL> 

TNS:lost contact while login as sysdba

ORA-12547: TNS:lost contact

Identify the issue
truss -aefo sqlplus.trc sqlplus /nolog
connect / as sysdba

Cause I
shared memory or semaphores leftover from previous instances (abnormal termination)
ipcs -a
remove problematic shared memory segments & semaphores
ipcrm -m & ipcrm -s
delete $ORACLE_HOME/dbs/lk file


Cause II
The /proc file system is not mounted.

Cause III
ulimit settings for 'open files' and 'pipe size' is very low.

$ulimit -n 4096
$ulimit -p 30

Cause IV
The stack limit is set too low.

ulimit -s 32768

Cause V
Invalid LD_LIBRARY_PATH

Set proper LD_LIBRARY_PATH path

Cause VI
dbs folder missing from oracle home
mkdir dbs and then create password & init files

Cause VII
Invalid env variables

echo $ORACLE_HOME
echo $ORACLE_SID
echo $LD_LIBRARY_PATH
echo $PATH

Cause VIII
Wrong file permissions

cd $ORACLE_HOME/bin
ls -l oracle

chmod 6751 oracle

or relink all

Fixing the Mysql Replication when its broken:

Re-building the slave by taking the master dump
-----------------------------------------------

MASTER:
RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
mysqldump -uroot -p --events --ignore-table=mysql.events --all-databases > /var/backups/mysqldump.sql
UNLOCK TABLES;
Copy mysql dump file to server 2
(scp -p /var/backups/mysqldump.sql root@192.168.1.106:/tmp)

SLAVE:
STOP SLAVE;
mysql -uroot -p < /tmp/mysqldump.sql
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword';
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE;

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword';
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE IO_THREAD;
copy mysql dump from master
mysql -uroot -p < /tmp/mysqldump.sql
START SLAVE SQL_THREAD;
That should start the downloading of binary logs while you're doing the restore, so you don't miss out on any.


By restoring the binary Log
---------------------------

1) Take the Binary log necessary for the replication to work from "Show Slave Status"
2) Restore the binary logs from backup
MASTER:
a. Restore the logfiles in any location where you have space
b. Add the file names in mysql-bin.index for the master to know the binary log info
c. login to master database and see if binary logs are shown while you query "show binary logs", if you dont see the bin logs run "FLUSH LOGS" and now you should see the bin log files.
Once you see the binary log files in database
3) Stop / Reset the replication and start from the missing logfile
SLAVE:
stop slave;
reset slave;
show warnings;
change master to master_host ='10.99.3.21', master_user='replicator', master_password='pwd', master_log_file='mysql-bin.018736', master_log_pos=47904313;  -- These information can be retreived from slave server /mysqldata/mysql/mysql-master.info
start slave;
show slave status \G

How to change default tablespace for LogMiner tables in Oracle Streams

1) Create a new tablespace for Log miner

CREATE TABLESPACE LOGMNR_TBS DATAFILE ‘/oradata01/STREAMDB/logmnr_tbs01.dbf' size 100m;

2) Move the objects from SYSAUX (default tablespace) to new tablespace.

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNR_TBS');
END;
/

3) If there are active log miner session, then we'll get following error

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE(‘LOGMNR_TBS');
END;
ORA-01356: active logminer sessions found
ORA-06512: at “SYS.DBMS_LOGMNR_D”, line 233
ORA-06512: at line 2

This error suggests that, there are active logminer sessions in the database.

There are two types of log mining sessions

1) Persistent (CAPTURE process)
2) Non-persistent

We need to stop the LogMiner sessions to change the tablespace.

– Stop Non-persistent

BEGIN
DBMS_LOGMNR.END_LOGMNR();
END;
/
PL/SQL procedure successfully completed.

NOTE: If no logminer sessions are active, we'll get ORA-01307: no LogMiner session is currently active

– Stop Persistent

BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(CAPTURE_NAME=>'STREAM_CAPTURE'); END;
/
PL/SQL procedure successfully completed.

4) Then change the LogMiner tablespace

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNR_TBS');
END;
/
PL/SQL procedure successfully completed.

5) Confirm the objects are moved to new tablespace

SELECT * FROM DBA_TABLES WHERE TABLESPACE_NAME = 'LOGMNR_TBS';


6) Start capture process

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(CAPTURE_NAME=>'STREAM_CAPTURE');
END;
/
PL/SQL procedure successfully completed.