DBA Common SQL database basic information

The first part:

1. View the maximum number of Oracle connections

Sql>show parameter processes

#最大连接数

2. Modify the maximum number of connections

Sql>alter system set Processes=value Scope=spfile

Restart database

Sql>shutdown Force Sql>start Force

3. View the current number of connections

Sql>select * from V$session where username are not null

4. View the number of connections for different users

Sql>select Username,count (username) from v$session where username isn't null GROUP by username

#查看指定用户的连接数

5. View the number of active connections

Sql>select Count (*) from v$session where status= ' active '

#查看并发连接数

6. View the number of connections for the specified program

Sql>select Count (*) from v$session where program= ' jdbc thin client '

#查看jdbc连接oracle的数目

7. View the database Installation instance (DBA authority)

Sql>select * from V$instance

8. View the running instance name

Sql>show parameter instance_name

9. View the database name

Sql>show parameter db_name

10. View the database domain name

Sql>show parameter Db_domain

11. View the database service name

Sql>show parameter Service_names

12. View the global database name

Sql>show parameter Global

Part II:

1. First to understand the current version and platform of Oracle database and related information
This is very important, busy a half day also know your database is which version, run on what system, it is not very sad, so I personally think this is the first step need to understand. The following scripts can help you get the information you need.
SELECT * from V$version;
SELECT * from Dba_registry_database;
Select dbid, Name, Open_mode, Database_role, platform_name from V$instance;
Select dbms_utility.port_string from dual;

Set Serveroutput on
Declare
Ver VARCHAR2 (100);
Compat VARCHAR2 (100);
Begin
Dbms_utility.db_version (ver, compat);
Dbms_output.put_line (' Version: ' | | ver | | ' Compatible: ' | | COMPAT);
End
/
2. Second, to understand which components are loaded in your database
SELECT * from Dba_registry;
　
3, figure out whether this environment is a single machine or a cluster?
This is a lot of judgment, and I'm going to give you a way to judge with Dbms_utility.
Set Serveroutput on

Declare
Inst_tab dbms_utility.instance_table;
INST_CNT number;
Begin
If Dbms_utility.is_cluster_database Then
Dbms_utility.active_instances (Inst_tab, inst_cnt);
Dbms_output.put_line ('-' | | | inst_tab. First);
Dbms_output.put_line (To_char (inst_cnt));
Else
Dbms_output.put_line (' Not A Clustered Database ');
End If;
End
/
4. are Dataguard configured?
Select Protection_mode, Protection_level, Remote_archive, Database_role, Dataguard_broker,guard_status
From V$database;
5. Do you have an archive mode?
Conn/as SYSDBA
Archive log list;
Select Log_mode from V$database;
　
6. Does the Flashback database feature be employed?
Select flashback_on from V$database;
If yes, further review the configuration of the FRA
7. Is there a force logging and supplemental journal?
Select Force_logging,supplemental_log_data_min, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI,
SUPPLEMENTAL_LOG_DATA_FK, Supplemental_log_data_all
From V$database;
8, understand the composition of control files
SELECT * from V$controlfile;
9. Understand the composition of log files
Select l.group#, Lf.type, Lf.member, L.bytes, L.status log_status, Lf.status logfile_status
From V$log L, V$logfile LF
where l.group# = lf.group#
Order by 1, 3;
10. Understand the composition and location of parameter files
Show Parameter SPFile
Create SPFile from Pfile ...
Create Pfile from SPFile;
Create SPFile from memory;
Create Pfile from memory;
11. Learn about Instance
Select instance_name, host_name, status, Archiver, Database_status, Instance_role, active_state
From V$instance;
12. User and password related
Do you use the default password?
Is the profile used?
Do you have a password validation function?
How is user authentication?
Password is case sensitive, etc.
Select name, value from Gv$parameter where name = ' Resource_limit ';
Select Profile, resource_name, limit from Dba_profiles order by 1, 2;

Select Username, profile from dba_users where account_status = ' OPEN ' ORDER by 1;
　
Select D.username, U.account_status
From Dba_users_with_defpwd D, dba_users u
where d.username = u.username and account_status = ' OPEN '
Order by 2, 1;
　
13, whether to open the block change TRACKING
Select filename, status, bytes from v$block_change_tracking;
14. What are the features (Feature)?
Dbms_feature_usage_report
　
15. Planning of table space and data files
We're all familiar with this, so we don't write.
　
16. Character Set correlation
SELECT * from Database_properties;
　
17. Is there a invalid object in the system?
Select owner, Object_type, COUNT (*)
From Dba_objects
where status = ' INVALID '
Group by owner, Object_type;
18, the further
is ASM used?
What are the backup methods and policies of the current system?
What is the configuration of the network files?
　
19. Check the latest alert log for some useful information
20. Run several performance analysis reports to see how the system is operating recently.
21. Run an RDA report to collect the complete System Status report

DBA Common SQL database basic information