Oracle Daily Operations Summary-(1)

Here are some summary of Oracle's daily management operations at work, all of which are basic Oracle operations and SQL statement writing, which are combed into manuals, hoping to help beginners (if there is a sort of inaccuracy, wish to point out).

I. Startup and shutdown of the database

1.1 Normal startup steps for a database to start or shut down an Oracle system must first switch to an Oracle user [[email protected] ~]# su-oracle log in to the database as a DBA (performed under Oracle user) [email Protected] ~]$ sqlplus "/as sysdba" execute start database command sql> startuporacle instance started. Total System Global area 285212672 bytesfixed size 1218968 bytesvariable size 88082024 byte Sdatabase buffers 188743680 Bytesredo buffers 7168000 bytesdatabase mounted. Database opened. Startup and shutdown monitoring (performed under Oracle user) [[email protected] ~]$ lsnrctl start[[email protected] ~]$ lsnrctl stop1.2 normal shutdown of database Also log in to the database as DBA [[email protected] ~]$ sqlplus "/as sysdba" execute the database shutdown command sql> shutdown immediate;database closed. Database dismounted. ORACLE instance shut down.
1.3 Several methods of closing a database compare shutdown has four parameters: normal, transactional, immediate, abort. The default is normal if no parameters are taken. Shutdown normal: Do not allow new connections, wait for the session to end, wait for the transaction to end, make a checkpoint, and close the data file. No instance recovery is required at startup, and this method is often not able to shut down the database or wait for a long time. Shutdown transactional: Does not allow a new connection, does not wait for the session to end, waits for the transaction to end, makes a checkpoint, and closes the data file. No instance recovery is required at startup. Shutdown immediate: Do not allow a new connection, do not wait for the session to end, do not wait for the transaction to end, make a checkpoint, and close the data file. Transactions that do not end are automatically rollback. No instance recovery is required at startup. The most common method. Shutdown abort: Do not allow new connections, do not wait for the session to end, do not wait for the transaction to end, do not checkpoint, and do not close the data file. Automatic instance recovery on startup. Generally not recommended, only if the database can not be closed when used, may cause database inconsistencies.

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

Several ways to start the Oracle database shutdown

-Start the database
The Oracle startup process involves several modes, which involve different files, each state of the database doing different things, and these patterns are suitable for different maintenance needs, the main mode has three kinds: nomount, MOUNT, OPEN.

Nomount: Starts the DB instance, reads the parameter file at this time, but does not load the database;
MOUNT: Launches the database instance, loads the database, but the database is turned off;
Open: Launches the database instance, loads and opens the database;
Force: Terminate the instance and restart the database, this mode in the database shutdown or startup encountered problems when used, this method is not used as a last resort, there will be data loss;

1) Nomount
This mode only creates instances (the various memory structures and service processes that create Oracle instances, where 5 processes must start, DBWR, LGWR, Smon, Pmon, CKPT), do not load the database, and do not open any data files.

Close the database First

sql> start Nomount;

The startup process of the database is recorded in the warning trace file, which includes the database startup information, which is stored in the directory defined by the parameter backgound_dump_dest, and the name of the warning log is Alert_orcl.log

Go to directory to view warning log about startup Nomount process record

When testing the Nomount state, the data dictionary is open, indicating that the database dictionary is inaccessible in the Nomount state because the data dictionary needs to obtain the file information from the control file, and the control file is not open so it cannot be viewed.

However, the location of the control file can be obtained from the parameter file under Nomount because the parameter file is already open

2) MOUNT
This mode launches the instance, loads the database, and maintains the database shutdown state. There are two ways to launch a database into Mount state, one is to start the database directly to mount, and the second is to switch the database to mount state using the ALTER DATABASE mount if it has already booted into the Nomount State;

sql> Alert database mount;

Sql> Startup Mount

At this point we can view the data dictionary because the control file is already open

However, the data file (table, view) of the database cannot be accessed at this time, and the file is not open at this time.

3) OPEN
This mode will launch the instance, load and open the database, this is the normal startup mode, the user wants to do a variety of operations on the database must use open mode to start the database, boot to open state, there are two ways, one is to boot directly to the open state (using startup or startup Open), and the second is if the database is in Nomount or Mount state, you can switch to the open state via ALTER DATABASE open.

