Arquivo da categoria: Sem categoria

Flex ASM

Publicado em por
Responder

Um dos problemas que temos ao utilizar o ASM e que, nas versoes anteriores a versao 12c, temos que instalar a instancia ASM em cada local onde sera consumido dos diskgroups.
Temos entao o Flex ASM e para a clusterizacao temos que utilizar o Flex Clusters.
Caracteristicas:
Flex ASM nao requer Flex Cluster
Flex ASM roda como um servico para cluster padrao para atender clientes atraves do cluster.
Flex ASM pode rodar como um hub de conexao para atender aos servicos de Flex Cluster atraves de nos de HUBs de Flex Cluster.
O Flex ASM tem menor gastos de recursos ja que o mesmo nao precisa da instancia ASM
Flex ASM introduz uma nova rede para que os clientes e as instancias ASM possam se comunicar nessa rede
Como ocorre a conexao:
Cada servidor contendo o banco de dados oracle ira configurar o local_listener apontando para o listener existente no servidor que roda a instancia ASM com seu devido Listener, sendo que o parametro local_listener do banco pode ser configurado para ate 3 listener.

As instancias ASM sao desenvolvidas para que tenhamos o minimo de monitoramento o possivel. Portanto sempre que possivel a recomentacao e utilizar o parametro memory_target. O calculo e feito na hora da criacao da instancia ASM baseando-se no numero de cores dos processadores e a quantidade de memoria disponivel. Atualmente o valor minimo e de 1076M para o memory_target.
Gerenciamento
$ srvctl status asm -detail

ASM is running on host03,host02,host01 ASM is enabled.
$ srvctl stop asm -node host03 -f
$ srvctl start asm -node host04 

$ srvctl status asm -detail

ASM is running on host04,host02,host01
ASM is enabled.
$ srvctl relocate asm -currentnode host04 -targetnode host03 $ srvctl status asm -detail
ASM is running on host03,host02,host01
ASM is enabled. 

$ srvctl stop asm -proxy -node host03 
$ srvctl start asm -proxy -node host04

Cardinalidade

A cardinalidade para as instancias ASM, informa qual e a quantidade de instancias ASM servindo o cluster ASM.

$ srvctl config asm
ASM home: /u01/app/12.1.0/grid
Password file: +DATA/orapwASM
ASM listener: LISTENER
ASM instance count: 3
Cluster ASM listener: ASMNET1LSNR_ASM

Para consultar quais sao os clientes que estao consumindo as instancias, utilizamos a gv$asm_client.

Para visualizar quais instancias estao fazendo o atendimento, faca a consulta abaixo:

 

Os clientes podem ser realocados, isso ja ocorre automaticamente caso a instancia ASM falhe. Mas atraves do novo comando abaixo, podemos realocar o cliente. Geralmente voce executa este comando para tentar realocar os clientes para outra instancia ASM.

SQL> ALTER SYSTEM RELOCATE CLIENT ‘<instance_name>:<db_name>‘;
O cliente sera desconectado e provavelmente ao se conectar novamente, ira se conectar na instancia ASM que ja estava conectado. Para evitar que isso ocorra, derrube a instancia ASM logo apos realocar todos clientes.

Quando os discos estao entregues para serem consumidos pelos diskgroups temos os seguintes status:
CANDIDATE – Disco recentemente criado, este disco nunca foi usado ainda.
FORMER – Disco que ja foi usado e removido do diskgroup, esta disponivel novamente para uso.
PROVISIONED – E similar ao candidate exceto porque o provisionado ainda necessita alguma acao por parte do administrador para disponibilizar este disco.
Para criar um diskgroup siga o exemplo:
 

CREATE DISKGROUP FRA NORMAL REDUNDANCY
DISK ‘ORCL:SDD11’ NAME ‘FRA_DISK1’ SIZE 977 M, ‘ORCL:SDD12’ NAME ‘FRA_DISK2’ SIZE 977 M;

Cada diskgroup criado, mesmo que nao contenha a clausula failure group, cada disco pertencendo a um diskgroup com redundancia normal ou redundancia high,  cada disco contem uma redundancia, ou seja se eu tiver 10 discos em um diskgroup vamos ter automaticamente 10 failgroups.
Redundancia
NORMAL – requer no minimo dois failure groups. No exemplo de criacao acima sao dois failure groups criados. Permite a falha de um failure group. Controlfiles armazenados neste tipo de diskgroup espelhado 3 vezes.
HIGH REDUNDANCY – requer a existencia de tres grupos de falha. Tolerante a perda de dois grupos de falhas.
EXTERNAL REDUNDANCY – nao tolera falhas, necessita redundancia externa.

