oracle基本命令

1. Oracle创建删除导入导出命令行总结

说明：
在创建数据库时输入的密码，是修改系统默认的密码，以system和sysman等系统默认身份登录时要输入的密码就是修改后的密码（创建数据库时输入的密码）
如果要创建游或新的用户就必须以system或者sysman（这二者的权限最大）的身份登录后才可创建
创建用户格式：create user 用户名 identified by 密码（例如：create user cht identified by cht;）
创建完成后，必须分配权限，否则连不上数据库和sqlplus。
因此要做：grant connect,resource,dba to cht;这样数据库就可以通过cht/cht连上了。那些select，update，delete，insert的权限就不必分配了，因为每个用户默认都有这些基本权限。
命令行方式连接数据库的方法：
开始==》运行==》cmd
方式一：
输入sqlplus，回车
输入用户名： system，回车
输入密码： orcl，回车
方式二：
输入sqlplus system/orcl@orcl，回车（system是用户名，orcl是密码，@后面的orcl是库的名字）
（数据库安装完成后，有两个系统级的用户神激伍 :
1） system 默认密码为 :manager
2） sys 默认密码为 :change_on_install）
创建用户前必须要先建好临时表空间和数据表空间两个表空间，否则用系统默认的表空间不好。
//创建临时表空间
create temporary tablespace zfmi_temp tempfile 'D:oracleoradatazfmizfmi_temp.dbf' size 100m autoextend on next 32m maxsize 2048m extent management local;
说明：
1、zfmi_temp 表空间的名字
2、铅春D:oracleoradatazfmi 存放数据库文件的地方，一般是安装数据库后有控制文件，数据文件和日志文件的文件夹，再加上要创建表空间的名字+dbf（数据文件）
3、100M 表空间的初始大小
4、32M 表空间自动增长的大小
5、2048M 表空间最大的大小
//创建数据表空间
create tablespace zfmi logging datafile 'D:oracleoradatazfmizfmi.dbf' size 100m autoextend on next 32m maxsize 2048m extent management local;
//创建用户并指定表空间
create user zfmi identified by zfmi default tablespace zfmi temporary tablespace zfmi_temp;
//给用户授予权限
grant connect,resource dba to zfmi; （dba：指定所有权限）
到这一步新建一个用户的工作就完成了。
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//删除用户以及用户所有的对象
drop user zfmi cascade;
//cascade参数是级联删除该用户所有对象，经常遇到如用户有对象而未加此参数则用户删不了的问题，所以习惯性的加此参数
//删除表空间
前提：删除表空间之前要确认该表空间没有被其他用户使用之后再做删除
drop tablespace zfmi including contents and datafiles cascade onstraints;
//including contents 删除表空间中的内容，如果删除表空间之前表空间中有内容，而未加此参数，表空间删不掉，所以习惯性的加此参数
//including datafiles 删除表空间中的数据文件
//cascade constraints 同时删除tablespace中表的外键参照
如果删除表空间之前删除了表空间文件，解决办法：
如果在清除表空间之前，先删除了表空间对应的数据文件，会造成数据库无法正常启动和关闭。
可使用如下方法恢复（此方法已经在oracle9i中验证通过）：
下面的过程中，filename是已经被删除的数据文件，如果有多个，则需要多次执行；tablespace_name是相应的表空间的名称。
$ sqlplus /nolog
SQL conn / as sysdba;
如果数据库已经启动，则需要先执行下面这行：
SQL shutdown abort
SQL startup mount
SQL alter database datafile 'filename' offline drop;
SQL alter database open;
SQL drop tablespace tablespace_name including contents;
导入导出命令：
Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。exp命令可以把数据从远程数据库服务器导出到本地的dmp文件， imp命令可以把dmp文件从本地导入到远处的数据库服务器中。 利用这个功能可以构建两个相同的数据库，一个用来测试，一个用来正式使用。
下面介绍的是导入导出的实例。（注意：不用连接到SQL/plus，直接在DOS下就可以导出。）
数据导出：
1 将数据库TEST完全导出，用户名system 密码manager 导出到D:chu.dmp中
exp system/manager@TEST file=d:chu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:chu.dmp owner=（system,sys）
3 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:datanewsmgnt.dmp tables=（inner_notify,notify_staff_relat）
4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:chu.dmp tables=（table1） query=" where filed1 like ༼%'"
上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。
也可以在上面命令后面 加上 compress=y 来实现。
数据的导入
1 将D:chu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:chu.dmp
imp aichannel/aichannel@HUST full=y file=d:datanewsmgnt.dmp ignore=y
上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
在后面加上 ignore=y 就可以了。
2 将d:chu.dmp中的表table1 导入
imp system/manager@TEST file=d:chu.dmp tables=（table1）
基本上上面的导入导出够用了。不少情况要先是将表彻底删除，然后导入。
注意：
操作者要有足够的权限，权限不够它会提示。
数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上。
附录一：
给用户增加导入数据权限的操作
第一，启动sql*puls
第二，以system/manager登陆
第三，create user 用户名 IDENTIFIED BY 密码 （如果已经创建过用户，这步可以省略）
第四，GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO 用户名字
第五， 运行-cmd-进入dmp文件所在的目录，
imp userid=system/manager full=y file=*.dmp
或者 imp userid=system/manager full=y file=filename.dmp
执行示例：
F:WorkOracle_Databackupimp userid=test/test full=y file=inner_notify.dmp
屏幕显示
Import: Release 8.1.7.0.0 - Proction on 星期四 2月 16 16:50:05 2006
（c） Copyright 2000 Oracle Corporation. All rights reserved.
连接到： Oracle8i Enterprise Edition Release 8.1.7.0.0 - Proction
With the Partitioning option
JServer Release 8.1.7.0.0 - Proction
经由常规路径导出由EXPORT:V08.01.07创建的文件
已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入
导出服务器使用UTF8 NCHAR 字符集 （可能的ncharset转换）
. 正在将AICHANNEL的对象导入到 AICHANNEL
. . 正在导入表 "INNER_NOTIFY" 4行被导入
准备启用约束条件
成功终止导入，但出现警告。
附录二：
Oracle 不允许直接改变表的拥有者， 利用Export/Import可以达到这一目的。
先建立import9.par,
然后，使用时命令如下：imp parfile=/filepath/import9.par
例 import9.par 内容如下：
FROMUSER=TGPMS
TOUSER=TGPMS2 （注：把表的拥有者由FROMUSER改为TOUSER，FROMUSER和TOUSER的用户可以不同）
ROWS=Y
INDEXES=Y
GRANTS=Y
CONSTRAINTS=Y
BUFFER=409600
file==/backup/ctgpc_20030623.dmp
log==/backup/import_20030623.log

