Oracle Database Learning Introductory article

Oracle creates table spaces and users

Oracle Learning for beginners like me, or to step by step, first of all to understand the basic concepts.

I think Oracle learning can be divided into several parts:
Basic system usage
System tuning
SQL statement Writing
SQL tuning
Pl/sql Program Development

The following discussions are done in single host mode. Welcome to the discussion.

Question 1: What is an instance?

After an Oracle software is installed, it has a default instance (instance), and the user can also add instances to Oracle.
An instance is a memory structure (sga+ background process), with each instance having a set of its own process groups to manage the running instance. The table space and user account are included in the instance.

Instances and instances are independent of each other. Logically, each instance has its own table space, its own users. The structure, data, and user information of the tables in the physical table space are stored in the disk's data files and user files.

One instance can open only one database.

Question 2: What is table space?
Table spaces are a logical concept in Oracle. Used to classify users, control the size of user tables.
Table space is equivalent to a basket, the user's data file is equivalent to a cargo, the size of the goods can not exceed the size of the basket.

Question 3: What does a database in Oracle say about an instance of SQL when you are in normal operation? A table space? A user?
A popular understanding can be: In an instance, a set of all tables in a table space that belongs to a user. is viewed from the user's latitude.

Compare MySQL and Oracle's database creation process.
In MySQL:
Create a user
Creating databases (CREATE DATABASE command)
Authorize user permissions to the database
CREATE TABLE ...

In Oracle:
Create a user
Create a table space
Specify the table space for a user
Specify user space quotas
Giving users permission to create database objects
CREATE TABLE ...

Question 4: What is the difference between saying "one instance can only open one database" and the database definition in question 3 when interpreting an instance?
is two different concepts, the instance open database is the Mount database that the instance mounts at startup.
The mounted database is a heap of physical files on disk, including files that are required for instance startup (datafile, redo log file, control file, archive log file, parameter file).
The database referred to in question 3 is the object of the SQL statement operation.

Summarize the above questions:

CREATE table spaces and user methods as follows.

Log on with the administrator account.

To create a user:

Create user Wangyi identified by 123456;

To create a table space:

Create tablespace users1 datafile '/u01/app/oracle/oradata/xe/users1.dbf ' size 200m autoextend on next 32m maxsize 1024m E Xtent Management Local;

Users1 table Space Initial size 200m, each increment 32m, maximum 1024m.

Specifies that the table space for the Wangyi user is users1:

Alter user Wangyi default tablespace users1 temporary tablespace temp;

Specifies the Wangyi user default table Space users1, temporary tablespace temp.

Specify quotas for Wangyi users to use table space:

Alter user Wangyi quota unlimited on users1;

Give User permissions:

Grant create session, CREATE table, create view, create no index to Wangyi;

The next article discusses Oracle's file system.

Racle File System Overview

In the last table space, when you start an instance, you need to load the database, which is made up of datafile, redo log files, control file, archive log file, parameter filename, and so on. So what is the effect of these documents?

1, Oracle's startup files
The location of the startup file is under $oracle_home/dbs, with two files Init.ora and Spfile<inst_name>.ora,inst_name being the current instance name.
These two files define the parameters that are started. Init.ora is a text file, Spfile<inst_name>.ora is a binary file.

Note: Spfile<inst_name>.ora cannot be modified with a text editor and cannot be opened, which may cause the instance to fail to start. Need to be modified with alter command.

2, Oracle's control files
The control file is *.ctl,10g in the/u01/oracle/oradata/<inst_name> directory. Shape such as: Control01.ctl, Control02.ctl, Control03.ctl.
The 11xe version is in the/u01/app/oracle/oradata/<inst_name> directory, but the file name becomes control.dbf, which is essentially a data file (binary format), except that the file suffix is not the same.

3. Oracle Data files
The data file is *.dbf,10g also in the/u01/oracle/oradata/<inst_name> directory. The data file that you need to specify when you create a tablespace is. dbf. The tables in the database are stored in the data file.
The 11xe version is in the/u01/app/oracle/oradata/<inst_name> directory.

4, Oracle's other parameter files
Parameter file for listener:
10g in
/u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
/u01/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
11xe version in
/u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
/u01/app/oracle/product/11.2.0/xe/network/admin/tnsnames.ora

5, Oracle's log file
The log file is divided into three main categories:
Alert Log Files Alarm logs
Trace log files trace logs (users and processes)
Redo log file redo logs (log database changes)

5.1 Alert Log
Logs the Oracle system running, including system parameter changes, system errors, etc.
10g in/u01/oracle/admin/orcl/bdump/alert_orcl.log
11xe version in/u01/app/oracle/diag/rdbms/xe/xe/trace/alert_xe.log