Questao da prova 1Z0-058

Publicado em por
Responder

Assuntos das questoes 1Z0-058 sobre as informacoes abaixo:

  1. Sobre os processos do ACFS

  3. Additional processes are started when ADVM volumes are configured.
    • VDBG: The Volume Driver Background process forwards ASM requests to lock or unlock an extent for volume operations to the Dynamic Volume Manager driver. The VDBG is a fatal background process, the termination of this process brings down the ASM instance.
    • VBGn: Volume Background processes wait for requests from the Dynamic Volume Manager driver, which need to be coordinated with the ASM instance. An example of such a request would be opening or closing an ASM volume file when the Dynamic Volume Manager driver receives an open for a volume (possibly due to a file system mount request) or close for an open volume (possibly due to a file system unmount request). The unplanned death of any of these processes does not have an effect on the ASM instance.
    • VMB: Volume Membership Background coordinates cluster membership with the ASM instance. 
      Sobre redundancia do diskgroup

  1. There are always failure groups even if they are not explicitly created. If you do not specify a failure group for a disk, that disk is placed in its own failure group with the failure group name the same as the disk name Therefore, if 20 disks were in a single disk group, there could be 20 failure groups as well. Failure groups have meaning only when used with normal and high redundancy disk groups. All failure groups within the same disk group should be created with the same capacity to avoid space allocation problems.

    Sobre Quiescing no RAC

Quiescing a database
For maintenance purposes you may require a database to be quiesed, so that the only connections allowed are as sys or system.  Even users with the DBA role will be excluded.
This mode is useful for:
Transactions that would otherwise fail if user transactions access the same object or set of objects.  Things like changing the schema of a database table, or adding a column to an existing table where a no-wait lock is required.
Multistep procedures, that could confuse application users, if they were still logged on. For example exporting dropping an re-creating a table.
Quiescing the database will have a similar effect to shutting the database down, and restarting it in restricted mode.  However on a high availability system, you may not be able to shut the database down.  Quiescing would have a smaller impact then the shutdown and restart.
To place a database into quiesced mode:
SQL> alter system quiesce restricted;
Non-DBA active session will continue until they become inactive.  An active session is a session that is inside a transaction or a session that is currently holding shared resources. No inactive sessions are allowed to become active.  When a connected user issues a query, the query will seem to hang until the databas
e is taken out of quiesced state, and the query will then continue.
Once all of the Non-DBA active sessions have become inactive, then the “SQL> alster system quiesce restricted;” completes, and the database is then in a quiesced state.  In a RAC environment, this statement will affect all the instances, and not just the instance where the statement was issued from.
While waiting for this statement to process you can query from another session SID(session ID) from v$blocking_quiesce view.  You can join this view with v$session to get more information about the session.
SQL> select bq.sid, ses.user, ses.osuser, ses.type, ses.program
From v$blocking_quiesce bq,
           V$session ses
Where bq.sid = ses.sid;
If your session terminates, or you cancel the request to quiesce the database, then the Oracle Database automatically reverses any partial effects of the statement.
For queries that are carried out by successive OCI(Oracle Call Interface) fetches,  the Oracle database only waits for the first fetch to complete.
The mechanism works the same for  dedicated and shared server connections.  The user trying to login after the quiesce statement has been issued, will experience it as though the login as hung.  The login will continue again when the database is unquiesced.
The database will remain in this state, regardless of whether the session that issued the statement is still active or not.  In order to take the database out of this mode a DBA(sys,system) will need to log in and unquiesce the database.  In a RAC environment, the unquiesce statement can even be issued from a different instance.
SQL> alter system unquiesce;
A quiesced database is not in the correct state for a cold backup, because the file headers are still being updated.  However an online backup in this state is possible.  
To view the QUIESCE state of an instance, you can:
SQL> select active_state from v$instance;
The possible values returned are:
·         NORMAL      – Normal unquiesced state
·         QUIESCING – Being quieced, but some non-DBA sessions are still active
·         QUESCED     – Quiesced, non non-DBA sessions are active or allowed

Verificar as recomendacoes do SEGMENT ADVISOR sem entrar no EM

Publicado em por
Responder

Com a consulta abaixo conseguimos ter acesso as recomendacoes do Segment advisor.

