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

Overview of Oracle 12c database

ORACLE database system is a group of software products with distributed database as the core provided by ORACLE (Oracle). It is one of the most popular client / server (CLIENT/SERVER) or Bhand S architecture databases. For example, SilverStream is a kind of middleware based on database. ORACLE database is the most widely used database management system in the world, as a general database system, it has complete data management function; as a relational database, it is a complete relational product; as a distributed database, it realizes the distributed processing function. But with all its knowledge, as long as you learn ORACLE knowledge on one model, you can use it on all types of machines.

Oracle 12c pluggable database architecture

A new feature introduced in Oracle 12c is Oracle Multitenant, which can create and maintain many pluggable databases in multi-tenant container databases. Oracle Multitenant is an extra component in Oracle Enterprise Edition. However, it is free to use in a pluggable database in all versions of Oracle.

A multi-tenant container database (CDB) is a database that can hold one or more pluggable databases. A container is a collection of data files and metadata in CDB. A pluggable database is a data container that can be easily created by cloning another database. If necessary, you can also transfer pluggable databases from one CDB to another CDB.

All CDB that contain a set of master data files and metadata are root containers. Each CDB also contains a seed container, which is a template for creating other pluggable databases. Each CDB consists of a root container, a seed container, and 0, 1, or more pluggable databases.

-installation environment-

Installation system: CentOS 7

IP address: 192.168.100.135

Hard disk size required for installation: 40g or remount a 40G hard disk (about 15g is needed for installation, of course, the bigger the better)

Memory: 4G (less than 4G is not recommended)

SWAP (virtual memory): 8G (less than 8G is not recommended)

Oracle database package, without decompression, can be called directly.

Baidu network disk: link: https://pan.baidu.com/s/1v1h3_wrB6er8HCpONh9xRQ password: a0h2

Preparation: turn off the firewall [root@oracle ~] # systemctl stop firewalld.service [root@oracle ~] # systemctl disable firewalld.service [root@oracle ~] # setenforce 0 to mount the new hard drive for installation

Steps:

[root@localhost ~] # cd / dev [root@localhost dev] # fdisk / dev/sdbnp enter w [root@localhost dev] # mkfs-t xfs / dev/sdb1 [root@localhost dev] # mkdir / orc [root@localhost dev] # vim / etc/hostname HOSTNAME=oracle # modify the hostname to facilitate management. Save and exit after modification and insert a line at the end of [root@localhost dev] # vim / etc/hosts192.168.100.135 oracle # to restart the virtual machine. Identify hard disk [root@oracle ~] # mount / dev/sdb1 / orc [root@oracle ~] # df-h

-installation steps-1.yum installation environment dependency package [root@oracle ~] # yum-y install binutils compat-libcapl compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libXi libXtst make sysstat unixODBC unixODBC-devel2. Adjust the kernel parameter [root@oracle ~] # vim / etc/sysctl.conf# add fs.aio-max-nr = 1048576 # number of asynchronous IO requests on the last line The recommended value is: 1048576 (1024,1024 or 1024K) fs.file-max = 6815744 # the maximum number of file handles opened, the problem to prevent file descriptor exhaustion kernel.shmall = 2097152 # Total shared memory pages Memory divided by 4K kernel.shmmax = 4294967295 kernel.shmmni = 4096kernel.sem = 250 32000 100 128 # numerical meaning: SEMMSL: maximum number of signals per signal set # SEMMNS: maximum number of signals used to control the entire Linux system # SEMOPM: kernel parameters are used to control the number of signal operations that can be performed per semop system call # SEMMNI: kernel parameters are used to control messages in the entire Linux system Maximum number of number sets net.ipv4.ip_local_port_range = 9000 65500 # Port range for outgoing connections net.core.rmem_default = 262144 # default value of socket receive buffer size net.core.rmem_max = 4194304 # maximum socket receive buffer size net.core.wmem_default = 262144 # socket send buffer Default net.core.wmem_max of zone size = 1048576 # maximum size of socket send buffer # after modification, save exit [root@oracle ~] # sysctl-p # to make it effective. Add administrative user, group [root@oracle ~] # groupadd oinstall [root@oracle ~] # groupadd dba [root@oracle ~] # useradd-g oinstall-G dba oracle [root@oracle ~] # passwd oracle change the password of user oracle. New password: 1231234. Create and modify Oracle installation directory permissions [root@oracle ~] # mkdir-p / orc/app/oracle [root@oracle ~] # chown-R oracle.oinstall / orc/app/ [root@oracle ~] # chmod-R 755 / orc/app/oracle/5. Modify Oracle source package permissions [root@oracle oracle] # chown-R oracle:oinstall / home/oracle/database/ [root@oracle oracle] # chmod-R 755 / home/oracle/database/6. Modify oracle user environment configuration [root@oracle ~] # vim / home/oracle/.bash_profile# delete the last two rows of data and insert the following content umask 022 # permissions The reverse mask ORACLE_BASE=/opt/app/oracle # specifies the base directory ORACLE_HOME=/opt/app/oracle/product/12.2.0/dbhome_1/ # specifies the data storage path ORACLE_SID=orcl # specifies the instance name NLS_LANG= "SIMPLIFIED CHINESE_CHINA" .UTF8 # simplified Chinese language character set PATH=$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/binLANG=zh_CN.UTF-8 export PATH LANG NLS_LANG Save and exit 7. 7 after ORACLE_BASE ORACLE_HOME ORACLE_SID# modification is completed. Configure Oracle user resource restrictions # use the pam_limits authentication module [root@oracle ~] # vim / etc/pam.d/login# to insert the following under line 7 or so after session required / lib/security/pam_limits.sosession required pam_limits.so# modification is completed, save and exit [root@oracle ~] # vim / etc/security/limits.conf # Edit resource restrictions # insert at the end Oracle soft nproc 2047 # but the number of processes available to the user oracle hard nproc 16384oracle soft nofile 1024 # the number of files that the user can open oracle hard nofile 65536oracle soft stack 10240 # Stack Settings # Save exit after modification [root@oracle ~] # vim / etc/profile # modify environment variable configuration file # insert if [$USER = "oracle"] then if [$SHELL = "/ bin/ksh"] then ulimit-p 16384 # buffer size ulimit-n 65536 # number of files else ulimit-u 16384-n 65536 # number of files after fifi# modification is completed and save and exit 8. Install the Oracle installation package

