Oracle Database startup and shutdown Methods

After sorting out "Oracle database provides several different database startup and shutdown methods", I wrote an example when I went back.

One problem occurs:

ORA-12528: TNS: listener: all appropriate instances are blocking new connections

So I went to the Internet to check a lot of information. Haha, it's good. The solution was found. The red one is the main solution. Here I will share my work.

I. Notes

To start and shut down a database, you must log on as an Oracle administrator. Generally, you must Log On As a sysdba user. Generally, INTERNAL users are often used to start and shut down databases (INTERNAL users are actually SYS users using SYSDBA to connect ). The new version of Oracle Database will gradually eliminate the INTERNAL user INTERNAL, so we should set DBA users to have SYSDBA permissions.

2. Three steps are required to start a database.
1. Create an Oracle instance (not in the installation phase)
2. Database Installation By instance (installation phase)
3. Open the database (open stage)

Iii. Startup (Database Startup command)

1. STARTUP NOMOUNT
The NONOUNT option only creates an Oracle instance. Read the init. ora initialization parameter file, start the background process, and initialize the system global zone (SGA ). The Init. ora file defines the instance configuration, including the memory size and number and type of background processes to be started. The instance name is set according to Oracle_SID and does not have to be the same as the name of the opened database. When the instance is turned on, the system displays a list of SGA Memory Structures and sizes, as shown below:
SQL> startup nomount
The ORACLE routine has been started.
Total System Global Area 35431692 bytes
Fixed Size 70924 bytes
Variable Size 18505728 bytes
Database Buffers 16777216 bytesu
Redo Buffers 77824 bytes

2. STARTUP MOUNT
This command creates an instance and installs the database, but does not open the database. The Oracle System reads the data file and rewrites the log file in the control file, but does not open the file. This method is often used in database maintenance operations, such as renaming data files, changing duplicate logs, and opening and archiving methods. In this mode, in addition to the SGA system list, the system also displays the "database Load completed" prompt.

3. STARTUP
This command completes three steps: Creating an instance, installing an instance, and opening a database. In this case, the database makes the data file and the duplicate log file online, and usually requests one or more rollback segments. In this case, in addition to all the prompts in the Startup Mount mode, a prompt "the database has been opened" is displayed. In this case, the database system is in normal operation and can accept user requests.

If you use startup nomount or startup mount to open a DATABASE, you must use the alter database command to open the DATABASE. For example, if you use startup nomount to open a database, that is, the instance has been created but the database has not been installed or opened. You must run the following two commands to start the database correctly.
Alter database mount;
ALTER DATABASE OPEN

4. Other open methods
In addition to the three options for opening a database, there are also some other options.
(1) STARTUP RESTRICT
In this way, the database is successfully opened, but only privileged users (users with DBA roles) are allowed to use the database. This method is often used to maintain the database. For example, you do not want other users to connect to the database for data import/export operations.
(2) STARTUP FORCE
In fact, this command is a combination of two commands, shutdown abort and startup. This command is used only when the database is shut down and cannot be shut down.
(3) alter database open read only;
This command opens the database in read-only mode after creating an instance and installing the database. This method can be used to open product databases that only provide the query function.
4. Shut down the database)
1. SHUTDOWN NORMAL
This is the option for saving the database SHUTDOWN command. That is to say, if you issue a command like SHUTDOWN, it means shutdown nornal.
After this command is issued, no new connections are allowed to connect to the database. Before the database is closed, Oracle will close the database only after all currently connected users exit from the database. Close the database in this way, and no instance recovery is required at the next startup. However, it may take several days or longer to close a database.
2. SHUTDOWN IMMEDIATE
This is a common method for shutting down databases. This method is often used to quickly shut down databases, but to make them clean and shut down.
The SQL statement being processed by Oracle is immediately interrupted, and any transaction not committed in the system is rolled back. If there is a long uncommitted transaction in the system, it will take some time to close the database in this way (the transaction rollback time ). The system does not wait to connect to all database users to exit the system, forcibly roll back all active transactions, and then disconnect all connected users.
3. SHUTDOWN TRANSACTIONAL
This option is only available after Oracle 8i. This command is often used to plan to close the database. It makes the current connection to the system and the execution of active transactions complete. After running this command, any new connections and transactions are not allowed. After all the active transactions are completed, the database will be shut down in the same way as shutdown immediate.
4. SHUTDOWN ABORT
This is the last move to shut down the database. It is also the method that has to be used only when there is no way to shut down the database. If the following situations occur, you can consider using this method to shut down the database.

The problem is solved as follows:
ORA-12528: TNS: listener: all appropriate instances are blocking new connections
1: Modify parameters of listener. ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C: \ oracle \ product \ 10.1.0 \ db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ammicly)
(ORACLE_HOME = c: \ oracle \ product \ 10.1.0 \ db_1)
(SID_NAME = ammicly)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = lypch) (PORT = 1521 ))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC ))
)
)
)

2: Modify the parameters of tnsnames. ora

AMMICLY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = lypch) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ammicly)
(UR =)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC ))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
Start Database
SQL> conn/as sysdba
SQL> alter database mount;
SQL> alter database open;

Close Database
SQL> shutdown immediate;