Different state of database startup

When you start the database
First to find the initialization file (SPFile or pfile), the database is in the Nomount state (the initial phase);
Secondly, the control file is found according to the initial file, and the database is in Mount state.
Then locate the database file (data file) and redo the log files (Redo file) according to the control file.
At this point the database is in open state, allowing the user to access the database.

Four different commands to close a database
1. Log in to the database with Sqlplus
sys/123456 as Sysdba
To view the status of the current database:
Select Instance_name,status from V$instance;
You can view the instance name and the current status:

2. Close the database

Shutdown immediate

In fact, the instance is closed (Instance) and the instance is no longer in memory, showing the state that the Oracle database is not available.

The 4 commands to close a database are as follows:
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. Things that don't end automatically rollback. There is no need for instance recovery of the database at startup, the most commonly used command, even if there is a transaction running, and the user stays on the database, you can close the database.
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. The database will be automatically recovered from the instance at startup. Rarely used, a general database when running complex business, the shutdown immediate command cannot shut down the database, and the need to quickly shut down the database is used.
Shurdown transactional: 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. The database does not require instance recovery at startup, and this command is rarely used, and when the business of the database is important, it needs to wait until the transaction is complete before it can be closed.
Shutdown normal (equals shutdown): Does not allow new connections, waits for a session to end, waits for a transaction to end, makes a checkpoint, and closes the data file. The database does not require instance recovery at startup. This command is rarely used, and only if the user stays on the database and does not shut down properly.

Second, start the database to Nomonunt state
Startup Nomount
Nomount state is the database process just running into the initialization file (spfile or pfile file)
Pfile files are text files that can be edited manually
SPFile is a binary file, can not manually modify the parameters inside, can only be modified with database commands, but also in the database run is to modify the parameters inside the SPFile, improve the security of the file. The database uses SPFile to start the database by default.
Nomount state is generally used to maintain the database, such as modifying the database important parameters, the recovery of the database control files and data need to use this state.
To view the operation of the SPFile file:
Sql>show parameter SPFile

F:\APP11G\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCL11G. ORA

Create a Pfile file based on the SPFile file
Sql>create pfile= ' D:\app\pfile.ora ' from SPFile;
Open the Pfile file to see the important parameters of the database
orcl11g.__db_cache_size=1509949440
orcl11g.__java_pool_size=16777216
orcl11g.__large_pool_size=16777216
Orcl11g.__oracle_base= ' F:\app11G\Administrator ' #ORACLE_BASE set from environment
orcl11g.__pga_aggregate_target=1358954496
orcl11g.__sga_target=2030043136
Orcl11g.__shared_io_pool_size=0
orcl11g.__shared_pool_size=452984832
Orcl11g.__streams_pool_size=0
*.audit_file_dest= ' F:\app11G\Administrator\admin\orcl11G\adump '
*.audit_trail= ' DB '
*.compatible= ' 11.2.0.0.0 '
*.control_files= ' F:\app11G\Administrator\oradata\orcl11G\control01.ctl ', ' F:\app11G\Administrator\flash_ Recovery_area\orcl11g\control02.ctl '
*.db_block_size=8192
*.db_domain= "
*.db_name= ' orcl11g '
*.db_recovery_file_dest= ' F:\app11G\Administrator\flash_recovery_area '
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest= ' F:\app11G\Administrator '
*.dispatchers= ' (protocol=tcp) (SERVICE=ORCL11GXDB) '
*.memory_target=3382706176
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile= ' EXCLUSIVE '
*.undo_tablespace= ' UNDOTBS1 '

Shutdown immediate re-use Pfile file to start database to Nomount state later

Startup Nomount pflie= ' D:\app\pfile.ora ';

After booting to the Nomount state, enter the query statement
Select name from V$controlfile;
Error, indicating that the database cannot view the path of the control file in the Nomount state.
When the Oracle database starts, the process reads the initialization SPFile file, which is one of the database's important files.

