Oracle 12c Background Process- CTWR (Change Tracking Writer)

 

CTWR (Change Tracking Writer) :-

  • CTWR (Change Tracking Writer) is the background process responsible for tracking the blocks.
  • CTWR will be useful in RMAN. Optimized incremental backups using block change tracking (faster incremental backups) using a file (named block change tracking file).
  • CTWR process is to write the changed block information in BCT file.

Block Change Tracking (file):-

RMAN’s change tracking feature for incremental backups improves incremental backup performance by recording changed blocks in each datafile in a change tracking file. If change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile.

Change tracking is disabled by default, because it introduces some minimal performance overhead on database during normal operations. However, the benefits of avoiding full datafile scans during backup are considerable, especially if only a small percentage of data blocks are changed between backups. If backup strategy involves incremental backups, then we should enable change tracking.

One change tracking file is created for the whole database. By default, the change tracking file is created as an Oracle managed file in DB_CREATE_FILE_DEST. We can also specify the name of the block change tracking file, placing it in any desired location.

Using change tracking in no way changes the commands used to perform incremental backups, and the change tracking files themselves generally require little maintenance after initial configuration.

From Oracle 10g, the background process Block Change Tracking Writer (CTWR) will do the job of writing modified block details to block change tracking file.

In a Real Applications Clusters (RAC) environment, the change tracking file must be located on shared storage accessible from all nodes in the cluster.

Oracle saves enough change-tracking information to enable incremental backups to be taken using any of the 8 most recent incremental backups as its parent.

Although RMAN does not support backup and recovery of the change-tracking file itself, if the whole database or a subset needs to be restored and recovered, then recovery has no user-visible effect on change tracking. After the restore and recovery, the change tracking file is cleared, and starts recording block changes again. The next incremental backup after any recovery is able to use change-tracking data.After enabling change tracking, the first level 0 incremental backup still has to scan the entire datafile, as the change tracking file does not yet reflect the status of the blocks. Subsequent incremental backup that use this level 0 as parent will take advantage of the change tracking file.

You can view the size of the CTWR dba buffer by looking at v$sgastat:

SQL> SELECT * FROM v$sgastat WHERE name like ‘CTWR%’;

POOL           NAME          BYTES
-------- -------------       -------
large pool  CTWR dba buffer  1220000

Enable Block Change Tracking:-

SQL> show parameter db_create_file_dest

NAME                TYPE   VALUE
----------------    -----  -----
db_create_file_dest string


SQL> alter system set db_create_file_dest='/home/oracle/app/oracle' scope=both;
System altered.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

System altered.

select filename, status, bytes from   v$block_change_tracking

STATUS       FILE                                    BYTES
-----------  -------------------------------------- --------------
ENABLED      /home/oracle/app/oracle/01_mf_nsrv.chg  10,000,000

Manually specify location for BCT :

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/home/oracle/app/oracle/block_change_tracking.dbf’;

or

The REUSE option tells Oracle to overwrite any existing file with the specified name.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/home/oracle/app/oracle/block_change_tracking.dbf’ REUSE;

Disable Block Change Tracking:-

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

SQL> select * from V$BLOCK_CHANGE_TRACKING;

STATUS       FILE                                    BYTES
-----------  --------------------------------------  --------------
DISABLED      /home/oracle/app/oracle/               10,000,000
              block_change_tracking.dbf

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba                      FB Group:https://www.facebook.com/groups/894402327369506/                FB Page: https://www.facebook.com/dbahariprasath/?                 Twitter: https://twitter.com/hariprasathdba

Oracle 12c Background Process-MMON (Manageability Monitor)

 

The manageability monitor process supports the Automatic
Workload Repository (AWR) by capturing statistics, monitoring
thresholds, and taking snapshots.

This is related to performance tuning and troubleshooting.

Restart MMON process without bouncing the database:

alter system enable restricted session;

alter system disable restricted session;

check status of mmon process, if it found as not running.