2. OracleRAC日常基本维护命令

所有实例和服务的状态

$ srvctl status database d orcl

Instance orcl is running on node linux

Instance orcl is running on node linux

单个实例的状态禅扒

$ srvctl status instance d orcl i orcl

Instance orcl is running on node linux

在数据库全局命名服务的状态

$ srvctl status service d orcl s orcltest

Service orcltest is running on instance(s) orcl orcl

特定节点上节点应用程序的状态

$ srvctl status nodeapps n linux

VIP is running on node: linux

GSD is running on node: linux

源蔽Listener is running on node: linux

ONS daemon is running on node: linux

ASM 实例的状态

$ srvctl status a *** n linux

ASM instance +ASM is running on node linux

列出配置的所有数据库

$ srvctl config database

orcl

显示 RAC 数据库的配置

$ srvctl config database d orcl

linux orcl /u /app/oracle/proct/ /db_

linux orcl /u /app/oracle/proct/ /db_

显示指定集群数据库的所有服务

$ srvctl config service d orcl

orcltest PREF: orcl orcl AVAIL:

显示节点应用程序的配置 —（VIP GSD ONS 监听器）

$ srvctl config nodeapps n linux a g s l

VIP exists : /linux vip/ / /eth :eth

GSD exists

雹袭州ONS daemon exists

Listener exists

显示 ASM 实例的配置

$ srvctl config a *** n linux

+ASM /u /app/oracle/proct/ /db_

集群中所有正在运行的实例

SELECT

inst_id

instance_number inst_no

instance_name inst_name

parallel

status

database_status db_status

active_state state

host_name host

FROM gv$instance

ORDER BY inst_id;

INST_ID INST_NO INST_NAME PAR STATUS DB_STATUS STATE HOST

orcl YES OPEN ACTIVE NORMAL rac

orcl YES OPEN ACTIVE NORMAL rac

位于磁盘组中的所有数据文件

select name from v$datafile

union

select member from v$logfile

union

