Oracle常用命令大全集（7）

第十一章： 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)

>copy 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)

>copy 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

>copy 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;

> 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:\oradata\redo03.log'size 1000k;

>alter database drop logfile group 1;

>alter database open;

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