Shulou(Shulou.com)06/01 Report--

Blogger QQ:819594300

Blog address: http://zpf666.blog.51cto.com/

Friends who have any questions can contact the blogger, the blogger will help you answer, thank you for your support!

Before, we have studied Microsoft's sqlserver and Oracle's mysql, and also understood the role of database systems. These two database management systems are suitable for background databases such as software, websites, games and so on. For example, we have built dynamic websites when we are learning mysql. But oracle is a very safe and perfect large-scale database management software, which has absolute advantages in telecom, banking, securities and other large-scale applications. So let's introduce the installation and basic use of oracle to the builders today. Oracle can be installed on windows and linux systems, but enterprises are more likely to install on linux, and then take redhat6.5 as an example.

First, install redhat6.5

1) choose simplified Chinese language (to better support the Chinese version of oracle)

2) Select all options in "Development" (all 5 options in Development hit √)

II. Preparation for the installation of oracle

1. System and configuration requirements

1) confirm the version of the linux system

2) physical memory: must be higher than 1G

Physical memory should not be less than 1GB, and 1200MB is recommended for VMWARE virtual machines. For the swap partition setting in the system, you should refer to the following convention: when the physical memory is 1GB~2GB, the swap partition is 1.5 to 2 times the physical memory; when the physical memory is 2GB~16GB, the swap partition is the same size as the physical memory; when the physical memory exceeds 16GB, the swap partition uses 16GB.

3) hard disk space: 5.65G is required for the enterprise version, and make sure that the partition space of the / tmp directory is not less than 1G. It is recommended that the total space should be more than 8G.

4) modify the hosts file after determining the hostname (modifying the hostname after installing oracle will cause database startup failure)

2. Configure yum and install the support package

You also need to install the pdksh package, but there is a compatibility conflict with ksh on the Redhat6.5 CD, so you need to install pdksh-5.2.14-37.el5_8.1.x86_64.rpm separately and install it using rpm.

3. Adjust kernel parameters

Kernel parameter tuning is reflected in the / etc/sysctl.conf file. It mainly includes some settings related to memory scheduling, port range, the number of open files, I _ swap O requests and so on, and the relevant values should not be lower than the installation requirements. After modification, execute the sysctl-p command to make the new configuration take effect immediately.

Detailed description of each parameter:

Fs.aio-max-nr:

This parameter limits the concurrency of outstanding requests and should be set to avoid the failure of the Istroke O subsystem.

Fs.file-max:

This parameter determines the maximum number of file handles allowed in the system, and the file handle setting represents the number of files that can be opened in the linux system.

Kernel.shmall:

This parameter controls the total number of pages of shared memory that can be used. The page size of Linux shared memory is 4KB, and the size of shared memory segments is an integral multiple of the size of shared memory pages. The maximum size of a shared memory segment is 16G, so the number of pages of shared memory needs to be 16GB/4KB=16777216KB / 4KB=4194304 (pages), that is, 16GB physical memory under 64Bit system. Set kernel.shmall = 4194304 to meet the requirement.

Kernel.shmmax:

Is one of the most important core parameters that defines the maximum value of a single shared memory segment. The setting should be large enough, and a low setting may result in the need to create multiple shared memory segments, which may lead to a decline in system performance. The main reason for the system decline is that when the instance is started and the ServerProcess is created, multiple small shared memory segments may lead to a slight degradation of system performance at that time (multiple virtual address fields need to be created at startup, and there will be some impact when the process is created to let the process "identify" multiple segments), but it will not be affected at other times.

Official recommended values:

32-bit linux system: the maximum preferred value is 4GB (4294967296bytes)-1byte, which is 4294967295. The recommended value is more than half of the memory, so if 32 is the system, the value is generally 4294967295.

64-bit linux system: the maximum preferred value is the physical memory value-1byte, and the recommended value is more than half of the physical memory. For example, in the case of 12GB physical memory, it is preferable to take 12 '1024' 1024-1 '12884901887.

Kernel.shmmni:

This parameter is the maximum number of shared memory segments. The default value of shmmni is 4096, which is generally sufficient.

Kernel.sem:

Take kernel.sem = 250 32000 100128 as an example:

250 is the value of the parameter semmsl, indicating the maximum number of semaphores that can be contained in a semaphore set.

32000 is the value of the parameter semmns, indicating the maximum number of semaphores allowed in the system.

100 is the value of the parameter semopm, which represents the number of operations that a single semopm () call can perform on a semaphore set.

The value of the parameter semmni represents the total set of system semaphores.

Net.ipv4.ip_local_port_range:

Represents the range of IPv4 ports that the application can use.

Net.core.rmem_default:

The default value that represents the size of the socket receive buffer.

Net.core.rmem_max:

Represents the maximum size of the socket receive buffer.

