Saturday, June 18, 2022

How to create a Data Guard Broker Configuration in Oracle 19c

 Here an article from my colleague Fernando:

https://rootfan.com/data-guard-broker-configuration-oracle-19c/#more-1677

And just another useful one, in a virtualized environment:

https://oracledbwr.com/step-by-step-guide-to-install-oracle-19c-rac-installation-using-virtualbox/

Oracle Clusterware 19C

Official documentation, all you need to know about all the architectures and possibilities of Oracle Clusterware 19C.

Here you will find Fleet Patching and Provisioning.

https://docs.oracle.com/en/database/oracle/oracle-database/19/cwadd/clusterware-administration-and-deployment-guide.pdf

DATAGUARD 19C

Official documentation, all you need to know about all the architectures and possibilities of Oracle Dataguard 19C.

Fleet Patching and Provisioning commands

Formerly “Rapid Home Provisioning (RHP)”, thats the reason for the command rhpctl.

This artefact consists of a server connected to an ACFS where clusterware and RDBMS binaries are stored for distribution and installation on client nodes.

The following is a summary of the main commands that will help us install and update the software

srvctl start rhpserver

srvctl add rhpserver -storage /// -diskgroup xxx

rhpctl import image… -imagetype -path


rhpctl add image -image abc -workingcopy abcwc


rhpctl add workingcopy -workingcopy WC1 -image iii -storagetype LOCAL -path /ora19


rhpctl upgrade gihome -sourcewc aa -destwc bb


rhpctl upgrade database


rhpctl zdtupgrade database…

rhpctl addpdb database…

rhpctl deletepdb database…


Bynaries:rhpctl add workingcopy -image db19 -path /u01…. -client xxx -oraclebase /u01.. - workingcopy w1


RDBMS:rhpctl add database -workingcopy w1 -dbname db - node a,b -dbtype RAC- datafiledestination xx_DG


rhpctl move database -sourcewc a -patchedwc a_psu

Thursday, May 12, 2011

CHANGING THE COLUMNS WIDTH USING CSSCAN

After executing CSSCAN some files are generated (.err, .out) informing about the columns that will not fit in the target charset(usually ALxUTFz)and, furthermore, in the host schema CSMIG (@csminst.sql) the same information is stored, so we can use it to generate the modifications needed:

[CAT ]Després de passar el CSSCAN, com a subproducte, i a part dels fitxerets que es generen, queda la mateixa informació a l'esquema CSMIG (s'ha de crear abans de passar el CSSCAN [@csminst.sql]). Doncs aquesta info es pot utilitzar per generar les ordres d'ampliació de les columnes que no hi cabran al nou codi de caràcters.


cat prova.err|grep "Number of Exceptions"|grep -v ": 0"|wc –l

This select produces the instructions for the tables that can't fit in the actual column size:

set linesize 222
SELECT 'ALTER TABLE '||PROP||'.'||TAULA||' MODIFY '
||dba_tab_columns.COLUMN_NAME||' '||
dba_tab_columns.DATA_TYPE||'('||TAMANY||');' DDL
FROM
(select DISTINCT U.USERNAME PROP, O.OBJECT_NAME TAULA,
T.COLUMN_ID COL_ID, max(MAXSIZ) TAMANY
from CSM$COLUMNS C, dba_users U,
dba_objects O, dba_tab_columns T
where C.ERRCNT > 0 and
C.USR#=U.USER_ID and
C.OBJ#=O.OBJECT_ID and
C.COL#=T.COLUMN_ID
group by U.USERNAME, O.OBJECT_NAME, T.COLUMN_ID),
dba_tab_columns
WHERE PROP = dba_tab_columns.OWNER
AND TAULA = dba_tab_columns.TABLE_NAME
AND COL_ID = dba_tab_columns.COLUMN_ID
ORDER BY 1;

EXAMPLE OUTPUT:
ALTER TABLE DMBATCH.TAULA MODIFY NOM_PERSONA_TIT VARCHAR2(54);
....

Wednesday, April 15, 2009

Searching for a user (enhanced)

Now the same as before but using a Windows batch FOR loop.
The script iterates through the file "oracle.dat", that contains "username password SID" in every line. Then connects with sqlplus using this information and executes "busca.sql" that do and formats the search.


BUSCA.BAT

