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 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
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
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
------------------------------------------------------------------
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
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:
Solution:
1 – Rebooting the standby server reduced memory utilization a little.