Oracle – Aprendendo Instalação do 12c – JPCorp

Instalação do Oracle 12c no Oracle Linux 7, se atentarem nas observações abaixo:

Variáveis: Acertar conforme seu ambiente o ".bashrc" e scripts de "startup.sh", "shutdown.sh" e "dbora.service", e dar atenção em todos detalhes, inclusive no final da ORACLE_BASE e ORACLE_HOME no "/" se tiver pode apresentar algum erro de sintaxe por estar duplicando a "/".
Hostname: Não esquecer de acertar o arquivos "hosts" e ficar atento nos locais que é colocado o hostname.
Listener: Nome do serviço deve ser igual ao da Instância, caso esteja divergente pode ter os erros ORA-00119 e ORA-00132 [SQL> ORA-00119: invalid specification for system parameter LOCAL_LISTENER | syntax error or unresolved network name 'NOME']. (No vídeo coloquei divergente e tive o problema, mais um aprendizado pra conta)

1 - Preparação ambiente para instalação Oracle

[root@srv01 ~]# yum -y update
[root@srv01 ~]# setenforce Permissive
[root@srv01 ~]# vim /etc/selinux/config
				SELINUX=permissive
[root@srv01 ~]# firewall-cmd --add-port=1521/tcp --permanent
[root@srv01 ~]# firewall-cmd --reload
[root@srv01 ~]# vim /etc/fstab
				tmpfs /dev/shm tmpfs defaults,size=3G 0 0
[root@srv01 ~]# mount -o remount tmpfs
[root@srv01 ~]# vim /etc/hosts
				192.168.200.13 srv01.labs.eti.br srv01
[root@srv01 ~]# yum -y install oracle-rdbms-server-12cR1-preinstall
[root@srv01 ~]# less /etc/sysctl.conf
[root@srv01 ~]# grep oracle /etc/passwd
[root@srv01 ~]# mkdir /u01/
[root@srv01 ~]# chown -R oracle:oracle /u01/

[root@srv01 ~]# yum -y update

[root@srv01 ~]# setenforce Permissive

[root@srv01 ~]# vim /etc/selinux/config

SELINUX=permissive

[root@srv01 ~]# firewall-cmd --add-port=1521/tcp --permanent

[root@srv01 ~]# firewall-cmd --reload

[root@srv01 ~]# vim /etc/fstab

tmpfs /dev/shm tmpfs defaults,size=3G 0 0

[root@srv01 ~]# mount -o remount tmpfs

[root@srv01 ~]# vim /etc/hosts

192.168.200.13 srv01.labs.eti.br srv01

[root@srv01 ~]# yum -y install oracle-rdbms-server-12cR1-preinstall

[root@srv01 ~]# less /etc/sysctl.conf

[root@srv01 ~]# grep oracle /etc/passwd

[root@srv01 ~]# mkdir /u01/

[root@srv01 ~]# chown -R oracle:oracle /u01/

2 - Download do pacote e instalação

[oracle@srv01 ~]# cd /u01/
[oracle@srv01 ~]# wget http://download.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_database_1of2.zip
[oracle@srv01 ~]# wget http://download.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_database_2of2.zip
[oracle@srv01 ~]# unzip linuxamd64_12102_database_1of2.zip && unzip linuxamd64_12102_database_2of2.zip;
[oracle@srv01 ~]# cd database/
[oracle@srv01 ~]# ./runInstaller

[oracle@srv01 ~]# cd /u01/

[oracle@srv01 ~]# wget http://download.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_database_1of2.zip

[oracle@srv01 ~]# wget http://download.oracle.com/otn/linux/oracle12c/121020/linuxamd64_12102_database_2of2.zip

[oracle@srv01 ~]# unzip linuxamd64_12102_database_1of2.zip && unzip linuxamd64_12102_database_2of2.zip;

[oracle@srv01 ~]# cd database/

[oracle@srv01 ~]# ./runInstaller

3 - Criar configuração do Listener

[oracle@srv01 ~]# cd /u01/app/oracle/product/12.1.0/dbhome_1/bin
[oracle@srv01 ~]# ./netca

