Loading...

Follow DBA Anderson Graf on Feedspot


Valid
or
Continue with Google
Continue with Facebook
Uma das novas features introduzidas na versão 18c do Oracle database é o ALTER SYSTEM CANCEL SQL que permite que uma instrução SQL em execução em determinada sessão seja manualmente cancelada sem que a sessão seja desconectada, o que ocorre na utilização das instruções ALTER SYSTEM KILL SESSION / DISCONNECT SESSION. 

A utilização do “CANCEL SQL” é útil quando determinada sessão está executando alguma instrução ofensora e é necessário liberação de recursos ou mesmo quando instruções SQL são executadas incorretamente. 

Sempre que uma instrução SQL é cancelada, ocorre o rollback dos dados alterados pela mesma. 

Sintaxe do comando:

ALTER SYSTEM CANCEL SQL 'SID, SERIAL#, [@INST_ID, SQL_ID]';

Caso a @INST_ID não seja especificada (opcional), a instance id da sessão conectada será utilizada;

Caso o SQL_ID não seja especificado (opcional), a instrução SQL atualmente em execução na sessão especificada será cancelada.


-- Cancelar SQL em execução em determinada sessão da instance conectada.
SQL> ALTER SYSTEM CANCEL SQL '38, 41388';

-- Cancelar SQL em execução na sessão com INST_ID = 1.
SQL> ALTER SYSTEM CANCEL SQL '38, 41388, @1';

-- Cancelar SQL específico em determinada sessão da instance conectada.
SQL> ALTER SYSTEM CANCEL SQL '38, 41388, 3tfmdd4xagv3y';

-- Cancelar SQL específico na sessão com INST_ID = 1.
SQL> ALTER SYSTEM CANCEL SQL '38, 41388, @1, 3tfmdd4xagv3y';

Exemplo:

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> select distinct sid from v$mystat;

SID
----------
38

SQL> begin
2 loop null;
3 end loop;
4 end;
5 /

Em uma nova sessão, capture as informações necessárias e execute o ALTER SYSTEM CANCEL SQL:

SQL> set lines 200
SQL> select inst_id, serial#, sql_id, status, (select sql_fulltext from v$sql sq where sq.sql_id=se.sql_id) sql_fulltext from gv$session se where sid=38;

INST_ID SERIAL# SQL_ID STATUS SQL_FULLTEXT
---------- ---------- ------------- -------- ------------------------------
1 41388 3tfmdd4xagv3y ACTIVE begin
loop null;
end loop;
end;

SQL> ALTER SYSTEM CANCEL SQL '38, 41388, @1, 3tfmdd4xagv3y';

System altered.

Voltando a sessão anterior:

SQL> begin
2 loop null;
3 end loop;
4 end;
5 /
begin
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

E a sessão continua conectada, basta executar qualquer instrução SQL.

SQL> select distinct sid from v$mystat;

SID
----------
38



Referência:

https://docs.oracle.com/en/database/oracle/oracle-database/18/newft/new-features.html#GUID-EFC832BE-DC2C-4997-8C38-8A9E27A4FFC4
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
A versão 18c do Oracle database trouxe mais algumas novidades relacionadas a utilização do SQL*PLUS, que são:





  • Alterar dinamicamente a exibição de saída para ajustar o tamanho da tela;


Agora o comando SET LINESIZE possui a opção WINDOW que ajusta automaticamente o tamanho da linha e o tamanho da página de saída para termos um ouput formatado de acordo com a largura e a altura da tela.

[oracle@localhost ~]$ sqlplus anderson/graf@pdb1

SQL*Plus: Release 18.0.0.0.0 Production on Wed Mar 21 13:46:06 2018
Version 18.1.0.0.0

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

Last Successful login time: Wed Mar 21 2018 13:44:32 -03:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0

SQL> select * from exemplo;

COD
----------
DESCRICAO
----------------------------------------------------------------------
1
row 1

2
row 2

3
row 3


COD
----------
DESCRICAO
----------------------------------------------------------------------
4
row 4

5
row 5

6
row 6


COD
----------
DESCRICAO
----------------------------------------------------------------------
7
row 7

8
row 8

9
row 9


COD
----------
DESCRICAO
----------------------------------------------------------------------
10
row 10


10 rows selected.

SQL> SET LINESIZE WINDOW
SQL> select * from exemplo;

COD DESCRICAO
---------- ----------------------------------------------------------------------
1 row 1
2 row 2
3 row 3
4 row 4
5 row 5
6 row 6
7 row 7
8 row 8
9 row 9
10 row 10

10 rows selected.


  • Exibe o sql_id da consulta executada;


O comando SET FEEDBACK também foi melhorado e agora permite exibir o sql_id da query. A nova opção SQL_ID do comando SET FEEDBACK exibe o sql_id para as instruções SQL e PL/SQL atualmente executadas. 

SQL> select count(1) from exemplo;

COUNT(1)
----------
10

SQL> SET FEEDBACK ON SQL_ID
SQL> select count(1) from exemplo;

COUNT(1)
----------
10

1 row selected.

SQL_ID: 18un1uja2917c


  • Define o número de linhas exibidas (retornadas) para uma consulta.


O comando SET ROWLIMIT permite que seja definido um limite para o número de linhas retornadas na consulta.

SQL> select * from exemplo;

COD DESCRICAO
---------- ----------------------------------------------------------------------
1 row 1
2 row 2
3 row 3
4 row 4
5 row 5
6 row 6
7 row 7
8 row 8
9 row 9
10 row 10

10 rows selected.

SQL_ID: 6vcxssbgaaumu
SQL> SET ROWLIMIT 5
SQL> select * from exemplo;

COD DESCRICAO
---------- ----------------------------------------------------------------------
1 row 1
2 row 2
3 row 3
4 row 4
5 row 5

5 rows selected. (rowlimit reached)

SQL_ID: 6vcxssbgaaumu


Referência:
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqprn/index.html#SQPRN104
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Lançado recentemente, o Oracle database 18c atualmente está disponível apenas para a Oracle Cloud e Oracle Exadata, ou seja, nada de binários para on-premises por enquanto, porém assim como muitos outros baixei o binário do 18c para Exadata (V974953-01.zip) do oracle edelivery e fiz o deploy na minha VM (Virtual Machine) com Oracle Linux 7.3 x86_64.


O deploy do binário ocorre sem problemas, porém ao tentar subir uma instancia, tanto pelo DBCA (Database Configuration Assistant) quanto por linha de comando (sqlplus) ocorre o erro ORA-12754: Feature 'startup' is disabled due to missing capability 'Runtime Environment'.


Olhando no arquivo de log do banco de dados (alertlog) é visível que ele realiza algumas checagens ao subir a instancia e como elas não são atendidas (detected : 0) o startup é abortado.

[oracle@localhost ~]$ tail -n 10 /orabin/app/oracle/diag/rdbms/cdb/cdb/trace/alert_cdb.log
Number of processor sockets in the system is 1
Shared memory segment for instance monitoring created
Capability Type : Network
capabilities requested : 1 detected : 0 Simulated : 0
Capability Type : Runtime Environment
capabilities requested : 400000FF detected : 40000000 Simulated : 0
Capability Type : Engineered Systems
capabilities requested : 7 detected : 0 Simulated : 0
Capability Type : Database Test
capabilities requested : 3 detected : 0 Simulated : 0

Para realizar o startup da instance e posterior criação da base de dados, temos alguns métodos como substituir a biblioteca libserver18.a ($ORACLE_HOME/lib/libserver18.a) com uma versão trazida da Oracle Cloud que permitirá que o DBCA suba a instance e crie o database ou podemos utilizar o parâmetro _exadata_feature_on=true nos scripts gerados pelo DBCA, método este que estarei utilizando aqui.


Com os scripts gerados pelo DBCA, vá no diretório scripts e altere todos os inits.ora encontrados, aqui foi gerado apenas um devido as escolhas realizadas na interface do DBCA porém em algumas opções podemos ter até 3 arquivos de init, logo, adicione em todos o parâmetro _exadata_feature_on=true

[oracle@localhost ~]$ cd /orabin/app/oracle/admin/cdb/scripts/
[oracle@localhost scripts]$ ls -lrt
total 56
-rw-r----- 1 oracle oinstall 1975 Mar 21 10:30 init.ora
-rwxr-xr-x 1 oracle oinstall 849 Mar 21 10:30 cdb.sh
-rw-r----- 1 oracle oinstall 1695 Mar 21 10:30 CreateDB.sql
-rw-r----- 1 oracle oinstall 404 Mar 21 10:30 CreateDBFiles.sql
-rw-r----- 1 oracle oinstall 2108 Mar 21 10:30 CreateDBCatalog.sql
-rw-r----- 1 oracle oinstall 1454 Mar 21 10:30 JServer.sql
-rw-r----- 1 oracle oinstall 1167 Mar 21 10:30 context.sql
-rw-r----- 1 oracle oinstall 1008 Mar 21 10:30 CreateClustDBViews.sql
-rw-r----- 1 oracle oinstall 1721 Mar 21 10:30 lockAccount.sql
-rw-r----- 1 oracle oinstall 758 Mar 21 10:30 postDBCreation.sql
-rw-r----- 1 oracle oinstall 93 Mar 21 10:30 PDBCreation.sql
-rw-r----- 1 oracle oinstall 798 Mar 21 10:30 plug_pdb1.sql
-rwxr-xr-x 1 oracle oinstall 901 Mar 21 10:30 cdb.sql
-rw-r----- 1 oracle oinstall 1269 Mar 21 10:30 postPDBCreation_pdb1.sql

Adicionar em todos os inits o parâmetro _exadata_feature_on=true

[oracle@localhost scripts]$ cat init.ora | grep exadata
_exadata_feature_on=true

Agora basta exportarmos as variáveis conforme abaixo e rodarmos o script .sh (neste caso cdb.sh) que tudo será criado.

[oracle@localhost scripts]$ export ORACLE_HOME=/orabin/app/oracle/product/18.0.0.0/dbhome1
[oracle@localhost scripts]$ export ORACLE_BASE=/orabin/app/oracle
[oracle@localhost scripts]$ export ORACLE_SID=cdb
[oracle@localhost scripts]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@localhost scripts]$ sh cdb.sh
You should Add this entry in the /etc/oratab: cdb:/orabin/app/oracle/product/18.0.0.0/dbhome1:Y

