VNC Server Install

 dnf install tigervnc-server tigervnc-server-module

optional checking : dnf group install "Server with GUI"

vncpasswd

vi /etc/tigervnc/tigervnc.users

need added 

:1=oracle

vi /etc/tigervnc/tigervnc-config-defaults

need added

session=gnome
geometry=1280x1024

systemctl daemon-reload

systemctl enable --now vncserver@:1.service

optional : cp /lib/systemd/system/vncserver@.service /etc/systemd/system/vncserver@.service 

systemctl start --now vncserver@:1.service

systemctl status --now vncserver@:1.service

Business Intelligence

 [oracle@alamin bin]$ ./datamodel.sh uploadrpd -I /home/oracle/voucher_pool.rpd -SI ssi -U weblogic -P weblogic123

Oracle ASM Configure

 yum install -y https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm

yum install -y oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm

oracle-rdbms-server-11gR2-preinstall (For 11g Forms)

systemctl stop firewalld
systemctl disable firewalld

groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper 
groupadd -g 54327 asmdba
groupadd -g 54328 asmoper
groupadd -g 54329 asmadmin
groupadd -g 54330 racdba

useradd oracle
passwd oracle
useradd grid
passwd grid

usermod -g oinstall -G dba,asmadmin,asmdba,racdba oracle
usermod -g oinstall -G dba,asmadmin,asmdba,asmoper grid

Manually install pre-requisites

yum install -y bc    
yum install -y binutils
#yum install -y compat-libcap1
yum install -y compat-libstdc++-33
#yum install -y dtrace-modules
#yum install -y dtrace-modules-headers
#yum install -y dtrace-modules-provider-headers
#yum install -y dtrace-utils
yum install -y elfutils-libelf
yum install -y elfutils-libelf-devel
yum install -y fontconfig-devel
yum install -y glibc
yum install -y glibc-devel
yum install -y ksh
yum install -y libaio
yum install -y libaio-devel
#yum install -y libdtrace-ctf-devel
yum install -y libXrender
yum install -y libXrender-devel
yum install -y libX11
yum install -y libXau
yum install -y libXi
yum install -y libXtst
yum install -y libgcc
yum install -y librdmacm-devel
yum install -y libstdc++
yum install -y libstdc++-devel
yum install -y libxcb
yum install -y make
yum install -y net-tools # Clusterware
yum install -y nfs-utils # ACFS
yum install -y python # ACFS
yum install -y python-configshell # ACFS
yum install -y python-rtslib # ACFS
yum install -y python-six # ACFS
yum install -y targetcli # ACFS
yum install -y smartmontools
yum install -y sysstat
yum install -y unixODBC

 

# New for OL8

yum install -y libnsl
yum install -y libnsl.i686
yum install -y libnsl2
yum install -y libnsl2.i686

useradd grid
passwd grid

mkdir -p /u01/app/grid/19c/grid_home
chown -R grid:oinstall /u01
chmod -R 775 /u01/

# Grid bash profile

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/grid
export ORACLE_HOME=/u01/app/grid/19c/grid_home
export ORACLE_SID=+ASM
export ORACLE_TERM=xterm
export BASE_PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$BASE_PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

#Oracle bash profile

export TMP=/tmp
export TMPDIR=$TMP
export inventory_loc=/u01/app/oraInventory
export inst_group=dba
export ORACLE_HOSTNAME=alamin
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/19c/db_1
export ORACLE_SID=orcl
#export DATA_DIR=/u02/oradata
JAVA_HOME=/u02/middleware/jdk1.8.0_391
export PATH=/usr/sbin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH:$JAVA_HOME/bin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

yum -y install wget oracleasm kmod-oracleasm oracleasm-support

fdisk /dev/sdb
fdisk /dev/sdc
fdisk /dev/sdd

oracleasm init
oracleasm configure -i

oracleasm createdisk ASMDISK_DATA /dev/sdb1
oracleasm createdisk ASMDISK_DATA1 /dev/sdc1
oracleasm createdisk ASMDISK_FRA /dev/sdd1

oracleasm scandisks

unzip asm binary 

cd /u01/app/19/grid/cv/rpm/
rpm -ivh cvuqdisk-1.0.10-1.rpm

Install grid binary

