HOW TO CATALOG TAPE BACKUP PIECES
Applicable Versions: Release 10.1 to 11.1
Starting with 10g, it's possible to use the rman CATALOG command to add backup
pieces stored in DISK to the rman repository.
You may need to catalog backup pieces in different situations like
. Copy or move a backup piece with OS command
. The RMAN Metadata information was removed and Piece still exist
. Take NOCATALOG Backup on one server and moved to another server fro restore
. Recreate controlfile and lost all backup information
. When controlfile autobackup is off, and you backup the controlfile and then
archivelog file. In this case you can restore the controlfile and not
all archivelogs.
Catalog from TAPE is not possible, But there is undocumented command that
allows to catalog TAPE backup piece
From 10.1 Onwards
rman> catalog device type 'SBT_TAPE' backuppiece 'handle';
To apply this command we have to do automatic channel configuration.
Its mandatory to do automatic configuration.
rman> show all;
rman> configure channel device type 'SBT_TAPE' parms <mml parameters>
example: configure channel device type 'SBT_TAPE' parms 'env=(env_client=
'xxx', env_server='xxx');
11gR2 Installation on RHEL 5
| 11gR2 Installation on RHEL 5 | ||
| No | Steps | Details |
| Hardware Requirement | ||
| 1 | Atleast 4 GB RAM is required | grep MemTotal /proc/meminfo |
| 2 | Swap Space | grep SwapTotal /proc/meminfoPropotional to the sysem’s physical RAM 8 to 32GB = 1.5*RAM |
| 3 | Disk Space Requirement | 1. 1024 MB in /tmp - 2. Approx.4.4 GB of disk space for the oracle software 3. 1.7 GB for the preconfigured Database(optional) |
| Kernal Requirement | ||
| 1 | System should be running in following kernal version or more than that 2.6.18-92 |
uname -r |
| Installing Required Packages | ||
| 1 | Make sure installing all the packages including the dependanace packages | binutils-2.17.50.0.6 compat-libstdc++-33-3.2.3 compat-libstdc++-33-3.2.3 (32 bit) elfutils-libelf-0.125 elfutils-libelf-devel-0.125 gcc-4.1.2 gcc-c++-4.1.2 glibc-2.5-24 glibc-2.5-24 (32 bit) glibc-common-2.5 glibc-devel-2.5 glibc-devel-2.5 (32 bit) glibc-headers-2.5 ksh-20060214 libaio-0.3.106 libaio-0.3.106 (32 bit) libaio-devel-0.3.106 libaio-devel-0.3.106 (32 bit) libgcc-4.1.2 libgcc-4.1.2 (32 bit) libstdc++-4.1.2 libstdc++-4.1.2 (32 bit) libstdc++-devel 4.1.2 make-3.81 numactl-devel-0.9.8.x86_64 sysstat-7.0.2 |
| Set Kernal Parameters | ||
| 1 | Add the following lines in the file /etc/sysctl.conf sysctl -p |
kernel.shmall = Physical Memory / page size [ getconf PAGE_SIZE=4 ] kernel.shmmax = 1/2 of the RAM [Should not greater than 4GB] kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 512 * Processes [Min 6815744] fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65500 [Must match Exactly] net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 aio-max-nr = 1048576 |
| /proc/sys/kernel/sem | semmsl = 250 semmns = 32000 semopm = 100 semmni = 128 |
|
| Create groups and user | ||
| 1 | Create Oracle Groups and User | # groupadd oinstall # groupadd dba # useradd -g oinstall -G dba oracle # password oraclecreate directory and give the ownership# mkdir -p /u01/app/oracle # chown -R oracle:oinstall /u01/app/oracleIf the oracle user exists, but its primary group is not oinstall or it is not a member of the dba group, then enter the following command: # /usr/sbin/usermod -g oinstall -G dba oracle |
| Set the session Limits for the oracle user | ||
| 1 | Set Shell Limits for the oracle User. Assuming that the “oracle” Unix user will perform the installation,add these lines /etc/security/limits.conf | oracle soft nproc 2047 oracle hard nproc 16384 oracle soft nofile 1024 oracle hard nofile 65536 |
| 2 | add tohe following lines in /etc/pam.d/login | Add the following linesession required pam_limits.so |
| 3 | Add the follwing lines to the /etc/profile | if [ $USER = "oracle" ]; then if [$SHELL = "/bin/ksh" ]; then ulimit -u 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi fi |
| gcc –version g++ –version Should return 4.1.x |
4.1.2 | |
| Installation | ||
| 1 | Login as oracle user | ./runIntaller |
| Screen Shots | ||
| | ||
Step by step Upgrade from 10gR2 to 11gR2
| Upgrade 10.2.0.5[64Bit] to 11gR2[64Bit] | |
| Steps | Details |
| Install Oracle 11gR2 in new Oracle Home | make sure there are no Linking-Error Install Latest Patchset available [11.2.0.2] |
| Take a Cold backup of source Database | |
| Copy all database file to appropriate folder | Create directories for the following:Datafile Control file Redolog Diag files Archive |
| Copy utlu 112i.sql from ORACLE_HOME/rdbms/admin and execute in source database. | Check the spool file and examine the output of the upgrade$ sqlplus ‘/ as sysdba’ SQL> spool upgrade_info.log SQL> @utlu112i.sql SQL> spool off SQL> Depending upon the spool file change initialization parameter, deprecated and obsolete parameters |
| Run Check the Integrity of the source databasedbupgdiag.sql | If script reports any invalid objects, run utlrp.sql to validate invalid objectSQL> @ORACLE_HOME/rdbms/admin/utlrp.sql
After vaklidating invalid object Re-run dbupgdiag.sql and make sure everything is fine. |
| Deprecated CONNECT Role | This query can be used to identify ehich users and roles are granted the CONNECT PrivlegeSELECT grantee FROM dba_role_privs WHERE granted_role = ‘CONNECT’ and grantee NOT IN ( ‘SYS’, ‘OUTLN’, ‘SYSTEM’, ‘CTXSYS’, ‘DBSNMP’, ‘LOGSTDBY_ADMINISTRATOR’, ‘ORDSYS’, ‘ORDPLUGINS’, ‘OEM_MONITOR’, ‘WKSYS’, ‘WKPROXY’, ‘WK_TEST’, ‘WKUSER’, ‘MDSYS’, ‘LBACSYS’, ‘DMSYS’, ‘WMSYS’, ‘EXFSYS’, ‘SYSMAN’, ‘MDDATA’, ‘SI_INFORMTN_SCHEMA’, ‘XDB’, ‘ODM’); |
| In case database to be downgraded, create a script for DBLINK | SELECT ‘CREATE ‘||DECODE(U.NAME,’PUBLIC’,'public ‘)||’DATABASE LINK ‘||CHR(10) ||DECODE(U.NAME,’PUBLIC’,Null, ‘SYS’,”,U.NAME||’.')|| L.NAME||chr(10) ||’CONNECT TO ‘ || L.USERID || ‘ IDENTIFIED BY “‘||L.PASSWORD||’” USING ”’||L.HOST||”” ||chr(10)||’;’ TEXT FROM SYS.LINK$ L, SYS.USER$ U WHERE L.OWNER# = U.USER#; |
| Check for TIMESTAMP with TIMEZONE datatype | select TZ_VERSION from registry$database; |
| Check that National Characterset (NLS_NCHAR_CHARACTERSET) | select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_NCHAR_CHARACTERSET’;If this is UTF8 or AL16UTF16 then no action is needed. If is not UTF8 or AL16UTF16 then refer the following article |
| Check Schemas with Stale Statistics | $ sqlplus ”/as sysdba”SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; |
| Check for logical corruption | Set verify off Set space 0 Set line 120 Set heading off Set feedback off Set pages 1000 Spool analyze.sqlSELECT ‘Analyze cluster “‘||cluster_name||’” validate structure cascade;’ FROM dba_clusters WHERE owner=’SYS’ UNION SELECT ‘Analyze table “‘||table_name||’” validate structure cascade;’ FROM dba_tables WHERE owner=’SYS’ AND partitioned=’NO’ AND (iot_type=’IOT’ OR iot_type is NULL) UNION SELECT ‘Analyze table “‘||table_name||’” validate structure cascade into invalid_rows;’ FROM dba_tables WHERE owner=’SYS’ AND partitioned=’YES’;spool off This Will create ascript called analyze.sql. Now execute this $ sqlplus “/ as sysdba” |
| Ensure that all snapshot refreshes are successfully completed, and that replication is stopped. | SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times; |
| Ensure that no files need media recovery | Query v$recover_file;SELECT * FROM v$recover_file; It should return no rows |
| Ensure that no files are in backup mode | SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;This should return no rows |
| Resolve outstanding distributed transactions prior to the upgrade. | SQL> select * from dba_2pc_pending;incase it returns rows you should do the following:
SQL> SELECT local_tran_id FROM dba_2pc_pending; |
| To check if a standby database exists, issue the following query: | SELECT SUBSTR(value,INSTR(value,’=',INSTR(UPPER(value),’SERVICE’))+1) FROM v$parameter WHERE name LIKE ‘log_archive_dest%’ AND UPPER(value) LIKE ‘SERVICE%’;If this query returns a row, then sync the standby database with the Primary database. 1. Make sure all the logs are transported to the standby server after a final log switch in the primary. 2. Start the recovery of the standby database with the NODELAY option. |
| Disable all batch and cron jobs. | |
| Ensure the users sys and system have ‘system’ as their default tablespace. You must have sufficient space in the tablespace or be set to extents unlimited. |
SELECT username, default_tablespace FROM dba_users WHERE username in (‘SYS’,'SYSTEM’);If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace to SYSTEM by using the below command.SQL> ALTER user SYS default tablespace SYSTEM; SQL> ALTER user SYSTEM default tablespace SYSTEM; |
| Ensure that if the aud$ table exists that it is in the sys schema and in the system tablespace. | SELECT owner,tablespace_name FROM dba_tables WHERE table_name=’AUD$’;If the AUD$ table is not in SYSTEM tablespace and not owned by the SYS user then before doing the upgrade put is back of the SYSTEM tablespace and it should be owned by SYS . |
| Check whether database has any externally authenticated SSL users. | SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = ‘GLOBAL’;If Exist Do this steps after Upgrade1.Make sure all following Env Variables are pointing to 11g - ORACLE_BASE - ORACLE_HOME - PATH Check oratab file also. 2. vi /etc/oratab |
| Check the location of datafile, controlfile and redo logs. Also take a backup of all configuration files like listener.ora tnsnames.ora | $ cp listener.ora /backup_ora $ cp tnsnames.ora /backup_oraSQL> SELECT name FROM v$controlfile; SQL> SELECT file_name FROM dba_data_files; SQL> SELECT group#, member FROM v$logfile;. |
| Stop the listener for the database, dbconsole and isqlplus | $ lsnrctl stop $ emctl stop dbconsole $ isqlplusctl stop |
| Shutdown the database. | $ sqlplus “/as sysdba” SQL> shutdown immediate;Tak e a cold backup or RMAN backupConnect to RMAN: rman “target / nocatalog” RUN sql> Shutdown immediate |
| Edit init<sid>.ora file | Comment out any obsoleted parameters – Preupgrade Check log -Change all deprecated parameters – Preupgrade Check log -Verify that the parameter DB_DOMAIN is set properly
The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST. |
| Check for enough freespace on archive log destination file systems. | Note that if your database is in archivelog mode, then it is always desirable and advisable to upgrade the database in noarchivelog mode as that will reduce the time taken to upgrade the database. After the upgrade you can again put the database in the archivelog mode. |
| Upgrade Database | $ cd $ORACLE_HOME/rdbms/admin $ sqlplus “/ as sysdba” Use startup eith upgrade option SQL> startup UPGRADE |
| Run upgrade script | SQL> set echo on SQL> SPOOL upgrade.log SQL> @catupgrd.sql SQL> spool off |
| Run post upgrade tool, which provides the summary of the upgrade | $ORACLE_HOME/rdbms/admin/utlu112s.sql$ sqlplus “/as sysdba” SQL> STARTUP SQL> @utlu112s.sql SQL> select comp_name, status, version from dba_registry; |
| Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions | SQL> @catuppst.sqlrun utlrp to recompile any invalid objects
SQL> @utlrp.sql |
| Get the diagnostic information of the upgraded database | Download and run dbupgdiag.sqlsql> @dbupgdiag.sql
If it shows any invalid object run utlrp.sql to validate any invalid onjects |
| Post upgrade steps | Create the listener $ netca $ lsnrctl start |
| Upgrade the timezone file | Please refer the article Note 977512.1 to upgrade the tiemzone |
| Upgrade Statistics Tables Created by the DBMS_STATS Package | EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(‘SYS’,'dictstattab’); |
| Upgrade Externally Authenticated SSL Users | ORACLE_HOME/rdbms/bin/extusrupgrade –dbconnectstring <hostname:port_no:sid> –dbuser <db admin> –dbuserpassword <password> -a |
| Edit init.ora | If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE - Migrate your initialization parameter file to a server parameter file.SQL> create spfile from pfile; |
| Upgrade OCR key for the database | use srvconfig srvconfig -upgrade db_name -orahome pre_11g_oracle_homerun srvctl from old oracle_home % $ORACLE_HOME/bin/srvctl remove database -d db_nameFrom 11g Oracle_Home: % $ORACLE_HOME/bin/srvctl add database -d db_name -o <location of 11g home> |
| Shut down the database | sqlplus ‘/ as sysdba’ shutdown |
| Startup database | sqlplus ‘/as sysdba’ Startup |
Data Loader…
Fastest Data Loading
In this paper I am going to explain you about data loading Techniques
- Sql Loader
- Direct Load Insert
- Guidelines for Sql Loader and Direct Load
Sql Loader
Sql Loader loads data from external files into oracle database tables
Direct Load Insert
It can be used to copy data within same oracle database. Bypass the buffer cache and speed up the process by writing data directly into the data files.
Direct Load inserts only supports INSERT … SELECT syntax only. ‘Insert values(…)’ wont support. You could see parallel and Serial Inserts in the Direct Load Insert Method.
Data will be inserted beyond high water mark of the table segment or each partition segment. High water mark is the level in which block have never been written to that area.
Serial Direct Load Insert
Example:
INSERT /*+ APPEND */ INTO emp
NOLOGGING
SELECT * FROM sarith.emp;
COMMIT;
Parallel Direct Load Insert
Each parallel execution server allocates a new temporary segment. When a statement executes, the parallel execution coordinator merges the new temporary segments into the primary table segment.
Example
ALTER SESSION ENABLE PARELLEL DML;
INSERT /*+PARALLEL(sarith.emp,3) */
INTO sarith.emp NOLOGGING
SELECT * FROM hr.emp_bad;
ALTER SESSION command must be executed at the beginning of the transaction
3 Indicates the number of parallel process
SQL Loader
Sql Loader loads data from external files into oracle database Data files
- It can use more than one input files
- Several input can be merged for loading
- Input field can be fixed or Variable
- Data can be loaded from diff types of media like Disk, Tape, named Pipes
- Data can be Loaded into several tables in one Run
- Column value can be auto generated
- Data Can be loaded directly into data file
Example
$sqlldr scott/tiger \
>control = example.ctl \
>log=example.log direct=y
An example for Control File
-- This is a sample control file
LOAD DATA
INFILE 'sample.dat'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'
APPEND
INTO TABLE emp
WHEN (57) = '.'
TRAILING NULLCOLS
(hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(2)
NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,'$99,999.99')",
comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%'
":comm * 100"
)
Infile can be a CSV file or any file that must have a proper format.
Oracle FlashBack Technology
The Oracle Flashback Technology
Oracle Flashback Technology provides some useful ways to get back your lost data without doing whole database or the whole datafile or a large portion of the database restoration from backup database.You do not have to do Point in time recovery. The flashback feature of Oracle 10g is less risk and Efficient of doing table recovery.
Example is from Arjudba Blog
Here is an example for your better understanding,
SQL> select * from t;
A
----------
1
100
10
SQL> insert into t values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
A
----------
1
100
10
2
Flashback Table
SQL> flashback table t to timestamp systimestamp-interval ’1′ minute;
Flashback complete.
SQL> select * from t;
A
----------
1
100
10
Flashback Versions Query
You can also keep track of change data over time suppose when one row is upadted when one row is deleted and when one row is inserted by following query,
select versions_starttime, versions_endtime, versions_xid,versions_operation,a
from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;
SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation,a
from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;
Output is,
VERSIONS_STARTT VERSIONS_ENDTIME VERSIONS_XID V A
--------------- -------------------- ---------------- - ----------
09-APR-08 02.47 09-APR-08 02.47.26 P 0100090035010000 I 5
.16 PM M
09-APR-08 02.47 0700100035010000 D 5
.26 PM
100
10
1
SQL> insert into t values(200);
1 row created.
SQL> commit;
Commit complete.
SQL> select versions_starttime, versions_endtime, versions_xid,versions_operation,a
from t versions between timestamp minvalue and maxvalue order by VERSIONS_STARTTIME;
VERSIONS_STARTT VERSIONS_ENDTIME VERSIONS_XID V A
--------------- -------------------- ---------------- - ----------
09-APR-08 02.47 09-APR-08 02.47.26 P 0100090035010000 I 5
.16 PM M
09-APR-08 02.47 0700100035010000 D 5
.26 PM
09-APR-08 02.49 0700140035010000 I 200
.35 PM
10
1
100
6 rows selected.
You can find out the changes over time by ,
select a, versions_starttime, versions_endtime
from t versions between timestamp
to_date(’7/3/20078 13:33:54′,’mm/dd/yyyy hh24:mi:ss’)
and to_date(’7/3/2008 13:37:55′,’mm/dd/yyyy hh24:mi:ss’)
Flashback Transaction Query
You can check undo_sql by,
SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = ‘….’;
Flashback Query
SQL> select * from flash;
A
———-
1
2
SQL> insert into flash values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from flash as of timestamp systimestamp-interval ’1′ minute;
A
———-
1
2
SQL> select * from flash;
A
———-
1
2
3
usefull Links
