Create an Oracle database and optimize the database

Disk Division	Name	Size	Purpose
	Drive C (master)	50 GB	Install 64-bit Windows 2003
	D Disk	184 GB	/Oracle Installation Database Software Directory
			/Oracle/oradata data file directory
	Disk E (master disk)	116 GB	/Oracle/oradata rollback segment, temporary tablespace, and log file directory (redo the log group (6, each 500 mb, and adjust the size according to the actual situation later ))
	Disk F	116 GB	/Backup Database Backup Directory
			/Oralog archive log directory

Project	Parameters	Description
Database instance name	SMS	This parameter is specified based on actual conditions. Here, the SMS column is used.
Memory Allocation	Oracletotal 6g (System Total 8g)	Total system memory used by Oracle
	SGA 4.8g	(80% of Oracle memory)
	PGA 1.2 GB
Data Block Size	16384	The default value is 8192 to 16384.
Character Set Selection	Zhs16gbk
National Character Set support	Al16utf16 (default)	Fixed Length two-byte efficiency and performance advantages
Default language	Simplified Chinese
Default Date Format	China
Connection Mode confirmation	Exclusive Mode
Process count	500
File directory
Database Software	D: \ oracle
Data Files	D: \ oracle \ oradata \ SMS	This data file refers to the data files created by yourself when you use the database in the future, such as the 12 data files created by the MT table partition.
Control Files and log files	E: \ oracle \ oradata \ SMS	Data files are stored here when dB is created
Archive logs	F: \ oralog
Backup File	F: \ backup
File Size
Single Data File	4G	Including undo and temp tablespace files
Single Control File	120 m
Single Log File	1g
Number of Files
Data Files		Specific table space size
Control File	3 groups
Log Files	6 groups (redo logs, each of which is 500 MB)
Roll back tablespace
Undotbs tablespace	30g
Temporary tablespace
Temp tablespace	30g
Data Table space
Sms_data	130 GB	Data Table space
Sms_index	50 GB	Index tablespace
System tablespace
System	2G
Sysaux	2G
User
Business User	SMS/Oracle	Set the password to Oracle.
Role	Resource, connect
	DBA	Decide whether to authorize based on the actual situation
System User	Sys/Oracle
	System/Oracle
	Scott/Tiger	For testing. It can be locked after it is officially launched.

After dbca:

Database Configuration Assistant

Step 2: database template

-- Custom database (data files in the demo will not be created)

Step 4: Manage options (no need to change, no need to "enable daily backup ")

Step 6: (default file system)

Step 7: Location of the database file

-- All database files use public locations

Create the D: \ oracle \ oradata directory on drive D and select the second directory. File Location input D: \ oracle \ oradata

Step 8: Restore Configuration

-- Specify the quick recovery area (uncheck) -- enable archiving (check) -- edit archive Mode Parameters

Q: What is the difference between a quick recovery area and an archive?
A: The quick recovery zone is a way to restore the database. For example, you can restore the database to yesterday, which is similar to a snapshot. With archive, you do not need to quickly restore the partition.

-- "Archiving log target

Create an oralog directory on disk F, and enter F: \ oralog in log Target 1.

Step 10: Initialize parameters (very important! The block size cannot be changed after it is written)

The character set and connection mode on the tab are not changed.

-- Memory -- (Note that if you select "typical" -- the percentage is changed to the PGA size, and the SGA and Oracle processes remain unchanged, you need to change the SGA, select the following "Custom ")

Memory Allocation	Total 6g
	SGA 4.8 GB (Oracle memory 80%)
	PGA 1.2 GB

-- Manual (automatic management and manual management of SGA and PGA)

SGA 4800 m
PGA 1200 m

A total of 8 GB of memory is not small. Oracle uses 6 GB, and 2 GB of memory is left for the system. If the system is missing, the roacle will be slow.

-- "Adjust size --" block size, select "16384" bytes -- "process, 500

Step 11: database storage

-- Control File

Create the \ oracle directory on disk E, and change d in the file directory to E. The result is as follows:

-- Tablespace -- sysaux

Change the file directory path d to E and set the size to 2048 MB.

 

-- "System

The temp, undo, and users file directories are changed to E: \ oracle \ oradata, the temp and undo file sizes are changed to 4096 MB, and the users file sizes remain unchanged:

-- Redo the log Group

Q: How to Create oracle \ oralog on drive F?

A: No. It is an archive directory. Change the file directory to "E". Select "500 mb" as the file size. This value can be determined only when your database runs for a while.

Q: Are three redo logs selected on the edisk? The size is 500 mb?

A: Do the same for 2 and 3.

You're done.

