鲁达 实验一、客户端连接服务器
1.查看服务器监听程序配置文件（先不用改动）
[root@rhel1 ~]# su - oracle
[oracle@rhel1 ~]$ cd $ORACLE_HOME/network/admin
[oracle@rhel1 admin]$ vim li
2.查看服务器的实例名
SQL> select instance_name from v$instance;
INSTANCE_NAME
--------------------------------
orcl
3.设置客户端配置文件
ORCL = //ORCL即为连接标识符
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = r)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orcl)
)
两种的格式
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.6)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = 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 = r)(PORT = 1522))
)
)
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/produc)
(GLOBAL_DBNAME = orclabc)
)//所有()前面至少加一个空格
)
②.重新加载配置文件（可以省略）
[oracle@rhel1 admin]$ lsnrctl reload
③.启动listener1监听器
The command completed successfully
[oracle@rhel1 admin]$ lsnrctl start listener1
④.在客户机上修改配置文件
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = r)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orcl)
)
)
ORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = r)(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
实验二：管理控制文件
Sql>show parameter spfile;
1.获得控制文件信息
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl
/u01/app/oracle/flash_recovery_area/orcl
2.获取控制文件中包含的内容
SQL> select type,record_size,records_total,records_used from v$controlfile_record_section;
3.创建多路复用控制文件
②在数据库仍然打开时，修改spfile中的contro_files参数
[root@rhel1 ~]# su - oracle
[oracle@rhel1 ~]$ sqlplus sys/123456 as sysdba
SQL> alter system set
control_files=
'/u01/app/oracle/oradata/orcl',
'/u01/app/oracle/oradata/orcl',
'/u01/app/oracle/oradata/orcl; scope=spfile;
③关闭数据库
SQL> shutdown immediate;
④使用操作系统命令将文件复制到新的位置
SQL>quit; [oracle@rhel1~]$cp/u01/app/oracle/oradata/orcl/u01/app/oracle/oradata/orcl
⑤重新启动数据库
[oracle@rhel1 ~]$ sqlplus sys/123456 as sysdba
SQL>startup
4.备份与恢复控制文件
方法一：直接用现有的完好的控制文件覆盖损坏或丢失的控制文件
下面为：二进制文件的方法
①模拟故障，停止数据库，删除控制文件
sql>shutdown immediate
sql>quit
$rm -f /u01/app/oracle/oradata/orcl
②启动数据库
sql>startup 观察现象
③恢复控制文件
sql>shutdown abort;
sql>quit
$ cp /u01/app/oracle/oradata/orcl /u01/app/oracle/oradata/orcl
④再次启动数据库
sql>startup
方法二：利用专用的备份数据库语句
①创建备份
SQL> quit
[oracle@rhel1 ~]$ su - root
[root@rhel1 ~]# mkdir /u01/app/oracle/oradata/orcl/u01/app/oracle/oradata
[root@rhel1 ~]# chown -R oracle /u01/app/oracle/oradata/orcl/u01/app/oracle/oradata/
SQL> alter database backup controlfile to '/u01/app/oracle/oradata/orcl/u01/app/oracle/oradata;;
②模拟故障
SQL> shutdown immediate
SQL> quit
[oracle@rhel1 ~]$ rm backup1/control
[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
③恢复控制文件
n 使用os命令复制备份文件到原来的路径，为了保持一致，将没有丢失的控制文件也恢复一份
[oracle@rhel1 ~]$cp /u01/app/oracle/oradata/orcl/u01/app/oracle/oradata /u01/app/oracle/oradata/orcl
[oracle@rhel1 orcl]$ cp /u01/app/oracle/oradata/orcl/u01/app/oracle/oradata /u01/app/oracle/oradata1/control
[oracle@rhel1 orcl]$ cp /u01/app/oracle/oradata/orcl/u01/app/oracle/oradata /u01/app/oracle/oradata2/control
[oracle@rhel1 orcl]$ sqlplus sys/123456 as sysdba
SQL> alter database mount;
n 查看当前活动的日志文件
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
n 利用控制文件来恢复数据库
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 :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_05_30/o1_mf_1_6_%u_.arc
ORA-00280: change 1039911 for thread 1 is in sequence #6 //根据提示在下面输入当前的日志文件
/u01/app/oracle/oradata/orcl
n 打开数据库
SQL> alter database open resetlogs;
resetlogs选项的意思是要打开数据时，重置重做日志，即将重做日志的sequence置零
实验三：管理重做日志文件
1. 使用v$log查看重做日志信息
SQL> select group#,sequence#,bytes,members,archived,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS ARCHIV STATUS
1 7 52428800 1 NO CURRENT（正在使用的）
2 5 52428800 1 NO INACTIVE（不活动的）
3 6 52428800 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
2 ONLINE /u01/app/oracle/oradata/orcl
1 ONLINE /u01/app/oracle/oradata/orcl
3.创建重做日志组,组号为4，并在此组里面添加两个日志文件，每个10m
SQL> alter database add logfile group 4
('/u01/app/oracle/oradata/orcl;,
'/u01/app/oracle/oradata/orcl;) size 10m;
4.删除重做日志组4（只是删除了日志组，日志文件并没有删除）
①删除之前先查看下，然后再删除
SQL> select group#,sequence#,bytes,members,archived,status from v$log;
SQL> alter database drop logfile group 4;
说明：
① 当前的日志组不能删除，要删除当前日志组需要先对当前日志组进行切换，使用命令为alter system switch logfile
② 活动的日志组不可以删除
③ 没有归档的日志组不可以删除（前提是已经运行在归档模式）
5.添加/删除重做日志文件，分别向日志组1和2添加一个日志文件
①添加重做日志文件
SQL> alter database add logfile member
'/u01/app/oracle/oradata/orcl; to group 1,
'/u01/app/oracle/oradata/orcl; to group 2;
②删除日志文件
SQL> alter database drop logfile member
'/u01/app/oracle/oradata/orcl/log;;
③查看日志文件
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
3 ONLINE /u01/app/oracle/oradata/orcl
2 ONLINE /u01/app/oracle/oradata/orcl
1 ONLINE /u01/app/oracle/oradata/orcl
1 INVALID ONLINE /u01/app/oracle/oradata/orcl/log
说明：
n 不能删除当前组的成员，若要删除则先执行强制性切换重做日志的命令
n 活动的日志成员不可以删除
n 没有归档的日志文件不能删除（前提是已运行在归档模式下）
n 当日志组只有一个成员
6.日志切换和检查点时间
①强制切换日志文件
SQL> select group#,sequence#,bytes,members,archived,status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS ARCHIV STATUS
1 7 52428800 2 NO ACTIVE
2 8 52428800 1 NO CURRENT
3 6 52428800 1 NO INACTIVE
②强制产生检查点事件
SQL> alter system checkpoint;
实验四：管理归档日志文件
1. 配置数据库归档日志
① 查看数据库归档模式，确定当前不处于归档模式
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Current log sequence 8
② 关闭数据库并启动数据库到mount状态
SQL> shutdown immediate
SQL> startup mount;
③ 将数据库设置为归档模式并查看归档模式是否改变
SQL> alter database archivelog;
SQL> archive log list;
Database log mode Archive Mode
sql>alter database open;
④ 查看归档日志文件的路径
查看前强制日志切换：
SQL> alter system switch logfile;
SQL> select dest_id,name,archived from v$archived_log;
DEST_ID NAME ARCHIV
1 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2016_11_19 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; size 100m；
2.创建本人用户，默认表空间为班级表空间
SQL> create user name
identified by 123456
default tablespace t374;
3. 授予其连接数据库和创建以及创建视图表的权限
SQL> grant connect,resource,create view to name;
4. 切换称本人账户并创建表student,表里包含name和passwd字段
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 table_name from user_tables;
TABLE_NAME
------------------------------------------------------------
STUDENT
7.查看当前用户有哪些视图
SQL> desc user_views;
SQL> select view_name from user_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%';
实验六：动态数据字典
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 * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_REC
------ -------------- ---------- ---------------------------------------- ------
3 ONLINE /u01/app/oracle/oradata/orcl NO
2 ONLINE /u01/app/oracle/oradata/orcl NO
1 ONLINE /u01/app/oracle/oradata/orcl NO
4.查看当前正在使用的重做日志文件的信息
SQL> select l.group#,l.archived,l.status,lf.type,lf.member from v$log l, v$logfile lf where l.group#=lf.group#;
GROUP# ARCHIV STATUS TYPE
------ ------ -------------------------------- ----------
MEMBER
----------------------------------------
3 NO INACTIVE ONLINE
/u01/app/oracle/oradata/orcl
2 NO INACTIVE ONLINE
/u01/app/oracle/oradata/orcl
1 NO CURRENT ONLINE
/u01/app/oracle/oradata/orcl
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 r 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