1 2	[oracle@srv01 ~]# cd /u01/app/oracle/product/12.1.0/dbhome_1/bin [oracle@srv01 ~]# ./netca

4 - Criar Base de dados

[oracle@srv01 ~]# cd /u01/app/oracle/product/12.1.0/dbhome_1/bin
[oracle@srv01 ~]# ./dbca

1 2	[oracle@srv01 ~]# cd /u01/app/oracle/product/12.1.0/dbhome_1/bin [oracle@srv01 ~]# ./dbca

5 - Vídeo instalação

6 - Pós instalação, variáveis e unit systemd

[root@srv01 ~]# vim /home/oracle/.bashrc
# Oracle
export VISUAL="vim"
export EDITOR="vim"
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_SID=syserp
export ORACLE_TERM=xterm
export PATH=$ORACLE_HOME/bin:/usr/sbin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export ORACLE_OWNER=oracle

# User specific aliases and functions
alias sqlplus="rlwrap sqlplus"
alias vi="vim"

[root@srv01 ~]# vim /lib/systemd/system/dbora.service
[Unit]
Description=The Oracle Database Service
After=syslog.target network.target

[Service]
# systemd ignores PAM limits, so set any necessary limits in the service.
# Not really a bug, but a feature.
# https://bugzilla.redhat.com/show_bug.cgi?id=754285
LimitMEMLOCK=infinity
LimitNOFILE=65535

Type=simple
RemainAfterExit=yes
User=oracle
Group=oinstall
ExecStart=/home/oracle/scripts/startup.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1 &
ExecStop=/home/oracle/scripts/shutdown.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1

[Install]
WantedBy=multi-user.target

[root@srv01 ~]# mkdir /home/oracle/scripts/
[root@srv01 ~]# vim /home/oracle/scripts/startup.sh
#!/bin/bash
export TMP=/tmp
export TMPDIR=$TMP
export PATH=/usr/sbin:/usr/local/bin:$PATH
export ORACLE_HOSTNAME=srv01.labs.eti.br
export ORACLE_UNQNAME=srv01

export ORACLE_SID=syserp
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES

# Start Listener
lsnrctl start
		
# Start Database
sqlplus / as sysdba << EOF
STARTUP;
EXIT;
EOF

[root@srv01 ~]# vim /home/oracle/scripts/shutdown.sh
#!/bin/bash
export TMP=/tmp
export TMPDIR=$TMP
export PATH=/usr/sbin:/usr/local/bin:$PATH
export ORACLE_HOSTNAME=srv01.labs.eti.br
export ORACLE_UNQNAME=srv01

export ORACLE_SID=syserp
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES

# Stop Database
sqlplus / as sysdba << EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF

# Stop Listener
lsnrctl stop

