cesardbaLeave a Comment on Restore backup from RAC to Single Instance

Restore backup from RAC to Single Instance

1 – Criar um init do spfile.

1.1 – Remover todos os parâmetros node 2 e ajustar como o init de exemplo abaixo.

RAC1.__db_cache_size=22414360576
RAC2.__db_cache_size=22280142848
RAC2.__java_pool_size=536870912
RAC1.__java_pool_size=536870912
RAC2.__large_pool_size=268435456
RAC1.__large_pool_size=268435456
RAC2.__pga_aggregate_target=21474836480
RAC1.__pga_aggregate_target=21474836480
RAC2.__sga_target=37580963840
RAC1.__sga_target=37580963840
RAC2.__shared_io_pool_size=0
RAC1.__shared_io_pool_size=0
RAC1.__shared_pool_size=13958643712
RAC2.__shared_pool_size=13958643712
RAC1.__streams_pool_size=134217728
RAC2.__streams_pool_size=268435456
*._buffer_busy_wait_timeout=2
*._disable_interface_checking=TRUE
*._ENABLE_NUMA_SUPPORT=FALSE
*._FILE_SIZE_INCREASE_INCREMENT=2143289344
*._gc_policy_time=0
*._gc_undo_affinity=FALSE
*._KGL_CLUSTER_LOCK_READ_MOSTLY=TRUE
*._kill_diagnostics_timeout=140
*._lm_rcvr_hang_allow_time=140
*.audit_file_dest='/u01/app/oracle/admin/RAC/adump'
*.audit_sys_operations=TRUE
*.audit_trail='DB','EXTENDED'
*.cluster_database=true
*.compatible='11.2.0.2.0'
*.control_files='+REDO/rac/controlfile/current.281.798910783'
*.cpu_count=24
*.cursor_sharing='EXACT'
*.db_block_checking='FULL'
*.db_block_checksum='FULL'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.db_domain=''
*.db_files=1024
*.db_lost_write_protect='TYPICAL'
*.db_name='RAC'
*.db_recovery_file_dest=''
*.db_recovery_file_dest_size=858993459200
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=RACXDB)'
*.fast_start_mttr_target=300
*.filesystemio_options='setall'
*.global_names=FALSE
*.RAC1.instance_number=1
*.RAC2.instance_number=2
*.log_archive_dest_1='LOCATION=+RECO/RAC/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.log_buffer=67108864
*.open_cursors=2000
*.optimizer_mode='ALL_ROWS'
*.os_authent_prefix=''
*.parallel_adaptive_multi_user=FALSE
*.parallel_execution_message_size=16384
*.parallel_min_servers=0
*.parallel_threads_per_cpu=2
*.pga_aggregate_target=21474836480
*.RAC2.pga_aggregate_target=21474836480
*.processes=2400
*.recyclebin='OFF'
*.remote_listener='odasrv:1521'
*.remote_login_passwordfile='exclusive'
*.RAC1.resource_manager_plan='PLAN_NAO_COMERCIAL'
*.RAC2.resource_manager_plan='PLAN_COMERCIAL'
*.resource_manager_plan=''
*.session_cached_cursors=2000
*.RAC2.sga_max_size=38654705664
*.sga_target=37580963840
*.RAC2.sga_target=37580963840
*.RAC1.shared_pool_size=13958643712
*.shared_pool_size=13958643712
*.RAC2.shared_pool_size=13958643712
*.smtp_out_server='192.168.90.3:25'
*.sql92_security=TRUE
*.RAC2.thread=2
*.RAC1.thread=1
*.undo_retention=900
*.RAC2.undo_tablespace='UNDOTBS5'
*.RAC1.undo_tablespace='UNDOTBS1'
ShellScript

2 – Ajuste o init para o ambiente single instance.

RAC.__db_cache_size=22280142848
RAC.__java_pool_size=536870912
RAC.__large_pool_size=268435456
RAC.__pga_aggregate_target=21474836480
RAC.__sga_target=37580963840
RAC.__shared_io_pool_size=0
RAC.__shared_pool_size=13958643712
RAC.__streams_pool_size=134217728
*.audit_file_dest='/u01/app/oracle/admin/RAC/adump'
*.audit_sys_operations=TRUE
*.audit_trail='DB','EXTENDED'
*.compatible='11.2.0.2.0'
*.control_files='/u01/app/oracle/oradata/RAC/current.281.798910783' #Adjust the controlfiles restore location
*.cursor_sharing='EXACT'
*.db_block_checking='FULL'
*.db_block_checksum='FULL'
*.db_block_size=8192
*.db_domain=''
*.db_files=1024
*.db_lost_write_protect='TYPICAL'
*.db_name='RAC'
*.db_recovery_file_dest_size=858993459200
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=RACXDB)'
*.fast_start_mttr_target=300
*.filesystemio_options='setall'
*.global_names=FALSE
*.log_archive_dest_1='LOCATION=/NFS_SVVMFILE01/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.log_buffer=67108864
*.open_cursors=2000
*.optimizer_mode='ALL_ROWS'
*.os_authent_prefix=''
*.pga_aggregate_target=21474836480
*.processes=2400
*.recyclebin='OFF'
*.remote_login_passwordfile='exclusive'
*.resource_manager_plan=''
*.session_cached_cursors=2000
*.sga_max_size=37580963840
*.sga_target=37580963840
*.shared_pool_size=13958643712
*.smtp_out_server='192.168.90.3:25'
*.sql92_security=TRUE
*.undo_retention=86400
*.undo_tablespace='UNDOTBS1'
*.log_file_name_convert='+redo/RAC/onlinelog/','/u01/app/oracle/oradata/RAC/'
ShellScript

