oracle管理在哪,Oracle管理

一、客户端连接服务器

1.查看服务器监听程序配置文件(先不用改动)

[root@oracle/]#su - oracle

[oracle@oracle~]$ cd $ORACLE_HOME/network/admin

[oracle@oracleadmin]$ vim listener.ora

2.查看服务器的实例名

SQL> select instance_name from v$instance;

INSTANCE_NAME

Orcl

3.设置客户端配置文件tnsnames.ora

ORCL = //ORCL即为连接标识符

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rhel1.benet.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = orcl)

)

4.连接实例

请输入用户名: sys/123456@orcl as sysdba

5.停止监听器

[oracle@rhel1 admin]$lsnrctl stop listener

6.再连接

请输入用户名: sys/123456@orcl as sysdba

ERROR:

ORA-12541: TNS: 无监听程序

7、再创建一个监听器，同时做静态注册，并进行连接

①.创建新的监听器

LISTENER1 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))

(ADDRESS = (PROTOCOL = TCP)(HOST = rhel1.benet.com)(PORT = 1522))

)

)

SID_LIST_LISTENER1 =

(SID_LIST =

(SID_DESC =

(SID_NAME = orcl)

(ORACLE_HOME = /opt/oracle/product/11.2/db_1)

(GLOBAL_DBNAME = orclabc)

)//所有()前面至少加一个空格

)

②.重新加载配置文件(可以省略)

[oracle@rhel1 admin]$lsnrctl reload

③.启动listener1监听器

The command completed successfully

[oracle@rhel1 admin]$lsnrctl start listener1

④.在客户机上修改tnsnames.ora配置文件

ORCL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rhel1.benet.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = orcl)

)

)

ORCL1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = rhel1.benet.com)(PORT = 1522))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = orcl)

)

)

⑤.在客户机上连接连接listener1监听器

请输入用户名: sys/123456@orcl1 as sysdba

连接到:

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

二•管理控制文件

1.获得控制文件信息

SQL> select name from v$controlfile;

NAME

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

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

2.获取控制文件中包含的内容

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

3.创建多路复用控制文件

①先创建存放控制文件的目录，并更改属主位oracle

[oracle@rhel1 ~]$su - root

[root@rhel1 ~]#mkdir -p /backup1/control

[root@rhel1 ~]#mkdir -p /backup2/control

[root@rhel1 ~]#chown -R oracle /backup1

[root@rhel1 ~]#chown -R oracle /backup2

②在数据库仍然打开时，修改spfile中的contro_files参数

[root@rhel1 ~]#su - oracle

[oracle@rhel1 ~]$sqlplus sys/123456 as sysdba

SQL> alter system set

2 control_files=

3 '/opt/oracle/oradata/orcl/control01.ctl',

4 '/backup1/control/control02.ctl',

5 '/backup2/control/control03.ctl' scope=spfile;

③关闭数据库

SQL> shutdown immediate;

④使用操作系统命令将文件复制到新的位置

SQL> quit;

[oracle@rhel1 ~]$ cp /opt/oracle/oradata/orcl/control01.ctl /backup1/control/control02.ctl

[oracle@rhel1 ~]$ cp /opt/oracle/oradata/orcl/control01.ctl /backup2/control/control03.ctl

⑤重新启动数据库

[oracle@rhel1 ~]$sqlplus sys/123456 as sysdba

SQL>startup

4.备份与恢复控制文件

方法一：直接用现有的完好的控制文件覆盖损坏或丢失的控制文件

①模拟故障，停止数据库，删除控制文件

sql>shutdown immediat

sql>quit

$rm -f /badkup1/control/control02.ctl

②启动数据库

sql>startup 观察现象

③恢复控制文件

sql>shutdown abort;

sql>quit

$ cp /opt/oracle/oradata/orcl/control01.ctl /backup1/control/control02.ctl

④再次启动数据库

sql>startup

方法二：利用专用的备份数据库语句

①创建备份

SQL> quit

[oracle@rhel1 ~]$su - root

[root@rhel1 ~]#mkdir /opt/oracle/oradata/orcl/backup

[root@rhel1 ~]#chown -R oracle /opt/oracle/oradata/orcl/backup/

SQL> alter database backup controlfile to '/opt/oracle/oradata/orcl/backup/control.bkp';

②模拟故障

SQL> shutdown immediate

SQL> quit

[oracle@rhel1 ~]$ rm backup1/control/control02.ctl

[oracle@rhel1 ~]$sqlplus sys/123456 as sysdba

SQL> startup

ORACLE instance started.

Total System Global Area 780824576 bytes

Fixed Size 2217424 bytes

Variable Size 490736176 bytes

Database Buffers 281018368 bytes

Redo Buffers 6852608 bytes

ORA-00205: error in identifying control file, check alert log for more info