chown -R grid:asmadmin /dev/sdb1
chown -R grid:asmadmin /dev/sdc1
chown -R grid:asmadmin /dev/sdd1

chmod -R 660 /dev/sdb1
chmod -R 660 /dev/sdc1
chmod -R 660 /dev/sdd1

vi /usr/local/bin/set_permission.sh
chmod +x /usr/local/bin/set_permission.sh

vi /etc/systemd/system/set_permission.service

[Unit]
Description=Fix Raw Disk Permission

[Service]
Type=oneshot
ExecStart=/usr/local/bin/set_permission.sh

[Install]
WantedBy=multi-user.target

systemctl daemon-reload
systemctl enable set_permission.service

cd /u01/app/19c/grid_home/crs/install
./roothas.sh

export CV_ASSUME_DISTID=OEL7.8

XML Data Uploading in oracle database

 CREATE TABLE tablexml1 ( xml_data  XMLTYPE) ; 

create directory xml_dir as 'G:\';

Demo xml data

<records>
<record>
<column1>Value1</column1>
<column2>123</column2>
</record>
<record>
<column1>Value2</column1>
<column2>456</column2>
</record>
</records>

create or replace PROCEDURE load_xml (p_dir       IN  VARCHAR2,
                                  p_filename  IN  VARCHAR2) AS
 l_bfile  BFILE := BFILENAME(p_dir, p_filename);
 l_clob   CLOB;

 l_dest_offset   INTEGER := 1;
 l_src_offset    INTEGER := 1;
 l_bfile_csid    NUMBER  := 0;
 l_lang_context  INTEGER := 0;
 l_warning       INTEGER := 0;
 BEGIN
  DBMS_LOB.createtemporary (l_clob, TRUE);

 DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
 -- loadfromfile deprecated.
 -- DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
 DBMS_LOB.loadclobfromfile (
   dest_lob      => l_clob,
   src_bfile     => l_bfile,
   amount        => DBMS_LOB.lobmaxsize,
   dest_offset   => l_dest_offset,
   src_offset    => l_src_offset,
   bfile_csid    => l_bfile_csid ,
   lang_context  => l_lang_context,
   warning       => l_warning);
   DBMS_LOB.fileclose(l_bfile);

  INSERT INTO tablexml1(
   xml_data
  )
 VALUES (
    XMLTYPE.createXML(l_clob)
 );


--delete tablexml2;
/*
insert into tablexml2       
SELECT xt.*
FROM   tablexml1 x, 
       XMLTABLE('/records/record' 
         PASSING x.xml_data 
         COLUMNS  
           column1     VARCHAR2(10)  PATH 'column1', 
           column2     number PATH 'column2'
         ) xt ; 

delete tablexml1;*/

 COMMIT;

 DBMS_LOB.freetemporary (l_clob);
 END load_xml;

SELECT xt.*

FROM   tablexml1 x, 

       XMLTABLE('/records/record' 

         PASSING x.xml_data 

         COLUMNS  

           column1     VARCHAR2(4)  PATH 'column1', 

           column2     VARCHAR2(10) PATH 'column2'

         ) xt ; 

exec load_xml('XML_DIR','data_file.xml');

SELECT XMLELEMENT("Emp", XMLELEMENT("name", e.first_name ||' '|| e.last_name),

                           XMLELEMENT ( "hiredate", e.hire_date)) AS "result" 

FROM employees e 

WHERE employee_id > 200 ;

JSON

CREATE OR REPLACE PROCEDURE get_customer_json (
customer_id IN NUMBER,
json_output OUT CLOB -- OUT parameter for JSON output
) IS
BEGIN
SELECT JSON_OBJECT(
'Customer_Name' VALUE c.cust_name,
'Customer_ID' VALUE c.cust_id,
'Accounts' VALUE JSON_ARRAYAGG(a.acct_id)
)
INTO json_output
FROM customers c, accounts a
WHERE c.cust_id =a.cust_id
GROUP BY c.cust_name, c.cust_id;
END;
⚡ Call Procedure ⚡
DECLARE
l_json CLOB;
BEGIN
get_customer_json(1, l_json);
DBMS_OUTPUT.PUT_LINE(l_json);
END;
⚡ Output ⚡
{"Customer_Name":"Alice","Customer_ID":1,"Accounts":[101,102]}