SQL*Plus: Release 18.0.0.0.0 Production on Wed Mar 21 10:42:01 2018
Version 18.1.0.0.0

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

Enter new password for SYS:
Enter new password for SYSTEM:

Enter password for SYS:

Connected to an idle instance.
SQL> spool /orabin/app/oracle/admin/cdb/scripts/CreateDB.log append
SQL> startup nomount pfile="/orabin/app/oracle/admin/cdb/scripts/init.ora";
ORACLE instance started.

Total System Global Area 654310184 bytes
Fixed Size 8899368 bytes
Variable Size 515899392 bytes
Database Buffers 121634816 bytes
Redo Buffers 7876608 bytes
SQL> CREATE DATABASE "cdb"
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 1024
7 DATAFILE '/orabin/app/oracle/oradata/CDB/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
8 EXTENT MANAGEMENT LOCAL
9 SYSAUX DATAFILE '/orabin/app/oracle/oradata/CDB/sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
10 SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/orabin/app/oracle/oradata/CDB/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
11 SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/orabin/app/oracle/oradata/CDB/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
12 CHARACTER SET WE8MSWIN1252
13 NATIONAL CHARACTER SET AL16UTF16
14 LOGFILE GROUP 1 ('/orabin/app/oracle/oradata/CDB/redo01.log') SIZE 200M,
15 GROUP 2 ('/orabin/app/oracle/oradata/CDB/redo02.log') SIZE 200M,
16 GROUP 3 ('/orabin/app/oracle/oradata/CDB/redo03.log') SIZE 200M
17 USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"
18 enable pluggable database
19 seed file_name_convert=('/orabin/app/oracle/oradata/CDB/system01.dbf','/orabin/app/oracle/oradata/CDB/pdbseed/system01.dbf','/orabin/app/oracle/oradata/CDB/sysaux01.dbf','/orabin/app/oracle/oradata/CDB/pdbseed/sysaux01.dbf','/orabin/app/oracle/oradata/CDB/temp01.dbf','/orabin/app/oracle/oradata/CDB/pdbseed/temp01.dbf','/orabin/app/oracle/oradata/CDB/undotbs01.dbf','/orabin/app/oracle/oradata/CDB/pdbseed/undotbs01.dbf') LOCAL UNDO ON;

Database created.

SQL> spool off
SQL> @/orabin/app/oracle/admin/cdb/scripts/CreateDBFiles.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /orabin/app/oracle/admin/cdb/scripts/CreateDBFiles.log append
SQL> CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/orabin/app/oracle/oradata/CDB/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TABLESPACE "USERS";

Database altered.

SQL> spool off
SQL> @/orabin/app/oracle/admin/cdb/scripts/CreateDBCatalog.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /orabin/app/oracle/admin/cdb/scripts/CreateDBCatalog.log append
SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> alter pluggable database pdb$seed close;

Pluggable database altered.

SQL> alter pluggable database pdb$seed open;

Pluggable database altered.

SQL> host perl /orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catcon.pl -n 1 -l /orabin/app/oracle/admin/cdb/scripts -v -b catalog -U "SYS"/"&&sysPassword" /orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catalog.sql;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/orabin/app/oracle/admin/cdb/scripts/catalog_catcon_28581.lst]

catcon::set_log_file_base_path: catcon: See [/orabin/app/oracle/admin/cdb/scripts/catalog*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/orabin/app/oracle/admin/cdb/scripts/catalog_*.lst] files for spool files, if any

catcon::catconInit2: start initializing catcon

catcon::catconInit2: finished constructing connect strings

catcon::catconInit2: start CDB-specific processing

catcon::catconInit2: finished examining instances which can be used to run scripts/SQL statements.

catcon::catconInit2: DBMS version: 18.0.0.0.0.

catcon::catconInit2: started SQL*Plus processes.

catcon::catconInit2: initialization completed successfully (2018-03-21 10:44:03)

catcon::catconExec: start executing scripts/SQL statements

catcon::catconExec_int: finished examining scripts/SQL statements to be executed.

catcon::catconExec_int: will run all scripts/statements against the Root (Container CDB$ROOT) of a CDB

catcon::log_script_execution: executing "@/orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catalog.sql" in container CDB$ROOT using process 0

catcon::catconExec_int: run all scripts/statements against remaining 1 PDBs

catcon::log_script_execution: executing "@/orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catalog.sql" in container PDB$SEED using process 0

catcon::catconExec: finished executing scripts/SQL statements

catcon::catconWrapUp: (PID=28581) about to free up all resources

catcon::catconWrapUp: done

catcon.pl: completed successfully

SQL> host perl /orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catcon.pl -n 1 -l /orabin/app/oracle/admin/cdb/scripts -v -b catproc -U "SYS"/"&&sysPassword" /orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catproc.sql;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/orabin/app/oracle/admin/cdb/scripts/catproc_catcon_28692.lst]

catcon::set_log_file_base_path: catcon: See [/orabin/app/oracle/admin/cdb/scripts/catproc*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/orabin/app/oracle/admin/cdb/scripts/catproc_*.lst] files for spool files, if any

catcon::catconInit2: start initializing catcon

catcon::catconInit2: finished constructing connect strings

catcon::catconInit2: start CDB-specific processing

catcon::catconInit2: finished examining instances which can be used to run scripts/SQL statements.

catcon::catconInit2: DBMS version: 18.0.0.0.0.

catcon::catconInit2: started SQL*Plus processes.

catcon::catconInit2: initialization completed successfully (2018-03-21 10:47:29)

catcon::catconExec: start executing scripts/SQL statements

catcon::catconExec_int: finished examining scripts/SQL statements to be executed.

catcon::catconExec_int: will run all scripts/statements against the Root (Container CDB$ROOT) of a CDB

catcon::log_script_execution: executing "@/orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catproc.sql" in container CDB$ROOT using process 0

catcon::catconExec_int: run all scripts/statements against remaining 1 PDBs

catcon::log_script_execution: executing "@/orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catproc.sql" in container PDB$SEED using process 0

catcon::catconExec: finished executing scripts/SQL statements

catcon::catconWrapUp: (PID=28692) about to free up all resources

catcon::catconWrapUp: done

catcon.pl: completed successfully

SQL> host perl /orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catcon.pl -n 1 -l /orabin/app/oracle/admin/cdb/scripts -v -b catoctk -U "SYS"/"&&sysPassword" /orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catoctk.sql;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/orabin/app/oracle/admin/cdb/scripts/catoctk_catcon_30435.lst]

catcon::set_log_file_base_path: catcon: See [/orabin/app/oracle/admin/cdb/scripts/catoctk*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/orabin/app/oracle/admin/cdb/scripts/catoctk_*.lst] files for spool files, if any

catcon::catconInit2: start initializing catcon

catcon::catconInit2: finished constructing connect strings

catcon::catconInit2: start CDB-specific processing

catcon::catconInit2: finished examining instances which can be used to run scripts/SQL statements.

catcon::catconInit2: DBMS version: 18.0.0.0.0.

catcon::catconInit2: started SQL*Plus processes.

catcon::catconInit2: initialization completed successfully (2018-03-21 11:35:23)

catcon::catconExec: start executing scripts/SQL statements

catcon::catconExec_int: finished examining scripts/SQL statements to be executed.

catcon::catconExec_int: will run all scripts/statements against the Root (Container CDB$ROOT) of a CDB

catcon::log_script_execution: executing "@/orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catoctk.sql" in container CDB$ROOT using process 0

catcon::catconExec_int: run all scripts/statements against remaining 1 PDBs

catcon::log_script_execution: executing "@/orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catoctk.sql" in container PDB$SEED using process 0

catcon::catconExec: finished executing scripts/SQL statements

catcon::catconWrapUp: (PID=30435) about to free up all resources

catcon::catconWrapUp: done

catcon.pl: completed successfully

SQL> host perl /orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catcon.pl -n 1 -l /orabin/app/oracle/admin/cdb/scripts -v -b owminst -U "SYS"/"&&sysPassword" /orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/owminst.plb;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/orabin/app/oracle/admin/cdb/scripts/owminst_catcon_30514.lst]

catcon::set_log_file_base_path: catcon: See [/orabin/app/oracle/admin/cdb/scripts/owminst*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/orabin/app/oracle/admin/cdb/scripts/owminst_*.lst] files for spool files, if any

catcon::catconInit2: start initializing catcon

catcon::catconInit2: finished constructing connect strings

catcon::catconInit2: start CDB-specific processing

catcon::catconInit2: finished examining instances which can be used to run scripts/SQL statements.

catcon::catconInit2: DBMS version: 18.0.0.0.0.

catcon::catconInit2: started SQL*Plus processes.

catcon::catconInit2: initialization completed successfully (2018-03-21 11:35:29)

catcon::catconExec: start executing scripts/SQL statements

catcon::catconExec_int: finished examining scripts/SQL statements to be executed.

catcon::catconExec_int: will run all scripts/statements against the Root (Container CDB$ROOT) of a CDB

catcon::log_script_execution: executing "@/orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/owminst.plb" in container CDB$ROOT using process 0

catcon::catconExec_int: run all scripts/statements against remaining 1 PDBs

catcon::log_script_execution: executing "@/orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/owminst.plb" in container PDB$SEED using process 0

catcon::catconExec: finished executing scripts/SQL statements

catcon::catconWrapUp: (PID=30514) about to free up all resources

catcon::catconWrapUp: done

catcon.pl: completed successfully

SQL> host perl /orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catcon.pl -n 1 -l /orabin/app/oracle/admin/cdb/scripts -v -b pupbld -u SYSTEM/&&systemPassword -U "SYS"/"&&sysPassword" /orabin/app/oracle/product/18.0.0.0/dbhome1/sqlplus/admin/pupbld.sql;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/orabin/app/oracle/admin/cdb/scripts/pupbld_catcon_30940.lst]

catcon::set_log_file_base_path: catcon: See [/orabin/app/oracle/admin/cdb/scripts/pupbld*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/orabin/app/oracle/admin/cdb/scripts/pupbld_*.lst] files for spool files, if any

catcon::catconInit2: start initializing catcon

catcon::catconInit2: finished constructing connect strings

catcon::catconInit2: start CDB-specific processing

