Command execution Series

Hi experts,

You have been a great source of my technical abilities ever since.

I do have a series of EXECUTION on Commands on various stages.

I just want to know whether I have MISSED or NOT in each step-wise serial execution numbered below. As I work on VERITAS CLUSTERING SOFTWARE. :)



1. Shutdown Oracle

# sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown immediate
SQL> exit
# lsnrctl stop

2. Enquire on problem Partition

# sqlplus /nolog
SQL> connect / as sysdba
SQL> Select Segment_Name, Segment_Type, Extend_Id, Block_Id, Partition_Name from DBA_Extents;
SQL> exit
# lsnrctl stop

3. Take a Before Backup

cd {the location of the datafile/affected partition}
copy *.dbf to /u02/oradata/BeforeBackup/
copy *.ctl to /u02/oradata/BeforeBackup/
copy *.log to /u02/oradata/BeforeBackup/

4. Export the Affected Data into a DMP file

exp userid/password file=special.dmp log= .log table = partitions= "CLOG_2005Q4"

5. Drop the broken Partition

SQL> connect / as sysdba
SQL> alter table Live_Log drop partition "CLOG_2005Q4"
SQL> alter table Proxy_Log drop partition "CLOG_2005Q4"
SQL> exit

6. Reduce the size of the affected datafile

SQL> connect / as sysdba
SQL> alter database datafile '/u02/oradata/CLOG/db02/CLOG_2005Q4.dbf' resize 1000M
SQL> exit

7. Recreate the affected partition

SQL> connect / as sysdba
SQL> alter table Live_Log, Proxy_Log
add partition "CLOG_2005Q4" values less than (to_date('2006-1-1', 'YYYY-MM-DD')) tablespace CLOG_2005Q4
SQL> exit

8. Take an After Backup

cd {the location of the datafile/affected partition}
copy *.dbf to /u02/oradata/AfterBackup/
copy *.ctl to /u02/oradata/AfterBackup/
copy *.log to /u02/oradata/AfterBackup/

9. Restart Oracle

# sqlplus /nolog
SQL> connect / as sysdba
SQL> startup
# lsnrctl stop

Is it necessary to check the Database out of Veritas Clustering Software (VCS) control before doing any of the above?

07-28-2006, 04:02 AM

davey23uk

your backups are uselss, you dont place the tablespaces in backup mode

07-28-2006, 05:26 AM

pvkdba

Thanks Davey.

Is it necessary to check the Database out of Veritas Clustering Software (VCS) control before doing any of the above?

Is it necessary That I need to Stop the Listener at every step OR will it have any impact on the scenario.

Thanks once again.

07-31-2006, 10:54 PM

PDSET

Quote:

---

Originally Posted by pvkdba

Thanks Davey.

Is it necessary to check the Database out of Veritas Clustering Software (VCS) control before doing any of the above?

Is it necessary That I need to Stop the Listener at every step OR will it have any impact on the scenario.

Thanks once again.

---

Veritas Clustering Software is group of clustering products which can be monitored by means of Veritas Enterprise Administrator (VEA) which monitors Vlume Manager, File system for Disk Management and OFS Operations and Techniques; It also does Database Edition for storage Checkpoint, Rollback Administration as well as File System Space, Planning Capacities need to be online for the database operations;and Veritas Replicator does job for transferring of Data to Remote servers.

08-04-2006, 03:39 AM

pvkdba

Partition Drops - Datafile Resized

Thanks very much PDSET, as it solved my problem being solved.

Can anyone Look into and clarify me if there is any EXEECUTION STEPS being wrongly done.

*************************************************

1 Enquire on problem Partition

@ OS
‘oerr ora-03297'

SQL> connect / as sysdba
SQL> Select Segment_Name, Segment_Type, Extend_Id, Block_Id, Partition_Name from DBA_Extents;
SQL> exit
# lsnrctl stop


2 Logical backup & Shutdown Oracle

