Restore Rman on ASM+RAC
Tried to prepare full documents to restore DB from PROD TO DEV
Make Copy of PROD DB in DEV == DB In RAC+ASM+RMAN
USING RMAN COLD BACK
Take a cold backup of RMAN as Under (we made a copy of DB with Netsnap to second location to use the cold backup and reduce network)
RMAN> connect target *
2> run
3> {
4> shutdown immediate;
5> startup mount;
6> allocate channel d01 type disk;
7> allocate channel d02 type disk;
8> allocate channel d03 type disk;
9> allocate channel d04 type disk;
10> allocate channel d05 type disk;
11> allocate channel d06 type disk;
12> backup database format '/d2/orabackup/RMAN/XXXPRD_%U'
13> include current controlfile;
14> }
ISSUE MORE CHANNELS TO COMPPLETE FAST AS ITS ON LOCAL DISK AND MAKE SURE WE HAVE SPACE AVAILABLE BEFORE FIRE
After its completion, ASK SA or you can do the FTP TO DEV DB Server all the RMAN backup Piece and start the PROD DB
Some IMPORTANT Point== Make sure you keep the RMAN backup Piece under same location where we took the back, or create same directory structure in DEV box
Shutdown the DEV Instances in RAC as under and before shutdown take backup of controlfile and also create a init file as you might be using spfile under ASM
srvctl stop instance -d xxxdev -i xxxdev01
srvctl stop instance -d xxxdev -i xxxdev02
Or
USE the Normal shutdown commands for both instances.
ASM Commands to removed the directory structure and also add the DISkGROUPS in ASM INSTANCE as UNDER
CHECK THE CURRENT structure in ASM sqlplus
SELECT name, type, total_mb, free_mb, required_mirror_free_mb,
usable_file_mb FROM V$ASM_DISKGROUP;
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
,free_mb free_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
===
Then CREATE THE DISKGROUP AS PER REQUIREMENT, under is the example
SQL> CREATE DISKGROUP xxxCTRL External REDUNDANCY DISK
'/orcl_san_links/asm/orcl_asm_rdw_ctrl1' size 10200M; 2
Diskgroup created.
SQL> CREATE DISKGROUP xxxREDO External REDUNDANCY DISK
'/orcl_san_links/asm/orcl_asm_rdw_redo1' size 10200M,
'/orcl_san_links/asm/orcl_asm_rdw_redo2' size 10200M; 2 3
Diskgroup created.
SQL> SELECT name from V$ASM_DISKgroup;
NAME
----------------------------------------
AAAADATA
xxxDATA
xxxARCH
xxxCTRL
xxxREDO
YOU CAN ADD THE DISK TO EXISTING GROUP AS UNDER
SQL> ALTER DISKGROUP xxxDATA ADD DISK '/dev/rdsk/c6t60A98000486E5850564A597964434473d0s0' NAME xxxDATA_0003;
Diskgroup altered.
OR
If you want to remove anything existing or created wrong then removed it as under
SQL> drop diskgroup xxxARCH including contents;
Diskgroup dropped.
---------------------------------------
CREATE DISKGROUP xxxARCH External REDUNDANCY DISK
'/orcl_san_links/asm/orcl_asm_rdw_arlogs1' SIZE 51100M,
'/orcl_san_links/asm/orcl_asm_rdw_arlogs2' SIZE 51100M,
'/orcl_san_links/asm/orcl_asm_rdw_arlogs3' SIZE 51100M,
'/orcl_san_links/asm/orcl_asm_rdw_arlogs4' SIZE 51100M;
Check the structure under ASM now, and remove the other physical files under ASM or create new Directories
munishtandon:/export/home/oracle $ asmcmd
ASMCMD> ls -lrt
State Type Rebal Name
cMOUNTED EXTERN N xxxREDO/
d MOUNTED EXTERN N xxxDATA/
MOUNTED EXTERN N xxxCTRL/
MOUNTED EXTERN N xxxARCH/
MOUNTED EXTERN N XXXDATA/
go the the Particular directory to remove the files under ASM
just use the simple unix command rm
create the new directory structure under ASM, like mkdir and sub directory (this is what you are keeping in initfile)
Also make the necessary changes under ASM initfile, if you are not using the spfile for ASM instance.
SHUTDOWN THE ASM instance and make necessary changes in init file on BOTH NODES (pointing to parameter diskgroups)
RESTART the ASM instance on both nodes and check the new file diskgroups mounted or not, if not then investigate more.
CURRENT SITUATION
1. YOU have RMAN COLD BACKUP PIECE ON DEV BOX
2. YOU HAD ADDED THE NEW DISKGROUPS UNDER ASM
3. TOOK BACKUP OF CONTROLFILE AND CREATED INIT FILE FROM EXISTING DEV DB
4. SHUTDOWN BOTH THE INSTANCES under RAC
5. REMOVED THE EXISTING PHYSICAL FILES UNDER ASM to CLEAR THE DB.
HERE IS THE NEW INIT FILE NOW to RESTORE THE DB, PLEASE NOTE WE ARE NOT CHANGING THE DB NAME AT THIS POINT, KEEPING PROD NAME in INIT, ALSO COMMENTING CLUSTER P*
***///
xxxdev02.__db_cache_size=872415232
xxxdev01.__db_cache_size=570425344
xxxdev01.__java_pool_size=16777216
xxxdev02.__java_pool_size=16777216
xxxdev01.__large_pool_size=33554432
xxxdev02.__large_pool_size=33554432
xxxdev01.__oracle_base='/opt/oracle/base'#ORACLE_BASE set from environment
xxxdev02.__oracle_base='/opt/oracle/base'#ORACLE_BASE set from environment
xxxdev01.__pga_aggregate_target=3875536896
xxxdev02.__pga_aggregate_target=3875536896
xxxdev01.__sga_target=1610612736
xxxdev02.__sga_target=1610612736
xxxdev01.__shared_io_pool_size=0
xxxdev02.__shared_io_pool_size=0
xxxdev02.__shared_pool_size=654311424
xxxdev01.__shared_pool_size=956301312
xxxdev02.__streams_pool_size=16777216
xxxdev01.__streams_pool_size=16777216
*.audit_file_dest='/opt/oracle/base/admin/xxxdev/adump'
*.audit_trail='db'
#*.cluster_database_instances=2
#*.cluster_database=true
*.compatible='11.1.0.0.0'
*.control_files='+xxxCTRL/xxxDEV/CONTROLFILE/current.256.713444083','+xxxCTRL/xxxDEV/CONTROLFILE/current.257.713444083','+xxxCTRL/xxxDEV/CON
TROLFILE/current.258.713444083'
*.db_block_size=8192
*.db_create_file_dest='+xxxDATA'
*.db_domain='citco.com'
*.db_name='xxxprd'
*.db_recovery_file_dest_size=104857600
*.db_recovery_file_dest='+xxxDATA'
*.diagnostic_dest='/opt/oracle/base'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=xxxdev0XDB)'
#xxxprd02.instance_number=2
#xxxprd01.instance_number=1
#xxxdev02.local_listener='LISTENER_xxxDEV02'
#xxxdev01.local_listener='LISTENER_xxxDEV01'
*.log_archive_dest_1='location=+xxxARCH/xxxDEV/ARCHIVELOG'
*.log_archive_format='xxxprd_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=3865051136
*.processes=400
*.remote_listener=''
*.remote_login_passwordfile='exclusive'
*.sessions=445
*.sga_target=1610612736
xxxdev02.thread=2
xxxdev01.thread=1
*.undo_management='AUTO'
*.undo_retention=600
xxxprd02.undo_tablespace='UNDOTBS2'
xxxprd01.undo_tablespace='UNDOTBS1'
***///
TIME TO KICK RESTORE NOW
PLEASE NOTE, WE DON"T HAVE EVEN THE CONTROLFILE AT THIS POINT, we are DOING FIRST CONTROLFILE RESTORE
TRY TO START THE INSTANCE ON ONE NODE WITH NEW PFILE AS ABOVE in nomount stage, as we are restoring controlfile now as under then go to RMAN NOW
munishtandon:/d2/oraback/RDW/RMAN $ rman
Recovery Manager: Release 11.1.0.6.0 - Production on Thu Jan 13 13:20:40 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: xxxDEV (not mounted)
RMAN> restore controlfile from '/d2/oraback/RDW/RMAN/control_qtluhq83_1_1';
Starting restore at 13-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=418 instance=xxxdev01 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output file name=+xxxCTRL/xxxdev/controlfile/current.256.740323291
output file name=+xxxCTRL/xxxdev/controlfile/current.257.740323291
output file name=+xxxCTRL/xxxdev/controlfile/current.258.740323291
Finished restore at 13-JAN-11
RMAN> exit
SQL> alter database mount;
Database altered.
NOW WE CAN LIST THE BACKUP in RMAN EVEN, to see the last backup, if you want to use until SCN, but in hour case not required as we have COLD BACKUP.
NOW KICK THE RMAN RESTORE OF DATABASE, MAKE SURE you HAVE THE RMAN PIECE UNDER SAME DIRECTORY STRUCTURE at time of backup, AS CONTROLFILE IS LOOKING SAME LOCATION
RMAN>run
{
allocated channel d01 type disk;
allocated channel d02 type disk;
allocated channel d03 type disk;
allocated channel d04 type disk;
allocated channel d05 type disk;
allocated channel d06 type disk;
allocated channel d07 type disk;
allocated channel d08 type disk;
allocated channel d09 type disk;
restore database;
}
RESTORE COMPLETED IN almost 5 hours, without any issue
NOW TIME TO OPEN THE DATABASE NOW.
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> Alter database open resetlogs;
Database altered.
SQL> SQL>
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
SQL> select name from v$database;
NAME
----------------------------------------
xxxPRD
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +xxxARCH/xxxdev/archivelog
Oldest online log sequence 3
Next log sequence to archive 6
Current log sequence 6
SO GOOD JOB DONE TILL HERE
================
NOW TIME TO CHANGE THE DATABASE NAME AS DEV and ALSO START THE DB IN RAC NOW
MAKE A COPY OF EXISTING CONTROLFILE as under
SQL> alter database backup controlfile to trace;
SQL> shutdown
NEW CONTrolfile as under,
IMPORTANT === WE are also backuping up the existing controlfile under ASM first and also updating the init file now for controlfile
UNDER ASM
ASMCMD> rm current.257.740323291
ASMCMD> cp current.258.740323291 current.258.740323291_org3
copying +xxxCTRL/xxxDEV/CONTROLFILE/current.258.740323291 -> +xxxCTRL/xxxDEV/CONTROLFILE/current.258.740323291_org3
ASMCMD>
ASMCMD> ls -lrt
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE JAN 15 20:00:00 Y current.256.740323291_org1.265.740521835
CONTROLFILE UNPROT FINE JAN 15 20:00:00 Y current.257.740323291_org2.256.740522439
CONTROLFILE UNPROT FINE JAN 15 20:00:00 Y current.258.740323291
CONTROLFILE UNPROT FINE JAN 15 20:00:00 Y current.258.740323291_org3.257.740522477
ASMCMD> rm current.258.740323291
ASMCMD> ls -lrt
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE JAN 15 20:00:00 Y current.256.740323291_org1.265.740521835
CONTROLFILE UNPROT FINE JAN 15 20:00:00 Y current.257.740323291_org2.256.740522439
CONTROLFILE UNPROT FINE JAN 15 20:00:00 Y current.258.740323291_org3.257.740522477
CONTROLFILE AS UNDER
munishtandon:/u04/app/oracle/product/database/11.1/dbs $ more xxxdev_ctl.sql
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "xxxDEV" RESETLOGS NOARCHIVELOG
MAXLOGFILES 40
MAXLOGMEMBERS 3
MAXDATAFILES 200
MAXINSTANCES 8
MAXLOGHISTORY 2337
LOGFILE
GROUP 1 (
'+xxxREDO/redo11a.log',
'+xxxREDO/redo11b.log'
) SIZE 512M,
GROUP 2 (
'+xxxREDO/redo12a.log',
'+xxxREDO/redo12b.log'
) SIZE 512M,
GROUP 3 (
'+xxxREDO/redo13a.log',
'+xxxREDO/redo13b.log'
) SIZE 512M,
GROUP 4 (
'+xxxREDO/redo14a.log',
'+xxxREDO/redo14b.log'
) SIZE 512M,
GROUP 5 (
'+xxxREDO/redo21a.log',
'+xxxREDO/redo21b.log'
) SIZE 512M,
GROUP 6 (
'+xxxREDO/redo22a.log',
'+xxxREDO/redo22b.log'
) SIZE 512M,
GROUP 7 (
'+xxxREDO/redo23a.log',
'+xxxREDO/redo23b.log'
) SIZE 512M,
GROUP 8 (
'+xxxREDO/redo24a.log',
'+xxxREDO/redo24b.log'
) SIZE 512M,
GROUP 9 (
'+xxxREDO/redo31a.log',
'+xxxREDO/redo31b.log'
) SIZE 512M,
GROUP 10 (
'+xxxREDO/redo32a.log',
'+xxxREDO/redo32b.log'
) SIZE 512M,
GROUP 11 (
'+xxxREDO/redo33a.log',
'+xxxREDO/redo33b.log'
) SIZE 512M,
GROUP 12 (
'+xxxREDO/redo34a.log',
'+xxxREDO/redo34b.log'
) SIZE 512M
-- STANDBY LOGFILE
DATAFILE
'+xxxDATA/system01.dbf',
'+xxxDATA/pl_fact_indx_6.dbf',
'+xxxDATA/sysaux01.dbf',
'+xxxDATA/users01.dbf',
'+xxxDATA/op_tmp_1.dbf',
'+xxxDATA/system02.dbf',
'+xxxDATA/sysaux02.dbf',
'+xxxDATA/meta_rome_ts.dbf',
'+xxxDATA/oper_rome_ts.dbf',
'+xxxDATA/pl_fact_indx_2.dbf',
'+xxxDATA/stage_rep_data.dbf',
'+xxxDATA/sysaux03.dbf',
'+xxxDATA/rep_data_1.dbf',
'+xxxDATA/rep_indx01.dbf',
'+xxxDATA/psdata01.dbf',
'+xxxDATA/psdate02.dbf',
'+xxxDATA/studentdata01.dbf',
'+xxxDATA/psadmin_data.dbf',
'+xxxDATA/rep_data_2.dbf',
'+xxxDATA/stage_rep_data_2.dbf',
'+xxxDATA/idc_data_1.dbf',
'+xxxDATA/idc_indx_1.dbf',
'+xxxDATA/modelmart01.dbf',
'+xxxDATA/modelmart_index01.dbf',
'+xxxDATA/pl_dimen_indx_2.dbf',
'+xxxDATA/oper_rome_ts_.dbf',
'+xxxDATA/op_tmp_2.dbf',
'+xxxDATA/cube_1_data_1.dbf',
'+xxxDATA/cube_1_data_2.dbf',
'+xxxDATA/cube_1_data_3.dbf',
'+xxxDATA/stage_rep_data_3.dbf',
'+xxxDATA/rep_data_3.dbf',
'+xxxDATA/rep_data_4.dbf',
'+xxxDATA/tools01.dbf',
'+xxxDATA/stage_rep_data_4.dbf',
'+xxxDATA/idc_data_2.dbf',
'+xxxDATA/idc_data_3.dbf',
'+xxxDATA/stage_rep_data_5.dbf',
'+xxxDATA/users02.dbf',
'+xxxDATA/stage_rep_data_6.dbf',
'+xxxDATA/cube_1_data_4.dbf',
'+xxxDATA/cube_1_data_5.dbf',
'+xxxDATA/idc_indx_2.dbf',
'+xxxDATA/cube_1_data_6.dbf',
'+xxxDATA/rep_data_5.dbf',
'+xxxDATA/cube_1_data_7.dbf',
'+xxxDATA/stage_rep_data_7.dbf',
'+xxxDATA/rep_data_6.dbf',
'+xxxDATA/cube_1_data_8.dbf',
'+xxxDATA/cube_1_indx_1.dbf',
'+xxxDATA/pl_dimen_data_1.dbf',
'+xxxDATA/pl_dimen_indx_1.dbf',
'+xxxDATA/pl_fact_data_1.dbf',
'+xxxDATA/pl_fact_indx_1.dbf',
'+xxxDATA/pl_meta_data_1.dbf',
'+xxxDATA/pl_dimen_data_2.dbf',
'+xxxDATA/cube_1_indx_2.dbf',
'+xxxDATA/cube_1_indx_3.dbf',
'+xxxDATA/cube_1_indx_4.dbf',
'+xxxDATA/cube_1_indx_5.dbf',
'+xxxDATA/cube_1_indx_6.dbf',
'+xxxDATA/pl_dimen_data_3.dbf',
'+xxxDATA/pl_dimen_data_4.dbf',
'+xxxDATA/pl_dimen_data_5.dbf',
'+xxxDATA/pl_dimen_data_6.dbf',
'+xxxDATA/pl_dimen_data_7.dbf',
'+xxxDATA/pl_dimen_data_8.dbf',
'+xxxDATA/pl_dimen_data_9.dbf',
'+xxxDATA/pl_dimen_data_10.dbf',
'+xxxDATA/pl_dimen_data_11.dbf',
'+xxxDATA/pl_dimen_data_12.dbf',
'+xxxDATA/pl_fact_indx_3.dbf',
'+xxxDATA/stage_rep_data_8.dbf',
'+xxxDATA/pl_fact_data_.2.dbf',
'+xxxDATA/pl_fact_data_.3.dbf',
'+xxxDATA/pl_fact_indx_4.dbf',
'+xxxDATA/cube_1_indx_7dbf',
'+xxxDATA/cube_1_indx_8.dbf',
'+xxxDATA/op_tmp_3.dbf',
'+xxxDATA/pl_fact_data_.4.dbf',
'+xxxDATA/pl_fact_data_5.dbf',
'+xxxDATA/cube_1_data_9.dbf',
'+xxxDATA/cube_1_data_10.dbf',
'+xxxDATA/cube_1_data_11.dbf',
'+xxxDATA/op_tmp_4.dbf',
'+xxxDATA/pl_fact_indx_5.dbf',
'+xxxDATA/cube_1_data_12.dbf',
'+xxxDATA/cube_1_data_14.dbf',
'+xxxDATA/pl_fact_data_6.dbf',
'+xxxDATA/cube_1_data_15.dbf',
'+xxxDATA/pl_fact_data_7.dbf',
'+xxxDATA/undotbs2_1.dbf',
'+xxxDATA/arc_pl_1.dbf',
'+xxxDATA/arc_pl_2.dbf',
'+xxxDATA/arc_pl_3.dbf',
'+xxxDATA/arc_pl_4.dbf',
'+xxxDATA/cube_1_data_16.dbf',
'+xxxDATA/pl_fact_data_8.dbf',
'+xxxDATA/pl_fact_data_9.dbf',
'+xxxDATA/pl_fact_indx_7.dbf',
'+xxxDATA/pl_fact_data_2_01.dbf',
'+xxxDATA/pl_fact_data_2.dbf',
'+xxxDATA/plf_arc_010608_data_1.dbf',
'+xxxDATA/plf_arc_010608_data_2.dbf',
'+xxxDATA/plf_arc_010608_data_3..dbf',
'+xxxDATA/plf_arc_010608_indx_1.dbf',
'+xxxDATA/plf_arc_010608_indx_2.dbf',
'+xxxDATA/pl_fact_data_10.dbf',
'+xxxDATA/pl_fact_data_11.dbf',
'+xxxDATA/pl_fact_indx_8.dbf',
'+xxxDATA/cube_1_data_17.dbf',
'+xxxDATA/cube_1_data_18.dbf',
'+xxxDATA/arc_pl_5.dbf',
'+xxxDATA/arc_pl_6.dbf',
'+xxxDATA/pl_fact_data_12.dbf',
'+xxxDATA/pl_fact_indx_9.dbf',
'+xxxDATA/pl_fact_data_14.dbf',
'+xxxDATA/pl_fact_data_16.dbf',
'+xxxDATA/cube_1_data_19.dbf',
'+xxxDATA/pl_fact_indx_10.dbf',
'+xxxDATA/arc_pl_7.dbf',
'+xxxDATA/arc_pl_8.dbf',
'+xxxDATA/pl_fct_dt_101208_1.dbf',
'+xxxDATA/pl_fct_dt_101208_2.dbf',
'+xxxDATA/pl_fct_ix_101208_1.dbf',
'+xxxDATA/pl_fct_ix_101208_2.dbf',
'+xxxDATA/cbe_dt_101208_1.dbf',
'+xxxDATA/cbe_dt_101208_2.dbf',
'+xxxDATA/cbe_dt_101208_3.dbf',
'+xxxDATA/cb_in_101208_1.dbf',
'+xxxDATA/cb_in_101208_2.dbf',
'+xxxDATA/pl_fact_data_17.dbf',
'+xxxDATA/cube_1_data_20.dbf',
'+xxxDATA/cube_1_data_21.dbf',
'+xxxDATA/market_data_1.dbf',
'+xxxDATA/market_data_2.dbf',
'+xxxDATA/market_indx_1.dbf',
'+xxxDATA/undotbs2_2.dbf',
'+xxxDATA/market_data_3.dbf',
'+xxxDATA/market_data_4.dbf',
'+xxxDATA/market_data_5.dbf',
'+xxxDATA/market_data_6.dbf',
'+xxxDATA/market_data_7.dbf',
'+xxxDATA/market_data_8.dbf',
'+xxxDATA/market_data_9.dbf',
'+xxxDATA/market_data_10.dbf',
'+xxxDATA/pl_fct_dt_101208_3.dbf',
'+xxxDATA/market_data_11.dbf',
'+xxxDATA/market_data_12.dbf',
'+xxxDATA/vpd_admin_ts01.dbf',
'+xxxDATA/cube_1_data_22.dbf',
'+xxxDATA/cube_1_data_23.dbf',
'+xxxDATA/cube_1_data_24.dbf',
'+xxxDATA/cube_1_data_25.dbf',
'+xxxDATA/cube_1_indx_9.dbf',
'+xxxDATA/cube_1_indx_10.dbf',
'+xxxDATA/market_indx_2.dbf',
'+xxxDATA/market_data_13.dbf',
'+xxxDATA/market_data_14.dbf',
'+xxxDATA/market_data_15.dbf',
'+xxxDATA/market_data_16.dbf',
'+xxxDATA/market_data_17.dbf',
'+xxxDATA/market_data_18.dbf',
'+xxxDATA/market_data_19.dbf',
'+xxxDATA/market_data_20.dbf',
'+xxxDATA/market_data_21.dbf',
'+xxxDATA/pl_dimen_data_13.dbf',
'+xxxDATA/pl_fct_ix_101208_3.dbf',
'+xxxDATA/pl_dimen_data_14.dbf',
'+xxxDATA/pl_dimen_data_15.dbf',
'+xxxDATA/cbe_dt_101208_4.dbf',
'+xxxDATA/undotbs1_1.dbf',
'+xxxDATA/undotbs1_2.dbf',
'+xxxDATA/undotbs3_1.dbf',
'+xxxDATA/undotbs3_2.dbf',
'+xxxDATA/cube_1_indx_11.dbf',
'+xxxDATA/cfp_data01.dbf',
'+xxxDATA/pl_dimen_indx_3.dbf',
'+xxxDATA/cube_1_data_26.dbf',
'+xxxDATA/cube_1_indx_12.dbf',
'+xxxDATA/pl_fct_ix_101208_4.dbf',
'+xxxDATA/pl_fct_ix_101208_5.dbf'
CHARACTER SET WE8ISO8859P1
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP1 ADD TEMPFILE '+xxxDATA/temp1.dbf'
SIZE 32767M REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
GOT SOME ERROR AND DID THE CHANGES IN INIT FILE FOR CONTROLFILE
////////////////
SQL> @xxxdev_ctl.sql
ORACLE instance started.
Total System Global Area 1620664320 bytes
Fixed Size 2089064 bytes
Variable Size 1023418264 bytes
Database Buffers 570425344 bytes
Redo Buffers 24731648 bytes
CREATE CONTROLFILE SET DATABASE "xxxDEV" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '+xxxCTRL/xxxdev/controlfile/current.256.713444083'
ORA-17502: ksfdcre:4 Failed to create file +xxxCTRL/xxxdev/controlfile/current.256.713444083
ORA-15046: ASM file name '+xxxCTRL/xxxdev/controlfile/current.256.713444083' is not in single-file creation form
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01507: database not mounted
ALTER TABLESPACE TEMP1 ADD TEMPFILE '+xxxDATA/temp1.dbf'
*
ERROR at line 1:
ORA-01109: database not open
SQL> shutdown immediate
ORA-01507: database not mounted
//////////////////
AFTER MAKING THE CHANGES AS UNDER IT WENT FINE
*.control_files='+xxxCTRL/xxxDEV/CONTROLFILE/controlfile_01.ctl','+xxxCTRL/xxxDEV/CONTROLFILE/contolfile_02.ctl','+xxxCTRL/xxxDEV/CONTROLFILE/controlfile_03.ctl
USED UNDER mentioned INIT FILE to start the DB and create new controlfile, but I also commented cluster Parameters, but its showing in this file after creation
of new controlfile and DB, cluster PARAMETER HAS BEEN put back
//////////////
munishtandon:/u04/app/oracle/product/database/11.1/dbs $ more initxxxdev01.ora_ORG
xxxdev02.__db_cache_size=872415232
xxxdev01.__db_cache_size=570425344
xxxdev01.__java_pool_size=16777216
xxxdev02.__java_pool_size=16777216
xxxdev01.__large_pool_size=33554432
xxxdev02.__large_pool_size=33554432
xxxdev01.__oracle_base='/opt/oracle/base'#ORACLE_BASE set from environment
xxxdev02.__oracle_base='/opt/oracle/base'#ORACLE_BASE set from environment
xxxdev01.__pga_aggregate_target=3875536896
xxxdev02.__pga_aggregate_target=3875536896
xxxdev01.__sga_target=1610612736
xxxdev02.__sga_target=1610612736
xxxdev01.__shared_io_pool_size=0
xxxdev02.__shared_io_pool_size=0
xxxdev02.__shared_pool_size=654311424
xxxdev01.__shared_pool_size=956301312
xxxdev02.__streams_pool_size=16777216
xxxdev01.__streams_pool_size=16777216
*.audit_file_dest='/opt/oracle/base/admin/xxxdev/adump'
*.audit_trail='db'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='11.1.0.0.0'
*.control_files='+xxxCTRL/xxxDEV/CONTROLFILE/controlfile_01.ctl','+xxxCTRL/xxxDEV/CONTROLFILE/contolfile_02.ctl','+xxxCTRL/xxxDEV/CONTROLFIL
E/controlfile_03.ctl'
*.db_block_size=8192
*.db_create_file_dest='+xxxDATA'
*.db_domain='citco.com'
*.db_name='xxxdev'
*.db_recovery_file_dest_size=104857600
*.db_recovery_file_dest='+xxxDATA'
*.diagnostic_dest='/opt/oracle/base'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=xxxdev0XDB)'
xxxdev02.instance_number=2
xxxdev01.instance_number=1
xxxdev02.local_listener='LISTENER_xxxDEV02'
xxxdev01.local_listener='LISTENER_xxxDEV01'
*.log_archive_dest_1='location=+xxxARCH/xxxDEV/ARCHIVELOG'
*.log_archive_format='xxxprd_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=3865051136
*.processes=400
*.remote_listener=''
*.remote_login_passwordfile='exclusive'
*.sessions=445
*.sga_target=1610612736
xxxdev02.thread=2
xxxdev01.thread=1
*.undo_management='AUTO'
*.undo_retention=600
xxxprd02.undo_tablespace='UNDOTBS2'
xxxprd01.undo_tablespace='UNDOTBS1'
/////////////////
SQL> @xxxdev_ctl.sql
ORACLE instance started.
Total System Global Area 1620664320 bytes
Fixed Size 2089064 bytes
Variable Size 1023418264 bytes
Database Buffers 570425344 bytes
Redo Buffers 24731648 bytes
Control file created.
Database altered.
Tablespace altered.
SQL>
SQL> select name from v$database;
NAME
----------------------------------------
xxxDEV
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
xxxdev01
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination +xxxARCH/xxxdev/archivelog
Oldest online log sequence 1
Current log sequence 1
SQL>
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------------------------------- --- ---------- --------------
+xxxCTRL/xxxdev/controlfile/controlfile_01.ctl NO 16384 872
+xxxCTRL/xxxdev/controlfile/contolfile_02.ctl NO 16384 872
+xxxCTRL/xxxdev/controlfile/controlfile_03.ctl NO 16384 872
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
LOOKS LIKE THIS AFTER CREATION in ASMCMD
N contolfile_02.ctl => +xxxCTRL/xxxDEV/CONTROLFILE/Current.268.740525747
N controlfile_01.ctl => +xxxCTRL/xxxDEV/CONTROLFILE/Current.258.740525747
N controlfile_03.ctl => +xxxCTRL/xxxDEV/CONTROLFILE/Current.269.740525747
ASMCMD> pwd
+xxxCTRL/xxxDEV/CONTROLFILE
NOW START THE DB WITH CLUSTER PARAMETER, just remove the comments in INIT FILE FOR CLUSTER and start DB
TIME TO CREATE SPFILE NOW ....
SQL> create spfile='+xxxCTRL/xxxDEV/PARAMETERFILE/spfilexxxdev.ora' from pfile='/u04/app/oracle/product/database/11.1/dbs/initxxxdev01.ora';
File created.
MAKE THE CHANGE IN INIT FILE AS UNDER
munishtandon:/u04/app/oracle/product/database/11.1/dbs $ mv initxxxdev01.ora initxxxdev01.ora_ORG
munishtandon:/u04/app/oracle/product/database/11.1/dbs $ vi initxxxdev01.ora
"initxxxdev01.ora" [New file]
spfile='+xxxCTRL/xxxDEV/PARAMETERFILE/spfilexxxdev.ora'
SQL> startup
ORACLE instance started.
Total System Global Area 1620664320 bytes
Fixed Size 2089064 bytes
Variable Size 1023418264 bytes
Database Buffers 570425344 bytes
Redo Buffers 24731648 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ --------------- ------------------------------
spfile string +xxxCTRL/xxxdev/parameterfile/
spfilexxxdev.ora
CHECK THE STATUS IN CLUSTER
munishtandon:/u04/app/oracle/product/database/11.1/dbs $ srvctl config database -d xxxdev -a
munishtandon xxxdev01 /u04/app/oracle/product/database/11.1
munishtandonB xxxdev02 /u04/app/oracle/product/database/11.1
DB_UNIQUE_NAME: xxxdev
DB_NAME: xxxdev
ORACLE_HOME: /u04/app/oracle/product/database/11.1
SPFILE: +xxxDATA/xxxdev/spfilexxxdev0.ora
DOMAIN: citco.com
DB_ROLE: null
START_OPTIONS: null
POLICY: AUTOMATIC
ENABLE FLAG: DB ENABLED
munishtandon:/u04/app/oracle/product/database/11.1/dbs $ srvctl start instance -d xxxdev -i xxxdev01
munishtandon:/u04/app/oracle/product/database/11.1/dbs $
munishtandon:/u04/app/oracle/product/database/11.1/dbs $ ps -ef|grep pmon
oracle 6224 1 0 Jan 07 ? 26:56 ora_pmon_AAAADEV1
oracle 5236 1 0 Jan 07 ? 10:37 asm_pmon_+ASM1
oracle 18403 15562 0 23:08:14 pts/5 0:00 grep pmon
oracle 17668 1 0 23:07:13 ? 0:00 ora_pmon_xxxdev01
CHECK THE STATUS ON LISTENER
munishtandon:/u04/app/oracle/product/database/11.1/dbs $ ps -ef|grep tns
oracle 19825 15562 0 23:10:38 pts/5 0:00 grep tns
oracle 5181 1 0 Jan 07 ? 0:39 /u03/app/oracle/product/database/11.1.0/bin/tnslsnr LISTENER_AAAA_munishtandon -i
oracle 5287 1 0 Jan 07 ? 0:42 /u02/app/oracle/product/asm/11.1.0/bin/tnslsnr LISTENER_munishtandon -inherit
====
NOW TIME TO WORK ON NODE B
change the init file as above on node a
munishtandonB:/export/home/oracle $ srvctl start instance -d xxxdev -i xxxdev02
got Error
munishtandonB:ora.xxxdev.xxxdev02.inst:ORA-01618: redo thread 2 is not enabled - cannot mount
DID THE FOLLOWING ON NODE A TO GET READ OF THIS ISSUE
SQL> show parameter thread
NAME TYPE VALUE
------------------------------------ --------------- ------------------------------
parallel_threads_per_cpu integer 2
thread integer 1
SQL> alter database enable public thread=2;
alter database enable public thread=2
*
ERROR at line 1:
ORA-02174: Missing required thread number
SQL> alter database enable public thread 2;
alter database enable public thread 2
*
ERROR at line 1:
ORA-01613: instance UNNAMED_INSTANCE_2 (thread 2) only has 0 logs - at least 2 logs required to enable.
SQL> alter database enable public thread 2;
alter database enable public thread 2
*
ERROR at line 1:
ORA-01613: instance UNNAMED_INSTANCE_2 (thread 2) only has 0 logs - at least 2 logs required to enable.
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
SQL> select thread#, enabled from v$thread;
THREAD# ENABLED
---------- --------
1 PUBLIC
SQL> Alter database add logfile instance 'xxxdev02' group 13('+xxxREDO/redo35a.log','+xxxREDO/redo35b.log') size 512M;
Database altered.
SQL> Alter database add logfile instance 'xxxdev02' group 14('+xxxREDO/redo36a.log','+xxxREDO/redo36b.log') size 512M;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- --------------- ------------------------------------------------------- ---
12 ONLINE +xxxREDO/redo34a.log NO
12 ONLINE +xxxREDO/redo34b.log NO
11 ONLINE +xxxREDO/redo33a.log NO
11 ONLINE +xxxREDO/redo33b.log NO
10 ONLINE +xxxREDO/redo32a.log NO
10 ONLINE +xxxREDO/redo32b.log NO
9 ONLINE +xxxREDO/redo31a.log NO
9 ONLINE +xxxREDO/redo31b.log NO
8 ONLINE +xxxREDO/redo24a.log NO
8 ONLINE +xxxREDO/redo24b.log NO
7 ONLINE +xxxREDO/redo23a.log NO
7 ONLINE +xxxREDO/redo23b.log NO
6 ONLINE +xxxREDO/redo22a.log NO
6 ONLINE +xxxREDO/redo22b.log NO
5 ONLINE +xxxREDO/redo21a.log NO
5 ONLINE +xxxREDO/redo21b.log NO
4 ONLINE +xxxREDO/redo14a.log NO
4 ONLINE +xxxREDO/redo14b.log NO
3 ONLINE +xxxREDO/redo13a.log NO
3 ONLINE +xxxREDO/redo13b.log NO
2 ONLINE +xxxREDO/redo12a.log NO
2 ONLINE +xxxREDO/redo12b.log NO
1 ONLINE +xxxREDO/redo11a.log NO
1 ONLINE +xxxREDO/redo11b.log NO
13 ONLINE +xxxREDO/redo35a.log NO
13 ONLINE +xxxREDO/redo35b.log NO
14 ONLINE +xxxREDO/redo36a.log NO
14 ONLINE +xxxREDO/redo36b.log NO
28 rows selected.
SQL> alter database enable public thread 2;
Database altered.
SQL> select thread#, enabled from v$thread;
THREAD# ENABLED
---------- --------
1 PUBLIC
2 PUBLIC
and then kicked it again on node B
munishtandonB:/u04/app/oracle/product/database/11.1/dbs $ srvctl start instance -d xxxdev -i xxxdev02
munishtandonB:/u04/app/oracle/product/database/11.1/dbs $
munishtandonB:/u04/app/oracle/product/database/11.1/dbs $ ps -ef|grep pmon
oracle 6436 1 0 Jan 07 ? 27:09 ora_pmon_AAAADEV2
oracle 5343 1 0 Jan 07 ? 12:18 asm_pmon_+ASM2
oracle 417 797 0 23:57:07 pts/1 0:00 grep pmon
oracle 29728 1 0 23:56:15 ? 0:00 ora_pmon_xxxdev02
munishtandonB:/u04/app/oracle/product/database/11.1/dbs $
munishtandonB:/u04/app/oracle/product/database/11.1/dbs $ ps -ef|grep tns
oracle 5277 1 0 Jan 07 ? 0:41 /u03/app/oracle/product/database/11.1.0/bin/tnslsnr LISTENER_AAAA_munishtandonB -i
oracle 5434 1 0 Jan 07 ? 0:27 /u02/app/oracle/product/asm/11.1.0/bin/tnslsnr LISTENER_munishtandonB -inherit
oracle 511 797 0 23:57:15 pts/1 0:00 grep tns
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ --------------- ------------------------------
spfile string +xxxCTRL/xxxdev/parameterfile/
spfilexxxdev.ora
SQL>
SQL> show parameter back
NAME TYPE VALUE
------------------------------------ --------------- ------------------------------
background_core_dump string partial
background_dump_dest string /opt/oracle/base/diag/rdbms/xx
idev/xxxdev02/trace
backup_tape_io_slaves boolean FALSE
db_flashback_retention_target integer 1440
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ --------------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
SQL> show parameter instance
NAME TYPE VALUE
------------------------------------ --------------- ------------------------------
active_instance_count integer
cluster_database_instances integer 2
instance_groups string
instance_name string xxxdev02
instance_number integer 2
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 2
SQL> select thread#, enabled from v$thread;
THREAD# ENABLED
---------- --------
1 PUBLIC
2 PUBLIC
================================ END =====================================
No comments:
Post a Comment