lots of stuff n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Lots of Stuff PowerPoint Presentation
Download Presentation
Lots of Stuff

Loading in 2 Seconds...

play fullscreen
1 / 123

Lots of Stuff - PowerPoint PPT Presentation


  • 133 Views
  • Uploaded on

Lots of Stuff. Gordon D. Brown. Science & Technology Facilities Council. WLCG Service Reliability Workshop CERN 26-30 November 2007. Overview. Database Security Passwords Database Security Checklist Host Housekeeping Grid Control Backups Overview Interactive Any questions.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Lots of Stuff' - zenia


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
lots of stuff

Lots of Stuff

Gordon D. Brown

Science & Technology Facilities Council

WLCG Service Reliability Workshop

CERN 26-30 November 2007

slide2

Overview

  • Database Security
  • Passwords
  • Database Security Checklist
  • Host Housekeeping
  • Grid Control
  • Backups Overview
  • Interactive
  • Any questions

WLCG Service Reliability Workshop 2

slide3

Database Security

WLCG Service Reliability Workshop 3

database security
Database Security
  • Authenticating DBA logins on host
    • Operating system (o/s) authentication
    • A password file
  • For nonsecure remote connections
    • Must be authenticated by a password file
  • Connecting to database as a privileged user over a local connection or a secure remote connection in two ways
    • If have password file and have been granted the SYSDBA or SYSOPER system privilege
    • If no password file, then operating system authentication, username of the database administrator in a special group
      • OSDBA:. Users in that group are granted SYSDBA privileges
      • OSOPER: Users in that group are granted SYSOPER privileges

WLCG Service Reliability Workshop 4

database security1
Database Security
  • Operating System Authentication
    • OSDBA and OSOPER
    • The groups are created and assigned specific names as part of the database installation process.
    • If you are not a member of either of these operating system groups and you attempt to connect as SYSDBA or SYSOPER, the CONNECT command fails

WLCG Service Reliability Workshop 5

database security2
Database Security
  • Using Password File Authentication
    • To enable authentication of an administrative user using password file authentication you must do the following:
    • If not already created, create the password file using the ORAPWD utility:
      • ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users
  • For RAC the environment variable for each instance should point to the same password file
  • Protect password file!

WLCG Service Reliability Workshop 6

database security3
Database Security
  • Operating system authentication takes precedence over password file authentication.
  • Specifically, if you are a member of the OSDBA or OSOPER group for the operating system, and you connect as SYSDBA or SYSOPER, you will be connected with associated administrative privileges regardless of the username/password that you specify. i.e.

sqlplus / as sysdba

  • If you are not in the OSDBA or OSOPER groups, and you are not in the password file, then attempting to connect as SYSDBA or as SYSOPER fails.

WLCG Service Reliability Workshop 7

database security4
Database Security
  • Setting REMOTE_LOGIN_PASSWORDFILE
    • With password file, you must set the initialization parameter REMOTE_LOGIN_PASSWORDFILE. Values:
      • NONE: Database behaves as if password file does not exist.
        • No privileged connections are allowed over nonsecure connections.
      • EXCLUSIVE: (default) Used with only one instance of one database.
        • Only an EXCLUSIVE file can be modified
        • Enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.
      • SHARED: Used by multiple databases running on the same server, or multiple instances of a (RAC) database.
        • Password file cannot be modified.
        • This option is useful if you are administering multiple databases or a RAC database.

WLCG Service Reliability Workshop 8

slide9

Database Security

  • Login overview

WLCG Service Reliability Workshop 9

slide10

Database Security

  • Viewing Password File Members
    • V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER
      • USERNAME: User that is recognized by the password file
      • SYSDBA: If TRUE, then the user can log on with SYSDBA
      • SYSOPER: if TRUE, then the user can log on with SYSOPER

WLCG Service Reliability Workshop 10

slide11

Database Security

  • Ensure that internal networks are inside a firewall
  • Oracle Advanced Security
    • Add on
    • Enables data encryption and integrity checking, enhanced authentication, single sign-on, and support for DCE
    • Combines network encryption, database encryption and strong authentication together to help customers address privacy and compliance requirements:

WLCG Service Reliability Workshop 11

slide12

Database Security

  • Transparent Data Encryption
    • Protection of stored data by transparently encrypting data (using 3DES or AES with up to 256 bits) at the column or tablespace level.
  • Network encryption and data integrity
    • Supports:
      • RC4 (40, 56, 128, and 256 bits)
      • DES (40 and 56 bits)
      • 3DES (2 and 3 keys)
      • AES (128, 192, and 256 bits)

WLCG Service Reliability Workshop 12

slide13

Database Security

  • Strong authentication
    • Two-factor (or "strong") authentication is based on something the user has (a smart card, token, etc.) and a PIN or passcode. Supports:
      • Kerberos
      • RADIUS (Remote Authentication Dial-In User Service)
      • Secure Sockets Layer (with digital certificates)
      • PKI

WLCG Service Reliability Workshop 13

slide14

Database Passwords

  • Use strong passwords
  • Try against a password checker
  • Passwords stored as hashes in database
  • Cleartext passwords can be typically but not necessarily found at the following places
    • Server
    • Shell History files
    • Unix Scripts
    • Log Files
    • Dump Files
    • Trace Files
    • Application Server
  • JDBC-Config-Files
  • Trace Files
  • DBA Client PC
  • Desktop-Shortcut
  • Batch-Files
  • Tool Configuration files (connections.ini)
  • Trace Files

WLCG Service Reliability Workshop 14

slide15

Database Passwords

  • Do not email passwords around
  • Where are yours written down?
  • Do your machine room staff have them? In emergency?
  • Who actually knows the passwords? A list of people?
  • Who has access to the host (as root? oracle?)
  • Credentials often in Grid Control

WLCG Service Reliability Workshop 15

slide16

Database Passwords

  • We use “pwsafe”
    • Use SSH key to access
    • Password on pwsafe to use
    • File replicated across two sites
    • Usernames and passwords are stored using a unique name to identify them, normally of the form group.name. However the group name is optional.
      • o/s <machine name>.<o/s user>
      • database users: <database name>.<user name>
      • application express: <apex instance>.<workspace>

WLCG Service Reliability Workshop 16

slide17

