Types Of Standby Database

Oracle Standby Database Types

Description : 
       In this blog, we are going to different types of standby databases with clear definitions.

Types of Standby Database:

Physical Standby
Logical Standby
Snapshot Standby

Physical Standby:

  • Physical Standby is the exact block-for-block copy of the primary database.
  • Physical standby database synchronized with the primary database through the application of redo data received from the primary database.
  • It can be used concurrently for data protection and reporting.     
  • Physical standby database will be mounted stage while recovery is processed.
  • It can be opened as read-only mode
  • Active standby database is available for reading mode, enabling recovery at the backend.  

Physical standby database benefits:

  • An identical physical copy of the primary database.
  • Disaster recovery and high availability.
  • High Data protection.
  • Reduction in primary database workload.
  • Performance can be Faster.

Logical Standby Database:

  • A logical standby database does not have to match the schema structure of the source database.
  • Logical standby tables can be open for SQL queries (read-only), and all other standby tables can be open for updates.
  • The primary database logs can be converted as SQL then the SQL statements can be applied into the logical standby database.
  • The logical standby database can have different unique Database IDs.
  • A logical standby database can have additional materialized views and indexes added for faster performance.

Logical Standby Database Benefits:

  • Simultaneous use for reporting, summations, and queries.
  • Efficient use of standby hardware resources.
  • Reduction in primary database workload.
  • Some limitations on the use of certain data types.

Snapshot Standby Database:

  • The snapshot standby database is and fully update standby database.
  • A snapshot standby database receives and archives redo data from a primary database but are not applied.
  • Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database,
  • This database can be used for updates but these updates are discarded before the snapshot database is converted back into a physical standby database.

Snapshot Standby Database benefits:

  • Temporary snapshot of the primary database.
  • fully updatable stand-alone database.
  • As this is in READ and WRITE mode, any kind of testing or changes can be done,


Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Physical Standby To Snapshot Standby

Physical Standby To Snapshot Standby

Description:
In this Blog, we are going to see what is snapshot standby database and how to convert a physical standby database to a snapshot standby database with Demo.

Snapshot Standby Database:

  • Snapshot standby allows doing a read-write operation on the standby database.
  • We can convert the physical standby database to snapshot standby.
  • On that, we can do all types of testing or can be used as a development database.
  • Once the testing is over we can again convert the snapshot database to physical standby.
  • Once it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.

Data Guard Configuration Environment:

Overall Steps:

1. Check primary and standby database role and open mode.
2. Check the archived sequence value in standby.
3. Disable the MRP process.
4. Check the flashback status if it not enabled, enable the flashback.
5. Check the db_recovery_file_set location and size.
6. Bounce the database and start the db as mount stage.
7. Convert physical standby to snapshot standby database.
8. Open standby database and check open mode READ/WRITE.
9. Test the snapshot standby database now we can create user and tables 
for testing purpose.
10. verify the log sequence primary and standby.
11. Bounce database again open in mount stage.
12. Convert snapshot standby to physical standby.
13. Bounce the database.
14. Check the database role has been changed as physical standby and the 
mode as read only.
15. After bounce enable the MRP process and check table available or not.

