Oracle Instance Management

go to "Little health " and leave it only as a record.

• Starting and stopping Oracle databases and components
• Management framework

• Start and stop the database control
• Start Emctl start Dbconsole
• Stop Emctl Stop Dbconsole
• View state emctl Status Dbconsole

• Turn on and stop listening
• Start Lsnrctl Start
• Stop Lsnrctl Stop
• View state lsnrctl Status

• Isql*plus and Sql*plus (1. Perform database management operations 2. Querying, inserting, updating, or deleting data in the database by executing SQL commands)
• Isql*plus (browser-based, no user with DBA authority is allowed to log on by default)
• Start Isqlplus service first before starting Isqlplusctl start
• Enter the URL http://host name:port/isqlplus (default port number 5560) in the browser
• Modify Port Modification $oracle_home/install/portlist.ini file
• Sql*plus (Command line tool)
• Use:
1. Input, edit, run, store, retrieve, and save SQL commands and PL/SQL blocks
2. Format, calculate, store, and print query results
3. List column definitions for any table
4. Send a message to end users or accept a response from an end user
5. Perform database management
• Start and exit
1. Open the terminal and switch to Oracle user
2. No user sqlplus/nolog Ordinary user sqlplus Username/password DBA sqlplus/as sysdba
3. Switch users to use conn such as: Now logged in with a normal user, switch to DBA Conn/as SYSDBA
4. Quit quit
• Call Sql*plus from a Shell script for example: Call:./hr.sh

• 1 #hr. Sh
  2 Sqlplus hr/hr <<eof
  3 Select COUNT (*) from employees;
  4 Update Employees Set salary =
  5 salary*1.10;
  6 commit;
  7 quit
  8 EOF
  9 exit

  　　

• Call SQL script from Sql*plus: $sqlplus hr/hr @hr. sql or sql> @hr. sql

• 
  
  3 where employee_id=101;

  　　

• Start EM

1. Start Dbconsole emctl start Dbconsole
2. Start monitoring Lsnrctl Start
3. Start the database Sql>startup
4. Login
5. Role:
• View alerts overview and performance graphs
• Creating and modifying objects
• Perform backup and restore

Database Initialization Parameters

• Parameter file
1. Server parameter file (preferred): A binary file that is written or read by the database server and cannot be edited manually. This file resides on the server that executes the Oracle database and is always present, unaffected by database shutdown or startup. The default name is Spfile<sid>.ora
2. Text parameter file: read by the database server, but cannot be written by it. You must manually set and change the initialization parameter settings using a text editor. The default name is Init<sid>.ora
• viewing and modifying parameters
1. EM: Manage-All initialization parameters
2. sql* Plus
• Display parameters show parameter []
• Modify the parameter alter system set Sga_target =150m scope=both/spfile/memory
• SPFile: Writes the initialization file, and the change will take effect on the next startup. Dynamic parameters, static parameters are all the same. The only way to modify static parameters
• Memory: Only changes in RAM, effective immediately, but after reboot will no longer take effect, only suitable for dynamic parameters
• Both:spfile+memory

Database Startup and shutdown

Start sql>startup [option]

• Nomount
• Process
1. Search for files with specific names in <oracle_home>/dbs in the following order:
• Spfile<sid>.ora
• If it is not found, search for Spfile.ora
• If it is not found, search for Init<sid>.ora
2. Allocate SGA
3. Start a background process
4. Open alert<sid>.log files and trace files
• Role
1. During database creation
2. During the re-creation of the control file
3. During certain backup and recovery scenarios
• Mount
• Process
1. Associating a database with a previously launched instance
2. Locate and open the control file specified in the parameter file
3. Gets the name and status of the data file and the online redo log file by reading the control file. However, you do not have to perform any checks at this time to verify that the data file and the online redo log file exist
• Role
1. Renaming a data file
2. Enabling and disabling online redo log file archiving options
3. Perform a full database recovery
• Open (verifies that all data files and online redo files can be opened) (default)
• Process
1. Open an online data file
2. Open the online redo log file
• Close sql>shutdown [option]
• Normal (default)
1. No new connections can be established.
2. The Oracle server waits for all users to disconnect before completing the shutdown.
3. The database and redo buffers are written to disk.
4. Background process is aborted, the SGA is removed from memory
5. The Oracle server shuts down and disconnects the database before shutting down the instance.
6. Instance recovery is not required for the next boot.
• Transactional
1. No client can start a new transaction with this particular instance
2. The client disconnects after the transaction is ended in progress.
3. Execution closes immediately after all transactions have been completed.
4. Instance recovery is not required for the next boot.
• IMMEDIATE
1. The current SQL statement being processed by the Oracle database is not yet complete.
2. The Oracle server does not wait for users who are currently connected to the database to disconnect.
3. The Oracle server rolls back the active transaction and disconnects all connected users.
4. The Oracle server shuts down and disconnects the database before shutting down the instance.
5. Instance recovery is not required for the next boot.
• ABORT
1. Immediately terminates the current SQL statement being processed by the Oracle database.
2. The Oracle server does not wait for users who are currently connected to the database to disconnect.
3. The database and redo buffers are not written to disk.
4. Uncommitted transaction processing is not rolled back.
5. The instance has been terminated, but the file has not been closed.
6. The database is not closed or uninstalled.
7. An instance recovery is required for the next boot, and the instance recovery is automatic.

Alert Logs

• Content: RELATED LINKS-Alert log content Alert_<sid>.log located on the database server, stored in the background_dump_dest initialization parameter specified directory
• History: RELATED LINKS-Early warning history

   Dynamic Performance View (The dynamic performance view enables you to access information about changing status and conditions in the database. ) (read may not be consistent )

• Content
• Session
• File status
• Progress of jobs and tasks
• Lock
• Backup Status
• Memory usage and allocation
• System and Session Parameters
• SQL execution
• Statistical information and metrics
• Example:

  Sql> SELECT sql_text, executions from V$sql
  WHERE cpu_time > 200000;

  　　

  
  ' Edrsr9p1 ' and Logon_time > SYSDATE-1;

  　　

  
  Block > 0;

  　　

Oracle Instance Management