Database development: Oracle Learning Notes

Instance

1. Start Oracle Server.
　　
Startup[nomount|mount|open][force][pfile=filename]

Parameter description:

Nomount: Only Start instance

Mount: Start the instance and mount the database

Open: Launches the instance, mounts and opens the database.

Force: Terminates the instance and restarts the database. (default option)

Pfile: Specifies a non-default parameter filename.

2. Stop Oracle Server

Shutdown [Normal|transactional|immediate|abort]

Parameter description:

Normal: Waits for a user to disconnect an existing connection, the system issues checkpoints, and then synchronizes data files, control text

and redo log files after closing (default option)

Transactional: After waiting for the client to end the transaction, disconnect the user automatically and then perform normal.
　　
Immediate: Cancels all current SQL statements, rollback all outstanding transactions, and then automatically disconnect, and then execute normal.

Abort: Terminates all SQL statements and terminates the instance immediately. No checkpoints are issued, so data files, control files, and redo log files are not synchronized.

3. Summary and related some of the scripts

A Modifying the data file name, performing a full recovery, performing an incomplete recovery, changing the log operation mode, the system must start the mount when the instance is started and the database is installed;
　　
B When you create a database or create a control file, the system is only in the Startup instance state (startup Nomount;)

C Display SGA size (show SGA or select * from V$SGA)

D Displays the background process that is currently running (select name from v$bgprocess where paddr!= ' 00 ')

E. Show Oracle Server status (select status from V$instance)

F. Displays initialization parameter information (SELECT * from V$parameter);

G Displays the physical file (select * from V$controfile, select * FROM V$datafile, select * from V$logfile);

H. Some data dictionaries attempt to:

All_objects, User_objects, Dba_objects, User_source, User_segments,

User_tab_columns, User_constraints, all_users

I. For client applications, you can access Oracle server, configure Listener.ora files on the server side, and configure Tnsnames.ora files on the client

Chapter III Control documents

1. The main information recorded in the control file:

Database name, database file name and location, redo log file name and location, table space name;

Current log sequence number, checkpoint information, log history information.

2. Diversity Control Document

1. Close Database

Sqlplus/nolog;

Connect system/admin@lgtest as SYSDBA;

Shutdown immediate;

2. Copy control file
　　
Copy source Controlfile to Dest Controlfile

For example: Copy E:\oracle\oradata\lgtest\CONTROL01. CTL F:\controltest.ctl

3. Modifying initialization parameters Control_files
　　
Control_files= E:\oracle\oradata\lgtest\CONTROL01. CTL, F:\controltest.ctl
　　
4. Start the database
　　
Startup pfile= E:\oracle\admin\lgtest\pfile\ init.ora.108200317263

Its role: A plurality of control files, as long as there is a control file has not been damaged, the other corrupted control files from the initialization parameter file to remove the reboot after the system can continue to run.

3. Ways to reconstruct control files

1. Start an instance in a Nomount way

Sqlplus/nolog;

Connect system/admin@lgtest as SYSDBA;

Shutdown normal;

Startup Nomount Pfile=%racle_home%\database\init.ora;

2. Establish control files

Create Controlfile Reuse Database Lgtest resetlogs

LogFile
　　
Group 1 ' E:\oracle\oradata\lgtest\ REDO01. LOG ' Size 20M,
　　
Group 2 ' E:\oracle\oradata\lgtest\ REDO02. LOG ' Size 20M
　　
DataFile
　　
' E:\oracle\oradata\lgtest\ SYSTEM01. DBF ',
　　
' E:\oracle\oradata\lgtest\ UNDOTBS01. DBF '
　　
Character Set ZHS16GBK;
　　
3. Open Database
　　
ALTER DATABASE open resetlogs;
　　
4. Additional Temporary files

A lter tablespace temp add tempfile ' E:\oracle\oradata\lgtest\TEMP01. DBF ' reuse;

Function: Once the control files are corrupted, as long as all the data files and redo log files are available, the system can continue to run by rebuilding the control files.