Oracle 常见管理

yao000

　　第七章:数据整理　　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
　　$copy datafile
　　$imp sys/.. file=xay.dmp transport_tablespace=y datafiles=(/disk1/sles01.dbf,/disk2
　　/sles02.dbf)

　　sql>>　　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 是， 表示自包含
　　第八章: 密码安全与资源管理
　　1.controlling account lock and password

　　sql>>　　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>>　　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>
　　7. view => resource_cost :>　　dba_Users,dba_profiles
　　8. enable resource limits

　　sql>>　　第九章:用户管理
　　1.create a user: database authentication

　　sql> create user juncky>　　sql> temporary tablespace temp quota 10m on data password expire
　　sql> [account lock|unlock] [profile profilename|default];
　　2.change user quota on tablespace

　　sql>>　　3.drop a user
　　sql> drop user juncky [cascade];
　　4. monitor user
　　view: dba_users , dba_ts_quotas
　　第十章:特权管理
　　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
　　第十一章: 规则管理器
　　1.create roles
　　sql> create role sales_clerk;

　　sql> create role hr_clerk>
　　sql> create role hr_manager>　　2.modify role

　　sql>>
　　sql>>
　　sql>>　　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>>
　　sql>>
　　sql>>
　　sql>>　　5.enable and disable roles
　　sql> set role hr_clerk;

　　sql> set role sales_clerk>　　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
　　第十二章: 备份与恢复
　　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

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

　　>>　　> copy files /backup/

　　>>
　　>>　　6.backup a control file

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