$ ps -ef |grep mmon

SQL> grant restricted session to public;
SQL> alter system enable restricted session;
SQL> alter system disable restricted session;
SQL> revoke restricted session from public;

 

The manageability monitor process (MMON) performs many tasks related to the Automatic Workload Repository (AWR).

For example, MMON writes when a metric violates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects.

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Oracle GoldenGate vs Oracle Data Guard

The configuration of Oracle GoldenGate / Data Guard has the purpose of synchronizing data between two or more systems .

The basic features of the two products may look similar but takes GoldenGate uses replication while dataguard not.

Oracle Data Guard and GoldenGate involving at least two systems where transactional data from one database is required to be moved to another database. The purpose of the replication of data can be disaster recovery, migration of data or preparation of a secondary system.

Data Guard is best for disaster recovery and data protection problems, GoldenGate is a more flexible heterogeneous replication mechanism and is also able to transform the data while it is being replicated.

  • Data Guard is an Oracle specific technology while GoldenGate support heterogeneous database systems including all the major RDBMS as DB2,Sybase, MySQL .
  • Data Guard supports active-passive replication. One of the database is the primary database and the other one is in an inactive Data Guard mode.
  • GoldenGate supports an active-active replication mode and allows both systems to work simultaneously while maintaining the data integrity.
  • GoldenGate allows transformation of the data, with conflict management while it is being replicated between both database systems.
  • GoldenGate allows replication across platform. Data can be extracted from a Unix platform and replicated to an Oracle database running on platform Windows.
  • GoldenGate has many case of utilization. The use of flat files for data transportation and the support of heterogeneous systems makes the technology so very interesting

 

GoldenGate is the best solution:
  • Replicate one or many tables to a read-write database.
  • Replicate and transform tables.
  • Bi-directional replication.
  • Zero or near-zero downtime upgrade.
  • Heterogeneous replication

GoldenGate replication.

• Zero Downtime Upgrade/Migration: You may want to setup an exact replica of your database for upgrade or migration, keep the data in sync till the last moment.

High Availability: Your applications cannot afford to have downtime so you want to have two databases up and running at the same time to support those applications.

Query or Reporting: You want to offload the reporting or long query operations from your production database.

Real Time BI: For your data warehouse requirements.

Data Synchronization: You want to Synchronize data across different departments of the company (different databases).

Furthermore the GoldenGate software supports extraction from the legacy systems and does not have any limitation on the distance either. Additionally, the trail files generated by GoldenGate capture can also be compressed and encrypted.

Oracle Data Guard

Used for

  • High availability
  • Data protection
  • Disaster recovery
  • Primary database workload reduction

Database roles

In a data guard environment, one database is a primary database, the others are standby databases. These roles are mutually exclusive.
The role of a database can be queried in the column database_role in v$database.

Primary database

The primary db is the »real« production database.
The primary db must run in archive log mode and with forced logging.

Standby databases

Standby databases are (transactionally consistent) copies of the primary database.
Up to 30 standby databases are supported.

Physical and logical standby databases

A standby DB is either physical or logical standby database. (A standby database can also become a snapshot standby database).
A physical database is an exact byte-wise copy of the primary database.
A logical database has the same data as the primary database, but is not necessarily stored in the same db blocks.
The physical database is kept synchronized with redo apply, the logical database with SQL apply.

Cascading standby database

A physical standby database might be configured to receive redo from a primary database and relay it to a another standby database.
In this case, the relaying database is  called a cascading standby database. The receiving standby databases is also known as a terminal destination.
With such a configuration, the load on the primary database can be reduced.

SQL*Plus

Some SQL*Plus statements come with the standby keyword which indicates that the statement should be applied on the standby databases.

Role of redo

Data Guard basically works as follows:

  • Archived redo log files from the production DB are copied to the standby DB.
  • They are then registered with the standby DB.
  • Finally, using media recovery, the redo is used to roll forward the standby database.

Preventing physical errors

