I asked many times what the difference between full backup and level 0 incremental backup is.Whether or not they are the same and ...
Incremental level 0 backup which is the base for subsequent incremental backup copies all blocks containing data same as full backup. (Same functionality)
The only difference between these two backups is that a full backup never considered as base backup in incremental strategy.
Therefore, if you select incremental strategy as part of your backup strategy, you need at least one level 0 incremental backup.
TIP #13: Ideal RMAN format for backup
Naming RMAN backup is vital.
Proper backup name gives you better idea of which backup may be used during recovery.
My ideal RMAN backup format is : backup_%d_set%s_piece%p_%T_%U for backupsets when duplexing is not used. For duplexing backup it would be backup_%d_set%s_piece%p_copy%c_%T_%U
The following is brief description of these tags :
Proper backup name gives you better idea of which backup may be used during recovery.
My ideal RMAN backup format is : backup_%d_set%s_piece%p_%T_%U for backupsets when duplexing is not used. For duplexing backup it would be backup_%d_set%s_piece%p_copy%c_%T_%U
The following is brief description of these tags :
- %d : name of database
- %s :backup set number. (Unique for controlfile lifetime)
- %p :piece number in backup set.
- %T : Specified date in format YYYYMMDD
- %U : Unique number consist of %u_%p_%c
- %c : copy number of backup piece.when duplexing of backup piece is in use
Please pay attention that these tags are case sensitive.
If no format is specified, Oracle by default uses %U.For controlfile autobackup, I prefer to use default config which is %F.This tag has DBID in the filename which would be necessary in the case of recovery when RMAN catalog is not accessible.This tag can not be used for backup sets.
Enjoy reading ...
TIP #12: Can not use TNS to login to database.
When I tried to connect to database via TNS, I got the following error :
ERROR - DBPrereq DBConection error
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
IBM AIX RISC System/6000 Error: 2: No such file or directory
However, database was available and was accessible from sqlplus in the box without using tnsnames.
TNS entry in tnsnames.ora was pinggable (tnsping for this entry was OK).
Environment settings were fine.
The problem was because of spare / in Oracle_home in listener.ora.
Wrong listener.ora
====================
SID_LIST_LISTENER_DASDBM =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /o029/home/metaAS10g/product/10.2.0.2/)
(PROGRAM = extproc)
)
(SID_DESC =
((GLOBAL_DBNAME = dasdbm.isc.upenn.edu)
(SID_NAME = dasdbm)
(ORACLE_HOME = /o029/home/metaAS10g/product/10.2.0.2/)
)
)
Correct listener.ora
====================
SID_LIST_LISTENER_DASDBM =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /o029/home/metaAS10g/product/10.2.0.2)
(PROGRAM = extproc)
)
(SID_DESC =
((GLOBAL_DBNAME = dasdbm.isc.upenn.edu)
(SID_NAME = dasdbm)
(ORACLE_HOME = /o029/home/metaAS10g/product/10.2.0.2)
)
)
ERROR - DBPrereq DBConection error
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
IBM AIX RISC System/6000 Error: 2: No such file or directory
However, database was available and was accessible from sqlplus in the box without using tnsnames.
TNS entry in tnsnames.ora was pinggable (tnsping for this entry was OK).
Environment settings were fine.
The problem was because of spare / in Oracle_home in listener.ora.
Wrong listener.ora
====================
SID_LIST_LISTENER_DASDBM =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /o029/home/metaAS10g/product/10.2.0.2/)
(PROGRAM = extproc)
)
(SID_DESC =
((GLOBAL_DBNAME = dasdbm.isc.upenn.edu)
(SID_NAME = dasdbm)
(ORACLE_HOME = /o029/home/metaAS10g/product/10.2.0.2/)
)
)
Correct listener.ora
====================
SID_LIST_LISTENER_DASDBM =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /o029/home/metaAS10g/product/10.2.0.2)
(PROGRAM = extproc)
)
(SID_DESC =
((GLOBAL_DBNAME = dasdbm.isc.upenn.edu)
(SID_NAME = dasdbm)
(ORACLE_HOME = /o029/home/metaAS10g/product/10.2.0.2)
)
)
TIP #11: Export failed, generate ORA-07745 error
This summary is not available. Please
click here to view the post.
TIP #10: How to find password of existing database link
I faced with a odd situation.I `d like to copy some databases link from source DBs to target DBs. In order to create database link in target database, I decided to run "create database link" sql statement.However, for creating database link in target database, I need to know username and password which is currently used by the existing database link in source database.
sys.link$ has unencrypted password value for each database link.
The following is sample query.
select 'create database link ' || name ||' connect to '|| userid ||' identified by ' || password || ' using '||''''||host ||''''||';' from sys.link$
sys.link$ has unencrypted password value for each database link.
The following is sample query.
select 'create database link ' || name ||' connect to '|| userid ||' identified by ' || password || ' using '||''''||host ||''''||';' from sys.link$
TIP #9 : Track many open cursors.
If cursor in PL/SQL code get open but never get close, Oracle may report too many open cursors error in Alert log.
I found the following code handy to track the cause of problem.
select
SADDR,
SID,
USER_NAME,
ADDRESS,
HASH_VALUE,
SQL_ID,
SQL_TEXT
from
v$open_cursor
where
sid in(SELECT sid FROM V$OPEN_CURSOR group by sid having count(*)>&threshold);
I found the following code handy to track the cause of problem.
select
SADDR,
SID,
USER_NAME,
ADDRESS,
HASH_VALUE,
SQL_ID,
SQL_TEXT
from
v$open_cursor
where
sid in(SELECT sid FROM V$OPEN_CURSOR group by sid having count(*)>&threshold);
Subscribe to:
Posts (Atom)