Oracle database package, no need to decompress, can be adjusted directly.

Baidu network disk: link: https://pan.baidu.com/s/1v1h3_wrB6er8HCpONh9xRQ password: a0h2

Download the file and mount it to / opt

[root@oracle ~] # xhost + # allows all users to access Xserveraccess control disabled Clients can connect from any host [root@oracle ~] # chmod-R 777 / opt/database/ [root@oracle ~] # su-oracle # switching user [oracle@oracle ~] $export DISPLAY=:0.0 [oracle@oracle ~] $cd / opt/database/ [oracle@oracle database] $lsinstall response rpm runInstaller sshsetup stage welcome.html [oracle@oracle ~] $. / runInstaller # execution installation script is starting Oracle Universal Installer...8. Enter the graphical installation (the default option is for those without picture presentation)

Click single instance database installation, next step

Typical installation, set your own password, next step

After the operation is installed, a small window pops up when the installation is about 79%, requiring two scripts to be run, which is operated by the root user.

# Open another terminal and log in to [root@oracle abc] # / orc/app/oraInventory/orainstRoot.sh [root@oracle abc] # / orc/app/oracle/product/12.2.0/dbhome_1/root.sh as ROOT

Wait for the completion before returning to the graphical interface point to determine and continue with the installation. (it will take some time to wait)

So far, the Oracle database has been installed, but the newly installed Oracle database is very unsafe. Next, we will introduce the basic post-loading optimization. The first thing you need to understand is the concept of control files and basic management-control file management-

Control file: is a binary file, the initialization size is specified by CREATE DATABASE, and you can use RMAN backup.