A standby database validates the redo it receives from the primary DB.
Thus, physical errors (such as data corruption) on the primary DB are not propagated to standby databases.

Changing roles of databases

The role (primary vs standby) of a database can be changed by either a

  • switch over or
  • a fail-over
A switch over is a planned role transition. In a switch over, the primary database becomes a standby database while (one of) the standby databases becomes the primary database.
A switch over is performed in two steps:
1) the primary DB becomes a standby DB
2) a standby DB becomes a primary DB.
A fail over occurs when the primary database becomes unavailable. In this case, one of the standby databases becomes the new primary database.

RAC

The primary db and the standby databases can be run as single instance or real application clusters(RAC).

Active Data Guard

Active data guard offers the same features as data guard plus that it standby databases are opened read only while the copying of data takes place
Thus, it allows to query the standby databases without impacting the performance of the production database (for example to create resource-intensive reports).
The standbase database is called physical standby database.
The buzzword is real time query.
Active data guard must be licenced seperatly.

Data protection modes

Data Guard runs in one of three data protection modes. This allows to balance data availability against system performance requirements.

These protection modes are:

  • Maximum performance (default)
  • Maximum protection
  • Maximum availability

Automatic gap detection

If connectivity is lost between the primary and one or more standby databases
Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Oracle Database Cloning Using Cold Backup

Cold database backup means while taking backup or doing cloning, we need to shutdown the source database. This method is usually used for test database when database is in noarchivelog mode.

Note- Both the source and target db server should be on same platform and the target db version will be that of the source db.So make sure oracle binary is already installed on target db server.

SOURCE DB – jupiter
TARGET DB – sakthi

Steps:

1.Take backup of controlfile as trace:[SOURCE DB]

2. Note down the location of datafiles[SOURCE DB]

3. Shutdown the database:[SOURCE DB]

4. Copy the data files and temp files to the target db server

5. Prepare the init file for target db:[TARGET DB]

6. Start the database in nomount stage:[TARGET DB]

7. Re-recreate the controlfile [ TARGET DB ]

8. Open the database in resetlog mode

Send the datafile,log files and control files to target destination

 

[oracle@jupiter rock]$ scp -r *.log oracle@192.168.1.135:/u01/ram

The authenticity of host '192.168.1.135 (192.168.1.135)' can't be established.

RSA key fingerprint is 9b:35:ae:ab:bf:6b:33:b4:43:86:f5:98:8b:bb:11:1c.

Are you sure you want to continue connecting (yes/no)? oracle

Please type 'yes' or 'no': yes

Warning: Permanently added '192.168.1.135' (RSA) to the list of known hosts.

oracle@192.168.1.135's password:

redo01.log                     100%  200MB   3.1MB/s   01:05    

redo02.log                     100%  200MB   4.0MB/s   00:50    

redo03.log                     100%  200MB   3.5MB/s   00:58    

[oracle@jupiter rock]$ scp -r users01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

users01.dbf                  100% 5128KB   5.0MB/s   00:01    

[oracle@jupiter rock]$ scp -r undotbs01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

undotbs01.dbf               100%   70MB   3.7MB/s   00:19    

[oracle@jupiter rock]$ scp -r temp01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

temp01.dbf                100%   32MB   2.9MB/s   00:11    

[oracle@jupiter rock]$ scp -r system01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

system01.dbf             100%  810MB   3.0MB/s   04:31    

[oracle@jupiter rock]$ scp -r sysaux01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

sysaux01.dbf            100%  490MB   3.2MB/s   02:34    

[oracle@jupiter u01]$ scp -r ctrl.sql oracle@192.168.1.135:/u01/ram
oracle@192.168.1.135's password:

ctrl.sql               100% 5865     5.7KB/s   00:00

Now all the files are in the target destination.

[oracle@sakthi ram]$ ls

ctrl.sql         redo01.log  redo03.log    system01.dbf  undotbs01.dbf

initjupiter.ora  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

