Migration from SOURCEDB1 to TARGETDB1:
--------------------------------------
I'm going to use Datapump for migrating from SOURCEDB1(Oracle 10g) to TARGETDB1(Oracle 11g) Database.
General information:
---------------------
Source:
---------
OS version: Windows Server 2003 R2
Database version:10.2.0.4.0
Database size:33 GB
Target:
---------
OS version:RHEL 5.4
Database version:11.2.0.1.0
SCHEMAS TO MIGRATE:TESTTOOL,TESTTOOLMIG,TESTTOOLQA,TESTD,TESTD_TEST,TEST3CNS,TESTHP,TEST_USR
STEP 1: CREATE USER FOR OUR MIGRATION PURPOSE WITH ENOUGH PRIVILEGES
------
SQL> create user MIGTEST
2 identified by MIGTEST;
User created.
SQL> grant dba to MIGTEST;
SQL> select name from v$database;
NAME
---------
SOURCEDB1
SQL> alter user MIGTEST identified by welcome123;
User altered.
SQL> alter user MIGTEST default tablespace users;
User altered.
Grant succeeded.
Step 2: CREATE DIRECTORY FOR DATAPUMP
-------
Create one directory for Datapump and point it to a directory where we have to
keep our dump files of datapump
Note: This location of keeping dump files should have enough space.
Directory Creation:
-------------------
SQL> CREATE DIRECTORY SOURCEDB1_MIG AS 'D:\ACEDBMIG';
Directory created.
GRANT Permission:
-----------------
SQL> GRANT READ,WRITE ON DIRECTORY SOURCEDB1_MIG TO MIGTEST;
Grant succeeded.
VERIFYING THE LOCATION:
---------------------------
SQL> select *from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
---------------------------------------------------------------------
SYS SOURCEDB1_MIG
D:\SOURCEDB1
STEP 3: EXPORT THE SCHEMAS WHICH WE WANT TO MIGRATE:
------------------
Here I'm create one parameter file for export where I'm specifying
schemas which I want to export.The details and usage of this parameter can
be obtained from command 'expdp help=y' and very much self explanatory.
a) create one parfile(.par):
----------------------------
mig_expdp_SOURCEDB1_12APR_2011.par:
________________________________
SCHEMAS=TESTTOOL,TESTTOOLMIG,TESTTOOLQA,TESTD,TESTD_TEST,TEST3CNS,TESTHP,TEST_USR
DIRECTORY= SOURCEDB1_MIG
DUMPFILE=mig1_expdp_SOURCEDB1_12APR_2011.dmp
LOGFILE=mig1_expdp_SOURCEDB1_12APR_2011.log
EXCLUDE=STATISTICS
CONTENT=ALL
JOB_NAME=mig_expdp_SOURCEDB1_12APR_2011
PARALLEL=2
b)Create one batch file(.bat):
----------------------------------
SOURCEDB1_MIG_12APR2011.bat:
__________________________
expdp MIGTEST/MIGTEST PARFILE=E:\TEST_work\SOURCEDB1 _MIG_UGALAXY\mig_expdp_SOURCEDB1_12APR_2011.par
We can execute the above batch file by double clicking it.Check the dump file location it
will be surely in the path 'D:\ACEDBMIG'(This is the path which you have used for creating
Datapump Directory).Check the log file for any errors or warnings.
STEP 4:COPY THE DUMPFILE FROM SOURCE DATABASE SERVER(WINDOWS) TO TARGET DATABASE SERVER(LINUX)
-------
For copying the dumpfile I used winscp to copy it to my windows machine and than transferred to linux target
server,however you can use ftp or any other technique.
STEP 5:CREATE THE DIRECTORY FOR THE DATAPUMP IMPORT:
------
create directory IMP_MIGRATE_UGALAXY as '/u04/UGALAXY/ACEDB_MIGRAT_DUMPS';
grant read,write on directory IMP_MIGRATE_UGALAXY to system;
STEP 6:IMPORT THE DUMP IN THE TARGET DATABASE:
------
The import scripts are:
-----------------------
vi impdp_job_Apr13_2011.sh
impdp system/manager DIRECTORY=IMP_MIGRATE_UGALAXY REMAP_SCHEMA=TESTTOOL:TESTTOOL,TESTTOOLMIG:TESTTOOLMIG,
TESTTOOLQA:TESTTOOLQA,TESTD:TESTD,TESTD_TEST:TESTD_TEST,TEST3CNS:TEST3CNS,TESTHP:TESTHP,TEST_USR:TEST_USR
DUMPFILE=MIG1_EXPDP_ACEDB_12APR_2011.DMP LOGFILE=MIG1_EXPDP_ACEDB_12APR_2011.log
To run impdp job:
------------------
nohup sh impdp_job_Apr13_2011.sh >a.out
Once the import is completed check the logfiles for errors and warnings.
STEP 7: Validation of Data:
------
Validating the Data:
--------------------
Once the import is done successfully verify the object imported.Compare the Source and Target Databases:
Source Database(Oracle 10g rel2):
----------------
SQL>select count(*) from dba_objects where owner in('TESTTOOL','TESTTOOLMIG','TESTTOOLQA','TESTD','TESTD_TEST','TEST3CNS','TESTHP','TEST_USR');
Target Database(Oracle 11g rel2):
----------------
SQL>select count(*) from dba_objects where owner in('TESTTOOL','TESTTOOLMIG','TESTTOOLQA','TESTD','TESTD_TEST','TEST3CNS','TESTHP','TEST_USR');
Check for the invalid objects in Target Database side:
--------------------------------
SQL> select owner||' '||object_name||' '||status from dba_objects
where STATUS='INVALID';
Compile the invalid object using the below script:
-------------------------------------------------
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
Recheck again and compile:
---------------------------
SQL> select owner||' '||object_name||' '||status from dba_objects
where STATUS='INVALID';
Comparison of privs:
----------------------
Source:
----------
1)Object privs:
------------------
select grantee||' '||owner||' '||table_name||' '||privilege from dba_tab_privs
where owner in('TESTTOOL','TESTTOOLMIG','TESTTOOLQA','TESTD','TESTD_TEST','TEST3CNS','TESTHP','TEST_USR');
2)System privs:
----------------
select grantee||' '||privilege from dba_sys_privs
where grantee in ('TESTTOOL','TESTTOOLMIG','TESTTOOLQA','TESTD','TESTD_TEST','TEST3CNS','TESTHP','TEST_USR');
Target:
------------
1)Object privs:
------------------
select grantee||' '||owner||' '||table_name||' '||privilege from dba_tab_privs
where owner in('TESTTOOL','TESTTOOLMIG','TESTTOOLQA','TESTD','TESTD_TEST','TEST3CNS','TESTHP','TEST_USR');
2)System privs:
----------------
select grantee||' '||privilege from dba_sys_privs
where owner in('('TESTTOOL','TESTTOOLMIG','TESTTOOLQA','TESTD','TESTD_TEST','TEST3CNS','TESTHP','TEST_USR');
Thursday, 18 December 2014
RMAN Backup & Recovery Scripts for Linux System
This is some basic RMAN script to backup the entire database and archived redo logs for a non-RAC environments. In this example we will save the script in a file called “dbbackup_??.scp”. The script contains disk location which can be changed according to your specific environment. The resulting shell script can be run manually from the command line or schedule using CRON. For RMAN place the following command into cmdfile or create a batch file. The batch file must include a catalog=entry if a recovery catalog is used.
There is several RMAN Backup & Recovery script i already uploaded you can search them and update it as per your environment. Below is some basic shell script i found. Hope it will be useful. While creating the RMAN shell script some of the point you must keep in mind. Click on the below link: Point to be considered with RMAN Backup Scripts
Consider you have already configure RMAN controlfile autobackup parameter ON
configure controlfile autobackup format for device type disk to '/u10/catdb/backup/auto_cntrl_%F';
configure controlfile autobackup on;
RMAN Script: Database Full Backup
To take rman offline backup mount the database and run the below script. Do not forget to startup the database again. It is better to include shutdown/startup as a part of the script. For more details please check our other rman offline backup script.
run {
allocate channel c1 type disk;
backup tag weekly_orcl3_full
format '/u07/orcl3/backup/full_%d_%s_%p_%t'
(database);
release channel c1;
allocate channel c2 type disk;
backup format '/u07/orcl3/backup/archive_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Cumulative level 2 backup
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman backup cm_level1 for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 Catalog Catalog/Catalog@rman cmdfile='/u04/catdb/dbbkup_cm1.scp'
echo rman backup cm_level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup incremental level 2 cumulative tag orcl3_cm2
format '/u10/catdb/backup/cm2_%d_%s_%p_%t'
(database);
release channel c1;
#backup up archivelog files
allocate channel c2 type disk;
backup format '/u10/catdb/backup/cm2_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Cumulative level 1 backup
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman backup cm level1 for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/dbbkup_cm1.scp'
echo rman backup cm level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup incremental level 1 cumulative tag orcl3_cm1
format '/u10/catdb/backup/cm1_%d_%s_%p_%t'
(database);
release channel c1;
#backup up archivelog files
allocate channel c2 type disk;
backup format '/u10/catdb/backup/cm1_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Cumulative level 0 backup
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman backup cm_level0 for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/dbbkup_cm0.scp'
echo rman backup cm level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup incremental level 0 cumulative tag orcl3_cm0
format '/u10/catdb/backup/cm0_%d_%s_%p_%t'
(database);
release channel c1;
#backup up archivelog files
allocate channel c2 type disk;
backup
format '/u10/catdb/backup/cm0_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Deleting archivelog when catalog exists.
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/orcl3_archflush.scp'
exit
# RMAN SCRIPT: DELETING ARCHIVE LOGS
run
{
allocate channel c1 type disk;
delete archivelog until time 'SYSDATE-8';
#or RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;
release channel c1;
}
RMAN Script: Deleting the old archives when no catalog exists
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="catdb"
PATH=$PATH:$ORACLE_HOME/bin
rman target sys/oracle@catdb cmdfile='/u04/rman_archflush.scp'
exit
run
{
allocate channel c1 type disk;
delete archivelog until time 'SYSDATE-8';
# OR delete archivelog until sequence=;
release channel c1;
RMAN Script: Backing up all the archivelog files
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman ARCHIVE backup for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/arch_bkup.scp'
echo rman ARCHIVE backup for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup
format '/u10/catdb/backup/arch_%d_%s_%p_%t'
(archivelog all);
release channel c1;
# deleting archive logs older than 8 days
allocate channel c2 type disk;
delete archivelog until time 'SYSDATE-5';
release channel c2;
}
Database Backup Script (OFFLINE / LOGICAL BKUP)
Script1 : Offline backup
This scripts first take the logical export using datapump, then it shutdown the database, Copy the files to tape drive using
TAR(it coppies all oracle db related file like spfile, pwdfile, listener,tnsnames files aswell) , then start the database.
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo orcl3 database export started at `date` >> /u05/orcl3/export/offbkup_orcl3.log (TAKING DATAPUMP EXP)
$ORACLE_HOME/bin/expdp system/oracle dumpfile=datapump_dir:offexpdp-`date '+%Y%m%d'`.dmp logfile=datapump_log:offexpdp-`date '+%Y%m%d'`.log schemas=orafin
echo ORCL3 database export ended at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo ORCL3 database shutdown begin at `date` >> /u05/orcl3/export/offbkup_orcl3.log (SHUTTING DOWN THE DATABASE)
$ORACLE_HOME/bin/sqlplus /nolog @/u06/orcl3/orcl3/scripts/shutdown_orcl3.sql
echo ORCL3 database shutdown at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo TAPE archiving started at `date` >> /u05/orcl3/export/offbkup_orcl3.log (COPYING FILES TO TAPEDRIVE USING TAR)
tar Ecvf /dev/rmt/0 /u05/orcl3/export /u01/app/oracle/product/10.2.0/dbs/initorcl3.ora /u01/app/oracle/product/10.2.0/dbs/spfileorcl3.ora /u01/app/oracle/product/10.2.0/network/admin/listener.ora /u01/app/oracle/oradata/orcl3 /u02/orcl3/oradata /u03/orcl3/oradata /u04/orcl3/oradata /u05/orcl3/oradata /u06/orcl3/orcl3/scripts /u06/orcl3/arch
echo TAPE archiving ended at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo ORCL3 database getting started at `date` >> /u05/orcl3/export/offbkup_orcl3.log(STARTING DATABASE)
$ORACLE_HOME/bin/sqlplus /nolog @/u06/orcl3/orcl3/scripts/startup_orcl3.sql
echo ORCL3 database started at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo ORCL3 offline backup finished at `date` >> /u05/orcl3/export/offbkup_orcl3.log
$ more startup_orcl3.sql
connect /as sysdba;
startup;
exit;
$ more shutdown_orcl3.sql
connect / as sysdba;
shutdown immediate;
exit;
Script2: Datapump export backup.
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo export started at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
$ORACLE_HOME/bin/expdp system/oracle dumpfile=datapump_dir:dailyexpdp-`date '+%Y%m%d'`.dmp logfile=datapump_log:dailyexpdp-`date '+%Y%m%d'`.log schemas=orafin
echo export stopped at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
echo tape archiving started at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
tar Ecvf /dev/rmt/0 /u05/orcl3/export /u06/orcl3/arch
echo tape archiving stopped at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
Script3: Backup all archive files generated during working hrs
$ more morning_arch.sh
echo morning tape archiving started
tar cvf /dev/rmt/0 /u06/orcl3/arch
echo morning tape archiving ended
Scripts to Stop/Start the Enterprise Manager while performing Offline backup
$ more emctl_start.sh
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo STARTING ENTERPRISE MANAGER DATABASE CONSOLE
$ORACLE_HOME/bin/emctl start dbconsole
$ more emctl_stop.sh
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo STOPPING ENTERPRISE MANAGER DATABASE CONSOLE
$ORACLE_HOME/bin/emctl stop dbconsole
RMAN SCRIPT : DISASTER RECOVERY
# The commands below assume that all initialization parameters files are in place and the complete directory structure for the datafiles is recreated and you already set LS_LANG environment variable
setenv NLS_LANG amarican_america.we8dec
# Start RMAN without the target option, and use the following commands to RESTORE and RECOVER the database
# SET DBID 63198018;
# not required if using recovery catalog. You can find DBID from RMAN Controlfile Autobackup.
connect target sys/oracle@orcl3
startup nomount;
run
{
# you need to allocate channels if not using recovery catalog.
allocate channel c1 type disk;
# optionally you can set newname and switch commands to restore datafiles to a new location
restore controlfile from autobackup;
alter database mount;
restore database;
reocver database;
alter database open resetlogs;
Note: You must take a new whole database backup after resetlogs, since backups of previous incarnation are not easily usable.
RMAN Script: POINT IN TIME RECOVERY
# This scenario assumes that all initializaiton filesa and the current controlfile are in place and you want to recover to a point in time '2012-05-22"10:30:00'.
# Ensure you set your NLS_LANG enviroment variable
STARTUP MOUNT FORCE;
RUN
{
SET UNTIL TIME "TO_DATE('2012-05-22"10:30:00','yyyy-dd-mm:hh24:mi:ss')";
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
Note: You must take a new whole database backup after resetlogs,since backups of previous incarnation are not easily usable
RMAN SCRIPT : CONTROLFILE RECOVERY
# Oracle strongly recommends that you specify multiple controlfiles, on separate physical disks and controllers, in the CONTROL_FILE initialization parameter.
# - If one copy is lost due to media failure, copy one of the others over the lost controlfile and restart the instance.
# - If you lose all copies of the controlfile, you must re-create it using the create controlfile sql command
# You should use RMAN to recover a backup controlfile only if you have lost all copies of the current controlfile, because after restoring a backup controlfile, you will have to open RESETLOGS and take a new whole database backup.
# This section assumes that all copies of the current controlfile have been lost, and than all initialization parameter files, datafiles and online logs are intact.
# Ensure you set your NLS_LANG environment variable e.g. in unix (csh):
# >setenv NLS_LANG american_america.we8dec
# Start RMAN without the TARGET option, and use the following commands to restore and recover the database;
# SET DBID 63198018;
connect target sys/oracle@orcl3
startup nomount;
run
{
# you need to allocate channels if not using recovery catalog.
allocate channel c1 type disk;
restore controlfile from autobackup # or directly provide the controlfile backup location
alter database mount;
recover database;
alter database open resetlogs;
}
# you must take a new whole database backup after reerlogs, since backups of previous incarnation are not easily usable
RMAN Script: DATAFILE RECOVERY
# This section assumes that datafile 5 has been damaged and needs to be restored and recovered, and that the current controlfile and all other datafiles are intact. The database is mounted during the restore and recovery.
# - offlie the datafile that needs recovery
# - restore the datafile from backups
# - apply incrementals and archivelogs as necessary to recover.
# - make online recovered datafile
run
{
sql 'alter database datafile 5 offline';
#if you want to restore to a different location,uncomment the following command
# Set newname for datafile 5 to '/newdirectory/new_filename.dbf';
restore datafile 5;
# if you restored to a different locatin, uncomment the command below to switch the controlfile to point to the file in the new location
# SWITCH DATAFILE ALL;
recover datafile 5;
sql 'alter database datafile 5 online';
}
There is several RMAN Backup & Recovery script i already uploaded you can search them and update it as per your environment. Below is some basic shell script i found. Hope it will be useful. While creating the RMAN shell script some of the point you must keep in mind. Click on the below link: Point to be considered with RMAN Backup Scripts
Consider you have already configure RMAN controlfile autobackup parameter ON
configure controlfile autobackup format for device type disk to '/u10/catdb/backup/auto_cntrl_%F';
configure controlfile autobackup on;
RMAN Script: Database Full Backup
To take rman offline backup mount the database and run the below script. Do not forget to startup the database again. It is better to include shutdown/startup as a part of the script. For more details please check our other rman offline backup script.
run {
allocate channel c1 type disk;
backup tag weekly_orcl3_full
format '/u07/orcl3/backup/full_%d_%s_%p_%t'
(database);
release channel c1;
allocate channel c2 type disk;
backup format '/u07/orcl3/backup/archive_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Cumulative level 2 backup
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman backup cm_level1 for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 Catalog Catalog/Catalog@rman cmdfile='/u04/catdb/dbbkup_cm1.scp'
echo rman backup cm_level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup incremental level 2 cumulative tag orcl3_cm2
format '/u10/catdb/backup/cm2_%d_%s_%p_%t'
(database);
release channel c1;
#backup up archivelog files
allocate channel c2 type disk;
backup format '/u10/catdb/backup/cm2_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Cumulative level 1 backup
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman backup cm level1 for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/dbbkup_cm1.scp'
echo rman backup cm level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup incremental level 1 cumulative tag orcl3_cm1
format '/u10/catdb/backup/cm1_%d_%s_%p_%t'
(database);
release channel c1;
#backup up archivelog files
allocate channel c2 type disk;
backup format '/u10/catdb/backup/cm1_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Cumulative level 0 backup
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman backup cm_level0 for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/dbbkup_cm0.scp'
echo rman backup cm level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup incremental level 0 cumulative tag orcl3_cm0
format '/u10/catdb/backup/cm0_%d_%s_%p_%t'
(database);
release channel c1;
#backup up archivelog files
allocate channel c2 type disk;
backup
format '/u10/catdb/backup/cm0_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Deleting archivelog when catalog exists.
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/orcl3_archflush.scp'
exit
# RMAN SCRIPT: DELETING ARCHIVE LOGS
run
{
allocate channel c1 type disk;
delete archivelog until time 'SYSDATE-8';
#or RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;
release channel c1;
}
RMAN Script: Deleting the old archives when no catalog exists
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="catdb"
PATH=$PATH:$ORACLE_HOME/bin
rman target sys/oracle@catdb cmdfile='/u04/rman_archflush.scp'
exit
run
{
allocate channel c1 type disk;
delete archivelog until time 'SYSDATE-8';
# OR delete archivelog until sequence=;
release channel c1;
RMAN Script: Backing up all the archivelog files
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman ARCHIVE backup for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/arch_bkup.scp'
echo rman ARCHIVE backup for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup
format '/u10/catdb/backup/arch_%d_%s_%p_%t'
(archivelog all);
release channel c1;
# deleting archive logs older than 8 days
allocate channel c2 type disk;
delete archivelog until time 'SYSDATE-5';
release channel c2;
}
Database Backup Script (OFFLINE / LOGICAL BKUP)
Script1 : Offline backup
This scripts first take the logical export using datapump, then it shutdown the database, Copy the files to tape drive using
TAR(it coppies all oracle db related file like spfile, pwdfile, listener,tnsnames files aswell) , then start the database.
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo orcl3 database export started at `date` >> /u05/orcl3/export/offbkup_orcl3.log (TAKING DATAPUMP EXP)
$ORACLE_HOME/bin/expdp system/oracle dumpfile=datapump_dir:offexpdp-`date '+%Y%m%d'`.dmp logfile=datapump_log:offexpdp-`date '+%Y%m%d'`.log schemas=orafin
echo ORCL3 database export ended at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo ORCL3 database shutdown begin at `date` >> /u05/orcl3/export/offbkup_orcl3.log (SHUTTING DOWN THE DATABASE)
$ORACLE_HOME/bin/sqlplus /nolog @/u06/orcl3/orcl3/scripts/shutdown_orcl3.sql
echo ORCL3 database shutdown at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo TAPE archiving started at `date` >> /u05/orcl3/export/offbkup_orcl3.log (COPYING FILES TO TAPEDRIVE USING TAR)
tar Ecvf /dev/rmt/0 /u05/orcl3/export /u01/app/oracle/product/10.2.0/dbs/initorcl3.ora /u01/app/oracle/product/10.2.0/dbs/spfileorcl3.ora /u01/app/oracle/product/10.2.0/network/admin/listener.ora /u01/app/oracle/oradata/orcl3 /u02/orcl3/oradata /u03/orcl3/oradata /u04/orcl3/oradata /u05/orcl3/oradata /u06/orcl3/orcl3/scripts /u06/orcl3/arch
echo TAPE archiving ended at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo ORCL3 database getting started at `date` >> /u05/orcl3/export/offbkup_orcl3.log(STARTING DATABASE)
$ORACLE_HOME/bin/sqlplus /nolog @/u06/orcl3/orcl3/scripts/startup_orcl3.sql
echo ORCL3 database started at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo ORCL3 offline backup finished at `date` >> /u05/orcl3/export/offbkup_orcl3.log
$ more startup_orcl3.sql
connect /as sysdba;
startup;
exit;
$ more shutdown_orcl3.sql
connect / as sysdba;
shutdown immediate;
exit;
Script2: Datapump export backup.
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo export started at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
$ORACLE_HOME/bin/expdp system/oracle dumpfile=datapump_dir:dailyexpdp-`date '+%Y%m%d'`.dmp logfile=datapump_log:dailyexpdp-`date '+%Y%m%d'`.log schemas=orafin
echo export stopped at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
echo tape archiving started at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
tar Ecvf /dev/rmt/0 /u05/orcl3/export /u06/orcl3/arch
echo tape archiving stopped at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
Script3: Backup all archive files generated during working hrs
$ more morning_arch.sh
echo morning tape archiving started
tar cvf /dev/rmt/0 /u06/orcl3/arch
echo morning tape archiving ended
Scripts to Stop/Start the Enterprise Manager while performing Offline backup
$ more emctl_start.sh
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo STARTING ENTERPRISE MANAGER DATABASE CONSOLE
$ORACLE_HOME/bin/emctl start dbconsole
$ more emctl_stop.sh
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo STOPPING ENTERPRISE MANAGER DATABASE CONSOLE
$ORACLE_HOME/bin/emctl stop dbconsole
RMAN SCRIPT : DISASTER RECOVERY
# The commands below assume that all initialization parameters files are in place and the complete directory structure for the datafiles is recreated and you already set LS_LANG environment variable
setenv NLS_LANG amarican_america.we8dec
# Start RMAN without the target option, and use the following commands to RESTORE and RECOVER the database
# SET DBID 63198018;
# not required if using recovery catalog. You can find DBID from RMAN Controlfile Autobackup.
connect target sys/oracle@orcl3
startup nomount;
run
{
# you need to allocate channels if not using recovery catalog.
allocate channel c1 type disk;
# optionally you can set newname and switch commands to restore datafiles to a new location
restore controlfile from autobackup;
alter database mount;
restore database;
reocver database;
alter database open resetlogs;
Note: You must take a new whole database backup after resetlogs, since backups of previous incarnation are not easily usable.
RMAN Script: POINT IN TIME RECOVERY
# This scenario assumes that all initializaiton filesa and the current controlfile are in place and you want to recover to a point in time '2012-05-22"10:30:00'.
# Ensure you set your NLS_LANG enviroment variable
STARTUP MOUNT FORCE;
RUN
{
SET UNTIL TIME "TO_DATE('2012-05-22"10:30:00','yyyy-dd-mm:hh24:mi:ss')";
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
Note: You must take a new whole database backup after resetlogs,since backups of previous incarnation are not easily usable
RMAN SCRIPT : CONTROLFILE RECOVERY
# Oracle strongly recommends that you specify multiple controlfiles, on separate physical disks and controllers, in the CONTROL_FILE initialization parameter.
# - If one copy is lost due to media failure, copy one of the others over the lost controlfile and restart the instance.
# - If you lose all copies of the controlfile, you must re-create it using the create controlfile sql command
# You should use RMAN to recover a backup controlfile only if you have lost all copies of the current controlfile, because after restoring a backup controlfile, you will have to open RESETLOGS and take a new whole database backup.
# This section assumes that all copies of the current controlfile have been lost, and than all initialization parameter files, datafiles and online logs are intact.
# Ensure you set your NLS_LANG environment variable e.g. in unix (csh):
# >setenv NLS_LANG american_america.we8dec
# Start RMAN without the TARGET option, and use the following commands to restore and recover the database;
# SET DBID 63198018;
connect target sys/oracle@orcl3
startup nomount;
run
{
# you need to allocate channels if not using recovery catalog.
allocate channel c1 type disk;
restore controlfile from autobackup # or directly provide the controlfile backup location
alter database mount;
recover database;
alter database open resetlogs;
}
# you must take a new whole database backup after reerlogs, since backups of previous incarnation are not easily usable
RMAN Script: DATAFILE RECOVERY
# This section assumes that datafile 5 has been damaged and needs to be restored and recovered, and that the current controlfile and all other datafiles are intact. The database is mounted during the restore and recovery.
# - offlie the datafile that needs recovery
# - restore the datafile from backups
# - apply incrementals and archivelogs as necessary to recover.
# - make online recovered datafile
run
{
sql 'alter database datafile 5 offline';
#if you want to restore to a different location,uncomment the following command
# Set newname for datafile 5 to '/newdirectory/new_filename.dbf';
restore datafile 5;
# if you restored to a different locatin, uncomment the command below to switch the controlfile to point to the file in the new location
# SWITCH DATAFILE ALL;
recover datafile 5;
sql 'alter database datafile 5 online';
}
Wednesday, 15 October 2014
To find Computer Model or Laptop Model using Command Prompt
To get Computer or laptop model, manufaturer using Command Prompt use the following commands.
***************************SAUDI APPS DBA**************************************
wmic csproduct get vendor, version
wmic computersystem get model,name,manufacturer,systemtype
***************************SAUDI APPS DBA**************************************
Tuesday, 14 October 2014
Download files using Command prompt in Windows
Use the below Command to download files using Command prompt
Command
C:\>powershell -command "& { iwr <WEBSITE LINK> -OutFile <File_Name> }"
Example
C:\>powershell -command "& { iwr http://th00.deviantart.net/fs70/PRE/f/2013/074/
a/0/kali_linux_wallpaper_by_humanlly-d5y4g08.png -OutFile kali.png }"
******************SAUDI APPS DBA******************
Monday, 13 October 2014
Saudi Apps DBA is now Available for Android
Saudi Apps DBA is now Available for Android
Scan QR Code to Download
  |
Or
Click the Below link
***********Saudi Apps DBA***************
How to Create a WiFi Hotspot Using the Command Prompt
How to Create a WiFi Hotspot Using the Command Prompt
Now-a-days internet connectivity has become one of the things that we need the most. It would be nice if we can use our computer's fast internet Connection. So guys the solution is Wireless Hosted Network. This concept of Wireless Hosted network was introduced in Windows 7. By using this feature we can easily create a virtual wireless adapter.To do this we only require a CMD(Command Prompt) with Administrator privileges. And best of all you can connect to another WiFi when Hosted Network is running. This process describes some of the commands that are available only in Windows 7 or Windows 8 Ready PC.
1
Start. Click on start button on your desktop or press the Windows Key on keyboard.- 2Search. Type "cmd" in search box and right click on command prompt. Select Run as Administrator.
- 3For Beginners. You are accessing the Admin privileges so you will be asked for User Account Control. Click on Yes. Now you have command prompt running.
- 4Device Check. Type
netsh wlan show driversin command prompt and press enter.
It will show the output like the image.
In the outputHosted network supported :Yesmeans that your computer supports hosted networks. Verify this. - 5Create. Type
netsh wlan set hostednetwork mode=allow ssid=Hotspotname key=passwordin Command prompt. This will create a Hotspot but it is currently offline. - 6Start. Type
netsh wlan start hostednetworkin command prompt to start the hotspot you just created. - 7Stop. Type
netsh wlan stop hostednetworkin command prompt to stop the hotspot. - 8Details. Type
netsh wlan show hostednetworkto check the hotspot status. - 9Internet. To share your internet connection on this hotspot go to the Network and sharing center and click on Change adapter setting. Here right click on the Connection that you use to connect to internet and select properties. In properties go to Sharing tab and check the box "Allow other network users to connect through this computer's internet connection and select the network connection name that is used by your hotspot.(For this check the Network Connection Window and look for Connection which says Microsoft Hosted Network Virtual Adapter). And save it. Now your inter net connection is shared.
- ************************************SAUDI APPS DBA****************************************
Subscribe to:
Posts (Atom)