catcon::catconInit2: finished examining instances which can be used to run scripts/SQL statements.

catcon::catconInit2: DBMS version: 18.0.0.0.0.

catcon::catconInit2: started SQL*Plus processes.

catcon::catconInit2: initialization completed successfully (2018-03-21 11:37:42)

catcon::catconExec: start executing scripts/SQL statements

catcon::catconExec_int: finished examining scripts/SQL statements to be executed.

catcon::catconExec_int: will run all scripts/statements against the Root (Container CDB$ROOT) of a CDB

catcon::log_script_execution: executing "@/orabin/app/oracle/product/18.0.0.0/dbhome1/sqlplus/admin/pupbld.sql" in container CDB$ROOT using process 0

catcon::catconExec_int: run all scripts/statements against remaining 1 PDBs

catcon::log_script_execution: executing "@/orabin/app/oracle/product/18.0.0.0/dbhome1/sqlplus/admin/pupbld.sql" in container PDB$SEED using process 0

catcon::catconExec: finished executing scripts/SQL statements

catcon::catconWrapUp: (PID=30940) about to free up all resources

catcon::catconWrapUp: done

catcon.pl: completed successfully

SQL> host perl /orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catcon.pl -n 1 -l /orabin/app/oracle/admin/cdb/scripts -v -b pupdel -u SYS/&&sysPassword -U "SYS"/"&&sysPassword" /orabin/app/oracle/product/18.0.0.0/dbhome1/sqlplus/admin/pupdel.sql;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/orabin/app/oracle/admin/cdb/scripts/pupdel_catcon_31019.lst]

catcon::set_log_file_base_path: catcon: See [/orabin/app/oracle/admin/cdb/scripts/pupdel*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/orabin/app/oracle/admin/cdb/scripts/pupdel_*.lst] files for spool files, if any

catcon::catconInit2: start initializing catcon

catcon::catconInit2: finished constructing connect strings

catcon::catconInit2: start CDB-specific processing

catcon::catconInit2: finished examining instances which can be used to run scripts/SQL statements.

catcon::catconInit2: DBMS version: 18.0.0.0.0.

catcon::catconInit2: started SQL*Plus processes.

catcon::catconInit2: initialization completed successfully (2018-03-21 11:37:44)

catcon::catconExec: start executing scripts/SQL statements

catcon::catconExec_int: finished examining scripts/SQL statements to be executed.

catcon::catconExec_int: will run all scripts/statements against the Root (Container CDB$ROOT) of a CDB

catcon::log_script_execution: executing "@/orabin/app/oracle/product/18.0.0.0/dbhome1/sqlplus/admin/pupdel.sql" in container CDB$ROOT using process 0

catcon::catconExec_int: run all scripts/statements against remaining 1 PDBs

catcon::log_script_execution: executing "@/orabin/app/oracle/product/18.0.0.0/dbhome1/sqlplus/admin/pupdel.sql" in container PDB$SEED using process 0

catcon::catconExec: finished executing scripts/SQL statements

catcon::catconWrapUp: (PID=31019) about to free up all resources

catcon::catconWrapUp: done

catcon.pl: completed successfully

SQL> connect "SYSTEM"/"&&systemPassword"
Connected.
SQL> set echo on
SQL> spool /orabin/app/oracle/admin/cdb/scripts/sqlPlusHelp.log append
SQL> host perl /orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catcon.pl -n 1 -l /orabin/app/oracle/admin/cdb/scripts -v -b hlpbld -u SYSTEM/&&systemPassword -U "SYS"/"&&sysPassword" -a 1 /orabin/app/oracle/product/18.0.0.0/dbhome1/sqlplus/admin/help/hlpbld.sql 1helpus.sql;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/orabin/app/oracle/admin/cdb/scripts/hlpbld_catcon_31099.lst]

catcon::set_log_file_base_path: catcon: See [/orabin/app/oracle/admin/cdb/scripts/hlpbld*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/orabin/app/oracle/admin/cdb/scripts/hlpbld_*.lst] files for spool files, if any

catcon::catconInit2: start initializing catcon

catcon::catconInit2: finished constructing connect strings

catcon::catconInit2: start CDB-specific processing

catcon::catconInit2: finished examining instances which can be used to run scripts/SQL statements.

catcon::catconInit2: DBMS version: 18.0.0.0.0.

catcon::catconInit2: started SQL*Plus processes.

catcon::catconInit2: initialization completed successfully (2018-03-21 11:37:46)

catcon::catconExec: start executing scripts/SQL statements

catcon::catconExec_int: finished examining scripts/SQL statements to be executed.

catcon::catconExec_int: will run all scripts/statements against the Root (Container CDB$ROOT) of a CDB

catcon::log_script_execution: executing "@/orabin/app/oracle/product/18.0.0.0/dbhome1/sqlplus/admin/help/hlpbld.sql #helpus.sql#" in container CDB$ROOT using process 0

catcon::catconExec_int: run all scripts/statements against remaining 1 PDBs

catcon::log_script_execution: executing "@/orabin/app/oracle/product/18.0.0.0/dbhome1/sqlplus/admin/help/hlpbld.sql #helpus.sql#" in container PDB$SEED using process 0

catcon::catconExec: finished executing scripts/SQL statements

catcon::catconWrapUp: (PID=31099) about to free up all resources

catcon::catconWrapUp: done

catcon.pl: completed successfully

SQL> spool off
SQL> spool off
not spooling currently
SQL> @/orabin/app/oracle/admin/cdb/scripts/JServer.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /orabin/app/oracle/admin/cdb/scripts/JServer.log append
SQL> host perl /orabin/app/oracle/product/18.0.0.0/dbhome1/rdbms/admin/catcon.pl -n 1 -l /orabin/app/oracle/admin/cdb/scripts -v -b initjvm -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" /orabin/app/oracle/product/18.0.0.0/dbhome1/javavm/install/initjvm.sql;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/orabin/app/oracle/admin/cdb/scripts/initjvm_catcon_31187.lst]
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
O Oracle Clusterware possui vários componentes essenciais para seu funcionamento, dentre eles o OCR e OLR.

O Oracle Cluster Registry (OCR) é responsável por gerenciar as configurações e recursos do Oracle Clusterware e do Oracle RAC, como lista de nós do cluster, instancias de bancode dados, serviços entre outros.

O Oracle Local Registry (OLR) foi introduzido na versão 11gR2 do Grid Infrastructure e diferentemente do OCR ele não é compartilhado entre os nodes, cada node possui seu OLR contendo especificações locais requeridas pelo OHASD (Oracle High Availability Services).

No intervalo de 1 semana, devido instabilidades no storage, precisei restaurar ambos para disponibilizar novamente o ambiente, desta forma compartilho abaixo procedimentos realizados:

Ao executar o ocrcheck, que exibe informações relacionadas ao OCR como tamanho, versão, localização e status, percebe-se que ao final ocorre o erro PROT-602 informando falha na recuperação dos dados do cluster registry (OCR).


[root@srvprd01 oraagent_oracle]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 5860
Available space (kbytes) : 256260
ID : 84391272
Device/File Name : +OCR
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

PROT-602: Failed to retrieve data from the cluster registry

O backup do OCR, além de poder ser feito manualmente (ocrconfig -manualbackup e ocrconfig -export) ainda é gerado de forma automática a cada 4 horas pelo CRSD (Cluster Ready Services Daemon) cuja retenção segue as seguintes políticas:

  • Retém os últimos 3 backups do OCR;
  • Retém 1 backup diário;
  • Retém 1 backup semanal.

[root@srvprd01 oracle]# ocrconfig -showbackup

srvprd01 2018/02/26 14:20:00 /oracle/GRID/11204/cdata/clusterprd/backup00.ocr

srvprd01 2018/02/26 10:19:59 /oracle/GRID/11204/cdata/clusterprd/backup01.ocr

srvprd01 2018/02/26 06:19:58 /oracle/GRID/11204/cdata/clusterprd/backup02.ocr

srvprd01 2018/02/25 10:19:55 /oracle/GRID/11204/cdata/clusterprd/day.ocr

srvprd01 2018/02/07 23:25:56 /oracle/GRID/11204/cdata/clusterprd/week.ocr
PROT-25: Manual backups for the Oracle Cluster Registry are not available

Para realizar o restore do OCR primeiramente é preciso parar o Clusterware em execução e subi-lo em modo exclusivo em um dos nodes:

[root@srvprd01 oracle]# crsctl stop crs
....
....
[root@srvprd01 oracle]# crsctl start crs -excl -nocrs
CRS-4123: Oracle High Availability Services has been started.
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'srvprd01'
CRS-2677: Stop of 'ora.drivers.acfs' on 'srvprd01' succeeded
CRS-2672: Attempting to start 'ora.mdnsd' on 'srvprd01'
CRS-2676: Start of 'ora.mdnsd' on 'srvprd01' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'srvprd01'
CRS-2676: Start of 'ora.gpnpd' on 'srvprd01' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'srvprd01'
CRS-2672: Attempting to start 'ora.gipcd' on 'srvprd01'
CRS-2676: Start of 'ora.cssdmonitor' on 'srvprd01' succeeded
CRS-2676: Start of 'ora.gipcd' on 'srvprd01' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'srvprd01'
CRS-2672: Attempting to start 'ora.diskmon' on 'srvprd01'
CRS-2676: Start of 'ora.diskmon' on 'srvprd01' succeeded
CRS-2676: Start of 'ora.cssd' on 'srvprd01' succeeded
CRS-2672: Attempting to start 'ora.drivers.acfs' on 'srvprd01'
CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'srvprd01'
CRS-2672: Attempting to start 'ora.ctssd' on 'srvprd01'
CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'srvprd01' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'srvprd01'
CRS-2676: Start of 'ora.drivers.acfs' on 'srvprd01' succeeded
CRS-2676: Start of 'ora.ctssd' on 'srvprd01' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'srvprd01' succeeded
CRS-2679: Attempting to clean 'ora.asm' on 'srvprd01'
CRS-2681: Clean of 'ora.asm' on 'srvprd01' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'srvprd01'
CRS-2676: Start of 'ora.asm' on 'srvprd01' succeeded

A opção -nocrs faz com que o processo CRSD e OCR não iniciem junto com os demais processos do Oracle Clusteware.

