Extract DDL Oracle

-- -----------------------------------------------------------------------------------

-- File Name    : https://oracle-base.com/dba/script_creation/user_ddl.sql

-- Author       : Tim Hall

-- Description  : Displays the DDL for a specific user.

-- Call Syntax  : @user_ddl (username)

-- Last Modified: 07/08/2018

-- -----------------------------------------------------------------------------------

set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on

column ddl format a1000

begin

   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);

   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);

end;

/

 

variable v_username VARCHAR2(30);

exec :v_username := upper('&1');

select dbms_metadata.get_ddl('USER', u.username) AS ddl

from   dba_users u

where  u.username = :v_username

union all

select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl

from   dba_ts_quotas tq

where  tq.username = :v_username

and    rownum = 1

union all

select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl

from   dba_role_privs rp

where  rp.grantee = :v_username

and    rownum = 1

union all

select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl

from   dba_sys_privs sp

where  sp.grantee = :v_username

and    rownum = 1

union all

select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl

from   dba_tab_privs tp

where  tp.grantee = :v_username

and    rownum = 1

union all

select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl

from   dba_role_privs rp

where  rp.grantee = :v_username

and    rp.default_role = 'YES'

and    rownum = 1

union all

select to_clob('/* Start profile creation script in case they are missing') AS ddl

from   dba_users u

where  u.username = :v_username

and    u.profile <> 'DEFAULT'

and    rownum = 1

union all

select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl

from   dba_users u

where  u.username = :v_username

and    u.profile <> 'DEFAULT'

union all

select to_clob('End profile creation script */') AS ddl

from   dba_users u

where  u.username = :v_username

and    u.profile <> 'DEFAULT'

and    rownum = 1

/

Instalacion RAC ORacle 19c ++ en Linux

Pre-Requisitos Basicos

--Storage --

/u01 de 100GB

5 discos de 10 GB cada uno para los Vote+OCR

2 discos de 100 GB para la BD de metada del Rac- Colocar en redundancia NORMAL al crear el diskgroup

N Cantidad de Discos para +DATA y +FRA

/tmp 30 GB almenos

---------------------------------------

--Networking

2 Ip en segmento publico

2 Ip en un segmento privado aislado solo para el RAC

3 Ip en el segmento publico para el DNS

2 Ip en el segmento publico para las Vip

Se debe crear y asignar en el Dns la resolucion de nombre del Scan: 

Ejemplo: scan-prod:

[oracle@dbserver01 ~]$ nslookup scan-prod

Server:         10.10.1.140

Address:        10.10.1.140#53

Name:   scan-prod.local.db

Address: 10.129.2.82

Name:   scan-prod.local.db

Address: 10.129.2.83

Name:   scan-prod.local.db

Address: 10.129.2.81

---------------------------------------

Deshabilitar SElinux
vi /etc/selinux/config   --- colocar en disable

Deshablitar Firewall

service firewalld stop

service firewalld disable

Creacion de Grupos de S.O.
groupadd oinstall -g 501
groupadd dba -g 502
groupadd oper -g 503
groupadd backupdba -g 504
groupadd dgdba -g 505
groupadd kmdba -g 506
groupadd asmadmin -g 507
groupadd asmdba -g 508

groupadd asmoper -g 509

Creacion de usuarios de S.O.
--------- where the installation owner is grid and the OSASM group asmadmin -------------------

useradd -u 501 -g oinstall -G asmadmin,asmdba,asmoper,dba grid

useradd -u 502 -g oinstall -G dba,oper,asmdba,backupdba,dgdba,kmdba oracle



Directorios para los binarios:

mkdir -p /u01/app/19.0.0/grid

mkdir -p /u01/app/grid
mkdir -p /u01/app/oracle

chown -R grid:oinstall /u01

chown oracle:oinstall /u01/app/oracle

chmod -R 775 /u01/

Editar archivos etc/hosts en los servidores:

--- IP PUBLICAS ---

10.129.2.72    server01

10.129.2.73    server02

--- IP PRIVADAS --

10.10.10.1    server01-priv

10.10.10.2   server02-priv

-- IP VIPS --

10.129.2.77    server01-vip

10.129.2.78    server02-vip

Nota no agregar el Servicio para el Scan #  Servicio scan: cl-scan-ip : 10.129.2.90, 10.129.2.91, 10.129.2.92

Configurar Interfaces de RED

#nmtui 

#nm-connection-editor 

export CV_ASSUME_DISTID=OEL7.8

[root@dsib0252]# cd /usr/bin
[root@dsib0252 bin]# mv scp scp.orig
[root@dsib0252 bin]# echo "/usr/bin/scp.orig -T \$*" > /usr/bin/scp
[root@dsib0252 bin]# chmod 555 /usr/bin/scp
[root@dsib0252 bin]# cat scp
/usr/bin/scp.orig -T $*

Convert Physical Standby into Snapshot Standby

 Convirtiendo una BD Standby en una Snapshot para pruebas.

Paso 0 

Asgurate que tengas suficiente espacio en el area de RECO para guardar los ARC que la producctiva generar durante la ventana

select * FROM V$FLASH_RECOVERY_AREA_USAGE;

select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

On Primary:

Genera un par de ARC y valida que sean aplicados correctamente en tu Standby

alter system archive log current;

On standby:
===========
alter database recover managed standby database cancel;
select name, open_mode from v$database; ----make sure its mounted
select log_mode,flashback_on from v$database;
CREATE RESTORE POINT RESTORE_PRE_SNAPSHOT GUARANTEE FLASHBACK DATABASE;
alter database convert to snapshot standby;
alter database open;  
select name, open_mode, database_role from v$database;


On standby:
===========
Do TESTS

On standby:
===========
select name, open_mode, database_role from v$database;
shut immediate;
startup mount;
alter database convert to physical standby;
shutdown immediate
startup mount;
select name, open_mode from v$database; 
DROP RESTORE POINT RESTORE_PRE_SNAPSHOT;  --borra los puntos de fashback
alter database recover managed standby database disconnect; or alter database recover managed standby database using current logfile disconnect from session;