You can now access the data files

4) Force
This mode terminates the instance and restarts the database, which is a mandatory startup mode that is used only when a problem occurs on startup or shutdown, and has some risk of losing data and causing unexpected problems.

-Shut down the database

In contrast to starting the database sequence, there are three steps: Close the database (close closes the data file), unload the database (close control file dismount), and close the Oracle instance (SHUTDOWN). There are also several common modes of closing:
1) NORMAL
Normal shutdown, if there is no limit to the time to close the database, this is usually the way to close the database in normal mode, Oracle will do the following:

• Prevent any user from establishing a new connection;
• Wait for all currently connected users to actively disconnect;
• When all current users are disconnected, the database will be closed immediately;

2) TRANSACTION
Transaction shutdown, its first task is to ensure that all currently active transactions can be committed and that the database is closed in the shortest possible time. As a transactional shutdown, Oracle performs the following actions:

• Prevent users from establishing new connections and starting new transactions;
• Wait for all active transactions to commit, then disconnect the user;
• When all active transactions have been committed and the user disconnects, the database is closed;

3) IMMEDIATE
The immediate shutdown method, which allows the database to be shut down quickly and safely, is a common way for DBAs to shut down the database immediately, and Oracle does the following:

• Prevents users from establishing new connections and starting new transactions;
• Interrupts the current transaction and rolls back uncommitted transactions;
• Force disconnect all users and perform checkpoints to write dirty data to a data file;
• Close the database

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

