快速熟悉oracle新坏境

忘是亡心i 2022-07-20 15:29 199阅读 0赞

在接触到一个新的数据库环境的时候，可以通过以下的来快速的了解概况

1、先要了解当前的Oracle 数据库的版本和平台和相关信息

SQL> set linesize 300 pagesize 300

SQL> SELECT \* FROM v$version;

BANNER

\------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

了解数据库版本信息

SQL> SELECT \* FROM dba\_registry\_database;

PLATFORM\_ID

\-----------

PLATFORM\_NAME

EDITION

\------------------------------------------------------------------------------------------

Linux x86 64-bit

数据库所在服务器的版本信息

SQL> select INSTANCE\_NUMBER,INSTANCE\_NAME,VERSION,LOG\_SWITCH\_WAIT,ACTIVE\_STATE FROM v$instance;

INSTANCE\_NUMBER INSTANCE\_NAME                                    VERSION                                             LOG\_SWITCH\_WAIT                               ACTIVE\_STATE

\--------------- ------------------------------------------------ --------------------------------------------------- --------------------------------------------- ---------------------------

1 BDODS                                            11.2.0.4.0                                                                                        NORMAL

查看实例相关信息

SQL> SELECT dbms\_utility.port\_string FROM dual;

PORT\_STRING

x86\_64/Linux 2.4.xx

返回该数据库的 操作系统和版本

2、其次要了解你的数据库中装了哪些组件

SQL> col version for a30

SQL> select comp\_id,comp\_name,version,status from dba\_registry;

COMP\_ID    COMP\_NAME                                                                                            VERSION                        STATUS

\---------- ---------------------------------------------------------------------------------------------------- ------------------------------ --------------------

OWB        OWB                                                                                                  11.2.0.4.0                     VALID

APEX       Oracle Application Express                                                                           3.2.1.00.12                    VALID

EM         Oracle Enterprise Manager                                                                            11.2.0.4.0                     VALID

AMD        OLAP Catalog                                                                                         11.2.0.4.0                     VALID

SDO        Spatial                                                                                              11.2.0.4.0                     VALID

ORDIM      Oracle Multimedia                                                                                    11.2.0.4.0                     VALID

XDB        Oracle XML Database                                                                                  11.2.0.4.0                     VALID

CONTEXT    Oracle Text                                                                                          11.2.0.4.0                     VALID

EXF        Oracle Expression Filter                                                                             11.2.0.4.0                     VALID

RUL        Oracle Rules Manager                                                                                 11.2.0.4.0                     VALID

OWM        Oracle Workspace Manager                                                                             11.2.0.4.0                     VALID

CATALOG    Oracle Database Catalog Views                                                                        11.2.0.4.0                     VALID

CATPROC    Oracle Database Packages and Types                                                                   11.2.0.4.0                     VALID

JAVAVM     JServer JAVA Virtual Machine                                                                         11.2.0.4.0                     VALID

XML        Oracle XDK                                                                                           11.2.0.4.0                     VALID

CATJAVA    Oracle Database Java Packages                                                                        11.2.0.4.0                     VALID

APS        OLAP Analytic Workspace                                                                              11.2.0.4.0                     VALID

XOQ        Oracle OLAP API                                                                                      11.2.0.4.0                     VALID

18 rows selected.

3、搞清楚这个环境是单机还是集群？

这个判断方法很多，我这里给出一个借助dbms\_utility来判断的方法。

SQL> SET serveroutput ON

SQL> 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;

Not A Clustered Database

PL/SQL procedure successfully completed.

说明不是一个集群

4、是否配置了DataGuard?

SQL> col protection\_mode for a30

SQL> SELECT protection\_mode, protection\_level, remote\_archive,database\_role, dataguard\_broker,guard\_status FROM v$database;

PROTECTION\_MODE                PROTECTION\_LEVEL                                             REMOTE\_ARCHIVE           DATABASE\_ROLE                                    DATAGUARD\_BROKER         GUARD\_STATUS

\------------------------------ ------------------------------------------------------------ ------------------------ ------------------------------------------------ ------------------------ ---------------------

MAXIMUM PERFORMANCE            MAXIMUM PERFORMANCE                                          ENABLED                  PRIMARY                                          DISABLED                 NONE

SQL> show parameter log\_archive

NAME                                 TYPE                              VALUE

\------------------------------------ --------------------------------- ------------------------------

log\_archive\_config                   string                            DG\_CONFIG=( BDODS,BDODSDG)

log\_archive\_dest                     string

