Oracle Data File Transfer

How can I move data files from drive C to drive D?
It's easy, just three steps.
Step 1: Move the tablespace offline to the directory specified by disk D.
Step 2: Modify the tablespace file path alter database rename file 'old file path' to 'new file path ';
Step 3: Make the tablespace online.

The following are some other references:

Data File Rename (filesystem and raw device)
Filesystem
Database must be open:
1. Alter tablespace TBS read only;
2. Alter tablespace TBS offline;
3. Copy an original file at offline and name it as a new file name.
4. Alter tablespace TBS rename datafile 'tbs _ file_old.dbf 'to 'tbs _ file_new.dbf ';
5. Alter tablespace TBS online;
6. Alter tablespace TBS read write;
7. Alter database recover datafile 'tbs _ file_new.dbf ';

Raw Device
Database must be mounted but not open:
1. Create a raw device connection file for the new data file
2. starup Mount;
3. Alter database rename file 'tbs _ file_old 'to 'tbs _ file_new ';
4. Alter database recover datafile 'tbs _ file_new ';
5. Alter database open;

Oracle System Emergency troubleshooting (Handling of data files, log files, and tablespace corruption)

Solution to Oracle physical structure faults:
Oracle physical structure faults refer to various database faults caused by damage to each physical file of the database. These faults may be caused by hardware faults or human error. Therefore, we must first determine the cause of the problem. If it is a hardware fault, we must first solve the hardware problem. We can follow the instructions below for further handling without any hardware problems.

Control File Corruption:
The control file records important Oracle configuration information, such as the database name, Character Set Name, data file, and log file location. If the control file is corrupted, the database is shut down abnormally. Once the control file is missing, the database cannot be started. This is a serious error.
You can query database log files to locate corrupted control files. The log file is located at $ oracle_base/admin/bdump/alert_orcl.ora.

Damage a single control file:
1. Make sure that the database is closed. If the following command is not used to close the database:
Svrmgrl> shutdown immediate;
2. Check the initialization file $ oracle_base/admin/pfile/initorcl. ora to determine the path of all control files.
3. Use the operating system command to overwrite the wrong control file with other correct control files.
4. Run the following command to restart the database:
Svrmgrl> startup;
5. Use appropriate methods for full database backup.

Corrupt all control files:
1. Make sure that the database is closed. If the following command is not used to close the database:
Svrmgrl> shutdown immediate;
2. Restore the nearest control file from the backup result set. You can directly back up the nearest control file from tape to the corresponding directory for points without database backup; use the appropriate RMAN script to restore the nearest control file for the point with database backup.
3. Use the following command to create a script to generate a Database Control file:
Svrmgrl> startup Mount;
Svrmgrl> alter Database Backup controlfile to trace noresetlogs;
4. Modify the trace file generated in step 3, copy some of the statements about creating the control file, and make some modifications so that it can reflect the latest database structure. Assume that the generated SQL file is named createcontrol. SQL.
Note:
The path of the trace file can be determined by checking the $ oracle_base/admin/bdump/alert_orcl.ora file after the step 3rd is executed.
5. Run the following command to recreate the control file:
Svrmgrl> shutdown abort;
Svrmgrl> startup nomount;
Svrmgrl> @ createcontrol. SQL;
6. Use appropriate methods for full database backup.

Damaged redo log files:
All Database additions, deletions, and changes are recorded in the redo log. If the active redo log file is damaged, the database is shut down abnormally. The inactive redo log will eventually change to the active redo log because of log switching. Therefore, the corrupted inactive redo log will eventually cause the database to terminate abnormally. In IPAs/mswitch, each redo log group has only one member. Therefore, in the following analysis, only the damage of the redo log group is considered, rather than the damage of a single redo log member.

Determine the location and status of damaged redo logs:
1. If the database is available:
Select * from V $ logfile;
Svrmgrl> select * from V $ log;
2. If the database is terminated abnormally:
Svrmlgr> startup Mount;
Svrmgrl> select * from V $ logfile;
Svrmgrl> select * from V $ log;
Among them, the status of logfile is invalid, indicating that this set of log files are damaged; the status of log is inactive, indicating that the redo log files are inactive; the status of active indicates that the redo log files are active; current: indicates that the redo log is a log file currently in use.

The corrupted log file is not activated:
1. Delete the corresponding log group:
Svrmgrl> alter database drop logfile group group_number;
2. Recreate the corresponding log group:
Svrmgrl> alter database add Log File Group group_number ('Log _ file_descritpion ',...) Size log_file_size;

The corrupted log file is active and non-current:
1. Clear the corresponding log group:
Svrmgrl> alter database clear Unarchived logfile group group_number;

The damaged log file is the current active log file:
Run the following command to clear the corresponding log group:
Svrmgrl> alter database clear Unarchived logfile group group_number;
If clearing fails, incomplete restoration based on time points is allowed.
Open the database and back up the database in an appropriate way:
Svrmgrl> alter database open;