Step 1- Check primary and standby database role and open mode.
Primary database:
[oracle@agent ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Thu Nov 18 14:16:48 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
———— —————- —————- ——————–
OPEN agent PRIMARY READ WRITE
SQL>

Standby database:
[oracle@data ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Thu Nov 18 14:18:41 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
———— —————- —————- ——————–
OPEN data PHYSICAL STANDBY READ ONLY WITH APPLY
SQL>

Step 2:- Check the archived sequence value in standby:
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1                 14

Step 3:- Disable the MRP process in the standby database:
SQL> alter database recover managed standby database cancel;
Database altered.

Step 4:- Check the flashback status if it is not enabled enable the flashback.
SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
NO
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES

Step 5:- Check the db_recovery_file_set location and size.
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 8256M

Step 6:- Bounce the database and start the DB as mount stage:
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 738197504 bytes
Database Buffers 503316480 bytes
Redo Buffers 7880704 bytes
Database mounted.

Step 7:- Convert physical standby to snapshot standby database:
SQL> alter database convert to snapshot standby;
Database altered.

Step 8:- Open standby database check  read/write mode, and database role:
SQL> alter database open;
Database altered.
SQL> select status, instance_name, database_role,open_mode from v$database, v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
———— —————- —————- ——————–
MOUNTED data SNAPSHOT STANDBY MOUNTED

Step 9:- Test the snapshot standby database now we can create user and tables for testing purposes.
SQL> create user agent identified by vbt default tablespace users quota unlimited on users;
User created
SQL> grant connect,resource to agent;
Grant succeeded.
SQL> conn agent/vbt
Connected.
SQL> create table test(id number,name varchar2(20));
Table created.
SQL> insert into test values(101,’ram’);
1 row created.
SQL> insert into test values(102,’raj’);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME
———- ——————–
101 ram
102 raj

Step 10:- verify the log sequence primary and standby.
Primary side
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
———- ————–
1 14
Standby side
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

THREAD# MAX(SEQUENCE#)
———- ————–
1 14
SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#
——— ———— ———-
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
LNS CONNECTED 0
DGRD ALLOCATED 0

Step 11:- Bounce database again open in mount stage.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 754974720 bytes
Database Buffers 486539264 bytes
Redo Buffers 7880704 bytes
Database mounted.

Step 12:- convert snapshot standby to physical standby:
SQL> alter database convert to physical standby;
Database altered.

Step 13- Bounce the database
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1258290752 bytes
Fixed Size 8896064 bytes
Variable Size 754974720 bytes
Database Buffers 486539264 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.

Step 14:- check the database role has been changed as physical standby and the mode as read-only
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
———— —————- —————- ——————–
OPEN data PHYSICAL STANDBY READ ONLY

Step 15:- After bounce enable the MRP process and check table available or not.
SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select * from agent.test;
select * from agent.test
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>


Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

19c Physical standby configuration – Dataguard

Physical standby configuration:


Description:
         In this Blog we are going to see the step by step process of data Guard configuration, and primary to physical standby database demo.

what Is Data Guard:

  • Oracle data Guard ensures high availability, data protection, and disaster recovery for enterprise data.
  • Data Guard maintains these standby databases as transactional consistent copies of the production database.
  • if the production database becomes unavailable, Data Guard can switch any standby database to the production role.

Physical Standby

  • Physical Standby is the exact block-for-block copy of primary database.
  • REDO logs apply primary to physical standby, its always sync with primary database  
  • Physical Standby database only opened as READ ONLY mode.
  • Most of the environment used the physical standby database.

Environment Details:
Overall Steps:

1.Check Archive log mode enable and force logging enabled.
2.Add Standby log file group.
3.check listener and TNS entry both primary and standby severs.
4.Change parameters in primary database.
5.copy password file primary to standby.
6.Create PFILE in standby database.
7.Create directory Structure in Standby database.
8.Connect RMAN in standby database and run the script
9.Check database can be configured or not.
10.Connect standby database execute MRP process.
11.Switch Logs in primary database, Execute Defer Enable.
12.Check standby database log files can be switched.
13.Crosscheck Alert Log files.

Primary Server Configuration:

Step 1: Check Archive log mode enable and force logging enabled.

In the primary database check whether the archive log mode is enabled or not, also check the force logging option to enable if is not enabled please enable it.

[oracle@agent ~]$ export ORACLE_SID=agent
[oracle@agent ~]$ sqlplus / as sysdba
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0

Connect database as mount stage

SQL> alter database archivelog;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> alter database open;
Database altered.
SQL> select FORCE_LOGGING,log_mode from v$database;
FORCE_LOGGING                   LOG_MODE
————————-                    ————–
YES                                           ARCHIVELOG

Step 2: Add Standby logfile group.

Add logfile group in the primary database mentioned as that file creation as standby log group.

SQL> alter database add standby logfile group 4 ‘/u01/app/oracle/oradata/AGENT/redo04.log’ size 50m;
Database altered.

SQL> alter database add standby logfile group 5 ‘/u01/app/oracle/oradata/AGENT/redo05.log’ size 50m;
Database altered.

SQL> alter database add standby logfile group 6 ‘/u01/app/oracle/oradata/AGENT/redo06.log’ size 50m;
Database altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS
———- ———- ———- — ———-
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED

Step 3- check listener and TNS entry both primary and standby servers.

Primary side entries.
LISTENER:
listener =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = agent)
(ORACLE_HOME =/u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = agent)
)
)
TNS names:
[oracle@agent admin]$ cat tnsnames.ora
agent=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = agent)
)
)
data=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = data)
(UR=A)
)
)
Standby side entries:
LISTENER:
[oracle@data admin]$ cat listener.ora
listener =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = data)
(ORACLE_HOME =/u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = data)
)
)
TNS names:
[oracle@data admin]$
data=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = data)
)
)
agent=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = agent)
(UR=A)
)
)

The output of tns ping statements connects both sides primary to standby and standby to primary.

[oracle@agent admin]$ tnsping data
TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 18-NOV-2021 07:19:44
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = data) (UR=A)))
OK (20 msec)

[oracle@agent admin]$ tnsping agent
TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 18-NOV-2021 07:19:55
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = agent)))
OK (0 msec)
[oracle@agent admin]$

Step 4:Change parameters in primary database.

1. dg_config – Specify the DG_CONFIG attribute to identify the DB_UNIQUE_NAME for the primary database and standby database.
SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(agent,data)’ SCOPE=both;
System altered.

2. log_archive_dest_1 – The LOG_ARCHIVE_DEST parameter is used to specify the directory to which Oracle archive logs are written.
SQL> ALTER SYSTEM SET log_archive_dest_1=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=agent’ SCOPE=both;
System altered.

3. log_archive_dest_2 – handle the transmission of the standby site’s archived redo logs back to the original primary database.
SQL> ALTER SYSTEM SET log_archive_dest_2=’service=data async valid_for=(online_logfiles,primary_role) db_unique_name=data’ SCOPE=both;
System altered.

4. FAL_SERVER – It is used to fetch an archive log server for a standby database.
SQL> ALTER SYSTEM SET fal_server=’data’ SCOPE=both;
System altered.

5. FAL_CLIENT – It is configured for point the FAL Client.
SQL> ALTER SYSTEM SET fal_client=’agent’ SCOPE=both;
System altered.

6. standby_file_management –Check the redo changes done in Primary and sync those changes in Standby by Stopping the recovery and making STANDBY_FILE_MANAGEMENT = MANUAL value.
Then sync the primary changes with standby changes and again change the parameter to AUTO and start the recovery on Standby database.
SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=both;
System altered.

SQL> show parameter log_archive_config;

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_config string dg_config=(agent,data)
SQL>

Step 5:Copy password file primary to standby

