Duplicando um banco de dados no mesmo servidor

Como Administrador de banco de dados Oracle, algumas vezes somos requisitados para efetuar algumas operações de cópias de dados. Entre essas operações está uma que chamamos de Duplicate. Geralmente essa operação é executada quando precisamos copiar uma base de dados de um servidor para outro servidor. No entanto, neste artigo, decidir fazer diferente: cópia a mesma base com outro SID no mesmo servidor, o que requer mais atenção, pois a qualquer momento é possível sobrescrever a base errada se não estiver atento. Sendo assim, segue o step-by-step que elaborei:

Algumas informações básicas:

Instância original: TECHDB (Porta 1522)
Instância nova: TECHDB2 (Porta 1523)
Nome do servidor:techdatabasket.bruno.com

1- Adicione uma nova entrada no TSNAMES.ora e no LISTENER.ora para a nova instância, no qual neste post chamarei TECHDB2:

TNSNAMES:


TECHDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = techdatabasket.bruno.com)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = techdb2)
    )
  )

LISTENER_TECHDB2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = techdatabasket.bruno.com)(PORT = 1523))



LISTENER:

LISTENER_TECHDB2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = techdatabasket.bruno.com)(PORT = 1523)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_TECHDB2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = techdb2)
      (ORACLE_HOME = /applications/oracle/11.2.0.4)
    )
  )

2- Crie o arquivo de inicialização (init<INSTANCENAME>.ora) e inicie o banco de dados no estado NOMOUNT:


$ cat inittechdb2.ora
*.control_files='/applications/oracle/techdb2/control01.ctl','/applications/oracle/techdb2/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='techdb2'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=techdb2XDB)'
*.event=''
*.filesystemio_options='SETALL'
*.java_jit_enabled=TRUE
*.local_listener='LISTENER_TECHDB2'
*.log_archive_format='arch%t_%s_%r.dbf'
# Convert file names to allow for different directory structure if necessary.
DB_FILE_NAME_CONVERT='/oradata/editest/','/applications/oracle/SR61572922/'
LOG_FILE_NAME_CONVERT='/oradata/editest/','/applications/oracle/SR61572922/'

3- Inicializando a base de dados em nomount:


$ export ORACLE_SID=techdb2   
$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 11 11:26:39 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>  STARTUP NOMOUNT;
ORACLE instance started.

Total System Global Area  225509376 bytes
Fixed Size                  2245024 bytes
Variable Size             167775840 bytes
Database Buffers           50331648 bytes
Redo Buffers                5156864 bytes

4- Inicializando a nova instância em outra porta, no caso 1523, (já que a instância atual está utilizando a porta 1522):
**Lembre-se no guia de segurança da Oracle não recomenda-se a porta 1521 .


LSNRCTL> start
Starting /applications/oracle/11.2.0.4/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
System parameter file is /home/oracle/net/admin/listener.ora
Log messages written to /applications/oracle/base/diag/tnslsnr/techdb2/listener_techdb2/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=techdatabasket.bruno.com)(PORT=1523)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=techdatabasket.bruno.com)(PORT=1523)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_TECHDB2
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
Start Date                11-JUL-2017 11:44:55
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /home/oracle/net/admin/listener.ora
Listener Log File         /applications/oracle/base/diag/tnslsnr/techdb2/listener_techdb2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=techdatabasket.bruno.com)(PORT=1523)))
Services Summary...
Service "techdb2" has 1 instance(s).
  Instance "techdb2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

5- Verificando o status dos dois LISTENERS do ambiente:


$ ps -ef|grep inh
  oracle 15663326        1   0   Jun 26      -  0:53 /applications/oracle/CURRENT_NET/bin/tnslsnr LISTENER_TECHDB -inherit
  oracle 37617758        1   0 11:44:54  pts/3  0:00 /applications/oracle/11.2.0.4/bin/tnslsnr LISTENER_TECHDB2 -inherit

6- Verificando o status das duas instâncias do ambiente:


$ ps -ef|grep smon
  oracle 12386328        1   0   Jun 26      -  0:28 ora_smon_techdb
  oracle 38928622        1   0 11:26:56      -  0:00 ora_smon_techdb2

7- Criacão do arquivo de senha (password file) para a nova instância:



orapwd file=/applications/oracle/11.2.0.4/dbs/orapwtechdb2 password=oracle4_U entries=10