Net.core.wmem_default:

The default value that represents the size of the socket send buffer.

Net.core.wmem_max:

Represents the maximum size of the socket send buffer.

4. Oracle user requirements

Oracle requires a fixed user oracle, installation group oinstall, and management group dba. These accounts need to be established in advance. In addition, the basic directory / opt/oracle used to store oracle programs and database files should also be created in advance.

1) establish users and groups

2) establish relevant catalogs

5. The installation task of Oracle11g should be performed as the user oracle, and the environment configuration of oracle users should be adjusted appropriately to meet the requirements. The language type of the Oracle11g installation world depends on the environment variable LANG. If you want to install the interface in Chinese, you should make sure that the value of the LANG variable is "zh_CN.UTF-8". The Chinese installation interface is sometimes garbled due to lack of fonts, improper font configuration and other reasons. At this point, you can switch to the "en_US.UTF-8" English environment to avoid similar questions.

6. Set Shell limits for oracle users: in order to optimize performance, you need to add limit parameters for oracle users. The process session restriction can be realized by using the pam_limits authentication module, which can be enabled by modifying the PAM setting of the login program login.

Modify / etc/security/limits.conf to load the number of processes that user oracle can open and the number of files used by processes.

7. Configure oracle user resource restrictions, which will not affect the successful installation of oracle database. However, considering that poor sql statements take up server resources infinitely, resulting in no response to user requests for a long time, it is recommended to limit oracle user resources in advance.

8. Solve the problem of Chinese garbled code

When installing oracle11g in RHEL6.5, the Chinese installation interface is garbled. Solution:

Execute the following command from the Linux command prompt to install the font library to the appropriate location

9. Turn off the firewall and selinux, and set the permanent ip address and hostname (hostname and IP have been completed earlier)

Be sure to restart the system after all the above preparations are completed.

III. Formal installation of oracle

1. Download the oracle11g package to the system / home/oracle, and extract the package.

2. Authorization allows the user oracle to use the graphics terminal, for example, execute: xhost + oracle@localhost, which means that the user is allowed to access oracle from the local machine, or execute xhost + to remove all restrictions. Here you can execute xhost +.

3. Execute the installation program

4. Configure security updates

5. Installation options, select "create and configure database"

6. Select "Server Class" in the system class.

7. Select "single instance database installation" in the grid options.

8. Select "typical installation" for installation type. If you set passwords for various administrative accounts, or select a language version, set automatic backup, storage management, etc., you should select Advanced installation.

9. In the configuration of a typical installation, the administrator password must be set, and other parameters will be default, and the system will automatically create three users, namely: Super Admin sys,system and database owner sysman. When you select advanced installation, you can set passwords for three users respectively, otherwise the same administrative password will be used.

10. Create a product inventory and change the inventory directory to / opt/oracle/oralnventory so that oracle users have the right to write inventory information to the oracle database.

11. prerequisite check and summary, the following package status failed, because these packages are 32-bit packages, and our system is 64-bit, so ignore it and click "ignore all". Then click "next".

You can save the response file, and you can use this script to perform the installation when installing oracle later, so you don't have to configure so much. (saved as / home/oracle/db.rsp file, ".rsp file is the response file for oracle database and can be used to avoid manual interaction during installation")

12. Installation is under way.

13. When the "password Management" window pops up, click the "password Management" button to unlock the user scott. After the modification is completed, click the "OK" button.

14. Complete the installation

After the file copy and related deployment is complete, the execute configuration script dialog box pops up, switches to the root user when prompted, and executes the script accordingly, as shown below.

Finally, when you click the OK button, you will be prompted to access https://oracleserver:1158/em/ and use this address to access the OEM (Enterprise Manager) platform to manage the oracle database instance.

4. Verify the installation results

1. Access the OEM platform (OEM platform can be used for image management oracle, which is more convenient).

Enter: https://oracleserver:1158/em/, in the browser, enter the user name: sys, password 123456, and select SYSDBA as the connection identity.

2. Use sqlplus command line to access oracle database

Enter: sqlplus sys/123456 as sysdba in the command line interface

Or:

1) execute the HELPINDEX command to view all available commands

2) execute SHOWUSER to view the currently logged-in users

Configure oracle and OEM to start randomly

After completing the installation of the oracle11g database, the relevant server will be enabled automatically, but it does not mean that the oracle server will still be available after the next boot. The following describes the basic service components of oracle and how to write service scripts to control the automatic operation of the oracle database system.

According to the above installation process, the database software for oracle11g will be installed in the location north of the variable ORACLE_HOME. For example, / opt/oracle/product/11.2.0/dbhome_1/, where the various server component programs (including the sqlplus command) are located in the bin subdirectory.

