How to take RMAN Backup from BCV copy of the Database:
1.
a.
DB Release info of target DB, catalog DB, BCV copy DB:
a) 10.2.0.4 - ASM storage
b) 10.2.0.4
c) 10.2.0.4 - ASM storage
b..
OS Release info of Primary and BCV:
a) Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
b) Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
2. Please verify fhrba_seq column values, should match between Primary and BCV copy:
-- On Primary
set linesize 120;
select hxfil FILE#,fhsta STAT,fhscn SCN, fhthr thr, fhrba_Seq SEQUENCE,fhtnm TABLESPACE
from x$kcvfh
order by 1;
-- On BCV
set linesize 120;
select hxfil FILE#,fhsta STAT,fhscn SCN, fhthr thr, fhrba_Seq SEQUENCE,fhtnm TABLESPACE
from x$kcvfh
order by 1;
Note: bug 6004226 may show different fhrba_seq column values between Primary and BCV copy.
Workaround is in step 4.
3.
Error:
RMAN> resync catalog;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 12/10/2008 07:45:32
ORA-00236: snapshot operation disallowed: mounted control file is a backup
RMAN> shutdown immediate
database dismounted
Oracle instance shut down
RMAN> run {
2> startup mount;
3> allocate channel edw_backup_sbt1 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
4> allocate channel edw_backup_sbt2 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
5> allocate channel edw_backup_sbt3 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
6> allocate channel edw_backup_sbt4 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
7> backup full filesperset = 10 as BACKUPSET tag '%d_full_backup_%TAG' database;
8> release channel edw_backup_sbt1;
9> release channel edw_backup_sbt2;
10>release channel edw_backup_sbt3;
11>release channel edw_backup_sbt4;
12>}
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 4194304000 bytes
Fixed Size 2089272 bytes
Variable Size 3976203976 bytes
Database Buffers 201326592 bytes
Redo Buffers 14684160 bytes
allocated channel: edw_backup_sbt1
channel edw_backup_sbt1: sid=4392 devtype=SBT_TAPE
channel edw_backup_sbt1: Veritas NetBackup for Oracle - Release 6.5 (2007111606)
allocated channel: edw_backup_sbt2
channel edw_backup_sbt2: sid=4386 devtype=SBT_TAPE
channel edw_backup_sbt2: Veritas NetBackup for Oracle - Release 6.5
(2007111606)
allocated channel: edw_backup_sbt3
channel edw_backup_sbt3: sid=4384 devtype=SBT_TAPE
channel edw_backup_sbt3: Veritas NetBackup for Oracle - Release 6.5 (2007111606)
allocated channel: edw_backup_sbt4
channel edw_backup_sbt4: sid=4382 devtype=SBT_TAPE
channel edw_backup_sbt4: Veritas NetBackup for Oracle - Release 6.5 (2007111606)
Starting backup at 10-DEC-08
released channel: edw_backup_sbt1
released channel: edw_backup_sbt2
released channel: edw_backup_sbt3
released channel: edw_backup_sbt4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/10/2008 07:47:10
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20035: invalid high recid
4.
Following is the complete procedure to take RMAN backup of BCV copy:
STEPS :
=======
1. Primary database has to be registered in the recovery catalog.
2. Verify the current RMAN Configuration.
show all;
* autobackup controlfile OFF
3. Take the BCV cut after the production database was shutdown with "shutdown immediate;".
4. On the staging server using BCV cut, Mount Database :
$ sqlplus / as sysdba
SQL> startup mount;
5. Check the controfile_type: (Controlfile type shoud be CURRENT ):
SQL> select controlfile_type from v$database;
CONTROL
---------
CURRENT
6. Connect to RMAN in nocatalog, take a backup of the controlfile and then restore it, you will have a backup controlfile then:
a.
$ rman target /
RMAN> run {
allocate channel disk1 device type disk;
backup current controlfile FORMAT '/u00/oradata/EDW/controlfile/current_controlfile1.ctl'; }
RMAN> exit;
b.
$ rman target /
RMAN> run {
allocate channel disk1 device type disk;
restore controlfile to '/u00/oradata/EDW/controlfile/backup_controlfile1.ctl' from '/u00/oradata/EDW/controlfile/current_controlfile1.ctl'; }
RMAN> exit;
7. Edit the init.ora file to point to the new controlfile (/u00/oradata/EDW/controlfile/backup_controlfile1.ctl)
8. Shutdown database:
SQL> shutdown immediate;
9. mount database:
SQL> startup mount;
10. Check the controfile_type : (Controlfile type shoud be BACKUP) :
SQL> select controlfile_type from v$database;
CONTROL
---------
BACKUP
11. Start RMAN session connected to this database mounted and the recovery catalog :
$ rman target / catalog rman/****@prman.world
12. Execute the backup.
RMAN> run {
2> startup mount;
3> allocate channel edw_backup_sbt1 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
4> allocate channel edw_backup_sbt2 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
5> allocate channel edw_backup_sbt3 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
6> allocate channel edw_backup_sbt4 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
7> backup full filesperset = 10 as BACKUPSET tag '%d_full_backup_%TAG' database;
8> release channel edw_backup_sbt1;
9> release channel edw_backup_sbt2;
10>release channel edw_backup_sbt3;
11>release channel edw_backup_sbt4;
12>}
Imp Note: If you want to open the database, edit the init.ora file to point to the original controlfile.
Tuesday, February 24, 2009
How to move a datafile from one diskgroup to another diskgroup in ASM using RMAN:
How to move a datafile from one diskgroup to another diskgroup in ASM using RMAN:
In this test, I will be moving the datafile from A_T1_DATA_01 to B_T1_DATA_01 diskgroup:
a. select the datafile to move:
sqlplus / as sysdba on DB:
col file_name for a50
select file_id, file_name from dba_data_files where file_id=7;
b. select the diskgroup to move:
sqlplus / as sysdba on ASM:
select GROUP_NUMBER, NAME, TOTAL_MB, FREE_MB, STATE from v$asm_diskgroup;
c. On DB:
. oraenv ORADB
rman target /
RMAN> report schema;
RMAN> sql 'alter database datafile 7 offline';
RMAN> backup as copy datafile 7 format '+B_T1_DATA_01';
RMAN> switch datafile 7 to COPY;
RMAN> recover datafile 7;
RMAN> sql 'alter database datafile 7 online';
RMAN> report schema;
d. Check and see the datafile new location:
sqlplus / as sysdba on DB:
col file_name for a50
select file_id, file_name from dba_data_files where file_id=7;
e. Remove the datafile from OLD disk group:
$ asmcmd
ASMCMD> cd A_T1_DATA_01/ORADB/datafile
ASMCMD> rm FLOW_1.776.644706747
In this test, I will be moving the datafile from A_T1_DATA_01 to B_T1_DATA_01 diskgroup:
a. select the datafile to move:
sqlplus / as sysdba on DB:
col file_name for a50
select file_id, file_name from dba_data_files where file_id=7;
b. select the diskgroup to move:
sqlplus / as sysdba on ASM:
select GROUP_NUMBER, NAME, TOTAL_MB, FREE_MB, STATE from v$asm_diskgroup;
c. On DB:
. oraenv ORADB
rman target /
RMAN> report schema;
RMAN> sql 'alter database datafile 7 offline';
RMAN> backup as copy datafile 7 format '+B_T1_DATA_01';
RMAN> switch datafile 7 to COPY;
RMAN> recover datafile 7;
RMAN> sql 'alter database datafile 7 online';
RMAN> report schema;
d. Check and see the datafile new location:
sqlplus / as sysdba on DB:
col file_name for a50
select file_id, file_name from dba_data_files where file_id=7;
e. Remove the datafile from OLD disk group:
$ asmcmd
ASMCMD> cd A_T1_DATA_01/ORADB/datafile
ASMCMD> rm FLOW_1.776.644706747
Sunday, February 15, 2009
Oracle masala at work - 2:
Oracle masala at work - 2:
1. How to make Oracle data Case-Insensitive search:
2. How to setup OEM Command Line Interface:
3. How to Update RMAN Recovery Catalog with Older Archive Log's that are on the Tape:
4. How to run the Oracle RAC Add Node procedure in Silent mode:
1. How to make Oracle data Case-Insensitive search:
a. Database Level Trigger to make Oracle data Case-Insensitive:
connect sys@ORADB as sysdba
--Schema Level
CREATE OR REPLACE TRIGGER sys.make_case_insensitive_AL
AFTER LOGON ON DATABASE
DECLARE
BEGIN
IF (user = 'TEST') THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP = LINGUISTIC'; -- 10gR2
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = BINARY_CI';
END IF;
END;
/
-- DB Level
CREATE OR REPLACE TRIGGER sys.make_case_insensitive_AL
AFTER LOGON ON DATABASE
DECLARE
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP = LINGUISTIC'; -- 10gR2
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = BINARY_CI';
END;
/
b. Verifying Index usage:
connect test@ORADB
create table emp(ename varchar2(50));
insert into emp values('ViJay');
insert into emp values('VIJAY');
insert into emp values('Vijay');
commit;
create index idx1_emp on emp(ename);
exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'EMP', degree=> 2, cascade => TRUE);
SET AUTOTRACE ON;
select * from emp where ename = 'vijay'; -- No Index use
select /*+ index(emp) */ * from emp where ename = 'vijay'; -- No Index use
select * from emp where ename LIKE 'Vi%'; -- This uses IDX1 Index FULL scan
-- To make use of the Index, we need to create below Index on ename column:
create index idx2_emp on emp(NLSSORT(ename,'NLS_SORT=BINARY_CI'));
exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'EMP', degree=> 2, cascade => TRUE);
select * from emp where ename = 'vijay'; -- This uses IDX2 Index
select * from emp where ename LIKE 'Vi%'; -- This uses IDX1 Index FULL scan (NOT IDX2 Index)
2. How to setup OEM Command Line Interface:
a.
export JAVA_HOME=/u00/app/oracle/product/OEM/oms10g/jdk
export PATH=$PATH:$JAVA_HOME/bin
cd /u00/app/oracle/product/OEM/oms10g/sysman/jlib
java -jar emclikit.jar client -install_dir=/export/home/oracle/OEMCL
b.
emcli setup -url="http://atlp158:4890/em" -username=sysman -password=oracle -dir=/export/home/oracle/OEMCL -trustall -novalidate
emcli sync
emcli help
emcli help update_password
emcli get_targets
3. How to Update RMAN Recovery Catalog with Older Archive Log's that are on the Tape:
a.Identify the Backup Pieces:
rman target / catalog=rman/rman1@prman
spool log to 'x.log';
list backup of archivelog all;
spool log off;
SELECT start_time, handle
FROM v$backup_piece
WHERE TRUNC (start_time) = '01-Feb-2009'
ORDER BY start_time;
b. Attach the missing Backup Pieces to RMAN Recovery Catalog:
rman target / catalog=rman/rman1@prman
-- Syntax for one Backup Pieces
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'aik6806g_1_1';
-- Syntax for more that one Backup Pieces
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'aik6806g_1_1,ahk6806g_1_1,ajk6806g_1_1';
4. How to run the Oracle RAC Add Node procedure in Silent mode:
a. Run below command from the existing node:
atlp260 > /DBHome/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={atlp259,atlp258,atlp257}" -logLevel trace -debug
Once all required DB binaries are copied from atlp260 to atlp257, atlp258 and atlp259 nodes, execute "root.sh" file:
/u00/app/oracle/product/10.2.0/DB/root.sh on atlp257, atlp258 and atlp259 nodes.
b.
Just to be sure, execute "crs_start -all" and check crs_stat -t for all the resources availability.
1. How to make Oracle data Case-Insensitive search:
2. How to setup OEM Command Line Interface:
3. How to Update RMAN Recovery Catalog with Older Archive Log's that are on the Tape:
4. How to run the Oracle RAC Add Node procedure in Silent mode:
1. How to make Oracle data Case-Insensitive search:
a. Database Level Trigger to make Oracle data Case-Insensitive:
connect sys@ORADB as sysdba
--Schema Level
CREATE OR REPLACE TRIGGER sys.make_case_insensitive_AL
AFTER LOGON ON DATABASE
DECLARE
BEGIN
IF (user = 'TEST') THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP = LINGUISTIC'; -- 10gR2
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = BINARY_CI';
END IF;
END;
/
-- DB Level
CREATE OR REPLACE TRIGGER sys.make_case_insensitive_AL
AFTER LOGON ON DATABASE
DECLARE
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP = LINGUISTIC'; -- 10gR2
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = BINARY_CI';
END;
/
b. Verifying Index usage:
connect test@ORADB
create table emp(ename varchar2(50));
insert into emp values('ViJay');
insert into emp values('VIJAY');
insert into emp values('Vijay');
commit;
create index idx1_emp on emp(ename);
exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'EMP', degree=> 2, cascade => TRUE);
SET AUTOTRACE ON;
select * from emp where ename = 'vijay'; -- No Index use
select /*+ index(emp) */ * from emp where ename = 'vijay'; -- No Index use
select * from emp where ename LIKE 'Vi%'; -- This uses IDX1 Index FULL scan
-- To make use of the Index, we need to create below Index on ename column:
create index idx2_emp on emp(NLSSORT(ename,'NLS_SORT=BINARY_CI'));
exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'EMP', degree=> 2, cascade => TRUE);
select * from emp where ename = 'vijay'; -- This uses IDX2 Index
select * from emp where ename LIKE 'Vi%'; -- This uses IDX1 Index FULL scan (NOT IDX2 Index)
2. How to setup OEM Command Line Interface:
a.
export JAVA_HOME=/u00/app/oracle/product/OEM/oms10g/jdk
export PATH=$PATH:$JAVA_HOME/bin
cd /u00/app/oracle/product/OEM/oms10g/sysman/jlib
java -jar emclikit.jar client -install_dir=/export/home/oracle/OEMCL
b.
emcli setup -url="http://atlp158:4890/em" -username=sysman -password=oracle -dir=/export/home/oracle/OEMCL -trustall -novalidate
emcli sync
emcli help
emcli help update_password
emcli get_targets
3. How to Update RMAN Recovery Catalog with Older Archive Log's that are on the Tape:
a.Identify the Backup Pieces:
rman target / catalog=rman/rman1@prman
spool log to 'x.log';
list backup of archivelog all;
spool log off;
SELECT start_time, handle
FROM v$backup_piece
WHERE TRUNC (start_time) = '01-Feb-2009'
ORDER BY start_time;
b. Attach the missing Backup Pieces to RMAN Recovery Catalog:
rman target / catalog=rman/rman1@prman
-- Syntax for one Backup Pieces
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'aik6806g_1_1';
-- Syntax for more that one Backup Pieces
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'aik6806g_1_1,ahk6806g_1_1,ajk6806g_1_1';
4. How to run the Oracle RAC Add Node procedure in Silent mode:
a. Run below command from the existing node:
atlp260 > /DBHome/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={atlp259,atlp258,atlp257}" -logLevel trace -debug
Once all required DB binaries are copied from atlp260 to atlp257, atlp258 and atlp259 nodes, execute "root.sh" file:
/u00/app/oracle/product/10.2.0/DB/root.sh on atlp257, atlp258 and atlp259 nodes.
b.
Just to be sure, execute "crs_start -all" and check crs_stat -t for all the resources availability.
Subscribe to:
Posts (Atom)
