All about Databases - Oracle, PostgeSQL, Mysql, Big Data .....

Friday, January 19, 2018

Creating a Physical Standby Database Manually

OS: Oracle Linux 7.3
DB: 12.2.0

Step 0: For Linux OS do followings on each machine
Stop and disable firewall as
[oracle@db admin]$systemctl stop firewalld
[oracle@db admin]$systemctl disable firewalld

Disable selinux as
[root@db admin]$vi /etc/selinux/config
SELINUX=disabled

SSH connection must be established between both databases as
ssh-keygen
ssh-copy-id db_hostname

------> Primary Database <------

Step 1: Enable Forced Logging
As part of preparing the primary database for standby database creation, you place the primary database in FORCE LOGGING mode.
To check the database logging mode:
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------
NO

You can do this after database creation using the following SQL statement:
SQL> ALTER DATABASE FORCE LOGGING;

Note: When you issue this statement, the primary database must at least be mounted (and it can also be open).

Step 2: Configure Redo Transport Authentication
Oracle Data Guard uses Oracle Net sessions to transport redo data and control
messages between the members of an Oracle Data Guard configuration.
These redo transport sessions are authenticated using either the Secure Sockets Layer
(SSL) protocol or a remote login password file.

Step 3: Set Primary Database Initialization Parameters
On the primary database, you define initialization parameters that control redo
transport services while the database is in the primary role.
There are additional parameters you need to add that control the receipt of the redo
data and apply services when the primary database is transitioned to the standby role.
The following example shows the primary role initialization parameters that you
maintain on the primary database. This example represents an Oracle Data Guard
configuration with a primary database located in Chicago (orcl) and one physical standby
database located in Boston (stby). The parameters shown in this example are valid for the
Chicago database when it is running in either the primary or the standby database role.

Content of initorcl.ora file. (create pfile from spfile, change pfile, start database with pfile, create spfile frpm pfile, force startup)
Note: We have already enabled the archive log here and db_recover_file_* parameters are defined. So we will use 'LOCATION=USE_DB_RECOVERY_FILE_DEST'
in parameter file.

# The lines start without '*' is new added.

*.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/recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_name='orcl'
db_unique_name=orcl
*.db_recovery_file_dest='/u01/app/oracle/recovery_area'
*.db_recovery_file_dest_size=8016m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.local_listener='LISTENER_ORCL'
*.memory_target=1570m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
log_archive_config='DG_CONFIG=(orcl,stby)'
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
log_archive_dest_2='SERVICE=stby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby'
log_archive_format=%t_%s_%r.arc

# These parameters take effect when the primary database is transitioned to the standby role
FAL_SERVER=stby
FAL_CLIENT=orcl
DB_FILE_NAME_CONVERT='/stby/','/orcl/'
LOG_FILE_NAME_CONVERT='/stby/','/orcl/'
STANDBY_FILE_MANAGEMENT=AUTO

Option 2: We can also update parameters from sqlplus.

SQL> alter system set db_unique_name=orcl scope=spfile;
SQL> alter system set log_archive_config='DG_CONFIG=(orcl,stby)' scope=both;
SQL> 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;
SQL> alter system set log_archive_dest_2='SERVICE=stby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby' scope=both;
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
SQL> alter system set FAL_SERVER=stby scope=both;
SQL> alter system set FAL_CLIENT=orcl scope=both;
SQL> alter system set DB_FILE_NAME_CONVERT='/stby/','/orcl/' scope=spfile;
SQL> alter system set LOG_FILE_NAME_CONVERT='/stby/','/orcl/' scope=spfile;
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
SQL> alter system set remote_login_passwordfile=exclusive; -->REMOTE LOGIN (default is 'exclusive')
SQL> startup force;

Note: For explanation of each line refer to page 57. (documentation "Data Guard Concepts and Administration").
Another useful link for standby creation: http://onlineappsdba.com/index.php/2009/08/19/standby-redo-logs-srl-in-standby-database/

Step 4: Enable Archiving
If archiving is not enabled, then you must put the primary database in ARCHIVELOG mode and enable automatic archiving.
Issue the following SQL statements:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Step 5: Create a Backup Copy of the Primary Database Data Files
[oracle@db admin]$ rman target=/
RMAN> backup database plus archivelog tag='for_stby';
Starting backup at 01-DEC-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 01-DEC-17
channel ORA_DISK_1: finished piece 1 at 01-DEC-17
piece handle=/u01/app/oracle/recovery_area/ORCL/backupset/2017_12_01/o1_mf_nnndf_FOR_STBY_f21wgkrc_.bkp tag=FOR_STBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 01-DEC-17