DeploymentRuleSet creation for java security

 Java Signing directory
cd D:\middleware\oracle_common\jdk\bin

Making ruleset.xml

<ruleset version="1.0+">
<rule>
    <id location="http://localhost:9001" />
    <action permission="run" version="1.8*" />
</rule>
</ruleset>

Making jar file
jar.exe -cvf DeploymentRuleSet.jar ruleset.xml

Making jks file
keytool.exe -genkey -alias Selfsigned -keystore "Selfsigned.jks" -keyalg RSA -keysize 2048 -startdate "2000/01/01 00:00:00" -validity 18262

Making cer file
keytool.exe -export -file "Selfsigned.cer" -alias Selfsigned -keystore "Selfsigned.jks"
c:\windows\system32\certutil.exe -addstore "Root" .\Selfsigned.cer

Jar signer
jarsigner.exe -keystore "Selfsigned.jks" -signedjar "DeploymentRuleSet.jar" DeploymentRuleSet.jar selfsigned

Testing
jarsigner -verify -verbose -certs DeploymentRuleSet.jar
jarsigner -verify -keystore Selfsigned.jks DeploymentRuleSet.jar

Oracle Wallet Setup

 select * from v$encryption_wallet

mkdir -p /home/oracle/wallet

export TNS_ADMIN=/u01/app/oracle/product/19.0.0/dbhome_1/network/admin

Add the following entries into the client "/u01/app/oracle/product/19.0.0/dbhome_1/network/admin" file, with your preferred wallet location.

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /home/oracle/wallet)
     )
   )
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

orapki wallet create -wallet "/home/oracle/wallet" -pwd "password" -auto_login

or 

mkstore -wrl "/home/oracle/wallet" -create

orapki wallet add -wallet D:\app\oracle\admin\orcl\wallet -trusted_cert -cert D:\app\oracle\admin\orcl\wallet\WE2.crt -pwd password

orapki wallet display -wallet D:\app\oracle\admin\orcl\wallet -pwd password

mkstore -wrl "/home/oracle/wallet" -createCredential orcl test test

mkstore -wrl "/home/oracle/wallet" -createCredential orcl2 test2 test2

mkstore -wrl "/home/oracle/wallet" -listCredential

mkstore -wrl <wallet_location> -modifyCredential <dbase_alias> <username> <password>

mkstore -wrl <wallet_location> -deleteCredential <db_alias>

administer key management create keystore 'C:\wallet' identified by password;

administer key management set keystore open identified by password container=all

Connection syntax "/@db_alias"

sqlplus /@orcl

sqlplus /@orcl2

begin
apex_instance_admin.validate_email_config;
end;

BEGIN

  DBMS_NETWORK_ACL_ADMIN.create_acl (

    acl          => 'mail.xml', 

    description  => 'Allow connections using UTL_TCP',

    principal    => 'APEX_240200',

    is_grant     => TRUE, 

    privilege    => 'connect');

end;

begin

  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl         => 'mail.xml',

    host        => '*'

);

end;

BEGIN

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

 host => '*',

 ace  =>  xs$ace_type(privilege_list => xs$name_list('connect'),

                      principal_name => 'APEX_240200',

                      principal_type => xs_acl.ptype_db)); 

END;

DECLARE

  l_acl_name         VARCHAR2(30) := 'utl_tcp01.xml';

  l_ftp_server_ip    VARCHAR2(20) := 'smtp.office365.com';

  l_ftp_server_name  VARCHAR2(20) := 'smtp.office365.com'; 

  l_username         VARCHAR2(30) := 'APEX_240200';

BEGIN

  DBMS_NETWORK_ACL_ADMIN.create_acl (

    acl          => l_acl_name, 

    description  => 'Allow connections using UTL_TCP',

    principal    => l_username,

    is_grant     => TRUE, 

    privilege    => 'connect',

    start_date   => SYSTIMESTAMP,

    end_date     => NULL);

  COMMIT;

  DBMS_NETWORK_ACL_ADMIN.add_privilege ( 

    acl         => l_acl_name, 

    principal   => l_username,

    is_grant    => FALSE, 

    privilege   => 'connect', 

    position    => NULL, 

    start_date  => NULL,

    end_date    => NULL);

  COMMIT;

  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl         => l_acl_name,

    host        => l_ftp_server_ip, 

    lower_port  => NULL,

    upper_port  => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl         => l_acl_name,

    host        => l_ftp_server_name, 

    lower_port  => NULL,

    upper_port  => NULL);

  COMMIT;

