[Reading notes] Oracle Database File Management

Database files include data files, log files, control files and password files, parameter files, and so on, the management of these files can be considered as the process of adding and removing changes.

1. Data file Management

Increase:

Add a data file when adding table space

Create tablespace test_2016 datafile '/u01/app/oracle/oradata/orcl/test_01.dbf ' size 10M autoextend on next 10M maxsize 10 0M;

Table Space Add data file

Alter tablespace test_2016 add datafile '/u01/app/oracle/oradata/orcl/test_02.dbf ' size 10M;

Alter tablespace test_2016 add datafile '/u01/app/oracle/oradata/orcl/test_03.dbf ' size 10M;

By deleting:

Delete a data file

Alter tablespace test_2016 drop datafile 7;

Alter tablespace test_2016 drop datafile '/u01/app/oracle/oradata/orcl/test_02.dbf ';

The data file cannot be deleted until it is empty.

To delete a table space:

Drop tablespace test_2016 including contents and datafiles;

Change : Changing the location of the physical file

There are two ways to modify the physical location of a data file, one is to modify the physical location after shutting down, and the other to modify the physical location after the offline.

The first way: Modify the location of the physical files after shutting down the machine

sql> shutdown immediate;

[email protected] orcl]$ mv test_0*. /

[email protected] orcl]$ CD.

[[email protected] oradata]$ ls

ORCL test_01.dbf test_02.dbf

[email protected] oradata]$

sql> startup Nomount;

Sql> ALTER DATABASE Mount;

sql> ALTER DATABASE rename file '/u01/app/oracle/oradata/orcl/test_01.dbf ' to '/u01/app/oracle/oradata/test_01.dbf ‘;

sql> ALTER DATABASE rename file '/u01/app/oracle/oradata/orcl/test_02.dbf ' to '/u01/app/oracle/oradata/test_02.dbf ‘;

sql> ALTER DATABASE open;

Sql> select file_name from dba_data_files where file_name like '%test% ';

file_name

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/test_01.dbf

/u01/app/oracle/oradata/test_02.dbf

Second way: Table space moves data files after offline

Sql>alter tablespace test_2016 offline;

Operation in operating system:

[email protected] oradata]$ MV test_0* orcl/

[email protected] oradata]$ CD orcl/

[[email protected] orcl]$ ls test_0*

TEST_01.DBF test_02.dbf

sql> ALTER DATABASE rename file '/u01/app/oracle/oradata/test_01.dbf ' to '/u01/app/oracle/oradata/orcl/test_01.dbf ‘;

sql> ALTER DATABASE rename file '/u01/app/oracle/oradata/test_02.dbf ' to '/u01/app/oracle/oradata/orcl/test_02.dbf ‘;

sql> alter tablespace test_2016 online;

Check:

Select File_name,file_id,online_status from Dba_data_files;

2. Online Redo log file management

Increase:

Add Log Members:

ALTER DATABASE Add logfile member ' path ' to group T;

sql> ALTER DATABASE Add logfile member '/u01/app/oracle/oradata/orcl/redo_11.log ' to group 1;

Log file groups increased:

ALTER DATABASE add logfile [Group N] (' path ') size 100M;

sql> ALTER DATABASE Add logfile '/u01/app/oracle/oradata/orcl/redo_04.log ' size 50M;

sql> ALTER DATABASE Add logfile Group 6 '/u01/app/oracle/oradata/orcl/redo_06.log ' size 50M; #可以跨序列增加日志文件组

By deleting:

To delete a log member:

ALTER DATABASE drop logfile member ' path '

sql> ALTER DATABASE drop logfile member '/u01/app/oracle/oradata/orcl/redo_11.log ';

To delete a log group:

sql> ALTER DATABASE drop logfile Group 6; --can only be deleted Inactive Log Group for status

Change:

Instead of shutting down the database to modify the location of the log file, add the new log file group directly in the database, and then delete the old filegroup.

Check:

SELECT * from V$log;

SELECT * from V$logfile order by group#;

3. Control file Management

The control file records the name of the database, the physical layout of the database: including the location of the data files, online log files, backup files, and database current SCN and other important information, in the second phase of database startup will be read into the control file.

Requires multiple copies of the control file to prevent damage to the database after a single file is damaged. When the database modifies the control file, all copies are modified at the same time.

How to view:

SELECT * from V$controlfile;

Show parameter control_files;

To create a copy of a control file:

1) Modify the parameter file

alter system set Control_files = '/u01/app/oracle/oradata/orcl/control01.ctl', '/u01/app/oracle/flash_ Recovery_area/orcl/control02.ctl' scope=spfile;

alter system set Control_files = '/u01/app/oracle/oradata/orcl/control01.ctl '

2) Stop the database

sql> shutdown immediate;

3) Copy control file

[email protected] yoon]$ cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/flash_ Recovery_area/orcl/control02.ctl

4) Start the database

Sql> Statup

5) Verify, view V$controlfile

Sql> select name from V$controlfile;

The steps to delete a copy of the control file are one less step than the steps you created to copy the control file.

4. password file management:

See: Password File summary

5. parameter file Management

The parameter file is divided into static parameter file (pfile) and dynamic parameter file (Spile). Pfile is a text document, SPFile is a binary file.

Parameter modification:

alter system set PARAMETER=VALUE [Scope=spfile|memory|both]

Scope, SPFile represents the modification in the Spile file, does not affect the current settings, memory is immediately modify the current settings, does not modify the Spile;both is also modified SPFile and the current settings.

For static parameters, use only Scope=spfile

Oracle boot Process load file order: spfilesid. Ora spfile.ora initsid. Ora

Location of the parameter file:

Show parameter SPFile;

Show parameter pfile;

Creation of the parameter file:

Create pfile= ' ... ';

Create spile= ' ... ';

How to start the database with the specified parameter file:

Startup pfile= ' ... ';

Startup spfile= ' ... ';

This article is from the "three countries Cold jokes" blog, please be sure to keep this source http://myhwj.blog.51cto.com/9763975/1796009

[Reading notes] Oracle Database File Management