Edit the control file

[oracle@sakthi ram]$ vi ctrl.sql

CREATE CONTROLFILE SET  DATABASE "ROCK" RESETLOGS ARCHIVELOG

    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/ram/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/u01/ram/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/u01/ram/redo03.log'  SIZE 200M BLOCKSIZE 512
DATAFILE
  '/u01/ram/system01.dbf',
  '/u01/ram/sysaux01.dbf',
  '/u01/ram/undotbs01.dbf',
  '/u01/ram/users01.dbf'
CHARACTER SET AL32UTF8 

Edit the PFILE

[oracle@sakthi ram]$ vi initjupiter.ora

db_name=rock
control_files='/u01/ram/ctrl.ctl'

NOW open the database.

[oracle@sakthi ram]$ export ORACLE_SID=rock

[oracle@sakthi ram]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 18 01:20:35 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup pfile='/u01/ram/initjupiter.ora' nomount

ORACLE instance started.
Total System Global Area  243269632 bytes
Fixed Size                  8619256 bytes
Variable Size             180357896 bytes
Database Buffers           50331648 bytes
Redo Buffers                3960832 bytes

SQL> @ctrl.sql
Control file created.

SQL> alter database open resetlogs;
Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
------    -------------
ROCK      READ WRITE
Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

How To Uninstall Oracle Database 12c

oracle software deinstall

Starting with Oracle Database 12c Release 1 (12.1.0.2), the roothas.sh script replaces the roothas.pl script in the Oracle Grid Infrastructure home.

[oracle@Ram dbhome_1]$ cd deinstall/

[oracle@Ram deinstall]$ ls
bootstrap_files.lst deinstall deinstall.xml readme.txt sshUserSetup.sh
bootstrap.pl deinstall.pl jlib response utl

The most common method of running the deinstallation tool is to use the version installed in the Oracle home being removed.

The deinstallation tool determines the software configuration for the local Oracle home, and then provides default values at each prompt.

You can either accept the default value, or override it with a different value.

If the software in the Oracle_home is not running (for example, after an unsuccessful installation), then the deinstallation tool cannot determine the configuration, and you must provide all the configuration details either interactively or in a response file.

You must run the deinstallation tool from the same release to remove Oracle software. Do not run the deinstallation tool from a later release to remove Oracle software from an earlier release.

For example, do not run the deinstallation tool from the Oracle Database 12.2 installation media to remove Oracle software from an existing 11.2.0.4 Oracle home.

[oracle@Ram deinstall]$ ./deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /tmp/deinstall2019-01-17_07-49-52PM/logs/

############ ORACLE DECONFIG TOOL START ############


############ DECONFIG CHECK OPERATION START ###################
## [START] Install check configuration ##


Checking for existence of the Oracle home location /u01/app/oracle/product/12.2.0/dbhome_1
Oracle Home type selected for deinstall is: Oracle Single Instance Database
Oracle Base selected for deinstall is: /u01/app/oracle
Checking for existence of central inventory location /u01/app/oraInventory

## [END] Install check configuration ##


Network Configuration check config START

Network de-configuration trace file location: /tmp/deinstall2019-01-17_07-49-52PM/logs/netdc_check2019-01-17_07-51-25-PM.log

Network Configuration check config END

Database Check Configuration START

Database de-configuration trace file location: /tmp/deinstall2019-01-17_07-49-52PM/logs/databasedc_check2019-01-17_07-51-27-PM.log

Use comma as separator when specifying list of values as input

Specify the list of database names that are configured in this Oracle home []: 
Database Check Configuration END
Oracle Configuration Manager check START
OCM check log file location : /tmp/deinstall2019-01-17_07-49-52PM/logs//ocm_check8100.log
Oracle Configuration Manager check END

############# DECONFIG CHECK OPERATION END ######################