END;

BEGIN

   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(

      acl => '/sys/acls/utl_tcp01.xml');

END;

declare

l_body_html clob;

l_body clob;

begin

l_body:='<p>This is Header</p>';

l_body_html:='<html><head></head><body>'||

'<p>This is paragraph</p>'||'<br>

<h1>This is a header</h1>'||

'</body></html>';

  apex_mail.send(

    p_to   => 'mdalamin@lantaburgroup.com',

    p_from => 'mdalamin@lantaburgroup.com',

    p_body=>l_body,

    p_body_html => l_body_html,

    p_subj => 'Test Mail '||to_char(SYSDATE,'YY.MM.DD HH12AM:MI:SS'));

  apex_mail.push_queue();  

end;

QR CODE IN ORACLE REPORT BUILDER

 https://drive.google.com/drive/folders/1Kv901xiIp7dbzcK71CVgHzOylrGmAw-b?usp=sharing

create directory qr_dir as 'D:\QR';
grant read,write on directory qr_dir to test;

@01-ZT_QR_PACKAGE.plb

@02-ZT_QR_BODY.plb

@03-bmp2jpg java

@04-f_bmp2jpg

create or replace procedure qr_code(p_text varchar2) as
lbQR blob;
begin
  lbQR := f_bmp2jpg( 
ZT_QR.F_QR_AS_BMP(
p_data => p_text,
p_error_correction => 'M')
);
    ZT_QR.p_save_file(lbQR, p_text||'.jpg' ,'QR_DIR');
END;

function CF_QRFormula return Char is
begin
  RETURN 'D:\QR\'||:P_QR||'.jpg';
end;

Drop database manually

startup mount;

OR

RMAN>sql 'alter system enable restricted session'; 
Drop database including backups noprompt;

Or sql command
startup mount restrict;
drop database;

Cold Backup in Oracle database

 

Create directory where to install.

mkdir -p /u01/app/oracle/oradata

Move pfile/spfile to $ORACLE/HOME/dbs

scp initorcl.ora spfileorcl.ora oracle@machine_name:$ORACLE/HOME/dbs

Rename controlfile location name in parameter file

Copy file controlfile, datafile, logfile

scp * oracle@machine_name:/u01/app/oracle/oradata

startup mount;

Rename datafile,logfile like below.

alter database rename file '/u01/app/oracle/system01.dbf' to '/u01/app/oracle/oradata/system01.dbf';

alter database rename file '/u01/app/oracle/sysaux01.dbf' to '/u01/app/oracle/oradata/sysaux01.dbf';

alter database rename file '/u01/app/oracle/log01.dbf' to '/u01/app/oracle/oradata/log01.dbf';

alter database open;

Archivelog File

 archive log list

show parameter archive_dest

alter system set log_archive_dest_1= 'LOCATION=F:\labs\archivelog' scope=both

list archivelog all; 

list copy of archivelog until time 'SYSDATE-1'; 

list copy of archivelog from time 'SYSDATE-1'; 

list copy of archivelog from time 'SYSDATE-1' until time 'SYSDATE-2'; 

list copy of archivelog from sequence 1000; 

list copy of archivelog until sequence 1500; 

list copy of archivelog from sequence 1000 until sequence 1500;

delete archivelog all;

delete archivelog until time 'SYSDATE-1'; 

delete archivelog from time 'SYSDATE-1'; 

delete archivelog from time 'SYSDATE-1' until time 'SYSDATE-2'; 

delete archivelog from sequence 1000; 

delete archivelog until sequence 1500; 

delete archivelog from sequence 1000 until sequence 1500;

delete noprompt archivelog until time 'SYSDATE-1';

crosscheck archivelog all;

list expired archivelog all;
delete expired archivelog all;

restore database preview;
restore datafile 14 preview;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW;
RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW SUMMARY;