$ cd $ORACLE_HOME
$ cd dbs
$ ls -ltr
total 339112
-rw-r--r--    1 oracle   dba            2851 May 15 2009  init.ora
-rw-r-----    1 oracle   dba              24 Feb 19 2015  lkEDITEST
-rw-r-----    1 oracle   dba            3584 Jun 26 12:25 spfiletechdb.ora
-rw-r-----    1 oracle   oinstall   86835200 Jul 10 18:25 c-2541910788-20170710-3c
-rw-r-----    1 oracle   dba            1139 Jul 11 11:19 inittechdb.ora
-rw-r--r--    1 oracle   oinstall        272 Jul 11 11:25 inittechdb2.ora
-rw-rw----    1 oracle   oinstall       1544 Jul 11 11:26 hc_techdb2.dat
-rw-r-----    1 oracle   dba        86753280 Jul 11 11:41 snapcf_techdb.f
-rw-r-----    1 oracle   dba            1536 Jul 11 11:52 orapwtechdb
-rw-rw----    1 oracle   dba            1544 Jul 11 12:05 hc_techdb.dat
$ pwd
/applications/oracle/11.2.0.4/dbs
$ orapwd file=/applications/oracle/11.2.0.4/dbs/orapwtechdb2 password=oracle4_U entries=10

$ ls -ltr
total 339120
-rw-r--r--    1 oracle   dba            2851 May 15 2009  init.ora
-rw-r-----    1 oracle   dba              24 Feb 19 2015  lkEDITEST
-rw-r-----    1 oracle   dba            3584 Jun 26 12:25 spfiletechdb.ora
-rw-r-----    1 oracle   oinstall   86835200 Jul 10 18:25 c-2541910788-20170710-3c
-rw-r-----    1 oracle   dba            1139 Jul 11 11:19 inittechdb.ora
-rw-r--r--    1 oracle   oinstall        272 Jul 11 11:25 inittechdb2.ora
-rw-rw----    1 oracle   oinstall       1544 Jul 11 11:26 hc_techdb2.dat
-rw-r-----    1 oracle   dba        86753280 Jul 11 11:41 snapcf_techdb.f
-rw-r-----    1 oracle   dba            1536 Jul 11 11:52 orapwtechdb
-rw-rw----    1 oracle   dba            1544 Jul 11 12:06 hc_editest.dat
-rw-r-----    1 oracle   oinstall       2560 Jul 11 12:07 orapwtechdb2

8- Teste de conexão para inicializar a operação de Duplicate:


$  rman target sys/oracle217U_4@techdb auxiliary sys/oracle4_U@techdb2

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jul 11 12:37:38 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TECHDB (DBID=2541910788)
connected to auxiliary database: TECHDB2 (not mounted)

RMAN>

9- Analise novamente se você está com as variáveis de ambiente configurados para a instância nova:


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> select instance_name, status from v$instance;

INSTANCE_NAME                                    STATUS
------------------------------------------------ ------------
techdb2                                         STARTED

10- Performe o Duplicate: (Note que no script abaixo eu desejo duplicar os dados da base original até uma data estipulada : 11 de Setembro ás 17 horas.


Script:

$ cat duplicate_techdb2.rcv1
run
{
  allocate auxiliary channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/TSM/techdastabasketnode/tdpo.opt)';
DUPLICATE DATABASE TO techdb2
  SPFILE
  UNTIL TIME "TO_DATE('2017-09-11 17:00:00', 'YYYY-MM-DD HH24:MI:SS')"; 
 }

Comando para executar o script via nohup:


$ nohup rman target  sys/oracle217U_4@TECHDB auxiliary sys/oracle4_U@TECHDB2  cmdfile=duplicate_techdb2.rcv1 log=duplicate_techdb2.log &amp;

11- Acompanhe a log através do arquivo duplicate_techdb2.log:


$ tail -f duplicate_techdb2.log

brunors

Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)

Duplicando um banco de dados no mesmo servidor

Related posts

Leave a Comment Cancel reply

Related posts

Oracle Database Growth Monitoring Script

PL/SQL package SYS.DBMS_BACKUP_RESTORE version is not currentPL/SQL package SYS.DBMS_RCVMAN version 19.10.00.00 is too old

Enable online status for all Oracle database datafiles

Leave a Comment Cancel reply