Tuesday, May 10, 2011
Switching Undo Tablespace
CREATE UNDO TABLESPACE "UNDOTBS"
DATAFILE '/u05/omr/oradata/undotbs.dbf' SIZE 4000M;
SQL> select name from v$tablespace where name like 'UNDO%';
NAME
--------
UNDOTBS1
UNDOTBS
SQL> ALTER SYSTEM SET undo_tablespace='UNDOTBS' SCOPE=BOTH;
SQL> show parameter undo
NAME TYPE VALUE
undo_management string AUTO
undo_retention integer 21600
undo_tablespace string UNDOTBS
SQL>select status from V$ROLLSTAT
STATUS
ONLINE
ONLINE
PENDING OFFLINE
PENDING OFFLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
If the status is pending offline; u cannot drop undo tablespace UNDOTBS1
SQL>drop tablespace undotbs1 including contents and datafiles
If u drop u will get ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
Note: You can find the following messages in alert.log after issuing alter system set command
Sat Jul 18 15:38:38 2009
Successfully onlined Undo Tablespace 7.
Undo Tablespace 1 moved to Pending Switch-Out state.
*** active transactions found in undo tablespace 1 during switch-out.
Sat Jul 18 15:46:28 2009
Undo Tablespace 1 successfully switched out.
DATAFILE '/u05/omr/oradata/undotbs.dbf' SIZE 4000M;
SQL> select name from v$tablespace where name like 'UNDO%';
NAME
--------
UNDOTBS1
UNDOTBS
SQL> ALTER SYSTEM SET undo_tablespace='UNDOTBS' SCOPE=BOTH;
SQL> show parameter undo
NAME TYPE VALUE
undo_management string AUTO
undo_retention integer 21600
undo_tablespace string UNDOTBS
SQL>select status from V$ROLLSTAT
STATUS
ONLINE
ONLINE
PENDING OFFLINE
PENDING OFFLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
If the status is pending offline; u cannot drop undo tablespace UNDOTBS1
SQL>drop tablespace undotbs1 including contents and datafiles
If u drop u will get ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
Note: You can find the following messages in alert.log after issuing alter system set command
Sat Jul 18 15:38:38 2009
Successfully onlined Undo Tablespace 7.
Undo Tablespace 1 moved to Pending Switch-Out state.
*** active transactions found in undo tablespace 1 during switch-out.
Sat Jul 18 15:46:28 2009
Undo Tablespace 1 successfully switched out.
Monday, May 9, 2011
Creating Catalog Database on Oracle10g
Step 1: Create o/s User:
Useradd –u 204 –g 2001 –G 1001 –c “Catdb Catalog User” –m –d /u04/catdb –s /bin/ksh catdb
Passwd : Enter password for catdb user
Group 2001: dba
Group 1001 : oinstall
Step 2: Creating Database
Use DBCA to create Database
Step 3: Create additional tablespaces:
create tablespace catdbtbs datafile '/u03/catdb/oradata/catdbtbs01.dbf' size 1000M reuse extent management local;
Step 4:
Take Backup of the database (if required)
Step 5:
Configure Listener.ora / tnsnames.ora
Step 6: Create RMAN user
Sql> Create user RMAN identified by RMAN
Default tablespace cattbs
Temporary tablespace temp;
Sql> Grant connect, resource, recovery_catalog_owner to RMAN;
Step 7: create catalog
$ rman
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Sep 8 11:16:57 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect catalog rman/rman@catdb
connected to recovery catalog database
RMAN> create catalog tablespace cattbs;
recovery catalog created
RMAN> exit
Recovery Manager complete.
Step 8: Registering database
$ rman target sys/omr1956@omr catalog rman/rman@catdb
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Sep 8 11:19:26 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: OMR (DBID=1312345947)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u10/catdb/backup/auto_cntrl_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/dbs/snapcf_omr.f'; # default
RMAN>
Useradd –u 204 –g 2001 –G 1001 –c “Catdb Catalog User” –m –d /u04/catdb –s /bin/ksh catdb
Passwd : Enter password for catdb user
Group 2001: dba
Group 1001 : oinstall
Step 2: Creating Database
Use DBCA to create Database
Step 3: Create additional tablespaces:
create tablespace catdbtbs datafile '/u03/catdb/oradata/catdbtbs01.dbf' size 1000M reuse extent management local;
Step 4:
Take Backup of the database (if required)
Step 5:
Configure Listener.ora / tnsnames.ora
Step 6: Create RMAN user
Sql> Create user RMAN identified by RMAN
Default tablespace cattbs
Temporary tablespace temp;
Sql> Grant connect, resource, recovery_catalog_owner to RMAN;
Step 7: create catalog
$ rman
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Sep 8 11:16:57 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect catalog rman/rman@catdb
connected to recovery catalog database
RMAN> create catalog tablespace cattbs;
recovery catalog created
RMAN> exit
Recovery Manager complete.
Step 8: Registering database
$ rman target sys/omr1956@omr catalog rman/rman@catdb
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Sep 8 11:19:26 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: OMR (DBID=1312345947)
connected to recovery catalog database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u10/catdb/backup/auto_cntrl_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/dbs/snapcf_omr.f'; # default
RMAN>
My 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.
$ more offbkup_omr.sh
$ more startup_omr.sql
connect /as sysdba;
startup;
exit;
$ more shutdown_omr.sql
connect / as sysdba;
shutdown immediate;
exit;
===========================================================
Script2: Datapump export backup.
===========================================================
Script3: backing up the archive files generated during working hrs
$ more morning_arch.sh
echo morning tape archiving started
tar cvf /dev/rmt/0 /u06/omr/arch
echo morning tape archiving ended
===========================================================
Following scripts were used 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="omr"
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="omr"
echo STOPPING ENTERPRISE MANAGER DATABASE CONSOLE
$ORACLE_HOME/bin/emctl stop dbconsole
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.
$ more offbkup_omr.sh
| #!/bin/ksh export ORACLE_HOME="/u01/app/oracle/product/10.2.0" export ORACLE_SID="omr" echo omr database export started at `date` >> /u05/omr/export/offbkup_omr.log (TAKING DATAPUMP EXP) $ORACLE_HOME/bin/expdp system/password dumpfile=datapump_dir:offexpdp-`date '+%Y%m%d'`.dmp logfile=datapump_log:offexpdp-`date '+%Y%m%d'`.log schemas=ae001t3 echo OMR database export ended at `date` >> /u05/omr/export/offbkup_omr.log echo OMR database shutdown begin at `date` >> /u05/omr/export/offbkup_omr.log(SHUTING DOWN DB B4 OFFLINE BKUP) $ORACLE_HOME/bin/sqlplus /nolog @/u06/omr/omr/scripts/shutdown_omr.sql echo OMR database shutdown at `date` >> /u05/omr/export/offbkup_omr.log echo TAPE archiving started at `date` >> /u05/omr/export/offbkup_omr.log (COPYING FILES TO TAPEDRIVE USING TAR) tar Ecvf /dev/rmt/0 /u05/omr/export /u01/app/oracle/product/10.2.0/dbs/initomr.ora /u01/app/oracle/product/10.2.0/dbs/spfileomr.ora /u01/app/oracle/product/10.2.0/network/admin/listener.ora /u01/app/oracle/oradata/omr /u02/omr/oradata /u03/omr/oradata /u04/omr/oradata /u05/omr/oradata /u06/omr/omr/scripts /u06/omr/arch echo TAPE archiving ended at `date` >> /u05/omr/export/offbkup_omr.log echo OMR database getting started at `date` >> /u05/omr/export/offbkup_omr.log(STARTING DB) $ORACLE_HOME/bin/sqlplus /nolog @/u06/omr/omr/scripts/startup_omr.sql echo OMR database started at `date` >> /u05/omr/export/offbkup_omr.log echo OMR offline backup finished at `date` >> /u05/omr/export/offbkup_omr.log |
$ more startup_omr.sql
connect /as sysdba;
startup;
exit;
$ more shutdown_omr.sql
connect / as sysdba;
shutdown immediate;
exit;
===========================================================
Script2: Datapump export backup.
$ more dailyexpdp_omr.sh
#!/bin/ksh export ORACLE_HOME="/u01/app/oracle/product/10.2.0" export ORACLE_SID="omr" echo export started at `date` >> /u05/omr/export/dailyexpdp_omr.log $ORACLE_HOME/bin/expdp system/omr1956 dumpfile=datapump_dir:dailyexpdp-`date '+%Y%m%d'`.dmp logfile=datapump_log:dailyexpdp-`date '+%Y%m%d'`.log schemas=ae001t3 echo export stopped at `date` >> /u05/omr/export/dailyexpdp_omr.log echo tape archiving started at `date` >> /u05/omr/export/dailyexpdp_omr.log tar Ecvf /dev/rmt/0 /u05/omr/export /u06/omr/arch echo tape archiving stopped at `date` >> /u05/omr/export/dailyexpdp_omr.log |
Script3: backing up the archive files generated during working hrs
$ more morning_arch.sh
echo morning tape archiving started
tar cvf /dev/rmt/0 /u06/omr/arch
echo morning tape archiving ended
===========================================================
Following scripts were used 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="omr"
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="omr"
echo STOPPING ENTERPRISE MANAGER DATABASE CONSOLE
$ORACLE_HOME/bin/emctl stop dbconsole
Monday, May 2, 2011
Time Zone issue while starting management Agent:
Agents fails to start or
Error: tzoffset for aisa/dubai is (0), but agent is rinning with tzoffset 120(min)
Cause: ORA_TZFILE may be set to different ORACLE_HOME
So unset this file
1. echo $ORA_TZFILE
2. unset OTA_TZFILE
3. start the agent
Error: tzoffset for aisa/dubai is (0), but agent is rinning with tzoffset 120(min)
Cause: ORA_TZFILE may be set to different ORACLE_HOME
So unset this file
1. echo $ORA_TZFILE
2. unset OTA_TZFILE
3. start the agent
Multiplexing Control File
Multiplexing the Control File When Using SPFILE
Sql> shutdown immediate
$ cp /u00/app/oracle/oradata/ota/control01.ctl /u04/ota/control04.ctl
Sql> Startup nomount
Sql> ALTER SYSTEM SET control_files =
‘/u00/app/oracle/oradata/ota/control01.ctl’,
‘/u00/app/oracle/oradata/ota/control02.ctl’,
‘/u00/app/oracle/oradata/ota/control03.ctl’,
‘/u04/ota/control04.ctl’
SCOPE=SPFILE;
Sql> shutdown immediate
Sql> startup
Sql> select name from v$controlfile;
Sql> create PFILE from SPFILE;
Multiplexing the Control File When Using PFILE
Sql> sqlplus /nolog
Sql> Connect /as sysdba
Sql> shutdown immediate
$ cp /u00/app/oracle/oradata/ota/control01.ctl /u0X/ota/control04.ctl
Add this entry in PFILE
control_files = ‘/u00/app/oracle/oradata/ota/control01.ctl’,
‘/u00/app/oracle/oradata/ota/control02.ctl’,
‘/u00/app/oracle/oradata/ota/control03.ctl’,
‘/u0X/ota/control04.ctl’
SQL> startup
Sql> shutdown immediate
$ cp /u00/app/oracle/oradata/ota/control01.ctl /u04/ota/control04.ctl
Sql> Startup nomount
Sql> ALTER SYSTEM SET control_files =
‘/u00/app/oracle/oradata/ota/control01.ctl’,
‘/u00/app/oracle/oradata/ota/control02.ctl’,
‘/u00/app/oracle/oradata/ota/control03.ctl’,
‘/u04/ota/control04.ctl’
SCOPE=SPFILE;
Sql> shutdown immediate
Sql> startup
Sql> select name from v$controlfile;
Sql> create PFILE from SPFILE;
Multiplexing the Control File When Using PFILE
Sql> sqlplus /nolog
Sql> Connect /as sysdba
Sql> shutdown immediate
$ cp /u00/app/oracle/oradata/ota/control01.ctl /u0X/ota/control04.ctl
Add this entry in PFILE
control_files = ‘/u00/app/oracle/oradata/ota/control01.ctl’,
‘/u00/app/oracle/oradata/ota/control02.ctl’,
‘/u00/app/oracle/oradata/ota/control03.ctl’,
‘/u0X/ota/control04.ctl’
SQL> startup
Install Oracle Grid Control
Download Software
Download the Grid Control software from technet.oracle.com to the /oracle/media/oem directory. You will need the following files downloaded
Linux_Grid_Control_full_102010_disk1.zip
Linux_Grid_Control_full_102010_disk2.zip
Linux_Grid_Control_full_102010_disk3.zip
GridControl_10.2.0.4_LINUX.zip
Download patch p7040389_10204_GENERIC.zip from metalink.oracle.com
Install Grid Control 10.2.0.1 (Software Only)
login as oracle
cd /oracle/media/oem
unzip Linux_Grid_Control_full_102010_disk1.zip
unzip Linux_Grid_Control_full_102010_disk2.zip
unzip Linux_Grid_Control_full_102010_disk3.zip
unzip GridControl_10.2.0.4_LINUX.zip
Edit the response file
cd /oracle/media/oem/response
vi em_with_new_db.rsp
And change the following settings
FROM_LOCATION=/oracle/media/oem/rdbms/Disk1/stage/products.xml
BASEDIR=/oracle/product/10.2.0
INSTALLATION_NAME=OEM1
s_gdbName=emrep.gridcontrol.example.com
s_mountPoint=/oracle/oradata
s_operGroup=dba
s_adminGroup=dba
s_securePassword=
s_securePasswordConfirm=
b_lockedSelected=true
b_passwordsDifferent=false
b_passwordsSame=true
s_reposPwd=
s_reposPwdConfirm=
Run Oracle Installer in Silent Mode
cd /oracle/media/oem/install
./runInstaller -noconfig -silent -responseFile /oracle/media/oem/response/em_with_new_db.rsp -force
After the installer is finished Run the following scripts as root
/home/oracle/oraInventory/orainstRoot.sh
/oracle/product/10.2.0/db10g/allroot.sh
Apply the 10.2.0.4 patch set to OMS
As Oracle
/oracle/product/10.2.0/oms10g/opmn/bin/opmnctl stopall
cd /oracle/media/oem
unzip p3731593_10204_LINUX.zip
cd /oracle/media/oem/3731593/Disk1/response
vi patchset.rsp
And change the following settings
FROM_LOCATION=”/oracle/media/oem/3731593/Disk1/stage/products.xml”
b_softwareonly=true
s_sysPassword=
ORACLE_HOME=/oracle/product/10.2.0/oms10g
Save the patchset.rsp file and execute the installer.
cd /oracle/media/oem/3731593/Disk1/install
./runInstaller -noconfig -silent -responseFile /oracle/media/oem/3731593/Disk1/response/patchset.rsp -force
Apply the 10.2.0.4 patch set to Agent
cd /oracle/media/oem/3731593/Disk1/response
vi patchset.rsp
And change the following settings
ORACLE_HOME=/oracle/product/10.2.0/agent10g
Save the response file and execute the installer
cd /oracle/media/oem/3731593/Disk1/install
./runInstaller -noconfig -silent -responseFile /oracle/media/oem/3731593/Disk1/ response/patchset.rsp -force
Apply the interm RDBMS patch# 4329444 to the database
cd /oracle/media/oem
unzip p4329444_10104_LINUX.zip
cd 4329444
export ORACLE_HOME=/oracle/product/10.2.0/db10g
$ORACLE_HOME/OPatch/opatch apply
Apply patch# 7040389 to the Oracle home directory of the OMS
cd /oracle/media/oem
unzip p7040389_10204_GENERIC.zip
cd 7040389/
export ORACLE_HOME=/oracle/product/10.2.0/oms10g
$ORACLE_HOME/OPatch/opatch apply
Configure Enterprise Manager Grid Control
Login as oracle
export DISPLAY=
export PERL5LIB=/oracle/product/10.2.0/oms10g/perl/lib/5.6.1
export ORACLE_HOME=/oracle/product/10.2.0/oms10g
$ORACLE_HOME/perl/bin/perl /oracle/product/10.2.0/oms10g/sysman/install/ConfigureGC.pl /oracle/product/10.2.0
Be patient, the above script can take upto an hour :-)
Download the Grid Control software from technet.oracle.com to the /oracle/media/oem directory. You will need the following files downloaded
Linux_Grid_Control_full_102010_disk1.zip
Linux_Grid_Control_full_102010_disk2.zip
Linux_Grid_Control_full_102010_disk3.zip
GridControl_10.2.0.4_LINUX.zip
Download patch p7040389_10204_GENERIC.zip from metalink.oracle.com
Install Grid Control 10.2.0.1 (Software Only)
login as oracle
cd /oracle/media/oem
unzip Linux_Grid_Control_full_102010_disk1.zip
unzip Linux_Grid_Control_full_102010_disk2.zip
unzip Linux_Grid_Control_full_102010_disk3.zip
unzip GridControl_10.2.0.4_LINUX.zip
Edit the response file
cd /oracle/media/oem/response
vi em_with_new_db.rsp
And change the following settings
FROM_LOCATION=/oracle/media/oem/rdbms/Disk1/stage/products.xml
BASEDIR=/oracle/product/10.2.0
INSTALLATION_NAME=OEM1
s_gdbName=emrep.gridcontrol.example.com
s_mountPoint=/oracle/oradata
s_operGroup=dba
s_adminGroup=dba
s_securePassword=
s_securePasswordConfirm=
b_lockedSelected=true
b_passwordsDifferent=false
b_passwordsSame=true
s_reposPwd=
s_reposPwdConfirm=
Run Oracle Installer in Silent Mode
cd /oracle/media/oem/install
./runInstaller -noconfig -silent -responseFile /oracle/media/oem/response/em_with_new_db.rsp -force
After the installer is finished Run the following scripts as root
/home/oracle/oraInventory/orainstRoot.sh
/oracle/product/10.2.0/db10g/allroot.sh
Apply the 10.2.0.4 patch set to OMS
As Oracle
/oracle/product/10.2.0/oms10g/opmn/bin/opmnctl stopall
cd /oracle/media/oem
unzip p3731593_10204_LINUX.zip
cd /oracle/media/oem/3731593/Disk1/response
vi patchset.rsp
And change the following settings
FROM_LOCATION=”/oracle/media/oem/3731593/Disk1/stage/products.xml”
b_softwareonly=true
s_sysPassword=
ORACLE_HOME=/oracle/product/10.2.0/oms10g
Save the patchset.rsp file and execute the installer.
cd /oracle/media/oem/3731593/Disk1/install
./runInstaller -noconfig -silent -responseFile /oracle/media/oem/3731593/Disk1/response/patchset.rsp -force
Apply the 10.2.0.4 patch set to Agent
cd /oracle/media/oem/3731593/Disk1/response
vi patchset.rsp
And change the following settings
ORACLE_HOME=/oracle/product/10.2.0/agent10g
Save the response file and execute the installer
cd /oracle/media/oem/3731593/Disk1/install
./runInstaller -noconfig -silent -responseFile /oracle/media/oem/3731593/Disk1/ response/patchset.rsp -force
Apply the interm RDBMS patch# 4329444 to the database
cd /oracle/media/oem
unzip p4329444_10104_LINUX.zip
cd 4329444
export ORACLE_HOME=/oracle/product/10.2.0/db10g
$ORACLE_HOME/OPatch/opatch apply
Apply patch# 7040389 to the Oracle home directory of the OMS
cd /oracle/media/oem
unzip p7040389_10204_GENERIC.zip
cd 7040389/
export ORACLE_HOME=/oracle/product/10.2.0/oms10g
$ORACLE_HOME/OPatch/opatch apply
Configure Enterprise Manager Grid Control
Login as oracle
export DISPLAY=
export PERL5LIB=/oracle/product/10.2.0/oms10g/perl/lib/5.6.1
export ORACLE_HOME=/oracle/product/10.2.0/oms10g
$ORACLE_HOME/perl/bin/perl /oracle/product/10.2.0/oms10g/sysman/install/ConfigureGC.pl /oracle/product/10.2.0
Be patient, the above script can take upto an hour :-)
Enable Flashback
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1300928 bytes
Variable Size 157820480 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320;
System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=536870912;
System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST='/u02/fra';
System altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1300928 bytes
Variable Size 157820480 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320;
System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=536870912;
System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST='/u02/fra';
System altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
Dropping oracle10g Database and Removing software
Dropping oracle10g Database
Deleing two databases (PROD/RMAN)
1. Deleting Target database using RMAN (PROD using RMAN)
Login as PROD user:
$ sqlplus /nolog
Sql> connect sys/pwd@connect_string as sysdba
Sql> startup mount exclusive
Sql> alter system enable restricted session
Sql> exit
Login as rman user
$ rman target sys/pwd@connect_string catalog rman/pwd@rman
RMAN> drop database including backup;
2. Deleting RMAN Database:
i. Connect to drop catalog
$ connect catalog rman/pwd@rman
RMAN> drop catalog;
RMAN> exit
ii. Check Datafile, Controlfile and Redo logs;
SQL> select name from v$datafile/ v$logfile / v$controlfile;
iii. Drop Database
SQL> startup mount exclusive restrict;
SQL> drop database;
3. Removing Oracle10g Software:
Login as software owner
Run/bin/runinstaller and uninstall oracle software
Restrictions and Usage Notes (FROM ORACLE DOCS)
• RMAN> drop database including backup; This command can only be run from RMAN.
• You must be connected to the target database from RMAN while the database is mounted in EXCLUSIVE mode with RESTRICTED SESSION enabled.
• When using the "DROP DATABASE" command, RMAN drops the target database including the following files at the operating system level:
Datafiles
Online Redo Log Files
Controlfiles
SPFILE (if it exists)
• When including the "INCLUDING BACKUPS" clause, RMAN will delete the files listed above as well as the following files at the operating system level:
Archive Redo Logs
Backup pieces generated by RMAN for the target database
• When using the "DROP DATABASE" command with RMAN connected to a recovery catalog, RMAN will unregister the target database.
• The "DROP DATABASE" command does not delete the following files:
init.ora (text version of the Oracle initialization file)
password file
Deleing two databases (PROD/RMAN)
1. Deleting Target database using RMAN (PROD using RMAN)
Login as PROD user:
$ sqlplus /nolog
Sql> connect sys/pwd@connect_string as sysdba
Sql> startup mount exclusive
Sql> alter system enable restricted session
Sql> exit
Login as rman user
$ rman target sys/pwd@connect_string catalog rman/pwd@rman
RMAN> drop database including backup;
2. Deleting RMAN Database:
i. Connect to drop catalog
$ connect catalog rman/pwd@rman
RMAN> drop catalog;
RMAN> exit
ii. Check Datafile, Controlfile and Redo logs;
SQL> select name from v$datafile/ v$logfile / v$controlfile;
iii. Drop Database
SQL> startup mount exclusive restrict;
SQL> drop database;
3. Removing Oracle10g Software:
Login as software owner
Run
Restrictions and Usage Notes (FROM ORACLE DOCS)
• RMAN> drop database including backup; This command can only be run from RMAN.
• You must be connected to the target database from RMAN while the database is mounted in EXCLUSIVE mode with RESTRICTED SESSION enabled.
• When using the "DROP DATABASE" command, RMAN drops the target database including the following files at the operating system level:
Datafiles
Online Redo Log Files
Controlfiles
SPFILE (if it exists)
• When including the "INCLUDING BACKUPS" clause, RMAN will delete the files listed above as well as the following files at the operating system level:
Archive Redo Logs
Backup pieces generated by RMAN for the target database
• When using the "DROP DATABASE" command with RMAN connected to a recovery catalog, RMAN will unregister the target database.
• The "DROP DATABASE" command does not delete the following files:
init
password file
Subscribe to:
Posts (Atom)