scp orapwagent [email protected]:$ORACLE_HOME/dbs
[oracle@agent dbs]$ scp orapwagent [email protected]:$ORACLE_HOME/dbs
[email protected]’s password:
orapwagent 100% 2048 2.1MB/s 00:00
[oracle@agent dbs]$

Move the file in new name db_unique_name(standby)
oracle@data ~]$ cd $ORACLE_HOME/dbs
[oracle@data dbs]$ ls
hc_data.dat init.ora lkDATA orapwdata orapwagent spfiledata.ora
[oracle@data dbs]$ mv orapwagent orapwdata

Step 6 :- create PFILE in standby database

In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initdata.ora
Containing a single parameter: DB_NAME=agent
[oracle@data dbs]$ cat initdata.ora
db_name=agent
[oracle@data dbs]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/dbs

Step 7 :- Create directory Structure in Standby database.

[oracle@data dbs]$ cd $ORACLE_BASE/admin/
[oracle@data dbs]$ mkdir data
[oracle@data dbs]$ cd data
[oracle@data dbs]$ mkdir adump
[oracle@data dbs]$ mkdir -p /u01/app/oracle/oradata/DATA

Step 8 :- Connect RMAN clone Database

method 1:
copy pfile primary database to standby edit the parameters after that connect RMAN and clone the database.

parameter_value_convert ‘agent’,’data’
set db_name=’agent’
set db_unique_name=’data’
set db_file_name_convert=’/u01/app/oracle/oradata/AGENT/’,’/u01/app/oracle/oradata/DATA/’
set log_file_name_convert=’/u01/app/oracle/oradata/AGENT/’,’/u01/app/oracle/oradata/DATA/’
set control_files=’/u01/app/oracle/oradata/DATA/standby1.ctl’
set log_archive_max_processes=’5′
set fal_client=’data’
set fal_server=’agent’
set standby_file_management=’AUTO’
set log_archive_config=’dg_config=(agent,data)’
set compatible=’19.3.0.0′
set memory_target=’1200m’

method 2:
craete a RMAN script mention the parameters changes in spfile to execute the script.

Target database as primary – Agent
Auxiliary database as a standby – Data

[oracle@data admin]$ export ORACLE_SID=agent
[oracle@data admin]$ rman target sys/oracle@agent auxiliary sys/oracle@data
Recovery Manager: Release 19.0.0.0.0 – Production on Thu Nov 18 08:00:22 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: AGENT (DBID=396457310)
connected to auxiliary database: AGENT (not mounted)

RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert ‘agent’,’data’
set db_name=’agent’
set db_unique_name=’data’
set db_file_name_convert=’/u01/app/oracle/oradata/AGENT/’,’/u01/app/oracle/oradata/DATA/’
set log_file_name_convert=’/u01/app/oracle/oradata/AGENT/’,’/u01/app/oracle/oradata/DATA/’
set control_files=’/u01/app/oracle/oradata/DATA/standby1.ctl’
set log_archive_max_processes=’5′
set fal_client=’data’
set fal_server=’agent’
set standby_file_management=’AUTO’
set log_archive_config=’dg_config=(agent,data)’
set compatible=’19.3.0.0′
set memory_target=’1200m’
nofilenamecheck;
}

the output of the script:

using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=82 device type=DISK

allocated channel: p2
channel p2: SID=81 device type=DISK

allocated channel: p3
channel p3: SID=85 device type=DISK

allocated channel: p4
channel p4: SID=86 device type=DISK

allocated channel: s1
channel s1: SID=35 device type=DISK

Starting Duplicate Db at 18-NOV-21

contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format ‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwdata’ targetfile
‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileagent.ora’ auxiliary format
‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfiledata.ora’ ;
sql clone “alter system set spfile= ”/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfiledata.ora””;
}
executing Memory Script

Starting backup at 18-NOV-21
Finished backup at 18-NOV-21

sql statement: alter system set spfile= ”/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfiledata.ora”

