Oracle Database migration from Windows XP to Windows 7

Recently, all the company's operating systems have been upgraded to Windows 7. Our company has an ORACLE product software for Engineering Planning called Primavera Six (P6). So I need to migrate the P6 database from XP to the Win7 system. This is because the P6 database has been successfully migrated from XP to XP, and it is a success, so there is no problem. This time, no tests were conducted before migration. Haha, I'm a little overconfident. I have to perform tests first in the future. Otherwise, if it is to restrict time for database migration, especially to help customers with projects, it will be criticized by leaders and customers, fortunately, I am a database of my own company this time, and there is no time limit. I can search for the cause of the unsuccessful reply within a few days. Let me talk about this migration story.

I wrote an email to my colleagues who used the P6 system on the morning of May and told them that I had migrated the server database from April 11 to April 11, so the database could not be used during this period. In fact, it will not take such a long time. It is mainly because our company needs special personnel authorization to install and uninstall the software. All the software cannot be installed or uninstalled without authorization, A little abnormal. Haha. Well, let's just talk about the migration time. I told IT in April 10 that I had to upgrade OS to WIN7 in April 11. Today I backed up the database and related software. Of course, this server also contains MYSQL database information. Today, we will focus on ORACLE database backup. Mysql I wrote a small batch for automatic backup every day. Basically, there is no problem with migrating to WIN7. So I switched the database several times: alter system switch logfile. I want to prepare the database because it is relatively small.

1, so make a full-database cold backup

A. Shut down the database shutdown immediate.

B. Copy all the data files, including tempfile, datafile, redo logile, and controlfile.

2. Use RMAN for full-database backup under database mount.

A. The RMAN configuration is as follows:

RMAN> configure channel device type disk format 'd: \ bak \ backupset \ % U ';

RMAN> configure controlfile autobackup on;
RMAN> configure controlfile autobackup format for device type disk to 'd: \ bak \ ctrlbak \ % F ';

RMAN> configure retention policy to redundancy 5;
RMAN> configure retention policy to recovery window of 5 days;
RMAN> backup as compressed backupset database plus archivelog;

 

B. From the above, we can see that the paths of my backup files are:

CONFIGURE the automatic backup of Control Files: configure controlfile autobackup on;

Control File Path: d: \ bak \ ctrlbak

Data File Path: d: \ bak \ backupset

 

C. Start Using RMAN for backup.

RMAN> backup database;

After the backup is complete, copy the files under d: \ bak andThe online redo log file is also copied.Later, I will tell you how to use it when replying. Back up other backup files to the mobile hard disk.

This ended in April 10. Wait for IT to erase the XP system in April 11, install the WIN7 system, and then reply.

 

On October 16, April 11, IT had been waiting until three o'clock P.M. in the morning to upgrade OS to WIN7. So I immediately prepared to install MySQL and reply to the MYSQL database. The MYSQL database was restored successfully in less than one hour, install the self-developed client program and connect to the MYSQL database. Everything is normal. The information system I developed is successfully connected to the MYSQL database, and the client runs properly. The following describes how to install Primavera Six. Of course, this software automatically installs the ORACLE database together. The software still uses ORACLE10.2.0.1.0. It took about 30 minutes to install P6. This installation of P6 actually has a very interesting story. Let me tell you the story below:

Because our WIN7 was not installed one day later, there were a lot of people in the company. IT was divided into three weeks for batch installation. When one of my colleagues installed P6 a week ago, unavailable. Later, Let me see why. I checked the database. When I configured the database connection, the system said that I didn't have the permission to access C: \ Program Files (x86) \, the specific directory cannot be remembered, and the file is written. To connect to the database, you need to write the database configuration information to the original disk. IT turns out that I have no permission to write data. I am confused about my own computer. So I asked our IT staff, they said IT was the company's POLICY. All users do not have the administrator's super user permission. This permission is in their IT. I am dizzy on the spot. Haha! There is no way. We must abide by the company's POLICY. So I tried to create a folder under the C: \ Program Files (x86) directory, and the system prompts that I need to enter the user name and password for verification. Again, I fainted on the spot and asked me not to create a directory. I need to check it, no wonder the program cannot write information to this directory, so I returned to the C root directory and tried to create a folder without verification, it proves that I can have the write permission under the C root directory. You may ask me why I want to install the database and System Disk on a disk, that is, drive C, all our computers have only one partition disk, that is, only one C disk. It is strange from time to time that you may ask, isn't it afraid that the system disk of the virus is broken, and the database will not suffer. We recommend that you monitor anti-virus software. Of course, if the disk is broken, I can't do it. Through the above analysis, I will analyze that we have a problem with the permissions under C: \ Program Files (x86), because many installation programs are installed in C by default: \ Program Files (x86), so we do not have the permission to write the database configuration file to the local directory under permission control. So I uninstalled P6 and re-installed it. At this time, I put the installation directory under the root directory of drive C. It took 20 minutes and the installation was successful. Of course, the database configuration is successful at this time. This story is complete.