SELECT 
                   ‘Segment Advice ————————–‘|| chr(10) || 
                   ‘TABLESPACE_NAME           : ‘ || tablespace_name              || chr(10) || 
                   ‘SEGMENT_OWNER             : ‘ || segment_owner                || chr(10) || 
                   ‘SEGMENT_NAME              : ‘ || segment_name                 || chr(10) || 
                   ‘ALLOCATED_SPACE           : ‘ || allocated_space              || chr(10) || 
                   ‘RECLAIMABLE_SPACE: ‘ || reclaimable_space || chr(10) || 
                   ‘RECOMMENDATIONS           : ‘ || recommendations              || chr(10) || 
                   ‘SOLUTION 1                : ‘ || c1                           || chr(10) || 
                   ‘SOLUTION 2                : ‘ || c2                           || chr(10) || 
                   ‘SOLUTION 3                : ‘ || c3 Advice
FROM
                   TABLE(dbms_space.asa_recommendations(‘FALSE’, ‘FALSE’, ‘FALSE’)); 


Tamanho de uma tabela

Publicado em por
Responder

Ola pessoal,

venho a mostrar a voces como temos o tamanho de uma tabela e de seus indices

– Tamanho de Cada Tabela

SELECT substr(owner,1,15) owner,segment_type, 
substr(tablespace_name,1,15) tablespace_name, 
round(sum(bytes/1024/1024),2) as Tamanho_MB 
FROM dba_segments
WHERE owner = ‘SANKHYA’
AND segment_type = ‘TABLE’
AND segment_name like ‘TSILGT%’
GROUP BY owner,segment_type, tablespace_name, segment_name
union all
SELECT substr(owner,1,15) owner,segment_type, 
substr(tablespace_name,1,15) tablespace_name, 
round(sum(bytes/1024/1024),2) as Tamanho_MB 
FROM dba_segments
WHERE owner = ‘SANKHYA’
AND segment_type = ‘INDEX’
AND segment_name in (
select index_name 
from dba_indexes 
where table_name = ‘TSILGT’
and owner = ‘SANKHYA’)
GROUP BY owner,segment_type, tablespace_name, segment_name;
SELECT owner, tablespace_name, segment_name,
round(sum(bytes/1024/1024),2) as Tamanho_MB –, extents as Num_extents
FROM dba_segments
WHERE owner = ‘SCOTT’
AND segment_type = ‘TABLE’
— AND segment_name like ‘DEPT%’
GROUP BY owner, tablespace_name, segment_name

– Tamanho das Tabelas Por Usuário

SELECT owner, round(sum(bytes/1024/1024),2) as Tamanho_MB –, extents as Num_extents
FROM dba_segments
GROUP BY owner

– Tamanho Total das Tabelas

SELECT round(sum(bytes/1024/1024),2) as Tamanho_MB –, extents as Num_extents
FROM dba_segments

– % de Uso das TableSpaces

SELECT a.TABLESPACE_NAME “TableSpace Name”,
round(a.BYTES/1024/1024) “MB Allocated”,
round((a.BYTES-nvl(b.BYTES, 0)) / 1024 / 1024) “MB Used”,
nvl(round(b.BYTES / 1024 / 1024), 0) “MB Free”,
round(((a.BYTES-nvl(b.BYTES, 0))/a.BYTES)*100,2) “Pct Used”,
round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) “Pct Free”
FROM (SELECT TABLESPACE_NAME,
sum(BYTES) BYTES
FROM dba_data_files
GROUP BY TABLESPACE_NAME) a,
(SELECT TABLESPACE_NAME,
sum(BYTES) BYTES
FROM sys.dba_free_space
GROUP BY TABLESPACE_NAME) b
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
ORDER BY ((a.BYTES-b.BYTES)/a.BYTES);

BTRFS

Publicado em por
Responder

Sistema BTRFS
O BTRFS ou better file system, e um sistema de arquivos que e disponibilizado a partir da versao 6.3 do Oracle Linux. Foi desenvolvido pela Oracle para que possamos ter mais uma opcao de um file system confiavel e de alta disponibilidade.
O BTRFS entre outras coisas podemos fazer o RAID 0, RAID 1 e o RAID 10. Obviamente, se gasta discos para a redundancia via software. O Interessante e que se voce precisar efetuar um clone do file system ou um backup pode ser feito a quente, e de quebra – copia remota para outra maquina.
BTRFS utiliza para gravacao um procedimento que se chama COPY ON WRITE que em miudos significa que ele somente grava mesmo nas operacoes de alteracao. Isso e interessante porque assim consegue fazer a replicacao sem comprometer a integridade.

Make Aliases on sqlplus