Some data files are damaged:
If the corrupted data file belongs to a non-system tablespace, the database can still be opened, but the corrupted data file cannot be accessed. In this case, the damaged data files can be recovered independently when the database is opened. If the data file in the system tablespace is damaged, the database system will terminate abnormally. In this case, the database can only be opened in the Mount mode, and then the data file is restored. You can check the database log file to determine whether the damaged data file actually belongs to the system tablespace.

Non-system tablespace Data File Corruption
1. Determine the name of the damaged file:
Svrmgrl> select name from V $ datafile where status = 'invalid ';
2. Leave the corrupted data file in the offline status:
Svrmgrl> alter database datafile 'datafile _ name' offline;

3. Recover the latest backup of the data file from the backup result set. You can directly recover data from tape from a point without database backup. You can use the appropriate RMAN script to recover data from a point with database backup.
4. Restore data files:
Svrmgrl> alter database recover datafile 'file _ name ';
5. Make database files online:
Svrmgrl> alter database datafile 'datafile _ name' online;
6. Use appropriate methods for full database backup.

System tablespace Data File Corruption:
1. Start the database in Mount Mode
Svrmgrl> startup Mount;
2. Recover the latest backup of the data file from the backup result set. You can directly recover data from tape from a point without database backup. You can use the appropriate RMAN script to recover data from a point with database backup.
3. Restore the system tablespace:
Svrmgrl> alter database recover datafile 'datafile _ name ';
4. Open the database:
Svrmgrl> alter database open;
5. Use appropriate methods for full database backup.

Tablespace corruption:
If the system tablespace is not damaged, the database can still be opened, but the damaged tablespace cannot be accessed. In this way, the damaged tablespace can be restored separately when the database is opened. If the system tablespace is damaged, the database system terminates abnormally. In this case, the database can only be opened in the Mount mode, and then the table space is restored. You can check the database log file to determine whether the currently corrupted tablespace is a system tablespace.

Non-system tablespace corruption:
1. Leave the corrupted tablespace In the offline status:
Svrmgrl> alter tablespace 'tablespace _ name' offline;
2. Recover the latest backup of the tablespace from the backup result set. You can directly recover data from tape from a point without database backup. You can use the appropriate RMAN script to recover data from a point with database backup.
3. Restore the tablespace:
Svrmgrl> alter database recover tablespace 'tablespace _ name ';
4. Make the tablespace online:
Svrmgrl> alter tablespace 'tablespace _ name' online;
5. Use appropriate methods for full database backup.

System tablespace corruption:
1. Start the database in Mount Mode
Svrmgrl> startup Mount;
2. Recover the latest backup of the system tablespace from the backup result set. You can directly recover data from tape from a point without database backup. You can use the appropriate RMAN script to recover data from a point with database backup.
3. Restore the system tablespace:
Svrmgrl> alter database recover tablespace system;
4. Open the database:
Svrmgrl> alter database open;
5. Use appropriate methods for full database backup.

All files in the entire database are damaged:
Damage to all files in the database is generally caused by a disaster that cannot be recovered in the shared disk array. In this case, the database can only be recovered. If the archive directory of the database is also lost, the database cannot be completely restored, resulting in the loss of user data.

Site with database backup not used:
1. Unpack the latest backup files from the tape to the corresponding directory.
2. Open the database in the Mount mode:
Svrmgrl> startup Mount;
3. Restore the database:
Svrmgrl> recover database until cancel;
4. Open the database:
Svrmgrl> alter database open resetlogs;
5. Use appropriate methods for full database backup.

Use a field with database backup:
1. Open the database in nomount mode:
Svrmgrl> startup nomount;
2. Use the corresponding RMAN script for Soft Recovery of the database.
$ RMAN prepare file = hot_database_restore.rcv
3. Open the database:
Svrmgrl> alter database open resetlogs;
4. Use appropriate methods for full database backup.

handling of typical emergencies in the context of the recent complete cold backup of the database:
data files, archiving and replaying logs, and control files are both lost or damaged:
no conditions for adding archives:
condition and hypothesis: no new archive log (s) has been generated since the last backup of the image; archivelog mode; image (cold) copy with synchronized datafile (s) and control file (s)
recovery procedure:
1. copy the copied datafile (s) and control file (s) to the original location:
$ CP/backup/good_one.dbf/orig_loc/bad_one.dbf
$ CP/backup/control1.ctl/disk1/control1.ctl
2. start the database with the Mount Option:
$ svrmgrl
svrmgrl> connect Intern Al
svrmgrl> startup Mount
3. restore the database with the old control file:
svrmgrl> recover database using backup controlfile until cancel;
*** media Recovery completed
(cancel is required immediately)
4. reset the logfiles (cannot be omitted for startup):
svrmgrl> alter database open resetlogs;
5. close the database and perform a full-database cold backup.