select name from v$controlfile

union

select name from v$tempfile;

NAME

+FLASH_RECOVERY_AREA/orcl/controlfile/current

+FLASH_RECOVERY_AREA/orcl/onlinelog/group_

+FLASH_RECOVERY_AREA/orcl/onlinelog/group_

+FLASH_RECOVERY_AREA/orcl/onlinelog/group_

+FLASH_RECOVERY_AREA/orcl/onlinelog/group_

+ORCL_DATA /orcl/controlfile/current

+ORCL_DATA /orcl/datafile/example

+ORCL_DATA /orcl/datafile/indx

+ORCL_DATA /orcl/datafile/sysaux

+ORCL_DATA /orcl/datafile/system

+ORCL_DATA /orcl/datafile/undotbs

+ORCL_DATA /orcl/datafile/undotbs

+ORCL_DATA /orcl/datafile/undotbs

+ORCL_DATA /orcl/datafile/undotbs

+ORCL_DATA /orcl/datafile/users

+ORCL_DATA /orcl/datafile/users

+ORCL_DATA /orcl/onlinelog/group_

+ORCL_DATA /orcl/onlinelog/group_

+ORCL_DATA /orcl/onlinelog/group_

+ORCL_DATA /orcl/onlinelog/group_

+ORCL_DATA /orcl/tempfile/temp

rows selected

属于 ORCL_DATA 磁盘组的所有 ASM 磁盘

SELECT path

FROM v$a *** _disk

WHERE group_number IN (select group_number

from v$a *** _diskgroup

where name = ORCL_DATA );

PATH

ORCL:VOL

ORCL:VOL

启动/停止RAC集群

确保是以 oracle UNIX 用户登录的 我们将从rac 节点运行所有命令

# su – oracle

$ hostname

Rac

停止 Oracle RAC g 环境

第一步是停止 Oracle 实例 当此实例（和相关服务）关闭后 关闭 ASM 实例 最后 关闭节点应用程序（虚拟IP GSD TNS 监听器和 ONS）

$ export ORACLE_SID=orcl

$ emctl stop dbconsole

$ srvctl stop instance d orcl i orcl

$ srvctl stop a *** n rac

$ srvctl stop nodeapps –n rac

启动 Oracle RAC g 环境

第一步是启动节点应用程序（虚拟 IP GSD TNS 监听器和 ONS） 当成功启动节点应用程序后 启动 ASM 实例 最后 启动 Oracle 实例（和相关服务）以及企业管理器数据库控制台

$ export ORACLE_SID=orcl

$ srvctl start nodeapps n rac

$ srvctl start a *** n rac

$ srvctl start instance d orcl i orcl

$ emctl start dbconsole

使用 SRVCTL 启动/停止所有实例

启动/停止所有实例及其启用的服务 我只是觉得有意思就把此步骤作为关闭所有实例的一种方法加进来了！

$ srvctl start database d orcl

3. 干货-Oracle里的常用命令

本文转自：51Testing软件测试网。（http://www.51testing.com/html/27/n-7827.html）

      第一章：日志管理

1.forcing log switches

sql> alter system switch logfile;

2.forcing checkpoints

sql> alter system checkpoint;

3.adding online redo log groups

sql> alter database add logfile [group 4]

sql> ('/disk3/log4a.rdo','/disk4/log4b.rdo') size 1m;

4.adding online redo log members

sql> alter database add logfile member

sql> '/disk3/log1b.rdo' to group 1,

sql> '/disk4/log2b.rdo' to group 2;

5.changes the name of the online redo logfile

sql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log'

sql> to 'c:/oracle/oradata/redo01.log';

6.drop online redo log groups

sql> alter database drop logfile group 3;

7.drop online redo log members

sql> alter database drop logfile member 'c:/oracle/oradata/redo01.log';

8.clearing online redo log files

sql> alter database clear [unarchived] logfile 'c:/oracle/log2a.rdo';

9.using logminer analyzing redo logfiles

a. in the init.ora specify utl_file_dir = ' '

b. sql> execute dbms_logmnr_d.build('oradb.ora','c:\oracle\oradb\log');

c. sql> execute dbms_logmnr_add_logfile('c:\oracle\oradata\oradb\redo01.log',

sql> dbms_logmnr.new);

d. sql> execute dbms_logmnr.add_logfile('c:\oracle\oradata\oradb\redo02.log',

sql> dbms_logmnr.addfile);

