Archive for 11/01/2008 - 12/01/2008

How to Register Older Backups to RMAN Catalog

Cataloging Older Files in the Recovery Catalog

RMAN> CATALOG DATAFILECOPY '/disk1/old_datafiles/01_01_2003/users01.dbf';

RMAN> CATALOG ARCHIVELOG '/disk1/arch_logs/archive1_731.dbf', '/disk1/arch_logs/archive1_732.dbf';

RMAN> CATALOG BACKUPPIECE '/disk1/backups/backup_820.bkp';

You can also catalog multiple backup files in a directory at once, using the CATALOG START WITH command, as shown in this example:

RMAN> CATALOG START WITH '/disk1/backups/';

RMAN lists the files to be added to the RMAN repository and prompts for confirmation before adding the backups.

Be careful when creating your prefix for CATALOG START WITH. RMAN scans all paths for all files on disk which begin with your specified prefix. The wrong prefix may include more files than you intend. For example, a group of directories /disk1/backups , /disk1/backups-year2003, /disk1/backupsets, and /disk1/backupsets/test and so on, all contain backup files. The command

RMAN> CATALOG START WITH '/disk1/backups';

catalogs all files in all of these directories, because /disk1/backups is a prefix for the paths for all of these directories. In order to catalog only backups in the /disk1/backups directory, the correct command would be:

RMAN> CATALOG START WITH '/disk1/backups/';

How to Create an RMAN Recovery Catalog Database

- Create a database with DBCA preferably named RMANCAT, CATDB ...;

- Create a user which will be owner of the recovery catalog;

$ sqlplus / as sysdba
SQL> create user rmanuser identified by rmanuser;

- Grant necessary roles, priviledges to user

SQL> grant recovery_catalog_owner to rmanuser;
SQL> grant connect, resource to rmanuser;

- Create Recovery Catalog

$rman catalog rmanuser/rmanuser
RMAN> create catalog;

- Register the databases which will use this database as recovery catalog

$rman target / catalog rmanuser/rmanuser@RMANCAT
RMAN> register database;

- Verify that the registration was successful by running REPORT SCHEMA

RMAN> report schema;

Recover Database Until

If you need to recover your database to a point in time by scn, sequence or time, you can use the following query to see the relation between time-scn-sequence, after restoring your database from a proper backup.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YY HH24:MI:SS';


SQL> select NAME, SEQUENCE#, THREAD#, FIRST_TIME, FIRST_CHANGE#, NEXT_TIME, NEXT_CHANGE# from v$archived_log where SEQUENCE# > 166;

Sample Output:

NAME SEQUENCE# THREAD# FIRST_TIME FIRST_CHANGE# NEXT_TIME NEXT_CHANGE#
------------------------------ --------- ------- ---------- ----------------- --------- ----------------
/arch/1_166_593039.arc 166 1 10-11-08 06:31:15 34516912
10-11-08 06:31:36 34521645
/arch/1_167_593039.arc 167 1 10-11-08 06:31:36 34521645
10-11-08 06:31:56 34527024
/arch/1_168_593039.arc 168 110-11-08 06:31:56 34527024
10-11-08 06:32:10 34532094
/arch/1_169_593039.arc 169 1 10-11-08 06:32:10 34532094
10-11-08 06:32:35 34537223
...

You can modify the where clause depending on your needs. SEQUENCE# gives the sequence number of the archive log. FIRST_CHANGE# and NEXT_CHANGE# specify the first and last System Change Number (SCN); FIRST_TIME and NEXT_TIME specify the starting and ending time of that archivelog. regarding to these information you can decide any of the following recover operations:

RMAN> recover database until sequence 162280;
RMAN> recover database until SCN 34527024;
RMAN> recover database until time '10-11-08 06:31:15'

,or if you want to manually control recover process with specifying archive logs one by one, you can use "until cancel" clause in SQL. This recovery process continues until you cancel. If your archive logs are not on their default path you can specify the full path of the archive logs in this recovery process.

SQL > recover database until cancel;

How to Query RMAN Session Status

Here is a query to V$SESSION_LONGOPS view that shows the status of RMAN sessions. It also calculates the percentage of the job that has been completed.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

Sample Output:

SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE
--- ---------- ---------- ------ ---------- ----------
18 11 1 1995623 3083904 64.71
15 11 1 1863491 3599872 51.77
14 11 1 1936968 3339904 57.99
16 11 1 1843544 3083904 59.78

Changing SYS Password in Dataguard Environment