run{
recover table "LABS"."MM_ITEM_STOCK" until time "to_date('14-09-2023 08:16:00','dd-mm-yyyy hh24:mi:ss')"
auxiliary destination 'g:\oracle_backup';
}

RUN {
set until time "to_date('08-AUG-2023 09:00', 'DD-MON-YYYY HH24:MI')";
restore database;
recover database;
}

Duplicate a Database Using RMAN in Oracle Database 12c Release 2

startup mount;

alter database archivelog;

archive log list;

show parameter db_recovery_file

needed in both server

mkdir -p /u01/app/oracle/fast_recovery_area

alter system set db_recovery_file_dest_size=5g scope=both;

alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

scp orapworcl oracle@[auxiliary databasae]:/u01/app/oracle/product/12.2.0/db_1/dbs

create pfile='/home/oracle/initorcl.ora' from spfile;

scp initorcl.ora oracle@[auxiliary databasae]:/u01/app/oracle/product/12.2.0/db_1/dbs

listener configuration and tns configuration in both server such as global database name orcl,clone

mkdir -p /u01/app/oracle/admin/orcl/adump

mkdir -p /u01/app/oracle/oradata/orcl/

setting below parameter in duplicate database pfile

*.db_recovery_file_dest_size=5368709120

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'

in duplicate database pfile like this

orcl.__data_transfer_cache_size=0

orcl.__db_cache_size=603979776

orcl.__inmemory_ext_roarea=0

orcl.__inmemory_ext_rwarea=0

orcl.__java_pool_size=16777216

orcl.__large_pool_size=33554432

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=671088640

orcl.__sga_target=989855744

orcl.__shared_io_pool_size=50331648

orcl.__shared_pool_size=268435456

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='12.2.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'

*.db_recovery_file_dest_size=5368709120

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.diagnostic_dest='/u01/app/oracle'

*.db_block_size=8192

*.db_name='orcl'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.local_listener='LISTENER_ORCL'

*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'

*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl'

*.memory_target=1570m

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.processes=320

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

startup pfile=/u01/app/oracle/product/12.2.0/db_1/dbs/initorcl.ora nomount

rman target sys/sys123@orcl auxiliary sys/sys123@clone

or 

rman target sys/sys123@orcl auxiliary /

duplicate database to 'orcl' from active database nofilenamecheck;

Data import using db link

create public database link link_orcl
connect to scott
identified by ***
usint 'local';

grant imp_full_database,exp_full_database to scott;
impdp network_link=link_orcl
impdp network_link=link_orcl directory=test logfile=test.log
impdp network_link=link_orcl directory=test logfile=test.log tables=dept

Data Guard

jPrimary database

startup mount;
alter database archivelog;
archive log list;

show parameter db_recovery_file

alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
alter system set db_recovery_file_dest_size=5g scope=both;


alter database force logging;

alter system switch logfile;

alter database add standby logfile '/u01/app/oracle/oradata/orcl/sbylog4.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/sbylog5.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/sbylog6.log' size 200M;
alter database add standby logfile '/u01/app/oracle/oradata/orcl/sbylog7.log' size 200M;


alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl' scope=both

alter system set log_archive_dest_2='service=stand async valid_for=(online_logfile,primary_role) db_unique_name=stand' scope=both;

alter system set standby_file_management=auto;

alter system set log_archive_config='DG_CONFIG=(orcl,stand)';

alter system set fal_client=orcl;

alter system set fal_server=stand;

scp orapworcl oracle@192.168.1.81:/u01/app/oracle/product/12.2.0/db_1/dbs/orapwstand

create pfile='/home/oracle/initstand.ora' from spfile;

Demo pfile
stand.__data_transfer_cache_size=0
stand.__db_cache_size=637534208
stand.__inmemory_ext_roarea=0
stand.__inmemory_ext_rwarea=0
stand.__java_pool_size=16777216
stand.__large_pool_size=33554432
stand.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
stand.__pga_aggregate_target=671088640
stand.__sga_target=989855744
stand.__shared_io_pool_size=50331648
stand.__shared_pool_size=234881024
stand.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='12.2.0'
*.control_files='/u01/app/oracle/oradata/stand/control01.ctl','/u01/app/oracle/oradata/stand/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'
*.db_unique_name='stand'
*.db_recovery_file_dest_size=5368709120
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='STAND'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(orcl,stand)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=stand'
*.log_archive_dest_2='service=orcl async valid_for=(online_logfile,primary_role) db_unique_name=orcl'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stand'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/stand'
*.memory_target=1570m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Directory standby
-----------------
/u01/app/oracle/fast_recovery_area/stand
/u01/app/oracle/admin/orcl/adump
/u01/app/oracle/oradata/stand