added archives:
condition and hypothesis: A new archive log (s) has been generated since the last backup of the image; archivelog mode; images with synchronized datafile (s) and control file (s) can be copied cold. archive log (s) is available.
recovery procedure:
1. if the database is not closed, close it first:
$ svrmgrl
svrmgrl> connect internal
svrmgrl> shutdown abort
2. copy the backup file back to the original location:
All database files
all control files (No archive (s) or Redo (s, the update of control files does not make any sense.)
all on-line redo logs (not archives)
init. ora file (option)
3. start the database:
$ svrmgrl
svrmgrl> connect internal
svrmgrl> startup

data files, which are lost or damaged at the same time as logs and Control Files:
condition and hypothesis: archivelog mode; images of all the lost files synchronized (cold) copy; archive log (s) available
recovery steps (Incomplete recovery is required):
1. if the database is not closed, close it first:
$ svrmgrl
svrmgrl> connect internal
svrmgrl> shutdown abort
2. copy the backup file back to the original location:
All database files
all control files
all on-line redo logs (not archives)
init. ora file (option)
3. start the database but not open it:
svrmgrl> startup Mount
4. incomplete Database recovery, application accumulation from the last image (cold) backup Archives:
svrmgrl> recover database until cancel using backup controlfile;
......
......
cancel
5. reset the logfiles (cannot be omitted for startup):
svrmgrl> alter database open resetlogs;
6. close the database and perform a full-database cold backup.

data files and control files are lost or damaged at the same time:
conditions and assumptions: archivelog mode; synchronized cold copies of datafile (s) and control file (s) are available; archive log (s) available
recovery steps:
1. copy the cold copies of datafiles (s) and control file (s) back to the original location:
$ CP/backup/good_one.dbf/orig_loc/bad_one.dbf
$ CP/backup/control1.ctl/disk1/control1.ctl
2. start the database with the Mount Option:
$ svrmgrl
svrmgrl> connect internal
svrmgrl> startup Mount
3. restore the database with the old control file:
svrmgrl> recover database until cancel using backup controlfile;
**** media Recovery completed
(cancel must be completed after the last archive log is applied)
4. reset the logfiles (cannot be omitted for startup):
svrmgrl> alter database open resetlogs;

when duplicate logs and control files are lost or damaged at the same time:
condition and hypothesis: All control files are lost or damaged; archivelog mode; images with control files (cold) copy
restore procedure:
1. if the database is not closed, close it first:
$ svrmgrl
svrmgrl> connect internal
svrmgrl> shutdown abort
svrmgrl> exit
2. overwrite the corrupted control file with the image (cold) of the Control File:
$ CP/backup/control1.ctl/disk1/control1.ctl
3. start the database but not open it:
$ svrmgrl
svrmgrl> connect internal
svrmgrl> startup Mount
4. drop the bad redo log (to eliminate hardware faults):
svrmgrl> alter database drop logfile group 2;
5. re-create redo log:
svrmgrl> alter database add logfile group 2 '/orig_loc/log2.dbf' size 10 m;
6. restore the database with the old control file:
svrmgrl> recover database until cancel using backup controlfile;
(must be cancel immediately)
7. reset the logfiles (cannot be omitted for startup):
svrmgrl> alter database open resetlogs;
8. shut down the database and perform a full-database cold backup

Only when the archived duplicate log is lost or damaged:
Select one of the following methods based on different environments and situations:
A. Back up all datafiles immediately (if the system uses a general hot backup or RMAN Hot Backup)
B. Shut down the database and perform cold backup immediately (if the system uses cold backup)
C. Take an adventure! Instead of backing up, let the database go on and wait until the next backup cycle to back up again. This error does not occur until the next backup cycle of the database is reached.
Note: if an error occurs and you need to recover the database, you can only recover the database to the operation site before the archive log fails. From another perspective, if the archive log (s) has a problem and the database does not need to be restored, there is no problem.

Troubleshooting of Oracle Logical Structure faults:
A logical structure fault generally refers to the loss of important data due to human error. In this case, the physical structure of the database is complete and consistent. This is not suitable for the full recovery of the original database. We generally use three methods to restore user data.

Use exp/IMP to restore user data:
This method can be used only if the lost data is backed up using the exp command.
1. Create a temporary user in the database:
Svrmgrl> create user test_user identified by test;
Svrmgrl> grant connect, resource to test_user;
2. From the file backed up by the previous exp command, add the table with lost data to the test user as a user:
$ Imp system/manager file = export_file_name tables = (lost_data_table_name ...) Fromuser = lost_data_table_owner touser = test_user constraint = N;
3. Use the corresponding DML statement to restore the lost data from the test user to the original user.
4. Delete the test user:
Svrmgrl> drop user test_user cascede;

Use logminer to restore user data:
Logminer is a log analysis tool provided by Oracle. It can analyze online and archive logs based on the data dictionary to obtain historical records of various DML operations in the database and rollback information of various DML operations. These users can re-Add the data lost due to misoperations to the database.
1. confirm that the utl_file_dir parameter of the database has been set. If not, add this parameter to the Oracle initialization parameter file and restart the database. The following example assumes utl_file_dir = '/opt/Oracle/db01 ';
2. Create the data dictionary information required by logminer. Assume that the generated data dictionary text file is dict. ora:
Svrmgrl> execute dbms_logmnr_d.build (dictionary_filename => 'dict. ora ', dictionary_location =>'/opt/Oracle/db01 ');
3. Determine the log to be analyzed or the range of archived logs. This can determine the approximate log range based on the time when the user misoperations occur. Assume that the log files may be/opt/Oracle/DB02/oradata/orcl/redo3.log and archived logs '/opt/Oracle/ARCH/orcl/orclarc_2017113.ora' During User misoperations '.
4. Create a list of log files to be analyzed and add them in sequence:
Svrmgrl> execute dbms_logmnr.add_logfile (logfilename => '/opt/Oracle/ARCH/orcl/orclarc_2017113.ora', Options => dbms_logmnr.new );
Svrmgrl> execute dbms_logmnr.add_logfile (logfilename => '/opt/Oracle/DB02/oradata/orcl/redo3.log', Options => dbms_logmnr.addfile );
5. Start log analysis. Assume that the analysis time is between '2017-06-28 12:00:00 'and '2017-06-28 13:00:00:
Svrmgrl> execute dbms_logmnr.start_logmnr (dictfilename => '/opt/Oracle/db01/dict. ora ', starttime => to_date ('12:00:00', 'yyyy-MM-DD hh: MI: ss '), endtime => to_date ('2017-06-28 13:00:00 ', 'yyyy-MM-DD hh: MI: ss '));
6. Obtain the analysis result:
Svrmgrl> select operation, SQL _redo, SQL _undo from V $ logmnr_contents;
7. Repair data based on the analysis results.
8. End logmnr:
Svrmgrl> dbms_logmnr.end_logmnr;
9. Back up the original database in an appropriate way.

Use Backup to restore user data:
This method is not used to recover from the original database. Instead, it uses database backup to re-create a new database on the new machine. Recover the database to the original database through backup and recovery on the new machine before the user's misoperation, so that you can obtain the lost data.
1. Install the database software on the new machine.
2. Install and debug the corresponding backup management software on the new database server for the scenario with database backup.
3. Restore the database based on the time point of the user's misoperation. For scenarios where database backup is not used, you can select the most recent backup tape before the user misoperations for recovery; you can use the RMAN script that is restored based on the time recovery point for the point with database backup.
4. re-open the database:
Svrmgrl> alter database open resetlogs;
5. retrieve lost user data from the new database and restore it to the original database through the DML operation.
6. Back up the original database in an appropriate way.

 

Method 1:
1. Install Oracle software
2. Run dbca to create a database. If the location is the same as Sid, dbname, and characterset, save the database as a script in the last step. If the database is not created, save the database and exit.
3. Open the database creation script (. BAT), run the statement manually (example):
mkdir E: \ oracle \ admin \ everac \ bdump
mkdir E: \ oracle \ admin \ everac \ cdump
mkdir E: \ oracle \ admin \ everac \ create
mkdir E: \ oracle \ admin \ everac \ pfile
mkdir E: \ oracle \ admin \ everac \ udump
mkdir V: \ database
mkdir V: \ oradata \ everac
set oracle_sid = everac1
E: \ oracle \ ora92 \ bin \ oradim.exe-New-Sid everac1-startmode m
E: \ oracle \ ora92 \ bin \ oradim.exe-edit-Sid Everac1-startmode A
E: \ oracle \ ora92 \ bin \ orapwd.exe file = E: \ oracle \ ora92 \ database \ pwdeverac1.ora Password = change_on_install
4. You can join the database to open the database. ----- Over

================================================= ================< br> method 2
1. after installing Oracle, create a database. The SID Name and path of the system default tablespace data file are the same as those of the original one. No other users or tablespaces need to be created.
2. Stop the Oracle service and overwrite the admin and oradata folders of the original database to the newly installed folder.
3. More importantly, replace the initsid. ora file and the pwdsid. ora file (password file) with the newly installed file. My files are under $ ORACLE_HOME \ database.
4. In addition, I replaced the def_sid.ora file under $ ORACLE_HOME \ sysman \ ifiles with the newly installed file (whether this step is required or not I am not sure, because my original system has been configured with OEM,)
5. Oracle is successfully restarted, just like the original one.