Description of several Oracle startup modes-----------------------------------------------------------------------------1) Startup Nomount non-installation boot, this way start under executable: Rebuild the control file, rebuild the database read Init.ora file, start instance, that is, start the SGA and background process, this boot only need Init.ora files. 2) Startup Mount DBName installation starts, this way starts under executable: Database log archive, database Media Restore, bring data file online or offline, relocate data file, redo log file. Execute "Nomount", and then open the control file to confirm the location of the data file and the online log file, but the data and log files are not checked for verification at this time. 3) Startup Open dbname performs "Nomount" first, then executes "mount" and then opens all database files, including the redo log file, in such a way that the data in the database can be accessed. 4) startup, equal to the following three commands startup Nomountalter database mountalter database open 5) startup restrict constrained way to start this way can start databases, However, when only privileged users are allowed access to non-privileged users, the following prompt appears: Error:ora-01035:oracle only allow users with RESTRICTED SESSION permissions to use 6) startup Force forced Start mode when the database cannot be closed, you can use startup forces to complete the shutdown of the database, close the database, and then execute the normal startup database command 7) startup pfile= parameter filename with initialization parameter file start mode read the parameter file first,  Start the database example by setting the settings in the parameter file: Startup Pfile=e:\oracle\admin\oradb\pfile\init.ora 8) Startup EXCLUSIVE Several methods of closure of Oracle-----------------------------------------------------------------------------There are three ways to shut down: 1) shutdown Normal closes the database normally.   2) Shutdown immediate close the database immediately. In SvrmgrL executes shutdown immediate, the database does not shut down immediately, but it shuts down (terminates the session, frees the session resource) after Oracle performs some cleanup work, and when shutdown cannot shut down the database, shutdown Immediate can complete the operation of the database shutdown. 3) Shutdown abort directly shuts down the database, the session that is accessing the database is abruptly terminated, and if a large number of operations are executing in the database, it takes a long time to restart the database after shutdown abort is executed. Oracle shutdown process and start-up instead, it went through close, dismount, shutdown. Can be closed in step or directly. Step Close: 1) Close database: Close data files, log files, etc. Sql> ALTER DATABASE close; 2) Dismount database: Close the control file. Sql> ALTER DATABASE dismount; 3) Shutdown database: Close the instance. Shutdown has 4 parameters: Shutdown Normal (default), shutdown immediate (recommended), shutdown transactional, shutdown abortsql> The shutdown database is closed. The database has been uninstalled.  The ORACLE routine has been closed. The difference between the startup and shutdown modes of Oracle and their different functions-----------------------------------------------------------------------------1) Start and close Oracl E-database for most Oracle DBAs, the most common way to start and shut down an Oracle database is in the command-line mode of server Manager. Since Oracle 8i, the system has all the functions of Server Manager in Sql*plus, that is, starting and shutting down the database from 8i can be done directly through Sql*plus, and no longer requires Server Manager. However, the system retains the Server Manager tool in order to maintain backward compatibility. In addition, the graphical user tool (GUI) of Oracle Enterprise Manager can be used to complete the system startup and shutdown, the graphical user interface instance Manager is very simple, not detailed here. To start and close the database, you mustTo log in as a user with Oracle Administrator privileges, typically with users with SYSDBA permissions. In general, we often use internal users to start and close the database (internal user is actually a synonym for the sys user to SYSDBA connection). The new version of the Oracle database will phase out internal this internal user, so we'd better set the DBA user to have SYSDBA permissions. 2) Startup of database (startup) requires three steps to start a database:-> create an Oracle instance (non-installation phase), open the database (open phase), from the instance installation database (Installation phase) in the startup command, Different options are available to control the different startup steps of the database. 1) The STARTUP nomountnonount option creates only one Oracle instance. Reads the Init.ora initialization parameter file, initiates a background process, initializes the system global Zone (SGA). The Init.ora file defines the configuration of the instance, including the size of the memory structure and the number and type of background processes that are started. The instance name is set according to Oracle_sid and does not have to be the same as the open database name. When the instance is opened, the system displays a list of the SGA memory structure and size, as shown below:sql> startup Nomount;oracle routine has been started. Total System Global area 35431692 bytesfixed size 70924 bytesvariable size 18505728 bytesdatabase buffers 16777216 bytesre Do buffers 77824 bytes 2) STARTUP mount the command creates an instance and installs the database, but does not open the database. The Oracle system reads the contents of the control file about the data file and the redo log file, but does not open the file. This open method is often used in database maintenance operations, such as renaming a data file, changing the redo log, and opening the archive.  In this open mode, in addition to the list of SGA systems can be seen, the system also gives the \ "database load complete \" prompt. 3) Startup This command completes all three steps to create an instance, install an instance, and open a database. At this point the database makes the data files and redo log files online, and typically requests one or more rollback segments. In addition to the system can see the previous startup Mount mode of all the hints, but also give a \ "database has been opened \" prompt. At this point, the database system is in a normal working state and can accept user requests. If you pickWith the startup Nomount or startup Mount database Open Command mode, you must use the ALTER DATABASE command to perform the operation of opening the database. For example, if you open the database as startup Nomount, that is, the instance has been created, but the database is not installed and opened. This is required to run the following two commands for the database to start correctly. sql> ALTER DATABASE MOUNT; sql> ALTER DATABASE OPEN; If you start the database in the Startup mount mode, you can open the database by running the following command:sql> ALTER. 4) Other Open methods other options are available in addition to the three database open options described earlier. STARTUP restrict in this way, the database will be opened successfully, but only some privileged users (users with DBA roles) can use the database. This approach is often used to maintain the database, such as data import/export operations when you do not want other users to connect to the database operation data. Startup force the command is actually a synthesis of forcibly shutting down the database (shutdown abort) and starting the database (startup) two commands. This command is only used when the database is closed and the database is not closed. ALTER database open read only; This command opens the databases as read-only after the instance is created and the database is installed. A product database that only provides query functionality can be opened in this way.

Ii. Creating a database user

2.1  Log in to the database as a DBA user (such as System,sys) sql> Conn System/[email protected] Note: If you log on to the local server @orcl you can remove the 2.2  with Create User syntax creates users sql> create user_name identified by User_passworddefaulttablespace Tbs_users;user_ Name is the user name of the database user user_password the password for the database user tbs_users the tablespace used by the user, by default the Users table space. For example:sql> CREATE user Cmsuser identified by Passworddefaulttablespace users;2.3  use permissions sql> alter USER User_ Name Quota Unlimited on user_tablespace Quota Unlimited on user_tablespace;2.4  assigning permissions to the user sql> GRANT Connect, resource T O Cmsuser; Connect users can log in to the database Permissions resource users can create some database to the image of permissions, tables, views, stored procedures, is generally granted to developers 2.5  Delete user sql> dropuser cmsuser Cascade; Use the cascade parameter to remove all objects for that user

Oracle Daily Operations Summary-(1)