[root@srv01 ~]# chmod +x /home/oracle/scripts/*
[root@srv01 ~]# chown -R oracle:oinstall /home/oracle/scripts/
[root@srv01 ~]# systemctl status dbora
● dbora.service - The Oracle Database Service
   Loaded: loaded (/usr/lib/systemd/system/dbora.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
[root@srv01 ~]# systemctl enable dbora
Created symlink from /etc/systemd/system/multi-user.target.wants/dbora.service to /usr/lib/systemd/system/dbora.service.
[root@srv01 ~]# systemctl stop dbora
[root@srv01 ~]# systemctl start dbora
[root@srv01 ~]# systemctl status dbora
● dbora.service - The Oracle Database Service
   Loaded: loaded (/usr/lib/systemd/system/dbora.service; enabled; vendor preset: disabled)
   Active: active (running) since Sáb 2016-07-02 19:21:15 BRT; 7s ago

100

[root@srv01 ~]# vim /home/oracle/.bashrc

# Oracle

export VISUAL="vim"

export EDITOR="vim"

export TMP=/tmp

export TMPDIR=$TMP

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1

export ORACLE_SID=syserp

export ORACLE_TERM=xterm

export PATH=$ORACLE_HOME/bin:/usr/sbin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;

export ORACLE_OWNER=oracle

# User specific aliases and functions

alias sqlplus="rlwrap sqlplus"

alias vi="vim"

[root@srv01 ~]# vim /lib/systemd/system/dbora.service

[Unit]

Description=The Oracle Database Service

After=syslog.target network.target

[Service]

# systemd ignores PAM limits, so set any necessary limits in the service.

# Not really a bug, but a feature.

# https://bugzilla.redhat.com/show_bug.cgi?id=754285

LimitMEMLOCK=infinity

LimitNOFILE=65535

Type=simple

RemainAfterExit=yes

User=oracle

Group=oinstall

ExecStart=/home/oracle/scripts/startup.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1 &

ExecStop=/home/oracle/scripts/shutdown.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1

[Install]

WantedBy=multi-user.target

[root@srv01 ~]# mkdir /home/oracle/scripts/

[root@srv01 ~]# vim /home/oracle/scripts/startup.sh

#!/bin/bash

export TMP=/tmp

export TMPDIR=$TMP

export PATH=/usr/sbin:/usr/local/bin:$PATH

export ORACLE_HOSTNAME=srv01.labs.eti.br

export ORACLE_UNQNAME=srv01

export ORACLE_SID=syserp

ORAENV_ASK=NO

. oraenv

ORAENV_ASK=YES

# Start Listener

lsnrctl start

# Start Database

sqlplus / as sysdba << EOF

STARTUP;

EXIT;

EOF

[root@srv01 ~]# vim /home/oracle/scripts/shutdown.sh

#!/bin/bash

export TMP=/tmp

export TMPDIR=$TMP

export PATH=/usr/sbin:/usr/local/bin:$PATH

export ORACLE_HOSTNAME=srv01.labs.eti.br

export ORACLE_UNQNAME=srv01

export ORACLE_SID=syserp

ORAENV_ASK=NO

. oraenv

ORAENV_ASK=YES

# Stop Database

sqlplus / as sysdba << EOF

SHUTDOWN IMMEDIATE;

EXIT;

EOF

# Stop Listener

lsnrctl stop

[root@srv01 ~]# chmod +x /home/oracle/scripts/*

[root@srv01 ~]# chown -R oracle:oinstall /home/oracle/scripts/

[root@srv01 ~]# systemctl status dbora

● dbora.service - The Oracle Database Service

Loaded: loaded (/usr/lib/systemd/system/dbora.service; disabled; vendor preset: disabled)

Active: inactive (dead)

[root@srv01 ~]# systemctl enable dbora

Created symlink from /etc/systemd/system/multi-user.target.wants/dbora.service to /usr/lib/systemd/system/dbora.service.

[root@srv01 ~]# systemctl stop dbora

[root@srv01 ~]# systemctl start dbora

[root@srv01 ~]# systemctl status dbora

● dbora.service - The Oracle Database Service

Loaded: loaded (/usr/lib/systemd/system/dbora.service; enabled; vendor preset: disabled)

Active: active (running) since Sáb 2016-07-02 19:21:15 BRT; 7s ago

7 - Listener

Listener é o serviço de rede do Oracle, ao qual abre conexão e roda na porta 1521/tcp por padrão, abaixo comando e arquivos para troubleshooting:

[oracle@srv01 ~]$ lsnrctl status
[oracle@srv01 ~]$ ll /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
[oracle@srv01 ~]$ ll /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora

[oracle@srv01 ~]$ lsnrctl status

[oracle@srv01 ~]$ ll /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

[oracle@srv01 ~]$ ll /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora

8 - Operações no banco

# Modos de login
sqlplus / as sysdba
sqlplus sys as sysdba
sqlplus system/senha@instancia

# Iniciar instancia, verificar status, baixar
SQL> startup;
SQL> shutdown immediate
SQL> select status from v$instance;
SQL> select INSTANCE_NAME, status from v$instance;

# Verificar arquivos Control File
SQL> show parameter control;
SQL> show parameter control_file;

# Consultar local dos Datafiles
SQL> set line 200;
SQL> col name for a50;
SQL> select FILE#, TS#, name from v$datafile;

# Verificar status dos Redo Logs
SQL> col member for a40;
SQL> select GROUP#, STATUS, TYPE, MEMBER from v$logfile;

# Pular de Redo Log
SQL> alter system switch logfile;

# Checar todos Redo Log e inativar
SQL> alter system checkpoint;

# Listar instâncias
SQL> select sys_context('USERENV','INSTANCE_NAME') from dual;

# Listar Databases
SQL> SELECT NAME FROM v$database;

# Modos de login

sqlplus / as sysdba

sqlplus sys as sysdba

sqlplus system/senha@instancia

# Iniciar instancia, verificar status, baixar

SQL> startup;

SQL> shutdown immediate

SQL> select status from v$instance;

SQL> select INSTANCE_NAME, status from v$instance;

# Verificar arquivos Control File

SQL> show parameter control;

SQL> show parameter control_file;

# Consultar local dos Datafiles

SQL> set line 200;

SQL> col name for a50;

SQL> select FILE#, TS#, name from v$datafile;

# Verificar status dos Redo Logs

SQL> col member for a40;

SQL> select GROUP#, STATUS, TYPE, MEMBER from v$logfile;

# Pular de Redo Log

SQL> alter system switch logfile;

# Checar todos Redo Log e inativar

SQL> alter system checkpoint;

# Listar instâncias

SQL> select sys_context('USERENV','INSTANCE_NAME') from dual;

# Listar Databases

SQL> SELECT NAME FROM v$database;

# Desbloquear usuario
SQL> set line 200;
SQL> col username for a50;
SQL> select username, account_status from dba_users where ACCOUNT_STATUS LIKE '%EXPIRED%';
SQL> ALTER USER zabbix IDENTIFIED BY senha;
SQL> ALTER USER zabbix ACCOUNT UNLOCK;

# Desbloquear usuario

SQL> set line 200;

SQL> col username for a50;

SQL> select username, account_status from dba_users where ACCOUNT_STATUS LIKE '%EXPIRED%';

SQL> ALTER USER zabbix IDENTIFIED BY senha;

SQL> ALTER USER zabbix ACCOUNT UNLOCK;

# RMAN

# Logar no RMAN
$ rman target /

# Apagar archivelog
$ RMAN> delete archivelog all;

# Caso seja apagado via rm os archives deve ser executado o comando abaixo para acertar as referencias
$ RMAN> crosscheck archivelog all;

# RMAN

# Logar no RMAN

$ rman target /

# Apagar archivelog

$ RMAN> delete archivelog all;

# Caso seja apagado via rm os archives deve ser executado o comando abaixo para acertar as referencias

$ RMAN> crosscheck archivelog all;

9 - Pacote rlwrap

O pacote "rlwrap" acerta o sqlplus para podemos trabalhar o histórico e maior facilidade no manuseio dentro dele:

[root@srv01 ~]# wget http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-7.noarch.rpm
[root@srv01 ~]# rpm -ivh epel-release-7-7.noarch.rpm
[root@srv01 ~]# yum -y install rlwrap
[root@srv01 ~]# vim /home/oracle/.bashrc
                    alias sqlplus="rlwrap sqlplus"

[root@srv01 ~]# wget http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-7.noarch.rpm

[root@srv01 ~]# rpm -ivh epel-release-7-7.noarch.rpm

[root@srv01 ~]# yum -y install rlwrap

[root@srv01 ~]# vim /home/oracle/.bashrc

alias sqlplus="rlwrap sqlplus"

10 - ADRCI Oracle

http://www.dba-oracle.com/t_linux_oracle_adr_directory.htm

14400 = minutos = 30 dias
720 = horas
8760 = horas

[root@oracle]# su - oracle                                                                                                                  
[oracle@oracle ~]$ adrci
adrci> set home diag/rdbms/orcl/orcl
adrci> select SHORTP_POLICY,LONGP_POLICY from ADR_CONTROL;
adrci> set control (SHORTP_POLICY=168);
adrci> set control (LONGP_POLICY=720);
adrci> purge -age 14400

[root@oracle]# su - oracle

[oracle@oracle ~]$ adrci

adrci> set home diag/rdbms/orcl/orcl

adrci> select SHORTP_POLICY,LONGP_POLICY from ADR_CONTROL;

adrci> set control (SHORTP_POLICY=168);

adrci> set control (LONGP_POLICY=720);

adrci> purge -age 14400