contents of Memory Script:
{
sql clone “alter system set audit_file_dest =
”/u01/app/oracle/admin/data/adump” comment=
”” scope=spfile”;
sql clone “alter system set dispatchers =
”(PROTOCOL=TCP) (SERVICE=dataXDB)” comment=
”” scope=spfile”;
sql clone “alter system set db_name =
”agent” comment=
”” scope=spfile”;
sql clone “alter system set db_unique_name =
”data” comment=
”” scope=spfile”;
sql clone “alter system set db_file_name_convert =
”/u01/app/oracle/oradata/AGENT/”, ”/u01/app/oracle/oradata/DATA/” comment=
”” scope=spfile”;
sql clone “alter system set log_file_name_convert =
”/u01/app/oracle/oradata/AGENT/”, ”/u01/app/oracle/oradata/DATA/” comment=
”” scope=spfile”;
sql clone “alter system set control_files =
”/u01/app/oracle/oradata/DATA/standby1.ctl” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_max_processes =
5 comment=
”” scope=spfile”;
sql clone “alter system set fal_client =
”data” comment=
”” scope=spfile”;
sql clone “alter system set fal_server =
”agent” comment=
”” scope=spfile”;
sql clone “alter system set standby_file_management =
”AUTO” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_config =
”dg_config=(agent,data)” comment=
”” scope=spfile”;
sql clone “alter system set compatible =
”19.3.0.0” comment=
”” scope=spfile”;
sql clone “alter system set memory_target =
1200m comment=
”” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set audit_file_dest = ”/u01/app/oracle/admin/data/adump” comment= ”” scope=spfile

sql statement: alter system set dispatchers = ”(PROTOCOL=TCP) (SERVICE=dataXDB)” comment= ”” scope=spfile

sql statement: alter system set db_name = ”agent” comment= ”” scope=spfile

sql statement: alter system set db_unique_name = ”data” comment= ”” scope=spfile

sql statement: alter system set db_file_name_convert = ”/u01/app/oracle/oradata/AGENT/”, ”/u01/app/oracle/oradata/DATA/” comment= ”” scope=spfile

sql statement: alter system set log_file_name_convert = ”/u01/app/oracle/oradata/AGENT/”, ”/u01/app/oracle/oradata/DATA/” comment= ”” scope=spfile

sql statement: alter system set control_files = ”/u01/app/oracle/oradata/DATA/standby1.ctl” comment= ”” scope=spfile

sql statement: alter system set log_archive_max_processes = 5 comment= ”” scope=spfile

sql statement: alter system set fal_client = ”data” comment= ”” scope=spfile

sql statement: alter system set fal_server = ”agent” comment= ”” scope=spfile

sql statement: alter system set standby_file_management = ”AUTO” comment= ”” scope=spfile

sql statement: alter system set log_archive_config = ”dg_config=(agent,data)” comment= ”” scope=spfile

sql statement: alter system set compatible = ”19.3.0.0” comment= ”” scope=spfile

sql statement: alter system set memory_target = 1200m comment= ”” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1258290752 bytes

Fixed Size 8896064 bytes
Variable Size 738197504 bytes
Database Buffers 503316480 bytes
Redo Buffers 7880704 bytes
allocated channel: s1
channel s1: SID=35 device type=DISK

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/u01/app/oracle/oradata/DATA/standby1.ctl’;
}
executing Memory Script

Starting backup at 18-NOV-21
channel p1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_agent.f tag=TAG20211118T080552
channel p1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-NOV-21

contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
“/u01/app/oracle/oradata/DATA/datafile/o1_mf_temp_jsc8y37j_.tmp”;
switch clone tempfile all;
set newname for datafile 1 to
“/u01/app/oracle/oradata/DATA/datafile/o1_mf_system_jsc8qv1o_.dbf”;
set newname for datafile 3 to
“/u01/app/oracle/oradata/DATA/datafile/o1_mf_sysaux_jsc8smrq_.dbf”;
set newname for datafile 4 to
“/u01/app/oracle/oradata/DATA/datafile/o1_mf_undotbs1_jsc8tq0b_.dbf”;
set newname for datafile 7 to
“/u01/app/oracle/oradata/DATA/datafile/o1_mf_users_jsc8tr78_.dbf”;
backup as copy reuse
datafile 1 auxiliary format
“/u01/app/oracle/oradata/DATA/datafile/o1_mf_system_jsc8qv1o_.dbf” datafile
3 auxiliary format
“/u01/app/oracle/oradata/DATA/datafile/o1_mf_sysaux_jsc8smrq_.dbf” datafile
4 auxiliary format
“/u01/app/oracle/oradata/DATA/datafile/o1_mf_undotbs1_jsc8tq0b_.dbf” datafile
7 auxiliary format
“/u01/app/oracle/oradata/DATA/datafile/o1_mf_users_jsc8tr78_.dbf” ;
sql ‘alter system archive log current’;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/DATA/datafile/o1_mf_temp_jsc8y37j_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 18-NOV-21
channel p1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/AGENT/datafile/o1_mf_system_jsc8qv1o_.dbf
channel p2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/AGENT/datafile/o1_mf_sysaux_jsc8smrq_.dbf
channel p3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/AGENT/datafile/o1_mf_undotbs1_jsc8tq0b_.dbf
channel p4: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/AGENT/datafile/o1_mf_users_jsc8tr78_.dbf
output file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_users_jsc8tr78_.dbf tag=TAG20211118T080558
channel p4: datafile copy complete, elapsed time: 00:00:08
output file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_sysaux_jsc8smrq_.dbf tag=TAG20211118T080558
channel p2: datafile copy complete, elapsed time: 00:01:00
output file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_undotbs1_jsc8tq0b_.dbf tag=TAG20211118T080558
channel p3: datafile copy complete, elapsed time: 00:00:59
output file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_system_jsc8qv1o_.dbf tag=TAG20211118T080558
channel p1: datafile copy complete, elapsed time: 00:01:22
Finished backup at 18-NOV-21

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1088928441 file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_system_jsc8qv1o_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1088928441 file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_sysaux_jsc8smrq_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1088928441 file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_undotbs1_jsc8tq0b_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1088928441 file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_users_jsc8tr78_.dbf
Finished Duplicate Db at 18-NOV-21
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1
RMAN> exit

Step 9: Check database can be configured or not:

SQL> select open_mode,name from v$database;

OPEN_MODE NAME
——————– ———
MOUNTED AGENT

SQL> select open_mode,name,database_role from v$database;

OPEN_MODE NAME DATABASE_ROLE
——————– ——— —————-
MOUNTED AGENT PHYSICAL STANDBY

Step 10:- Connect standby database execute MRP process:

SQL> alter database recover managed standby database disconnect nodelay;

Database altered.

Step 11:- Switch Logs in the primary database, Execute Defer Enable.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9

Set log_archive_dest_state_2 as defer
SQL> alter system set log_archive_dest_state_2=defer;

System altered.

Enable log_archive_dest_state_2 location
SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