log\_archive\_dest\_1                   string                            LOCATION=/u01/app/oracle/orada

ta/BDODS/Archive VALID\_FOR=(AL

L\_LOGFILES,ALL\_ROLES) DB\_UNIQU

E\_NAME=BDODS

说明是dg

5、是否起用了归档模式？

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/oradata/BDODS/Archive

Oldest online log sequence     6617

Next log sequence to archive   6622

Current log sequence           6622

SQL> SELECT log\_mode FROM v$database;

LOG\_MODE

\------------------------------------

ARCHIVELOG

说明开启了归档模式

6、是否起用了flashback database特性？

SQL> SELECT flashback\_on FROM v$database;

FLASHBACK\_ON

\------------------------------------------------------

说明没有开启flashback database特性

7、是否起用了force logging和补充日志？

SQL> 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;

FORCE\_LOG SUPPLEMENTAL\_LOG\_DATA\_MI SUPPLEMEN SUPPLEMEN SUPPLEMEN SUPPLEMEN

\--------- ------------------------ --------- --------- --------- ---------

NO        NO                       NO        NO        NO        NO

说明没有开启，这个补充日志一般在ogg中使用

8、了解控制文件的组成

SQL> col NAME for a60

SQL> SELECT \* FROM v$controlfile;

STATUS               NAME                                                         IS\_RECOVE BLOCK\_SIZE FILE\_SIZE\_BLKS

\-------------------- ------------------------------------------------------------ --------- ---------- --------------

/u01/app/oracle/oradata/BDODS/control01.ctl                  NO             16384            964

/u01/app/oracle/fast\_recovery\_area/BDODS/control02.ctl       NO             16384            964

oracle规范上控制文件，在数据文件路径和fast\_recovery\_area路径各放一份

9、了解日志文件的组成，这里查询的是日志redo

SQL> 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、了解参数文件的组成和位置

SQL> SHOW parameter spfile

NAME                                 TYPE

\------------------------------------ ---------------------------------

VALUE

\------------------------------

spfile                               string

/u01/app/oracle/product/11.2.0

.4/db\_1/dbs/spfileBDODS.ora

若不存在的话就要创建spfile

CREATE spfile FROM pfile;

CREATE spfile FROM memory;

SQL> CREATE pfile FROM memory;

File created.

11、了解instance的相关信息

SQL> col instance\_name for a20

SQL> col HOST\_NAME for a20

SQL> col STATUS for a10

SQL> col INSTANCE\_ROLE for a20

SQL> col DATABASE\_STATUS for a20

SQL> SELECT instance\_name, host\_name, STATUS, archiver,database\_status, instance\_role, active\_state FROM v$instance;

INSTANCE\_NAME        HOST\_NAME            STATUS     ARCHIVER

\-------------------- -------------------- ---------- ---------------------

DATABASE\_STATUS      INSTANCE\_ROLE        ACTIVE\_STATE

\-------------------- -------------------- ---------------------------

BDODS                bd-prd-oracle-151    OPEN       STARTED

ACTIVE               PRIMARY\_INSTANCE     NORMAL

12、用户和密码相关

是否使用了缺省密码？

是否使用了profile?

是否起用了密码验证函数？

用户身份验证的方法？

密码是否区分大小写等。

SQL> SELECT name, value FROM gv$parameter WHERE name = 'resource\_limit';

NAME

\--------------------------------------------------------------------------------

VALUE

\--------------------------------------------------------------------------------

resource\_limit

FALSE

说明数据库没有资源限制

SQL> col profile for a20

SQL> col resource\_name for a20

SQL> col LIMIT for a20

SQL> SELECT profile, resource\_name, LIMIT FROM dba\_profiles ORDER BY 1,2;

PROFILE              RESOURCE\_NAME        LIMIT

\-------------------- -------------------- --------------------

DEFAULT              COMPOSITE\_LIMIT      UNLIMITED

DEFAULT              CONNECT\_TIME         UNLIMITED

DEFAULT              CPU\_PER\_CALL         UNLIMITED

DEFAULT              CPU\_PER\_SESSION      UNLIMITED

DEFAULT              FAILED\_LOGIN\_ATTEMPT 10

DEFAULT              IDLE\_TIME            UNLIMITED

DEFAULT              LOGICAL\_READS\_PER\_CA UNLIMITED

PROFILE              RESOURCE\_NAME        LIMIT

\-------------------- -------------------- --------------------

DEFAULT              LOGICAL\_READS\_PER\_SE UNLIMITED

SSION