The basic service components of the Oracle11g database are as follows: (note: oracle service components are best run as oracle users such as: su-oracle)

Lsnrctl: listener program, used to provide database access, listening to TCP 1521 port by default.

Dbstart, dbshut: database control program, which is used to start and stop database instances.

Emctl: manager control tool, which is used to control the opening and closing of OEM platform. OEM platform provides HTTPS access through port 1158 and TCP access through port 5520.

In order to facilitate the execution of the oracle11g service component program, it is recommended to further optimize the environment configuration of all users, supplement the PATH path, oracle terminal type and other variable settings. In addition, the / etc/oratab configuration file should be modified so that the database instance is automatically enabled when running dbstart.

Modify with root users

1. Modify / etc/oratab, which is as follows:

2. Modify the / etc/rc.local file by adding the following at the end of the file:

3. Set up the startup script for the oracle service: (you don't have to do it)

Although the entire oracle11g database system can be started and stopped through various service component programs, it is obviously not convenient enough. In the RHEL6 server, it is better to write the relevant operations as service scripts and give them to chkconfig tools to manage, so as to automatically start and stop the oracle database with the system on and off.

Vi/etc/init.d/oracle, enter the following:

Set permissions to the script and add it as a system service

After adding the oracle service, you can control the start, stop, view status, and restart of the oracle database through the start, stop, status, and restart parameters of the script.

Common system control commands

1. Execute the "lsnrctl start" command to start the oracle listener.

2. Execute the "lsnrctl status" command to view the status of the oracle listener.

3. Execute the "emctl stop dbconsole" command to close the OEM management console.

4. Execute "dbshut $ORACLE_HOME" command to stop the database instance.

5. Execute the "dbstart $ORACLE_HOME" command to start the database instance.

6. When using the sqlplus command tool, if you log in as Super Admin sys, you can also enable and close the default database instance by executing the "STARTUP;" and "SHUTDOWN IMMEDIATE;" instructions in the "SQL >" environment.

Configure oracle command line to turn the page and clear the screen

1. Download 3 packages:

1) IO-Tty-1.07.tar.gz

2) Term-ReadLine-Gnu-1.16.tar.gz

3) uniread-1.01.tar.gz

2. Installation

1) decompress

2) install the first package

3) install the second package

4) install the third package

3. Log in to oracle: add uniread before the command

The original login is: sqlplus sys/123456 as sysdba

Now it is: unireadsqlplus sys/123456 as sysdba (if this command is not available for root users, just switch to oracle users)

8. Create a database

If you choose not to build a database when installing an oracle product, you must first create a database to use the oracle system, or you can create a second database if you choose to build a database when installing an oracle product.

There are two ways to create a database: one is through a graphical interface, and the other is using the create database command, which is described below.

In linux, run the database configuration assistant (DBCA) and execute DBCA under the $ORACLE_HOME/bin directory

1. Execute netca to enable snooping (this step is required if it is not enabled) (in order to enable clients to connect to oracle instances, and some commands also need to enable snooping)

Or use the command to start listening:

2. Run DBCA

Note: it is recommended to create only one database on a server. We created an orcl database when we installed oracle. We can use orcl in our experiments. (of course, we need to learn to create new databases)

Add: connect to the newly created database (replace the original BENET database listener listener1 with the new listener listener2)

1) turn on dbca configuration database listening

2) Select database BDQN

3) register the database BENET with the listener

(in fact, there is a BUG in the middle, that is, click on the shared service mode first-finish-confirm-cancel)

4) enable snooping

5) Connect to BENET database

9. Start and shut down the database

1. Startup of the database

Starting (startup) a database requires three steps

1) start the oracle instance

2) install the database by the instance

3) Open the database

In the startup command, you can control the different startup steps of the database through different options.

NOMOUNT options:

Nomount: you already know where the control file is through the parameter file, but you haven't read the control file yet. The main function is to start an instance, read the init.ora initialization parameter file, start the background process, and initialize the global SGA

MOUNT options:

Read the control file, through the control file to know other files (data files and log files), at this time did not read data files and log files, this state is called mount status. The instance was started and the database was installed, but the database was not opened. This open mode is often used in database maintenance operations, such as renaming data files, changing the redo log and opening the archiving mode, and so on. In this open mode, in addition to seeing the list of SGA systems, the system will also give a "database loaded" prompt.

Open options:

After reading the control file, the data file and log file are found, and the database is in the OPEN state.

Startupnomount | mount | open

Shudownnormal | immedieate | transactional | abort

Note: if you only execute the startup command, all the three steps of starting the instance, installing the instance and opening the database will be completed. This is that the system will not only see all the prompts in the previous startup mount mode, but also give a prompt of "the database has been opened". At this time, the database system is in a normal working state and can receive user requests.