@ OS
exp system/passwd file = '/usr/local/backup/bin/live_proxy.dmp' log = '/tmp/expdb.log' tables= Live_log, Proxy_log partitions = ' PRIM_2005Q4'

# sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown immediate
SQL> exit
# lsnrctl stop


3 Take a Before Backup
# sqlplus /nolog
SQL> connect / as sysdba
SQL>select file_name, tablespace_name, status from the dba_data_files;
OR
SQL> select file#, status from v$datafile;
/* to get the Data Filenames */
SQL> select lpad(name,45) Name, status from v$controlfile;
/* To get the Controlfile names */
SQL> select member from v$logfiles;
/* To get the Log File names */

SQL> shutdown immediate

@ OS
# cd /u02/oradata/PRIM/db02
# copy *.dbf to /u02/oradata/BeforeBackup/

# cd /u02/oradata/PRIM/db01
# copy *.dbf to /u02/oradata/BeforeBackup/

# cd /u02/oradata/PRIM/ix01
# copy *.dbf to /u02/oradata/BeforeBackup/

# cd /u02/oradata/PRIM/rbs01
# copy *.dbf to /u02/oradata/BeforeBackup

# cd /u01/oradata/PRIM/control
# copy *.ctl to /u02/oradata/BeforeBackup/
# cd /u02/oradata/PRIM/control
# copy *.ctl to /u02/oradata/BeforeBackup/

# cd /u03/oradata/PRIM/control
# copy *.ctl to /u02/oradata/BeforeBackup/

# cd /u03/oradata/PRIM/redo01
# copy *.log to /u02/oradata/BeforeBackup/


# copy Init_PRIM.Ora /u02/oradata/Beforebackup/


4 Start Oracle

# sqlplus /nolog
SQL> connect / as sysdba
SQL> startup

5 Drop the broken Partition

SQL> alter table Live_Log drop partition "PRIM_2005Q4" update global indexes;
/*update information into associated Indexes */
SQL> alter table Proxy_Log drop partition "PRIM_2005Q4" update global indexes;
/*update information into associated Indexes */


6 Resize Data File

SQL> alter database datafile '/u02/oradata/PRIM/db02/PRIM_2005Q4.dbf' resize 1000M;
/* Data file has been resized */

SQL> Select Segment_Name, Segment_Type, Extend_Id, Block_Id, Partition_Name from DBA_Extents;
/* to verify from step -2 information on BLOCK_ID where the obstruction has caused to resize the data file, if exists, then again DROP PARTITION*/

SQL> select * from dba_part_tables;

/* to check the partitions information on tables - ensure partition does not exist */

SQL> exit


7 Steps to roll back to the before backup

In case of database crashes during the process of the Datafile being RESIZEd, then following steps to rollback, primarily checking in the ALERT_LOG file from 'u01/app/oracle/admin/PRIM/bdump/alert_PRIM.log' for the kind of error that being proccessed.

# less u01/app/oracle/admin/PRIM/bdump/alert_PRIM.log /* inside the log look for error for which database has comes down*/

I If a logical restore is required then,

# sqlplus /nolog
SQL> connect / as sysdba
SQL> exit

# echo Logical backup which consists of tables and its associated partitions; This will ensure if and only if, the tables (Live_log, Proxy_log) were affected and not whole database

# imp system/passwd file = '/usr/local/backup/bin/live_proxy.dmp' log = '/tmp/impdb.log' tables= Live_log, Proxy_log partitions = ' PRIM_2005Q4'

II if a cold restore is required then,

# sqlplus /nolog
SQL> shutdown immediate

-- copy the data files back into respective directories

