For Oracle Developers / DBA's who work on multiple Environments , here is a script for you to quickly check which environment you are working on , it's extremely important to be aware of the Environment you are working on in SQL*Plus
My script info.sql helps you achieve the same
https://github.com/abhilash-8/ora-tools/blob/master/info.sql
The script shows you the following
- Database Name and Database Unique you are working on
- Role ( Primary / Standby ) of Database you are working on
- Role of Database you are working on
- Current FRA Usage
- Current Sessions/Processes Connected
- Alert Log File Location
- Database Edition
- Database Version
The above should give you a quick summary of the database you are connected to help you work further. The script also sets some formatting for you if you are a hard core SQL*Plus user
# sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 30 06:00:11 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @info
Session altered.
06:00:16 SQL> select instance_name, status, host_name, version, startup_time from gv$instance;
INSTANCE_NAME |STATUS |HOST_NAME |VERSION |STARTUP_TIME
----------------|---------------|----------------------------------------------------------------|-----------------|-----------------------------
ORCL |OPEN |oralinux01 |12.2.0.1.0 |29-JUN-2020 08:03:07
06:00:16 SQL> select dbid, name, db_unique_name,flashback_on,force_logging,log_mode, open_mode, database_role, sysdate from gv$database;
DBID|NAME |DB_UNIQUE_NAME |FLASHBACK_ON |FORCE_LOGGING |LOG_MODE |OPEN_MODE |DATABASE_ROLE |SYSDATE
----------|---------|------------------------------|---------------|---------------|---------------|--------------------|--------------------|-----------------------------
1483155577|ORCL |ORCL |NO |NO |ARCHIVELOG |READ WRITE |PRIMARY |30-JUN-2020 06:00:16
06:00:16 SQL> show parameter control_management_pack_access
NAME |TYPE |VALUE
------------------------------------|--------------------------------------------|------------------------------
control_management_pack_access |string |DIAGNOSTIC+TUNING
06:00:16 SQL> show parameter diag
NAME |TYPE |VALUE
------------------------------------|--------------------------------------------|------------------------------
diagnostic_dest |string |/u01/app/oracle
06:00:16 SQL> show parameter background
NAME |TYPE |VALUE
------------------------------------|--------------------------------------------|------------------------------
background_core_dump |string |partial
background_dump_dest |string |/u01/app/oracle/product/12.2.0.1/d
| |b_1/rdbms/log
06:00:16 SQL> select * from v$flash_Recovery_area_usage;
FILE_TYPE |PERCENT_SPACE_USED|PERCENT_SPACE_RECLAIMABLE|NUMBER_OF_FILES| CON_ID
--------------------|------------------|-------------------------|---------------|----------
CONTROL FILE | 0| 0| 0| 0
REDO LOG | 0| 0| 0| 0
ARCHIVED LOG | 9.92| 0| 35| 0
BACKUP PIECE | .01| .01| 8| 0
IMAGE COPY | 0| 0| 0| 0
FLASHBACK LOG | 0| 0| 0| 0
FOREIGN ARCHIVED LOG| 0| 0| 0| 0
AUXILIARY DATAFILE C| 0| 0| 0| 0
OPY | | | |
8 rows selected.
06:00:16 SQL> show parameter reco
NAME |TYPE |VALUE
------------------------------------|--------------------------------------------|------------------------------
control_file_record_keep_time |integer |7
db_recovery_file_dest |string |+RECODG
db_recovery_file_dest_size |big integer |3T
db_unrecoverable_scn_tracking |boolean |TRUE
recovery_parallelism |integer |0
remote_recovery_file_dest |string |
06:00:16 SQL> set lines 200 pages 200
06:00:16 SQL> col db_link format a40
06:00:16 SQL> select inst_id,resource_name, current_utilization, max_utilization from gv$resource_limit where resource_name in ('processes','sessions');
INST_ID|RESOURCE_N|CURRENT_UTILIZATION|MAX_UTILIZATION
----------|----------|-------------------|---------------
1|processes | 240| 445
1|sessions | 208| 444
06:00:16 SQL> select vd.value||'/alert_'||vi.instance_name||'.log' "alert_log" from v$diag_info vd ,gv$instance vi where vd.name like 'Diag Trace';
alert_log
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ORCL/ORCL/trace/alert_ORCL.log
06:00:16 SQL> select * from v$version;
BANNER | CON_ID
--------------------------------------------------------------------------------|----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production | 0
PL/SQL Release 12.2.0.1.0 - Production | 0
CORE 12.2.0.1.0 Production | 0
TNS for Linux: Version 12.2.0.1.0 - Production | 0
NLSRTL Version 12.2.0.1.0 - Production | 0
06:00:16 SQL> set time off
SQL> set echo off
ORCL>
Top comments (0)