Publicado em por
Responder
In first example, i can show these:
[oracle@localhost~] echo “select count(*) from tab;” | sqlplus -s username/pass
With another method, you can run from one Linux command a SQL file
 [oracle@localhost~] sqlplus -s username/pass <filename.sql
 COUNT(*)
————-
                20
  1. alias sysdba=‘select status, instance_name from v$instance; | sqlplus / as sysdba’
  2. alias sysoper=‘sqlplus / as sysoper’
  3. alias rman=‘rman target / ‘

Change the face for SQLPLUS – Automatically display SID and connected User in Sql*Plus

Publicado em por
Responder

Show the name of instance on startup of sqlplus

set termout off
define gname=idle
column global_name new_value gname
select lower(user)||’@’ ||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name from (select global_name, instr(global_name,’.’) dot from global_name);
set sqlprompt ‘&gname>’
set termout on

Put this code on file “glogin.sql” in your $ORACLE_HOME/sqlplus/admin 

before
SQL>show user
USER is “SYS”
SQL>

After
sys@ORCL>show user
USER is “SYS”
sys@ORCL>


Automatically startup the database when Oracle Linux Starts

Publicado em por
Responder
To make a startup of Oracle database automatically please follow these steps.
1. First of all we’ve to change our /etc/oratab file. This file have some hints like, database name, Oracle home, and start or not – N/Y
orcl:/home/oracle/product/10.2.0/Db_1:N
The first field is the name of my database (orcl), the second one is my home directory (/home/oracle/product/10.2.0/Db_1), and the third indicates to the dbstart utility whether the database should, or should not be brough up at the system boot time with “Y” or “N” parameters respectively
As a root user, we’ll change last field of this line from “N” to “Y” to let dbstart utility start this database when it runs
2. This need to put one file dbora in /etc/init.d/ directory. In this script, we’ll define two variables, ORA_OWNERand ORACLE_HOME and then, we’ll start (or shutdown) our database by connecting with oracle user and running lsnrctl (Listener utility), dbstart (utility which starts the database) and dbshut (utility which shutdowns the database) utilities depending on our OS state.
This is the source of our dbora file:
ORACLE_HOME=/home/oracle/product/10.2.0/Db_1
ORA_OWNER=oracle
case “$1″ in
 ’start’)  #If the system is starting, then …
su – $ORA_OWNER -c “$ORACLE_HOME/bin/lsnrctl start” #Start the listener
su – $ORA_OWNER -c “$ORACLE_HOME/bin/dbstart #Start the database
  ;;
‘stop’)   #If the system is stops, that is we’re shutting down our OS, then …
  su -$ORA_OWNER -c $ORACLE_HOME/bin/dbshut
  su -$ORA_OWNER -c “$ORACLE_HOME/bin/lsnrctl stop”
  ;;
esac
3. Once you’ve saved your file into this directory, make sure that it’s executable by running:
chmod 750  dbora
4. Then you need to add the appropriate symbolic links to cause the script to be executed when the system goes down, or comes up. Create it with ln -s command.
# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/K01dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/K01dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora
Let’s analyze these commands:
The first line creates symbolic link of our script in rc3.d directory. At startup, Linux runs /etc/rc.d/rc script at the current level (normally 3 or 5). rc3.d indicates 3rd runlevel, “K” indicates OS’s shutdown (on servers shutdown, Linux calls the scripts in /etc/rc.d/rc3/K* in order)
The second line creates another symbolic link of our script, to be run at startup of our OS, indicating “S99″ and the name of our script
Another two lines creates symoblic links for 5th runlevel.
That’s all. Your script and your service is ready for use. Just restart your OS and upon startup you’ll see your newly created service running. Then open new terminal, enter you database and issue :
SQL>SELECT status FROM v$instance;
You’ll see your database’s status is OPEN
P.S
In some releases, even when we create a new service, it doesn’t work. When we issue dbstart command manually from OS, we’re getting an error:
cat: /var/opt/oracle/oratab: No such file or directory
It has simple reason and solution
If the directory /var/opt/oracle exists during the Oracle installation the dbhome script will have the parameter ORATAB set to “/var/opt/oracle/oratab” instead of “/etc/oratab”. The normal DBCA process during a default installation will still add the instance entry to /etc/oratab.
It has two solutions:
1. You can either copy the original oratab file to this directory :
cp /etc/oratab /var/opt/oracle/
2. Or you can edit dbstart and dbshut scripts, find the variable ORATAB, and you’ll find that it’s addressing to  /var/opt/oracle/oratab file. Jus
t change it to /etc/oratab