Oracle配置管理实验

 

 

一:服务器监听配置

1:启动与关闭监听进程

[oracle@oracle root]$ lsnrctl start

[oracle@oracle root]$ lsnrctl stop

 

2:增加新的监听器

方法一:

[root@oracle ~]# xhost +

[root@oracle ~]# su oracle

[oracle@oracle root]$ netmgr

 

弹出界面在这里添加监听

 Oracle配置管理实验

 

 

方法二:

查看listener.ora文件的内容

[root@oracle ~]# vi /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora  

 

# Generated by Oracle configuration tools.

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

 

增加新的监听器

[oracle@oracle root]$vi /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora

在末尾添加新的监听:

LISTENER1 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

           (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1522))

    )

  )

 

启动listener1的监听

[oracle@oracle root]$ lsnrctl start listener1

查看监听状态

[oracle@oracle root]$ lsnrctl status listener1

 

3:监听启动和数据库实例启动的顺序

先启动监听,再启动数据库

 

4查看全局数据库名

[oracle@oracle root]$ sqlplus sys/aptech AS SYSDBA

SQL> startup mount

SQL> alter database open;

SQL> select global_name from global_name;

 

5查询实例名SID

[oracle@oracle root]$ sqlplus sys/oracle AS SYSDBA

SQL> select instance_name from v$instance;

 

二:管理控制文件

系统默认有两个控制文件

 

1:获取控制文件信息

SQL> select name from v$controlfile;

SQL> select name,value from v$parameter where name=‘control_files‘;

 

2:查看控制文件的内容

SQL>select type,record_size,records_total,records_used from v$controlfile_record_section;

3:存储多重控制文件

SQL> shutdown immediate

SQL> exit

 

oracle身份拷贝,不要在root下拷贝

[oracle@oracle ~]$ cd /u01/app/oracle/oradata/orcl/

[oracle@oracle orcl]$ cp control01.ctl control03.ctl

 

[oracle@oracle orcl]$ sqlplus sys/aptech as sysdba

SQL> startup nomount

 

SQL> alter system set

control_files=‘/u01/app/oracle/oradata/orcl/control01.ctl‘,

‘/u01/app/oracle/oradata/orcl/control02.ctl‘,

‘/u01/app/oracle/oradata/orcl/control03.ctl‘

SCOPE=SPFILE;

 

SQL> shutdown immediate

SQL> startup

SQL> select name from v$controlfile;

 

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/oradata/orcl/control02.ctl

/u01/app/oracle/oradata/orcl/control03.ctl

 

 

4:备份和恢复控制文件

再打开一个终端root账户创建备份目录

[root@oracle ~]# mkdir /opt/backup

[root@oracle ~]# chmod 777 /opt/backup

回到原终端

SQL> alter database backup controlfile to ‘/opt/backup/control.bkp‘;

 

注释:如果要恢复控制文件,先关闭数据库实例,在用系统命令cpcontrol.bak文件覆盖损坏的控制文件,再启动数据库实例。

 

三:管理重做日志文件

系统默认有三个重做日志文件组,每个组一个重做日志文件

 

1:使用v$log查看重做日志文件信息

SQL> col status for a10;

SQL> select group#,sequence#,bytes,members,archived,status from v$log;

 

    GROUP#  SEQUENCE#    BYTES    MEMBERS ARCHIVED  STATUS

---------- ---------- ---------- ---------- --------- ----------

 1     1  209715200   1 NO       INACTIVE

 2     2  209715200   1 NO       INACTIVE

 3     3  209715200   1 NO       CURRENT

 

 

2使用v$logfile查看重做日志组信息

SQL> set line 120;

SQL> col member for a50;

SQL> select group#,status,type,member from v$logfile;

 

    GROUP# STATUS     TYPE     MEMBER

---------- ---------- --------------------- --------------------------------------------------

 3       ONLINE     /u01/app/oracle/oradata/orcl/redo03.log

 2       ONLINE     /u01/app/oracle/oradata/orcl/redo02.log

 1       ONLINE     /u01/app/oracle/oradata/orcl/redo01.log

 

 

3添加重做日志组,并向新的组中添加新的重做日志文件

root账户创建备份目录用于存放新的重做日志文件

[root@oracle ~]# mkdir -p /opt/log

[root@oracle ~]# chmod 777 /opt/log

 

[oracle@oracle orcl]$ sqlplus/aptech sys as sysdba

SQL> alter database add logfile group 4

(‘/u01/app/oracle/oradata/orcl/redo04.log‘,‘/opt/log/redo05.log‘) size 10m;

 

SQL> select group#,status,type,member from v$logfile;

 

5:向原有的日志组添加重做日志文件

SQL> alter database add logfile member

‘/opt/log/redo01b.log‘ to group 1,

‘/opt/log/redo02b.log‘ to group 2;

 