Step 12: – check standby database log files can be switched

SQL> select error, status from v$archive_dest;

SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# , SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#, FIRST_TIME ) IN (SELECT THREAD#, MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# , SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#, FIRST_TIME ) IN (SELECT THREAD#, MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
 

Check MRP0 process status:
SQL> SELECT STATUS FROM V$MANAGED_STANDBY WHERE PROCESS=’MRP0′;

STATUS
————
WAIT_FOR_LOG

Archived log applied time and status:

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
——— ——————–
AGENT MOUNTED

Step 13:- Monitor the process in the alert log
The best method to monitor the data guard process in the alert log shows as exact data transfer reports and errors we can using the logs easily find out the mistakes.

oracle@data trace]$ pwd
/u01/app/oracle/diag/rdbms/data/data/trace
[oracle@data trace]$ tail -100f alert_data.log
MRP0 (PID:26173): Media Recovery Log /u01/app/oracle/fast_recovery_area/DATA/archivelog/2021_11_18/o1_mf_1_11_jsclfmk7_.arc
MRP0 (PID:26173): Media Recovery Waiting for T-1.S-12 (in transit)
2021-11-18T09:05:44.267442+05:30
rfs (PID:29555): Archived Log entry 7 added for B-1088922080.T-1.S-12 ID 0x17a1415e LAD:2
rfs (PID:29555): No SRLs created for T-1
2021-11-18T09:05:44.297904+05:30
rfs (PID:29555): Opened log for T-1.S-13 dbid 396457310 branch 1088922080
2021-11-18T09:05:44.346775+05:30
MRP0 (PID:26173): Media Recovery Log /u01/app/oracle/fast_recovery_area/DATA/archivelog/2021_11_18/o1_mf_1_12_jsclfnxx_.arc
MRP0 (PID:26173): Media Recovery Waiting for T-1.S-13 (in transit)
2021-11-18T09:05:45.670215+05:30
rfs (PID:29555): Archived Log entry 8 added for B-1088922080.T-1.S-13 ID 0x17a1415e LAD:2
rfs (PID:29555): No SRLs created for T-1
2021-11-18T09:05:45.699219+05:30
rfs (PID:29555): Opened log for T-1.S-14 dbid 396457310 branch 1088922080
2021-11-18T09:05:46.482970+05:30
MRP0 (PID:26173): Media Recovery Log /u01/app/oracle/fast_recovery_area/DATA/archivelog/2021_11_18/o1_mf_1_13_jscljj95_.arc
MRP0 (PID:26173): Media Recovery Waiting for T-1.S-14 (in transit)
2021-11-18T09:16:20.023804+05:30

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

GENARATE AWR REPORT

AWR REPORT:

The Automatic Workload Repository (AWR) collects and maintains statistics of the 
database. We can generate awr report for a particular time frame in the past using the script
awrrpt.sql ( located under $ORACLE_HOME/rdbms/admin) script – @$ORACLE_HOME/rdbms/admin/awrrpt.sql

step – 1