e. sql> execute dbms_logmnr.start_logmnr(dictfilename=>'c:\oracle\oradb\log\oradb.ora');

f. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters

sql> v$logmnr_logs);

g. sql> execute dbms_logmnr.end_logmnr;

第二章：表空间管理

1.create tablespaces

sql> create tablespace tablespace_name datafile 'c:\oracle\oradata\file1.dbf' size 100m,

sql> 'c:\oracle\oradata\file2.dbf' size 100m minimum extent 550k [logging/nologging]

sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0)

sql> [online/offline] [permanent/temporary] [extent_management_clause]

2.locally managed tablespace

sql> create tablespace user_data datafile 'c:\oracle\oradata\user_data01.dbf'

sql> size 500m extent management local uniform size 10m;

3.temporary tablespace

sql> create temporary tablespace temp tempfile 'c:\oracle\oradata\temp01.dbf'

sql> size 500m extent management local uniform size 10m;

4.change the storage setting

sql> alter tablespace app_data minimum extent 2m;

sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999);

5.taking tablespace offline or online

sql> alter tablespace app_data offline;

sql> alter tablespace app_data online;

6.read_only tablespace

sql> alter tablespace app_data read only|write;

7.droping tablespace

sql> drop tablespace app_data including contents;

8.enableing automatic extension of data files

sql> alter tablespace app_data add datafile 'c:\oracle\oradata\app_data01.dbf' size 200m

sql> autoextend on next 10m maxsize 500m;

9.change the size fo data files manually

sql> alter database datafile 'c:\oracle\oradata\app_data.dbf' resize 200m;

10.Moving data files: alter tablespace

sql> alter tablespace app_data rename datafile 'c:\oracle\oradata\app_data.dbf'

sql> to 'c:\oracle\app_data.dbf';

11.moving data files:alter database

sql> alter database rename file 'c:\oracle\oradata\app_data.dbf'

sql> to 'c:\oracle\app_data.dbf';

第三章：表

1.create a table

sql> create table table_name (column datatype,column datatype]....)

sql> tablespace tablespace_name [pctfree integer] [pctused integer]

sql> [initrans integer] [maxtrans integer]

sql> storage(initial 200k next 200k pctincrease 0 maxextents 50)

sql> [logging|nologging] [cache|nocache]

2. an existing table

sql> create table table_name [logging|nologging] as subquery

3.create temporary table

sql> create global temporary table xay_temp as select * from xay;

on commit preserve rows/on commit delete rows

4.pctfree = (average row size - initial row size) *100 /average row size

pctused = 100-pctfree- (average row size*100/available data space)

5.change storage and block utilization parameter

sql> alter table table_name pctfree=30 pctused=50 storage(next 500k

sql> minextents 2 maxextents 100);

6.manually allocating extents

sql> alter table table_name allocate extent(size 500k datafile 'c:/oracle/data.dbf');

7.move tablespace

sql> alter table employee move tablespace users;

8.deallocate of unused space

sql> alter table table_name deallocate unused [keep integer]

9.truncate a table

sql> truncate table table_name;

10.drop a table

sql> drop table table_name [cascade constraints];

11.drop a column

sql> alter table table_name drop column comments cascade constraints checkpoint 1000;

alter table table_name drop columns continue;

12.mark a column as unused

sql> alter table table_name set unused column comments cascade constraints;

alter table table_name drop unused columns checkpoint 1000;

alter table orders drop columns continue checkpoint 1000

data_dictionary : dba_unused_col_tabs

第四章：索引

1.creating function-based indexes

sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped);

2.create a B-tree index

sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace

sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]

sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0

sql> maxextents 50);

3.pctfree(index)=(maximum number of rows-initial number of rows)*

100/maximum number of rows

4.creating reverse key indexes

sql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k

sql> next 200k pctincrease 0 maxextents 50) tablespace indx;

5.create bitmap index

sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k

sql> pctincrease 0 maxextents 50) tablespace indx;

6.change storage parameter of index

sql> alter index xay_id storage (next 400k maxextents 100);

7.allocating index space

sql> alter index xay_id allocate extent(size 200k datafile 'c:/oracle/index.dbf');

8.alter index xay_id deallocate unused;

第五章：约束

1.define constraints as immediate or deferred

sql> alter session set constraint[s] = immediate/deferred/default;