Starting Control File and SPFILE Autobackup at 01-DEC-17
piece handle=/u01/app/oracle/recovery_area/ORCL/autobackup/2017_12_01/o1_mf_s_961582112_f21wh125_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 01-DEC-17

RMAN>

Step 6: Configure the Primary Database to Receive Redo Data
It is a best practice to configure the primary database to receive redo if this is the first
time a standby database is added to the configuration.
The primary database can then quickly transition to the standby role and begin receiving redo data, if necessary.
To create a standby redo log, use the SQL ALTER DATABASE ADD STANDBY LOGFILE statement. For example:

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog1.rdo') SIZE 200M;
or
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;

We added 4 standby logfile in our test environment.

Note: Each standby redo log file must be at least as large as the largest redo log file in the
redo log of the redo source database. For administrative ease, Oracle recommends that
all redo log files in the redo log at the redo source database and the standby redo log
at a redo transport destination be of the same size.
The standby redo log must have at least one more redo log group than the redo log at
the redo source database, for each redo thread at the redo source database. At the redo
source database, query the V$LOG view to determine how many redo log groups are in
the redo log at the redo source database and query the V$THREAD view to determine
how many redo threads exist at the redo source database.

Step 7: Create a Control File for the Standby Database
Create the control file for the standby database, as shown in the following example
(the primary database does not have to be open, but it must at least be mounted):

SQL> alter database create standby controlfile as '/tmp/stby.ctl';
Database altered.

The ALTER DATABASE command designates the database that is to operate in the
standby role; in this case, a database named stby.
You cannot use a single control file for both the primary and standby databases. They
must each have their own file.

Note: If a control file backup is taken on the primary and restored on a standby (or
vice versa), then the location of the snapshot control file on the restored system is configured
to be the default. (The default value for the snapshot control file name is platform-specific and
dependent on Oracle home.) Manually reconfigure it to the correct value using the RMAN CONFIGURE
SNAPSHOT CONTROLFILE command.

Step 8: Create a Parameter File for the Standby Database
Perform the following steps to create a parameter file for the standby database.

8.1 Create a parameter file (PFILE) from the server parameter file (SPFILE) used by the
primary database.

SQL> create pfile='/tmp/initstby.ora' from spfile;
File created.

8.2 Modify the parameter values in the parameter file created in the previous step.
Although most of the initialization parameter settings in the parameter file are also
appropriate for the physical standby database, some modifications must be made.

# The lines start without '*' is new added.
audit_file_dest='/u01/app/oracle/admin/stby/adump'
*.audit_trail='db'
*.compatible='12.2.0'
control_files='/u01/app/oracle/oradata/stby/control01.ctl','/u01/app/oracle/recovery_area/stby/control02.ctl'
*.db_block_size=8192
DB_FILE_NAME_CONVERT='/orcl/','/stby/'
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/recovery_area'
*.db_recovery_file_dest_size=8016m
db_unique_name=stby
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
FAL_SERVER=orcl
FAL_CLIENT=stby
---->*.local_listener='LISTENER_ORCL' # must be removed
*.log_archive_config='DG_CONFIG=(orcl,stby)'
log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stby'
log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_format='%t_%s_%r.arc'
LOG_FILE_NAME_CONVERT='/orcl/','/stby/'
*.memory_target=1570m
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'

Ensure the COMPATIBLE initialization parameter is set to the same value on both the
primary and standby databases. If the values differ, then redo transport services may
be unable to transmit redo data from the primary database to the standby databases.
It is always a good practice to use the SHOW PARAMETERS command to verify no other
parameters need to be changed.

Step 9: Configure and start a listener on the primary system if one is not already configured.
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=orcl)
(GLOBAL_DBNAME=orcl.xxx.com)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
)

(SID_DESC=
(SID_NAME=stby)
(GLOBAL_DBNAME=stby.xxx.com)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

Step 10: Create Oracle Net service names
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL = #generated automatically
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)

Step 11: Copy Files from the Primary System to the Standby System
Ensure that all required directories are created and use an operating system copy
utility to copy the following binary files from the primary system to their correct
locations on the standby system:

Parameter File
[oracle@db ~]$ scp /tmp/initstby.ora oracle@192.168.10.20:/u01/app/oracle/product/12.2.0/dbhome_1/dbs
oracle@192.168.10.20's password:
initstby.ora 100% 1077 1.1KB/s 00:00

Backup File
[oracle@db ~]$ scp /u01/app/oracle/recovery_area/ORCL/backupset/2017_12_01/o1_mf_nnndf_FOR_STBY_f21wgkrc_.bkp
oracle@192.168.10.20:/u01/app/oracle/oradata/tmp
oracle@192.168.10.20's password:
o1_mf_nnndf_FOR_STBY_f21wgkrc_.bkp 100% 1.1GB 10.1MB/s 02:10

Control File
[oracle@db ~]$ scp /tmp/stby.ctl oracle@192.168.10.20:/u01/app/oracle/oradata/tmp
oracle@192.168.10.20's password:
stby.ctl 100% 10MB 10.1MB/s 00:00

Password File
[oracle@db ~]$ scp /u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl oracle@192.168.10.20:/u01/app/oracle/product/12.2.0/dbhome_1/dbs/
oracle@192.168.10.20's password:
orapworcl 100% 3584 3.5KB/s 00:00

Then we change our password file for standby database as
[oracle@standby dbs]$ mv orapworcl orapwstby

------>Standby Database<------

Step 12: Create new directories for new database data files and control files as
[oracle@standby oracle]$ mkdir -p /u01/app/oracle/oradata/stby
[oracle@standby oracle]$ mkdir -p /u01/app/oracle/recovery_area/stby
[oracle@standby oracle]$ mkdir -p /u01/app/oracle/admin/stby/adump

Step 13: Set Up the Environment to Support the Standby Database
Perform the following steps to create a Windows-based service, create a password file,
set up the Oracle Net environment, and create a SPFILE.

13.1 If the standby database is going to be hosted on a Windows system, then use the
ORADIM utility to create a Windows service. For example:
WINNT> oradim –NEW –SID boston –STARTMODE manual

13.2 Copy the remote login password file from the primary database system to the standby database system.
This step is optional if operating system authentication is used for administrative
users and if SSL is used for redo transport authentication. If not, then copy the
remote login password file from the primary database to the appropriate directory
on the physical standby database system.

We use second method here as in Step 11.

13.3 Configure and start a listener on the standby system if one is not already configured.
[oracle@standby admin]$ pwd
/u01/app/oracle/product/12.2.0/dbhome_1/network/admin
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=orcl)
(GLOBAL_DBNAME=orcl.xxx.com)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
)

(SID_DESC=
(SID_NAME=stby)
(GLOBAL_DBNAME=stby.xxx.com)
(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

13.4 Create Oracle Net service names
(If we want to use hostname then we have to add each hostname in '/etc/hosts' for both machine)
[oracle@standby ~]$ cd /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/
[oracle@standby admin]$ cat tnsnames.ora
STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

We can check with tnsping from each machine as
[oracle@db ~]$ tnsping stby
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 08-DEC-2017 18:38:34
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stby)))
OK (0 msec)

[oracle@standby admin]$ tnsping orcl
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 01-DEC-2017 15:44:40
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

13.5 On an idle standby database, use the SQL CREATE statement to create a server parameter file for the standby
database from the text initialization parameter file that was edited in Step 8.

[oracle@stand dbs]$ mv /tmp/initst.ora /u01/app/oracle/product/12.2.0/dbhome_1/dbs

SQL> create spfile from pfile;
File created.

13.6 If the primary database has a database encryption wallet, then copy it to the
standby database system and configure the standby database to use this wallet.

Note: The database encryption wallet must be copied from the primary database
system to each standby database system whenever the master encryption key is updated.
Encrypted data in a standby database cannot be accessed unless the standby
database is configured to point to a database encryption wallet or hardware
security module that contains the current master encryption key from the primary database.

Step 14: Add Standby Database parameters to system file for oracle which is used for ora
environment.
[oracle@standby admin]$ vi /etc/oratab
stby:/u01/app/oracle/product/12.2.0/dbhome_1:N

Step 15: Start the Physical Standby Database
Here we assume that all required files (control file, backup files) transferred from primary are located in /tmp directory)