[oratest@oracle ~]$ export ORACLE_SID=test
[oratest@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 7 11:06:50 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      READ ONLY

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> show user;
USER is "SYS"

step – 2

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.

'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

Enter value for report_type:<strong> <span style="color:#ed0911" <br />class="has-inline-color">html</span></strong>
old   1: select 'Type Specified: ',lower(nvl('&&report_type','html')) <br />report_type from dual
new   1: select 'Type Specified: ',lower(nvl('html','html')) <br />report_type from dual

Type Specified:  html

old   1: select '&&report_type' report_type_def from dual
new   1: select 'html' report_type_def from dual



old   1: select '&&view_loc' view_loc_def from dual
new   1: select 'AWR_PDB' view_loc_def from dual



Current Instance
~~~~~~~~~~~~~~~~
DB Id          DB Name        Inst Num       Instance       Container Name
-------------- -------------- -------------- -------------- --------------
 2378581000     TEST                        1 test           test








Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
* 2378581000     1      TEST         test         oracle.local

Using 2378581000 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

test         TEST               646  29 Sep 2021 00:30    1
                                647  29 Sep 2021 01:30    1
                                648  29 Sep 2021 02:30    1
                                649  29 Sep 2021 03:30    1
                                650  29 Sep 2021 04:30    1
                                651  29 Sep 2021 05:30    1
                                652  29 Sep 2021 06:30    1
                                653  30 Sep 2021 00:14    1
                                654  30 Sep 2021 01:30    1
                                655  30 Sep 2021 03:46    1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:<strong><span style="color:#a3000d" <br />class="has-inline-color"> </span><span style="color:#ec0b1e" <br />class="has-inline-color">650</span></strong>
Begin Snapshot Id specified: 650

Enter value for end_snap: <strong><span style="color:#e90c17" <br />class="has-inline-color">651</span></strong>
</pre>
<!-- /wp:preformatted -->

<!-- wp:preformatted -->
<pre class="wp-block-preformatted">Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_650_651.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: awrrpt_1_07_10_2021.html

Using the report name awrrpt_1_07_10_2021.html

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

Analysis Period
---------------
AWR snapshot range from 650 to 651.
Time period starts at 29-SEP-21 04.30.25 AM
Time period ends at 29-SEP-21 05.30.33 AM

Analysis Target
---------------
Database &apos;TEST&apos; with DB ID 2378581000.
Database version 19.0.0.0.0.
ADDM performed an analysis of instance test, numbered 1 and hosted at
oracle.localdomain.

Activity During the Analysis Period
-----------------------------------
Total database time was 0 seconds.
The average number of active sessions was 0.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

There are no findings to report.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          Additional Information
          ----------------------

Miscellaneous Information
-------------------------
There was no significant database activity to run the ADDM.

</pre>
<br /><a class="awr" href="#top">Back to Top</a><p />
<p />
<p />
<p />
<p />
<p />
<p />
<p />
<p />
<p />
End of Report
</body></html>
Report written to awrrpt_1_07_10_2021.html

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

PACKAGES REQUIRED FOR AWR

AWR REPORT:

AWR collects database statistics every 60 minutes (on the hour) out of the box 
(this is configurable), and this data is maintained for a week and then purged. ü The Oracle database uses AWR for problem detection and analysis as well as for
self-tuning.A number of different statistics are collected by the AWR,
including wait events, time model statistics, active session history statistics,
various system- and session-level statistics, object usage statistics, and
information on the most resource-intensive SQL statements. ü To properly collect database statistics, set the initialization parameter
STATISTICS_LEVEL to TYPICAL (the default) or ALL. ü The AWR consists of a number of tables owned by the SYS schema and typically stored
in the SYSAUX tablespace (currently no method exists that I know of to move these
objects to another tablespace). Recommendations before getting an AWR Report :

Collect Multiple AWR Reports

It’s always good to have two AWR Reports, one for good time (when database was 
performing well), second when performance is poor. This way Remote DBA can easily
compare good and bad report to find out the culprit.

Stick to Particular Time

Database is performing slow” will not help anymore to resolve performace issues. 
We have to have a specific time like Database was slow yesterday at 1 Pm and
continue till 4Pm. Here, DBA will get a report for these three hours.

Split Large AWR Report into Smaller Reports

Instead of having one report for long time like one report for 4hrs. it’s
better to have four reports each for one hour. This will help to isolate the problem.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Database link

Database link:

A database links allows an Oracle client to access two or more databases as one 
logical database via a connection between the database servers. Database links are actually entries in a data dictionary table that define
a communication path from one Oracle database to another remote database. In order to access the database link a user must be connected to the
database that contains the data dictionary table that defines the database link

Types Of Database Links

Private and Public

Private Link

To create a private database link, you must have the CREATE DATABASE LINK 
system privilege. To create a public database link, you must have the CREATBE PUBLIC DATAASE LINK
system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

Private database link to a user in a remote database

SQL> CREATE DATABASE LINK orcl_remote
   CONNECT TO scott IDENTIFIED BY tiger
   USING 'orcl'; 

Database link created.

Private database link to a user in a remote database, with the full connection string

SQL> CREATE DATABASE LINK scott_remote
   CONNECT TO apple IDENTIFIED BY apple
   USING '(DESCRIPTION=
            (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1521))
            (CONNECT_DATA=(SERVICE_NAME=orcl))
          )';

Database link created.

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Oracle Materialized View

Oracle Materialized View

A materialized view in Oracle is a database object that contains the results of a 
query.They are local copies of data located remotely, or are used to create summary
tables based on aggregations of a table’s data.
A materialized view can query tables, views, and other materialized views. A materialized view, or snapshot as they were previously known, is a table segment
whose contents are periodically refreshed based on a query, either against a local
or remote table.

Syntax

CREATE MATERIALIZED VIEW view-name BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ][[ENABLE | DISABLE] QUERY REWRITE] AS SELECT …;

Methods

BUILD

IMMEDIATE : The materialized view is populated immediately.This option is default one.
DEFERRED : The materialized view is populated on the first requested refresh.

Refresh Types

  • FAST: A fast refresh is attempted only there is a change in the base table. If materialized view logs are not present against the source tables in advance, the creation fails. To maintain the history of change in the base table, it is known as materialized view log.It is named as MLOG$_<base_table>. Materialized view log will be located in the source database in the same schema as the master table. Refresh fast will perform refresh according to the changes that occurred in the master table.
  • COMPLETE: The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
  • FORCE: A fast refresh is attempted. If one is not possible a complete refresh is performed.
ON COMMIT : The refresh is triggered by a committed data change in one of the 
dependent tables. ON DEMAND : The refresh is initiated by a manual request or a scheduled task.
GRANT CREATE MATERIALIZED VIEW TO INDIA;
Grant succeeded.

GRANT CREATE DATABASE LINK TO INDIA;
Grant succeeded.

Creating Database link to point remote database 

SQL> CREATE DATABASE LINK PROD_LINK CONNECT TO ph IDENTIFIED BY ph USING 'orcl';

Database link created.

COMPLETE REFRESH

Creating Materialized View

SQL> CREATE MATERIALIZED VIEW dept_mv BUILD IMMEDIATE REFRESH COMPLETE AS 
SELECT * FROM ph.emp@PROD_LINK; Materialized view created.
SQL> select * from emp;

        NO NAME                     SALARY
---------- -------------------- ----------
         1 apple                     20000
         2 orange                    23000
         3 mango                     32000

Table Data present in Remote database

SQL> insert into emp values (6,'ice',30000);

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid,no,name,salary from dept_mv;

ROWID                      NO NAME                     SALARY
------------------ ---------- -------------------- ----------
AAAR36AABAAAbURAAA          1 apple                     20000
AAAR36AABAAAbURAAB          2 orange                    23000
AAAR36AABAAAbURAAC          3 mango                     32000

Manually using complete refresh using the DBMS_VIEW package

