Oracle Database File Management

Oracle Database File Management

Database files include data files, log files, control files, password files, and parameter files. Management of these files can be seen as the process of adding, deleting, modifying, and querying.

1. Data File Management
Add:
Add data files when adding tablespace
Create tablespace test_2016 datafile '/u01/app/Oracle/oradata/orcl/test_01.dbf' size 10 M autoextend on next 10 M maxsize 100 M;

Add data files to the tablespace
Alter tablespace test_2016 add datafile '/u01/app/oracle/oradata/orcl/test_02.dbf' size 10 M;
Alter tablespace test_2016 add datafile '/u01/app/oracle/oradata/orcl/test_03.dbf' size 10 M;

Delete:
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 can be deleted only when it is null.
Delete tablespace:
Drop tablespace test_2016 including contents and datafiles;

Change: change the location of the physical file
There are two ways to modify the physical location of a data file: one is to change the physical location after shutdown, and the other is to change the physical location after offline.
Method 1: change the location of the physical file after Shutdown
SQL> shutdown immediate;

[Oracle @ myCentOS orcl] $ mv test_0 *../
[Oracle @ mycentos orcl] $ cd ..
[Oracle @ mycentos oradata] $ ls
Orcl test_01.dbf test_02.dbf
[Oracle @ mycentos 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
Method 2: Move the data file after the tablespace is deprecated
SQL> alter tablespace test_2016 offline;
Operation in the operating system:
[Oracle @ mycentos oradata] $ mv test_0 * orcl/
[Oracle @ mycentos oradata] $ cd orcl/
[Oracle @ mycentos 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;

Change the data file size:
Alter database datafile '/u01/app/oracle/oradata/test_02.dbf' resize 5 M;

Query:
Select file_name, file_id, online_status from dba_data_files;
2. Online redo log file management
Add:
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 Group added:
Alter database add logfile [group n] ('path') size 100 M;
SQL> alter database add logfile '/u01/app/oracle/oradata/orcl/redo_04.log' size 50 M;
SQL> alter database add logfile group 6'/u01/app/oracle/oradata/orcl/redo_06.log 'size 50 M; # You can add a log file group across sequences.

Delete:
Delete a log member:
Alter database drop logfile member 'path'
SQL> alter database drop logfile member '/u01/app/oracle/oradata/orcl/redo_11.log ';
Delete a log group:
SQL> alter database drop logfile group 6; -- only INACTIVE log groups can be deleted.

Change:
Instead of shutting down the database to modify the location of log files, it is better to directly add a new log file group to the database and then delete the old file group.
Query:
Select * from v $ log;
Select * from v $ logfile order by group #;
3. Control File Management
The control file records the Database Name and physical layout of the database, including the location, online log file, backup file, and the current SCN of the database, the control file is read in the second stage of database startup.
Multiple copies of the control file are required to prevent damage to the database. When the database changes the control file, all copies must be modified at the same time.
View method:
Select * from v $ controlfile;
Show parameter control_files;

To create a control file copy, follow these steps:
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 the control file
[Oracle @ 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 and view v $ controlfile
SQL> select name from v $ controlfile;
The procedure for deleting a control file copy is less than that for creating a control file.
4. Password File Management:
For more information, see Password File summary.
5. parameter file management
Parameter files are classified into static parameter files (pfile) and dynamic parameter files (Spile. Pfile is a text document, and spfile is a binary file.
Parameter modification:
Alter system set parameter = value [scope = spfile | memory | both]
In scope, spfile indicates to modify the current settings in the spile file without affecting the current settings; memory indicates to modify the current settings immediately without modifying spile; both indicates to modify both the spfile and the current settings.
For static parameters, only scope = spfile can be used.
File loading sequence during Oracle startup: spfilesid. ora spfile. ora initsid. ora

Parameter file location:
Show parameter spfile;
Show parameter pfile;

Parameter file creation:
Create pfile = '...';
Create spile = '...';
How to start a database using the specified parameter file:
Startup pfile = '...';
Startup spfile = '...';