1.Type of Oracle Database Users
- Database Administrator
- Security Officer
- Network Adminstrator
- Application Developer
- Application Adminstrator
- Database Users
1.1 Database Adminstrators
A database administrator's responsibilities can include the following tasks:
-
Installing and upgrading the Oracle Database server and application tools
-
Allocating system storage and planning future storage requirements for the database system
-
Creating primary database storage structures (tablespaces) after application developers have designed an application
-
Creating primary objects (tables, views, indexes) once application developers have designed an application
-
Modifying the database structure, as necessary, from information given by application developers
-
Enrolling users and maintaining system security
-
Ensuring compliance with Oracle license agreements
-
Controlling and monitoring user access to the database
-
Monitoring and optimizing the performance of the database
-
Planning for backup and recovery of database information
-
Maintaining archived data on tape
-
Backing up and restoring the database
-
Contacting Oracle for technical support
1.2 Application Developers
Application developers design and implement database applications.
Their responsibilities include the following tasks:
-
Designing and developing the database application
-
Designing the database structure for an application
-
Estimating storage requirements for an application
-
Specifying modifications of the database structure for an application
-
Relaying this information to a database administrator
-
Tuning the application during development
-
Establishing security measures for an application during development
1.3 Database Users
Database users interact with the database through applications or utilities.
A typical user's responsibilities include the following tasks:
-
Entering, modifying, and deleting data, where permitted
-
Generating reports from the data
2.1 Task of a Database Adminstrator
2.1.1 Evaluate the Database Server Hardware
Evaluate how Oracle Database and its applications can best use the available computer resources.
This evaluation should reveal the following information:
-
How many disk drives are available to the Oracle products
-
How many, if any, dedicated tape drives are available to Oracle products
-
How much memory is available to the instances of Oracle Database you will run (see your system configuration documentation)
2.1.2 Install the Oracle Database Software
As the database administrator, you install the Oracle Database server software and any front-end tools and database applications that access the database.
In some distributed processing installations, the database is controlled by a central computer (database server) and the database tools and applications are executed on remote computers (clients). In this case, you must also install the Oracle Net components necessary to connect the remote systems to the computer that executes Oracle Database.
2.1.3 Plan the Database
database.
It is important to plan how the logical storage structure of the database will affect system performance and various database management operations. For example, before creating any tablespaces for your database, you should know how many data files will comprise the tablespace, what type of information will be stored in each tablespace, and on which disk drives the data files will be physically stored. When planning the overall logical storage of the database structure, take into account the effects that this structure will have when the database is actually created and running. Consider how the logical storage structure of the database will affect:
-
The performance of the computer running Oracle Database
-
The performance of the database during data access operations
-
The efficiency of backup and recovery procedures for the database
Plan the relational design of the database objects and the storage characteristics for each of these objects. By planning the relationship between each object and its physical storage before creating it, you can directly affect the performance of the database as a unit. Be sure to plan for the growth of the database.
In distributed database environments, this planning stage is extremely important. The physical location of frequently accessed data dramatically affects application performance.
During the planning stage, develop a backup strategy for the database. You can alter the logical storage structure or design of the database to improve backup efficiency.
2.1.4 Create and Open the Database
After you complete the database design, you can create the database and open it for normal use.
You can create a database at installation time, using the Database Configuration Assistant, or you can supply your own scripts for creating a database.
2.1.5 Back Up the Database
After you create the database structure, perform the backup strategy you planned for the database.
Create any additional redo log files, take the first full database backup (online or offline), and schedule future database backups at regular intervals.
2.1.6 Enroll System Users
After you back up the database structure, you can enroll the users of the database in accordance with your Oracle license agreement, and grant appropriate privileges and roles to these users.
2.1.7 Implement the Database Design
After you create and start the database, and enroll the system users, you can implement the planned logical structure database by creating all necessary tablespaces. When you have finished creating tablespaces, you can create the database objects.
2.1.8 Back Up the Fully Functional Database
When the database is fully implemented, again back up the database. In addition to regularly scheduled backups, you should always back up your database immediately after implementing changes to the database structure.
2.1.9 Tune Database Performance
Optimizing the performance of the database is one of your ongoing responsibilities as a DBA. Oracle Database provides a database resource management feature that helps you to control the allocation of resources among various user groups.
The database resource manager is described in Managing Resources with Oracle Database Resource Manager.
2.1.10 Download and Install Release Updates and Release Update Revisions
After the database installation, download and install Release Updates (Updates) and Release Update Revisions (Revisions) for your Oracle software on a regular basis.
Starting with Oracle Database 18c, Oracle provides quarterly updates in the form of Release Updates (Updates) and Release Update Revisions (Revisions). Oracle no longer releases patch sets. Check the My Oracle Support website for required updates for your installation.
2.1.11 Roll Out to Additional Hosts
After you have an Oracle Database installation properly configured, tuned, patched, and tested, you may want to roll that exact installation out to other hosts.
Reasons to do this include the following:
-
You have multiple production database systems.
-
You want to create development and test systems that are identical to your production system.
Instead of installing, tuning, and patching on each additional host, you can clone your tested Oracle Database installation to other hosts, saving time and avoiding inconsistencies. There are two types of cloning available to you:
-
Cloning an Oracle home—Just the configured and patched binaries from the Oracle home directory and subdirectories are copied to the destination host and fixed to match the new environment. You can then start an instance with this cloned home and create a database.
You can use Oracle Enterprise Manager Cloud Control to clone an Oracle home to one or more destination hosts. You can manually clone an Oracle home using a set of provided scripts and Oracle Universal Installer.
-
Cloning a database—The tuned database, including database files, initialization parameters, and so on, are cloned to an existing Oracle home (possibly a cloned home).
You can use Cloud Control to clone an Oracle database instance to an existing Oracle home.
2.2 SQL Statements
2.2.1 Connecting to the Database with SQL*PLUS
2.2.1.1 About Connecting to the Database with SQL*PLUS
Step 1: Open a Command Window
Step 2: Set Operation System Enviroment Variables
Example 1-1 Setting Environment Variables in Unix (C Shell)
setenv ORACLE_SID orcl
setenv ORACLE_HOME /u01/app/oracle/product/database_release_number/dbhome_1
setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib:/usr/dt/lib:/usr/openwin/lib:/usr/ccs/libExample 1-2 Setting Environment Variables in Linux (Bash Shell)
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/database_release_number/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/dt/lib:/usr/openwin/lib:/usr/ccs/libExample 1-3 Setting Environment Variables in Microsoft Windows
SET ORACLE_SID=orawin2
setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib:/usr/dt/lib:/usr/openwin/lib:/usr/ccs/lib:$LD_LIBRARY_PATH3.1Identifying Your Oracle Database Software Release
3.1.1 About Oracle Database Release Numbers
Figure 1-1 Example of an Oracle Database Release Number
-
First numeral: This numeral indicates the major release version. It also denotes the last two digits of the year in which the Oracle Database version was released for the first time.
-
Second numeral: This numeral indicates the release update version (Update, or RU).
-
Third numeral: This numeral indicates the release update revision version (Revision, or RUR).
-
Fourth numeral: This numeral is reserved for future use. Currently it is always set to 0.
-
Fifth numeral: Although only the first three fields are commonly used, the fifth field can show a numerical value that redundantly clarifies the release date of a release update (RU), such as 19.7.0.0.200414.
4.1.1 Check Your Current Release Number
SQL> col PRODUCT FORMAT A38
SQL> col VERSION FORMAT A10
SQL> COL VERSION_FULL FORMAT A12
SQL> COL STATUS FORMAT A12
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT VERSION VERSION_FULL STATUS
-------------------------------------- ---------- ------------ ------------
Oracle Database 19c Enterprise Edition 19.0.0.0.0 19.3.0.0.0 Production
SQL> 5.1 About Database Adminstrator Security and Privileges
5.1.1 The Database Administrator's Operating System Account
Administrative User Accounts
- About Administrative User Accounts
Administrative user accounts have special privileges required to administer areas of the database, such as theCREATE ANY TABLEorALTER SESSIONprivilege, orEXECUTEprivilege on packages owned by theSYSschema. - SYS
When you create an Oracle database, the userSYSis automatically created with all the privileges. - SYSTEM
When you create an Oracle database, the userSYSTEMis also automatically created and granted theDBArole. - SYSBACKUP, SYSDG, SYSKM, and SYSRAC
When you create an Oracle database, the following users are automatically created to facilitate separation of duties for database administrators:SYSBACKUP,SYSDG,SYSKM, andSYSRAC. - The DBA Role
A predefinedDBArole is automatically created with every Oracle Database installation. This role contains most database system privileges. Therefore, the DBA role should be granted only to actual database administrators.
About Administrative User Accounts
The following administrative user accounts are automatically created when Oracle Database is installed:
-
SYS -
SYSTEM -
SYSBACKUP -
SYSDG -
SYSKM -
SYSRAC
Note:If the database password file name or location is changed, then run the following command for the changes to take effect:
SQL> ALTER SYSTEM FLUSH PASSWORDFILE_METADATA_CACHE;
To synchronize the passwords for non-SYS administrative users, such as SYSDBA, SYSOPER, SYSBACKUP, SYSDG, and SYSKM users, you must first revoke and then regrant the privileges to these users as follows:
1.Find all users who have been granted the SYSDBA privilege
SQL> SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';
USERNAME
--------------------------------------------------------------------------------
MYDBA
SQL>2.Revoke and then re-grant the SYSDBA privilege to these users
REVOKE SYSDBA FROM non-SYS-user;
GRANT SYSDBA TO non-SYS-user;
3.Find all users who have been granted the SYSOPER privilege
SQL> SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSOPER='TRUE';
no rows selected
SQL> 4.Revoke and regrant the SYSOPER privilege to these users.
REVOKE SYSOPER FROM non-SYS-user;
GRANT SYSOPER TO non-SYS-user;
5.Find all users who have been granted the SYSBACKUP privilege.
SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSBACKUP ='TRUE';
6.Revoke and regrant the SYSBACKUP privilege to these users.
REVOKE SYSBACKUP FROM non-SYS-user;
GRANT SYSBACKUP TO non-SYS-user;
7.Find all users who have been granted the SYSDG privilege.
SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDG='TRUE';
8.Revoke and regrant the SYSDG privilege to these users.
REVOKE SYSDG FROM non-SYS-user;
GRANT SYSDG TO non-SYS-user;
9.Find all users who have been granted the SYSKM privilege.
SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSKM='TRUE';
10.Revoke and regrant the SYSKM privilege to these users.
REVOKE SYSKM FROM non-SYS-user;
GRANT SYSKM TO non-SYS-user;
Granting and Revoking Administrative Privileges
SQL> show user;
USER is "SYS"
SQL> revoke sysdba from mydba;
revoke sysdba from mydba
*
ERROR at line 1:
ORA-01917: user or role 'MYDBA' does not exist
SQL> ALTER SESSION SET CONTAINER=ORCLPDB1;
Session altered.
SQL> revoke sysdba from mydba;
Revoke succeeded.
SQL> grant sysdba to mydba;
Grant succeeded.
SQL> Viewing Database Password File Members
SQL> select * from v$pwfile_users;
USERNAME
--------------------------------------------------------------------------------
SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM ACCOUNT_STATUS
----- ----- ----- ----- ----- ----- ------------------------------
PASSWORD_PROFILE
--------------------------------------------------------------------------------
LAST_LOGIN
---------------------------------------------------------------------------
LOCK_DATE EXPIRY_DATE
------------------ ------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
AUTHENTI COM CON_ID
-------- --- ----------
SYS
364
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