DEFAULT              PASSWORD\_GRACE\_TIME  7

DEFAULT              PASSWORD\_LIFE\_TIME   UNLIMITED

DEFAULT              PASSWORD\_LOCK\_TIME   1

DEFAULT              PASSWORD\_REUSE\_MAX   UNLIMITED

DEFAULT              PASSWORD\_REUSE\_TIME  UNLIMITED

DEFAULT              PASSWORD\_VERIFY\_FUNC NULL

TION

PROFILE              RESOURCE\_NAME        LIMIT

\-------------------- -------------------- --------------------

DEFAULT              PRIVATE\_SGA          UNLIMITED

DEFAULT              SESSIONS\_PER\_USER    UNLIMITED

MONITORING\_PROFILE   COMPOSITE\_LIMIT      DEFAULT

MONITORING\_PROFILE   CONNECT\_TIME         DEFAULT

MONITORING\_PROFILE   CPU\_PER\_CALL         DEFAULT

MONITORING\_PROFILE   CPU\_PER\_SESSION      DEFAULT

MONITORING\_PROFILE   FAILED\_LOGIN\_ATTEMPT UNLIMITED

MONITORING\_PROFILE   IDLE\_TIME            DEFAULT

MONITORING\_PROFILE   LOGICAL\_READS\_PER\_CA DEFAULT

PROFILE              RESOURCE\_NAME        LIMIT

\-------------------- -------------------- --------------------

MONITORING\_PROFILE   LOGICAL\_READS\_PER\_SE DEFAULT

SSION

MONITORING\_PROFILE   PASSWORD\_GRACE\_TIME  DEFAULT

MONITORING\_PROFILE   PASSWORD\_LIFE\_TIME   DEFAULT

MONITORING\_PROFILE   PASSWORD\_LOCK\_TIME   DEFAULT

MONITORING\_PROFILE   PASSWORD\_REUSE\_MAX   DEFAULT

MONITORING\_PROFILE   PASSWORD\_REUSE\_TIME  DEFAULT

MONITORING\_PROFILE   PASSWORD\_VERIFY\_FUNC DEFAULT

PROFILE              RESOURCE\_NAME        LIMIT

\-------------------- -------------------- --------------------

TION

MONITORING\_PROFILE   PRIVATE\_SGA          DEFAULT

MONITORING\_PROFILE   SESSIONS\_PER\_USER    DEFAULT

32 rows selected.

查看oracle所有内存资源的限制情况

SQL> SELECT username, profile FROM dba\_users WHERE account\_status='OPEN' ORDER BY 1;

USERNAME                                                                                   PROFILE

\------------------------------------------------------------------------------------------ --------------------

BDUSER                                                                                     DEFAULT

DGMOR                                                                                      DEFAULT

MSS\_GUBA\_GUBA100                                                                           DEFAULT

MSS\_GUBA\_GUBA101                                                                           DEFAULT

MSS\_GUBA\_GUBA102                                                                           DEFAULT

MSS\_GUBA\_GUBA103                                                                           DEFAULT

MSS\_GUBA\_GUBA104                                                                           DEFAULT

MSS\_GUBA\_GUBA99                                                                            DEFAULT

MSS\_GUBA\_HKBA49                                                                            DEFAULT

MSS\_GUBA\_HKBA\_HZ49                                                                         DEFAULT

MSS\_GUBA\_UNBA49                                                                            DEFAULT

OGG                                                                                        DEFAULT

ORA\_MNZH\_MNCG                                                                              DEFAULT

READER                                                                                     DEFAULT

SCOTT                                                                                      DEFAULT

SYS                                                                                        DEFAULT

SYSTEM                                                                                     DEFAULT

17 rows selected.

查看所有用户资源的限制情况

SQL> 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;

USERNAME                                                                                   ACCOUNT\_STATUS

\------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------

SCOTT                                                                                      OPEN

查看普通用户状态，有木有被锁之类的

13、是否打开了BLOCK CHANGE TRACKING

SQL> col filename for a30

SQL> col STATUS for a30

SQL> col bytes for a30

SQL> SELECT filename, STATUS, bytes FROM v$block\_change\_tracking;

FILENAME                       STATUS                              BYTES

\------------------------------ ------------------------------ ----------

DISABLED

块修改跟踪(Block Change Tracking)，该特性主要服务于RMAN的增量备份，通过使用block change tracking功能，可以提高Oracle incremental备份进程的效率。

默认情况下，change tracking是关闭的，因为它会引起很小一部分的性能开销。