SQL>  execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'C' );

PL/SQL procedure successfully completed.

SQL> select rowid,no,name,salary from dept_mv;

ROWID                      NO NAME                     SALARY
------------------ ---------- -------------------- ----------
AAAR36AABAAAbURAAD          1 apple                     20000
AAAR36AABAAAbURAAE          2 orange                    23000
AAAR36AABAAAbURAAF          3 mango                     32000
AAAR36AABAAAbURAAG          6 ice                       30000

DEFERRED

Initially drop the existing materialized view

SQL> drop MATERIALIZED VIEW dept_mv;

Materialized view dropped.

Materialized view creation using DEFERRED option

SQL> CREATE MATERIALIZED VIEW dept_mv BUILD DEFERRED REFRESH COMPLETE AS 
SELECT * FROM ph.emp@PROD_LINK; Materialized view created. SQL> execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'C' ); PL/SQL procedure successfully completed.
QL> select rowid,no,name,salary from dept_mv;

ROWID                      NO NAME                     SALARY
------------------ ---------- -------------------- ----------
AAAR39AABAAAbURAAA          1 apple                     20000
AAAR39AABAAAbURAAB          2 orange                    23000
AAAR39AABAAAbURAAC          3 mango                     32000
AAAR39AABAAAbURAAD          6 ice                       30000

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Linux File Compress commands

File Compress commands 

Linux we have two types of compress commands

  1. GZIP
  2. GUNZIP

To reduce the file size by using compress commands

gzip – This command used to compress the file size

[oracle@oracletest scripts]$ gzip ramkumar.txt
[oracle@oracletest scripts]$ ls -l
total 24
-rwxrwxrwx. 1 oracle oracle 72 Oct 21 01:49 ramkumar.txt.gz
-rwxrwxr-x. 1 oracle oracle 1291 Oct 20 23:47 rman_bkp.sh
-rw-rw-r--. 1 oracle oracle 6979 Oct 20 23:48 rman.log
-rwxr--r--. 1 oracle oinstall 515 Sep 9 14:45 setEnv.sh
-rwxr--r--. 1 oracle oinstall 134 Sep 7 10:54 stop_all.sh

Gunzip – This command is user to unzip the original file size

[oracle@oracletest scripts]$ gunzip ramkumar.txt.gz
[oracle@oracletest scripts]$ ls -l
total 24
-rwxrwxrwx. 1 oracle oracle 42 Oct 21 01:49 ramkumar.txt
-rwxrwxr-x. 1 oracle oracle 1291 Oct 20 23:47 rman_bkp.sh
-rw-rw-r--. 1 oracle oracle 6979 Oct 20 23:48 rman.log
-rwxr--r--. 1 oracle oinstall 515 Sep 9 14:45 setEnv.sh
-rwxr--r--. 1 oracle oinstall 134 Sep 7 10:54 stop_all.sh
[oracle@oracletest scripts]$

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Linux Link Commands

Linux Link Commands

Creating links is a kind of shortcut to access a file. Links allow more than one file name to refer to the same file.

There are two types of links :

  1. Hard Link
  2. Soft Link or Symbolic links

Hard Link

A hard link is one most power full links in the Linux system when we create a hard link to the file and then delete the file, we can still access the file using the hard link.

syntax : ln file_name link_name
Eg: ln ramkumar.txt ram

[oracle@oracletest scripts]$ ls -l
total 24
-rwxrwxrwx. 1 oracle oracle 42 Oct 21 01:49 ramkumar.txt
-rwxrwxr-x. 1 oracle oracle 1291 Oct 20 23:47 rman_bkp.sh
-rw-rw-r--. 1 oracle oracle 6979 Oct 20 23:48 rman.log
-rwxr--r--. 1 oracle oinstall 515 Sep 9 14:45 setEnv.sh
-rwxr--r--. 1 oracle oinstall 134 Sep 7 10:54 stop_all.sh
[oracle@oracletest scripts]$ ln ramkumar.txt ram
[oracle@oracletest scripts]$ ls -l
total 28
-rwxrwxrwx. 2 oracle oracle 42 Oct 21 01:49 ram
-rwxrwxrwx. 2 oracle oracle 42 Oct 21 01:49 ramkumar.txt
-rwxrwxr-x. 1 oracle oracle 1291 Oct 20 23:47 rman_bkp.sh
-rw-rw-r--. 1 oracle oracle 6979 Oct 20 23:48 rman.log
-rwxr--r--. 1 oracle oinstall 515 Sep 9 14:45 setEnv.sh
-rwxr--r--. 1 oracle oinstall 134 Sep 7 10:54 stop_all.sh
[oracle@oracletest scripts]$ cat ram
HI 
Hello
welcome to linux basic commands
[oracle@oracletest scripts]$ rm -rf ramkumar.txt
[oracle@oracletest scripts]$ ls -l
total 24
-rwxrwxrwx. 1 oracle oracle 42 Oct 21 01:49 ram
-rwxrwxr-x. 1 oracle oracle 1291 Oct 20 23:47 rman_bkp.sh
-rw-rw-r--. 1 oracle oracle 6979 Oct 20 23:48 rman.log
-rwxr--r--. 1 oracle oinstall 515 Sep 9 14:45 setEnv.sh
-rwxr--r--. 1 oracle oinstall 134 Sep 7 10:54 stop_all.sh
[oracle@oracletest scripts]$ cat ram
HI 
Hello
welcome to linux basic commands


 

