About orcale (3)

Post:
1. connect to your Oracle Database
SQL> sqlplus/nolog
SQL> connect/As sysdba
Ii. modify Oracle account information
1. Modify the account password and use the following statement:
SQL> alter User Username identified by password
2. Use the following statement to modify the password of a locked account:
SQL> alter User Username identified by password account unlock
3. If you want to lock your account, use the following statement:
SQL> alter User Username account lock
4. To unlock an account, use the following statement:
SQL> alter User Username account unlock
3. Create an account and grant sysdba Permissions
1. Create a user (username = yzq, password = yezhuangqiao) and use the following statement:
SQL> create user yzq identified by "yezhuangqiao" account unlock
2. Grant sysdba and sysoper permissions to the yzq User:
SQL> grant sysdba to yzq;
SQL> grant sysdba to yzq;
3. Use the following statement to revoke User Permissions:
SQL> revoke sysdba from yzq;
SQL> revoke sysoper from yzq;
4. Test the login Connectivity:
SQL> connect yzq/yezhuangqiao as sysdba
4. Oracle startup or login Methods
1. Start the instance and do not load the database (nomount Mode)
This mode only creates instances and does not load databases. Oracle only creates various Memory Structures and service processes for instances and does not open any database files.
If you want to perform the following maintenance tasks, you must do so in nomount Startup Mode:
A. Create a new database
B. Recreate the control file
The following shows the process of starting the database to enter nomount mode in SQL * Plus:
SQL> startup nomount
2. Start the instance to load the database, but do not open the database (Mount Mode)
If you want to perform the following maintenance tasks, you must do so in Mount Startup Mode:
A. Rename the data file.
B. add, delete, or rename the redo log file.
C. Perform full database recovery.
D. Change the database archiving mode.
The following shows the process of starting the database to enter the Mount mode in SQL * Plus:
SQL> startup Mount
3. Start the instance to load and open the database (Open Mode)
This is the normal startup mode. For normal database users to operate databases, such as connecting to and querying databases, the database must be in open startup mode.
The following shows the process of starting the database to open mode in SQL * Plus:
SQL> startup
2. Use the following statement to connect to Oracle as a dba:
(1) operating system authentication
SQL> connect/As sysdba
(2) password file authentication method
SQL> connect username/password as sysdba
Username/password must be a database user account with sysoper or sysdba permissions, such as sys or system.
V. Switch the Startup Mode
1. load databases for instances
When performing some special management and maintenance operations, you need to enter the nomount startup mode. After completing the operation, you can use the following statement to load the database for the instance and switch to the Mount Startup Mode:
Alter database Mount;
2. From the loading status to the open status
After the database is loaded for the instance, the database may still be closed. To enable the user to access the database, use the following statement to open the database and switch to the Open start mode:
Alter database open;
4.1.7 limited switching and read-only status
1. Restricted status
In normal start mode, that is, in open start mode, you can choose to set the database to a non-restricted or restricted State.
In a restricted State, only users (DBAs) with administrative permissions can access the database. When the following database maintenance operations are required, the database must be placed in a restricted State:
A. Import or export data.
B. use SQL * loader to extract external databases.
C. Temporarily refuse normal users to access the database.
D. Port the database or upgrade the database.
Use the following statement to start the database. The database enters the restricted open mode:
Startup restrict
If you need to restore the database to a non-restricted State after completing the management operation, you can use the alter System statement to change the database state. the user who executes the statement must have the alter system permission. For example:
Alter system disable restricted session;
If you need to switch from the unrestricted status to the restricted status during database operation, you can also use the alter System statement. For example:
Alter system enbale restricted session;
2. Read-Only status
When the database is read-only, you can only query the database, but cannot modify the database objects in any way.
You can use the alter database statement to switch to read-only mode when the database is running. The user who runs the statement must have the alter database system permission. For example:
Alter database open read only;
You can also use the alter database statement to reset the database to read/write status, for example:
Alter database open read write;
4.1.8 Force start the database
In some cases, you may not be able to start the database successfully using the above startup modes. In this case, you need to forcibly start the database. In the following situations, you must forcibly start the database:
A. You cannot use the shutdown normal, shutdown immediate, or shutdown transactional statements to close the database instance.
B. An unrecoverable error occurs when the instance is started.
In this case, you can forcibly restart a new database instance for troubleshooting. To forcibly start a database, you must use the startup statement with the force clause, for example:
Startup force;
4.2.2 normal close mode (normal)
If there is no limit on the time for shutting down the database, the database is usually closed in a normal way. The shutdown statement with the normal clause will close the database normally. The following shows how to disable SQL * Plus in Normal Mode:
SQL> shutdown normal;
When the database is shut down normally, Oracle will perform the following operations:
A. Stop any user from establishing new connections.
B. Wait for all currently connected users to disconnect. Connected users can continue their current work and even commit new transactions.
C. Once all users are disconnected, immediately close and unmount the database and terminate the instance.
4.2.3 immediate close (immediate)
Immediately shut down the database in the shortest time. You must immediately close the database in the following situations:
A. Automatic Data Backup will be started soon.
B. Power supply is about to be interrupted.
C. When an exception occurs in the database itself or a database application, you cannot be notified to take the initiative to disconnect the database, or you cannot perform the disconnection operation at all.
The shutdown statement with the immediate clause will immediately shut down the database, for example:
Shutdown immediate
When you close the database immediately, Oracle will perform the following operations:
A. Stop any user from establishing new connections and stop the current connected user from starting any new transactions.
B. Any uncommitted transactions are rolled back.
C. Oracle no longer waits for the user to actively disconnect, close and unmount the database directly, and terminate the instance.
4.2.4 transaction closure method (transactional)
The transaction mode is between the normal mode and the immediate mode. It can close the database as short as possible, but all the active transactions can be committed. When you close a database using transactions, Oracle will wait until all uncommitted transactions are completed before closing the database.
The shutdown statement with the transactional clause will shut down the database as a transaction, for example:
Shutdown transactional
When the database is closed in transaction mode, Oracle performs the following operations:
A. Stop any user from establishing new connections and stop the current connected user from starting any new transactions.
B. Wait until all uncommitted active transactions are submitted, and then disconnect the user immediately.
C. Close or uninstall the database and terminate the instance.
Tip: the transaction close method ensures that the user does not lose the current work information, and can close the database as soon as possible. The transaction close method is also the most common database close method. If you close the database as a transaction, you do not need to recover the database the next time you start the database.
4.2.5 terminate close (abort)
If none of the above three methods can be used to close the database, it indicates that the database has encountered a serious error. In this case, you can only terminate the database. Terminating or disabling the database will lose part of the data information and cause damage to the integrity of the database. You need to restore the database at the next time you start the database. Therefore, try to avoid terminating the database.
In the following special circumstances, the termination and close method should be used:
A. An exception occurs in the database itself or a database application, and other close methods are ineffective.
B. Shut down the database immediately in case of an emergency (for example, the power supply will be interrupted within one minute if notified ).
C. errors are generated when the database instance is started.
The shutdown statement with the abort clause will terminate the database. For example:
Shutdown abort
When the database is shut down in the termination mode, Oracle will perform the following operations:
A. Stop any user from establishing new connections and stop the current connected user from starting any new transactions.
B. Terminate the SQL statement that is being executed immediately.
C. No uncommitted transactions will be rolled back.
D. Disconnect all users immediately, close or detach the database, and terminate the instance.
If you terminate the database, the database must be restored the next time you start the database because the transaction is not rolled back.
4.3.1 silent status
1. Silent status features
Sometimes DBAs need to put the database in silent State. In silent State, only users with DBA permissions can perform query and update operations in the database and run PL/SQL programs. No non-DBA user can perform any operations in the database.
When performing some operations, DBA must exclude the activities of other users. This type of operation includes the following two types:
A. Some operations may fail if other users access the objects during execution. For example, if a DBA adds a new field to a table and a user is using the table, the operation fails.
B. The intermediate results of some operations during execution should not be seen by other users. For example, assume that DBA needs to perform one operation in multiple steps: first, export the data of a table, delete the table, and then re-import the database to create a new table, to recreate the table. If a user accesses the table before deleting the table and recreating the table, an error is returned.
If the silent function is not available, you must first shut down the database before enabling the database in restricted mode to successfully complete the preceding operations. The cost of doing so is very high, especially in environments that must ensure uninterrupted database operation (7x24 system ). The same purpose can be quickly achieved by enabling the database to enter the silent State, but the operation of the database does not need to be interrupted.
Note: in Oracle9i, only DBA users sys and system are allowed to perform operations in silent State, even if other users are granted the DBA role or sysdba permission, it cannot be operated in a silent database. Therefore, the silent State is more "clean" than the restricted State.
2. Enter the silent State
When the database is running, the following statement enters the silent State:
Alter system quiesce restricted;
Note: Only sys and system users have the permission to execute the alter system quiesce restricted statement. Other users with the DBA permission cannot set the database to silent.
In silent State, the SQL statement is suspended even if a non-DBA user tries to execute an SQL statement to forcibly activate a session. When the database recovers from the silent State, the stopped session continues to be executed, and the previously suspended SQL statement continues to be executed.
It may take a long time to wait until all non-DBA user sessions are terminated. During this process, if the session executing the alter System statement is accidentally terminated, the operation that enters the silent State will be canceled, and the paused session will be restored.
3. Exit the silent State.
If the database is in the silent State, execute the following statement to restore the silent State to normal:
Alter system unquiesce;
4. query silent status
You can use dynamic performance to try V $ instance to check whether the current database is in silent State. The active_status field in the V $ instance view shows the current activity status of the database:
A. Normal normal (non-silent ).
B. quiescing is in silent State (there are still active non-DBA user sessions ).
C. quiesced silent State (no active non-DBA user sessions ).
The following example shows how to change and query the silent State of a database:
SQL> alter system quiesce restricted;
The system has been changed.
SQL> select active_state from V $ instance;
Active_st
---------
Quiesced
SQL> alter system unquiesce;
The system has been changed.
SQL> select active_state from V $ instance;
Active_st
---------
Normal
4.3.2 suspension status
1. Features of pending status
When the database is suspended, all the physical files (control files, data files, and redo log files) of the database are suspended. This ensures that the database is physically backed up without any I/O operations. The difference between the suspended state and the silent State is that it does not prohibit non-DBA users from performing database operations, but temporarily stops all user I/O operations.
When the database is suspended, you can first create a hard disk image for the database and then separate the backup files from the image. This provides an alternative for database backup and recovery. For example, the database is originally running in a system with a hard disk image, but the image file cannot be separated when hard disk I/O operations exist. In this case, you can first set the database to the suspended state, and then perform the separation operation when you stop all I/O operations.
When the database is suspended, all the current I/O operations can continue, but all newly submitted I/O operations will not be executed, but will be placed in a waiting queue. Once the database returns to the normal state, these I/O operations will be removed from the queue and continue to be executed.
2. The instance is suspended.
You can use the alter System statement to suspend a database. the user who executes the statement must have the alter system permission. For example:
Alter system suspend;
3. Exit the pending status
To restore the database from the suspended state, use the following statement:
Alter system resume;
4. query the pending status
You can use the dynamic performance view v $ instance to check whether the current database is suspended. The database_status field in the V $ instance view shows the current activity status of the database:
A. sushortded is suspended.
B. actived is normal (not suspended ).
The following example shows how to change and query the suspension status of a database:
SQL> alter system suspend;
The system has been changed.
SQL> select database_status from V $ instance;
Database_status
-----------------
Sushortded
SQL> alter system resume;
The system has been changed.
SQL> select database_status from V $ instance;
Database_status
-----------------
Active