set constraint[s] constraint_name/all immediate/deferred;

2. sql> drop table table_name cascade constraints

sql> drop tablespace tablespace_name including contents cascade constraints

3. define constraints while create a table

sql> create table xay(id number(7) constraint xay_id primary key deferrable

sql> using index storage(initial 100k next 100k) tablespace indx);

primary key/unique/references table(column)/check

4.enable constraints

sql> alter table xay enable novalidate constraint xay_id;

5.enable constraints

sql> alter table xay enable validate constraint xay_id;

第六章：LOAD数据

1.loading data using direct_load insert

sql> insert /*+append */ into emp nologging

sql> select * from emp_old;

2.parallel direct-load insert

sql> alter session enable parallel dml;

sql> insert /*+parallel(emp,2) */ into emp nologging

sql> select * from emp_old;

3.using sql*loader

sql> sqlldr scott/tiger \

sql> control = ulcase6.ctl \

sql> log = ulcase6.log direct=true

第七章：reorganizing data

1.using expoty

$exp scott/tiger tables(dept,emp) file=c:\emp.dmp log=exp.log compress=n direct=y

2.using import

$imp scott/tiger tables(dept,emp) file=emp.dmp log=imp.log ignore=y

3.transporting a tablespace

sql>alter tablespace sales_ts read only;

$exp sys/.. file=xay.dmp transport_tablespace=y tablespace=sales_ts

triggers=n constraints=n

$ datafile

$imp sys/.. file=xay.dmp transport_tablespace=y datafiles=(/disk1/sles01.dbf,/disk2

/sles02.dbf)

sql> alter tablespace sales_ts read write;

4.checking transport set

sql> DBMS_tts.transport_set_check(ts_list =>'sales_ts' ..,incl_constraints=>true);

在表transport_set_violations 中查看

sql> dbms_tts.isselfcontained 为true 是， 表示自包含

第八章：managing password security and resources

1.controlling account lock and password

sql> alter user juncky identified by oracle account unlock;

2.user_provided password function

sql> function_name(userid in varchar2(30),password in varchar2(30),

old_password in varchar2(30)) return boolean

3.create a profile : password setting

sql> create profile grace_5 limit failed_login_attempts 3

sql> password_lock_time unlimited password_life_time 30

sql>password_reuse_time 30 password_verify_function verify_function

sql> password_grace_time 5;

4.altering a profile

sql> alter profile default failed_login_attempts 3

sql> password_life_time 60 password_grace_time 10;

5.drop a profile

sql> drop profile grace_5 [cascade];

6.create a profile : resource limit

sql> create profile developer_prof limit sessions_per_user 2

sql> cpu_per_session 10000 idle_time 60 connect_time 480;

7. view => resource_cost : alter resource cost

dba_Users,dba_profiles

8. enable resource limits

sql> alter system set resource_limit=true;

第九章：Managing users

1.create a user: database authentication

sql> create user juncky identified by oracle default tablespace users

sql> temporary tablespace temp quota 10m on data password expire

sql> [account lock|unlock] [profile profilename|default];

2.change user quota on tablespace

sql> alter user juncky quota 0 on users;

3.drop a user

sql> drop user juncky [cascade];

4. monitor user

view: dba_users , dba_ts_quotas

第十章：managing privileges

1.system privileges: view => system_privilege_map ,dba_sys_privs,session_privs

2.grant system privilege

sql> grant create session,create table to managers;

sql> grant create session to scott with admin option;

with admin option can grant or revoke privilege from any user or role;

3.sysdba and sysoper privileges:

sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile,

alter tablespace begin/end backup,recover database

alter database archivelog,restricted session

sysdba: sysoper privileges with admin option,create database,recover database until

4.password file members: view:=> v$pwfile_users

5.O7_dictionary_accessibility =true restriction access to view or tables in other schema

6.revoke system privilege

sql> revoke create table from karen;

sql> revoke create session from scott;

7.grant object privilege

sql> grant execute on dbms_pipe to public;

sql> grant update(first_name,salary) on employee to karen with grant option;

8.display object privilege : view => dba_tab_privs, dba_col_privs

9.revoke object privilege

sql> revoke execute on dbms_pipe from scott [cascade constraints];

10.audit record view :=> sys.aud$

11. protecting the audit trail

sql> audit delete on sys.aud$ by access;

12.statement auditing

sql> audit user;