Executando o restore físico do OCR utilizando o último backup automático gerado:

[root@srvprd01 oracle]# ocrconfig -restore /oracle/GRID/11204/cdata/clusterprd/backup00.ocr
[root@srvprd01 oracle]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 5860
Available space (kbytes) : 256260
ID : 84391272
Device/File Name : +OCR
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

Conforme retorno do ocrcheck acima já é visível que não temos mais problemas com o OCR, basta parar agora o CRS que estava em modo exclusivo e iniciá-lo normalmente. 

[root@srvprd01 oracle]# crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'srvprd01'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'srvprd01'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'srvprd01'
CRS-2673: Attempting to stop 'ora.ctssd' on 'srvprd01'
CRS-2673: Attempting to stop 'ora.asm' on 'srvprd01'
CRS-2677: Stop of 'ora.mdnsd' on 'srvprd01' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'srvprd01' succeeded
CRS-2677: Stop of 'ora.drivers.acfs' on 'srvprd01' succeeded
CRS-2677: Stop of 'ora.asm' on 'srvprd01' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'srvprd01'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'srvprd01' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'srvprd01'
CRS-2677: Stop of 'ora.cssd' on 'srvprd01' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'srvprd01'
CRS-2677: Stop of 'ora.gipcd' on 'srvprd01' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'srvprd01'
CRS-2677: Stop of 'ora.gpnpd' on 'srvprd01' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'srvprd01' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@srvprd01 oracle]#
[root@srvprd01 oracle]#
[root@srvprd01 oracle]#
[root@srvprd01 oracle]#
[root@srvprd01 oracle]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.

No caso do OLR, ao tentar subir o HAS obtive retorno de falha.

[root@srvprd01 ~]# crsctl start has
CRS-4124: Oracle High Availability Services startup failed.
CRS-4000: Command Start failed, or completed with errors.

Olhando para o arquivo de log abaixo é visível várias tentativas de startup do OLR no servidor, porém falhas (erro no final das tentativas).

[root@srvprd01 srvprd01]# pwd
/oracle/GRID/11204/log/srvprd01
[root@srvprd01 srvprd01]# tail -f alertsrvprd01.log
....
2018-03-05 20:17:59.768:
[ohasd(34126)]CRS-2112:The OLR service started on node srvprd01.
2018-03-05 20:18:00.014:
[ohasd(34149)]CRS-2112:The OLR service started on node srvprd01.
2018-03-05 20:18:00.261:
[ohasd(34171)]CRS-2112:The OLR service started on node srvprd01.
2018-03-05 20:18:00.507:
[ohasd(34208)]CRS-2112:The OLR service started on node srvprd01.
2018-03-05 20:18:00.754:
[ohasd(34230)]CRS-2112:The OLR service started on node srvprd01.
2018-03-05 20:18:01.003:
[ohasd(34263)]CRS-2112:The OLR service started on node srvprd01.
[client(34291)]CRS-10001:CRS-10132: No msg for has:crs-10132 [10][60]

O OLR, diferente do OCR, não possuí backups automáticos depois que o GI (Grid Infrastructure) é configurado, apenas backups manuais podem ser feitos (ocrconfig -local -manualbackup). Os únicos backups do OLR realizados "automaticamente" são durante a configuração (instalação) do GI e durante seu upgrade (se ocorrer algum).

Para visualizar os backups do OLR basta utilizar a mesma sintaxe do showbackup do OCR acrescentando o parâmetro -local (pelo fato do OLR ser local de cada servidor). 

[root@srvprd01 ~]# $GI_HOME/bin/ocrconfig -local -showbackup
PROTL-25: Manual backups for the Oracle Local Registry are not available

Pelo retorno nenhum backup manual do OLR está sendo localizado, olhando no node2 podemos ver que retorna um arquivo de backup (realizado durante a instalação do GI), porém este backup é do OLR do node2 e não do node1, logo não devemos utilizá-lo.

[root@srvprd02 ~]# ocrconfig -local -showbackup

srvprd02 2015/02/18 10:49:06 /oracle/GRID/11204/cdata/srvprd02/backup_20150218_104906.olr

A localização dos backups do OLR em cluster ficam em:

GI Cluster: <GI_HOME>/cdata/<hostname>/backup*.olr

Desta forma, voltando ao node1, podemos ver que existe um arquivo de backup dentro deste diretório:

[root@srvprd01 srvprd01]# ls -lrt
total 6660
-rw------- 1 root root 6807552 Feb 18 2015 backup_20150218_104154.olr

A localização do OLR em cluster, fica um diretório antes do seu backup e pode ser identificado através do arquivos '/etc/oracle/olr.loc' ou '/var/opt/oracle/olr.loc', dependendo da plataforma.

[root@srvprd01 cdata]# cat /etc/oracle/olr.loc
olrconfig_loc=/oracle/GRID/11204/cdata/srvprd01.olr
crs_home=/oracle/GRID/11204

Para realizar o restore do OLR é preciso verificar se não existe qualquer serviço do grid iniciado, para isto basta utilizar o comando abaixo:

[root@srvprd01 srvprd01]# ps -ef| grep ohasd.bin
root 44843 31674 0 20:33 pts/1 00:00:00 grep ohasd.bin

Caso algum processo seja retornado indicando que o serviço está ativo basta para-lo

# <GI_HOME>/bin/crsctl stop crs -f 

Uma vez parado, pode-se proceder com o restore do OLR e startup:


[root@srvprd01 cdata]# $GI_HOME/bin/ocrconfig -local -restore /oracle/GRID/11204/cdata/srvprd01/backup_20150218_104154.olr

[root@srvprd01 oracle]# crsctl start crs
CRS-4123: Oracle High Availability Services has been started

Referências:

https://docs.oracle.com/cd/E11882_01/rac.112/e41959/votocr.htm#CWADD91099
How to backup or restore OLR in 11.2/12c Grid Infrastructure (Doc ID 1193643.1)
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
O OMS (Oracle Management Service) e OMA (Oracle Management Agent) estavam configurados e funcionando no servidor srv-old porém houve a necessidade de alterar o hostname desta máquina para srv-new, busquei documentações no MOS (My Oracle Support), Oracle Help Center e até em outros Blogs porém nenhum tratava de ponta-a-ponta o cenário que eu precisava, todos que encontrei abordavam como migrar de fato para um novo servidor com a instalação de um novo OMS (software only) e recover das configurações do antigo, desta forma compartilho abaixo os procedimentos realizados para deixar o OMS e OMA funcionando no mesmo servidor após a mudança do hostname.

Atual hostname:

[oracle@srv-old ~]$ hostname
srv-old

Status do OMS:

[oracle@srv-old ~]$ $OMS_HOME/bin/emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up
BI Publisher Server is Up

Para realizar o export das configurações do OMS que são fundamentais para recuperação posterior o OMS precisa estar ativo (is Up) conforme demonstrado acima.

Exportando as configurações do OMS:

[oracle@srv-old ~]$ $OMS_HOME/bin/emctl exportconfig oms -dir /tmp/bkpoem
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
Informe a Senha Raiz do Enterprise Manager (SYSMAN):
ExportConfig iniciado...
Backup directory is /tmp/bkpoem
A máquina é o host do Servidor de Administração. Executando o backup do Servidor de Administração...
Exportando as propriedades emoms...
Exportando propriedades seguras...

A exportação determinou que o OMS não está na frente de
um SLB. O nome do host local Não foi exportado.
Os dados exportados pode ser importados em qualquer host, mas
a nova proteção de todos os agentes será necessária. Consulte
o Guia de Configuração Avançada do EM para obter mais ndetalhes.

Exportando configuração de módulos plugáveis...
Testando arquivo compactado...
O backup foi gravado no arquivo: /tmp/bkpoem/opf_ADMIN_20180226_170802.bka

O arquivo de exportação contém dados confidenciais.
Você deve mantê-los seguros.

ExportConfig completed successfully!

Durante a execução do exportconfig será solicitado o diretório onde será gerado o backup, no exemplo foi especificado /tmp/bkpoem

Ao termino do processo será apresentado o nome do arquivo gerado abaixo deste diretório - "O backup foi gravado no arquivo: /tmp/bkpoem/opf_ADMIN_20180226_170802.bka"

Ainda com o hostname antigo e com o OMS ativo execute o comando omsca delete -full, isso resultará na desconfiguração e exclusão do OMS onde o servidor de administração está configurado:

[oracle@srv-old ~]$ $OMS_HOME/bin/omsca delete -full -REP_CONN_STR '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=srv-old)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=EMPDBREPOS)))'
Oracle Enterprise Manager Cloud Control 13c Release 13.2.0.0.0
Copyright (c) 1996, 2016, Oracle. All rights reserved.

log4j:WARN No appenders could be found for logger (emctl.secure.oms.SecureOMSCmds).
log4j:WARN Please initialize the log4j system properly.
Encontrado apenas um OMS : EMGC_OMS1
Deseja realmente excluir o OMS (S/N):S
Exclusão não confirmada.
Informe o Nome OMS:EMGC_OMS1
Informe o nome do usuário do Servidor de Administração:weblogic
Informe a senha do usuário do Servidor de Administração:
Informe a senha de usuário do banco de dados do Repositário:
Informe a senha de SYS:
Deseja realmente excluir o OMS (S/N):S

Algumas interações foram necessárias durante a execução do processo acima, como confirmações da operação, nome do OMS, usuários e senhas.

Baixando todos os serviços do OMS:

[oracle@srv-old ~]$ $OMS_HOME/bin/emctl stop oms -all
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
Stopping Oracle Management Server...
WebTier Successfully Stopped
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down
Stopping BI Publisher Server...
BI Publisher Server Successfully Stopped
AdminServer Successfully Stopped
BI Publisher Server is Down

Baixando o agent:

[oracle@srv-old ~]$ $AGENT_HOME/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.

Realizando a mudança do hostname para "srv-new":

[root@srv-old ~]# echo srv-new > /proc/sys/kernel/hostname
[root@srv-old ~]# su - oracle
[oracle@srv-new ~]$ hostname
srv-new

Caso o listener e tnsnames estejam configurados com hostname em vez de IP proceda com o ajuste nos mesmos:   