Database Passwords

  • pwsafe
    • Showing A Password:
      • # pwsafe up <unique name>
    • Adding A Password:
      • # pwsafe a <unique name>
    • Deleting A Password:
      • # pwsafe delete <unique name>
  • Can add notes too

WLCG Service Reliability Workshop 17

slide18

Machine Ports

  • Check the Oracle default port list
  • Changing the default ports can help to stop simple attacks but not real portscans.
  • In Oracle it is very often not possible to change the default port because the port is hardcoded.
  • At least for the Oracle database (except iasdb) it's is recommended to change the TNS listener port from 1521/1526 to something else.
  • Options include running a local firewall then using something called SQLNet Proxy to manage the port. Otherwise we could use Connection Manager.

WLCG Service Reliability Workshop 18

slide19

Database Security Checklist

WLCG Service Reliability Workshop 19

slide20

Database Security Checklist – Step 1

  • Install only what is required
  • Oracle Database Installation contains a host of options and products in addition to the database server.
  • Install additional products and options only as necessary.
  • Use the custom installation option to avoid installing unnecessary products or, perform a typical installation, and then deinstall unrequired options and products.
  • It is best practise to know what the database will be used for so that you can apply only what Oracle products are needed.

WLCG Service Reliability Workshop 20

slide21

Database Security Checklist – Step 1

  • Common components that you can install with each database are:
    • Oracle Data Mining
    • Oracle Text
    • Oracle Olap
    • Oracle Spatial
    • Oracle Ultra Search
    • Oracle Label Security
    • Sample Schemas
    • Enterprise Manager Repository
    • Oracle database extensions for .NET

WLCG Service Reliability Workshop 21

slide22

Database Security Checklist – Step 1

  • None of these are needed to get a database up and running. They are "extras" to be added for more functionality.
  • Check which of these have been installed by running DBCA
  • ACTION: Use the instructions below to deinstall any options on PRODUCTION databases that are not actually used
    • Dropping Sample Schemas
    • Sample Schemas script directories are located in $ORACLE_HOME/demo/schema.
    • The script xx_drop.sql, where xx is the schema abbreviation, removes all objects from a particular schema. Run this for HR/OE/PM/IX/SH/BI schemas.

WLCG Service Reliability Workshop 22

slide23

Database Security Checklist – Step 1

  • Dropping schemas (cont)

# cd $ORACLE_HOME/demo/schemas

# sqlplus hr/pass

SQL> @human_resources/hr_drop

SQL> exit

# sqlplus oe/pass

SQL> @order_entry/oe_drop

SQL> exit

# sqlplus pm/pass

SQL> @product_media/pm_drop

SQL> exit

WLCG Service Reliability Workshop 23

slide24

Database Security Checklist – Step 1

  • Dropping schemas (cont)

# sqlplus ix/pass

SQL> @info_exchange/dix_v3.sql

-- ignore any errors

SQL> exit

# sqlplus sh/pass

SQL> @sales_history/sh_drop.sql

-- ignore any errors

SQL> exit

WLCG Service Reliability Workshop 24

slide25

Database Security Checklist – Step 1

  • Dropping schemas (cont)
    • no script to run for BI
    • Once this has been done for all the schemas, drop the users HR/OE/PM/IX/SH/BI.

SQL> drop user <user> cascade;

  • Manual de-install of Spatial
    • Spatial is installed by default when using DBCA. To de-install manually follow Metalink Note:179472.1
  • Also drop the MDDATA user (the notes miss this one)

WLCG Service Reliability Workshop 25

slide26

Database Security Checklist – Step 2

  • Lock and Expire Default User Accounts
  • Oracle installs a number of default database server user accounts.
  • On DBCA installation
    • most default database user accounts automtically locked and expireed .
  • On MANUAL installation
    • no accounts are locked
  • The following two slides show a list of Oracle account and their status that is given AFTER the database is created using DBCA
  • ACTION: Check these accounts are still expired and locked if not being used.

WLCG Service Reliability Workshop 26

slide27

Database Security Checklist – Step 2

WLCG Service Reliability Workshop 27

slide28

Database Security Checklist – Step 2

WLCG Service Reliability Workshop 28

slide29

Database Security Checklist – Step 3

  • Review all database users every 6 months (or more often)
  • Contact users and check schemas are still required
    • If not export and drop them
  • If any further options or Oracle products e.g. Apex, Oracle Warehouse Builder, HTTP server have been installed, check they are still required
    • If not deinstall them
  • Run the Oracle Installer in $ORACLE_HOME/oui/bin/runInstaller to check if there are any other Oracle Homes with products in them and deinstall from there.
  • Other products like Oracle Warehouse Builder may have an alternative de-install process. Check the documentation for your product.

WLCG Service Reliability Workshop 29

slide30

Database Security Checklist – Step 4

  • Change default User Passwords
  • Unlock ONLY those accounts that need to be accessed on a regular basis and assign a strong password to each of these accounts.
    • Minimum password of 8 alphanumeric characters.
    • Make sure the passwords are different between the users
    • Make sure they are also different for any counterpart account on a production/development/test account.
  • This is mandatory for all DBA accounts on the database
    • sys, system, sysman, dbsnmp

WLCG Service Reliability Workshop 30

slide31

Database Security Checklist – Step 4

  • Change passwords with

$ sqlplus / as sysdba

SQL> alter user system identified by ********;

User altered.

SQL>

  • Basic password management rules (such as length, history, complexity etc...) as provided by the database be applied to all user passwords and that these passwords are changed periodically.
  • ACTION: ensure all passwords are at least 8 random alphanumeric characters. If change of password is required to satisfy this, make sure you advise users/arrange appropriate downtime as an application may need to change it's settings.

WLCG Service Reliability Workshop 31

slide32

Database Security Checklist – Step 4

  • ACTION: Change SYS, SYSTEM, SYSMAN (on Grid Control DB), DBSNMP passwords every 6 months. Remember to change preferred credentials in Grid Control, agent config and pwsafe (or your wherever you store passwords).
  • Changing DBSNMP password
    • Change DBSNMP password as above. To tell the agent that the password has changed, edit the file $AGENT_HOME/<machine>/sysman/emd/targets.xml and edit the database target section as follows:

Property NAME="UserName" VALUE="dbsnmp";

Property NAME="password" VALUE="newpass" ENCRYPTED="FALSE";

  • Then stop and start the agent. The password will be encrypted again when the agent restarts.

WLCG Service Reliability Workshop 32

slide33

Database Security Checklist – Step 5

  • Enable Data Dictionary Protection
  • To prevent regular users that have ANY system privileges e.g. DROP ANY TABLE being able to use these on the data dictionary make sure the parameter is O7_DICTIONARY_ACCESSIBILITY is set to FALSE.
  • Action FALSE is the default in 10g but check it anyway

$ sqlplus / as sysdba

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY

NAME TYPE VALUE

------------------------------- ------------ -----

07_DICTIONAIRY_ACCESSIBILITY boolean FALSE

WLCG Service Reliability Workshop 33

slide34

Database Security Checklist – Step 5

  • If a user does need to access a dictionary view for non malicious information, they can be granted the "SELECT ANY DICTIONARY" privilege.

WLCG Service Reliability Workshop 34

slide35

Database Security Checklist – Step 6

  • Practise the principle of least privilege
  • Do not provide users with any more priviliges than are strictly necessary
  • Restrict the following as much as possible:
    • The number of SYSTEM and OBJECT privileges granted to database users
    • 2) The number of people who are allowed to make SYS-privileged connections to the database.
  • ACTION: Review the SYSTEM privs (Note most regular users should only have CREATE SESSION privilege or CONNECT role assigned (although check CONNECT role only contains "CREATE SESSION" privilege in DBA_SYS_PRIVS).

WLCG Service Reliability Workshop 35

slide36

Database Security Checklist – Step 6

  • ACTION: The RESOURCE role (allows creation of tables etc) should only be assigned on creation of the schema and for schema changes and then dropped again on production databases). Drop RESOURCE role from regular users (see DBA_SYS_PRIVS). Review the contents of the RESOURCE role in DBA_ROLE_PRIVS.
  • ACTION: Also "ANY" privileges should not be assigned as they allow this user access to objects in all other schemas. Change ANY system privileges to object privileges e.g. drop SELECT ANY TABLE and assign ....

WLCG Service Reliability Workshop 36

slide37

Database Security Checklist – Step 6

  • To check currently assigned system privs

SELECT * from DBA_SYS_PRIVS

WHERE GRANTEE NOT IN ('DBA', 'SYS', 'SYSTEM', 'OEM_MONITOR', 'OEM_ADVISOR', 'IMP_FULL_DATABASE', 'EXP_FULL_DATABASE', 'ORDSYS', 'ORDPLUGINS', 'JAVADEBUGPRIV', 'OUTLN', 'FLOWS_020200', 'FLOWS_030000', 'AQ_ADMINISTRATOR_ROLE', 'CTXSYS', 'EXFSYS', 'XDB', 'APEX_PUBLIC_USER', 'CONNECT', 'RESOURCE', 'DIP',

'SCHEDULER_ADMIN', 'DBSNMP', 'ANONYMOUS', 'WMSYS', 'RECOVERY_CATALOG_OWNER')

ORDER BY GRANTEE, PRIVILEGE;

WLCG Service Reliability Workshop 37

slide38

Database Security Checklist – Step 6

  • Review and revoke unneccessary privs
    • GRANT CONNECT TO <user>;
    • REVOKE CREATE PROCEDURE FROM <user>;
    • REVOKE RESOURCE FROM <user>;
  • To see roles assigned to users
    • SELECT * FROM dba_role_privs WHERE USER = 'ADAM';
  • ACTION: Review object privs for each regular user and revoke as necessary
    • select * from dba_tab_privs where grantee ='SSO';
  • ACTION: Check that only SYS has DBA role

WLCG Service Reliability Workshop 38

slide39

Database Security Checklist – Step 6

  • Additionally, review the privileges grants to the PUBLIC role as you may wish to restrict what access this will give to all users.
  • Oracle provides execution rights to four packages on the PUBLIC role that should be removed after installation:
  • ACTION: Revoke the execution privilege on PUBLIC to the packages:
    • UTL_SMTP [EMAIL]
    • UTL_TCP [Network Connections]
    • UTL_HTTP [request/retrieve information from HTTP server]
    • UTL_FILE [access to files outside the DB]

WLCG Service Reliability Workshop 39

slide40

Database Security Checklist – Step 6

  • Remove with

# sqlplus / as sysdba

SQL> REVOKE EXECUTE ON sys.utl_smtp FROM "PUBLIC";

Revoke succeeded.

SQL> REVOKE EXECUTE ON sys.utl_tcp FROM "PUBLIC";

Revoke succeeded.

SQL> REVOKE EXECUTE ON sys.utl_http FROM "PUBLIC";

Revoke succeeded.

SQL> REVOKE EXECUTE ON sys.utl_file FROM "PUBLIC";

Revoke succeeded.

WLCG Service Reliability Workshop 40

slide41

Database Security Checklist – Step 7

  • Enforce access controls effectively and authenticate clients stringently
  • Setting the initialization parameter REMOTE_OS_AUTHENT = FALSE, creates a more secure configuration that enforces proper, server-based authentication of clients connecting to an Oracle database.
  • You should not alter the default setting of the REMOTE_OS_AUTHENT initialization parameter, which is FALSE. Setting this parameter to FALSE does not mean that users cannot connect remotely. It simply means that the database will not trust that the client has been already authenticated, and will therefore apply its standard authentication processes.

WLCG Service Reliability Workshop 41

slide42

Database Security Checklist – Step 7

  • ACTION: Check parameter REMOTE_OS_AUTHENT is FALSE on each database instance (all on RAC):

$ sqlplus / as sysdba

SQL> show parameter remote_os_authent

NAME TYPE VALUE

---------------------- --------------------- -----

remote_os_authent boolean FALSE

WLCG Service Reliability Workshop 42

slide43

Database Security Checklist – Step 8

  • Restrict Operating System Access
  • Limit the number of operating system users.
  • Limit the privileges of the operating system accounts (administrative, root-privileged or DBA) on the host (physical machine) to the least privileges needed for the user's tasks.
  • ACTION: Check with System Administrator that only default users have been created and any unused default ones are locked. Ask them to remove any old users.
  • ACTION: Check with System Administrator that the privileges of OS accounts have the least privileges necessary.

WLCG Service Reliability Workshop 43

slide44

Database Security Checklist – Step 8

  • ACTION: Check that any database developers have their own O/S id and are in the osinstall group but not the dba group. This allows them to access sqlplus but not to log on as SYSDBA.
  • Restricting the ability to modify the default file and directory permissions for the Oracle Database home (installation) directory or its contents. Even privileged operating system users and the Oracle owner should not modify these permissions, unless instructed otherwise by Oracle.

WLCG Service Reliability Workshop 44

slide45

Database Security Checklist – Step 8

  • ACTION: Check with System Administrator that no O/S users can change file/directory permissions of the Oracle Home
  • Restricting symbolic links. Ensure that when providing a path or file to the database, neither the file nor any part of the path is modifiable by an untrusted user.
  • The file and all components of the path should be owned by the DBA or some trusted account, such as root. This recommendation applies to all types of files: data files, log files, trace files, external tables, bfiles, and so on.
  • ACTION: Check no symbolic links against data files, log files, trace files, external tables, bfiles etc

WLCG Service Reliability Workshop 45

slide46

Database Security Checklist – Step 9

  • Restrict Network Access
  • Use a firewall
  • Oracle Listener (9i)
    • An Oracle Listener running without an established password may be probed for critical details about the databases on which it is listening such as trace and logging information, banner information and database descriptors and service names.
    • Restrict the privileges of the listener, so that it cannot read or write files in the database or the Oracle server address space. This restriction prevents external procedure agents spawned by the listener (or procedures executed by such an agent) from inheriting the ability to do such reads or writes.

WLCG Service Reliability Workshop 46

slide47

Database Security Checklist – Step 9

  • Oracle Listener
    • Prevent online administration by requiring the administrator to have write privileges on the LISTENER.ORA file
    • Use the parameter ADMIN_RESTRICTIONS_listener_name to restrict runtime administration of the listener. The parameter is useful if the listener is not password-protected.
    • Setting ADMIN_RESTRICTIONS_listener_name=on disables the runtime modification of parameters in listener.ora. That is, the listener will refuse to accept SET commands that alter its parameters.
    • To change any of the parameters in listener.ora, including ADMIN_RESTRICTIONS_listener_name itself, modify the listener.ora file manually and reload its parameters (with the RELOAD command) for the new changes to take effect without explicitly stopping and restarting the listener.

WLCG Service Reliability Workshop 47

slide48

Database Security Checklist – Step 9

  • ACTION: Add or alter this line in the LISTENER.ORA file
    • ADMIN_RESTRICTIONS_LISTENER=ON

Then RELOAD the configuration.

  • If you are administering the listener remotely over an insecure network and require maximum security, configure the listener with a secure protocol address that uses the TCP/IP with SSL protocol. If the listener has multiple protocol addresses, ensure that the TCP/IP with SSL protocol address is listed first in the listener.ora file.

WLCG Service Reliability Workshop 48

slide49

Database Security Checklist – Step 9

  • ACTION: Use SSL when administering the listener, by making the TCPS protocol the first entry in the address list as follows:

LISTENER=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=

(PROTOCOL=tcps)

(HOST = ed-pdsun1.us.oracle.com)

(PORT = 8281)))

WLCG Service Reliability Workshop 49

slide50

Database Security Checklist – Step 9

  • For Oracle Database 10g Release 1 and higher the default authentication mode is local O/S authentication which requires administrator to be a member of the local dba group.
  • Setting a password for the TNS listener in Oracle Database 10g Release 1 and higher simplifies administration.
  • However, setting a password requires good password management to prevent unauthorized users from guessing the password and potentially gaining access to privileged listener operations.
  • Customers may wish to consider not setting a password for the TNS listener starting with Oracle Database 10g Release 1.
  • ACTION: No need to set a listener password for release 10.1 or higher but ensure that only the oracle O/S user (or equivalent) is a member of the dba O/S group (or equivalent)

WLCG Service Reliability Workshop 50

slide51

Database Security Checklist – Step 9

  • ACTION: Remove the external procedure configuration from the listener.ora file if you do not intend to use such procedures. Remove the following

EXTPROC_LISTENER=

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=ipc)(KEY=extproc)))

WLCG Service Reliability Workshop 51

slide52

Database Security Checklist – Step 9

  • Monitor listener activity
  • Authenticating client computers over the Internet is problematic. Do user authentication instead, which avoids client system issues that include falsified IP addresses, hacked operating systems or applications, and falsified or stolen client system identities.
  • Configure the connection to use SSL. Using SSL (Secure Sockets Layer) communication makes eavesdropping unfruitful and enables the use of certificates for user and server authentication. (requires OAS??)

WLCG Service Reliability Workshop 52

slide53

Database Security Checklist – Step 9

  • Consider setting up certificate authentication for clients and servers such that:
    • The organization is identified by unit and certificate issuer and the user is identified by distinguished name and certificate issuer.
    • Applications test for expired certificates.
    • Certificate revocation lists are audited

WLCG Service Reliability Workshop 53

slide54

Database Security Checklist – Step 9

  • ACTION: Check network IP addresses
    • Use the Oracle Net valid node checking security feature to allow or deny access to Oracle server processes from network clients with specified IP addresses. To use this feature, set the following protocol.ora (Oracle Net configuration file) parameters:

tcp.validnode_checking = YES

tcp.excluded_nodes = {list of IP addresses}

tcp.invited_nodes = {list of IP addresses}

    • The first parameter turns on the feature whereas the latter parameters respectively deny and allow specific client IP addresses from making connections to the Oracle listener (This helps in preventing potential Denial of Service attacks).

WLCG Service Reliability Workshop 54

slide55

Database Security Checklist – Step 9

  • Encrypt network traffic
    • If possible, use Oracle Advanced Security to encrypt network traffic between clients, databases, and application servers.
  • Harden the operating system
    • disabling all unnecessary operating system services.
    • Both UNIX and Windows platforms provide a variety of operating system services, most of which are not necessary for most deployments. Such services include FTP, TFTP, TELNET, and so forth.
    • Be sure to close both the UDP and TCP ports for each service that is being disabled. Disabling one type of port and not the other does not make the operating system more secure.
    • See your friendly local system administrator

WLCG Service Reliability Workshop 55

slide56

Database Security Checklist – Step 10

  • Apply all security patches
  • Always apply all relevant and current security patches for both the operating system on which Oracle Database resides and Oracle Database itself, and for all installed Oracle Database options and components.
  • Periodically check the security site on Oracle Technology Network for details on security alerts released by Oracle Corporation at http://www.oracle.com/technology/deploy/security/alerts.htm

WLCG Service Reliability Workshop 56

slide57

Database Security Checklist – Step 10

  • Also check Oracle Worldwide Support Service site, Metalink, for details on available and upcoming security-related patches at http://metalink.oracle.com
  • ACTION: Check with System Administrator re outstanding OS security patches and apply
  • ACTION: Install Oracle Critical Patch updates as soon as possible after release

WLCG Service Reliability Workshop 57

slide58

Database Security Checklist – Step 11

  • Contact Oracle Security products if you come across a vulnerability in Oracle Database
  • If you believe that you have found a security vulnerability in Oracle Database, then submit a Service Request to Oracle Worldwide Support Services using Metalink, or e-mail a complete description of the problem, including product version and platform, together with any exploit scripts and examples to the following address: secalert_us@oracle.com

WLCG Service Reliability Workshop 58

slide59

Database Security Checklist – Other Steps

  • Policies in Enterprise Manager
    • Each target should be checked to make sure it complies with all security polices for host, database and cluster instances, listener and other targets where appropriate.
  • ACTION: Go to Grid Control, click on Compliance | Policy Violations. Correct any security violations for your system including Host, Cluster Database, Database Instance and Listener targets.

WLCG Service Reliability Workshop 59

slide60

Database Security Checklist – Other Steps

  • Personal Data (from HBI audit)
    • If the database schema contains Personal data it must comply to the Data Protection Act.
    • Define Personal Data - what are the rules of compliance?
  • Sensitive Data (from HBI audit)
    • If the database schema contains Sensitive data it must comply to the Data Protection Act.
    • Define Sensitive Data - what are the rules of compliance?

WLCG Service Reliability Workshop 60

slide61

Database Auditing

  • Audit any object

NAME VALUE

---------------- ------

audit_file_dest /opt/oracle/app/oracle/admin/ngsdb/adump

audit_sys_operations FALSE

audit_trail DB

  • See dba_audit_* tables
  • There is an AUDIT privilege
  • Logins/logouts stored in dba_audit_session

WLCG Service Reliability Workshop 61

slide62

Database Auditing

  • Check logins

SET LINESIZE 120;

COL username FORMAT a15

COL terminal FORMAT a15

COL logon_time FORMAT a25

COL logoff_time FORMAT a25

COL action_name FORMAT a20

COL returncode FORMAT 9999

SELECT username,

terminal,

action_name,

TO_CHAR(timestamp,'DD-MON-YYYY HH24:MI:SS') logon_time,

TO_CHAR(logoff_time,'DD-MON-YYYY HH24:MI:SS') logoff_time,

returncode

FROM dba_audit_session

WHERE ROWNUM < 16

ORDER BY logoff_time DESC;

WLCG Service Reliability Workshop 62

slide63

Database Auditing

  • Check logins

WLCG Service Reliability Workshop 63

slide64

Database Auditing

  • O/S logins monitored in ora_<pid>.aud files

Audit file /opt/oracle/app/oracle/admin/<host>/adump/ora_22155.aud

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

ORACLE_HOME = /opt/oracle/app/oracle/product/10.2.0

System name: Linux

Node name: <hostname>

Release: 2.6.9-55.0.2.ELsmp

Version: #1 SMP Tue Jun 12 17:59:08 EDT 2007

Machine: i686

Instance name: <instance name>

Redo thread mounted by this instance: 1

Oracle process number: 76

Unix process pid: 22155, image: oracle@<hostname> (TNS V1-V3)

Wed Nov 28 20:54:00 2007

ACTION : 'CONNECT'

DATABASE USER: '/'

PRIVILEGE : SYSDBA

CLIENT USER: oracle

CLIENT TERMINAL: pts/0

STATUS: 0

WLCG Service Reliability Workshop 64

slide65

Host Security

  • Use ssh key pairs
    • Update /etc/ssh/sshd_config where you can say that you do not want to allow password authentication

PasswordAuthentication yes

to

PasswordAuthentication no

then restart the ssh daemon

/etc/init.d/sshd restart

    • After that change you need to have your public ssh key in the ~/.ssh/authorized_keys file for any user that you would like to log in as.

WLCG Service Reliability Workshop 65

slide66

Other Security

  • Do not leave host names or database names in presentations etc
  • Google your hosts (or will that then go in their cache?)
  • Lock your computer when you’re away
  • Don’t leave putty or “screen” sessions open
  • Speak to your security officer if in any doubt
  • Arrange a security audit

WLCG Service Reliability Workshop 66

slide67

Host Housekeeping

Managing Your Environment

WLCG Service Reliability Workshop 67

slide68

Host Housekeeping

  • Log files can grow to large sizes and be difficult to view or take up too much space
  • We have developed script to purge them
    • Runs through Grid Control
  • Make sure files are backed up first if you need them
  • Balance between history and space

WLCG Service Reliability Workshop 68

slide69

Host Housekeeping