@echo off
echo Se genera el fichero "%1.out"
echo RESUMEN:
echo ========
@echo off
@echo >%1.out
FOR /F "tokens=1,2,3" %%G IN (oracle.dat) DO (sqlplus -S %%G/%%H@%%I @c:\busca.sql %1>>%1.out)

type %1.out|find "->"

REM ^ List only nontrivial results
type %1.out|find /c /i "%1"
REM ^ Count the results

ORACLE.DAT
(Information separated by ONE space. Can be configured with "option delims=xxx")

system password SID
....


BUSCA.SQL
set pagesize 0
set feed off
set serveroutput on
declare
ins varchar2(10);
hostname varchar2(40);
OK varchar2(40):=NULL;
estado varchar2(40):=NULL;

begin
select UPPER(instance) into ins from v$thread where rownum=1;
select HOST_NAME into hostname from v$instance where rownum=1;
select username,ACCOUNT_STATUS into OK,estado from dba_users where upper(username) like upper('&1');
if ok is not null then
dbms_output.put_line(rpad(ins,9,' ')||'-> '||rpad(hostname,30,' ')||' ---> '||OK||' ('||estado||')');
end if;
exception
when no_data_found then null;
end;
/
exit

EXAMPLE

C:\>busca rman
Se genera el fichero "rman.out"
RESUMEN:
========
AE02 -> aecobde9 ---> RMAN (OPEN)
AE03 -> arepbde1 ---> RMAN (OPEN)
AE05 -> aremsae4 ---> RMAN (OPEN)
PROD -> sun06.sscc.intranet.local ---> RMAN (OPEN)
RMAN -> rman ---> RMAN (OPEN)
RMAN9 -> rman ---> RMAN (OPEN)
XE -> bcnaorarman ---> RMAN (OPEN)
AOLT -> sun03 ---> RMAN (OPEN)
8

Searching for a user

It's usual to have to locate where an user resides in. Here it's a way to do it from my Windows, that have all access permisions. Another way is to install a Linux or emulator and do it with a shell script (some day...).

Result:

C:\>busca perfstat
Se genera el fichero "perfstat.out"
RESUMEN:
========
AE02 -> aecobde9 ---> PERFSTAT (OPEN)
AE03 -> arepbde1 ---> PERFSTAT (OPEN)
AE05 -> aremsae4 ---> PERFSTAT (OPEN)
AE07 -> aecobde9 ---> PERFSTAT (OPEN)
AE08 -> arepbde1 ---> PERFSTAT (OPEN)
AE09 -> arepbde1 ---> PERFSTAT (OPEN)
FINP -> sunfin ---> PERFSTAT (OPEN)
PROD -> sun06.sscc.intranet.local ---> PERFSTAT (OPEN)
RMAN -> rman ---> PERFSTAT (OPEN)
SCENTER1 -> bcnascec1p01 ---> PERFSTAT (OPEN)
AOLT -> sun03 ---> PERFSTAT (OPEN)
APP02 -> aremsar1 ---> PERFSTAT (OPEN)
APP05 -> agenbdr1 ---> PERFSTAT (OPEN)
13

And now the implementation:

C:\>type busca.bat
@echo off
echo Se genera el fichero "%1.out"
echo RESUMEN:
echo ========
echo ***************************>%1.out
echo *** P R O D U C C I O N ***>>%1.out
echo ***************************>>%1.out

sqlplus -S system/password@AE01 @c:\busca.sql %1>>%1.out
sqlplus -S system/password@AE01-10 @c:\busca.sql %1>>%1.out
....
type %1.out|find "->"
type %1.out|find /c /i "%1"


C:\>type busca.sql
set pagesize 0
set feed off
set serveroutput on
declare
ins varchar2(10);
hostname varchar2(40);
OK varchar2(40):=NULL;
estado varchar2(40):=NULL;

begin
select UPPER(instance) into ins from v$thread where rownum=1;
select HOST_NAME into hostname from v$instance where rownum=1;
select username,ACCOUNT_STATUS into OK,estado from dba_users where upper(username) like upper('&1'
);
if ok is not null then
dbms_output.put_line(rpad(ins,9,' ')||'-> '||rpad(hostname,30,' ')||' ---> '||OK||' ('||estado
||')');
end if;
exception
when no_data_found then null;
end;
/
exit