13.privilege auditing

sql> audit select any table by summit by access;

14.schema object auditing

sql> audit lock on summit.employee by access whenever successful;

15.view audit option : view=> all_def_audit_opts,dba_stmt_audit_opts,

dba_priv_audit_opts,dba_obj_audit_opts

16.view audit result: view=> dba_audit_trail,dba_audit_exists,dba_audit_object,

dba_audit_session,dba_audit_statement

第十一章： manager role

1.create roles

sql> create role sales_clerk;

sql> create role hr_clerk identified by bonus;

sql> create role hr_manager identified externally;

2.modify role

sql> alter role sales_clerk identified by commission;

sql> alter role hr_clerk identified externally;

sql> alter role hr_manager not identified;

3.assigning roles

sql> grant sales_clerk to scott;

sql> grant hr_clerk to hr_manager;

sql> grant hr_manager to scott with admin option;

4.establish default role

sql> alter user scott default role hr_clerk,sales_clerk;

sql> alter user scott default role all;

sql> alter user scott default role all except hr_clerk;

sql> alter user scott default role none;

5.enable and disable roles

sql> set role hr_clerk;

sql> set role sales_clerk identified by commission;

sql> set role all except sales_clerk;

sql> set role none;

6.remove role from user

sql> revoke sales_clerk from scott;

sql> revoke hr_manager from public;

7.remove role

sql> drop role hr_manager;

8.display role information

view: =>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs,

role_sys_privs,role_tab_privs,session_roles

第十二章: BACKUP and RECOVERY

1. v$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v$sgastat

2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size

3. Monitoring Parallel Rollback

> v$fast_start_servers , v$fast_start_transactions

4.perform a closed database backup (noarchivelog)

> shutdown immediate

> cp files /backup/

> startup

5.restore to a different location

> connect system/manager as sysdba

> startup mount

> alter database rename file '/disk1/../user.dbf' to '/disk2/../user.dbf';

> alter database open;

6.recover syntax

--recover a mounted database

>recover database;

>recover datafile '/disk1/data/df2.dbf';

>alter database recover database;

--recover an opened database

>recover tablespace user_data;

>recover datafile 2;

>alter database recover datafile 2;

7.how to apply redo log files automatically

>set autorecovery on

>recover automatic datafile 4;

8.complete recovery:

--method 1(mounted databae)

> c:\backup\user.dbf c:\oradata\user.dbf

>startup mount

>recover datafile 'c:\oradata\user.dbf;

>alter database open;

--method 2(opened database,initially opened,not system or rollback datafile)

> c:\backup\user.dbf c:\oradata\user.dbf (alter tablespace offline)

>recover datafile 'c:\oradata\user.dbf' or

>recover tablespace user_data;

>alter database datafile 'c:\oradata\user.dbf' online or

>alter tablespace user_data online;

--method 3(opened database,initially closed not system or rollback datafile)

>startup mount

>alter database datafile 'c:\oradata\user.dbf' offline;

>alter database open

> c:\backup\user.dbf d:\oradata\user.dbf

>alter database rename file 'c:\oradata\user.dbf' to 'd:\oradata\user.dbf'

>recover datafile 'e:\oradata\user.dbf' or recover tablespace user_data;

>alter tablespace user_data online;

--method 4(loss of data file with no backup and have all archive log)

>alter tablespace user_data offline immediate;

>alter database create datafile 'd:\oradata\user.dbf' as 'c:\oradata\user.dbf''

>recover tablespace user_data;

>alter tablespace user_data online

5.perform an open database backup

> alter tablespace user_data begin backup;

> files /backup/

> alter database datafile '/c:/../data.dbf' end backup;

> alter system switch logfile;

6.backup a control file

> alter database backup controlfile to 'control1.bkp';

> alter database backup controlfile to trace;

7.recovery (noarchivelog mode)

> shutdown abort

> cp files

> startup

8.recovery of file in backup mode

>alter database datafile 2 end backup;

9.clearing redo log file

>alter database clear unarchived logfile group 1;

>alter database clear unarchived logfile group 1 unrecoverable datafile;

10.redo log recovery

>alter database add logfile group 3 'c:\oradata\redo03.log' size 1000k;

>alter database drop logfile group 1;

>alter database open;

or >cp c:\oradata\redo02.log' c:\oradata\redo01.log

>alter database clear logfile 'c:\oradata\log01.log';