Soft Link or Symbolic links

Soft link is another variety type of Linux link, But if we create a soft link of the file and then delete the file, we can’t access the file through the soft link, and the soft link becomes dangling

Syntax: ln -s file_name link_name

Eg: ln -s ramkumar.txt ram

Eg: ls -s sample.txt kar

[oracle@oracletest scripts]$ ln -s sample.txt kar
[oracle@oracletest scripts]$ ls -l
total 28
lrwxrwxrwx. 1 oracle oracle 9 Oct 21 23:12 ram -> sample.txt
-rw-rw-r--. 1 oracle oracle 49 Oct 21 23:12 ramkumar.txt
-rwxrwxrwx. 1 oracle oracle 42 Oct 21 01:49 ram
-rwxrwxr-x. 1 oracle oracle 1291 Oct 20 23:47 rman_bkp.sh
-rw-rw-r--. 1 oracle oracle 6979 Oct 20 23:48 rman.log
-rwxr--r--. 1 oracle oinstall 515 Sep 9 14:45 setEnv.sh
-rwxr--r--. 1 oracle oinstall 134 Sep 7 10:54 stop_all.sh
[oracle@oracletest scripts]$ cat ram
Good morning
How are you all
Take care
Bye Bye !
[oracle@oracletest scripts]$ rm -rf sample.txt
[oracle@oracletest scripts]$ ls -l
total 24
lrwxrwxrwx. 1 oracle oracle 9 Oct 21 23:12 ram -> sample.txt
-rwxrwxrwx. 1 oracle oracle 42 Oct 21 01:49 ram
-rwxrwxr-x. 1 oracle oracle 1291 Oct 20 23:47 rman_bkp.sh
-rw-rw-r--. 1 oracle oracle 6979 Oct 20 23:48 rman.log
-rwxr--r--. 1 oracle oinstall 515 Sep 9 14:45 setEnv.sh
-rwxr--r--. 1 oracle oinstall 134 Sep 7 10:54 stop_all.sh
[oracle@oracletest scripts]$ cat ram
cat: ram: No such file or directory

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Linux search commands

Linux search commands

In Linux, we have four various types of search commands

  • Grep

Grep is an acronym that stands for Global Regular Expression Print.

Grep is a Linux command used to search for a string of characters in a specified file.

To Search a File:

Syntax: Grep match_string file_name

Eg: grep database sample1

To Search Multiple Files: 

To search multiple files with the grep command, insert the filenames you want to search, separated with a space character.

Syntax: grep match_string file_name file_name

Eg: grep database sample1 sample2 sample3
  • AWK

AWK this command  is used for pattern search and processing, scanning files line by line then performing specified actions on matching lines

Syntax: awk ‘{search options from to}’ file_name

Eg: $ awk '{print $2 "\t" $3}' file.txt
  • Locate

locate command and find command is used to search a file by name. But, the difference between both commands is that locating command is a background process and searches the file in the database whereas, find command searches in the file system. The locate command is much faster than the find command.

We have some options in locating search commands by locate -h (help cmd)
Search for entries in a locate database.

-A, --all only print entries that match all patterns
-b, --basename match only the base name of path names
-c, --count only print number of found entries
-d, --database DBPATH use DBPATH instead of default database (which is
/var/lib/mlocate/mlocate.db)
-e, --existing only print entries for currently existing files
-L, --follow follow trailing symbolic links when checking file
existence (default)
-h, --help print this help
-i, --ignore-case ignore case distinctions when matching patterns
-l, --limit, -n LIMIT limit output (or counting) to LIMIT entries
-m, --mmap ignored, for backward compatibility
-P, --nofollow, -H don't follow trailing symbolic links when checking file
existence
-0, --null separate entries with NUL on output
-S, --statistics don't search for entries, print statistics about each
used database
-q, --quiet report no error messages about reading databases
-r, --regexp REGEXP search for basic regexp REGEXP instead of patterns
--regex patterns are extended regexps
-s, --stdio ignored, for backward compatibility
-V, --version print version information
-w, --wholename match whole path name (default)
Syntax: locate {Options…..} (filename)

 Eg:  locate ramkumar.txt

[oracle@oracletest pfile]$ locate ramkumar.txt
/home/oracle/scripts/ramkumar.txt

  • Find

find command is one of the most powerful tools in Linux, It supports searching by file, folder, name, creation date, modification date, owner, and permissions.

By using the ‘-exec’

Syntax: find [where to start searching from]
[expression determines what to find] [-options] [what to find]

Eg: find ramkumar

Options :

-exec CMD: The file being searched which meets the above criteria and returns 0 
for as its exit status for successful command execution.

-ok CMD : It works same as -exec except the user is prompted first.

-inum N : Search for files with inode number ‘N’.

-links N : Search for files with ‘N’ links.

-name demo : Search for files that are specified by ‘demo’.

-newer file : Search for files that were modified/created after ‘file’.

-perm octal : Search for the file if permission is ‘octal’.

-print : Display the path name of the files found by using the rest of the criteria.

-empty : Search for empty files and directories.

-size +N/-N : Search for files of ‘N’ blocks; ‘N’ followed by ‘c’can be used to 
measure size in characters; ‘+N’ means size > ‘N’ blocks and ‘-N’ means 
size < 'N' blocks.

-user name : Search for files owned by user name or ID ‘name’.

\(expr \) : True if ‘expr’ is true; used for grouping criteria combined with OR or AND.

! expr : True if ‘expr’ is false.

 

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8