It records the structure information of the current database, as well as the information of data files and log files, as well as related status, archiving information and so on. Describe its location, number, and so on in the parameter file. The principles of decentralization and multiplexing are usually adopted. The mount phase is read and the open phase is used all the time. Maintain database consistency (when the database starts, it compares the control file with the ckpt in the online log file, that is, the starting scn number. If it is equal, it starts normally, otherwise media recovery is required. A control file can only belong to one database. Any modifications to the control file are written to all control files specified in the initialization parameters, and only the first control file is read when read. Control files can only be connected to one database, the size of control files is generally no more than MB, at most, at least one, mirror each other.

-

Control the name of the content database contained in the file, ID, the name of the timestamp table space created, the online log file, the location and number of data files, the name of the Sequence number of the online log, checkpoint information, undo segment start or end archive information backup information

-

When the database starts from the nomount state to the mount state, the control file must be read to obtain the location of the data file and log file; if the control file is effective, the database cannot be started; and during the real-time operation of the database, the system keeps writing stop scn and checkpoint scn to the control file, and if the control file cannot be written, the database will be down. If there is a backup control file in this situation, you only need to overwrite the correct backup control file to the effective control file in the nomount state, and then start the database to the open state to solve the problem. Therefore, by default, at least several of the same control files are backed up each other, and the same control files are best placed in different disk directories to spread the risk.

-

12C after the installation is completed, by default, there are 2 control files. It can be seen from the above that if Oracle does not change the control files after installation, there will be great security risks, so it is recommended to optimize the control files. Preparatory work:

Need to use root user, give / opt 777permission to facilitate Oracle user to read and write

[root@HOSTNAMEoracle ~] # chmod 777 / opt/

Enter Oracle

[root@HOSTNAMEoracle ~] # su-oracle

[oracle@HOSTNAMEoracle ~] $sqlplus / as sysdba

1. View the control file location SQL > select name from v$controlfile

two。 View the contents of the control file # store in the control file, create database information, redo log information, data file and archived log file records, etc. SQL > select type,record_size,records_total,records_used from vested control file recording section; 3. Store the path to the multiple control file SQL > alter system set 2 control_files='/orc/app/oracle/oradata/orcl/control01.ctl', 3'/ opt/control02.ctl' scope=spfile; # change control file 02 to increase security

After the parameter changes, you need to close the Oracle database, make changes in the path of the / orc/app/oracle/oradata/orcl control file, and restart Oracle to take effect SQL > shutdown immediate; # shut down the database SQL > ho cp / orc/app/oracle/oradata/orcl/control02.ctl / opt/control02.ctl # use the ho command to execute the system command SQL > startup # Open database SQL > select name from v$controlfile without exiting the database # View the location of the control file

4. Backup control file SQL > alter database backup controlfile to'/ opt/control.bak'; # perform backup database has changed. SQL > ho ls / opt # run the system command control02.ctl control.bak ORCLfmap rh in the sql environment

-redo log-when the Oracle database is running, the data changed by the user will first be stored in the database high-speed buffer. In order to improve disk read and write, the oracle mechanism will not frequently write the buffer data to disk, but will not write to the database file until the checkpoint or data cache reaches a certain number. If the system dies before the checkpoint or the amount of data is met, the data submitted by the user will be lost because the data is still in memory. In order to prevent data loss, oracle proposes a redo log.

-

Mode of the log:

Online log: online log mode, the log will not be submitted actively, and some data will be lost when the server is shut down. When you switch a log group, the file is automatically committed and saved, and the files written in the switched log group are lost.

Archive log: as soon as the data is written, it will be submitted to the hard disk for preservation.

Redo log group: an online redo log group consists of one or more identical online log files. There are at least two log groups with one member in each group (it is recommended that two members in each group should be distributed to different disks). The LGWR background process writes the log contents to all members of a group at the same time.

Trigger conditions of LGWR

Changes of more than one megabyte at the time of transaction commit (COMMIT) Redo Log Buffer 1/3 full Redo Log Buffer are recorded before DBWn writes to the data file. Redo log query # View the group number, sequence, size, number, archive status, log group status SQL > select group#,sequence#,bytes,members,archived,status from v$log of the redo log group

After the Oracle database is newly installed, check the status of the redo log file to confirm whether the archive file mode is enabled. If it is not turned on, it is recommended to open it to increase security and prevent some data loss. two。 Archive log management # View archive log mode SQL > archive log list

Enable archive log mode: SQL > shutdown immediate; # close Oracle database SQL > startup mount; # start the instance, load the database but not open the database SQL > alter database archivelog; # enable archive log mode SQL > alter database open; # start open database SQL > archivelog list # View archive log mode

3. To make it easy to view the log group, you can modify SQL > set line 120; / / set the display width 120SQL > col member for a50; / / set the column display width 50 characters width SQL > select group#,status,type,member from vault log file; / / use the logfile view to view

PS:status meaning

1: White space is in use

2:stale content is incomplete

3:invalid cannot be accessed such as just established

4:deleted files are no longer useful

/ orc/app/oracle/oradata/orcl/ both control files and redo log files are in this path

4. About new and deleted redo log groups-new redo log groups-SQL > alter database add logfile group 42 ('/ orc/app/oracle/oradata/orcl/redo04a.log', 3'/ opt/redo04b.log') size 10m; the database has been changed. -Delete the redo log group-- SQL > alter database drop logfile group 4; the database has changed. # current log group cannot be deleted: switch log group: alter system switch logfile;- add / delete redo log files-SQL > alter database add logfile member 2'/ opt/redo01b.log' to group 1, 3'/ opt/redo02b.log' to group 2; the database has been changed. The SQL > alter database drop logfile member 2'/ opt/redo02b.log'; database has changed.

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.