# copy /u02/oradata/BeforeBackup/c*.dbf
/u02/oradata/PRIM/db02
# copy /u02/oradata/BeforeBackup/cwmlite01.dbf
/u02/oradata/PRIM/db01
# copy /u02/oradata/BeforeBackup/odm01.dbf
/u02/oradata/PRIM/db01
# copy /u02/oradata/BeforeBackup/users01.dbf
/u02/oradata/PRIM/db01
# copy /u02/oradata/BeforeBackup/xdb01.dbf
/u02/oradata/PRIM/db01
# copy /u02/oradata/BeforeBackup/drsys01.dbf
/u02/oradata/PRIM/db01
# copy /u02/oradata/BeforeBackup/system01.dbf
/u02/oradata/PRIM/db01
# copy /u02/oradata/BeforeBackup/tools01.dbf /u02/oradata/PRIM/db01
# copy /u02/oradata/BeforeBackup/rbs*.dbf
/u02/oradata/PRIM/rbs01
# copy /u02/oradata/BeforeBackup/indx*.dbf
/u02/oradata/PRIM/ix01
# copy /u02/oradata/BeforeBackup/undo*.dbf
/u03/oradata/PRIM/rbs01

-- copy the Control files back into respective directories

copy /u02/oradata/BeforeBackup/control*.ora
/u02/oradata/PRIM/control

-- copy the Redolog files back into respective directories

copy /u02/oradata/BeforeBackup/redo01.log
/u02/oradata/PRIM/redo01
copy /u02/oradata/BeforeBackup/redo02.log
/u02/oradata/PRIM/redo02

*** find out where init_PRIM.ora file is located * MB
copy Init_PRIM.Ora /u02/oradata/Beforebackup

-- startup the database backup
- check * to ensure restore worked properly

# sqlplus /nolog
SQL> connect / as sysdba
SQL> startup

8 Recreate the affected partition

SQL> alter table Live_Log, Proxy_Log add partition "PRIM_2005Q4" values less than (to_date('2006-1-1', 'YYYY-MM-DD')) tablespace PRIM_2005Q4 update global indexes;

SQL> select * from dba_indexes;
/* if the index is dropped on which the Partition is based then recreate the indexes */

SQL> Drop index live_log_log_name_idx;

SQL> create index live_log_log_name_idx on live_log ( log_name, time) tablespace indx2 logging;

SQL> Drop index live_log_cs_idx ;

SQL> create index live_log_cs_idx on live_log ( cp_id, time) tablespace indx2 logging;

SQL> Drop index live_log_cp_id_idx ;

SQL> create index live_log_cp_id_idx on live_log ( cp_id, time,log_name) tablespace indx2 logging;

SQL> Drop index live_log_userid_idx;

SQL> create index live_log_userid_idx on live_log ( userid, time, log_name) tablespace indx2 logging;

SQL> Drop index live_log_msisdn_idx;
SQL> create index live_log_msisdn_idx on live_log ( msisdn, time, log_name) tablespace indx2 logging;

/* Need to create indexes since at time of EXPORT indexes were not considered since it takes more time so better be re-created */

SQL> exit



9 Take an After Backup

/* Repeat Steps 2, 3, 4 and 5, in case the crash might occur during the process of RESIZING of datafile, preferably COLD BACKUP
and during offtime just process the backup to TAPE drive. */

# cd /u02/oradata/PRIM/db02
# copy *.dbf to /u02/oradata/AfterBackup

# cd /u02/oradata/PRIM/db01
# copy *.dbf to /u02/oradata/AfterBackup

# cd /u02/oradata/PRIM/ix01
# copy *.dbf to /u02/oradata/AfterBackup

# cd /u02/oradata/PRIM/rbs01
# copy *.dbf to /u02/oradata/AfterBackup

# cd /u01/oradata/PRIM/control
# copy *.ctl to /u02/oradata/AfterBackup

# cd /u02/oradata/PRIM/control
# copy *.ctl to /u02/oradata/AfterBackup

# cd /u03/oradata/PRIM/control
# copy *.ctl to /u02/oradata/AfterBackup

# cd /u03/oradata/PRIM/redo01
# copy *.log to /u02/oradata/AfterBackup

# cd /u01/app/oracle/product/9.2.0.1.0/dbs/spdilePRIM.ora
# copy Init_PRIM.Ora /u02/oradata/Afterbackup


Thanks in Advance

All times are GMT -4. The time now is 03:22 PM.