First, connect Oracle
1. Oracle's process of establishing a connection
Whether you connect to Oracle remotely via the Sqlplus command or a third-party tool, you need to establish a connection between the client and the server. The Oracle Net Service component is intended to be used to establish the connection, which is already installed when Oracle is installed.
1) There is a listener listener on the server side that listens for connection requests from the client
2) Enter the user name, password, and network service name on the client to send the connection request
3) The client views the local Tnsnames.ora file and maps the network service name to the connection descriptor that contains the Oracle server address listener port and the global database name
4) The client locates the listener based on the descriptor and sends the connection information to the listener over the network
5) Listener Query Listener configuration file Listener.ora, find the server to connect to
6) client and server-side start communication
2. Three ways to configure the listener
1)
NETCA
2)
Netmgr
3) Manually modify the configuration file
$ORACLE _home/network/admin/listener.ora。
And then execute
lsnrctl start Listener1Start the Listener
3. Monitoring startup and DB instance start-up sequence
1) It is recommended to start the listener before starting the DB instance.
2) If you choose to start the instance first, and then start the listener, you will need to wait a while. or modify the Listener.ora file
4, the Client Oraclient lite on the configuration
Modify the $/oracle/product/11.2/client_lite/network/admin/tnsnames.ora file
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = Oracle) (port = 1521))
(Connect_data =
(SERVER = dedicated)
(SID = ORCL)
）
)
Note the host name of the Oracle database is populated at the host.
Ii. Control of document management
1, the role of control files at the start
1) record the name and creation time of the database
2) record the name, location, online, and offline status of the data file
3) contains the name, location, and archive information of the redo log file
4) All tablespace information, current log sequence number, recent checkpoint information
2. View Control File information
select name from V$controlfile;Or
Select Name,value from V$parameter where name= ' control_files ';
3. View Control file contents, use data dictionary v$controlfile_record_section to view
Select type,record_size,records_total,records_used from V$controlfile_reco
rd_section;
4. Store multiple control files
1) Enter the database
alter system set
Control_files= '/opt/oracle/oradata/orcl/control01.ctl ',
'/opt/oracle/oradata/orcl/control02.ctl ',
'/opt/oracle/oradata/orcl/control03.ctl ',
Scope=spfile;
2) Close the database
Shutdown Immediate
3) Copy the original control01.ctl, generate Control02.ctl and control03.ctl (note that the path should be pre-configured the same)
4) Start the instance again
Startup
5. Recovery control File
If the control files Control01.ctl and Control02.ctl are damaged, the database can be restored by CONTROL03.CTL.
alter system set control_files= '/opt/oracle/oradata/orcl/control03.ctl ' scope=spfile;
Three, redo log file management
1, Cause: Redo log file also becomes online redo log file, its function is for data recovery. To improve the efficiency of writing data, it is not a data change that immediately writes data to a data file. Frequent writes to a data file that reads a disk can make the system inefficient, so it waits until the data buffer reaches a certain amount of data before writing. However, this situation is faced with a problem, that is, when the data in the buffer, not written to the data file, the server is down, then the buffer data will be lost.
Workaround: Redo log files will first save the changed data.
2. Redo log File structure:
Specifies that there are at least two redo log filegroups with one or more log group members in each group. When a redo log filegroup is filled with logs, it is written to another group, and the log file group is recycled.
3. View Redo Log Membership Information
select Group#,sequence#,bytes,members,archived,status from V$log;Or
select Group#,status,type,member from V$logfile;
4. Create Redo log file group
First under the root user:
mkdir-p/backup/orcl/log/
Using Oracle users to log on to Oracle:
ALTER DATABASE add logfile Group 4
('/opt/oracle/oradata/orcl/redo04a.log ',
/backup/orcl/log/redo04a.log ') size 10m;
Delete a log file group
ALTER DATABASE drop logfile Group 4;
Iv. Archive log files
1, Oracle database operation mode is divided into archiving and non-archiving methods. As the name implies, in non-archive mode, the log member switch overwrites the previous redo log file directly, and the archive mode will archive this part of the redo log file before switching. However, archiving mode has some performance implications for database operations, and by default Oracle uses non-archive mode
2. Configuring the Archive Log
1) Querying the current archive mode
archive log list;
2) Close the DB instance to mount State
shutdown immediate;
startup Mount;
3) Set the archive mode
ALTER DATABASE archivelog;
V. Data dictionary
1. What the data dictionary means: A data dictionary is a collection of tables and views where Oracle stores critical information. The Oracle process maintains these tables and views in sys user mode, which means that the owner of the data dictionary is sys, which is stored in the system table space. Data dictionary is installed by default when Oracle is installed, no data dictionary Oracle does not function
2. The data dictionary consists of a data dictionary table and a data dictionary view. Data dictionary table data is the system data stored by the Oracle system, in order to facilitate the distinction, the data dictionary table name with ' $ ' end; Oracle has set up a user view for these data dictionary tables for easy querying.
1) Data dictionary view
• Static data dictionary View: mainly divided into user
*, all
and Dba_。
• Common, static data dictionary views
User_tables: Information for all tables under the current user
User_indexes: All indexes under the current user
User_views: All Views under the current user
User_objects: Database objects under the current user (including tables, views, triggers, indexes, stored procedures, and so on)
User_users: Information for the current user
All_objects: All tables, procedures, etc. that the current user can access
2) Dynamic Data dictionary View--"v$ View"
These Dynamic Data dictionary views are constantly updated, dynamically displaying the activity of the current database, and information from the Dynamic Data dictionary view is derived from the memory and control files.
Common examples:
Select from v$fixed_table where name like ' v$log% ';
Information related to querying and log files
select Group#,members,archived,status from V$log;
Querying Log Group Information
Select from V$logfile;
Querying redo log File information
Oracle Configuration Management