function rmfiles {

DIR=$1

DESC=$2

DAYS=$3

if [ -d $DIR ]

then

echo ` `

echo "Checking ${DESC}: ${DIR}"

echo "Removing files/directories older than: $DAYS days"

# The * stops find deleting the directory itself

echo `find ${DIR}/* -mtime +$DAYS | wc -l` files or directories

# This lists the files/dirs to delete

find ${DIR}/* -mtime +$DAYS

# then deletes them

find ${DIR}/* -mtime +$DAYS -exec rm -rf {} \;

else

echo "Directory $DIR does not exist on this server"

fi

}

WLCG Service Reliability Workshop 69

slide70

Host Housekeeping

echo "Housekeeping files"

date +\%d\%m\%y_\%H\%M\%S

echo `date`

OBASE=/opt/oracle/ora01/app/oracle

OHOME=$OBASE/OracleHomes/10.2.0/db_1

AGHOME=/opt/oracle/ora01/app/oracle/OracleHomes/oms10g/agent10g

# For RAC

CRSHOME=/opt/oracle/crs/oracle/product/10/app

# For HTTP server installations

HTTPHOME=/opt/oracle/ora01/app/oracle/OracleHomes/oms10g/oms10g

# For Grid Control installations

OMSHOME=/opt/oracle/ora01/app/oracle/OracleHomes/oms10g/oms10g

WLCG Service Reliability Workshop 70

slide71

Host Housekeeping

rmfiles "$OBASE/admin/$ORACLE_SID/bdump" "BACKGROUND dump destination" 120

rmfiles "$OBASE/admin/$ORACLE_SID/cdump" "CORE dump destination" 31

rmfiles "$OBASE/admin/$ORACLE_SID/dpdump" "DATA PUMP destination" 31

rmfiles "$OBASE/admin/$ORACLE_SID/udump" "USER dump destination" 31

rmfiles "$OBASE/admin/$ORACLE_SID/adump" "AUDIT destination" 31

rmfiles "$OBASE/admin/$ORACLE_SID/hdump" "HIGH AVAILABILITY tracefile destination" 31

rmfiles "$OHOME/admin/+ASM/bdump" "ASM BACKGROUND dump destination" 31

rmfiles "$CRSHOME/log/`hostname -s`/client" "CRS client logs" 31

rmfiles "$AGHOME/`hostname`/sysman/log" "AGENT logs" 31

rmfiles "$AGHOME/sysman/log" "AGENT logs" 31

rmfiles "$HTTPHOME/Apache/Apache/logs" "HTTP SERVER logs" 31

rmfiles "$OMSHOME/opmn/logs" "OMS opmn logs" 31

rmfiles "$OMSHOME/sysman/log" "OMS sysman logs" 31

WLCG Service Reliability Workshop 71

slide72

Host Housekeeping

Housekeeping files 291107_074653 Thu Nov 29 07:46:53 GMT 2007

Checking BACKGROUND dump destination:

/opt/oracle/ora01/app/oracle/admin/mercury/bdump

Removing files/directories older than: 120 days

1 files or directories

/opt/oracle/ora01/app/oracle/admin/mercury/bdump/alert_mercury.log.200

70731

Checking CORE dump destination:

/opt/oracle/ora01/app/oracle/admin/mercury/cdump

Removing files/directories older than: 31 days

find: /opt/oracle/ora01/app/oracle/admin/mercury/cdump/*: No such file

or directory 0 files or directories

.

.

Completed Thu Nov 29 07:46:55 GMT 2007

WLCG Service Reliability Workshop 72

slide79

Alert Log Maintenance

  • Script keeps alert log to a managable size
  • Allows file to be emailed daily

#!/bin/bash

BDUMP=/opt/oracle/app/oracle/admin/ogma/bdump

# This script will move and rename the current alert.log by adding the date as the extension

# GDB 08-OCT-2004

# Email the log

cat $BDUMP/alert_ogma1.log | mail -s "Oracle: ogma1 alert log for `date`" db_admins

# Rename the log

CURRDATE=`date '+%Y%m%d'`

cp $BDUMP/alert_ogma1.log $BDUMP/alert_ogma1.log.$CURRDATE

cat /dev/null > $BDUMP/alert_ogma1.log

WLCG Service Reliability Workshop 79

slide80

Alert Log Maintenance

  • Run daily from cron

0 8 * * * /home/oracle/mercury/alert.sh >/dev/null 2>&1

  • Emailed to group
    • Details in /home/oracle/.mailrc file

alias db_admins databaseservices@rl.ac.uk

WLCG Service Reliability Workshop 80

slide81

Alert Log Maintenance

  • Keeps alert logs small and easy to find

-rw-r----- 1 oinstall 35347 Nov 20 08:00 alert_ogma1.log.20071120

-rw-r----- 1 oinstall 35449 Nov 21 08:00 alert_ogma1.log.20071121

-rw-r----- 1 oinstall 35654 Nov 22 08:00 alert_ogma1.log.20071122

-rw-r----- 1 oinstall 35974 Nov 23 08:00 alert_ogma1.log.20071123

-rw-r----- 1 oinstall 36269 Nov 24 08:00 alert_ogma1.log.20071124

-rw-r----- 1 oinstall 36539 Nov 25 08:00 alert_ogma1.log.20071125

-rw-r----- 1 oinstall 34669 Nov 26 08:00 alert_ogma1.log.20071126

-rw-r----- 1 oinstall 35014 Nov 27 08:00 alert_ogma1.log.20071127

-rw-r----- 1 oinstall 35465 Nov 28 08:00 alert_ogma1.log.20071128

-rw-r----- 1 oinstall 0 Nov 28 08:00 alert_ogma1.log

WLCG Service Reliability Workshop 81

slide82

Host Housekeeping

  • Directory Structure
    • /opt/oracle/product/10.2.0/db_1 # oracle database home
    • /opt/oracle/product/10.2.0/client_1 # oracle client home
    • /opt/oracle/product/10.2.0/agent_1 # oracle agent home
    • /opt/oracle/product/10.2.0/crs # oracle clusterware home
    • /opt/oracle/oraInventory # oracle inventory
    • /opt/oracle/archive/<sid> # archives if not using ASM
    • /opt/oracle/backup/<sid> # backups
    • /opt/oracle/oradata/<sid> # data if not using ASM
    • /opt/oracle/admin/<sid> # logs and alerts
    • /opt/oracle/flash_recovery_area/<sid> # flash recovery area if used

WLCG Service Reliability Workshop 82

slide83

Host Housekeeping

  • All database, environment and schema scripts should be kept under /home/oracle
  • The /home/oracle directory should be the home directory for the unix user oracle
  • In /home/oracle the following scripts should be available:
    • ora10<database name>.sh # Script to set database SID
    • ora10asm.sh # Script to set ASM SID
    • ora10common.sh # Script to set common environment variables
  • Scripts should use LOGIN_PATH and LOGIN_LD_LIBRARY_PATH environment variables so that ora10 scripts can be run more than once in a session and the variables PATH and LD_LIBRARY_PATH to not grow to big by adding themselves to their new definition

WLCG Service Reliability Workshop 83

slide84

Host Housekeeping

  • Summary of environment files

[oracle@lcgdb01 ~]$ l ora*

-rwxrwxrwx 1 oinstall 43 May 18 2006 ora10asm.sh

-rwxrwxrwx 1 oinstall 302 May 22 2006 ora10agent_cern.sh

-rwxrwxrwx 1 oinstall 5114 Mar 29 2007 orahousekeep.sh

-rwxrwxrwx 1 oinstall 552 May 22 2007 ora10common.sh

-rwxrwxrwx 1 oinstall 55 Aug 8 10:00 ora10ogma.sh

-rwxrwxrwx 1 oinstall 298 Sep 12 15:32 ora10agent_cclrc.sh

WLCG Service Reliability Workshop 84

slide85

Host Housekeeping

  • ora10common.sh

# clusterware

export CRS_HOME=/opt/oracle/crs/oracle/product/10/app

# oracle

export ORACLE_BASE=/opt/oracle/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0

export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/jdk/bin:$CRS_HOME/bin

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

# sqlplus

export EDITOR=vi

export ORA_NLS10=$ORACLE_HOME/nls/data

export NLS_LANG="english_united kingdom.AL32UTF8"

export NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"

export RMAN="$ORACLE_HOME/bin/rman target / catalog rman/<password>@rcat"

WLCG Service Reliability Workshop 85

slide86

Host Housekeeping

  • ora10ogma.sh

/home/oracle/ora10common.sh

export ORACLE_SID=ogma1

WLCG Service Reliability Workshop 86

slide87

Host Housekeeping

  • ora10asm.sh

/home/oracle/ora10common.sh

export ORACLE_SID=+ASM1

WLCG Service Reliability Workshop 87

slide88

Host Housekeeping

  • Agent environment script: ora10agent_cclrc.sh

export ORACLE_BASE=/opt/oracle/app/oracle

export ORACLE_HOME=$ORACLE_BASE/agent10g

export AGENT_HOME=$ORACLE_BASE/agent10g

unset ORACLE_SID

export PATH=$PATH:$ORACLE_HOME/lcgdb01.gridpp.rl.ac.uk/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/jdk/bin

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

WLCG Service Reliability Workshop 88

slide89

Host Housekeeping

  • Agent environment script: ora10agent_cern.sh

export ORACLE_BASE=/opt/oracle/app/oracle

export ORACLE_HOME=$ORACLE_BASE/agent10g_CERN/agent10g

export AGENT_HOME=$ORACLE_BASE/agent10g_CERN/agent10g

unset ORACLE_SID

export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/jdk/bin

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

WLCG Service Reliability Workshop 89

slide90

Host Housekeeping

  • Agent environment scripts
    • Check you are using the right emctl
    • which emctl
  • Check status with emctl status agent
  • Check files are uploading
  • Automatically start on host boot

WLCG Service Reliability Workshop 90

slide91

Documentation

  • System documentation
    • versions, hosts, databases, users, patch versions
  • wiki
    • Who can access?
  • Disaster Recovery
  • Contacts
  • Standard Operating Procedures
    • startups, shutdown, user alerts
  • Do you know who to ring/email in an emergency?
  • Do you know what to without power/telephones?

WLCG Service Reliability Workshop 91

slide92

Grid Control

WLCG Service Reliability Workshop 92

slide94

Grid Control

  • Secure agent upload
  • The agents as to be secured against the OMS before they can be upgrade via grid control.
    • ./emctl secure agent
  • To secure the agent you need a registration password from the OMS. Which you can obtain or add one to use for a one -off registration
  • Go to Grid Vontrol setup page and then click-on registration password link.

WLCG Service Reliability Workshop 94

target properties

Always set Target Properties

target properties

WLCG Service Reliability Workshop 95

slide96

Target Properties found in Target Home

WLCG Service Reliability Workshop 96

slide97

There is workaround for missing button!

WLCG Service Reliability Workshop 97

slide98

Check alerts - Critical

WLCG Service Reliability Workshop 98

slide99

Check alerts - Warning

WLCG Service Reliability Workshop 99

slide100

Check alerts - Errors

WLCG Service Reliability Workshop 100

slide101

Set Credentials

WLCG Service Reliability Workshop 101

email alerts

Setup Email Alerts

email alerts

WLCG Service Reliability Workshop 102

slide103

Setup Templates

WLCG Service Reliability Workshop 103

slide104

Setup Metric Thresholds

WLCG Service Reliability Workshop 104

slide105
Backups

WLCG Service Reliability Workshop 105

slide106

Backups

  • Backup details stored centrally
  • Common scripts
  • One connection from all machines
  • Version has to be newest
  • Setup at RAL:
    • Dedicated server
    • Cold backups

WLCG Service Reliability Workshop 106

slide107

Backups – RMAN Catalog

  • Jobs in crontab on each database

# RMAN Schedules follow here

30 1 * * 1 /home/oracle/rmanfulback.sh mars rman <password> rcat >/dev/null 2>&1

30 1 * * 2-7 /home/oracle/rmanincback.sh mars rman <password> rcat >/dev/null 2>&1

30 3 * * * /home/oracle/rmanvalidate.sh mars rman <password> rcat >/dev/null 2>&1

WLCG Service Reliability Workshop 107

slide108

Backups – Full Backup Script (1)

#!/bin/ksh

#

####################################################################

#

# THE INPUT PARAMETERS ARE :

#

# $1 - Target DB

# $2 - RMAN owner

# $3 - RMAN password

# $4 - RMAN DB

#

# Script to backup archived redo log files

#

####################################################################

#

# Check arguments

#

if [[ $# -ne 4 ]]

then

echo "$0 Error on $HOST: \nUsage: $0 TARGET_DB RMAN_OWNER RMAN_PWD RMAN_DB"

exit 1

fi

WLCG Service Reliability Workshop 108

slide109

Backups – Full Backup Script (2)

###################

# RMAN variables #

###################

#

export SID=$1

export RMAN_OWNER=$2

export RMAN_PWD=$3

export RMAN_DB=$4

export LOGIN_PATH=$PATH

export LOGIN_LD_LIBRARY_PATH=$LD_LIBRARY_PATH

. /home/oracle/ora10$SID.sh

export HBlog=/home/oracle/$SID/rmanlogs/fulback${SID}.`date +%d%m%y%H%M%S`.log

echo $HBlog

export emailsub=$HOSTNAME.$ORACLE_SID..Backup.Failure

WLCG Service Reliability Workshop 109

slide110

Backups – Full Backup Script (3)

rman target / catalog ${RMAN_OWNER}/${RMAN_PWD}@${RMAN_DB} log \"${HBlog}\" << SQL

show all;

run {

backup incremental level 0 database plus archivelog delete input;

}

report obsolete;

delete noprompt obsolete;

list backup;

Exit

SQL

oraerror=`cat $HBlog | grep "ORA-"`

rmanerror=`cat $HBlog | grep "RMAN-"`

echo $oraerror

echo $rmanerror

if [ -z "$oraerror" -a -z "$rmanerror" ]

then

echo "No Errors"

else

echo "Errors Found !!!!"

cat $HBlog |mailx -s $emailsub databaseservices@stfc.ac.uk

fi

WLCG Service Reliability Workshop 110

slide111

Backups – Level 1 Backup Script

show all;

run {

backup incremental level 1 database plus archivelog delete input;

}

report obsolete;

delete noprompt obsolete;

list backup;

exit

WLCG Service Reliability Workshop 111

slide112

Backups – Archive Logs Backup Script

show all;

run {

backup archivelog all delete input;

}

report obsolete;

delete noprompt obsolete;

list backup;

exit

WLCG Service Reliability Workshop 112

slide113

Backups – RMAN Sync Script

  • Run through OEM

show all;

run {

resync catalog;

}

list backup;

exit

WLCG Service Reliability Workshop 113

slide114

Backups – Restore Validate Script

show all;

run {

restore validate database archivelog all;

}

list backup;

exit

WLCG Service Reliability Workshop 114

slide115

Backup Job in Grid Control

WLCG Service Reliability Workshop 115

slide116

Backups – Backup Report by Email (1)

Latest Database Backup Times

Database DBID Latest Backup Bytes Processed Seconds Taken Backup Type

------------------------ ---------- ----------------------------- --------------- ------------- ---------------------

ATLASDLF 4091293823 26-NOV-2007 23:44:39 6021971968 3850 DB INCR

ATLSTAGE 819435209 27-NOV-2007 00:54:13 85770240 56 DB INCR

CASTORP 2263675954 27-NOV-2007 00:31:40 12722176 26 DB INCR

CASTORT 2415163970 16-NOV-2007 00:31:39 23625728 28 DB INCR

CMSDLF 1227029095 26-NOV-2007 21:11:42 3530555392 2098 DB INCR

.

.

MINERVA 402814294 26-NOV-2007 22:24:11 6494879744 4843 DB INCR

STAGERDB 1089287619 26-NOV-2007 22:31:57 164298752 91 DB INCR

VENUS 2579470848 26-NOV-2007 22:17:14 3631218688 2679 DB INCR

WARHORSE 780645177 26-NOV-2007 18:47:06 127975424 82 DB INCR

WARHORSE 792067278 27-NOV-2007 00:46:17 7471104 9 DB INCR

23 rows selected.

---------------------------------------------------------------------------------------------------------------------

The Following Databases have not been backed up in the last 24 hours

DB_NAME

------------------------

CASTORT

WLCG Service Reliability Workshop 116

slide117

Backups – Backup Report by Email (2)

Latest Restore Validate Runs

Database DBID Latest Restore Validate

------------------------ ---------- -----------------------------

VENUS 2579470848 27-NOV-2007 01:11:22

ATLASDLF 4091293823 27-NOV-2007 04:38:24

LHCBSTAG 3505332007 27-NOV-2007 02:50:41

MERCURY 344759533 27-NOV-2007 06:42:30

.

.

WARHORSE 792067278 27-NOV-2007 02:46:54

SETH 2382003523 27-NOV-2007 02:07:17

CASTORP 2263675954 27-NOV-2007 03:32:05

HEKATE 3791789259 27-NOV-2007 00:04:33

CMSDLF 1227029095 27-NOV-2007 00:19:53

CASTORT 2418831922 27-NOV-2007 02:34:26

MARS 1068316094 27-NOV-2007 04:48:05

LHCBDLF 2704015590 27-NOV-2007 03:06:59

24 rows selected.

--------------------------------------------------------------------------------------------------------

The Following Databases have not had a Validate in the last 24 hours

no rows selected

WLCG Service Reliability Workshop 117

slide118

Backups – Email Script

####################################################################

#

# This script Produces Backup Reports to be sent to the DBAs

#

# Amendments :

# AJW - 15/05/2007 initial construction

#

####################################################################

# set the rcat environment

. /home/oracle/ora10rcat.sh

sqlplus / as sysdba <<-!!

@/home/oracle/rmanrep1.sql

exit

!!

export emailsub=Backup.Report.`date +%d%m%y%H%M%S`

echo $emailsub

cat /home/oracle/rmanrep1.txt |mailx -s $emailsub databaseservices@stfc.ac.uk

WLCG Service Reliability Workshop 118

slide119

Backups – Email SQL

spool /home/oracle/rmanrep1.txt;

set line 120

set pagesize 999

/* get list of latest database backup times */

select a.db_name "Database",

db.dbid "DBID",

a.end_time "Latest Backup",

a.output_bytes "Bytes Processed",

(end_time - start_time) * 60 * 60 * 24 "Seconds Taken"

from rman.rc_rman_status a, rman.rc_database db

where object_type in ('DB FULL','DB INCR')

and status = 'COMPLETED'

and operation = 'BACKUP'

and end_time = (select max(end_time) from rman.rc_rman_status b

where b.db_name = a.db_name

and b.db_key = a.db_key

and object_type in ('DB FULL','DB INCR')

and status = 'COMPLETED'

and operation = 'BACKUP')

and db.db_key = a.db_key

order by end_time ;

WLCG Service Reliability Workshop 119

slide120

Backups – Catalog Backup Script

${ORACLE_HOME}/bin/rman target / nocatalog log \"${HBlog}\" << SQL

show all;

shutdown immediate

startup mount;

backup database;

startup;

report obsolete;

delete noprompt obsolete;

list backup;

exit

WLCG Service Reliability Workshop 120

slide121

Backups – RMAN Configuration

RMAN> show all;

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/oracle/backup/mars/%F.bak';

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 100 M FORMAT '/opt/oracle/backup/mars/mars_%U.bak';

CONFIGURE MAXSETSIZE TO UNLIMITED;

CONFIGURE ENCRYPTION FOR DATABASE OFF;

CONFIGURE ENCRYPTION ALGORITHM 'AES128';

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/app/oracle/product/10.2.0/dbs/snapcf_mars1.f';

WLCG Service Reliability Workshop 121

slide122

Backups - Summary

  • We run backup jobs through crontab
  • Resync and Email job through OEM
  • Archive Logs backup job could be run automatically as oem job when area 70% full (for example)
  • Keep it simple, keep it documented

WLCG Service Reliability Workshop 122

slide123

Questions & (hopefully) Answers

g.d.brown@rl.ac.uk

WLCG Service Reliability Workshop 123