Trusted by +2 million users,
1000+ happy students everyday
1000+ happy students everyday
Showing pages 1 to 4 of 11 pages
Week Four LabThis week we will be using the SPOCK database. I want you to be aware of the types ofcommands you can issue and work that you can do in a CONTAINER database versus a NON-CONTAINER database. This is one of the significant architectural changes Oracle has madefrom Oracle 11c to 12c.We you are setting up your environment make sure you set the ORACLE_SID correctly. If youset the environment incorrectly you will see that many of these commands will not workcorrectly.Section I SYSBACKUP PrivilegeThe SYSBACKUP is a new privilege introduced in the Oracle Database 12c release that allows auser to perform any backup or recovery operations. It is recommended that a new user is createdis created to complete backup and recovery tasks. At installation there is a sysbackup usercreated, which should remain locked.For this section we will create a new Oracle user backup in the CDB. What we are doing here isadhering to a security principle SEPARATION OF DUTIES. you can grant SYSBACKUPprivilege to a user allowed only to perform backup or recovery operation using the RMANcommand line, but without possibility to see or access the data of the database.The SYSBACKUP privilege allows for the following:1.ALTERSYSTEM2.AUDITANY3.SELECTANYTRANSACTION4.SELECTANYDICTIONARY5.RESUMABLE6.CREATEANYDIRECTORY7.UNLIMITEDTABLESPACE8.ALTERTABLESPACE9.ALTERSESSION10.ALTERDATABASE11.CREATEANYTABLE12.DROPTABLESPACE13.CREATEANYCLUSTERIn addition to the SYSBACKUP account there are two additional new accounts in Oracle 12c,the SYSDG and SYSKM accounts.Let’s take a look at these accounts.
Three new user SYSBACKUP, SYSDG and SYSKM are created in support of this, when thedatabase is created, with their account in the “EXPIRED & LOCKED” status. A equivalentadministrative privilege with the same name as the user is created as well.1.SQL>SELECT username , account_status FROM dba_users where USERNAME like'SYS%';New Administrative PrivilegesThese new accounts have been provisioned for use with the appropriate privileges.2.SQL>SELECT * FROM V$pwfile_users;SYSBACKUPwill be used to perform allbackup and recoveryrelated operations either viaRMAN or SQL*PLUS. Here you can find a complete list ofSYSBACKUP privilegesyou areassigned when logged in with the SYSBACKUP administrative privilege.SYSDGis in place to separate theData Guardrelated operations from other activities. Here youcan find a complete list ofSYSDG privilegesyou are assigned when logged in with the SYSDGadministrative privilege.SYSKMwill be responsible for allTDE(Transparent Data Encryption) and Data Vault relatedadministrative operations. Here you can find a complete list ofSYSKM privilegesyou areassigned when logged in with the SYSKM administrative privilege.None of these new database roles can be dropped. They have enough privileges that using themuser can connect to database even if it is closed. Also all these roles are incorporated into theOracle database Vault. Actions performed using these privilege can be auditedifAUDIT_SYS_OPERATIONS is set to true.Add New Privileges to Password FileWhen a user needs to connect to the database using the SYSBACKUP, SYSDG or SYSKMadminstrative privilege the user must me be added to the password file with the appropriate userprivilege flag. The option to include these new privileges has been added to the orapwd utility.
3. orapwd file='$ORACLE_HOME/dbs/orapwrmancat' force=y format=legacy sysbackup=ysysdg=y syskm=y delete=n ENTRIES=10 SYSBACKUP=yCurrent Schema and Session for SYSBACKUP, SYSDG and SYSKMWhen a user is connected using any of these admin privileges, the schema that they are assignedto is the SYS schema and the session name corresponds to the privilege name that they are using.3.SQL> conn sys as sysdba4.SQL> select sys_context('userenv', 'current_schema') current_schema,sys_context('userenv', 'session_user') session_user from dual;5.SQL> conn sysdg as sysdg;6.SQL> select sys_context('userenv', 'current_schema') current_schema,sys_context('userenv', 'session_user') session_user from dual;7.SQL> conn sysbackup as sysbackup;8.SQL> select sys_context('userenv', 'current_schema') current_schema,sys_context('userenv', 'session_user') session_user from dual;9.SQL> conn syskm as syskm;10. SQL> select sys_context('userenv', 'current_schema') current_schema,sys_context('userenv', 'session_user')session_user from dual;New Database Role OS GroupTo further ensure the separation of access to the new SYSBACKUP, SYSDG and SYSKMprivileges, Oracle recommends mapping them to the new OSBACKUPDBA, OSDGDBA andOSKMDBA operating system groups respectively.
SYSBACKUPBACKUPDBASYSDGDGDBASYSKMKMDBASummaryWith the introduction of the new Database Administration users and the scaled down privileges,implementing segregation of duties is indeed possible.Further by providing the flexibility to only assign the required DBA privilege and mapping it tothe specific OS role groups, accountability on the use of the specific role is made easier.$ orapwd file='$ORACLE_HOME/dbs/orapwrman' force=y format=legacy sysbackup=ysysdg=y syskm=y delete=n ENTRIES=10 SYSBACKUP=ySection IIRestore and RecoveryThe preservation of data is one of the most important tasks that a database administrator isresponsible for day-to-day. In todays computing environments company are very dependent upontheir data, and ensuring that it available and its integrity is preserved are integral to anenterprise’s vitality. In this section we will be looking at the different of restore scenarios, andhow they will drive decisions about recovery.For clarity throughout the remainder of this lab, let us define our terms. Restore is the process ofputting data back into place following a media failure. Media failure can include disk failure,server crash, etc., some type of event that interferes with the normal functioning of the database.Recovery on the other hand is the rolling forward of archived redo logs, which puts data backinto place since the last backup. Let’s keep in mind that the backup of a database is a “snapshot”in time, which does not include any changes that occur after the backup has been completed. Arecovery will put data back into place that has been committed since this backup. The DBA willneed to make decisions about the type of recovery that will be employed based the identifier thatwill be employed. For instance, a DBA can choose to do a point-in-time recovery if they knowthe exact moment which they recovery needs to roll forward to, one based on the SCN or untilsequence. This are all types of INCOMPLETE database recoveries. What we are trying to do inthese scenarios is to bring the bring the database back to a state of consistency.This work will be done in the CONTAINER database.1.The first restore will be a FULL RESTORE. This restore and recovery will be completein a situation in which has lost most or all of its data files.1.If the database is not mounted, then mount but do not open the datafiles.(5 points)2.