If you change SYS password with ALTER USER SYS IDENTIFIED BY NEWPASSWORD on the primary database of a dataguard environment, primary side stops to transfer archivelogs to standby and you will see an error on primary database alertlog file like:

------------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------------

This is because "If you issue the ALTER USER statement to change the password for SYS, both the password stored in the data dictionary and the password stored in the password file are updated." So your password file is updated in primary side but not in standby side.

In this situation set your password file in standby server with:
orapwd file=$ORACLE_HOME/dbs/orapwSID password=newpassword; (don't forget to move/delete old one)

Conclusion: If you're going to change your sys password in a dataguard environment you must set the password files with new password in both primary and standby servers.

Dataguard Performance

Edit (07,2013):
The following information is important about Physical Data Guard Redo Apply performance:
11g Media Recovery performance improvements include:
•More parallelism by default
•More efficient asynchronous redo read, parse, and apply
•Fewer synchronization points in the parallel apply algorithm
•The media recovery checkpoint at a redo log boundary no longer blocks the apply of the next log

In 11g, when tuning redo apply consider following:

•By default recovery parallelism = CPU Count-1. Do not use any other values.
•Keep PARALLEL_EXECUTION_MESSAGE_SIZE >= 8192
•Keep DB_CACHE_SIZE >= Primary value
•Keep DB_BLOCK_CHECKING = FALSE (if you have to)
•System Resources Needs to be assessed
•Query what MRP process is waiting
select a.event, a.wait_time, a.seconds_in_wait from gv$session_wait a, gv$session b where a.sid=b.sid and b.sid=(select SID from v$session where PADDR=(select PADDR from v$bgprocess where NAME='MRP0'))

Check: Active Data Guard 11g Best Practices Oracle Maximum Availability Architecture White Paper


When tuning redo transport service, consider following:

1 - Tune LOG_ARCHIVE_MAX_PROCESSES parameter on the primary.
•Specifies the parallelism of redo transport
•Default value is 2 in 10g, 4 in 11g
•Increase if there is high redo generation rate and/or multiple standbys
•Must be increased up to 30 in some cases.
•Significantly increases redo transport rate.
2 - Consider using Redo Transport Compression:
•In 11.2.0.2 redo transport compression can be always on
•Use if network bandwidth is insufficient
•and CPU power is available


Also consider:
3 - Configuring TCP Send / Receive Buffer Sizes (RECV_BUF_SIZE / SEND_BUF_SIZE)
4 - Increasing SDU Size
5 - Setting TCP.NODELAY to YES



Check: Redo Transport Services Best Practices Chapter of Oracle® Database High Availability Best Practices 11g Release 1
-------------------------------------------------------------------
Original Post:
Problem: Recovery service has stopped for a while and there has been a gap between primary and standby side. After recovery process was started again, standby side is not able to catch primary side because of low log applying performance. Disk I/O and memory utilization on standby server are nearly 100%.

Solution:
1 – Rebooting the standby server reduced memory utilization a little.
2 – ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 8 DISCONNECT FROM SESSION;
In general, using the parallel recovery option is most effective at reducing recovery time when several datafiles on several different disks are being recovered concurrently. The performance improvement from the parallel recovery option is also dependent upon whether the operating system supports asynchronous I/O. If asynchronous I/O is not supported, the parallel recovery option can dramatically reduce recovery time. If asynchronous I/O is supported, the recovery time may be only slightly reduced by using parallel recovery.
3 – SQL>alter system Set PARALLEL_EXECUTION_MESSAGE_SIZE = 4096 scope = spfile;
Set PARALLEL_EXECUTION_MESSAGE_SIZE = 4096
When using parallel media recovery or parallel standby recovery, increasing the PARALLEL_EXECUTION_MESSAGE_SIZE database parameter to 4K (4096) can improve parallel recovery by as much as 20 percent. Set this parameter on both the primary and standby databases in preparation for switchover operations. Increasing this parameter requires more memory from the shared pool by each parallel execution slave process.
4 – Kernel parameters that changed in order to reduce file system cache size.
dbc_max_pct 10 10 Immed
dbc_min_pct 3 3 Immed
5 – For secure path (HP) load balancing, SQL Shortest Queue Length is chosen.
autopath set -l 6005-08B4-0007-4D25-0000-D000-025F-0000 -b SQL
Powered by Blogger.

Page Views

- Copyright © Emre Baransel - Oracle Blog -Metrominimalist- Powered by Blogger - Designed by Johanes Djogan -