Select a log group and click Create below. You also need to create 4 5 6 with the same size. (when creating 4 5 6, note that the file path is the same as that of 1 2 3:

Even if the database is created, no script is generated or saved as a template (no listener is created ).

Last question: Now the data file is put on the E disk. I think yesterday's excle. What about putting the data file written above into the D disk?

A: The excle D disk is prepared to store the space file of the business data table. After the data is saved, create the business tablespace. I just created a non-business disk, so I put it on an E disk.

This creation process takes 2 hours.

 

After the database is created, optimize the article (disable automatic growth, and create data table space and index tablespace on a specific disk ):

In the real remote server sqlplus (that is, the CMD black box), some commands are often executed and then displayed ?????, This is a problem with character set settings:

Don't worry. The default value is English.

C:>alter session set nls_language='american';

C:>set wrap off linesize 120;

-- Check whether the database is created:

Start Menu --> Run --> RUN cmd

C:> sqlplus/nolog (there is no space between/and nolog here, otherwise an error is reported. If other problems occur: 1. Check that the user currently logged on to the system is not in the oracle_dba_group group. 2. environment variable -- System variable -- New -- variable name: oracle_sid -- variable value, fill in the SID of the Oracle you created, such as orclxxx)
SQL> Conn/As sysdba
-- View the database status:

SQL>select status from v$instance;

-- Create a listener later

Start Menu -- Oracle directory -- configure and transplant tools -- net manager

When a listener is created, the network service name is the database name, such as orclxxx.

The service name here is the SID of the newly created database, that is, the database to which you want to create a listener (one database creates one listener ). After creating a listener, you can directly connect to the database in the local service. When you connect to the database, -- create a new "service name" (local, there can be multiple, because local can be connected to multiple databases). The name here is, write the name you know. You can add the Server IP address to the backend. It is the name you see when you connect to the server using PL/SQL.

-- View the listening status:

C:>lsnrctl  status

This figure shows that there is no listener, but it does not, because the listener does not have a start

-- Start the listener:

C:>lsnrctl start

Q: Is this enabled or not? There is an error 1060 on it.
A: Do not worry about errors. This indicates that the listener is started. If you create a service name on your local computer, you can connect to the database remotely.

-- View the size and location of all data files:

select name,bytes/1024/1024 mb from v$datafile      union all      select name,bytes/1024/1024 mb from v$tempfile       union       select a.member,b.bytes/1024/1024 mb from v$logfile a,v$log b ;

After the query, we found that the values 01 02 03 and 04 05 06 are not in the same directory, but the redu group should be in the same directory. Here we will solve the problem by placing redo in the location where it should be put (E: \ oracle \ oradata)

So. Change the redo group file location:

1. query all redo:

 select a.group#,thread#,sequence#,b.member,bytes/1024/1024/1024 GB,archived,b.type,a.status         from v$log a, v$logfile b       where a.group#=b.group#

2. Delete the wrong redo group (, 6)

alter database drop logfile group 4 ;   alter database drop logfile group 5;alter database drop logfile group 6 ; 

3. Check whether the deletion is successful:

select a.group#,thread#,sequence#,b.member,bytes/1024/1024/1024 GB,archived,b.type,a.status         from v$log a, v$logfile b       where a.group#=b.group#

As you can see, 4 5 6 has been deleted. However, it is found that the 4 5 6 physical data files on the computer disk are still being deleted. Do you want to delete them manually? E: \ product deletes the entire data file because oracle is installed on the d disk, the e-disk cannot have the product directory at all. It was created by the wrong redo group at the time and is deleted now.

4. Create a redo group for the correct directory (time required for creation ):

alter database add logfile group 4  'e:\oracle\oradata\orclyxkj\redo04.log' size 500m;   alter database add logfile group 5  'e:\oracle\oradata\orclyxkj\redo05.log' size 500m;   alter database add logfile group 6  'e:\oracle\oradata\orclyxkj\redo06.log' size 500m;  

After execution, the results are as follows:

At this point, you can change the redo group location.

 

-- Disable automatic growth of the tablespace file size:

alter database datafile 'e:\oracle\oradata\orclyxkj\system01.dbf' autoextend off ；           alter database datafile 'e:\oracle\oradata\orclyxkj\sysaux01.dbf' autoextend off ；         alter database datafile 'e:\oracle\oradata\orclyxkj\undotbs01.dbf' autoextend off ；    

-- Disable automatic increase of the size of the tablespace file (tempfile:

alter database tempfile 'e:\oracle\oradata\orclyxkj\temp01.dbf'  autoextend off   

-- Add temporary tablespace files (fast execution. Temp is a sparse file system (and only temp is a sparse file). For example, if one file is 4 GB, temp occupies 4 GB on the surface, but it does not actually exist, so it is very fast)

 alter tablespace temp add tempfile    'e:\oracle\oradata\orclyxkj\temp02.dbf'   size 4096m autoextend off,    'e:\oracle\oradata\orclyxkj\temp03.dbf'   size 4096m autoextend off,    'e:\oracle\oradata\orclyxkj\temp04.dbf'   size 4096m autoextend off,       'e:\oracle\oradata\orclyxkj\temp05.dbf'   size 4096m autoextend off,                       'e:\oracle\oradata\orclyxkj\temp06.dbf'   size 4096m autoextend off;     

-- Add undo tablespace data files (slow execution. While Undo is a data file that occupies space in the system)

alter tablespace undotbs1 add datafile    'e:\oracle\oradata\orclyxkj\undo02.dbf'   size 4096m autoextend off,    'e:\oracle\oradata\orclyxkj\undo03.dbf'   size 4096m autoextend off,    'e:\oracle\oradata\orclyxkj\undo04.dbf'   size 4096m autoextend off,       'e:\oracle\oradata\orclyxkj\undo05.dbf'   size 4096m autoextend off,                       'e:\oracle\oradata\orclyxkj\undo06.dbf'   size 4096m autoextend off,     'e:\oracle\oradata\orclyxkj\undo07.dbf'   size 4096m autoextend off,          'e:\oracle\oradata\orclyxkj\undo08.dbf'   size 4096m autoextend off; 

If the execution is not completed, let it execute and continue:

(This can be left blank or executed:

On the remote server, CMD:

Sqlplus/nolog

Conn/As sysdba

Now we need to change the parameters:

SQL> set wrap off linesize 120;

)
-- PL/SQL:

alter system set open_cursors=1000;          alter system set  open_links=20 scope=spfile ;   alter system set db_files=1022 scope=spfile;          alter system set    job_queue_processes=50  ; 

-- Remote server: (restart the database)

CMD:

SQL> shutdown immediate;

SQL> startup
Q: Some SQL statements can only be executed in the CMD black box of the remote server, and some can be executed directly in PL/SQL by DBA users. As I closed the database just now, I ran the command window in PL/SQL and said, "Why are invalid SQL statements?" (for example, restarting the database, it cannot be successfully executed in PL/SQL. It will say "invalid SQL statement ")

A: What you execute in PLSQL is not the real sqlplus.

-- Next, reconnect to the Oracle database in the local PLSQL and create a data table space (for example, MT, not here)

Q: It means that all the database optimization tasks are completed here. The rest are those related to my business, including creating tablespaces and partition tables.

A: Yes. It takes some time for the database to run during optimization. After the performance report is generated, analyze the specific problem.
-- Create an index tablespace first (if a path error is reported, for example, if the orclyxkj Folder does not exist, you can manually create and modify the folder before executing the create statement ):

create tablespace tbs_yxkj_index datafile 'd:\oracle\oradata\orclyxkj\tbs_yxkj_index01.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_index02.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_index03.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_index04.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_index05.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_index06.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_index07.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_index08.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_index09.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_index10.dbf'   size 4096m autoextend offEXTENT MANAGEMENT LOCAL AUTOALLOCATE;

-- Create a data table space (for a period of time ):

create tablespace tbs_yxkj_data datafile 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data01.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data02.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data03.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data04.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data05.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data06.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data07.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data08.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data09.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data10.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data11.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data12.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data13.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data14.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data15.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data16.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data17.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data18.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data19.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data20.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data21.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data22.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data23.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data24.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data25.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data26.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data27.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data28.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data29.dbf'   size 4096m autoextend off, 'd:\oracle\oradata\orclyxkj\tbs_yxkj_data30.dbf'   size 4096m autoextend offEXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Create a new tablespace but reuse the previous data file (no data in the file by default when the tablespace is reused ):

create tablespace tbs_yxkj_data  datafile 'd:\oradata\orclyxkj\tbs_yxkj_data02.dbf'   size 4096m reuse , 'e:\oradata\orclyxkj\tbs_yxkj_data03.dbf'   size 4096m reuse, 'd:\oradata\orclyxkj\tbs_yxkj_data05.dbf'   size 4096m reuse, 'd:\oradata\orclyxkj\tbs_yxkj_data08.dbf'   size 4096m reuse, 'd:\oradata\orclyxkj\tbs_yxkj_data11.dbf'   size 4096m reuse, 'd:\oradata\orclyxkj\tbs_yxkj_data14.dbf'   size 4096m reuse, 'e:\oradata\orclyxkj\tbs_yxkj_data15.dbf'   size 4096m reuse, 'd:\oradata\orclyxkj\tbs_yxkj_data17.dbf'   size 4096m reuse,  'e:\oradata\orclyxkj\tbs_yxkj_data18.dbf'   size 4096m reuse,  'd:\oradata\orclyxkj\tbs_yxkj_data20.dbf'   size 4096m reuse, 'e:\oradata\orclyxkj\tbs_yxkj_data21.dbf'   size 4096m reuse,  'd:\oradata\orclyxkj\tbs_yxkj_data23.dbf'   size 4096m reuse,  'e:\oradata\orclyxkj\tbs_yxkj_data24.dbf'   size 4096m reuse, 'd:\oradata\orclyxkj\tbs_yxkj_data26.dbf'   size 4096m reuse, 'e:\oradata\orclyxkj\tbs_yxkj_data27.dbf'   size 4096m reuse, 'd:\oradata\orclyxkj\tbs_yxkj_data29.dbf'   size 4096m reuseEXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Create a user, grant the DBA permission, and authorize the user:

create user usernameidentified by "pwd" default tablespace tbs_yxkj_data;grant connect,resource,dba to username;drop public synonym t_sys_user;grant all privileges on t_sys_user to yxclient;create public synonym t_sys_user for ydsoft_test.t_sys_user;