5.2 Trace Log
With the. TRC suffix file, which records the various SQL operations and the time consumed, and so on, according to the trace file we can understand which SQL caused the system performance bottlenecks, and then take the appropriate approach to tuning.
10g under the/u01/oracle/admin/orcl/udump/directory
11xe version in the/u01/app/oracle/diag/rdbms/xe/xe/trace/directory

5.3 Redo Log files
Redo logs are divided into
Online redo log files on-line redo logs
Archive redo log Files archive redo logs

1) Online redo log
Also called the online redo log, in the form of SQL script real-time record of changes to the database information, including user data modification and database administrator of the database structure changes.

The online redo log file is used for recycling. When the first log file reaches a certain number, it stops writing, turns to the second log file, and the second one turns to the third log file. The third full is written to the first log file. And the first log file has no automatic backup involves the issue of archiving or not archiving. When the database automatically backs up the original log file is called the archive mode, do not need to automatically back up the database is called a non-archive mode.

In archive mode, when the online redo log is full, the online redo log is archived to disk and becomes the archive redo log.
In non-archive mode, when the online redo log is full, overwrite the contents of the first log and continue looping.

10g online redo Log in the/u01/oracle/oradata/orcl/directory, in the form of: Redo01.log, Redo02.log, Redo03.log.

2 Archive Redo Log
An archive log, which means that Oracle saves the online redo log to the hard disk (persistent) when the condition is met.

In fact, the so-called archiving, means that the online log for archiving, persisted to a fixed file to the hard disk, easy to recover and query later.

Of course, the prerequisite is that the database is in archive mode.

The location of the archive log is defined by the Db_recovery_file_dest parameter, which defaults to the path of the Flash RECOVERY area. It contains archived logs, flashback logs, and other files.
10g in/u01/oracle/flash_recovery_area
11xe version in/u01/app/oracle/fast_recovery_area

6, Oracle's password file
The function of the password file is to authenticate the special user of the database.
10g in/u01/oracle/product/10.2.0/db_1/dbs/orapw<inst_name>
11xe version in/u01/app/oracle/product/11.2.0/xe/dbs/orapwxe

Oracle Archive mode and non-archive mode switching
Oracle Boot Sequence Brief

The previous article said that Oracle's log files have online redo logs and archived redo logs, and only in archive mode will the system write archive redo logs.

Use the archive log list to see if you are in Archive mode:

SQL> archive log list Database log mode	       No Archive Mode Automatic archival	       Disabled Archive destination	       USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     176 Current log sequence	       

One, non-archive mode to archive mode
1. Order:

shutdown;
startup mount;
alter database archivelog;
alter database open;
archive log list;

2, the implementation process:

SQL> shutdown;   Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started.  Total System Global Area  835104768 bytes Fixed Size		    2231096 bytes Variable Size		  624952520 bytes Database Buffers	  205520896 bytes Redo Buffers		    2400256 bytes Database mounted. SQL> alter database archivelog;  Database altered.  SQL> alter database open;  Database altered.  SQL> archive log list; Database log mode	       Archive Mode Automatic archival	       Enabled Archive destination	       USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     176 Next log sequence to archive   177 Current log sequence	       

Second, archive mode to non-archive mode
1. Order:

shutdown;
startup mount;
alter database noarchivelog;
alter database open;
archive log list;

2, the implementation process:

SQL> shutdown; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started.  Total System Global Area  835104768 bytes Fixed Size		    2231096 bytes Variable Size		  624952520 bytes Database Buffers	  205520896 bytes Redo Buffers		    2400256 bytes Database mounted. SQL> alter database noarchivelog;   Database altered.  SQL> alter database open;  Database altered.  SQL> archive log list; Database log mode	       No Archive Mode Automatic archival	       Disabled Archive destination	       USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     176 Current log sequence	       

Iii. Oracle Boot Sequence
We'll find that when we start Oracle with the startup command, and when we set up archive mode, we use the Startup Mount command, so there are certainly several phases of Oracle startup, so that you can modify the archive mode during the Mount phase.

You can see the startup status by Nomount-> Mount-> Open
1, Nomount State
The Nomount process is the process of starting an Oracle database instance. At this stage Oracle first looks for the parameter file (pfile or SPFile), and then creates the database instance (allocating memory, creating a background process) based on the settings in the parameter file.

2. Mount Status
After booting to the Mount State, Oracle can obtain control file location information from SPFile or pfile, locate the controlling files, and read the control files.
In this state, data files and log files can be managed.

3, open State
At this stage, we first check each data file, check whether the checkpoint count (Checkpoint CNT) in the header of the data file is consistent with the checkpoint count in the control file, and then open the database and lock the data file.

4. The startup command equals the following three command combinations

startup nomount;
alter database mount;
alter database open;