[oracle@srv-new ~]$ vi $ORACLE_HOME/network/admin/listener.ora
[oracle@srv-new ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora

Como o OMR (Oracle Management Repository) está neste mesmo servidor que agora é "srv-new" e nas configurações do OMS ainda está sendo apontado para o antigo nome do servidor "srv-old" será adicionado temporariamente no /etc/hosts uma nova entrada com o ip do servidor e com o hostname antigo conforme abaixo:

[oracle@srv-new ~]$ cat /etc/hosts | grep srv-old
192.168.0.35 srv-old

Realizando um teste pingando o srv-old e srv-new:

[oracle@srv-new ~]$ ping srv-old
PING srv-old (192.168.0.35) 56(84) bytes of data.
64 bytes from srv-old (192.168.0.35): icmp_seq=1 ttl=64 time=0.031 ms
64 bytes from srv-old (192.168.0.35): icmp_seq=2 ttl=64 time=0.036 ms
^C
--- srv-old ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 999ms
rtt min/avg/max/mdev = 0.031/0.033/0.036/0.006 ms
[oracle@srv-new ~]$ ping srv-new
PING srv-new (192.168.0.35) 56(84) bytes of data.
64 bytes from srv-old (192.168.0.35): icmp_seq=1 ttl=64 time=0.029 ms
^C
--- srv-new ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 0.029/0.029/0.029/0.000 ms

Anteriormente já baixamos o OMS e OMA porém para garantir que não tenha ficado nenhum serviço ativo podemos executar o comando a seguir que irá matar qualquer processo que contenha as strings Middleware e gc_inst (caso seja necessário ajuste as strings de acordo com seu middleware e instance homes). 

[oracle@srv-new ~]$ ps -ef | grep -i -P "(Middleware|gc_inst)" | grep -v grep | awk '{print $2}' | xargs kill -9

Apesar de já desconfigurado, ainda temos resquícios da instalação antiga do OMS e que não são mais necessárias, desta forma vamos movimentar o arquivo de properties e o diretório gc_inst conforme abaixo. Caso prefira, ambos podem ser excluídos.

[oracle@srv-new ~]$ mv $OMS_HOME/sysman/config/emInstanceMapping.properties $OMS_HOME/sysman/config/emInstanceMapping.properties.old
[oracle@srv-new ~]$ mv $OMS_HOME/../gc_inst $OMS_HOME/../gc_inst_old
[oracle@srv-new ~]$ mkdir $OMS_HOME/../gc_inst

Realizando o recover das configurações do OMS utilizando o arquivo de backup gerado anteriormente e especificando o novo hostname "srv-new" nos parâmetros: 

[oracle@srv-new ~]$ $OMS_HOME/bin/omsca recover -NOSTART -BACKUP_FILE /tmp/bkpoem/opf_ADMIN_20180226_170802.bka -AS_HOST srv-new -EM_INSTANCE_HOST srv-new -AS -MS
Oracle Enterprise Manager Cloud Control 13c Release 13.2.0.0.0
Copyright (c) 1996, 2016, Oracle. All rights reserved.

log4j:WARN No appenders could be found for logger (emctl.secure.oms.SecureOMSCmds).
log4j:WARN Please initialize the log4j system properly.
Recovered Instance Host name used from command line argument : srv-new
OS check passed.
OMS version check passed.
Performing Admin Server Recovery...
Retrieved Admin Server template.
Nome do Host da Instância de Origem para o qual a configuração foi exportada : srv-old
Nome do instHost recuperado do nome do host local: srv-old
Populated install params from backup...
Informe a senha do usuário do Servidor de Administração:
Confirmar Senha:
Informe a Senha do Gerenciador de Nós:
Confirmar Senha:
Informe a senha de usuário do banco de dados do Repositório:
Informe a senha de Registro do Agente:
Confirmar Senha:
Executando verificações de pré-requisito ......
Verificações de pré-requisitos concluída com sucesso

Checking Plugin software bits
Proceed to recovery
Setting up domain from template...
Setup EM infrastructure succeeded!
Admin Server recovered from backup.
Now performing cleanup of OMS EMGC_OMS1...
Now launching DeleteOMS...
OMS Excluído com sucesso

Delete finished successfully
Now launching AddOMS...
Configuração da infraestrutura do EM concluída com sucesso.

Executando operações de pré-implantação ......
A Pré-Implantação do EM foi concluída com sucesso.

Implantando o EM...
Creating Data source first !!
Implantação do EM concluída com sucesso.

Configurando camada web ......
Configuração de Camada Web concluída com sucesso.

Importing OMS configuration from recovery file...

If you have software library configured
please make sure it is functional and accessible
from this OMS by visiting:
Setup->Provisioning and Patching->Software Library

Importando configuração de módulos plugáveis...
Protegendo OMS ......
O adaptador já existe: emgc_USER
Adaptador criado com sucesso: emgc_GROUP
As operações Pós "Configuração de Implantação e de Repositório" foram concluídas com sucesso.

Executando operações de pós-implantação ....
Total 0 errors, 85 warnings. 0 entities imported.
pluginID:oracle.sysman.core
Done with csg import
pluginID:oracle.sysman.core
Done with csg import
pluginID:oracle.sysman.core
Done with csg import
pluginID:oracle.sysman.core
Done with csg import
No logging has been configured and default agent logging support is unavailable.
Operações de pás-implantação concluídas com sucesso.

Configuração do EM concluída com êxito.
O URL do EM é:https://srv-new:7803/em

Add OMS finished successfully
Recovery of server EMGC_OMS1 completed successfully
OMSCA Recover completed successfully

Finalizado o processo de recover e reconfiguração do OMS podemos subi-lo novamente:

[oracle@srv-new ~]$ $OMS_HOME/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
JVMD Engine is Up
Starting BI Publisher Server ...
BI Publisher Server Successfully Started
BI Publisher Server is Up

Ajustado as configurações do repositório com o novo hostname:

[oracle@srv-new ~]$ $OMS_HOME/bin/emctl config oms -store_repos_details -repos_conndesc "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=srv-new)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=EMPDBREPOS)))" -repos_user sysman
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
Enter Repository User's Password :
Successfully updated datasources and stored repository details in Credential Store.
If there are multiple OMSs in this environment, run this store_repos_details command on all of them.
And finally, restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.
It is also necessary to restart the BI Publisher Managed Server.

Conforme descrito no termino do processo vamos proceder com o stop do OMS:

[oracle@srv-new ~]$ $OMS_HOME/bin/emctl stop oms -all
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
Stopping Oracle Management Server...
WebTier Successfully Stopped
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down
Stopping BI Publisher Server...
BI Publisher Server Successfully Stopped
AdminServer Successfully Stopped
BI Publisher Server is Down

Vamos remover a entrada temporária no /etc/hosts pois agora o repositório já aponta para o novo hostname.

[root@srv-new ~]# vi /etc/hosts

Iniciando novamente o OMS:

[oracle@srv-new ~]$ $OMS_HOME/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
JVMD Engine is Up
Starting BI Publisher Server ...
BI Publisher Server Successfully Started
BI Publisher Server is Up

Verificando detalhes do OMS onde já traz o novo hostname "srv-new":

[oracle@srv-new ~]$ $OMS_HOME/bin/emctl status oms -details
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host : srv-new
HTTP Console Port : 7788
HTTPS Console Port : 7803
HTTP Upload Port : 4889
HTTPS Upload Port : 4903
EM Instance Home : /orabin/app/oracle/product/13.2.0.0/EM/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /orabin/app/oracle/product/13.2.0.0/EM/gc_inst/em/EMGC_OMS1/sysman/log
OMS is not configured with SLB or virtual hostname
Agent Upload is locked.
OMS Console is locked.
Active CA ID: 1
Console URL: https://srv-new:7803/em
Upload URL: https://srv-new:4903/empbs/upload

WLS Domain Information
Domain Name : GCDomain
Admin Server Host : srv-new
Admin Server HTTPS Port: 7102
Admin Server is RUNNING

Oracle Management Server Information
Managed Server Instance Name: EMGC_OMS1
Oracle Management Server Instance Host: srv-new
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up

BI Publisher Server Information
BI Publisher Managed Server Name: BIP
BI Publisher Server is Up

BI Publisher HTTP Managed Server Port : 9701
BI Publisher HTTPS Managed Server Port : 9803
BI Publisher HTTP OHS Port : 9788
BI Publisher HTTPS OHS Port : 9851
BI Publisher is locked.
BI Publisher Server named 'BIP' running at URL: https://srv-new:9851/xmlpserver
BI Publisher Server Logs: /orabin/app/oracle/product/13.2.0.0/EM/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/
BI Publisher Log : /orabin/app/oracle/product/13.2.0.0/EM/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/bipublisher/bipublisher.log

Finalizado o OMS vamos ao OMA, para configurá-lo/reapontá-lo para o novo OMS hostname e preciso remover a atual instalação e realizar uma nova.

Parando o agent (já estava parado - foi parado anteriormente)

[oracle@srv-new ~]$ $AGENT_HOME/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved.
Agent is Not Running

Removendo a instance do agent e recriando o diretório:

[oracle@srv-new ~]$ rm -rf $AGENT_HOME/../agent_inst
[oracle@srv-new ~]$ echo $AGENT_HOME
/orabin/app/oracle/product/13.2.0.0/EM/agent/agent_inst
[oracle@srv-new ~]$ mkdir /orabin/app/oracle/product/13.2.0.0/EM/agent/agent_inst

Realizando o novo deploy, lembre-se de ajustar conforme detalhes do ambiente como OMS_HOST com o novo hostname do OMS, portas do agent e upload, etc. 

No parâmetro ORACLE_HOSTNAME especifique o nome do servidor que você está, neste caso estou no mesmo do OMS por isso é "srv-new", caso fosse outro servidor especifique o hostname dele (este nome será visível nas páginas do EMCC).

[oracle@srv-new ~]$ $AGENT_HOME/../agent_13.2.0.0.0/sysman/install/agentDeploy.sh AGENT_BASE_DIR=/orabin/app/oracle/product/13.2.0.0/EM/agent OMS_HOST=srv-new AGENT_PORT=3872 EM_UPLOAD_PORT=4903 AGENT_REGISTRATION_PASSWORD=senha123 AGENT_INSTANCE_HOME=/orabin/app/oracle/product/13.2.0.0/EM/agent/agent_inst ORACLE_HOSTNAME=srv-new -configOnly

Validating the OMS Host and Port completed.
AGENT_BASE_DIR=/orabin/app/oracle/product/13.2.0.0/EM/agent
OMS_HOST=srv-new
AGENT_PORT=3872
EM_UPLOAD_PORT=4903
AGENT_REGISTRATION_PASSWORD=senha123
AGENT_INSTANCE_HOME=/orabin/app/oracle/product/13.2.0.0/EM/agent/agent_inst
-configOnly
/orabin/app/oracle/product/13.2.0.0/EM/agent/agent_13.2.0.0.0/oracle_common/jdk/jre/bin/java -classpath /orabin/app/oracle/product/13.2.0.0/EM/agent/agent_13.2.0.0.0/oui/modules/OraInstaller.jar:/orabin/app/oracle/product/13.2.0.0/EM/agent/agent_13.2.0.0.0/oui/modules/emCfg.jar:/orabin/app/oracle/product/13.2.0.0/EM/agent/agent_13.2.0.0.0/jlib/agentInstaller.jar oracle.sysman.agent.installer.AgentFreshInstaller /orabin/app/oracle/product/13.2.0.0/EM/agent/agent_13.2.0.0.0 /orabin/app/oracle/product/13.2.0.0/EM/agent/agent_inst /orabin/app/oracle/product/13.2.0.0/EM/agent RESPONSE_FILE=/orabin/app/oracle/product/13.2.0.0/EM/agent/agentInstall.rsp $args
The AgentFreshInstaller is starting now
Oraclehome : ../orabin/app/oracle/product/13.2.0.0/EM/agent/agent_13.2.0.0.0
InstanceHome : /orabin/app/oracle/product/13.2.0.0/EM/agent/agent_inst
Agent Base Directory : /orabin/app/oracle/product/13.2.0.0/EM/agent
The oraclehome /orabin/app/oracle/product/13.2.0.0/EM/agent/agent_13.2.0.0.0
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: /orabin/app/oracle/product/13.2.0.0/EM/agent/agent_13.2.0.0.0
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: /orabin/app/oracle/product/13.2.0.0/EM/agent/agent_inst
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: /orabin/app/oracle/product/13.2.0.0/EM/agent
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: RESPONSE_FILE=/orabin/app/oracle/product/13.2.0.0/EM/agent/agentInstall.rsp
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: AGENT_BASE_DIR=/orabin/app/oracle/product/13.2.0.0/EM/agent
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: OMS_HOST=srv-new
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: AGENT_PORT=3872
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: EM_UPLOAD_PORT=4903
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: AGENT_INSTANCE_HOME=/orabin/app/oracle/product/13.2.0.0/EM/agent/agent_inst
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: ORACLE_HOSTNAME=srv-new
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: -configOnly
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: ORACLE_HOME=/orabin/app/oracle/product/13.2.0.0/EM/agent/agent_13.2.0.0.0
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: AGENT_PORT=3872
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: EM_UPLOAD_PORT=4903
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: OMS_HOST=srv-new
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: AGENT_INSTANCE_HOME=/orabin/app/oracle/product/13.2.0.0/EM/agent/agent_inst
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: AGENT_BASE_DIR=/orabin/app/oracle/product/13.2.0.0/EM/agent
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: b_doDiscovery=true
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: START_AGENT=true
startAgent is:true
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: b_forceConfigure=false
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: b_sharedAgents=false
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: b_secureAgent=true
seci is :true
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: b_noUpgrade=true
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: b_agentupgrade=false
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: PLUGIN_RSPFILE=/orabin/app/oracle/product/13.2.0.0/EM/agent/agent_inst/../agent_13.2.0.0.0/sysman/install/plugins.txt
Fev 27, 2018 11:50:44 AM oracle.sysman.agent.installer.AgentInstaller parseResponseFile
INFORMAÇÕES: ORACLE_HOSTNAME=srv-new
Writing the following contents into /orabin/app/oracle/product/13.2.0.0/EM/agent/agent_13.2.0.0.0/install/oragchomelist
/orabin/app/oracle/product/13.2.0.0/EM/agent/agent_13.2.0.0.0:/orabin/app/oracle/product/13.2.0.0/EM/agent/agent_inst
Creating directory /orabin/app/oracle/product/13.2.0.0/EM/agent/agent_13.2.0.0.0/install/tmp completed successfully.
File /etc/oragchomelist exists.
File /etc/oragchomelist is writable.
Index :-1 for line : /orabin/app/oracle/product/13.2.0.0/EM/middleware
Index :0 for line : /orabin/app/oracle/product/13.2.0.0/EM/agent/agent_13.2.0.0.0:/orabin/app/oracle/product/13.2.0.0/EM/agent/agent_inst
Overwriting the contents since oracle home:/orabin/app/oracle/product/13.2.0.0/EM/agent/agent_13.2.0.0.0 entry already exists.
The value of chainInstall : false forceConfigure : false skipValidation : false
Validando o host e a porta de oms com o url: https://srv-new:4903/empbs/genwallet
Validando o host e a porta de oms com o url: http://srv-new:4903/empbs/genwallet
The status is 0
Validated the oms host and port :- srv-new----4903
Getting Inet Addresses for host srv-new
SEVERE:
Executando pré-requisitos de instalação do agente...
Validated the..
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Se você não possui drive de fita mas quer simular um backup para fita ou mesmo realizar testes quando dizem que o problema está no RMAN e não no produto de terceiros como NetBackup, NetWorker, Data Protector e TSM, por exemplo, utilize a API oracle.disksbt que a Oracle fornece para simular o uso da MML (Media Management Library) fazendo com o que um diretório do servidor seja tratado igual a uma unidade de fita.

A API pode ser especificada na alocação do canal ou configurada nas parametrizações do RMAN onde será atribuída automaticamente ao canal quando um backup SBT_TAPE for executado inibindo a necessidade de alocar o canal com os parâmetros manualmente.

SBT_LIBRARY  : recebe a API que simula o uso da MML;
BACKUP_DIR   : recebe o diretório que serão gerados os arquivos de backup.

Na Alocação do canal:


[oracle@db3 ~]$ mkdir -p /orabackup/sbt
[oracle@db3 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Feb 13 18:32:40 2018

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

connected to target database: CDB1 (DBID=913632474)

RMAN> RUN {
2> ALLOCATE CHANNEL CH01 DEVICE TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/orabackup/sbt)";
3> BACKUP CURRENT CONTROLFILE;
4> }

using target database control file instead of recovery catalog
allocated channel: CH01
channel CH01: SID=56 device type=SBT_TAPE
channel CH01: WARNING: Oracle Test Disk API

Starting backup at 13-FEB-18
channel CH01: starting full datafile backup set
channel CH01: specifying datafile(s) in backup set
including current control file in backup set
channel CH01: starting piece 1 at 13-FEB-18
channel CH01: finished piece 1 at 13-FEB-18
piece handle=2jsr52rc_1_1 tag=TAG20180213T173331 comment=API Version 2.0,MMS Version 8.1.3.0
channel CH01: backup set complete, elapsed time: 00:00:01
Finished backup at 13-FEB-18

Starting Control File and SPFILE Autobackup at 13-FEB-18
piece handle=c-913632474-20180213-00 comment=API Version 2.0,MMS Version 8.1.3.0
Finished Control File and SPFILE Autobackup at 13-FEB-18
released channel: CH01

RMAN> exit


Recovery Manager complete.
[oracle@db3 ~]$ ls -lrt /orabackup/sbt/
total 49164
-rw-r--r--. 1 oracle oinstall 25165840 Feb 13 18:33 2jsr52rc_1_1
-rw-r--r--. 1 oracle oinstall 25165840 Feb 13 18:33 c-913632474-20180213-00
-rw-r--r--. 1 oracle oinstall 3360 Feb 13 18:33 Oracle_Disk_SBT_Catalog

 Nas parametrizações do RMAN:

[oracle@db3 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Feb 13 18:43:04 2018

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

connected to target database: CDB1 (DBID=913632474)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name CDB1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/orabin/app/oracle/product/12.2.0.1/dbhome_1/dbs/snapcf_cdb1.f'; # default

RMAN> CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS="SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/orabackup/sbt)";

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS "SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/orabackup/sbt)";
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new RMAN configuration parameters are successfully stored

RMAN> backup current controlfile format 'exemplo2.bkp';

Starting backup at 13-FEB-18
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=59 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: WARNING: Oracle Test Disk API
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_SBT_TAPE_1: starting piece 1 at 13-FEB-18
channel ORA_SBT_TAPE_1: finished piece 1 at 13-FEB-18
piece handle=exemplo2.bkp tag=TAG20180213T174719 comment=API Version 2.0,MMS Version 8.1.3.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-FEB-18

Starting Control File and SPFILE Autobackup at 13-FEB-18
piece handle=c-913632474-20180213-01 comment=API Version 2.0,MMS Version 8.1.3.0
Finished Control File and SPFILE Autobackup at 13-FEB-18

RMAN> exit


Recovery Manager complete.
[oracle@db3 ~]$ ls -lrt /orabackup/sbt/
total 98328
-rw-r--r--. 1 oracle oinstall 25165840 Feb 13 18:33 2jsr52rc_1_1
-rw-r--r--. 1 oracle oinstall 25165840 Feb 13 18:33 c-913632474-20180213-00
-rw-r--r--. 1 oracle oinstall 25165840 Feb 13 18:47 exemplo2.bkp
-rw-r--r--. 1 oracle oinstall 25165840 Feb 13 18:47 c-913632474-20180213-01
-rw-r--r--. 1 oracle oinstall 5600 Feb 13 18:47 Oracle_Disk_SBT_Catalog

 A nível de curiosidade, a API existe desde a versão 9i do Oracle database ¹.

Referências:

https://docs.oracle.com/cd/A84870_01/doc/server.816/a76990.pdf
https://docs.oracle.com/cd/B10500_01/server.920/a96566/wnrcmug.htm¹
https://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmconfg.htm#BRADV006
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Durante a realização de um duplicate from active database, fui notificado que o processo havia falhado durante o recover da base de dados apresentando o erro ORA-19909 conforme detalhamento (case de exemplo) a seguir.


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/10/2017 16:42:19
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until change 226605 using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u01/app/oracle/oradata/dbtst/system.dbf'

Como a liberação da base era importante para a continuidade dos negócios, verifiquei o incarnation do ambiente produtivo e realizei o reset do incarnation do ambiente auxiliary para a mesma incarnação do produção, conclui o recover e demais etapas necessárias.

DBPROD:

RMAN> list incarnation;


List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DBPRD 3274873056 CURRENT 1 30-OCT-17

DBTST:
RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DBPRD 3274873056 PARENT 1 30-OCT-17
2 2 DBPRD 3274873056 ORPHAN 221767 05-NOV-17
4 4 DBPRD 3274873056 ORPHAN 222025 05-NOV-17
3 3 DBPRD 3274873056 CURRENT 222452 05-NOV-17

RMAN> reset database to incarnation 1;

database reset to incarnation 1

RMAN> list incarnation of database;


List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DBPRD 3274873056 CURRENT 1 30-OCT-17
2 2 DBPRD 3274873056 ORPHAN 221767 05-NOV-17
4 4 DBPRD 3274873056 ORPHAN 222025 05-NOV-17
3 3 DBPRD 3274873056 ORPHAN 222452 05-NOV-17



<.. recover e demais processos ..>

Na semana seguinte fui notificado da mesma ocorrência, fiz o mesmo procedimento para liberação do ambiente mas também fui analisar o motivo que gerava o erro para resolvê-lo de modo que não fosse necessário uma nova ação manual durante o próximo processo de duplicate.

Examinando o log de alertas do banco (alertlog), podemos constatar a ocorrência de vários "Setting recovery target incarnation to N" as 16:42:14 que represetam as mudanças de incarnation durante o processo de recover. 


[oracle@tst1 /]$ vi $ORACLE_BASE/diag/rdbms/dbtst/dbtst/trace/alert_dbtst.log
..
..
Tue Jan 10 16:41:29 2017
alter database mount
Set as converted control file due to db_unique_name mismatch
Changing di2dbun from DBPRD to DBTST
Successful mount of redo thread 1, with mount id 3249055801
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount
RMAN DUPLICATE: Errors in krbm_getDupCopy
Errors in file /u01/app/oracle/diag/rdbms/dbtst/dbtst/trace/dbtst_ora_14094.trc:
ORA-19625: error identifying file /u01/app/oracle/oradata/dbtst/system.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RMAN DUPLICATE: Errors in krbm_getDupCopy
Errors in file /u01/app/oracle/diag/rdbms/dbtst/dbtst/trace/dbtst_ora_14094.trc:
ORA-19625: error identifying file /u01/app/oracle/oradata/dbtst/sysaux.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RMAN DUPLICATE: Errors in krbm_getDupCopy
Errors in file /u01/app/oracle/diag/rdbms/dbtst/dbtst/trace/dbtst_ora_14094.trc:
ORA-19625: error identifying file /u01/app/oracle/oradata/dbtst/undotbs1.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Tue Jan 10 16:42:14 2017
Setting recovery target incarnation to 2 <========
Setting recovery target incarnation to 2 <========
Setting recovery target incarnation to 3 <========
Setting recovery target incarnation to 3 <========
Tue Jan 10 16:42:14 2017
Switch of datafile 1 complete to datafile copy
checkpoint is 226585
Switch of datafile 2 complete to datafile copy
checkpoint is 226597
Switch of datafile 3 complete to datafile copy
checkpoint is 226594
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover datafile list
1 , 2 , 3
Completed: alter database recover datafile list
1 , 2 , 3
alter database recover if needed
start until change 226605 using backup controlfile
Media Recovery Start
Serial Media Recovery started
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 226585) is orphaned on incarnation#=1
Media Recovery failed with error 19909
ORA-283 signalled during: alter database recover if needed
start until change 226605 using backup controlfile

Um pouco antes destas mensagens de mudança de incarnation podemos ver a ocorrência de outros erros juntamente com o nome de um arquivo de rastreamento (.trc). Abrindo o arquivo e pesquisando por incarnation podemos ver que eles ocorreram logo após a leitura de alguns archives pois neles foram identificados mudanças no incarnation deste database em decorrência de outras cargas com open resetlogs (sempre que ocorrer um RESETLOGS uma nova incarnation será gerada).

[oracle@tst1 /]$ vi /u01/app/oracle/diag/rdbms/dbtst/dbtst/trace/dbtst_ora_14094.trc
..
..
*** 2017-01-10 16:42:14.546
*** ACTION NAME:(0000023 STARTED124) 2017-01-10 16:42:14.546

New incarnation branch detected in ArchiveLog, filename /oraarchive/DBTST/archivelog/2017_11_05/dbtst_1_1_959263858.arc
Inspection of file changed rdi from 1 to 2
New incarnation branch detected in ArchiveLog, filename /oraarchive/DBTST/archivelog/2017_11_05/dbtst_1_1_959265418.arc
Inspection of file changed rdi from 2 to 3
New incarnation branch detected in ArchiveLog, filename /oraarchive/DBTST/archivelog/2017_11_05/dbtst_1_1_959264815.arc

Para solucionar o "problema" basta mover/remover os archives indicados (possivelmente já desnecessários) ou apontar a FRA (Fast Recovery Area) para outro destino onde não exitam archives com o mesmo dbid, caso não esteja sendo utilizado a recovery area basta mudar o apontamento do log_archive_dest_1, por exemplo.

Mas por que o processo de recover lê estes archives com outras incarnações?

A partir da versão 11.2.0.3 o comando foi alterado para catalogar todos os arquivos(archives) abaixo do local onde foi configurado a recovery area ou log_archive_dest_N do banco auxiliary, desta forma o "catalog clone start with +DISKGROUP" lê e cataloga todos os arquivos abaixo do diretório que contenham o mesmo DBID da base que está sendo clonada.

Este cenário ocorre normalmente nos casos em que:

FRA está configurada:

*.db_recovery_file_dest='+RECO1'


log_archive_dest_N utilizando LOCATION=+DISKGROUP:

*.log_archive_dest_1='LOCATION=+RECO1'


Para evitar o erro reaponte para outro destino conforme supracitado:

*.db_recovery_file_dest='+RECOC4'
*.log_archive_dest_1='LOCATION=+RECOC4/newdb'

contents of Memory Script:
{
  backup as copy reuse
  archivelog like  "+RECOC4/luz/archivelog/2014_07_18/thread_1_seq_1742.2339.853243229" auxiliary format "+RECOC4/newdb/1_1742_847627148.dbf"   ;
  catalog clone archivelog  "+RECOC4/newdb/1_1742_847627148.dbf";   <======== é catalogado apenas o archive copiado
  switch clone datafile all;


NOTA:
No cenário de exemplo acima copiei os archives com novas incarnações para dentro do diretório da base DBTST para simular o problema.

[oracle@tst1 /]$ cp /oraarchive/DBPRD/archivelog/2017_11_05/* /oraarchive/DBTST/archivelog/2017_11_05/

e durante o recover:

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/oraarchive/DBPRD/archivelog/2017_01_10/o1_mf_1_14_d7bbr54c_.arc" auxiliary format
 "/oraarchive/DBTST/archivelog/2017_01_10/o1_mf_1_14_%u_.arc"   ;
   catalog clone recovery area; <======== catalogando todos os arquivos abaixo da FRA
   switch clone datafile all;
}

Referências:

RMAN-11003 ORA-283 ORA-19909 during duplicate database (Doc ID 1913573.1)
Manual Completion of a Failed RMAN Backup based Duplicate (Doc ID 360962.1)
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Após algum tempo de estudo fiz a prova 1Z0-338 Oracle Exadata Database Machine and Cloud Service 2017 Implementation Essentials e consegui passar com o score de 86% dos 63% requeridos. =)


Aos que tiverem interesse:

Link da certificação na Oracle University: https://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=5001&get_params=p_exam_id:1Z0-338


Tópicos do exame:

Oracle Database Software
  • Configure and administer a basic database using Oracle features and functions
  • Tune the Oracle database for performance in common use case scenarios
Oracle Exadata Architecture
  • Describe offloading processing
  • Describe the architecture used by Oracle Exadata software
  • Use Smart Flash Cache
  • Enable Flash Cache Write Back
  • Enable Advanced Compression and Hybrid Columnar Compression on Exadata tables
  • Describe storage indexes
  • Describe Oracle recommended configurations for High Availability configurations
  • Enable parallelism for Data Warehouse workloads
  • Describe database and Automatic Storage Management-Scoped Security Cconcepts
  • Describe the Power Distribution Unit
  • Describe memory capabilities of different models
  • Describe the importance and use of In-Memory Fault Tolerance in the Exadata Database Machine
  • Explain the differences between the types of storage servers available on the Exadata Database Machine
Administration and Configuration 
  • Compress data using Advanced Compression and Hybrid Columnar Compression
  • Use network configuration options
  • Create and configure Automatic Storage Management disk groups using Exadata
  • Explain the impact of various diskgroup failure group choices
  • Install software onto the Exadata Database Machine
  • Describe various tasks that can be done with the Integrated Lights Out Management (ILOM) console
  • Reconfigure a 1/8th rack into a 1/4 rack
  • Describe network components in Exadata
  • Create a database snapshot on Exadata for testing purposes

Backup and Recovery
  • Create a backup plan for Exadata
  • Implement recovery procedures on different failure scenarios
  • Describe troubleshooting tasks for backup and recovery processes
  • Describe how the Exadata Storage Servers are backed up internally

High Availability
  • Describe Oracle's recommended guidelines for high availability configurations on Exadata
  • Identify failure scenarios and implement recovery procedures
  • Install high availability software onto the Exadata Database Machine
Monitoring
  • Install an Enterprise Manager Exadata Monitoring plug-in
  • Set up an agent, plugin, and Exadata target for Enterprise Manager Cloud Control
  • Monitor the storage cell using Cell Command Line Interface and Distributed Command Line Interface
  • Describe the purpose and uses for Simple Network Management Protocol (SNMP), Intelligent Platform Management Interface (IPMI) and Integrated Lights Out Management for a DB machine
  • Use osWatcher and iostat to monitor the Database Machine operating system
  • Use exacli and dbcli to monitor storage cells
Software Maintenance
  • Describe software components
  • Locate patches for Exadata
  • Apply Oracle recommendations to the patching process for Exadata
  • Describe the Oracle Configuration Manager
  • Explain how to perform planned maintenance of the Exadata machine
  • Describe how to use the Oracle Exadata Deployment Assistant to create virtual machines on Exadata
Bulk Loading and Migration
  • Use Database File System (DBFS) to perform bulk loading operations
  • Use the ASM Cluster File System (ACFS) to perform bulk data loading operations
  • Configure a Database for staging input data files
  • Use external tables for data loads
  • Describe migration strategies for Exadata
  • Explain Oracle recommendations on how to choose a migration strategy
Resource Management
  • Set up instance caging
  • Manage I/O on Exadata
Support
  • Set up an Automatic Service Request (ASR) on the Database Machine
  • Explain the ASR configuration process
  • Describe implementation requirements for Platinum Support
Exadata in the Cloud
  • Describe the Oracle Exadata Cloud Service provisioning process
  • Configure backup and recovery on the Exadata Cloud Service
  • Administer the Oracle Database on the Exadata Cloud Service
  • Administer the Oracle Database on the Exadata Cloud Machine
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Através do pacote DBMS_SYSTEM do Oracle RDBMS podemos fazer várias coisas, como:



SQL> var VAR varchar2(255)
SQL> exec dbms_system.get_env('ORACLE_HOME',:VAR);

PL/SQL procedure successfully completed.

SQL> print :VAR

VAR
--------------------------------------------------------------------------------
/orabin/app/oracle/product/12.2.0.1/dbhome_1

Exportando uma variável de teste:

Banco=cdb1-> export TESTE="variavel de teste"
Banco=cdb1-> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 3 03:47:54 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> var VAR varchar2(255)
SQL> exec dbms_system.get_env('TESTE',:VAR);

PL/SQL procedure successfully completed.

SQL> print :VAR

VAR
--------------------------------------------------------------------------------
variavel de teste

Exemplo criando uma function para retorno da variável:

SQL> create or replace function getvar (envvar varchar2) return varchar2 as
output varchar2(4000);
begin
dbms_system.get_env(envvar, output);
return output;
end;
/

Function created.

SQL> select getvar('ORACLE_HOME') from dual;

GETVAR('ORACLE_HOME')
--------------------------------------------------------------------------------
/orabin/app/oracle/product/12.2.0.1/dbhome_1

Exemplo criando uma procedure para retorno da variável:

SQL> create or replace procedure getvar2 (envvar varchar2) as
output varchar2(4000);
begin
dbms_system.get_env(envvar, output);
dbms_output.put_line(output);
end;
/

Procedure created.

SQL> set serveroutpu on
SQL> exec getvar2('TESTE');
variavel de teste

PL/SQL procedure successfully completed.

SQL> exec getvar2('ORACLE_HOME');
/orabin/app/oracle/product/12.2.0.1/dbhome_1
PL/SQL procedure successfully completed.
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

As tabelas do AWR (Automatic Workload Repository) contém uma grande quantidade de dados de desempenho extremamente úteis para análises de performance e detecção de problemas. Os dados do AWR são armazenados em tabelas WRH$ e DBA_HIST alimentadas através de snapshots regulares e armazenados por um período de tempo definido na SYSAUX tablespace.

Algumas vezes é necessário que estes dados sejam salvos para utilizações futuras ou mesmo importados em outras bases de dados para comparações, maior tempo de armazenamento em relação ao definido na base origem, etc. A Oracle fornece dois scripts para realizar o export e import dos dados do AWR, são eles: awrextr.sql e awrload.sql, ambos localizados em $ORACLE_HOME/rdbms/admin


O export dos dados é realizado através do script awrextr.sql que gera um arquivo data pump contendo o intervalo de snapshots desejado e que posteriormente pode ser importado em outra base de dados.


1 – Criando o diretório que será gerado o arquivo de dump:

SQL> create directory awr_directory as '/orabackup';

Directory created.

2 - Executando o script de export:

SQL> @?/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script will extract the AWR data for a range of snapshots ~
~ into a dump file. The script will prompt users for the ~
~ following information: ~
~ (1) database id ~
~ (2) snapshot range to extract ~
~ (3) name of directory object ~
~ (4) name of dump file ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id DB Name Host
------------ ------------ ------------
* 3274873056 DBPRD tst1

The default database id is the local one: '3274873056'. To use this
database id, press to continue, otherwise enter an alternative.

Quando executado o script precisa de 4 interações para gerar o export dos snapshots.

2.A - Informar o DBID da base de dados desejada. O script irá listar todos os dbids disponíveis, se o dbid desejado for o default(*) basta pressionar ENTER ou então primeiramente informar o dbid necessário.

Enter value for dbid: 3274873056

Using 3274873056 for Database ID

2.B - O segundo passo é especificar a quantidade de dias para que a listagem dos snapshots (snap_ids) seja retornada e o período (begin_snap/end_snap) da extração de dados definida.

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots

DB Name Snap Id Snap Started
------------ --------- ------------------
DBPRD 1 05 Nov 2017 09:50
2 05 Nov 2017 11:00
3 05 Nov 2017 12:00
4 05 Nov 2017 13:00
5 05 Nov 2017 14:00
6 05 Nov 2017 15:00
7 05 Nov 2017 16:00


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 2
Begin Snapshot Id specified: 2

Enter value for end_snap: 6
End Snapshot Id specified: 6

2.C - Especificar o diretório que será utilizado para gerar o arquivo de dump (expdp).

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name Directory Path
------------------------------ -------------------------------------------------
AWR_DIRECTORY /orabackup
DATA_PUMP_DIR /u01/app/oracle/product/11.2.0.4/dbhome1/rdbms/lo
g/

ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0.4/dbhome1/ccr/host
s/tst1/state

ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0.4/dbhome1/ccr/stat
e


Choose a Directory Name from the above list (case-sensitive).

Enter value for directory_name: AWR_DIRECTORY

Using the dump directory: AWR_DIRECTORY

2.D - Por fim, informar o nome do arquivo data pump que será gerado. O nome não deve conter a extensão .dmp, ele será acrescentado automaticamente pelo script.

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_2_6.
To use this name, press to continue, otherwise enter
an alternative.

Enter value for file_name: awrdata_dbprd_2_6

Using the dump file prefix: awrdata_dbprd_2_6
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| /orabackup
| awrdata_dbprd_2_6.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Extract Started ...
|
| This operation will take a few moments. The
| progress of the AWR extract operation can be
| monitored in the following directory/file:
| /orabackup
| awrdata_dbprd_2_6.log
|

End of AWR Extract

Dependendo da quantidade de dados AWR que precisa ser extraído a operação de extração pode demorar um pouco para ser concluída. Uma vez concluída você pode levar o arquivo para o local ou servidor desejado.

O import de dados é realizado através do script awrload.sql utilizando o dump gerado anteriormente pelo script awrextr.sql, conforme demonstrado abaixo:

1 - Criando o diretório em outra base de dados com o caminho onde se encontra o arquivo de dump:

SQL> create directory awr_directory as '/orabackup';

Directory created.

2 - Executando o script de import:

SQL> @?/rdbms/admin/awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ This script will load the AWR data from a dump file. The ~
~ script will prompt users for the following information: ~
~ (1) name of directory object ~
~ (2) name of dump file ~
~ (3) staging schema name to load AWR data into ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Da mesma forma que no script de export, o script de import também requer algumas interações.

2.A - Informar o nome do diretório que contem o arquivo.

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name Directory Path
------------------------------ -------------------------------------------------
AWR_DIRECTORY /orabackup
DATA_PUMP_DIR /u01/app/oracle/product/11.2.0.4/dbhome1/rdbms/lo
g/

ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0.4/dbhome1/ccr/host
s/tst1/state

ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0.4/dbhome1/ccr/stat
e


Choose a Directory Name from the list above (case-sensitive).

Enter value for directory_name: AWR_DIRECTORY

Using the dump directory: AWR_DIRECTORY

2.B - Informar o nome do arquivo contendo os dados AWR extraídos anteriormente. Não é preciso incluir junto ao nome do arquivo sua extensão (.dmp), ela será adicionada automaticamente pelo script.

Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:

Enter value for file_name: awrdata_dbprd_2_6

Loading from the file name: awrdata_dbprd_2_6.dmp

2.C - Nesta etapa deve ser informado o nome de um schema temporário para que os dados sejam importados e posteriormente transferidos para as tabelas do AWR. Após a transferencia dos dados para as tabelas do AWR o schema é automaticamente removido.

Informações como a tablespace permanente e temporária do schema também serão solicitadas nesta etapa e ao fim os dados serão finalmente importados.

Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the staging schema
where the AWR snapshot data will be loaded.
After loading the data into the staging schema,
the data will be transferred into the AWR tables
in the SYS schema.


The default staging schema name is AWR_STAGE.
To use this name, press to continue, otherwise enter
an alternative.

Enter value for schema_name: AWR_STAGE

Using the staging schema name: AWR_STAGE

Choose the Default tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE users's default tablespace. This is the
tablespace in which the AWR data will be staged.

TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE
------------------------------ --------- ------------------
SYSAUX PERMANENT *

Pressing will result in the recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: SYSAUX

Using tablespace SYSAUX as the default tablespace for the AWR_STAGE


Choose the Temporary tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE user's temporary tablespace.

TABLESPACE_NAME CONTENTS DEFAULT TEMP TABLESPACE
------------------------------ --------- -----------------------
TEMP TEMPORARY *

Pressing will result in the database's default temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as the temporary tablespace for AWR_STAGE


... Creating AWR_STAGE user

|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /orabackup
| awrdata_dbprd_2_6.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
| *** AWR Load Started ...
|
| This operation will take a few moments. The
| progress of the AWR load operation can be
| monitored in the following directory/file:
| /orabackup
| awrdata_dbprd_2_6.log
|
... Dropping AWR_STAGE user

End of AWR Load

Dependendo da quantidade de dados AWR que serão importadas a operação pode demorar um pouco para ser concluída.

3 - Verificando se os snapshots do DBID desejado foram importados:

SQL> select distinct dbid from DBA_HIST_SNAPSHOT;

DBID
----------
3274873056
3149615557

Referências:

How to Export and Import the AWR Repository From One Database to Another (Doc ID 785730.1)
Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview