nomount mode
only parameter file is accessible.
this mode is only use to modify parameter file content/paramter.
mount mode
read control file but not validate its file content.
certain maintenance operation requires mount mode. ( RMAN backup restore recovery)
Note:- ******* its mandatory for "recover database" that db should be in mount mode*******
parameter file, processes gets started and memory allocated for the instance.
open mode
start to read data files and redo logs
orapwd FILE=path_to_password_file [ENTRIES=num_users] [FORCE={y|n}] [IGNORECASE={y|n}]
example:-
orapwd file=path_to_password_file entries=num_users force=y/n ignorecase=y/n
SQL> show parameter password ;
exclusive:- password file can only used by single database.
shared:- password file can be used by multiple database.
FILE: Specifies the path and filename for the password file. The password file should be created in the $ORACLE_HOME/dbs directory.
ENTRIES: (Optional) Specifies the number of entries (user accounts) that the password file can hold. The default value is 5.
FORCE: (Optional) If set to 'y', the existing password file will be overwritten. If set to 'n', and if the password file already exists, the orapwd command will not create a new file.
IGNORECASE: (Optional) If set to 'y', passwords are case-insensitive. If set to 'n', passwords are case-sensitive.
To Switch to "/bin" directory directly :-
cd $(dirname $(ps -ef | grep -i d.bin | grep -v grep | awk '{print $8}' | awk 'NR==1') )
--------------------------------------------------------------------------------------------------------------------
SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME, TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME, INST_ID, ROUND (VALUE * 10, 2) "Response_Time (msecs)" FROM GV$SYSMETRIC WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time' ORDER BY INST_ID; BEGIN_TIME END_TIME INST_ID Response_Time (msecs) ----------------- ----------------- ------- --------------------- 15-JUL-2023 01:05 15-JUL-2023 01:06 1 .44 15-JUL-2023 01:04 15-JUL-2023 01:05 1 .49 15-JUL-2023 01:03 15-JUL-2023 01:04 1 .59 15-JUL-2023 01:02 15-JUL-2023 01:03 1 .52 15-JUL-2023 01:01 15-JUL-2023 01:02 1 .45 15-JUL-2023 01:00 15-JUL-2023 01:01 1 1.07 15-JUL-2023 00:59 15-JUL-2023 01:00 1 .58
You can also query for the minimum, average and maximum response time for the last minute:
SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Min Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Avg Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Max Response Time (msecs)" FORMAT 999,999,999,999.99
SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
INST_ID,
ROUND (MINVAL * 10, 2) "Min Response Time (msecs)",
ROUND (AVERAGE * 10, 2) "Avg Response Time (msecs)",
ROUND (MAXVAL * 10, 2) "Max Response Time (msecs)"
FROM GV$SYSMETRIC_SUMMARY
WHERE 1 = 1
AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY INST_ID;BEGIN_TIME END_TIME INST_ID Min Response Time (msecs) Avg Response Time (msecs) Max Response Time (msecs) ----------------- ----------------- ------- ------------------------- ------------------------- ------------------------- 30-JUL-2023 20:12 30-JUL-2023 21:13 1 .00 17.65 40.38
SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Response Time (msecs)" FORMAT 999,999,999,999.99
SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
INSTANCE_NUMBER INST_ID,
ROUND (VALUE * 10, 2) "Response Time (msecs)"
FROM DBA_HIST_SYSMETRIC_HISTORY
WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY BEGIN_TIME DESC, INSTANCE_NUMBER;BEGIN_TIME END_TIME INST_ID Response Time (msecs) ----------------- ----------------- ------- ---------------------15-JUL-2023 01:03 15-JUL-2023 01:04 1 .59 15-JUL-2023 01:02 15-JUL-2023 01:03 1 .52 15-JUL-2023 01:01 15-JUL-2023 01:02 1 .45 15-JUL-2023 01:00 15-JUL-2023 01:01 1 1.07 15-JUL-2023 00:59 15-JUL-2023 01:00 1 .58
SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Response Time (msecs)" FORMAT 999,999,999,999.99
SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
INSTANCE_NUMBER INST_ID,
ROUND (VALUE * 10, 2) "Response Time (msecs)"
FROM DBA_HIST_SYSMETRIC_HISTORY
WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY BEGIN_TIME DESC, INSTANCE_NUMBER;SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Min Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Avg Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Max Response Time (msecs)" FORMAT 999,999,999,999.99
SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
INSTANCE_NUMBER INST_ID,
ROUND (MINVAL * 10, 2) "Min Response Time (msecs)",
ROUND (AVERAGE * 10, 2) "Avg Response Time (msecs)",
ROUND (MAXVAL * 10, 2) "Max Response Time (msecs)"
FROM DBA_HIST_SYSMETRIC_SUMMARY
WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY BEGIN_TIME DESC, INSTANCE_NUMBER;
cat /etc/oratab | grep -e "^[\a-Az-Z0-9\]\{0,8\}\:*/[[:graph:]]*"
Hints:-
Always use caret "^" and "$" without escape "\" like "^[[:digit:]]" or "$[[:digit:]]" .
To include forward search use "\+" or "*" like "^[[:graph:]]\+" or "^[[:digit:]]*" .
unzip -o /path/to/source_zip_file.zip -d /path/to/destination
-o :- source_directory
-d :- destination_directory
unzip
-o /u01/software/LINUX.X64_19300_GRID_HOME.zip
-d /u01/app/grid
Opatch :-
a java based utility enables the application and rollback patches oracle software.
location:- $ORACLE_HOME/OPatch/
----------------------------------------------------------------------------------------------------------------
Opatchauto :-
orchestration tool generates patching instruction specific to your target configuration then uses opatch to perform patching without user intervention.
location:- $GRID_HOME/OPatch/opatchauto
Stop DB
sudo -iu oracle
default :- srvctl stop database -d PUNEDB ---------> will stop database on all nodes
srvctl stop database -d PUNEDB -o normal
srvctl stop database -d PUNEDB -o immediate
srvctl stop database -d PUNEDB -o transactional
srvctl stop database -d PUNEDB -o abort
START DB
sudo -iu oracle
default :- srvctl start database -d PUNEDB ----------------> will start database on all nodes
srvctl start database -d PUNEDB -o nomount
srvctl start database -d PUNEDB -o mount --------> for standby db, backup task
srvctl start database -d PUNEDB -o open ---------> default behaviour
STOP / START INSTANCE
sudo -iu oracle
srvctl stop instance -d PUNEDB -i PUNE1
srvctl start instance -d PUNEDB -i PUNE2
To Stop CLUSTERWARE / CRS :-
sudo -iu root
ps -ef | grep -i d.bin
cd ~/bin
~/bin # ./crsctl disable crs
~/bin # ./crsctl stop crs -f
To Start CLUSTERWARE /CRS :-
sudo -iu root
ps -ef | grep -i d.bin
cd ~/bin
~/bin # ./crsctl enable crs
~/bin # ./crsctl start crs
Stop log apply service (MRP) on standby :-
alter database recover managed standby database cancel;
shutdown immediate;
stop log shipping from primary and shutdown primary database:-
Note:- Run this on primary--> to get log_archive_dest location for standby db's please use below query:-
then :-
show parameter log_archive_dest_state_&n; ------> n is number from above query
alter system set log_archive_dest_state_&n='DEFER';
shutdown immediate;
To start primary database & enable log shipping use below steps:-
steps to be performed on PRIMARY DB:-
startup;
alter system set log_archive_dest_state_&n='ENABLE';
steps to be performed on STANDBY DB:-
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
--------- to check status of mrp on standby and (lgwr or lns) on Primary -----
on standby:-
SQL > ! ps -ef | grep -e "pmon\|tnslsnr\|crs\|PARAM\|mrp" | grep -v grep
or
$ ps -ef | grep -e "pmon\|tnslsnr\|crs\|PARAM\|mrp" | grep -v grep
SQL>
-------------------------- Final all in one query for MRP DR ARCHIVE LOG GAP --------------
----------------------------------------------------------------------------------------------------
For Listener must start at reboot automatically:-