ORACLE数据库实操培训.docx

此文档收集于网络，如有侵权请联系网站删除1.使用tnsnames.ora文件配置本地服务名步骤：1) 切换到oracle用户 su - oracle2) 进入配置目录 cd $ORACLE_HOME/network/admin3) 配置本地服务名tnsnames.ora配置文件用于配置本地服务名，可以手工修改文件配置，也可以通过netca工具配置。打开tnsnames.ora文件：vi tnsnames.ora拷贝样例连接串：YXDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = james)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yxdb) ) )配置新的服务名连接串：NEW_YXDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = james)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = yxdb) ) )4）测试新的服务名连接串的可用性在oracle用户下，执行 tnsping new_yxdb如下表示正常状态：oraclejames admin$ tnsping new_yxdbTNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 18-MAY-2016 11:51:32Copyright (c) 1997, 2013, Oracle. All rights reserved.Used parameter files:/oracle/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = yxdb)OK (0 msec)注释：2.根据需求输出相应结果集（group by，多表链接等）例1：分组函数group by 计算按照工作职位分类最高平均工资和最低平均工资数。SQL SELECT MAX(AVG(sal),MIN(AVG(sal) FROM EMP GROUP BY JOB;例2：多表链接查询职员名称，组织编号，组织名称，公司位置。select e.ename,d.deptno,d.dname,d.loc from dept d,emp e where d.deptno = e.deptno;3.根据要求编写存储过程，函数，视图例1：编写存储过程：创建一个存储过程，查询员工姓名，员工岗位，雇佣日期和薪水。CREATE OR REPLACE PROCEDURE selectemp(employeeno IN INTEGER)IS employeename varchar2(20); employeejob varchar2(9); employeehiredate date; employeesal number(7,2);BEGIN select ename,job,hiredate,sal INTO employeename,employeejob,employeehiredate,employeesal FROM emp WHERE empno = employeeno;DBMS_OUTPUT.put_line (员工姓名 |employeename |员工岗位 |employeejob |雇佣日期 |employeehiredate |薪水 |employeesal);EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(ERRORS!);END;/例2：编写函数：创建一个函数，返回3.14*(f*f)的值。CREATE OR REPLACE FUNCTION area(f float)RETURN floatISBEGINRETURN 3.14*(f*f);END area;例3：编写视图：创建一个视图，可以查询员工的姓名，工作，雇佣日期，工资，组织名称。create view accounting_view asselect e.ename employee_name,e.job job,e.hiredate hiredate,e.sal salary,d.dname dep_namefrom dept d,emp ewhere e.deptno =d.deptno; 4.数据库存储管理操作步骤:假设存在表空间TEST，要求给该表空间添加数据文件。1）确认表空间TEST已存在的数据文件路径和大小SQL create tablespace test add datafile /oracle/app/oracle/oradata/yxdb/test01.dbf size 2M;SQL select file_name,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name=TEST;FILE_NAME TABLESPACE_NAME BYTES/1024/1024- - -/oracle/app/oracle/oradata/yxdb/test01.dbf TEST 22）按照需求添加数据文件，大小为2M，不开启自动扩展SQL alter tablespace test add datafile /oracle/app/oracle/oradata/yxdb/test02.dbf size 2M autoextend off;Tablespace altered5.数据库闪回操作步骤：假设数据表被误删除，利用闪回恢复特性恢复误删数据表。1）确认是否已经开启闪回SQL select flashback_on from v$database;FLASHBACK_ON-NO2) 开启闪回功能SQLshutdown immediate;SQLstartup mount;SQLalter database flashback on;SQLalter database open;SQL select flashback_on from v$database;3）确认闪回路径和空间大小NAME TYPE VALUE- - -db_recovery_file_dest string /oracle/app/oracle/fast_recovery_areadb_recovery_file_dest_size big integer 4182M4）模拟用户误删除数据库表，利用闪回特性恢复误删数据表，并重新命名SQL create user test identified by test default tablespace test;User created.SQL grant dba to test;Grant succeeded.SQL conn test/test;Connected.SQL create table test as select * from dba_objects where rownum drop table test;Table dropped.SQL select object_name,original_name,ts_name,createtime,droptime from recyclebin;OBJECT_NAME ORIGINAL_NAME TS_NAME CREATETIME DROPTIME- - - - -BIN$Mxf36aYJUX7gU2U4qMAgfA=$0 TEST TEST 2016-05-18:13:16:19 2016-05-18:13:17:05SQL select * from test;select * from test *ERROR at line 1:ORA-00942: table or view does not existSQL flashback table BIN$Mxf36aYJUX7gU2U4qMAgfA=$0 to before drop rename to test1;Flashback complete.SQL select count(*) from test1; COUNT(*)- 179996.Impdp/expdb导入导出数据操作假设需求是导出用户test的表t1，并导入到用户test1中。步骤：1） 创建模拟数据环境，并创建数据泵目录，以及授权。SQL create table test.t1 tablespace test as select * from dba_objects where rownum create user test1 identified by test1 default tablespace test;User created.SQL grant dba to test1;Grant succeeded.SQL !-创建dump目录oraclejames $ mkdir dumporaclejames $ pwd/home/oracleoraclejames $ cd dumporaclejames dump$ pwd/home/oracle/dumporaclejames dump$ exitexit-创建数据泵目录，并授权SQL create or replace directory dump as /home/oracle/dump;Directory created.SQL grant read,write on directory dump to public;Grant succeeded.2）导出用户test的数据表t1expdp / as sysdba directory=dump dumpfile=t1.dmp tables=test.t1 logfile=t1.log3）导入用户test的数据表t1到用户test2里impdp / as sysdba directory=dump dumpfile=t1.dmp remap_schema=test:test1 logfile=test1.log7.数据文件损坏的恢复操作（rman）假设数据文件损坏，需利用备份进行恢复。前提是使用RMAN进行全备份，确保备份集的完整。步骤：1） 全备数据库mkdir -p /home/oracle/backuprman target / catalog rman/rmancatalogrun backup as compressed backupset full databaseformat /home/oracle/backup/full_bk_%u%p%s.rmninclude current controlfile;backup as compressed backupset archivelog allformat /home/oracle/backup/arch_bk_%u%p%s.rmndelete all input;2) 模拟数据文件损坏oraclejames $ cd /oracle/app/oracle/oradata/yxdboraclejames yxdb$ lscontrol01.ctl redo01.log redo03.log system01.dbf test01.dbf test03.dbf undotbs01.dbfexample01.dbf redo02.log sysaux01.dbf temp01.dbf test02.dbf test04.dbf users01.dbforaclejames yxdb$ test01.dbf (直接清空数据文件)SQL shutdown immediateORA-01115: IO error reading block from file 6 (block # 1)ORA-01110: data file 6: /oracle/app/oracle/oradata/yxdb/test01.dbfORA-27072: File I/O errorAdditional information: 4Additional information: 13） 利用RMAN备份恢复数据库SQL shutdown abortORACLE instance shut down.SQL startup mountORACLE instance started.oraclejames backup$ rman target /RMAN restore database;RMAN recover database;SQL alter database open;8.重做日志文件损坏的恢复操作（rman）假设重做日志文件损坏，恢复重做日志需根据实际情况，采用非常规的方式进行修复。RMAN的备份集只用于恢复数据和归档。步骤：1) 全备数据库mkdir -p /home/oracle/backuprman target / catalog rman/rmancatalogrun backup as compressed backupset full databaseformat /home/oracle/backup/full_bk_%u%p%s.rmninclude current controlfile;backup as compressed backupset archivelog allformat /home/oracle/backup/arch_bk_%u%p%s.rmndelete all input;2) 模拟当前重做日志文件损坏SQL select group#,members,status from v$log; GROUP# MEMBERS STATUS- - - 1 1 INACTIVE 2 1 CURRENT 3 1 INACTIVEoraclejames $ cd /oracle/app/oracle/oradata/yxdboraclejames yxdb$ lscontrol01.ctl redo01.log redo03.log system01.dbf test01.dbf test03.dbf undotbs01.dbfexample01.dbf redo02.log sysaux01.dbf temp01.dbf test02.dbf test04.dbf users01.dbforaclejames yxdb$ echo redo02.log(直接清空日志文件)SQL startupORACLE instance started.Total System Global Area 313159680 bytesFixed Size 2252824 bytesVariable Size 171970536 bytesDatabase Buffers 134217728 bytesRedo Buffers 4718592 bytesDatabase mounted.ORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: /oracle/app/oracle/oradata/yxdb/redo02.logORA-27048: skgfifi: file header information is invalidAdditional information: 133） 恢复重做日志-设置隐含参数SQL alter system set _allow_resetlogs_corruption=true scope=spfile; System altered.SQL shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL startup ORACLE instance started.Total System Global Area 313159680 bytesFixed Size 2252824 bytesVariable Size 176164840 bytesDatabase Buffers 130023424 bytesRedo Buffers 4718592 bytesDatabase mounted.ORA-00313: open failed for members of log group 2 of thread 1ORA-00312: online log 2 thread 1: /oracle/app/oracle/oradata/yxdb/redo02.logORA-27048: skgfifi: file header information is invalidAdditional information: 13SQL show parameter resetNAME TYPE VALUE- - -_allow_resetlogs_corruption boolean TRUE-进行不完全恢复SQL recover database until cancel;ORA-00279: change 739425 generated at 05/20/2016 11:48:48 needed for thread 1ORA-00289: suggestion :/home/oracle/flash/YXDB/archivelog/2016_05_20/o1_mf_1_50_%u_.arcORA-00280: change 739425 for thread 1 is in sequence #50Specify log: =suggested | filename | AUTO | CANCELORA-00308: cannot open archived log/home/oracle/flash/YXDB/archivelog/2016_05_20/o1_mf_1_50_%u_.arcORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: /oracle/app/oracle/oradata/yxdb/system01.dbfSQL alter database open;alter database open*ERROR at line 1:ORA-01589: must use RESETLOGS or NORESETLOGS option for database openSQL alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00600: internal error code, arguments: 2662, 0, 739432, 0,740004, 4194432, , , , , , Process ID: 9525Session ID: 125 Serial number: 5SQL !oraclejames $ ps -ef | grep pmonoracle 2719 1 0 09:42 ? 00:00:00 ora_pmon_catalogoracle 9568 9554 0 12:03 pts/0 00:00:00 grep pmonoraclejames $ exitexitSQL exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsoraclejames $ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Fri May 20 12:04:05 2016Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to an idle instance.SQL startupORACLE instance started.Total System Global Area 313159680 bytesFixed Size 2252824 bytesVariable Size 176164840 bytesDatabase Buffers 130023424 bytesRedo Buffers 4718592 bytesDatabase mounted.Database opened.SQL select group#,members,status from v$log; GROUP# MEMBERS STATUS- - - 1 1 INACTIVE 2 1 CURRENT 3 1 UNUSEDSQL alter system switch logfile;System altered.SQL select group#,members,status from v$log; GROUP# MEMBERS STATUS- - - 1 1 INACTIVE 2 1 ACTIVE 3 1 CURRENTSQL show parameter resetNAME TYPE VALUE- - -_allow_resetlogs_corruption boolean TRUE-回退隐含参数SQL alter system reset _allow_resetlogs_corruption scope=spfile;System altered.SQL shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL startupORACLE instance started.Total System Global Area 313159680 bytesFixed Size 2252824 bytesVariable Size 176164840 bytesDatabase Buffers 130023424 bytesRedo Buffers 4718592 bytesDatabase mounted.Database opened.SQL show parameter reset9.恢复数据库到某一个时间点操作（rman） 第一次全备数据库，记录备份时间 创建新的数据文件 第二次全备数据库，记录备份时间 利用第一次的全备，恢复到该时间点的数据库步骤：1）第一次全备数据库，记录备份完成的时间点mkdir -p /home/oracle/backuprman target / catalog rman/rmancatalogrun backup as compressed backupset full databaseformat /home/oracle/backup/full_bk_%u%p%s.rmninclude current controlfile;backup as compressed backupset archivelog allformat /home/oracle/backup/arch_bk_%u%p%s.rmndelete all input;RMAN exitoraclejames backup$ date2016年 05月 19日 星期四 16:21:34 CST2）创建新的数据文件alter tablespace users add datafile /oracle/app/oracle/oradata/yxdb/users02.dbf size 2M autoextend off;3）第二次全备数据库，记录备份的时间点rman target / catalog rman/rmancatalogrun backup as compressed backupset full databaseformat /home/oracle/backup/full_bk_%u%p%s.rmninclude current controlfile;backup as compressed backupset archivelog allformat /home/oracle/backup/arch_bk_%u%p%s.rmndelete all input;RMAN exitRecovery Manager complete.oraclejames backup$ date2016年 05月 19日 星期四 16:25:25 CST4）检查备份集的完成时间点set linesize 250set pagesize 50col STATUS for a10col input for a10col output for a10select to_char(START_TIME,yyyy-mm-dd hh24:mi:ss) start_time,to_char(end_TIME,yyyy-mm-dd hh24:mi:ss) end_time,INPUT_TYPE,OUTPUT_BYTES/1024/1024/1024 output_GB,ELAPSED_SECONDS/60 min,status,INPUT_BYTES_PER_SEC_DISPLAY input,OUTPUT_BYTES_PER_SEC_DISPLAY outputfrom V$RMAN_BACKUP_JOB_DETAILSorder by start_time;START_TIME END_TIME INPUT_TYPE OUTPUT_GB MIN STATUS INPUT OUTPUT- - - - - - - -2016-05-19 16:20:31 2016-05-19 16:21:27 DB FULL .31223011 .933333333 COMPLETED 30.16M 5.71M2016-05-19 16:24