ORASOA side: create table tab1(c1 char(10),c2 char(10),c3 char(10)); begin for i in 1..10000 loop insert into tab1 values(i,’A’,’B’); end loop; end; alter table tab1 add constraint pk_tab1 primary key(c1); create materialized view log on tab1;—– Default is Primary Key SQL> select * from mlog$_tab1 where rownum<5; C1 SNAPTIME$ D O ———- ——— […]
CREATING ORACLE DATABASE OBJECTS CREATING TEMPORARY TABLES SQL>CREATE GLOBAL TEMPORARY TABLE TEMP_EMP (EMPNO NUMBER,ENAME VARCHAR2(10)) CREATING ONE TABLE WITH DATA FROM ANOTHER SQL> create table emp_copy(empno,sal) as select empno,sal from emp; SQL> CREATE TABLE employee_new 2 AS SELECT * FROM employees 3 PARALLEL DEGREE 4 4*NOLOGGING; Table created.SQL> TEXT DATATYPE EXPLAINED SQL> select vsize(sal) […]
SQL> CONN SYS/SYS AS SYSDBA Connected. SQL> set serveroutput on SQL> SQL> set linesize 121 SQL> SQL> DECLARE l_credit_card_no VARCHAR2(19) := ‘1234-5678-9012-3456’; l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_credit_card_no); l_key RAW(128) := utl_raw.cast_to_raw(‘abcdefgh’); l_encrypted_raw RAW(2048); l_decrypted_raw RAW(2048); BEGIN dbms_output.put_line(‘Original : ‘ || l_credit_card_no); l_encrypted_raw := dbms_crypto.encrypt(l_ccn_raw, dbms_crypto.des_cbc_pkcs5, l_key); dbms_output.put_line(‘Encrypted : ‘ || RAWTOHEX(utl_raw.cast_to_raw(l_encrypted_raw))); l_decrypted_raw := dbms_crypto.decrypt(src => l_encrypted_raw, typ => dbms_crypto.des_cbc_pkcs5, key => […]
FAST START FAILOVER (FSFO) CONFIGURATION USING FAR SYNC NODE AS OBSERVER IN DATAGUARD USING DGMGRL 1.For data guard DGMGRL configuration and Far Sync setup please follow below link:- http://clouddba.co/step-step-oracle-active-data-guard-far-sync-rac/ 2.Please check current database status DGMGRL> show database ebilpd1; Database – ebilpd1 Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): EBILPD11 EBILPD12 Database Status: SUCCESS DGMGRL> show […]
Step by Step Oracle Active Data Guard Far Sync RAC Active Data Guard Far Sync, a new capability with Oracle Database 12c, eliminates compromise by extending zero data loss protection to a replica database located at any distance from the primary database. Active Data Guard Far Sync accomplishes this with minimal expense or complexity compared to other extended distance […]
1.First create the shell script mentioning all environmental variables adjusted to your environment. vi dataguard_dr_sync_check.sh #!/bin/bash cd /u01/app/oracle/shells for k in ORCL do export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1 export ORACLE_SID=$k $ORACLE_HOME/bin/sqlplus -S monitor@”$k”/xxx << EOF >> dataguard_dr_sync_check_orcl_`date +\%d\%m\%Y\%H`.log @dataguard_dr_sync_check_orcl.sql exit EOF done 2.For 2 node RAC,below will be dataguard_dr_sync_check_orcl.sql script .For 4 nodes,just add another 2 sql with thread and union […]
RMAN Incremental backup This backups only datafile blocks changed since specified previous backup. 1.This strategy could be followed to make incrementally updated backup .These incrementally updated backup will be used to make updated image copy will all roll forward image. 2.Reduce amount of time to take backup. 3.Save network bandwidth To be able to recover changes to objects created with […]
Automatic Storage Management (ASM) A.Overview of Automatic Storage Management (ASM) Automatic Storage Management (ASM) simplifies administration of Oracle related files by allowing the administrator to reference disk groups rather than individual disks and files, which are managed by ASM. The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to […]
DB CREATION STEPS IN WINDOWS SETTING AN ENVIRONMENT(FOR WINDOWS) ORADIM -new -sid <new_sid> -intpwd oracle -startmode AUTO -pfile c:\oracle\admin\<new_sid>\pfile\init<new_sid>.ora PASSWORD FILE MANAGEMENT Check in the initclone.ora REMOTE_LOGIN_PASSWORDFILE is set to exclusive or shared. If this is set, then a valid passwordfile should exist in ORACLE_HOME/dbs or created using orapwd as orapwd file=/u01/oracle/V816/dbs/orapwV722 password=oracle entries=1 STARTING UP WITH […]
MANAGING SECURITY Managing Profiles 1.Creating profile and Granting it to user CONN sys/password AS SYSDBA CREATE PROFILE my_profile LIMIT FAILED_LOGIN_ATTEMPTS 3 — Account locked after 3 failed logins. PASSWORD_LOCK_TIME 5 — Number of days account is locked for. UNLIMITED required explicit unlock by DBA. PASSWORD_LIFE_TIME 30 — Password expires after 90 days. PASSWORD_GRACE_TIME 3 — Grace period for password […]