After the above database recovery, after the database is installed, I copied the data I backed up with RMAN to C: \ bak \ on this computer. I will prepare to use RMAN for recovery. The specific steps are as follows:

A. Hit the database to the nomount state.

SQL> startup nomount;

B. Run the following command to start the RMAN interface:

C: \> rman target sys/pwd

B. Run the following command to restore the backup control file:

RMAN> restore controlfile from 'disk path ';

C. After the control file is restored, I can set the database to the mount State. In this example, RMAN can read the information in the control file to restore the database. I use controlfile, catalog is not used. Therefore, the information is stored in the control file. To MOUNT the database, run the following command in RMAN:

RMAN> SQL 'alter database mount ';

After the mount succeeds, I can start restore the database.

D. Restore the database

RMAN> restore database;

Restore failed, for example:

The above prompt shows that no backup set file is found. I have backup files. The files are stored in c: \ bak \ backupset and c: \ bak \ ctrlbak, which are the latest backup of data files and control files. Why not? So I am RMAN, and I certainly found the path of the backup file through the record information in the control file. So I guess there is no file in the path of the RMAN record. I can use list backup to check the path of the backup set.

See:

The backup set path recorded in the control file is d: \ bak \ backupset. My backup file is copied to c: \ bak \ backupset. Of course, RAMN will see the above error, saying that the backup file cannot be found. But now I have another tragedy. As mentioned above, our computer has only one drive C and there are no other drive letters. There is no way to copy the data to the mobile hard disk, and then try to set the drive letter of the backup data I need for the mobile hard disk to drive D, and then create the bak \ backupset path above, copy the backup file to this directory and restore it. After the paths are set, restore the database again. For example:

 

From the preceding figure (reading from backup piece d: \ bak \ backupset \ 3AP5A2E3_1_1), we can see that the files read at this time are my backup files. Under normal circumstances, it should be successful.

We can see from the above that the restore is successful. Next, let's recover the database.

RMAN> recover database;

When I recover the database, the system prompts that one log is missing, which should be because the online redo log is missing. This is why I mentioned above that I want to copy the redo log to the backup. Copy the online redo log of the backup to the corresponding location and continue the recover database, for example:

This recover database is successful.

Next, open the database. Because the database has been recovered, you need to use resetlogs to open the database. The command is as follows:

RMAN> SQL 'alter database open resetlogs ';

Now I have recovered the database with RMAN. At this time, I am very happy. However, my tragedy is that it is not over, and now I have come again.

Since the database has been restored successfully, I will open the P6 application to see if it can be used. When I open it, it is okay for the program to load data at the beginning, however, after loading for a while, a window pops up as follows:

 

 

The error message above indicates that the database is successfully loaded if the application fails to load the database. I don't know why. My first reaction was not the reason why Windows XP went to Windows 7. So I went to IT to find an XP system and made the same recovery actions on IT. IT was also because the database was not recovered, when the P6 application is opened, the same error message is displayed, so the reason related to the operating system is ruled out.

At this time, I started to log on to the database through SQLPLUS to query the relevant tables.

I checked the dba_data-files,

When I check dba_temp_files, an error occurs, saying that my TEMP file is damaged and cannot be read. The specific screenshot was not taken at the time, so I suddenly realized that the temporary tablespace was broken, causing the P6 system to prompt a strange error during login, saying that the data loading was unsuccessful, this is because temporary tablespace is used for sorting and other actions during data loading. So I re-created a temporary tablespace. The command is as follows:

Create temporary tablespace TEMP1 tempfile 'C: \ oraclexe \ oradata \ XE \ temp1.dbf' size 64 M autoextend on maxsize 2G

Extent management local uniform size 1 M

Set TEMP1 as the default temporary tablespace of the database:

Alter database default temporary tablespace TEMP1;

Delete the damaged temporary tablespace of TEMP.

Drop tablespace TEMP including contents and datafiles;

Log on to the P6 client application again, and finally succeeded.

 

I will make the following summary for this story: it can be said that it is a lessons learned.

A. Before performing A formal database migration, you must simulate it and try to test it to avoid the strange and strange problem during the formal migration.

B. The source database server and Target database server must be preferably a drive letter, with the same path planning. The backup file cannot be found during my recovery.

C. Make sure to back up the online redo log when backing up RMAN. Otherwise, I will see the problem that I cannot find the log in the last recover database.

D. After the database is restored to the Target server, check whether all the logical files of the database are correct, such as dba_data_files and dba_temp_files. Otherwise, my TEMP file may be broken, A strange problem occurs.

 

The entire ORACLE kernel migrated from XP to WIN7

 

The story is over. Thank you!