############## DECONFIG CHECK OPERATION SUMMARY ####################
Oracle Home selected for deinstall is: /u01/app/oracle/product/12.2.0/dbhome_1
Inventory Location where the Oracle home registered is: /u01/app/oraInventory
Checking the config status for CCR
Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/tmp/deinstall2019-01-17_07-49-52PM/logs/deinstall_deconfig2019-01-17_07-51-16-PM.out'
Any error messages from this session will be written to: '/tmp/deinstall2019-01-17_07-49-52PM/logs/deinstall_deconfig2019-01-17_07-51-16-PM.err'

############### DECONFIG CLEAN OPERATION START ######################
Database de-configuration trace file location: /tmp/deinstall2019-01-17_07-49-52PM/logs/databasedc_clean2019-01-17_07-51-59-PM.log

Network Configuration clean config START

Network de-configuration trace file location: /tmp/deinstall2019-01-17_07-49-52PM/logs/netdc_clean2019-01-17_07-51-59-PM.log

De-configuring backup files...
Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

Oracle Configuration Manager clean START
OCM clean log file location : /tmp/deinstall2019-01-17_07-49-52PM/logs//ocm_clean8100.log
Oracle Configuration Manager clean END

################### DECONFIG CLEAN OPERATION END ######################


################## DECONFIG CLEAN OPERATION SUMMARY ###################
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
#######################################################################


############# ORACLE DECONFIG TOOL END #############

Using properties file /tmp/deinstall2019-01-17_07-49-52PM/response/deinstall_2019-01-17_07-51-16-PM.rsp
Location of logs /tmp/deinstall2019-01-17_07-49-52PM/logs/

############ ORACLE DEINSTALL TOOL START ############

############# DEINSTALL CHECK OPERATION SUMMARY #####################
A log of this session will be written to: '/tmp/deinstall2019-01-17_07-49-52PM/logs/deinstall_deconfig2019-01-17_07-51-16-PM.out'
Any error messages from this session will be written to: '/tmp/deinstall2019-01-17_07-49-52PM/logs/deinstall_deconfig2019-01-17_07-51-16-PM.err'

############## DEINSTALL CLEAN OPERATION START ####################
## [START] Preparing for Deinstall ##
Setting LOCAL_NODE to Ram
Setting CRS_HOME to false
Setting oracle.installer.invPtrLoc to /tmp/deinstall2019-01-17_07-49-52PM/oraInst.loc
Setting oracle.installer.local to false

## [END] Preparing for Deinstall ##

Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START

Detach Oracle home '/u01/app/oracle/product/12.2.0/dbhome_1' from the central inventory on the local node : Done

Delete directory '/u01/app/oracle/product/12.2.0/dbhome_1' on the local node : Done

Delete directory '/u01/app/oraInventory' on the local node : Done

The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is not empty.

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END


## [START] Oracle install clean ##


## [END] Oracle install clean ##


############### DEINSTALL CLEAN OPERATION END #####################


############## DEINSTALL CLEAN OPERATION SUMMARY ###################
Successfully detached Oracle_home '/u01/app/oracle/product/12.2.0/dbhome_1' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/12.2.0/dbhome_1' on the local node.
Successfully deleted directory '/u01/app/oraInventory' on the local node.
Oracle Universal Installer cleanup was successful.


Run 'rm -r /etc/oraInst.loc' as root on node(s) 'Ram' at the end of the session.

Run 'rm -r /opt/ORCLfmap' as root on node(s) 'Ram' at the end of the session.
Run 'rm -r /etc/oratab' as root on node(s) 'Ram' at the end of the session.
Review the permissions and contents of '/u01/app/oracle' on nodes(s) 'Ram'.
If there are no Oracle home(s) associated with '/u01/app/oracle', manually delete '/u01/app/oracle' and its contents.
Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################


############# ORACLE DEINSTALL TOOL END #############

[oracle@Ram deinstall]$

 