SQL> select group#,status,type,member from v$logfile;

 

6删除重做日志文件

SQL> alter database drop logfile member

  ‘/backup/orcl/log/redo02b.log‘;

 

7删除重做日志组

SQL> alter database drop logfile group 4;

 

8强制日志切换

SQL> select group#,sequence#,bytes,members,archived,status from v$log;

SQL> alter system switch logfile;

SQL> select group#,sequence#,bytes,members,archived,status from v$log;

观察当前使用的日志文件

 

9强制产生检查点事件

SQL> alter system checkpoint;     \\将修改过的数据保存到数据库中

 

四:管理归档日志文件

1:配置数据库归档日志

1):查询归档方式

SQL> archive log list;

数据库日志模式             非存档模式

自动存档             禁用

存档终点            /u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch

最早的联机日志序列     1

当前日志序列           3

 

2):关闭和启动数据库到mount状态

SQL> shutdown immediate;

SQL> startup mount

 

3):将数据库设置为归档模式

SQL> alter database archivelog;

 

SQL> archive log list;

数据库日志模式            存档模式

自动存档             启用

存档终点            /u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch

最早的联机日志序列     1

下一个存档日志序列   3

当前日志序列           3

 

SQL> alter database open        \\打开数据库

 

2:获取归档日志信息

如果没有日志内容会显示未选定行

SQL> select dest_id,dest_name,status,destination from v$archive_dest where status=‘valid‘;

SQL> select dest_id,name,archived from v$archived_log;

 

五:数据字典

查询当前表空间:
select username,default_tablespace from user_users;


查询所有表空间:
select tablespace_name from dba_tablespaces;


查询所有表空间大小:
select tablespace_name, sum(bytes)/1024 from dba_data_files group by tablespace_name;

 

1:静态数据字典视图

1):列举当前用户拥有的所有表的信息

SQL> select * from user_tables;

2):查询用户拥有哪些索引

SQL> select index_name from user_indexes;

3):查询用户拥有哪些视图

SQL> select view_name from user_views;

4):查询用户拥有哪些数据库对象

SQL> select object_name from user_objects;

5):查询当前用户的信息

SQL> select * from user_users;

6):查询当前用户所能访问的所有表,过程,函数等信息

SQL> select owner,object_name,object_type from all_objects;

 

7):查看数据字典

SQL> desc dictionary

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 TABLE_NAME                                         VARCHAR2(30)

 COMMENTS    

8):查看以USER开头的视图

 

SQL> select table_name from dictionary where table_name like ‘USER%‘;      \\user要大写

 

2:动态数据字典视图

1):查询和日志文件相关的信息

SQL> conn /as sysdba

Connected.

SQL> select * from v$fixed_table where name like ‘V$LOG%‘;

2):查看日志组状态信息

SQL> select group#,members,archived,status from v$log;

 

    GROUP#    MEMBERS ARC STATUS

---------- ---------- --- ----------------

         1          2 YES INACTIVE

         2          1 NO  CURRENT

         3          1 YES INACTIVE

 

3):查看重做日志文件

SQL> col member for a40

SQL> select * from v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER                                   IS_

---------- ------- ------- ---------------------------------------- ---

         3         ONLINE  /opt/oracle/oradata/orcl/redo03.log      NO

         2         ONLINE  /opt/oracle/oradata/orcl/redo02.log      NO

         1         ONLINE  /opt/oracle/oradata/orcl/redo01.log      NO

         1 INVALID ONLINE  /backup/orcl/log/redo01b.log             NO

4):查询当前正在重做日志文件的信息 L

SQL> select l.group#,l.archived,l.status,lf.type,lf.member from v$log l,v$logfile lf

  where l.group# = lf.group#

  and l.status = ‘CURRENT‘;

 

    GROUP# ARC STATUS           TYPE    MEMBER

---------- --- ---------------- ------- ----------------------------------------

         2 NO  CURRENT          ONLINE  /opt/oracle/oradata/orcl/redo02.log

 

5):通过v$instance视图查看实例信息

SQL> col instance_name for a20

SQL> col host_name for a10

SQL> select instance_name,host_name,version,startup_time,logins from v$instance;

 

INSTANCE_NAME        HOST_NAME  VERSION           STARTUP_T LOGINS

-------------------- ---------- ----------------- --------- ----------

orcl                 oracle.ben 11.2.0.1.0        05-JUL-15 ALLOWED

                     et.com

 

6):查看当前数据库的信息

SQL> col name for a10

SQL> select name,created,log_mode from v$database;

 

NAME       CREATED   LOG_MODE

---------- --------- ------------

ORCL       11-MAR-15 ARCHIVELOG

 

Oracle配置管理实验

上一篇:Qt数据库- 执行SQL语句


下一篇:MySQL数据库入门