Do followings to mount standby database.
oracle@standby admin]$ rman target=/
RMAN> startup nomount;
RMAN> restore controlfile from '/tmp/stby.ctl';
RMAN> alter database mount;
RMAN> catalog start with '/tmp/o1_mf_nnndf_FOR_STBY_f21wgkrc_.bkp';
RMAN> restore database;
RMAN> recover database;

Step 16: Add Standby Redo Logs
Because we use transport mode - Log Writer (LGWR) we have to add standby redo logs.
Note: If neither the ARCH or LGWR attribute is specified, the default is ARCH.

How many standby redo log file do we need to add???

It is recommended that we have 1 more standby redo log file than the source (primary) database at least with the same size.
We have 3 redo logs which type 'ONLINE'. So we will add 4 standby redo log for standby datbase.

SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
Database altered.

SQL> select group#, type, status from v$logfile order by group#;

GROUP# TYPE STATUS
--------- ------- -------
1 ONLINE
2 ONLINE
3 ONLINE
4 STANDBY
5 STANDBY
6 STANDBY
7 STANDBY

Step 17: Issue the following command to start Redo Apply
SQL> alter database recover managed standby database disconnect from session;
Database altered.
The statement includes the DISCONNECT FROM SESSION option so that Redo Apply runs in a background session.

To stop Redo Apply
SQL> alter database recover managed standby database cancel;

Step 18: Verify the Physical Standby Database Is Performing Properly
Query the V$MANAGED_STANDBY view to verify that redo is being transmitted from the primary database and applied to the standby database.

SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';

CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR RFS 1 33 IDLE
N/A MRP0 1 33 APPLYING_LOG

The query output should show one line for the primary database with a CLIENT_PROCESS of LGWR. This indicates that redo
transport is functioning correctly and the primary redo thread is being sent to the standby.

SQL> select thread#, sequence#, applied from v$archived_log;

THREAD# SEQUENCE# APPLIED
--------- ---------- ---------
1 54 YES
1 55 YES
1 56 IN-MEMORY

Friday, September 29, 2017

Active Database Duplication (non-ASM to non-ASM on separate hosts)

Target (source) Host: Red Hat Enterprise Linux Server release 7.2 (Maipo)

Destination Host: CentOS Linux release 7.3.1611 (Core)

DB: 11.2.0.4.0

Target DB Name: maliyye

Destination DB Name: alfamal

Steps 1,2,9,10,12 will be executed from target (source) host.

Steps 3,4,5,6,7,8,11,13,14,15 will be executed from destination host.

1. Create a service for new database(alfamal) in tnsnames.ora file in target host.

[oracle@db admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

maliyye =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 112.100.11.25)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = maliyye)

)

stby =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 112.100.11.26)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = stby)

)

alfamal =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 112.100.11.31)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME =alfamal)

)

2. Edit listener.ora file on target host.

[oracle@db admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=alfamal)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME=alfamal)

)

ADR_BASE_LISTENER = /u01/app/oracle

And the restart listener: lsnrctl stop, lsnrctl start

3. Install only database software on destination host and create listener with netca.

4. Edit listener file on destination host as

[oracle@alfa admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = alfa)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=alfamal)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME=alfamal)

)

ADR_BASE_LISTENER = /u01/app/oracle

And the restart listener: lsnrctl stop, lsnrctl start

5. Create a service for new database(alfamal) on destination host

[oracle@alfa admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

maliyye =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 112.100.11.25)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = maliyye)

)

alfamal =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 112.100.11.31)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME =alfamal)

)

6. Add a line in the file /etc/oratab to reflect the new database instance on destination host

alfamal:/u01/app/oracle/product/11.2.0/dbhome_1:N

7. Set environment variables for duplicated database on destination host as

[oracle11g@orcl11g oracle]$ . oraenv

ORACLE_SID = [maliyye] ? alfamal

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

8. Create folders in destination host for duplicated database

[oracle@alfa ~]$ mkdir -p /u01/app/oracle/oradata

[oracle@alfa ~]$ mkdir -p /u01/app/oracle/fast_recovery_area

[oracle@alfa ~]$ mkdir -p /u01/app/oracle/admin/alfamal/adump

[oracle@alfa ~]$ mkdir -p /u01/app/oracle/admin/alfamal/dpdump

9. Create pfile from target (source) database.

SQL> create pfile='$ORACLE_HOME/dbs/initsource.ora' from spfile;

File created.