③恢复控制文件

 使用os命令复制备份文件到原来的路径，为了保持一致，将没有丢失的控制文件也恢复一份

[oracle@rhel1 ~]$cp/opt/oracle/oradata/orcl/backup/control.bkp /opt/oracle/oradata/orcl/control01.ctl

[oracle@rhel1 orcl]$ cp /opt/oracle/oradata/orcl/backup/control.bkp /backup1/control/control02.ctl

[oracle@rhel1 orcl]$ cp /opt/oracle/oradata/orcl/backup/control.bkp /backup2/control/control03.ctl

[oracle@rhel1 orcl]$sqlplus sys/123456 as sysdba

SQL> alter database mount;

 查看当前活动的日志文件

SQL> startup mount;

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

GROUP# SEQUENCE# ARCHIV STATUS

1 4 NO INACTIVE

3 6 NO CURRENT

2 5 NO INACTIVE

 利用控制文件来恢复数据库

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

SQL> recover database using backup controlfile;

ORA-00279: change 1039911 generated at 05/30/2017 12:55:30 needed for thread 1

ORA-00289: suggestion :

/opt/oracle/flash_recovery_area/ORCL/archivelog/2017_05_30/o1_mf_16%u_.arc

ORA-00280: change 1039911 for thread 1 is in sequence #6 //根据提示在下面输入当前的日志文件

/opt/oracle/oradata/orcl/redo03.log

 打开数据库

SQL> alter database open resetlogs;

resetlogs选项的意思是要打开数据时，重置重做日志，即将重做日志的sequence置零

三.管理重做日志文件

使用v$log查看重做日志信息

[oracle@oracle~]$sqlplus / as sysdba

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

GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED

STATUS

1 4 209715200 1 NO

CURRENT

2 2 209715200 1 NO

INACTIVE

3 3 209715200 1 NO

INACTIVE

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

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

4.删除重做日志组4(只是删除了日志组，日志文件并没有删除)

①删除之前先查看下，然后再删除

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

GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED

STATUS

1 4 209715200 1 NO

CURRENT

2 2 209715200 1 NO

INACTIVE

3 3 209715200 1 NO

INACTIVE

GROUP# SEQUENCE# BYTES MEMBERS ARCHIVED

STATUS

4 0 10485760 2 YES

UNUSED

SQL> alter database drop logfile group 4;

数据库已更改。

说明：

① 当前的日志组不能删除，要删除当前日志组需要先对当前日志组进行切换，使用命令为alter system switch logfile

② 活动的日志组不可以删除

③ 没有归档的日志组不可以删除(前提是已经运行在归档模式)

5.添加/删除重做日志文件，分别向日志组1和2添加一个日志文件

①添加重做日志文件

SQL> alter database add logfile member

2 '/backup/orcl/log/redo01a.log' to group 1,

3 '/backup/orcl/log/redo02b.log' to group 2;

数据库已更改。

②删除日志文件

SQL> alter database drop logfile member

'/backup/orcl/log/redo02b.log';

数据库已更改。

③查看日志文件

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

GROUP# STATUS TYPE

MEMBER

1 INVALID ONLINE

/backup/orcl/log/redo01a.log

说明：

 不能删除当前组的成员，若要删除则先执行强制性切换重做日志的命令

 活动的日志成员不可以删除

 没有归档的日志文件不能删除(前提是已运行在归档模式下)

 当日志组只有一个成员

6.日志切换和检查点时间

①强制切换日志文件

SQL> alter system swith logfile;

②强制产生检查点事件

SQL> alter system checkpoint;

四•管理归档日志文件

配置数据库归档日志

① 查看数据库归档模式，确定当前不处于归档模式

SQL> archive log list;

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

自动存档 禁用

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

最早的联机日志序列 2

当前日志序列 4

② 关闭数据库并启动数据库到mount状态

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup mount;

ORACLE 例程已经启动。

Total System Global Area 1593835520 bytes

Fixed Size 8793256 bytes

Variable Size 1023411032 bytes

Database Buffers 553648128 bytes

Redo Buffers 7983104 bytes

数据库装载完毕。

③ 将数据库设置为归档模式并查看归档模式是否改变

SQL> alter database archivelog;

数据库已更改。

SQL> archive log list;

数据库日志模式 存档模式

自动存档 启用

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

最早的联机日志序列 2

下一个存档日志序列 4

当前日志序列 4

SQL> alter database open ;

数据库已更改。

④ 查看归档日志文件的路径

查看当前有效的归档日志文件存储目录

SQL> selectdest_id,dest_name,status,destination from v$archive_dest ;

DEST_ID

DEST_NAME

STATUS

DESTINATION

1

LOG_ARCHIVE_DEST_1

VALID

/u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch

DEST_ID

DEST_NAME

STATUS

DESTINATION

