I have consolidated these useful commands under each of these Oracle utilities,
ADRCI Commands
ADRCI is the command line interface for diagnostic utility used for viewing diagnostics data like listener log , alert log ,incident and cor dump etc and creating incident packages. Below are the the list of useful commands.
1. Get current base location:( Also known as ADR_BASE)
adrci> show base
ADR base is "/u01/app/oracle/"
2. Set new ORACLE_BASE(ADR_BASE)
adrci> set base /u01/app/grid
3. List current ORACLE_HOME
adrci> show home
4. Set new ORACLE_HOME
adrci> set homepath /u02/app/oracle
5. View alert log
adrci> show alert
adrci> show alert -tail 100
6. Purge alerts and trace files
-- This will purge data older than 600 minutes.
adrci> purge -age 600 -type ALERT
adrci> purge -age 600 -type TRACE
adrci> purge -age 600 -type incident
adrci> purge -age 10080 -type cdump
7. Set control policy for auto purge of files
There are two types of policies,
LONGP_POLICY is used to purge below data . Default value is 365 days.
• ALERT • INCIDENT • SWEEP • STAGE • HM
SHORTP_POLICY is used to purge for below data Default value is 30 days.
• TRACE • CDUMP • UTSCDMP • IPS
— Get existing control policy
adrci> show control
Change default value of control policy details.
-- Set in hours.
adrci> set control (SHORTP_POLICY = 240)
adrci> set control (LONGP_POLICY = 600)
8 . Create incident package:
adrci> show incident
adrci>IPS CREATE PACKAGE INCIDENT
(or)
adrci> ips pack incident in /tmp
Generated package 9 in file /tmp/ORA1578_20230602113045_COM_1.zip, mode complete
We can create empty package and add required incident or problem or alert log files.
-- Create empty package
adrci>IPS CREATE PACKAGE
-- add the necessary incident files. ( package_number will be displayed in the above command)
adrci>IPS ADD INCIDENT incident_number PACKAGE 2
adrci>IPS ADD FILE /u01/app/oracle/alert_db.log PACKAGE 2
-- Now generate the package file.
adrci>IPS GENERATE PACKAGE 2 IN /home/dbaclass/housekeeping
9. Unpack a ips file
adrci> ips unpack file ORA_98928.zip into /tmp/housekeeping
10. Pack all incident files within a particular time frame
--Generates the package with the incidents occurred between the times '2023-05-01 12:00:00.00' and '2023-05-02 23:00:00.00'
ips pack time '2023-05-01 12:00:00.00' to '2023-05-02 23:00:00.00'
11. View package information
adrci> ips show package
adrci> ips show package 12 detail
12. Remove/delete package information:
-- Delete the complete package:
adrci> ips delete package 2
-- Remove incidents from the packages
adrci > ips remove incident 2 package 7
-- Remove the problem keys from packages
adrci > ips remove problem 4 package 8
TFACTL commands
1. Check tfactl status with version:
tfactl status
2. Check tfactl tool status:
tfactl toolstatus
3. Get config details:
tfactl print config
4. List of user having access to tfactl:
tfactl access lsusers
6. changing property of a user:
tfactl access promote -user oracle
5.Adding or removing users from access list of tfactl:
tfactl access add -user rpdtro
tfactl access remove -user rdptro
6. change port number for tfactl:
tfactl set port=5001
NOTE – make sure to restart the tfactl after port change.
7. Stop/ start tfactl:
tfactl stop
tfactl start
8. Enable /disable autostart of tfactl upon reboot:
tfactl disable
tfactl enable
9. Find tfactl version with simple command:
tfactl version
AHF VERSION: 20.2.0
10. Collect diagnostic report pass the time of incident in YYYY-MM-DD HH24:MI:SS)
tfactl diagcollect -all
11. Get notificationaddress email:
tfactl get notificationAddress
12. Change notification address email:
tfactl set notificationAddress=oracle:admin@dbaclass.com
11. Generate summary report :
tfactl summary
-- Genearate complete summary overview in html
tfactl summary -html
-- Generate patching summary:
tfactl summary -patch -html
-- Generate asm summary
tfactl summary -asm -html
12.view smtp details :
tfactl print smtp
13. Manage logs using tfactl managelogs:
tfactl managelogs -show usage
14. Purge old logs:
-- This is just a dry run:
tfactl managelogs -purge -older 5d -dryrun
-- This will actually delete the logs older than 5 days
tfactl managelogs -purge -older 5d
-- Delete only GI logs:
tfactl managelogs -purge -gi 5d
tfactl run managelogs -purge -older 5d -gi
-- Delete only database logs:
tfactl run managelogs -purge -older -5d -database
15. Get repository location and usage:
tfactl print repository
16. Get component details:
tfactl print components
17. Find diag collection details of tfactl:
tfactl print collections
18. Verify email/smtp configuration.
tfactl sendmail support@samiora.itgo.com
ASMCMD commands
This article contains the list of useful asmcmd commands which will come handy in your day to day operations.
1. List all diskgroups:
ASMCMD> lsdg
-- Include dismounted diskgroups:
ASMCMD> lsdg --discovery
-- List diskgroups across all nodes of cluster:
ASMCMD> lsdg -g --discovery
2. List asm disks:
-- List all asm disks
ASMCMD> lsdsk -k
-- List disks of a diskgroup(CDATA) with free and total MB
ASMCMD> lsdsk -k -G CDATA
-- List disks of a diskgroup(CDATA) with group and disk number
ASMCMD> lsdsk -p -G CDATA
-- List disks with disk creation date
ASMCMD> lsdsk -t -G CDATA
-- List candidiate disks only
ASMCMD> lsdsk --candidate -k
-- List member disks only
ASMCMD> lsdsk --candidate -p
3. Get attributes of ASM diskgroups:
-- List attribute of all diskgroups:
ASMCMD> lsattr -lm
-- List attribute of specific diskgroup(DMARCH)
ASMCMD> lsattr -lm -G DMARCH
Group_Name Name Value RO Sys
DMARCH access_control.enabled FALSE N Y
DMARCH access_control.umask 066 N Y
DMARCH au_size 1048576 Y Y
DMARCH cell.smart_scan_capable FALSE N N
-- List attributes with specific pattern
ASMCMD> lsattr -lm %au_size%
Group_Name Name Value RO Sys
CDATA au_size 1048576 Y Y
BDM au_size 1048576 Y Y
CRMG au_size 1048576 Y Y
PMARCH au_size 1048576 Y Y
BCMS au_size 1048576 Y Y
4. unmount diskgroup:
unmount command works only on the local node. So if you want to unmount the diskgroup from all nodes of cluster, then run this command from all the nodes.
-- unmount all diskgroups
ASMCMD> umount -a
--- unmount specific diskgroup(ARCH)
ASMCMD> umount ARCH
4. Mount diskgroup:
Mount command works only on the local node. So if you want to Mount the diskgroup from all nodes of cluster, then run this command from all the nodes.
-- mount all diskgroups on local node
ASMCMD> mount -a
--- mount a specific diskgroup on local node
ASMCMD> mount ARCH
5. Rebalance a diskgroup:
-- here asm_power_limit is 8 and diskgroup is ARCH
ASMCMD> rebal --power 8 ARCH
Rebal on progress.
-- Monitor progress
ASMCMD> lsop
Group_Name Pass State Power EST_WORK EST_RATE EST_TIME
ARCH COMPACT RUN 8 0 16831 0
ARCH REBALANCE DONE 8 0 0 0
6. Get password file of database
ASMCMD> pwget --dbuniquename DBACLASS
+CDATA/DBACLASS/PASSWORD/pwddbaclass.256.899912377
8 .Get password file of asm :
ASMCMD> pwget --asm
+MGMT/orapwASM
9. Get asm template info of a diskgroup:
ASMCMD> lstmpl -l -G ARCH
Group_Name Group_Num Name Stripe Sys Redund PriReg MirrReg
ARCH 1 ARCHIVELOG COARSE Y UNPROT COLD COLD
ARCH 1 ASMPARAMETERFILE COARSE Y UNPROT COLD COLD
ARCH 1 AUDIT_SPILLFILES COARSE Y UNPROT COLD COLD
ARCH 1 AUTOBACKUP COARSE Y UNPROT COLD COLD
ARCH 1 AUTOLOGIN_KEY_STORE COARSE Y UNPROT COLD COLD
ARCH 1 BACKUPSET COARSE Y UNPROT COLD COLD
10. Check whether flex asm is enabled or not
ASMCMD> showclustermode
ASM cluster : Flex mode disabled
11.Check cluster state:
ASMCMD> showclusterstate
Normal
12. View asm version:
ASMCMD> showversion
ASM version : 12.1.0.2.0
13. Get asm spfile location:
ASMCMD> spget
+MGMT/DBACLASS-cluster/ASMPARAMETERFILE/registry.253.899644763
14. Take backup of asm spfile:
-- copy backup of spfile to a specific location
ASMCMD> spbackup +MGMT/DBACLASS-cluster/ASMPARAMETERFILE/registry.253.899644763 /home/oracle/asmspfile.ora
15. Find clients connected to a diskgroup:
ASMCMD> lsct DMARCH
DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group
DBACLASS CONNECTED 12.1.0.2.0 12.1.0.2.0 DBACLASS1 DMARCH
16. Get asm diskstring
ASMCMD> dsget
parameter:ORCL:*
profile:ORCL:*
17. List asm users with password:
ASMCMD> lspwusr
Username sysdba sysoper sysasm
SYS TRUE TRUE TRUE
ASMSNMP TRUE FALSE FALSE
18. List open files of a diskgroup:
— Open files of a diskgroup ( ARCH)
ASMCMD>lsof -G ARCH
19 . List open files related to a database
-- Open files of a database( DBACLASS)
ASMCMD>lsof --dbname DBACLASS
20. Check filter driver is enabled or not:
ASMCMD> afd_state
ASMCMD-9526: The AFD state is 'NOT INSTALLED' and filtering is 'DEFAULT' on host 'b20e4bay01'
filter driver disks:
21. List filter driver disks(if enabled)
ASMCMD> afd_lsdsk
22. Get filter driver asm diskstring
ASMCMD> afd_dsget
AFD discovery string:
srvctl commands
SRVCTL is known as server control utility, which is used to add, remove,relocate and manage different crs services or components in RAC database.
1. STOP DATABASE :
SYNTAX – srvctl stop database -d db_name [-o stop_options] where stop_options is normal/immediate(default)/transactional/abort
e.g
srvctl stop database -d PRODB -o normal
srvctl stop database -d PRODB -o immediate
srvctl stop database -d PRODB -o transactional
srvctl stop database -d PRODB -o abort
2. START DATABASE SYNTAX – srvctl start database -d db_name [-o start_options] where start_option is nomount/mount/open(default)
e.g
srvctl start database -d PRODB -o nomount
srvctl start database -d PRODB -o mount
srvctl start database -d PRODB -o open
3. STOP AN INSTANCE
SYNTAX – srvctl stop instance -d db_unique_name [-i “instance_name_list”]} [-o stop_options] [-f] e.g
srvctl stop instance -d PRODB -i PRODB1
4. START AN INSTANCE
SYNTAX – srvctl start instance -d db_unique_name [-i “instance_name_list”} [-o start_options] e.g
srvctl start instance -d PRODB -i PRODB1
5. REMOVING DB FROM CRS: SYNTAX – srvctl remove database -d db_unique_name [-f] [-y] [-v] e.g
srvctl remove database -d PRODB -f -y
6. ADDING DB IN CRS : SYNTAX – srvctl add database -d db_unique_name -o ORACLE_HOME [-p spfile] e.g
srvctl add database -d PRODB -o /u01/app/oracle/product/12.1.0.2/dbhome_1 -p +DATA/PRODDB/parameterfile/spfilePRODB.ora
7. REMOVING AN INSTANCE FROM CRS: SYNTAX – srvctl remove instance -d DB_UNIQUE_NAME -i INSTANCE_NAME e.g
srvctl remove instance -d PRODB - I PRODB1
8.ADDING AN INSTANCE TO CRS: SYNTAX – srvctl add instance –d db_unique_name –i inst_name -n node_name e.g
srvctl add instance -d PRODB - i PRODB1 -n rachost1
9. Enable/disable auto restart of the instance
srvctl enable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME
srvctl disable instance -d DB_UNIQUE_NAME-i INSTANCE_NAME
10. Enable/disable auto restart of the database
srvctl enable database -d DB_UNIQUE_NAME
srvctl disable database -d DB_UNIQUE_NAME
11. ADDING A SERVICE:
SYNTAX – srvctl add servicec -d {DB_NAME} -s {SERVICE_NAME} -r {“preferred_list”} -a {“available_list”} [-P {BASIC | NONE | PRECONNECT}]
e.g
srvctl add service -d PREDB -s PRDB_SRV -r "PREDB1,PREDB2" -a "PREDB2" -P BASIC
12.REMOVING A SERVICE:
SYNTAX – srvctl remove service -d {DB_NAME} -s {SERVICE_NAME} e.g
srvctl remove service -d PREDB -s PRDB_SRV
13. START A SERVICE SYNTAX– srvctl start servicec -d {DB_NAME} -s {SERVICE_NAME} e.g
srvctl start service -d PREDB -s PRDB_SRV
14. STOP A SERVICE
SYNTAX– srvctl stop servicec -d {DB_NAME} -s {SERVICE_NAME} e.g
srvctl stop service -d PREDB -s PRDB_SRV
15. RELOCATE A SERVICE
SYNTAX – srvctl relocate service -d {database_name} -s {service_name} -i {old_inst_name} -r {new_inst_name}
EXAMPLE: (Relocating service PRDB_SRV from PREDB2 to PREDB1)
srvctl relocate service -d PREDB -s PRDB_SVC -i PREDB2 -t PREDB1
16. Check the status of service SYNTAX – srvctl status service -d {database_name} -s {service_name}
srvctl status service -d PREDB -s PRDB_SVC
17. Check the configuration of service SYNTAX – srvctl config service -d {database_name} -s {service_name}
srvctl config service -d PREDB -s PRDB_SVC
18. Check scan listener configuration
srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1522
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1522
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1522
Registration invited nodes:
Registration invited subnets:
SCAN Listener is enabled.
SCAN Listener is individually enabled on nodes:
SCAN Listener is individually disabled on nodes:
19. Modify scan_listener port:
srvctl modify scan_listener -p {new-SCAN-port}
srvctl modify scan_listener -p 1523
$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='*';
20. Manage MGMTDB in oracle 12c:
srvctl status mgmtdb
Database is enabled
Instance -MGMTDB is running on node node12-1
-- stop and start MGMT db.
srvctl stop mgmtdb
srvctl start mgmtdb
21. Enable trace for srvctl commands:
-- set this to enable trace at os
SRVM_TRACE=true
export SRVM_TRACE
-- run any srvctl command
srvctl status database -d ORACL
22. Set environment variables through srvctl.
-- setenv to set env variables.(ORCL is the db_unique_name)
srvctl setenv database -db ORCL -env "ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1"
srvctl setenv database -db ORCL -env "TNS_ADMIN=/oracle/app/oracle/product/12.1.0.2/dbhome_1/network/admin"
--getenv to view the env setting:
srvctl getenv database -db ORCL
ORCL:
ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1
TNS_ADMIN=/oracle/app/oracle/product/12.1.0.2/dbhome_1/network/admin
23. Check status and config of ASM instance:
srvctl config asm
ASM home:
Password file: +MGMT/orapwASM
ASM listener: LISTENER
srvctl status asm
ASM is running on ses11-4,ses11-5
24. Stop and start services running from ORACLE_HOME
srvctl stop home -oraclehome /oracle/product/12.1.0.2/dbhome_1 -statefile /home/oracle/state.txt -node dbhost-1
srvctl start home -oraclehome /oracle/product/12.1.0.2/dbhome_1 -statefile /home/oracle/state.txt -node dbhost-1
25. Create a TAF policy
srvctl add service -db ORCLDB -service TAF_ORCL -preferred ORCLDB1 -available ORCLDB2 -tafpolicy BASIC -failovertype SELECT srvctl start service -db
CRSCTL commands
CRSCTL Utility is used to managed oracle clusterware resources and components.
1. STOP & START CRS: ( run from root user)
$GRID_HOME/bin/crsctl stop crs
$GRID_HOME/bin/crsctl start crs
2. Enable/Disable auto restart of CRS.
$GRID_HOME/bin/crsctl disable crs
$GRID_HOME/bin/crsctl enable crs
3. Find the cluster name
$GRID_HOME/bin/cemutlo -n
or
$GRID_HOME/bin/olsnodes -c
4. Find grid version:
SYNATX – $GRID_HOME/bin/crsctl query crs softwareversion <hostname)
$GRID_HOME/bin/crsctl query crs softwareversion host-dbaclass1
5. check cluster component status
$GRID_HOME/bin/crsctl stat res -t
$GRID_HOME/bin/crsctl check crs
$GRID_HOME/bin/crsctl check cssd
$GRID_HOME/bin/crsctl check crsd
$GRID_HOME/bin/crsctl check evmd
6. Find voting disk location
$GRID_HOME/bin/crsctl query css votedisk
7. Find OCR location:
$GRID_HOME/bin/ocrcheck
8. Find cluster interconnect details
$GRID_HOME/bin/oifcfg getif
app-ipmp0 172.21.39.128 global public
loypredbib0 172.16.3.192 global cluster_interconnect
loypredbib1 172.16.4.0 global cluster_interconnect
select NAME,IP_ADDRESS from v$cluster_interconnects;
NAME IP_ADDRESS
--------------- ----------------
loypredbib0 172.16.3.193
loypredbib1 172.16.4.1
9. Check CRS status of local node
crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
10. Check status of all crs resourcs
$GRID_HOME/bin/crsctl stat res -t
$GRID_HOME/bin/crsctl stat res -t -init
10. Check active version of cluster
crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [12.1.0.2.0]
11. Stop and start high availability service ( HAS)
crsctl stop has
crsctl start has
12. Check CRS status of remote nodes
crsctl check cluster
crsctl check cluster -all
13. Disk timeout from node to voting disk(disktimeout)
crsctl get css disktimeout
CRS-4678: Successful get disktimeout 200 for Cluster Synchronization Services.
14. Network latency in the node interconnect (Misscount)
crsctl get css misscount
CRS-4678: Successful get misscount 30 for Cluster Synchronization Services.
15. Move voting disk to another diskgroup:
crsctl replace votedisk +OCRVD
Successful addition of voting disk 2e4ded6cee504fc8bf078b080fb7be6f.
Successful addition of voting disk 8e87826024e24fffbf5add65c011fc66.
Successful addition of voting disk e1ba56dedff84fa8bf5605e0302fc81e.
Successful deletion of voting disk 2b7ce864c44d4fecbf60885a188290af.
Successfully replaced voting disk group with +OCRVD.
CRS-4266: Voting file(s) successfully replaced
16. Add another voting disk:
crsctl add css votedisk
17. Delete voting disk:
crsctl delete css votedisk
18 . Get ocr disk backup details
ocrconfig -showbackup
19 . Check whether standard or flex ASM
crsctl get cluster mode status
Cluster is running in "standard" mode
20 . Check CRS configuation
crsctl config crs
21 . Find cluster configuration information:
$ crsctl get cluster configuration
Name : dbaclass-cluster
Configuration : Cluster
Class : Standalone Cluster
Type : flex
The cluster is not extended.
--------------------------------------------------------------------------------
MEMBER CLUSTER INFORMATION
Name Version GUID Deployed Deconfigured
,
21 . Find node roles in cluster
crsctl get node role status -all
Node 'dbhost1' active role is 'hub'
Node 'dbhost1' active role is 'hub'
22. crsctl has commands for standalone grid infrastrcuture
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
DGMGRL commands in oracle dataguard
This topic contains useful dgmgrl commands to manage the dataguard environments.
1. Setup DG broker in the standby setup.(Run on both primary and standby)
- For standalone db :
ALTER SYSTEM SET dg_broker_config_file1 = '\U01\oradata\dr1node.dat' scope=both sid='*';
ALTER SYSTEM SET dg_broker_config_file2 = '\U01\oradata\dr2node.dat' scope=both sid='*';
-- For oracle RAC/ASM file system;
ALTER SYSTEM SET dg_broker_config_file1 = '+DATA/broker/dr1node.dat' scope=both sid='*';
ALTER SYSTEM SET dg_broker_config_file2 = '+DATA/broker/dr2node.dat' scope=both sid='*';
ALTER SYSTEM SET DG_BROKER_START=TRUE scope=both sid='*';
2. Create configuration in dgbroker:
-- on primary
$dgmgl
DGMGRL> CONNECT sys/;
Connected.
-- create configuration with primary db_unique_name and its service name .
DGMGRL> CREATE CONFIGURATION 'PROD_DG' AS PRIMARY DATABASE IS 'PRIMDB' CONNECT IDENTIFIER IS PRIMDB;
Configuration "PRIMDB" created with primary database "PRIMDB"
--- Add standby in the configuration:
DGMGRL> ADD DATABASE 'STYDB' AS CONNECT IDENTIFIER IS STYDB MAINTAINED AS PHYSICAL;
Database "STYDB" added
3. Enable the configuration
DGMGRL> ENABLE CONFIGURATION;
Enabled.
At this stage our dg broker setup is completed.
4. View configuration of dgbroker:
DGMGRL> show configuration
DGMGRL> show configuration verbose
5. view database informations:
-- Here PRIMDB and STYDB are db_unique_name of primary and standby db
DGMGRL> show database 'PRIMDB'
DGMGRL > show database 'STYDB'
DGMGRL> show database verbose 'PRIMDB'
6. View statusreport of databases
-- Here PRIMDB and STYDB are db_unique_name of primary and standby db
show database PRIMDB statusreport
7. View database inconsistent properties
-- Here PRIMDB and STYDB are db_unique_name of primary and standby db
show database PRIMDB InconsistentProperties
show database PRIMDB InconsistentLogXptProps
show database STYDB InconsistentProperties
show database STYDB InconsistentLogXptProps
8. Check whether all logfiles are archived or not( on primary)
show database PRIMDB sendQentries
PRIMARY_SEND_QUEUE
STANDBY_NAME STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
CURRENT 1022762318 1 294 10/30/2019 11:09:26 12298130044308 274219
9. Check information of received log sequence(not applied) ( Run for standby)
DGMGRL>show database STYDB recvqentries
STANDBY_RECEIVE_QUEUE
STATUS RESETLOGS_ID THREAD LOG_SEQ TIME_GENERATED TIME_COMPLETED FIRST_CHANGE# NEXT_CHANGE# SIZE (KBs)
NOT_APPLIED 1022762318 1 293 10/30/2019 10:03:06 10/30/2019 11:09:26 12298109948824 12298130044308 3487164
10. Check database wait events:
DGMGRL>show database PRIMDB topwaitevents
11.Validate database information:
dgmgrl> validate database verbose 'PRIMDB'
dgmgrl> validate database 'PRIMDB'
dgmgrl> validate database 'STYDB'
12. Enable tracing for troubleshooting:
-- For standalone:
DGMGRL> edit configuration set property tracelevel=support;
DGMGRL> edit database PRIMDB set property LogArchiveTrace=8191;
DGMGRL> edit database STYDB set property LogArchiveTrace=8191;
-- For RAC:
DGMGRL> EDIT INSTANCE * ON DATABASE 'PRIMDB' SET PROPERTY LogArchiveTrace=8191;
13. Disable tracing:
DGMGRL> edit configuration reset property tracelevel ;
DGMGRL> edit database PRIMDB reset property logarchivetrace;
DGMGRL> edit database STYDB reset property logarchivetrace;
14. Switchover using dgmgrl:
DGMGRL> connect sys/oracle
Password:
Connected as sys.
DGMGRL> switchover to STYDB
Performing switchover NOW, please wait...
Operation requires a connection to instance "STYDB1" on database "STYDB"
Connecting to instance "STYDB1"...
Connected as SYSDBA.
New primary database "STYDB" is opening...
Oracle Clusterware is restarting database "PRIMDB" ...
Switchover succeeded, new primary is "STYDB"
15. Convert physical standby to snapshot standby
DGMGRL> convert database 'STYDB' to snapshot standby;
16. Convert snapshot to physical standby db
DGMGRL> CONVERT DATABASE 'STYDB' to PHYSICAL STANDBY;
Flashback related commands
Below are the collection of useful flashback related commands.
1. How to check whether flashback is enabled or not:
select flashback_on from v$database;
2. Enable flashback in database:
--- make sure database is in archivelog mode:
alter system set db_recovery_file_dest_size=10G scope=both;
alter system set db_recovery_file_dest='/dumparea/FRA/B2PMT3' scope=both;
alter database flashback on;
3. Disable flashback in database:
alter database flashback off;
4. Create flashback restore point :
create restore point FLASHBACK_PREP guarantee flashback database;
5. Find the list of restore points:
-- From SQL prompt:
SQL>Select * from v$restore_points:
-- From RMAN prompt:
RMAN>LIST RESTORE POINT ALL;
6. Drop restore point:
drop restore point FLASHBACK_PREP;
7. Flashback database to restore point:
--- Below are the steps for flashback database to a guaranteed restore point;
1. Get the restore point name:
SQL> select NAME,time from v$restore_point;
NAME TIME
-------------------------------- -----------------------------------------------
FLASHBACK_PREP 21-MAR-17 03.41.33.000000000 PM
2. Shutdown database and start db in Mount stage:
shutdown immediate;
startup mount;
3. flashback db to restore point:
flashback database to restore point FLASHBACK_PREP;
4. Open with resetlog:
alter database open resetlogs;
8. Flashback query as of timestamp:
SELECT * FROM DBACLASS.EMP AS OF TIMESTAMP
TO_TIMESTAMP('2017-01-07 10:00:00', 'YYYY-MM-DD HH:MI:SS');
SELECT * FROM DBACLASS.EMP AS OF TIMESTAMP SYSDATE -1/24;
9. Flashback database to particular SCN or timestamp:
shutdown immediate;
startup mount;
--FLASHBACK DATABASE TO SCN 202381; -- Use this for particular scn
--FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); - Use for flashback to last one hour
--FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2018-03-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');- to specific timestamp:
alter database open resetlogs;
10. Flashback a table from recyclebin:
-- First get whether the table name exists in recyclebin or not:
SELECT object_name, original_name, createtime FROM recyclebin where original_name='EMP';
-- restore the table as same name:
FLASHBACK TABLE int_admin_emp TO BEFORE DROP;
-- Restore that table to a new name:
FLASHBACK TABLE int_admin_emp TO BEFORE DROP
RENAME TO int2_admin_emp;
11. Get flashback are usage info:
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
12. How far can we flashback:
--How Far Back Can We Flashback To (Time)
select to_char(oldest_flashback_time,’dd-mon-yyyy hh24:mi:ss’) “Oldest Flashback Time”
from v$flashback_database_log;
--How Far Back Can We Flashback To (SCN)
col oldest_flashback_scn format 99999999999999999999999999
select oldest_flashback_scn from v$flashback_database_log;