3 – Ajuste o caminho do redolog do ambiente RAC para sinlge instance.

SQL> select 'alter database rename File ''' || Member || ''' to ''' || REPLACE(Member,'+REDO/rac/onlinelog/','/u01/app/oracle/oradata/rac/') || ''';' from gv$logfile;
SQL

4 – Instale o binário do Oracle Database com os patchs necessários.

4.1 – Inicie o banco single com o init criado, faça o restore do backup de controlfile e monte o banco.

4.2 – Desative o block change tracking se estiver ativado.

rman target /
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT pfile='/backup/init.ora';
RUN
{
restore controlfile from '/backup/controlfile/c-2495972795-20191008-02';
ALTER DATABASE MOUNT;
}
catalog start with '/NFS_SVVMFILE01/backupset/' NOPROMPT;
catalog start with '/NFS_SVVMFILE01/archivelog/' NOPROMPT;
CROSSCHECK BACKUP OF DATABASE;
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK COPY;
sql 'alter database disable block change tracking';
SQL

5 – Faça o ajuste do local dos redologs para o ambiente single instance com o select informado acima, em seguida faça o restore do banco de dados usando o SET NEWNAME FOR DATABASE, desta forma não será necessário fazer o set newname for datafile para todos os datafiles.

5.1 – Se seu ambiente for enterprise use o paralelismo com vários canais.

rman target /
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
ALLOCATE CHANNEL c3 DEVICE TYPE DISK;
ALLOCATE CHANNEL c4 DEVICE TYPE DISK;
ALLOCATE CHANNEL c5 DEVICE TYPE DISK;
ALLOCATE CHANNEL c6 DEVICE TYPE DISK;
ALLOCATE CHANNEL c7 DEVICE TYPE DISK;
ALLOCATE CHANNEL c8 DEVICE TYPE DISK;
ALLOCATE CHANNEL c9 DEVICE TYPE DISK;
ALLOCATE CHANNEL c10 DEVICE TYPE DISK;
ALLOCATE CHANNEL c11 DEVICE TYPE DISK;
ALLOCATE CHANNEL c12 DEVICE TYPE DISK;
SET NEWNAME FOR DATABASE TO '/u01/app/oracle/oradata/RAC/%b';
sql "alter database rename file ''+REDO/RAC/onlinelog/group_1.282.798910783'' TO ''/u01/app/oracle/oradata/RAC/redo01a.log'' ";
sql "alter database rename file ''+REDO/RAC/onlinelog/group_2.283.798910803'' TO ''/u01/app/oracle/oradata/RAC/redo02a.log'' ";
sql "alter database rename file ''+REDO/RAC/onlinelog/group_3.284.798911213'' TO ''/u01/app/oracle/oradata/RAC/redo03a.log'' ";
sql "alter database rename file ''+REDO/RAC/onlinelog/group_4.285.798911229'' TO ''/u01/app/oracle/oradata/RAC/redo04a.log'' ";
sql "alter database rename file ''+REDO/RAC/onlinelog/group_5.286.809431083'' TO ''/u01/app/oracle/oradata/RAC/redo05a.log'' ";
sql "alter database rename file ''+REDO/RAC/onlinelog/group_6.287.809431109'' TO ''/u01/app/oracle/oradata/RAC/redo06a.log'' ";
sql "alter database rename file ''+REDO/RAC/onlinelog/group_7.288.809431135'' TO ''/u01/app/oracle/oradata/RAC/redo07a.log'' ";
sql "alter database rename file ''+REDO/RAC/onlinelog/group_8.289.809431157'' TO ''/u01/app/oracle/oradata/RAC/redo08a.log'' ";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
sql 'alter database disable block change tracking';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
release channel c10;
release channel c11;
release channel c12;
}
SQL

6 – Acompanhe o restore para ter ideia de quanto tempo e qual o percentual do restore.

set linesize 500 pagesize 2000
REM RMAN Progress
set colsep   " | "
alter session set nls_date_format='dd/mm/yy hh24:mi:ss';
select SID, START_TIME inicio,TOTALWORK total, sofar faltando, (sofar/totalwork) * 100 concluido,
((sysdate-start_time)*24*60)/(sofar/totalwork)-(sysdate-start_time)*24*60 minutos_concluir,
sysdate + TIME_REMAINING/3600/24 fim
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%';
SQL

7 – Após concluir o restore crie o spfile do init que convertemos do RAC para single instance.

SQL> shutdown immediate
SQL> startup mount
SQL> startup mount pfile='/backup/init.ora';
SQL> create spfile from pfile='/backup/init.ora';
SQL

8 – Caso necessário ajuste o local da geração dos archivelogs.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/backup/archivelog/ MANDATORY' SCOPE=BOTH;
SQL> alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile;
SQL> shutdown immediate
SQL> startup mount
SQL

9 – Abra uma aba monitorando o alertlog do banco e em outra abra abra o banco.

tail -f /u01/app/oracle/diag/rdbms/lgx/RAC/trace/alert_RAC.log
SQL> alter database open resetlogs;
SQL

10 – Verifique os arquivos da tablespace de undo e recrie de RAC para single.

SET PAGESIZE 60
SET LINESIZE 300
COLUMN name  FORMAT A30
COLUMN value FORMAT A70
COLUMN ses_mod FORMAT a10
COLUMN sys_mod FORMAT a10
COLUMN ins_mod FORMAT a10
SELECT name,value FROM v$parameter WHERE name IN ('undo_management','undo_tablespace');
 
SQL> alter system set undo_management='MANUAL' scope=spfile;
 
CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/u01/app/oracle/oradata/RAC/undotbs.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 32767M;
 
SQL> alter system set undo_management='AUTO' scope=spfile;
SQL> alter system set undo_tablespace = 'UNDOTBS' scope=both;
 
SQL> shutdown immediate
SQL> startup
SQL> DROP TABLESPACE UNDOTBS1  INCLUDING CONTENTS AND DATAFILES;
SQL

11 – Crie uma nova tablespace temporária e apague as usadas no ambiente anterior para RAC.

SQL> COLUMN property_name FORMAT A30
SQL> COLUMN property_value FORMAT A30
SQL> COLUMN description FORMAT A50
SQL> SET LINESIZE 200
SQL> SELECT * FROM database_properties WHERE property_name like '%TABLESPACE' AND property_name LIKE '%TEMP%';
 
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/RAC/temp.dbf' SIZE 1024M autoextend on next 100M maxsize 32767M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;
SQL

12 – Verifique e altere o parâmetro de cluster.

SQL> show parameter cluster_Database
SQL> show parameter cluster_database_instances
SQL> alter system set cluster_database=false scope=spfile;
SQL> alter system set cluster_database_instances=1 scope=spfile; 
 
SQL> shut immediate
SQL> startup
SQL

13 – Desative e exclua a thread 2 do grupo de redolog usada no ambiente RAC.

SQL> set lines 1000
SQL> select THREAD#, STATUS, ENABLED from v$thread;
SQL> select group# from v$log where THREAD#=2;
 
SQL> alter database disable thread 2;
 
-- Verifique os arquivos do redolog da thread 2 e drop.
-- Check thread 2 and drop redolog files.
 
SQL> select group#, (bytes/1024/1024) AS SIZE_MB,(bytes/1024/1024/1024) AS SIZE_GB, thread#,status from v$log order by group#;
 
SQL> alter database drop logfile group 3;
SQL> alter database drop logfile group 8;
SQL> alter database drop logfile group 7;
SQL> alter database drop logfile group 4;
SQL> select THREAD#, STATUS, ENABLED from v$thread;
 
   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
SQL

14 – Caso necessário ajuste os parâmetros de memoria.

SQL> show parameter memory
SQL> show parameter sga_target
SQL> show parameter sga_max_size
SQL> show parameter pga_aggregate_target
SQL> show parameter memory_max_target
SQL> show parameter memory_target 
 
SQL> ALTER SYSTEM SET sga_target=98g SCOPE=SPFILE;
SQL> ALTER SYSTEM SET sga_max_size = 98g scope=SPFILE;
SQL> ALTER SYSTEM SET pga_aggregate_target =42g  SCOPE=SPFILE;
SQL> shutdown immediate
SQL

15 – Multiplex os arquivos dos controlfiles por segurança.

cp /u01/app/oracle/oradata/RAC/current.281.798910783 /u01/app/oracle/oradata/RAC/control01.ctl
cp /u01/app/oracle/oradata/RAC/current.281.798910783 /u01/app/oracle/oradata/RAC/control02.ctl
cp /u01/app/oracle/oradata/RAC/current.281.798910783 /u01/app/oracle/oradata/RAC/control03.ctl
 
startup
alter system set control_files='/u01/app/oracle/oradata/RAC/control01.ctl','/u01/app/oracle/oradata/RAC/control02.ctl','/u01/app/oracle/oradata/RAC/control03.ctl' scope=spfile;
shutdown immediate
startup
show parameter control_files
SQL

Oracle Reference Note: How To Restore RMAN Disk backups of RAC Database to Single Instance On Another Node (Doc ID 415579.1)

Leave a Reply

Your email address will not be published. Required fields are marked *

search previous next tag category expand menu location phone mail time cart zoom edit close