Command execution Series
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Command execution Series

  1. #1
    Join Date
    Mar 2003
    Posts
    38

    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?
    __________________________________
    PVKDBA

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    your backups are uselss, you dont place the tablespaces in backup mode

  3. #3
    Join Date
    Mar 2003
    Posts
    38
    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.
    __________________________________
    PVKDBA

  4. #4
    Join Date
    Jul 2006
    Posts
    2
    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.

  5. #5
    Join Date
    Mar 2003
    Posts
    38

    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
    __________________________________
    PVKDBA

  6. Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •  


    Click Here to Expand Forum to Full Width