If the software in the Oracle_home is not running (for example, after an unsuccessful installation), then the deinstallation tool cannot determine the configuration and you must provide all the configuration details either interactively or in a response file.

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba Facebook:https://www.facebook.com/HariPrasathdba 
FB Group:https://www.facebook.com/groups/894402327369506/ 
FB Page: https://www.facebook.com/dbahariprasath/? 
Twitter: https://twitter.com/hariprasathdba

Installing Oracle Client In Silent Mode Using Response File

Though GUI is a preferable method to do client installation, But sometimes DBAs might be not to be able to xmanager to enable GUI for the server. In that case, installation using response file will be useful.

In this example, we will do the installation of Oracle client 11.2.0.4 version.

1. Download Oracle client software from oracle portal.- 

2. Copy and unzip the software in the server

3. Update the response file.

4. Now do the installation silently.

COMMAND – ./runInstaller -ignoreSysPrereqs -showProgress -silent -responseFile /software/client/response/client_install.rsp

 

[oracle@abhi Desktop]$ df -h
Filesystem   Size   Used   Avail   Use%   Mounted on
/dev/sda2    15G    12G    2.5G    83%      /
tmpfs        1.5G   188K   1.5G    1%       /dev/shm
/dev/sda1    2.0G   81M    1.8G    5%       /boot
/dev/sda5    3.9G   93M    3.6G    3%       /home
/dev/sda3    5.8G   211M   5.3G    4%       /tmp
/dev/sda7    20G    15G    3.7G    81%      /u01
.host:/      183G   26G    158G    14%      /mnt/hgfs