10. Copy the initialization parameter file from the target (source) database to destination database

oracle11g@orcl11g oracle]$ cp $ORACLE_HOME/dbs/initsource.ora

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initalfamal.ora

11. Edit the pfile initalfamal.ora as:

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

*.audit_trail='db'

*.compatible='11.2.0.4.0'

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

*.db_block_size=8112

*.db_domain=''

*.db_name='alfamal'

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

*.db_recovery_file_dest_size=83687091200

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

*.dispatchers=''

*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

*.log_archive_max_processes=5

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.db_create_file_dest='/u01/app/oracle/oradata'

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

db_file_name_convert=("/u01/app/oracle/oradata/maliyye","/u01/app/oracle/oradata/alfamal")

log_file_name_convert=("/u01/app/oracle/oradata/maliyye","/u01/app/oracle/oradata/alfamal")

12. Copy the password file from target to destination host as well

oracle11g@db~]$ cp $ORACLE_HOME/dbs/orapwmaliyye

$ORACLE_HOME/dbs/orapwalfamal

13. Startup the destination database in nomount mode using modified parameter file and create spfile from it.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initalfamal.ora';

ORACLE instance started.

Total System Global Area 2388422656 bytes

Fixed Size 2215664 bytes

Variable Size 1342177552 bytes

Database Buffers 1023410176 bytes

Redo Buffers 20611264 bytes

SQL> sho parameter db_name

NAME TYPE VALUE

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

db_name string alfamal

SQL> create spfile from pfile;

File created.

14. Connect to target and auxiliary instance then duplicate database.

Note: We have to connect auxiliary(destination) database with TNS name. If not duplication will fail.

[oracle@alfa admin]$ rman target sys/maliyye12@maliyye auxiliary=sys/maliyye12@alfamal

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 29 11:43:11 2017

connected to target database: MALIYYE (DBID=2910117303)

connected to auxiliary database: ALFAMAL (not mounted)

RMAN> duplicate target database to ALFAMAL from active database;

Starting Duplicate Db at 29-SEP-17

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=66 device type=DISK

contents of Memory Script:

{

sql clone "create spfile from memory";

}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 254738432 bytes

Fixed Size 2252176 bytes

Variable Size 180355696 bytes

Database Buffers 67108864 bytes

Redo Buffers 5021696 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''MALIYYE'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''ALFAMAL'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

backup as copy current controlfile auxiliary format '/u01/app/oracle/oradata/alfamal/control01.ctl';

restore clone controlfile to '/u01/app/oracle/fast_recovery_area/alfamal/control02.ctl' from

'/u01/app/oracle/oradata/alfamal/control01.ctl';

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''MALIYYE'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''ALFAMAL'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 254738432 bytes

Fixed Size 2252176 bytes

Variable Size 180355696 bytes

Database Buffers 67108864 bytes

Redo Buffers 5021696 bytes

Starting backup at 29-SEP-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=4 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_maliyye.f tag=TAG20170929T114514 RECID=2 STAMP=955971914

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 29-SEP-17

Starting restore at 29-SEP-17

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=66 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 29-SEP-17

database mounted

contents of Memory Script:

