Chapter 10: 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 Privile from any user or role
3.sdba 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_ACCESSIBILITILIBILITY = 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 Chapter 11: Manager Role
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 Externalness;
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.Stablish 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 Commsion;
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
Chapter 12: 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 automaticly
> 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, INIALLY 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 IMMEDITE;
> 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 Unrchived 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
"