[oracle@abhi Desktop]$ cd /mnt/hgfs/
[oracle@abhi hgfs]$ ls
hero
[oracle@abhi hgfs]$ cd hero/
[oracle@abhi hero]$ ls
database p13390677_112040_Linux-x86-64_2of7.zip
p13390677_112040_Linux-x86-64_1of7.zip
[oracle@abhi hero]$ cd database/
[oracle@abhi database]$ ;s
bash: syntax error near unexpected token `;'
[oracle@abhi database]$ ls
install response runInstaller stage
readme.html rpm sshsetup welcome.html
[oracle@abhi database]$ cd response/
[oracle@abhi response]$ pwd
/mnt/hgfs/hero/database/response
[oracle@abhi response]$ cp dbca.rsp dbca.rsp_bkp
[oracle@abhi response]$ cp db_install.rsp db_install.rsp_bkp
[oracle@abhi response]$ cp netca.rsp netca.rsp_bkp
[oracle@abhi response]$ ls
dbca.rsp db_install.rsp netca.rsp
dbca.rsp_bkp db_install.rsp_bkp netca.rsp_bkp
[oracle@abhi response]$ vi dbca.rsp
[oracle@abhi response]$ vi dbca.rsp
[oracle@abhi response]$ dbca -silent -responseFile /mnt/hgfs/hero/database/response/dbca.rsp
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/silent/silent.log" for further details.
[oracle@abhi response]$

[oracle@abhi response]$ ps -ef | grep pmon
oracle 4340 1 0 21:59 ? 00:00:00 ora_pmon_SILENTDB
oracle 7724 3428 0 22:05 pts/0 00:00:00 grep pmon

[oracle@abhi response]$ export ORACLE_SID=SILENTDB
[oracle@abhi response]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 11 22:12:40 2019

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

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 name,open_mode from v$database;

NAME                          OPEN_MODE
---------                 --------------------
SILENT                        READ WRITE
SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                     TABLESPACE_NAME
------------------------------         -----------------------
/u01/app/oracle/oradata/silent/users01.dbf         USERS

/u01/app/oracle/oradata/silent/undotbs01.dbf       UNDOTBS1

/u01/app/oracle/oradata/silent/sysaux01.dbf        SYSAUX

FILE_NAME                                  TABLESPACE_NAME
------------------------                 --------------------
/u01/app/oracle/oradata/silent/system01.dbf    SYSTEM

Thus the oracle silent mode installation completed successfully.

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/                   FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

Create Oracle Pluggable Database Manually Using SQL Prompt

We can create a pluggable database in an existing multitenant database either using dbca or manually.

STEPS:

Connect to the container database:(ROOT)

[oracle@localhost ~]$ sqlplus sys/oracle@cdb1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 23 10:27:08 2015

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
-----------
CDB$ROOT
While creating PDB if you don’t mention FILE_NAME_CONVERT parameter then below error will come
SQL> create pluggable database dbwr admin user ram identified by ram;
create pluggable database dbwr admin user ram identified by ram                                                                        *
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified
Create PDB:(correct command)
SQL> create pluggable database dbwr admin user ram identified by ram FILE_NAME_CONVERT=('/home/oracle/app/oracle/oradata/cdb1/pdbseed','/home/oracle/app/oracle/oradata/cdb1/dbwr');


Pluggable database created.


SQL> select con_id,name,open_mode from v$Pdbs;

CON_ID NAME                OPEN_MODE
----------            -------------------
2 PDB$SEED               READ ONLY
3 ORCL                   READ WRITE
4 DBWR                   MOUNTED
-- Open the PDB
SQL> alter pluggable database dbwr open;

Pluggable database altered.



SQL> select con_id,name,open_mode from v$pdbs; 

CON_ID NAME                      OPEN_MODE
----------                       ----------
2 PDB$SEED                           READ ONLY
3 ORCL                               READ WRITE
4 DBWR                               READ WRITE

SQL> alter session set container=dbwr;

Session altered.

SQL> show con_name

CON_NAME
-------------
DBWR

SQL> select file_name from dba_data_files;

FILE_NAME
----------------------------------------------
/home/oracle/app/oracle/oradata/cdb1/dbwr/system01.dbf
/home/oracle/app/oracle/oradata/cdb1/dbwr/sysaux01.dbf

SQL>

Continue reading “Create Oracle Pluggable Database Manually Using SQL Prompt”

Oracle 12c-Multitenant Database Backup Using Rman

With multi tenant feature introduced in oracle 12c, New commands are there for taking rman backup of pluggable database and root container database.

Backup of complete container( ROOT + ALL PDBS )

Taking backup of particular pluggable database:(backup pluggable database PDB5)

Backup of tablespace of pluggable database

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/                   FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

 

Oracle-Delete Old Archives Using RMAN

Deletion of archive logs in oracle 11g database by comparing the sysdate

Prepare the shell script.

cat rman_arch_del.sh

#!/bin/bash
export ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2.0
export ORACLE_SID=FRANCE
export PATH=$ORACLE_HOME/bin:$PATH

delBackup () {
rman log=/home/oracle/arch_del.log << EOF
connect target /
DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE ‘sysdate-1’;
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;
exit
EOF
}
# Main

delBackup

Now configure in crontab:

00 30 * * * /u01/app/oracle/rman_arch_del.sh > /tmp/rmanarch.log

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/                   FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

 

 

Oracle Pluggable Database Automatic Startup

In oracle 12c , when we startup the CDB, the PDBS will be in MOUNTED stage.

We need to open them manually using alter pluggable PDB open. To make the PDBS open automatically, we can use the saved state option .

This feature is available from Oracle 12.1.0.2  onwards

1.  Restart the container database:

2. Check the status of PDBS:

We can see the PDBS are in MOUNTED stage after starting CDB. Lets open them

3. Open the PDBS:

Now use save state command to save the states, so that next time ,when container db restarts, PDBs will in READ WRITE MODE automatically.

4. Save the PDB state:

5. check the saved state in dba_pdb_saved_states

6.Bounce the container database, to check the PDB state:

DISCARD STATE OPTION:

We can discard the saved state, so that next time CDB restarts, PDBs will start with MOUNT STATE only.
Lets discard state for PDB6.

1. Bounce the CDB to check the PDB State:

We can observe that, only PDB6 started in MOUNTED stage, because we have discarded the saved state for PDB6 .

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/                   FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba