首页 > 数据库 > Oracle > ORACLE DBA常用命令集锦(2)

ORACLE DBA常用命令集锦(2)

原创 Oracle 作者:exitgogo 时间:2006-03-09 12:17:45 0 删除 编辑
ORACLE DBA常用命令集锦(2)[@more@]

接上篇 ORACLE DBA常用命令集锦(1):

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,


  第十一章: 规则管理器

  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,


  第十二章: 备份与恢复

  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; to apply redo log files automatically

  >set autorecovery on

  >recover automatic datafile 4;

  8.complete recovery:

  --method 1(mounted databae)

  >copy c:backupuser.dbf c:oradatauser.dbf

  >startup mount

  >recover datafile 'c:oradatauser.dbf;

  >alter database open;

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

  >copy c:backupuser.dbf c:oradatauser.dbf (alter tablespace offline)

  >recover datafile 'c:oradatauser.dbf' or

  >recover tablespace user_data;

  >alter database datafile 'c:oradatauser.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:oradatauser.dbf' offline;

  >alter database open

  >copy c:backupuser.dbf d:oradatauser.dbf

  >alter database rename file 'c:oradatauser.dbf' to 'd:oradatauser.dbf'

  >recover datafile 'e:oradatauser.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:oradatauser.dbf' as 'c:oradatauser.dbf''

  >recover tablespace user_data;

  >alter tablespace user_data online

  5.perform an open database backup

  > alter tablespace user_data begin backup;

  > copy 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:oradataredo03.log' size 1000k;

  >alter database drop logfile group 1;

  >alter database open;

  or >cp c:oradataredo02.log' c:oradataredo01.log

  >alter database clear logfile 'c:oradatalog01.log';

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录


  • 博文量
  • 访问量