startup pfile=initstand.ora nomount

rman target sys/sys123@orcl auxiliary sys/sys123@stand

duplicate target database for standby from active database dorecover NOFILENAMECHECK;



ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

select open_mode, controlfile_type from v$database;

alter database open read only;

alter database recover managed standby database cancel;

select * from v$logfile;

Performance mode change step
==============================================================
In Primary

alter system set log_archive_dest_2='service=stand affirm lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=stand' scope=both;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
alter database open;

In Standby
alter system set log_archive_dest_2='service=orcl affirm lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=orcl' scope=both;


SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;

Performance Check


select SOURCE_DBID,name,value,unit,TIME_COMPUTED,DATUM_TIME,con_id from v$dataguard_stats

select thread#,max(sequence#) from v$archived_log group by thread#;


select thread#, max(sequence#) "Last Primary Seq Generated" 
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;


select thread#, max(sequence#) "Last Standby Seq Applied" 
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied='YES' 
group by thread# order by 1;

select * from v$dataguard_status;

SELECT a.resetlogs_id, DECODE (a.thread#, 1, 'node1', 2, 'node2') HOST, b.last_seq prmy_last_file,
      a.applied_seq stdby_last_file, CASE WHEN b.last_seq - a.applied_seq > 2 THEN '=>' ELSE to_char(b.last_seq - a.applied_seq) END archive_difference, TO_CHAR (a.latest_apply_time, 'dd/mm/yyyy hh24:mi:ss') stdby_latest_time
 FROM (SELECT   resetlogs_id, thread#, MAX (sequence#) applied_seq, MAX (next_time) latest_apply_time
           FROM v$archived_log
          WHERE applied = 'YES'
       GROUP BY resetlogs_id, thread#) a,
      (SELECT   resetlogs_id, thread#, MAX (sequence#) last_seq
           FROM v$archived_log
       GROUP BY resetlogs_id, thread#) b
WHERE a.thread# = b.thread#
ORDER BY a.thread#;

select max(sequence#),thread#, applied from v$archived_log group by thread#, applied;

Errors in file /u01/app/oracle/diag/rdbms/stand/orcl/trace/orcl_ora_5913.trc:
ORA-00313: open failed for members of log group 7 of thread 0
ORA-00312: online log 7 thread 0: '/u01/app/oracle/oradata/orcl/sbylog7.log'
ORA-27037: unable to obtain file status


alter system set standby_file_management=manual;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;

alter system set standby_file_management=auto;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

Switchover Operation Procedure: 

Status cheque:
select name,open_mode,database_role from v$database;
select switchover_status from v$database;

On primary :
Startup mount
alter database convert to pyysical standby;
shutdown immediate
startup nomount
alter database mount standby database
alter database recover managed standby database disconnect from session;

On standby :
recover managed standby database finish;
alter database commit to switchover to primary;

Required IPC RDMAV_FORK_SAFE environment not set

C:\Users\Admin>adrci
adrci>show homes
adrci> set home diag\rdbms\stand\lan
adrci> show control
adrci> set control (SHORTP_POLICY=72);
adrci> purge -age 4320 -type trace
adrci> purge -age 1440   (for older than 1 days)
adrci> show alert -tail 20

Service Create in windows
oradim -new -sid lan -SYSPWD sys321 -pfile D:\app\oracle\product\db_1\database\initlan.ora




Add static entry in Standby Listener

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (PROGRAM = extproc)
    )
   (SID_DESC =
      (SID_NAME = ucas)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
   )
 )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine02)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

DML Checking in view

select table_name,column_name,updatable,insertable,deletable from
USER_UPDATABLE_COLUMNS where table_name='MM_ITEM_INFO_V'


CREATE VIEW EMP_SAL AS SELECT EMPNO, ENAME ,SAL FROM EMP WHERE SAL
< 2000 WITH CHECK OPTION;