Third, the Mount state of the database
In the Mount state of the database, the Oracle process reads the parameters in the control file, which is required when the database is restored, and the database is in Mount state when the database is in the standby repository (Dataguard) of the configuration database.
ALTER DATABASE Mount;
Select Instance_name,status from V$instance;

The database display is in the mounted state, indicating that Oracle is already in the Mount State
To view the specific location of the control file:
Select name from V$controlfile;

Open the control file is a binary file and you cannot view its contents with Notepad.

You can use Oracle's commands to display the contents of the control file in Trace (trace):
ALTER DATABASE backup Controlfile to trace as ' D:\app\ct1.trc ';
You can use Notepad to open the Ct1.trc file, to view the contents of the control file inside, where '--' as a comment, the other content is the control of the contents of the file, according to the control file to reconstruct the database control file, the file shows the redo log (Redo Logfile) group location and its size, Data file location, character set, and so on.

To boot the database into the Mount State operation:
Close the database first: shutdown immediate;
Boot to mount Status: Startup Mount;

Iv. open state of the database
The database open state indicates that the database is running properly, and allows database users to access the database, the data can be increased and censored operations, the database can work properly.
To change the database to an open state:
ALTER DATABASE open;
To view the data file location for the database:
Select name from V$datafile;

To view the Redo log location for the database:

Select member from V$logfile;

Five, the database password file

The password file location in the Oracle database on the Windows system:
F:\app11G\Administrator\product\11.2.0\dbhome_1\database
\pwdorcl11g.ora (orcl11g-bit database name)
Linux and Unix systems named ORAPWORCL (ORCL for database names)
The role of the password file is to allow the database SYSDBA, sysoper users to telnet through the password, such as SYS for the database of SYSDBA users.
To view the "Allow remote login" parameter in the database:
Show parameter remote_login_passwordfile;

Remote_login_passwordfile to none means remote; for exclusive, you can remotely

Alter system set Remote_login_passwordfile=none Scope=spfile;
Restart the database to take effect, enter the Force database restart SQL command
Startup force;

Vi. Enquiry and Description table space
Log in with SYS account query:
Select
Tablespace_name as "tablespace name",
block_size/1024 as "Data block storage size Unit KB",
Status as "Table Space state",
Contents as "Type of table space",
Logging as "whether there is a log record"
From Dba_tablespaces;

Tablespace_name as "tablespace name",

block_size/1024 as "block storage size Unit KB",--generally by default is 8KB, if you need to store large objects can be set to 18K
Status as "Table Space state",--online table space, offline offline
Contents as "type of tablespace",--Permanent permanent Save, undo restores the tablespace data, equivalent to the Recycle Bin, only the most recently changed data, temporary is generally temporary tablespace data, is not permanently stored in the system data file, Use the change space to temporarily store the data only when the query or sort is not enough memory
Logging as "Whether there is logging"-the table space that is important to keep the data, generally logging state, need to log related logs, such as temporary table space or non-important table space do not need to record.

Vii. Query and description data file
View the tablespace, size, scale, and maximum value of a data file
Select
Tablespace_name as "tablespace name",
file_name as "Data file path",
bytes/1024/1024 as "Data file size MB",
Autoextensible as "Data files are automatically extended",
maxbytes/1024/1024 as "Data file Max MB"
From Dba_data_files;

Table spaces and data files are one-to-many relationships.

Data file path: is the disk location where the data file system of the Oracle database resides;
Data file Size: You can see how much space each data file now uses;
Whether to automatically expand: In order to save disk space, the data files are set to a relatively small, when the data is increasing, whether the file is automatically expanded until the maximum set value;
Maximum data file size: and the maximum capacity of the data file. Can be set according to the specific needs, if default, according to the system allows the maximum value of a single file.

Viii. Query and Description redo log
To view the location, status, size, and type of redo log:
SELECT * from V$logfile;

group# (Group): You can see that there are 3 groups of redo logs in the database, with only one file per group in the graph

Status: Stale has submitted the data to the database, the blank state is using the file
Member (member): is a member of each group (there is only one member for each group) and lists the path and name.

Different state of database startup