Today, I received a call from Shanghai Tac, who reported that the recent week's Rman backup was slow and the amount of data was small, but an incremental backup would take about 3 days.
Database version: Oracle9204 RAC
Operating system version: Solaris 9
The Rman Backup statement is:
Rman> run{
Allocate channel C1 type disk;
Backup incremental Level 1 database plus archivelog Delete input;
Release channel C1;
}
Log on to the server where the catalog database resides, and view backup logs:
Allocated CHANNEL:C1
Channel c1:sid=48 Devtype=disk
Allocated CHANNEL:C2
Channel c2:sid=46 Devtype=disk
Starting backup at 2007-09-18:04:21:08
Discovery has been stopped at this step, do not go down ...
Log in to the target database to see what waiting events are on the session:
Sql> Select Event,sid,p1,p2,p3 from v$session_wait where event isn't like ' sql*% ' and event not ' like ' rdbms% ';
EVENT SID P1 P2 P3
---------------------------------------------------------------- ---------- ---------- ---------- ----------
Pmon Timer 1 300 0 0
GES Remote Message 4 32 0 0
GCS Remote Message 5 64 0 0
GCS Remote Message 7 64 0 0
Log switch/archive 22 2 0 0
Wakeup time Manager 15 0 0 0
Smon Timer 12 300 0 0
7 rows selected.
A session with Sid 22 was found to wait for the most serious, waiting for the event to log switch/archive, which usually occurs because the first log archive has not completed until the log group loop is full, and the wait may be an IO problem.
Solution:
Move archive files to fast disks;
Adjust the log_archive_max_processes.
Check session 22 for the corresponding SQL statement:
Sql> Select Sql_text from V$sqltext a where a.hash_value= (select Sql_hash_value from V$session b
2 where b.sid= ') Order by piece ASC;
Sql_text
----------------------------------------------------------------
Alter system archive Log current
The command was executed manually on both nodes, and it was slow enough to return for a few hours.
Remove the plus archivelog Delete input command from the Rman Backup statement and do a test.
Rman> run{
Allocate channel C1 type disk;
Backup incremental level 1 database;
Release channel C1;
}
The 2-minute execution indicates that it is due to the slow archiving of the current log that there is a problem with the disk I/O where the archive log resides.
Log in to the database to view the archive path:
Sql> Show Parameter Archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Log_archive_dest string
Log_archive_dest_1 string location=/export/home/oracle/dev/msp/log_archive_dest_1
Log_archive_dest_10 string
Log_archive_dest_2 string location=/export/home/oracle/dev/msp/log_archive_dest_2
Log_archive_dest_3 string
Log_archive_dest_4 string
Log_archive_dest_5 string
Log_archive_dest_6 string
log_archive_dest_7 string
Log_archive_dest_8 string
Log_archive_dest_9 string
Log_archive_dest_state_1 string Enable
Log_archive_dest_state_10 string Enable
Log_archive_dest_state_2 string Enable
Log_archive_dest_state_3 string Enable
Log_archive_dest_state_4 string Enable
Log_archive_dest_state_5 string Enable
Log_archive_dest_state_6 string Enable
Log_archive_dest_state_7 string Enable
Log_archive_dest_state_8 string Enable
Log_archive_dest_state_9 string Enable
Standby_archive_dest string?/dbs/arch
Sql> Show Parameter log_archive_max_processes
Log_archive_max_processes Integer 1
Test the rate of two archive path volumes, the results show 1 seconds about 1M, this speed is slow enough, normal should write 10M or so.
Modify initialization parameters, modify the archive log path to local disk/var/backup, add log_archive_max_processes=2, restart the database, and manually execute ALTER system archive log current. return the result soon; Perform Rman testing and backup quickly.
Conclusion: The Rman backup is unusually slow due to the problem of disk I/O read and write to the archive log.