2

LOG_ARCHIVE_DEST_2

INACTIVE//省略部分信息

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

DEST_ID NAME ARCHIV

1/opt/oracle/flash_recovery_area/ORCL/archivelog/2016_11_19/o1_mf_1_8d2yh7kfx.arc YES

⑤ 改归档日志文件存放路径

[root@oracleserver~]#mkdir /aa

[root@oracleserver~]#chown -R oracle /aa

[root@oracleserver~]#su – oracle

[oracle@oracleserver~]$sqlplus / as sysdba

SQL> alter system set log_archive_dest='/aa' scope=spfile;

实验五：数据字典管理

1.创建一个班级表空间，空间大小为100m，数据文件放在/data目录下

[root@rhel1 ~]#mkdir /data

[root@rhel1 ~]#chown -R oracle /data

[root@rhel1 ~]#su - oracle

[oracle@rhel1 ~]$sqlplus / as sysdba

SQL> create tablespace t374

datafile '/data/t374.dbf' size 100m；

2.创建本人用户，默认表空间为班级表空间

SQL> create user name

identified by 123456

default tablespace t374;

授予其连接数据库和创建以及创建视图表的权限

SQL> grant connect,resource,create view to name;

切换称本人账户并创建表student,表里包含name和passwd字段

SQL>SQL> conn name;

SQL> create table student

(name varchar(10),

passwd varchar(20));

5.创建视图student_view

SQL> create view student_view

2 as

3 select * from student;

6.查询当前用户有哪些表

SQL> desc user_tables;

SQL>select * from user_tables;

SQL> select table_name fromuser_tables;

TABLE_NAME

STUDENT

7.查看当前用户有哪些视图

SQL> desc user_views;

SQL> select view_name fromuser_views;

VIEW_NAME

STUDENT_VIEW

8.查询当前用户有哪些数据库对象

SQL> select object_name from user_objects;

OBJECT_NAME

STUDENT_VIEW

STUDENT

9.查询当前用户的信息，包括用户id，用户状态，默认表空间

①查看表结构

SQL> desc user_users;

Name Null? Type

USERNAME NOT NULL VARCHAR2(30)

USER_ID NOT NULL NUMBER

ACCOUNT_STATUS NOT NULL VARCHAR2(32)

LOCK_DATE DATE

EXPIRY_DATE DATE

DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)

TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)

CREATED NOT NULL DATE

INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)

EXTERNAL_NAME

②格式化输出

SQL> col user_id for 999

SQL> col account_status for a10

SQL> col default_tablespace for a30

③查询

SQL> select user_id,account_status,default_tablespace from user_users;

USER_ID ACCOUNT_ST DEFAULT_TABLESPACE

91 OPEN T374

10.查询当前用户能访问的所有对象

SQL> select owner,object_name,object_type from all_objects;

11.查询所有的数据字典，并查询所有的以user开头的所有表

SQL> desc dictionary;

Name Null? Type

TABLE_NAME VARCHAR2(30)

COMMENTS VARCHAR2(4000)

SQL> select table_name from dictionary where table_name like 'USER%';

12.查看scott用户的表和表空间

SQL> col owner for a10

SQL> col table_namefor a20

SQL> col tablespace_name for a30

SQL> select owner,table_name,tablespace_name from dba_tables where owner='SCOTT';

OWNER TABLE_NAME TABLESPACE_NAME

SCOTT DEPT USERS

SCOTT EMP USERS

SCOTT BONUS USERS

SCOTT SALGRADE USERS

实验六：动态数据字典

1.查看和日志文件相关的信息(注意大写)

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

2.查看日志组状态信息

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

GROUP# MEMBERS ARCHIV STATUS

1 1 NO INACTIVE

2 1 NO INACTIVE

3 1 NO CURRENT

3.查看日志文件信息

SQL> col type for a10

SQL> col group# for 99

SQL> select * fromv$logfile;

GROUP# STATUS TYPE MEMBER IS_REC

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

4.查看当前正在使用的重做日志文件的信息

SQL> select l.group#,l.archived,l.status,lf.type,lf.member from v$log l, v$logfilelf where l.group#=lf.group#;

GROUP# ARCHIV STATUS TYPE

MEMBER

3 NO INACTIVE ONLINE

/opt/oracle/oradata/orcl/redo03.log

2 NO INACTIVE ONLINE

/opt/oracle/oradata/orcl/redo02.log

1 NO CURRENT ONLINE

/opt/oracle/oradata/orcl/redo01.log

5.查看实例信息

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_TIME

LOGINS

orcl rhel1.bene 11.2.0.1.0 30-MAY-17

t.com

ALLOWED

6.查看数据库信息

SQL> col name for a10;

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

NAME CREATED LOG_MODE

ORCL 30-MAY-17 NOARCHIVELOG