If you use the startup nomount command or the startup mount command, you must use the alter database command to open the database, for example: if you open the database in startup nomount mode, that is, the instance has been started, but the database is not installed and opened, you must run the following two commands before the database can start correctly.

ALTER DATABASE MOUNT

ALTERDATABASE OPEN

If you start the database in startup mount mode, you only need to run the following command to open the database

ALTERDATABASE OPEN

2. Execute the startup command

Note:

An ORA-00845MEMORY_TARGET not supported on this system error may occur when the oracle11g database restarts oracle after executing dbca or adjusting sga.

The reason is that the size of the shm of the Linux system is smaller than that set by SGA. As a result, the distance is 4G for SGA and 1G for shm.

Solution:

Resize the shm as follows:

Vi/etc/fstab

Modify the settings such as the following line

Tmpfs/dev/shm tmpfs defaults 0 0

Change to

Tmpfs/dev/shm tmpfs defaults,size=6G 0 0

Save exit

Re-mount shm to make it effective

Mount-o remount / dev/shm

3. Close the database

4. Start startup nomount status

5. Modify the database to open status

Summary: the startup command equals the three commands "startupnomount" + "alter databasemount;" + "alter database open;".

6. Database shutdown

Normal: this is the default option, when using this option, no new user connections are allowed, but all current connections are allowed to continue, and the database can only be truly shut down when all users (voluntarily) log out.

In general, a normal database shutdown makes no sense, and even if only database control is running, there are always users who have not logged in. It may take a few days or more to shut down a database (if the user has not shut down himself)

Immediate: when using this option, no new user connections are allowed, and all currently connected sessions are terminated. Any active transaction will be rolled back and the database will then be shut down. It is a common way to close the database and close the database quickly.

Transactional: when using this option, no new user connections are allowed, existing sessions that do not exist in a transaction are terminated, and sessions currently in a transaction are allowed to terminate after the transaction is completed. As soon as all sessions are terminated, the data is closed (using shutdown immediate)

Abort: in the case of oracle, using this option is equivalent to a power outage. The instance is terminated immediately. At this point, no data is written to disk. But the next time you start the database, it is slow (instance recovery is required). This approach is recommended in the following situations.

1) the database has been in an abnormal working state and cannot be shut down with shutdownnormal or shutdown immediate commands.

2) the database needs to be shut down immediately

3) encountered a problem when starting the database instance

Using Navicat for Oracle tools to manage oracle remotely and graphically

1. Install the software Navicat for Oracle on the win7 host.

The following are all the next steps.

2. Create a new connection

3. Click Advanced-the role selects sysdba, and the location selects the location of the image settings.

4. View the database

11. Sql statements and basic operations

1. Use scott users to connect to the database server

If scott is locked, it needs to be unlocked

Change the password and log in to the server

2. View all the tables under the scott user's database

3. Create a new table products

4. View the structure of table products

Descproducts

5. Set up automatic submission of things

Set autocommit on

6. Format the field

1) View the emp table graphically.

2) View the emp table in sqlplus

The above picture shows that the layout is very messy.

3) formatting

4) query again

Give an example of a formatting problem:

SQL > conn sys/123.abcas sysdba

Connected.

SQL > selectfile_name,tablespace_name from dba_data_files

SQL > col file_name fora55

SQL > coltablespace_name for A10

SQL > selectfile_name,tablespace_name from dba_data_files

FILE_NAME TABLESPACE

-

/ u01/app/oracle/oradata/orcl/users01.dbf USERS

/ u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1

/ u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX

/ u01/app/oracle/oradata/orcl/system01.dbf SYSTEM

/ u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE

At present, the default line can display 80 characters, and if it exceeds 80 characters, it will wrap.

SQL > show linesize

Linesize 80

SQL > set linesize 60

You can view the following display:

SQL > selectfile_name,tablespace_name from dba_data_files

14 lines are displayed by default at one time

SQL > col username fora30

SQL > select usernamefrom dba_users

14 lines are displayed by default at one time

SQL > showpagesize

Pagesize 14

Modify the default displayed behavior 100

SQL > set pagesize 100

SQL > select usernamefrom dba_users

The above settings for col file_name for A10, linesize, and pagesize are only valid in the current session and will no longer have an effect after being disconnected. You can do this by modifying the glogin.sql under $ORACLE_HOME/sqlplus/admin/

[oracle@orclsrv ~] $cd / u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/

[oracle@orclsrv admin] $ls

Glogin.sql help libsqlplus.def plustrce.sqlpupbld.sql

[oracle@orclsrv admin] $

[oracle@orclsrvadmin] $vimglogin.sql

Add some commonly used parameters

Setpagesize 100

Setlinesize 120

Coldname for a20

Colloc for a15

Colfile_name for a55

Coltablespace_name fora10

Col name for a20

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.