{

set newname for datafile 1 to

"/u01/app/oracle/oradata/alfamal/system01.dbf";

set newname for datafile 2 to

"/u01/app/oracle/oradata/alfamal/sysaux01.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/alfamal/undotbs01.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/alfamal/users01.dbf";

set newname for datafile 5 to

"/u01/app/oracle/oradata/alfamal/index1";

backup as copy reuse

datafile 1 auxiliary format

"/u01/app/oracle/oradata/alfamal/system01.dbf" datafile

2 auxiliary format

"/u01/app/oracle/oradata/alfamal/sysaux01.dbf" datafile

3 auxiliary format

"/u01/app/oracle/oradata/alfamal/undotbs01.dbf" datafile

4 auxiliary format

"/u01/app/oracle/oradata/alfamal/users01.dbf" datafile

5 auxiliary format

"/u01/app/oracle/oradata/alfamal/index1" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

Starting backup at 29-SEP-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/maliyye/sysaux01.dbf

output file name=/u01/app/oracle/oradata/alfamal/sysaux01.dbf tag=TAG20170929T114521

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/maliyye/system01.dbf

output file name=/u01/app/oracle/oradata/alfamal/system01.dbf tag=TAG20170929T114521

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/maliyye/users01.dbf

output file name=/u01/app/oracle/oradata/alfamal/users01.dbf tag=TAG20170929T114521

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/maliyye/index1

output file name=/u01/app/oracle/oradata/alfamal/index1 tag=TAG20170929T114521

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/maliyye/undotbs01.dbf

output file name=/u01/app/oracle/oradata/alfamal/undotbs01.dbf tag=TAG20170929T114521

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 29-SEP-17

sql statement: alter system archive log current

contents of Memory Script:

{

backup as copy reuse

archivelog like "/u01/app/oracle/fast_recovery_area/MALIYYE/archivelog/2017_09_29/o1_mf_1_205_dwvv1jtj_.arc" auxiliary format

"/u01/app/oracle/fast_recovery_area/ALFAMAL/archivelog/2017_09_29/o1_mf_1_205_%u_.arc" ;

catalog clone recovery area;

switch clone datafile all;

}

executing Memory Script

Starting backup at 29-SEP-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=205 RECID=396 STAMP=955971968

output file name=/u01/app/oracle/fast_recovery_area/ALFAMAL/archivelog/2017_09_29/o1_mf_1_205_16sfluc1_.arc RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Finished backup at 29-SEP-17

searching for all files in the recovery area

List of Files Unknown to the Database

=====================================

File Name: /u01/app/oracle/fast_recovery_area/ALFAMAL/archivelog/2017_09_29/o1_mf_1_205_16sfluc1_.arc

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /u01/app/oracle/fast_recovery_area/ALFAMAL/archivelog/2017_09_29/o1_mf_1_205_16sfluc1_.arc

datafile 1 switched to datafile copy

input datafile copy RECID=2 STAMP=955971967 file name=/u01/app/oracle/oradata/alfamal/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=3 STAMP=955971967 file name=/u01/app/oracle/oradata/alfamal/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=4 STAMP=955971967 file name=/u01/app/oracle/oradata/alfamal/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=955971967 file name=/u01/app/oracle/oradata/alfamal/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=6 STAMP=955971967 file name=/u01/app/oracle/oradata/alfamal/index1

contents of Memory Script:

{

set until scn 5557210;

recover

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 29-SEP-17

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 205 is already on disk as file /u01/app/oracle/fast_recovery_area/ALFAMAL/archivelog/2017_09_29/o1_mf_1_205_16sfluc1_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/ALFAMAL/archivelog/2017_09_29/o1_mf_1_205_16sfluc1_.arc thread=1 sequence=205

media recovery complete, elapsed time: 00:00:01

Finished recover at 29-SEP-17

Oracle instance started

Total System Global Area 254738432 bytes

Fixed Size 2252176 bytes

Variable Size 180355696 bytes

Database Buffers 67108864 bytes

Redo Buffers 5021696 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''ALFAMAL'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set db_name = ''ALFAMAL'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 254738432 bytes

Fixed Size 2252176 bytes

Variable Size 180355696 bytes

Database Buffers 67108864 bytes

Redo Buffers 5021696 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ALFAMAL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ( '/u01/app/oracle/oradata/alfamal/redo01.log' ) SIZE 50 M REUSE,

GROUP 2 ( '/u01/app/oracle/oradata/alfamal/redo02.log' ) SIZE 50 M REUSE,

GROUP 3 ( '/u01/app/oracle/oradata/alfamal/redo03.log' ) SIZE 50 M REUSE

DATAFILE

'/u01/app/oracle/oradata/alfamal/system01.dbf'

CHARACTER SET AL32UTF8

contents of Memory Script:

{

set newname for tempfile 1 to

"/u01/app/oracle/oradata/alfamal/temp01.dbf";

switch clone tempfile all;

catalog clone datafilecopy "/u01/app/oracle/oradata/alfamal/sysaux01.dbf",

"/u01/app/oracle/oradata/alfamal/undotbs01.dbf",

"/u01/app/oracle/oradata/alfamal/users01.dbf",

"/u01/app/oracle/oradata/alfamal/index1";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/alfamal/temp01.dbf in control file

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/alfamal/sysaux01.dbf RECID=1 STAMP=955971980

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/alfamal/undotbs01.dbf RECID=2 STAMP=955971980

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/alfamal/users01.dbf RECID=3 STAMP=955971980

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/alfamal/index1 RECID=4 STAMP=955971980

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=955971980 file name=/u01/app/oracle/oradata/alfamal/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=955971980 file name=/u01/app/oracle/oradata/alfamal/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=955971980 file name=/u01/app/oracle/oradata/alfamal/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=955971980 file name=/u01/app/oracle/oradata/alfamal/index1

Reenabling controlfile options for auxiliary database

Executing: alter database force logging

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 29-SEP-17

RMAN>

15. Test the duplicated database.

[oracle@alfa ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 29 17:17:52 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS

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

OPEN

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/alfamal/control01.ctl

/u01/app/oracle/fast_recovery_area/alfamal/control02.ctl

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/alfamal/redo03.log

/u01/app/oracle/oradata/alfamal/redo02.log

/u01/app/oracle/oradata/alfamal/redo01.log

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/app/oracle/oradata/alfamal/system01.dbf

/u01/app/oracle/oradata/alfamal/sysaux01.dbf

/u01/app/oracle/oradata/alfamal/undotbs01.dbf

/u01/app/oracle/oradata/alfamal/users01.dbf

/u01/app/oracle/oradata/alfamal/index1

Tuesday, September 12, 2017

MySQL Source

MySql

https://medium.com/datadenys/tuning-mysql-8-0-server-for-better-performance-238e463f5583

https://severalnines.com/blog/mysql-performance-cheat-sheet/

https://hub.packtpub.com/optimize-mysql-8-servers-clients/

https://haydenjames.io/mysql-8-sample-config-tuning/

https://catonrug.blogspot.com/2019/11/reclaim-data-on-data-table.html

https://stackoverflow.com/questions/32760202/buffered-warning-changed-limits-max-connections-214-requested-800
https://stackoverflow.com/questions/22217186/difference-between-opened-files-and-open-files-in-mysql

http://www.datadisk.co.uk/html_docs/mysql/architecture.htm
https://lalitvc.wordpress.com/2016/11/03/mysql-architecture-and-components/
https://thinkingmonster.wordpress.com/database/mysql/mysql-architecture/
http://www.oracle.com/us/corporate/pricing/price-lists/mysql-pricelist-183985.pdf
http://blog.ronenb.com/2011/07/27/mysql-cluster-manager-hands-on/

https://blog.dotkam.com/2007/04/10/mysql-reset-lost-root-password/
https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html
https://www.shellhacks.com/mysql-run-query-bash-script-linux-command-line/

https://opensourcedbms.com/dbms/passwordless-authentication-using-mysql_config_editor-with-mysql-5-6/
https://dev.mysql.com/doc/refman/8.0/en/mysql-config-editor.html
https://www.thegeekstuff.com/2017/08/mysqlbinlog-examples/

MySql Performance Links
https://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof/
http://www.speedemy.com/mysql/17-key-mysql-config-file-settings/innodb_log_file_size/
https://www.linode.com/docs/databases/mysql/how-to-optimize-mysql-performance-using-mysqltuner/
https://hostadvice.com/how-to/how-to-tune-and-optimize-performance-of-mysql-5-7-on-ubuntu-18-04/ ( https://github.com/RootService/tuning-primer )
https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/
https://www.saotn.org/mysql-innodb-performance-improvement/
https://dba.stackexchange.com/questions/161617/how-to-find-out-innodb-write-buffer-efficiency
https://dba.stackexchange.com/questions/1261/how-to-safely-change-mysql-innodb-variable-innodb-log-file-size/1265
https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size
https://www.saotn.org/restore-single-mysql-table-full-mysqldump-backup-file/
https://spin.atomicobject.com/2010/12/13/extracting-a-table-from-a-mysql-dump/
https://dzone.com/articles/how-to-choose-the-mysql-innodb-log-file-size
https://www.percona.com/blog/2016/05/31/what-is-a-big-innodb_log_file_size/
https://dba.stackexchange.com/questions/114696/mysql-find-full-table-scan-queries

To merge backed up data with existing data
https://dba.stackexchange.com/questions/33807/dump-of-a-mysql-table-on-import-replaced-existing-records
http://www.mysqlab.net/knowledge/kb/detail/topic/backup/id/5285
https://dba.stackexchange.com/questions/5033/mysqldump-with-insert-on-duplicate
https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/

MySQL Innodb Cluster Setup
https://santhinesh.wordpress.com/2018/09/21/innodb-cluster-in-mysql-8-0-11/
https://dinfratechsource.com/2018/11/10/mysql-innodb-cluster-a-complete-high-availability-solution-for-mysql/
http://shrenikp.blogspot.com/2018/10/mysql-innodb-cluster-troubleshooting.html
http://lefred.be/content/mysql-innodb-cluster-how-to-handle-fc/

Percona

https://www.percona.com/blog/2014/09/01/galera-replication-how-to-recover-a-pxc-cluster/

https://www.percona.com/blog/2012/08/17/percona-xtradb-cluster-multi-node-writing-and-unexpected-deadlocks/

http://galeracluster.com/documentation-webpages/restartingcluster.html

https://www.howtoforge.com/tutorial/mysql-hot-backup-with-percona-xtrabackup-on-ubuntu-16-04/

http://galeracluster.com/documentation-webpages/deploymentvariants.html

https://severalnines.com/blog/full-restore-mysql-galera-cluster-backup

https://www.hostlatte.com/blog/error-1805-hy000-column-count-mysql-user-wrong-table-probably-corrupted/
https://www.percona.com/blog/2012/12/19/percona-xtradb-cluster-pxc-what-about-gra_-log-files/
https://www.percona.com/doc/percona-monitoring-and-management/conf-mysql.html

Note: How do I completely remove MySQL from a linux installation?
If they installed it via yum on a RedHat based distribution you can remove with:
[redhat@redhat ~]$yum remove mysql-server

Swap Space

About Swap Space

Oracle Linux uses swap space when your system does not have enough physical memory to store the text (code) and data pages that the processes are currently using. When your system needs more memory, it writes inactive pages to swap space on disk, freeing up physical memory. However, writing to swap space has a negative impact on system performance, so increasing swap space is not an effective solution to shortage of memory. Swap space is located on disk drives, which have much slower access times than physical memory. If your system often resorts to swapping, you should add more physical memory, not more swap space.

You can configure swap space on a swap file in a file system or on a separate swap partition. A dedicated swap partition is faster, but changing the size of a swap file is easier. Configure a swap partition if you know how much swap space your system requires. Otherwise, start with a swap file and create a swap partition when you know what your system requires.

Viewing Swap Space Usage

To view a system's usage of swap space, examine the contents of /proc/swaps:

# cat /proc/swaps
Filename                Type        Size      Used   Priority
/dev/sda2               partition   4128760   388    -1
/swapfile               file        999992    0      -2

In this example, the system is using both a 4-gigabyte swap partition on /dev/sda2 and a one-gigabyte swap file, /swapfile. The Priority column shows that the system preferentially swaps to the swap partition rather than to the swap file.

You can also view /proc/meminfo or use utilities such as free, top, and vmstat to view swap space usage, for example:

# grep Swap /proc/meminfo
SwapCached:          248 kB
SwapTotal:       5128752 kB
SwapFree:        5128364 kB
# free | grep Swap
Swap:      5128752        388    5128364

Creating and Using a Swap File

Note

Configuring a swap file on a btrfs file system is not supported.

To create and use a swap file:

Use the dd command to create a file of the required size (for example, one million one-kilobyte blocks):
```
# dd if=/dev/zero of=/swapfile bs=1024 count=1000000
```
Initialize the file as a swap file:
```
# mkswap /swapfile
```
Enable swapping to the swap file:
```
# swapon /swapfile
```
Add an entry to /etc/fstab for the swap file so that the system uses it following the next reboot:
```
/swapfile       swap       swap       defaults       0 0
```

Creating and Using a Swap Partition

To create and use a swap partition:

Use fdisk to create a disk partition of type 82 (Linux swap) or parted to create a disk partition of type linux-swap of the size that you require.
Initialize the partition (for example, /dev/sda2) as a swap partition:
```
# mkswap /dev/sda2
```
Enable swapping to the swap partition:
```
# swapon /swapfile
```
Add an entry to /etc/fstab for the swap partition so that the system uses it following the next reboot:
```
/dev/sda2       swap       swap       defaults       0 0
```

Removing a Swap File or Swap Partition

To remove a swap file or swap partition from use:

Disable swapping to the swap file or swap partition, for example:
```
# swapoff /swapfile
```
Remove the entry for the swap file or swap partition from /etc/fstab.
Optionally, remove the swap file or swap partition if you do not want to use it in future.

Reference: https://docs.oracle.com/cd/E52668_01/E54669/html/ol7-s4-storage.html