Explore Oracle: change

Showing posts with label change. Show all posts

Monday, June 23, 2014

Change the standby protection mode

Change the protection mode from maximum performance to maximum availability

SQL> show parameter log_archive_dest_3

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3                   string      SERVICE=ol6rac2  db_unique_nam
                                                 e=ol6rac2 sync lgwr compressio
                                                 n=enable max_failure=4 reopen=
                                                 8
log_archive_dest_30                  string
log_archive_dest_31                  string
SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(ol6rac1,ol6rac2)
SQL> select name,open_mode,protection_Mode,database_role from v$database;

NAME      OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
--------- -------------------- -------------------- ----------------
NEW12CDB  READ WRITE           MAXIMUM PERFORMANCE  PRIMARY

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2296576 bytes
Variable Size             671089920 bytes
Database Buffers          390070272 bytes
Redo Buffers                5480448 bytes
Database mounted.
SQL> alter database set standby database to maximize availability;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name, open_mode,protection_mode,database_role from v$database;

NAME      OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
--------- -------------------- -------------------- ----------------
NEW12CDB  READ WRITE           MAXIMUM AVAILABILITY PRIMARY

Sunday, May 18, 2014

Change archivelog and delete

RMAN>change archivelog all backed up 1 times to device type disk delete;
Ouput of the script execution

released channel: ORA_DISK_2

released channel: ORA_DISK_3

released channel: ORA_DISK_4

released channel: ORA_DISK_5

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=24 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=25 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=58 device type=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: SID=60 device type=DISK

List of Archived Log Copies for database with db_unique_name CVRMAN

=====================================================================



Key     Thrd Seq     S Low Time

------- ---- ------- - ---------

70      1    4       A 15-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_17/thread_1_seq_4.295.847791843



68      1    5       A 15-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_17/thread_1_seq_5.296.847791841



72      1    6       A 15-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_17/thread_1_seq_6.304.847791845



74      1    7       A 15-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_17/thread_1_seq_7.284.847791839



69      1    8       A 15-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_17/thread_1_seq_8.315.847791843



71      1    9       A 15-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_17/thread_1_seq_9.269.847791843



67      1    12      A 15-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_17/thread_1_seq_12.310.847791839



75      1    13      A 15-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_17/thread_1_seq_13.311.847791847



84      1    1       A 17-MAY-14

        Name: /SHARED/backup1/CVRMAN_1_1_847769732.arc



78      1    1       A 17-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_17/thread_1_seq_1.301.847792345



79      1    2       A 17-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_17/thread_1_seq_2.289.847792561



80      1    3       A 17-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_17/thread_1_seq_3.328.847792661



81      1    4       A 17-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_17/thread_1_seq_4.292.847802345



82      1    5       A 17-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_17/thread_1_seq_5.312.847802453



83      1    6       A 17-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_17/thread_1_seq_6.332.847836579



85      1    7       A 17-MAY-14

        Name: +DATA/cvrman/archivelog/2014_05_18/thread_1_seq_7.288.847857575





Do you really want to delete the above objects (enter YES or NO)?

Change and delete backup

RMAN> change backup of tablespace users device type disk delete;
Ouput of the script execution

released channel: ORA_DISK_1

released channel: ORA_DISK_2

released channel: ORA_DISK_3

released channel: ORA_DISK_4

released channel: ORA_DISK_5

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=24 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=25 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=58 device type=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: SID=60 device type=DISK



List of Backup Pieces

BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

------- ------- --- --- ----------- ----------- ----------

223     192     1   1   AVAILABLE   DISK        /SHARED/backup1/9fp8grvh_1_1



Do you really want to delete the above objects (enter YES or NO)?

RMAN> change copy
tag="CPY_DB"
delete ;

released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
released channel: ORA_DISK_5
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=24 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=25 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=58 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=60 device type=DISK
specification does not match any control file copy in the repository
List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
74 1 A 17-MAY-14 1246558 17-MAY-14
Name: +DATA/cvrman/datafile/system.314.847802947
Tag: CPY_DB

73 2 A 17-MAY-14 1246561 17-MAY-14
Name: +DATA/cvrman/datafile/sysaux.344.847802947
Tag: CPY_DB

71 3 A 17-MAY-14 1246567 17-MAY-14
Name: +DATA/cvrman/datafile/undotbs1.342.847802951
Tag: CPY_DB

78 4 A 18-MAY-14 1246607 17-MAY-14
Name: +DATA/cvrman/datafile/users.dbf
Tag: CPY_DB

75 4 A 17-MAY-14 1246607 17-MAY-14
Name: +DATA/cvrman/datafile/users.340.847803041
Tag: CPY_DB

72 5 A 17-MAY-14 1246564 17-MAY-14
Name: +DATA/cvrman/datafile/example.273.847802947
Tag: CPY_DB

70 6 A 17-MAY-14 1246570 17-MAY-14
Name: +DATA/cvrman/datafile/tbs1.341.847802953
Tag: CPY_DB

Do you really want to delete the above objects (enter YES or NO)?

change with keep and nokeep

RMAN> change backupset 192 keep until time 'sysdate+20';

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
keep attributes for the backup are changed
backup will be obsolete on date 07-JUN-14
backup set key=192 RECID=192 STAMP=847802445

RMAN> change backupset 192 nokeep;

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
keep attributes for the backup are deleted
backup set key=192 RECID=192 STAMP=847802445

change with completed and crosscheck

RMAN> change backup of database
completed after "sysdate-2"
crosscheck;

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/SHARED/backup1/9hp8grvi_1_1 RECID=221 STAMP=847802361
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/SHARED/backup1/9gp8grvi_1_1 RECID=222 STAMP=847802364
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/SHARED/backup1/9fp8grvh_1_1 RECID=223 STAMP=847802359
Crosschecked 3 objects

change with unavailable,validate,crosscheck

RMAN> change backuppiece '/SHARED/backup1/9gp8grvi_1_1' unavailable;

changed backup piece unavailable
backup piece handle=/SHARED/backup1/9gp8grvi_1_1 RECID=222 STAMP=847802364
Changed 1 objects to UNAVAILABLE status

RMAN> change backupset 191 available;

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
changed backup piece available
backup piece handle=/SHARED/backup1/9gp8grvi_1_1 RECID=222 STAMP=847802364
Changed 1 objects to AVAILABLE status

RMAN> change backuppiece '/SHARED/backup1/9gp8grvi_1_1' validate;

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

crosschecked backup piece: found to be 'AVAILABLE'

backup piece handle=/SHARED/backup1/9gp8grvi_1_1 RECID=222 STAMP=847802364

Crosschecked 1 objects

RMAN> change backuppiece '/SHARED/backup1/9gp8grvi_1_1' crosscheck;