Peoplesoft









============================
https://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tprt/concept_ApplicationServers-c071d0.html
https://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tprt/concept_ApplicationServers-c071d0.html
Workstation listener (WSL)
The workstation listener monitors Oracle Tuxedo ports for initial connection requests sent from the PeopleTools development environment. After the workstation listener accepts a connection from a workstation, it directs the request to a workstation handler. From that point, the Microsoft Windows workstation interacts with the workstation handler to which it is assigned.

Workstation handler (WSH)

The workstation handler processes the requests that it receives from the workstation listener. A unique port number identifies a workstation handler. The port numbers for the workstation handler are selected (internally by Oracle Tuxedo) from a specified range of numbers. You can configure multiple workstation handlers to take care of demand increases; new processes are created as other processes become overloaded.

Oracle Jolt server listener (JSL)
The Oracle Jolt server listener applies only to browser requests. The Oracle Jolt server listener monitors the Oracle Jolt port for connection requests sent from the browser through the web server. After the Oracle Jolt server listener accepts a connection, it directs the request to an Oracle Jolt server handler. From that point, the browser interacts with the Oracle Jolt server handler. This is analogous to the relationship between the workstation server listener and workstation server handler.

Oracle Jolt server handler (JSH)

The Oracle Jolt server handler applies only to browser requests. The Oracle Jolt server handler processes the requests that it receives from the Oracle Java server listener. The port numbers for the Oracle Jolt server handler are selected internally by Oracle Tuxedo in sequential order.

Request queues

Each type of server process has a service request queue that it shares with other servers of the same type (as in PSAPPSRV on APPQ and PSQCKSRV on QCKQ). The workstation handler and Oracle Jolt server handler insert requests into the appropriate queue, and then the individual server processes complete each request in the order that it appears.

Server processes

The server processes act as the heart of the application server domain. They maintain the SQL connection and make sure that each transaction request gets processed on the database and that the results are returned to the appropriate origin.


PeopleSoft Server Processes

Multiple server processes run in an application server domain. A server process is executable code that receives incoming transaction requests. The server process carries out a request by making calls to a service, such as MgrGetObject.

Server processes invoke services to perform application logic and issue SQL to the RDBMS. Each application server process, such as PSAPPSRV, PSQCKSRV, PSQRYSRV, PSSAMSRV, or PSOPTENG, establishes and maintains its own connection to the database.
The server process waits for the service to finish, then returns information to the device that initiated the request, such as a browser. While a server process waits for a service to finish, other transaction requests wait in a queue until the current service finishes. A service may take a fraction of a second to finish or several seconds, depending on the type and complexity of the service. When the service finishes, the server process is then available to process the next request in the corresponding queue
You need to configure only those server processes that your implementation requires per domain. The minimum server processes that a domain requires are PSAPPSRV and PSSAMSRV.
You can configure multiple instances of the same server processes to start when you start the application server domain. This helps you handle predicted workloads. Furthermore, Oracle Tuxedo can dynamically generate incremental server processes to handle increasing numbers of transaction requests. The capability to configure multiple server processes and generate incremental server processes contributes to the application server's scalability.
The following list describes the possible server processes included in an application server domain. Depending on the configuration options that you choose, not all of the server processes will necessarily be a part of every domain.
•PSAPPSRV
This process performs functional requests, such as building and loading components (which were known as panel groups in previous releases). It also provides the memory and disk-caching feature for PeopleTools objects on the application server. PSAPPSRV is required to be running in any domain.
•PSQCKSRV
This process performs quick, read-only SQL requests. This is an optional process designed to improve performance by reducing the workload of PSAPPSRV.
•PSQRYSRV
This process is designed to handle any query run by PeopleSoft Query. This is an optional process designed to improve performance by reducing the workload of PSAPPSRV.
•PSSAMSRV
This SQL application manager process handles the conversational SQL that is mainly associated with PeopleSoft Application Designer. This process is required to be running on any domain.
•PSOPTENG
This optimization engine process provides optimization services in PeopleSoft Optimization Framework. You need to configure this process in a server domain only if you want to use the optimization plug-in delivered with PeopleSoft applications.
The following set of server processes is used for application messaging. (Your messaging domain must also contain PSAPPSRV and PSSAMSRV, the required server processes.)
•PSMSGDSP
•PSMSGHND
•PSPUBDSP
•PSPUBHND
•PSSUBDSP
•PSSUBHND

note:You can examine servers by using the ps -ef command in UNIX or Task Manager in Microsoft Windows. The PeopleSoft configuration utility, PSADMIN, also offers a monitoring utility.
Services
When a PeopleSoft application sends a request to the application server, it sends a service name and a set of parameters, such as MgrGetObject and its parameters. Oracle Tuxedo then queues the transaction request to a specific server process that is designed to handle certain services.
When a server process starts, it advertises to the system the predefined services it handles. You can see the association between the many services and server processes by reviewing the PSAPPSRV.UBB file.


Oracle Middleware

PeopleSoft software uses Oracle Tuxedo, a middleware framework and transaction monitor, to manage database transactions. PeopleSoft software also uses Oracle Jolt, a Java API and class library, as the layer that facilitates communication between the PeopleSoft servlets on the web server and the application server. Both Oracle Tuxedo and Jolt are required.



https://docs.oracle.com/cd/E15645_01/pt850pbr0/eng/psbooks/tsec/chapter.htm?File=tsec/htm/tsec03.htm

PeopleSoft Internet Architecture Security


PeopleSoft Internet Architecture security is also known as runtime security. Only authorized users can connect to the web and application server, and only authorized application servers can connect to a given database.

PeopleSoft software uses authentication tokens embedded in browser cookies to authorize users and enable single sign-in throughout the system. To secure links between elements of the system, including browsers, web servers, application servers, and database servers, PeopleSoft software incorporates a combination of SSL security and Oracle Tuxedo and Oracle Jolt encryption.

SSL is a protocol developed by Netscape that defines an interface for data encryption between network nodes. To establish an SSL-encrypted connection, the nodes must complete the SSL handshake. The simplified steps of the SSL handshake are as follows:

1 Client sends a request to connect.

2 Server responds to the connect request and sends a signed certificate.

3 Client verifies that the certificate signer is in its acceptable certificate authority list.

4 Client generates a session key to be used for encryption and sends it to the server encrypted with the server's public key (from the certificate received in step 2).

5 Server uses a private key to decrypt the client generated session key.

Establishing an SSL connection requires two certificates: one containing the public key of the server (server certificate or public key certificate) and another to verify the certification authority that issued the server certificate (trusted root certificate). The server needs to be configured to issue the server certificate when a client requests an SSL connection, and the client needs to be configured with the trusted root certificate of the certificate authority that issued the server certificate.

The nature of those configurations depends on both the protocol being used and the client and server platforms. In most cases you replace HTTP with LDAP. SSL is a lower level protocol than the application protocol, such as HTTP or LDAP. SSL works the same regardless of the application protocol.


Note. Establishing SSL connections with LDAP is not related to web server certificates or certificates used with PeopleSoft integrations



The system uses SSL encryption in the following locations:

Between the browser and the web server.

Between the application server and the integration gateway.

Between the integration gateway and an external system.

The system uses Oracle Tuxedo and Oracle Jolt encryption in these locations:

Between the web server and the application server.

Between the integration gateway and a PeopleSoft system (Oracle Jolt only).

Security between the application server and database is supplied by RDBMS connectivity.

PeopleSoft Integration Broker and portal products have additional security concerns, which are addressed in the documentation for those products.




PeopleSoft Authorization IDss


The PeopleSoft system uses various authorization IDs and passwords to control user access. You use PeopleTools Security to assign two of these IDs: the user ID and the symbolic ID.

This section discusses:

User IDs.

Connect ID.

Access IDs.

Symbolic IDs.

Administrator access.




User IDs
User id is nothing but peoplesoft user uses his id and password at peoplesoft login page.  as peoplesoft DBA , you assign a user with user id and password

The system can also use a user id stored within an LDAP directory server.


what is connect id ?

Connect ID
The connect ID performs the initial connection to the database.

Note. PeopleSoft no longer creates users at the database level.

what is the use of connect ID?

if connect ID is used the you do not have to create a new database user for every PeopleSoft user that you add to the system.

why connected Id is required ?

Note. A connect ID is required for a direct connection (two-tier connection) to the database.


Application servers and two-tier Microsoft Windows clients require a connect ID.

You have to specify the connect ID for an application server in the Signon section of the PSADMIN utility.

For Microsoft Windows clients, you specify the connect ID in the Startup tab of PeopleSoft Configuration Manager.

You can create a connect ID by running the Connect.SQL and Grant.SQL scripts.

Note. When performing a database compare or copy, both databases must have the same connect ID.

Warning! Without a connect ID specified, the system assumes the workstation is accessing PeopleSoft through an application server. The option to override the database type is disabled.




Access IDs
When you create any user ID, you must assign it an access profile, which specifies an access ID and password.

The PeopleSoft access ID is the RDBMS ID with which PeopleSoft applications are ultimately connected to your database after the PeopleSoft system connects using the connect ID and validates the user ID and password. An access ID typically has all the RDBMS privileges necessary to access and manipulate data for an entire PeopleSoft application. The access ID should have Select, Update, and Delete access.

Users do not know their corresponding access IDs. They just sign in with their user IDs and passwords. Behind the scenes, the system signs them into the database using the access ID.

If users try to access the database directly with a query tool using their user or connect IDs, they have limited access. User and connect IDs only have access to the few PeopleSoft tables used during sign-in, and that access is Select-level only. Furthermore, PeopleSoft encrypts the sensitive data that resides in those tables.s



Note. Access profiles are used when an application server connects to the database, when a Microsoft Windows workstation connects directly to the database, and when a batch job connects directly to the database. Access profiles are not used when end users access applications through PeopleSoft Pure Internet Architecture. During a PeopleSoft Pure Internet Architecture transaction, the application server maintains a persistent connection to the database, and the end users leverage the access ID that the application server domain used to sign in to the database.


Note. PeopleSoft suggests that you only use one access ID for your system. Some RDBMS do not permit more than one database table owner. If you create more than one access ID, it may require further steps to ensure that this ID has the correct rights to all PeopleSoft system tables.


Symbolic IDs
PeopleSoft encrypts the access ID when it is stored in the PeopleTools security tables. Consequently, an encrypted value cannot be readily referenced or accessed. So when the access ID, which is stored in PSACCESSPRFL, must be retrieved or referenced, the query selects the appropriate access ID by using the symbolic ID as a search key.

The symbolic ID acts as an intermediary entity between the user ID and the access ID. All the user IDs are associated with a symbolic ID, which in turn is associated with an access ID. If you change the access ID, you need to update only the reference of the access ID to the symbolic ID in the PSACCESSPRFL table. You do not need to update every user profile in the PSOPRDEFN table.


Administrator Access
As an administrator, you must customize your own user definition. PeopleSoft delivers at least one full-access user ID with each delivered database. Your first task should be to sign in with this ID and personalize it for your needs or to create a new, full-access ID, being sure to specify a new password. You should change the passwords of all delivered IDs as soon as possible.

Note. PeopleSoft-delivered IDs and passwords are documented in your installation manual.
When you install PeopleSoft, you are prompted for an RDBMS system administrator ID and password. This information is used to automatically create a default access profile. If you will be using more than one access profile, set up the others before creating any new PeopleSoft security definitions. Most sites only use one access profile.

The number of database-level IDs you create is up to your site requirements. However, in most cases, having fewer database-level IDs reduces maintenance issues.

For example, if you implement pure LDAP authentication, at a minimum you need two database-level IDs—your access ID and your connect ID. With this scenario, in PeopleSoft you need to maintain only a symbolic ID to reference the access ID and maintain a user ID that the application server uses during sign-in. With this minimal approach, each user who needs a two-tier connection, to run an upgrade, for example, could use the same user ID that the application server uses.



PeopleSoft Sign-in :

This section discusses:

PeopleSoft sign-in.

Directory server integration.

Authentication and signon PeopleCode.

Single signon.


PeopleSoft Sign-in
The most common direct sign-in to the PeopleSoft database is the application server sign-in.

These are the basic steps that are taken when the application server signs in to the database:

1.Initial connection.

The application server starts and uses the connect ID and user ID specified in its configuration file (PSAPPSRV.CFG) to perform the initial connection to the database.

2.The server performs a SQL Select statement on security tables.

After the connect ID is verified, the application server performs a Select statement on PeopleTools security tables, such as PSOPRDEFN, PSACCESSPRFL, and PSSTATUS. From these tables, the application server gathers such items as the user ID and password, symbolic ID, access ID, and access password. After the application server has the required information, it disconnects.

3.The server reconnects using the access ID.

When the system verifies that the access ID is valid, the application server begins the persistent connection to the database that all PeopleSoft Pure Internet Architecture and Microsoft Windows three-tier clients use to access the database. Typically, the users signing in using a Microsoft Windows workstation are developers using PeopleSoft Application Designer.


Note. A Microsoft Windows workstation attempting a two-tier connection uses the same process as the application server.


PeopleSoft recommends that all connectivity be made through either a three-tier Microsoft Windows client or through the browser. A two-tier connection is no longer necessary other than for the application server, PeopleSoft Process Scheduler, or for a user who will be running upgrades or PeopleSoft Data Mover scripts.

Sign-in PeopleCode does not run during a two-tier connection, so maintaining two-tier users in an LDAP server is not supported.



Directory Server Integration

PeopleSoft recognizes that your site uses software produced by numerous vendors, and each different product requires security authorizations for users. Most of these products adhere to the model that includes user profiles and roles (or groups) to which users belong. PeopleSoft enables you to integrate your authentication scheme for the PeopleSoft system with your existing infrastructure. You can reuse user profiles and roles that are already defined within an LDAP directory service.

Organizations typically store user profiles in a central repository that serves user information for all of the programs that require it. The central repository is typically an LDAP directory server.

A directory server enables you to maintain a single, centralized user profile that you can use across all of your PeopleSoft and non-PeopleSoft applications. This approach reduces redundant maintenance of user information stored separately throughout your enterprise, and it reduces the possibility of user information getting out of synchronization.

You always maintain permission lists and roles using PeopleTools Security. However, you can maintain user profiles in PeopleTools Security or with an external LDAP server.


Authentication and Signon PeopleCode :

You can store PeopleSoft passwords within PeopleTools, in the PSOPRDEFN table. You can also store and maintain user passwords and the rest of the user profile data in an LDAP directory server. PeopleSoft retrieves the information stored in an external directory server using a combination of the User Profiles component interface and sign-in PeopleCode.

If you decide to reuse existing user profiles stored in a directory server, you don’t need to perform dual maintenance on the two copies of the user data—one copy in the LDAP server and one copy in PSOPRDEFN. PeopleSoft ensures that the user information stays synchronized. If you configure LDAP authentication, you maintain your user profiles in LDAP and not in PeopleTools Security.

Signon PeopleCode copies the most recent user profile data from a directory server to the local database whenever a user signs in. PeopleSoft applications reference the user information stored in the PeopleSoft database rather than making a call to the LDAP directory each time the system requires user profile information. Signon PeopleCode ensures the local database has a current copy of the user profile based on the information in the directory. Each time the user signs in, signon PeopleCode checks to see to see if the row in the user profile cache needs to be updated.

The sign-in process occurs as follows:

The user enters a user ID and password on the sign-in page.

PeopleTools attempts to authenticate the user against the PSOPRDEFN table.

Signon PeopleCode runs.

The default signon PeopleCode program updates the user profile based on the current data stored in the directory server.

You can use signon PeopleCode and business interlinks to synchronize the local copy of the user profile with any data source at sign-in time; the program that ships with PeopleTools is designed to synchronize the user profile with an LDAP directory server only. Because the sign-in program is PeopleCode, you can modify it, incorporating any of the PeopleSoft integration technologies that PeopleCode supports.

To edit the signon PeopleCode program, you open the LDAP function library record and use the PeopleCode editor to customize the PeopleCode. Developers who modify the sign-in PeopleCode program need to have a good understanding of PeopleCode and the integration features it offers.


Single Signon :

PeopleSoft Pure Internet Architecture uses browser cookies for seamless single signon across all PeopleSoft nodes. A node refers to a database and the application servers connected to it. For example, a user can complete a PeopleSoft Human Resources transaction, and then click a link for a PeopleSoft Financials transaction without ever reentering a password. Single signon is especially important to the PeopleSoft portal, which aggregates content from several different applications and data sources into a single, integrated display.

Process Scheduler Cache


Process scheduler has it’s own cache apart from the application server cache. It keeps its own copies of Component Interfaces,
Application Engine PeopleCode and Application engine sections and SQL.

In order to clear the Process Scheduler cache , the process scheduler needs to be shut down.


Component Interface
Application Engine People Code
Application Engine sections
SQL

To delete process scheduler cache, you need to manually delete all the files and folders
in this directory $PS_HOME/appserv/prcs/{YOUR-ENV-NAME}/cache on the process scheduler machine(s).




Application Engine
If you are having an issue with an application engine still running old code after a project migration then ask for a Process Scheduler cache clear.

The other 2 types of server cache DO NOT need to be cleared.

If your application engine calls a component interface and you recently migrated a change to the component interface, generally a Process Scheduler cache clear is needed. The other 2 types of server cache DO NOT need to be cleared.

COBOL
COBOL does not use CACHE. If you are having a COBOL issue cache has nothing to do with it. Do not waste your time trying.

SQR
SQR does not use CACHE. If you are having a SQR issue cache has nothing to do with it. Do not waste your time trying.

Questions

1)what is oracle server ?

2)what is oracle instance ?

3)what is SGA ? and what is it consist of ?

4)what is redolog buffer ?

5)what happens when commit complete?

6)what is purpose of redolog buffer and database buffer cache ?



1)what are the types of standby databases ?

2)What is physical standby database ?

3)what is logical standby database ? what is advantage and disadvantage ?

4)what is snapshot standby database ? what is advantage and disadvantage ?

5)what are types of  apply services ?

6)what is redo apply services ?

7)what is sql apply services ?




How to get the information of redo data generated per second ?

Oracle Server :

It is a database management system used to manage the data and Oracle server consist of instance and

database

SGA : (system global area)

Its nothing but a group of shared memory structures that contains data and control information of

oracle instance. Each instance has its own SGA

When users are connected to server then data in the instance is shared among users hence it is also

called as SHARED GLOBAL AREA

THE SGA AND BAGROUND processes constitute an instance

SGA consist of

1) Database buffer cache

2) Redo log buffer cache

3) Java pool

4) Large pool

5) Shared pool

Shared pool

1) Data dictionary cache

2) Library cache

1) Shared sql area

2) Shared plsql area

Redolog Buffer

It is a circular buffer in SGA and it contains information of all changes made to the database.

The changed information is stored in redo entries or redo record. A redo record is a group of change

vectors each of which is a description of a change made to a single block .

(For Ex: if you change the salary value of an employee in employee table , you generate a redo record

containing change vector that describes the changes to the data segment block of the table, the undo

segment data block and the transaction table of the undo segments.)

(Redo records contain all the information needed to reconstruct changes made to the database. During

media recovery, the database will read change vectors in the redo records and apply the changes to the

relevant blocks.)

Every changes made to the database is written to redolog buffer cache before it is written to database

buffer cache



Because The goals of the Logbuffer and Database buffer is entirely different .

The Purpose of the logbuffer is to temporarily keep the changed transaction and get them quickly to

written to current online redo log file whereas the purpose of database buffer is to keep the frequently

accessed blocks in memory as long as possible to increase the performance of processes (using

frequently accessed blocks.)

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

When you see commit complete, oracle guarantees that a record of that transaction has been safely

written to the current online redo log file on disk. That does not mean the modified block has been

written to the appropriate data file

The transaction information in the online redologbuffer is very frequently written to online redolog file

by logwriter

whereas modified block in database buffer are intermittently written to datafile by database writer

periodically , all changed block buffers or dirty buffers in memory are written to the datafiles by

database writer . This is known as a checkpoint. When checkpoint occurs , the checkpoint process

records the current checkpoint SCN in the controlfiles and the corresponding scn in the datafile headers

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

Rman Backup

Rman incremental backup can be used to take the backup only those data that are changed since last

backup.

How algoritham of incremental backup works.?

Each datablock in a datafile consist of System change number(SCN). so this specifies the most recent

was made to the block . so during incremental backup, rman checks or reads the each and every

datablock in the input file and compares it to the checkpoint SCN of parent incremental backup.

if SCN of the datablock is greater than that of checkpoint SCN of parent backup then rman copies that

block

Block change tracking features

if you enable this feature then rman can able to refer change tracking file to identify the changed blocks

in the datafile without scanning the full contents of datafile so it increases performance

Incremental backup can be level 0 or Level 1

Level 0 backup is nothing but full database backup and it is a base for subsequent backup

Level 1 backup may be either differential backup or cumulative backup

Differential backup is nothing but which backups all the blocks that are changed since most recent

Level 0 or Level 1 backup

Cumulative backup is nothing but which backups all the blocks that are changed since most recent level

0 backup

Oracle System Identifier

It is a unique name for Oracle database instance on a specific host

When a client wants to connect to a database he can specify the SID in oracle net architecture or use

a net service name then oracle database convert a service name into an oracle_home and Oracle_sid.

For Single instance , there will be one to one relationship will be there between database and instance

For Rac instance, there will be one to many relationship will be there between database And instance

Overview of Instance and database startup

Startup Nomount

When oracle server starts the instance then it searches for server parameter file if it is not found then it

Looks for init parameter file . even if init parameter also not found then it throws an error. Otherwise

It reads the parameter file and allocates the memory depending upon parameter value

It starts the background process and opens the alertlog file and trace file and write the all explicit

Parameter settings to the alert_log file in valid formats

Startup nomount command is used during

1)Creation of database

2)Recovery of controlfiles or recreation of controlfiles

Startup mount

In this stage, oracle instance mounts database. If instance need to mount the database then it requires

the control file and control file location is defined by control_files parameter. so through that

It locates the datafile and redologfiles but not opened

This is command is used during

1) Datafile rename

2) Redologfile rename

3) When converting from non archivelog to archive log (OR) vice – versa

4) When performing full database recovery

5) When enabling flashback

Startup

In this stage, database is opend

Datafile ,redologfile are opened and read

If database is found to be inconsistent then smon performs instance recovery

Startup force

This is command is used when database is in hung state

This command aborts the currently running instance and again creates the instance,mounts the

database and opens

Startup upgrade : used during upgrade

Startup restrict : This command is used when you want to perform some maintainance tasks

When this command is executed then existing users connections will be continued . users who have

Restricted session privilege can connect to database but only user who don’t have Restricted session

privilege can’t connect to the database.

Readonly mode:

In this mode, oracle allows only read only transactions to execute and controlfiles remains open to

Update and writing to operating system files like alert log,tracefiles and audit files will be continued

The users who have been granted administration privilege can only shutdown the database.

Shutdown

Database shutdown is done in 3 stages

1 Database closed

2 Database dismounted

3 Oracle instance shutdown

If database shutdown with any option other than abort then data in SGA is written to redolog

Files and datafiles and Datafilescloses online redologfiles and datafiles

In this condition ,controlfile is opened even after database is closed

Abnormal Shutdown

If a shutdown abort or abnormal termination occurs then instance of the database closes and shutdown

The database simultaneously. So DBWR doesn’t write the data from SGA to datafile and redologs

So when database is opened then database requires instance recovery

System Change Number(SCN)

It is database ordering primitive , which specifies the most recent changes was made to the database

Data Defination Language statement creates schema objects,changes the structure of the object and

drop the schema objects

Ex: Create , Alter, Drop, Truncate

Granting and revoking(Grant and Revoke)

Auditing on and off(audit)

Data Manipulation Language:

These are the statements which is used to modify the data in the existing object

Ex: select , delete and update

What is Schema?

A named collection of objects is known as schema

Schema object : Logical structure of the data stored in schema is known as Schema object

How DML quiries works in oracle or How oracle database process DML queries?

Oracle uses read consistency mechanism to retrive data.

This mechanism uses undo data to show the past version of the data and guarentees that all the

data retrived by the query are consistent at that time.

For ex: Assume that session fires query that retrives the 100 rows and while the query being

processed , another session fires a update statement to modify 75 th block against the same

table.and doesn’t commit.When session 1 reaches 75 th block. It realizes that change so uses

undo Data to retrive old and sends the output to the user and makes sure that all the data

retrived at that point are consisten at a point in a time.

Database buffer Cache:

The database buffer cache is a portion of the SGA that holds the copies of datablocks read

from the datafiles.

Organization of databuffer cache.

The buffer in cache are organized into 2 lists

1)write list

2) Least recently used list(LRU)

Write list holds dirty buffers which contains data that has been modified but has not yet been

written to disk or datafiles

List recently used list contains free buffers , pinned buffers and dirty buffer that are not yet

been written to the write list

Free buffers are buffers that do not contain any useful data and are avaible for use

Pinned buffers are buffer which are currently being used

If oracle user process needs to access the piece of data then it first searches for the database

buffer cache . if it found the required data then its called cache it otherwise it has to copy the

data from datafile to buffer cache before copying , the process must first find the enough

space in buffer . if enough space is not there then the process should signal DBWR to write

down some dirty buffer to datafile on disk.

Cache miss: if required data is not found on database buffer cache for a oracle user process

then it is called cache miss

DataDictionary is a collection of tables and view containing reference about the database ,its

structure and its users

Rman

To Create Script

Create script my_script(script name)

{

Backup database;

}

To run script

Run

{

Execute script my_script(script name);

}

Replacing script

Replace script my_script(script name)

{

}

To list script

List script names

To get the code of the script

Print script script_name

Create user username Identified by passaword

Default tablespacetablespace_name

Temporary tablespace temp

Quota 100m on users(tablespace_name)

Rman

Scanerio

If you want to take backup of the database by skipping offline tablespaces and readonlytablespaces

Then which command will be used

Rman>Backup database

Skip readonly

Skip offline;

Scenario

Assume that you want skip the tablespace every time while taking the backup so how do you do that

RMAN>configure exclude for tablespace users;

If you want override this feature while taking backup then use

Rman>backup database NOEXCLUDE;

Scenario

Assume that one day last night backup is not succededso you want to backup those data that is not

backed up so how would you do that

Rman>backup

NOT BACKED UP SINCE TIME ‘SYSDATE-1’

MAXSET SIZE 100M

DATABASE PLUS ARCHIVELOG

Oracle DBA roles and Responsibilities

1. Creating and managing development,testing and production databases.

2. Installing new version of oracle RDBMS.

3. Implementing backup and recovery of oracle database.

4. Administration all the database objects like tables, indexes, sequences, views, clusters and

packages and procedures.

5. Providing technical support to the application development team.

6. Create database users and assigning previleges as and when required.

7. Managing sharing of resources amongst applications.

8. Troublshooting problems regarding the databases, applications and development tools.

9. Migration of databases .

10. Interface with oracle corporation for technical support.

11. Upgradation of databases.

Asm Disk Groups

The creation of disk group involves the validation of disks to be added

Following are the attributes to validate a disk

1. The disks cannot be already in some other disk group

2. Disks must not have pre-existingValid Asmheader . if it has, then it can be override using FORCE

option.

Following example shows that a diskgroup is created using four disks that reside in storage array, with

redundancy being handled externally by the storage array.

SQL> SELECT NAME, PATH, MODE_STATUS, STATE FROM V$ASM_DISK;

NAME PATH MODE_ST STATE

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

/dev/rdsk/c3t19d5s4 ONLINE NORMAL

/dev/rdsk/c3t19d16s4 ONLINE NORMAL

/dev/rdsk/c3t19d17s4 ONLINE NORMAL

/dev/rdsk/c3t19d18s4 ONLINE NORMAL

SQL> CREATE DISKGROUP DATA EXTERNAL REDUNDANCY DISK

'/dev/rdsk/c3t19d5s4',

'/dev/rdsk/c3t19d16s4',

'/dev/rdsk/c3t19d17s4',

'/dev/rdsk/c3t19d18s4';

The following output, from v$ASM_DISKGROUP shows newly added disk groups.

SQL> SELECT NAME, STATE, TYPE, TOTAL_MB, FREE_MB FROM V$ASM_DISKGROUP;

NAME STATE TYPE TOTAL_MB FREE_MB

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

DATA MOUNTED EXTERN 34512 34101

After creation of disk group is successufully completed then metadata information like creation date,

disk group name and redundancy type is stored in system global area and on each disk header in the

disk

The following output shows how the V$ASM_DISK view reflects the disk state change after the disk is

incorporated into disk group.

SQL> SELECT NAME, PATH, MODE_STATUS, STATE, DISK_NUMBER FROM V$ASM_DISK;

NAME PATH MODE_ST STATE DISK_NUMBER

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

DATA_0000 /dev/rdsk/c3t19d5s4 ONLINE NORMAL 0

DATA_0001 /dev/rdsk/c3t19d16s4 ONLINE NORMAL 1

DATA_0002 /dev/rdsk/c3t19d17s4 ONLINE NORMAL 2

DATA_0003 /dev/rdsk/c3t19d18s4 ONLINE NORMAL 3

DATA_0000,DATA_0001,DATA_0002,DATA_0003 (disk_group_name+Disk_number)

The above are the automatically created disknames, so if you want to assign disk names then write as

follows

SQL> CREATE DISKGROUP DATA EXTERNAL REDUNDANCY DISK

'/dev/rdsk/c3t19d5s4' name DMX_disk1,

'/dev/rdsk/c3t19d16s4' name DMX_disk2,

'/dev/rdsk/c3t19d17s4' name DMX_disk3,

'/dev/rdsk/c3t19d18s4' name DMX_disk4;

Asm disk Name is used when performing disk management activities such as Drop disk or Resize disk.

Restore is a process of restoring files(datafile,controlfile,server parameter file and not online redologfile)

from backup and recovery is process of applying online redo logfiles on datafiles

Complete recovery means you can recover all the committed transactions until the point of failure or

before failure occurred

Dataguard operates on simple principle that is ship redo and then apply redo because redo contains all

of the information needed by oracle database to recover a database transactions

Difference between Dataguard and Remote Mirroring

Dataguard Remote Mirroing

Dataguard transmits only redo data(data or information) Remote Mirroing doesn’t have any

That is required to recover a database transactions) to sy Knowledge of an oracle transactions

Nchronize a standby database with its primary so it requires every write to every

file

2) it requires 7 times more network

Volume compared to network

needed for dataguard

3) 27 times more network I/O

Operations than dataguard

Primary database transaction generate redo records

A redo record , is also as redo entry , is made up of a group of change vectors, each of which is

description of a change made to a single block in the database.

For Ex: if you change the salary value of an employee in employee table , you generate a redo record

containing change vector that describes the changes to the data segment block to the table, the undo

segment data block and the transaction table of the undo segments.

Redo records contain all the information needed to reconstruct changes made to the database. During

media recovery, the database will read change vectors in the redo records and apply the changes to the

relevant blocks.

 A commit record Whenever a transaction is committed, the LGWR writes the transaction redo

records from the redo log buffer to an ORL and assigns a system change number (SCN) to identify

the redo records for each committed transaction. Only when all redo records associated with a

given transaction have been written to the ORL is the user process notified that the transaction has

been committed.

Redo Transport Services

When Transactions are committed , then primary database LGWR process writes the redo to its online

redo log files and at the same time LOG NETWORK SERVER(LNS) reads the redo records from Redo log

buffer and passes the redo to Oracle Network Services for the transmission to the standby databases

Redo records transmitted by the LNS are received at standby database by another dataguard process

called as Remote File Server(RFS)

Synchornous Redo Transport

It is also called as “ZERO DATA LOSS” method because LGWR is not allowed to acknowledge a commit

has succededuntill LNS confirm that redo required for recover the transaction has been written to

disk(redolog files) at standby databases

ASynchornous Redo Transport

It is reverse of Synchronous

Apply Services

Redo Apply(Physical standby)

SqlApply(Logical Standby)

Redo apply maintains the standby database that is an exact, block by block , physical copy of the primary

database

Types of database parameter files

1. SPFILE(server parameter file) : A binary text file that contains initialization parameter

2. PFILE(initialization parameter file) : A text file that contains initialization parameter

Oracle server

Oracle server is divided into

1) Database

2) Instance

Database inturn divided into

1) Logical structure

2) Physical structure

Logical structures are

1) Tablespace

2) Segments

3) Extents

4) Blocks

Physical structures

1) Datafiles

2) Controlfile

3) Redologfile

4) Archivelog file

5) Parameter file

6) Password file

7) Network file

Instance is divided into

1) Memmory Sturcture

2) Background Process

Memmory structures are divided into

1)SGA

2)PGA

SGA consist of

1) Shared pool area

2) Databuffer cache

3) Log buffer

4) Large pool

5) Java pool

PGA consist of

1) Session Information area

2) Cursor area

3) Software

4) statspack

Metadata is nothing but data about the data

5 Imp Background process

Database Writer(DBWR)

Dbwr writes the data from db buffer to datafile under the following situation

1) Checkpoint occurs.

2) Dirty buffer reach threshold.

3) When there are no free buffer.

4) When timeout occurs.

5) When tablespace put in offline mode

6) When tablespace put in begin backup mode

7) When tablespace made read only

8) When table is dropped or truncated

9) When RAC ping request is made

Logwriter(LGWR)

1) At commit.

2) When 1/3 rd of the buffer is full.

3) When more than 1MB of redo changes.

4) Every 3 secs.

5) Before DBWR writes.

Smon (system Monitor)

Smon is Responsible for Instance Recovery

Instance Recovery

1) Rolls forward what are the changes in the redologs

2) Opens the database for user access(if we are connecting to database means then smon is reason for

that)

3) Rollback uncommitted Transactions

4) Coalesces free space or combines free space

5) Deallocates temporary segments

Pmon (Process monitor)

Pmon periodically cleans up 1) the process that died abnormally

3) The session that were killed.

4) Detached transactions that have exceeded their idle timeout

5) Detached Network connections which exceeded their idle timeout.

Pmon is responsible for registering the information about instance and dispatcher process with

The network Listener

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

Checkpoint.

Periodically The dirty buffers in the databuffer cache is written to datafile that process is called is

Checkpoint.

When checkpoint occurs the ckpt process will update the control file and header of the datafile with the

current SCN number

Purpose of Checkpoint

1) It ensures that all the committed data is written to disk during consistent shutdown.

2) Ensures that dirty buffer in buffer cache is written to disk regurlarly

3) Reduce the time required for recovery in case of media failure or instance failure

When checkpoint occurs

1) When log switch occurs

2) When instance is shutdown with normal or immediate

3) When forced by initialization parameter FAST_START_MTTR_TARGET

4) When manually by DBA

5) When tablespace put in offline mode

6) When tablespace put in begin backup mode

7) When tablespace put in read only mode

Information about the checkpoint is recorded in alert log file if LOG_CHECKPOINT_TO_ALERT

initialization parameter is set to true.

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

You are required to have at least two online redo log groups in your database. Each online redo log group

must contain at

least one online redo log member

Online redo logs are crucial database files that store a record of transactions that have occurred in your

database. Online

redo logs serve several purposes:

n Provide a mechanism for recording changes to the database so that in the event of a media failure you

have a method

of recovering transactions.

n Ensure that in the event of total instance failure, committed transactions can be recovered (crash

recovery) even if

committed data changes have not yet been written to the data files.

n Allow administrators to inspect historical database transactions through the Oracle LogMiner utility.

The contents of the current online redo log files are not archived until a log switch occurs. This means

that if you lose all

members of the current online redo log file, then you'll most likely lose transactions

The contents of the current online redo log files are not archived until a log switch occurs. This means

that if you lose all

members of the current online redo log file, then you'll most likely lose transactions. Listed next are

several mechanisms

you can implement to minimize the chance of failure with the online redo log files:

n Multiplex groups to have multiple members.

n If possible, don't allow two members of the same group to share a controller.

n If possible, don't put two members of the same group on the same physical disk.

n Ensure operating system file permissions are set appropriately (restrictive so that only the owner of the

Oracle binaries

has permissions to write and read).

n Use physical storage devices that are redundant (that is, RAID).

n Appropriately size the log files so that they switch and are archived at regular intervals.

n Consider setting the archive_lag_target initialization parameter to ensure that the online redo logs

are switched at

regular intervals.

Note The only tool provided by Oracle that can protect you and preserve all committed transactions in the

event you

lose all members of the current online redo log group is Oracle Data Guard implemented in Maximum

Protection

Mode. Refer to MOS note 239100.1 for more details regarding Oracle Data Guard protection modes.

The clear logfile command will drop and re-create all members of a log group for you. You can issue

this

command even if you have only two log groups in your database.

If the clear logfile command does not succeed because of an I/O error and it's a permanent problem, then

you will need to

consider dropping the log group and re-creating it in a different location. See the next two subsections for

directions on how to drop and re-create a log file group

When Control file error or Media recover or Instance recovery occur

CF checkpoint SCN < Data file checkpoint SCN

"Control file too old" error

Ans : Restore a newer control file or recover with the using backup controlfile clause.

CF checkpoint SCN > Data file checkpoint SCN

Ans : Media recovery required . Most likely a data file has been restored from a backup. Recovery

is now required.

CF checkpoint SCN = Data file SCN Start up normally

None Database is in mount mode, instance thread status = OPEN Crash recovery required

(And Oracle automatically performs crash recovery(instance Recovery)

Media recovery requires restore and recovery

Instance recovery requires archivelog files and redolog files

v$datafile_header uses physical datafile file on disk as a resource

v$datafile uses controlfile as a resource

When incomplete recovery is need to done ?

When online redolog file and incremental backup required for recovery are lossed then we need to go

for incomplete recovery

When you validate backup sets, RMAN actually reads the backup files as if it were doing a restore

operation ( restore madbekadare rman hege backup file na hege read madtadeyo ade tara validate

madbekadarenu read madtade)

This will indicate how much time it takes to read the files during a real restore operation (this could be

useful for troubleshooting I/O problems

Rman can perform(instead of "do" use) media recovery for physically corrupted block but logically

corrupted blocks cannot be recovered by rman

To recover logically corrupt blocks, restore datafile from backup and perform media recovery

When block ( or )blocks do not match the physical format that oracle expects then it is called Physicall

corruption.

Physical corruptions are generally the result of infrastructure problems

Possible sources of physical corruption are storage array cache corruption , any filesystem bugs ,

application errors , array controller failure

LOGICAL CORRUPTION

When block contents are inconsistent with the logical information that oracle expects to then its called

Logical corruption

For example : one of the block header structures, which tracks the number of locks associated with rows in

the block, differs from the actual number of locks present

Another example would be if the header information on available space differs from the true available space

on the block.

RMAN> recover database until time 'sysdate - 1/48' test;

RMAN> recover database until scn 2328888 test;

RMAN> recover database until sequence 343 test;

Caution If you attempt to issue a recover tablespace until … test, RMAN will attempt to perform a

tablespace point-in- time recovery (TSPITR).

Performing Database-Level Recovery

$ rman target /

RMAN> startup mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open;

When recovery catalog is configured

$ rman target / catalog rcat/rcat@rcat

$ rman target /

RMAN> startup mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open;

Performing tablespace recovery

$ rman target / catalog rcat/rcat@rcat

$ rman target /

RMAN> alter tablespace users offline immediate;

RMAN> restore tablespace users;

RMAN> recover tablespace users;

RMAN> alter tablespace users online;

For 11g and Lower

$ rman target /

RMAN> sql 'alter tablespace users offline immediate';

RMAN> restore tablespace users;

RMAN> recover tablespace users;

RMAN> sql 'alter tablespace users online';

Performing Data File-Level Recovery

$ rman target /

RMAN> startup mount;

RMAN> alter database datafile '/u01/dbfile/o12c/users01.dbf' offline;

RMAN> alter database open;

RMAN> restore tablespace users;

RMAN> recover tablespace users;

RMAN> alter tablespace users online;

Use the RMAN report schema command to list data file names and file numbers

.

.

.

.

.

. Forcing RMAN to Restore a File Problem

As part of a test exercise, you attempt to restore a data file twice and receive this RMAN message:

restore not done; all files read only, offline, or already restored

In this situation, you want to force RMAN to restore the data file again

Use the force command to restore data files and archived redo log files even if they already exist in a

location.

Restoring from an Older Backup

You want to specifically instruct RMAN to restore from a backup set that is older than the last backup that

was taken

Sol: You can restore an older backup a couple different ways: using a tag name or using the restore …

until command

list backup summary

List of Backups

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

Key TY LV S Device Type #Pieces #Copies Compressed Tag

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

79 B F A DISK 1 1 NO TAG20120724T210842

80 B F A DISK 1 1 NO TAG20120724T210918

81 B F A DISK 1 1 NO TAG20120729T122948

$ rman target /

RMAN> startup mount;

RMAN> restore database from tag TAG20120724T210842;

RMAN> recover database;

RMAN> alter database open;

Using restore … until

You can also tell RMAN to restore data files from a point in the past using the until clause of the

restore command in

one of the following ways:

n Until SCN

n Until log sequence

n Until restore point

n Until time

RMAN> startup mount;

RMAN> restore database until scn 1254174;

RMAN> recover database;

RMAN> alter database open;

Recovering Through Resetlogs

Problem

You recently performed an incomplete recovery that required you to open your database with the open

resetlogs

command. Before you could back up your database, you experienced another media failure. Prior to

Oracle Database 10g,

it was extremely difficult to recover using a backup of a previous incarnation of your database. You now

wonder whether

you can get your database back in one piece.

Solution

Beginning with Oracle Database 10g, you can restore a backup from a previous incarnation and recover

through a

previously issued open resetlogs command. You simply need to restore and recover your database as

required by the

type of failure. In this example, the control files and all data files are restored:

$ rman target /

RMAN> startup nomount;

RMAN> restore controlfile from autobackup;

RMAN> alter database mount;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open resetlogs;

When we should open our database with open resetlog command?

Perform an incomplete recovery

Recover with a backup control file

Use a re-created control file and you're missing your current online redo logs

Difference between 10 and 11g

Prior to Oracle Database 10g, you were required to take a backup of your database immediately after you

reset the online

redo log files. This is because resetting the online redo log files creates a new incarnation of your

database and resets

your log sequence number back to 1. Prior to Oracle Database 10g, any backups taken before resetting

the logs could not

be easily used to restore and recover your database.

Starting with Oracle Database 10g, Oracle allows you to restore from a backup from a previous

incarnation of your

database and issue restore and recovery commands as applicable to the type of failure that has occurred.

Oracle keeps

track of log files from all incarnations of your database. The V$LOG_HISTORY view is no longer cleared

out during a

resetlogs operation and contains information for the current incarnation as well as any previous

incarnations.

If you're using an FRA (fast recovery area), then Oracle creates the archive redo logs using the Oracle

Managed File name

format. If you aren't using an FRA, the format mask of the archived redo log files must include the thread

(%t), sequence (%

s), and resetlogs ID (%r). For example

RMAN> list incarnation

Restoring the spfile

If you receive an error such as this when running the restore command:

RMAN-20001: target database not found in recovery catalog

RMAN set dbid 3414586809;

Not Using a Recovery Catalog, RMAN Auto Backup in Default Location

For this scenario you need to know your database identifier before you can proceed. See Recipe 10-3 for

details about

determining your DBID.

This recipe assumes that you have configured your auto backups of the spfile to go to the default

location. The default

location depends on your operating system. For Linux/Unix, the default location is ORACLE_HOME/dbs.

On Windows

systems, it's usually ORACLE_HOME\database.

$ rman target /

RMAN> startup force nomount; # start instance for retrieval of spfile

RMAN> set dbid 3414586809;

RMAN> restore spfile from autobackup;

RMAN> startup force; # startup using restored spfile

Not Using a Recovery Catalog, RMAN Auto Backup Not in Default Location

$ rman target /

RMAN> set dbid 3414586809;

RMAN> startup force nomount; # start instance for retrieval of spfile

RMAN> restore spfile from

'/u01/fra/012C/autobackup/2012_07_30/o1_mf_s_789989279_81fb00rl_.bkp';

RMAN> startup force; # startup using restored spfile

Restoring Archived Redo Log Files

Restoring controlfile

If FRA is configured and autobackup of the controlfile is configured then just use

“restore from auto backup” because Rman know about /<FRA>/<target database

SID>/autobackup/YYYY_MM_DD/<backup piece file>

If FRA is configured but autobackup of the controlfile is configured in rman then rman

uses /<FRA>/<target database SID>/backupset/YYYY_MM_DD/<backup piece file>

If recovery catalog is configured then just use “ restore controlfile “ because

Recovery catalog will be knowing all information of the backup.

If both recovery catalog and fra is not configured then first we need to set dbid

Catlog command : you can add metadata information about the backup pieces to directly

your controlfile

Catalog recovery area

Catalog

To RESTORE SPFILE WE NEED TO USE STARTUP FORCE

ROOT.sh

It creates the additional directories and sets appropriate ownership and permissions on files for root user.

Catalog - creates data dictionary views.

Catproc - create in built PL/SQL Procedures, Packages etc

If any (a long-running )transaction can't find the undo data it needs, then it generates the well-known

Oracle snapshot-too- old error. Here's an example:

Recovering Data Files Not Backed Up

Assume that you have added a datafile and had failure before taking backup of the newly added datafile

so how do you recover it

You wonder how can we restore and recover a datafile that doesn’t exists ?

When media failure occurs then there may be 2 situations

1) .Using current controlfile (means you have loss only datafile and not controlfile , controlfile is

have entry of the newly available datafile and control file is available)

2) Using backup controlfile ( means both newly added datafile and controlfile is lossed so old

controlfile doesn’t contain information of newly added datafile)

For first situation , you can restore and recover at datafile level ,tablespace level and database level

But for 2 nd situation , you can restore and recover at database level

Using a Current Control File

In this example, we use the current control file and are recovering the tools01.dbf datafile in the newly

added tools

tablespace.

$ rman target /

RMAN> startup mount;

RMAN> restore tablespace tools;

You should see a message like the following in the output as RMAN re-creates the data file:

creating datafile file number=5 name=/u01/dbfile/o12c/tools01.dbf

Now issue the recover command and open the database:

RMAN> recover tablespace tools;

RMAN> alter database open;

Using a Backup Control File

This scenario is applicable anytime you use a backup control file to restore and recover a data file that

has not yet been

backed up. First, we restore a control file from a backup taken prior to when the data file was created:

$ rman target /

RMAN> startup nomount;

RMAN> restore controlfile from

'/u01/app/oracle/product/12.1.0.1/db_1/dbs/c-3412777350- 20120730-05';

RMAN> alter database mount;

Now you can verify the control file has no record of the tablespace that was added after the backup was

taken:

RMAN> report schema;

When the control file has no record of the data file, RMAN will throw an error if you attempt to recover

at the tablespace or

data file level. In this situation, you must use the restore database and recover database commands as

follows:

RMAN> restore database;

RMAN> recover database;

Next, you should see quite a bit of RMAN output. Near the end of the output you should see a line

similar to this indicating

that the data file has been re-created:

creating datafile file number=5 name=/u01/dbfile/o12c/tools01.dbf

Since you restored using a backup control file, you are required to open the database with the resetlogs

command:

RMAN> alter database open resetlogs;

How It Works

Starting with Oracle Database 10g, there is enough information in the redo stream for RMAN to

automatically re-create a

data file that was never backed up. It doesn't matter whether the control file has a record of the data

file.

Prior to Oracle Database10g, manual intervention from the DBA was required to recover a data file that

had not been

backed up yet. If Oracle identified that a data file was missing that had not been backed up, the recovery

process would

halt, and you would have to identify the missing data file and re-create it:

SQL> alter database create datafile '/u01/dbfile/o12c/tools01.dbf '

as '/u01/dbfile/o12c/tools01.dbf' size 10485760 reuse;

After re-creating the missing data file, you had to manually restart the recovery session. If you are using

an old version of

the Oracle database, see MOS note 1060605.6 for details on how to re-create a data file in this scenario.

In Oracle Database 10g and newer, this is no longer the case. RMAN automatically detects that there

isn't a backup of a

data file being restored and re-creates the data file from information retrieved from the control file

and/or redo information

as part of the restore and recovery operations

ARCHIVE log

You may have disk space issues and need to spread the restored archived redo logs across multiple

locations. You can do

so as follows:

RMAN> run{

set archivelog destination to '/u01/archrest';

restore archivelog from sequence 1 until sequence 10;

set archivelog destination to '/u02/archrest';

restore archivelog from sequence 11;

}

http://scn.sap.com/community/oracle/blog/2014/11/03/how-to- interpret-awr- reports

4 Instance Efficiency Percentage – these needs to be looked very carefully, since these are not really a good

measurement of the database performance.

For example in very processing-intensive SQL statements which are executed repeatedly, only read blocks from the buffer pool

increases the hit rate of the buffer pool. After optimizing such statements the hit ratio decreased though performance improves.

Buffer Nowait – shows how often buffer cache were accessed with no wait time.

Buffer Hit – shows how often a requested block has been found in the buffer cache without requiring disk access.

Redo NoWait – shows if log_buffer size is set correctly. Preemptive redolog switches in Oracle 11.2

Parse CPU to Parse Elapsd – shows how much time was spent on parsing while waiting for resources.

Non-Parse CPU – in the following example the the figure is close to 100% meaning that the overall CPU usage is only 0.15 % for

statement parsing.

http://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta027.htm#CHDCCBDF

AWR : (Automatic workload repository)

Is extremely useful to diagnose database performance issues.

ADDM (Automatic Database Diagonostic)

Is extremely used for tuning sql statements

ASH : (Active session history)

This report provides Recent active session activities.

Difference Between OBSOLETE AND EXPIRED Backup

RMAN considers backups of datafiles and control files as obsolete, that is, no longer needed for recovery, according to criteria that we specify in the CONFIGURE command. We can then use the REPORT OBSOLETE  command to view obsolete files and DELETE OBSOLETE to delete them .

For ex  :  we set our retention policy to redundancy 2. this means we always want to keep at least 2 backup, after 2 backup, if we take an another backup oldest one become obsolete because there is 3 backup and we want to keep 2. if our flash recovery area is full then obsolete backups can be overwrite.

A status of "expired" means that the backup piece or backup set is not found in the backup destination or missing .Since backup info is hold in our controlfile and catalog . Our controlfile thinks that there is a backup under a directory with a name but someone delete this file from operating system. We can run crosscheck command to check if these files are exist and if rman found a file is missing then mark that backup record as expired which means is no more exists.






One Can Succeed at Almost Anything For Which He Has Enthusiasm...
RMAN crosscheck command compares the RMAN  catalog entries or controlfiles with the actual OS files and reports to locate "expired" or "obsolete" RMAN catalog entries.


CATALOG.SQL - Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.
CATPROC.SQL - catproc.sql creates all structures required for PL/SQL.






One Can Succeed at Almost Anything For Which He Has Enthusiasm...
RMAN crosscheck command compares the RMAN  catalog entries or controlfiles with the actual OS files and reports to locate "expired" or "obsolete" RMAN catalog entries.


CATALOG.SQL - Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.
CATPROC.SQL - catproc.sql creates all structures required for PL/SQL.





Backup details


V$RMAN_BACKUP_JOB_DETAILS , V$BACKUP_SET_DETAILS , V$BACKUP_SET





what is cache fusion ?

Cache Fusion is a new technology that uses a high speed interprocess communication (IPC) interconnect
that provides copies of blocks directly from the holding instance's memory cache to the requesting instance's memory cache

cache coherency or cache consistency

The synchronization of data in multiple caches so that reading a memory location through any cache will return the most recent data .Sometimes also called cache consistency.


Cache Fusion addresses these types of concurrency between instances,
•Concurrent Reads on Multiple Nodes
•Concurrent Reads and Writes on Different Nodes
•Concurrent Writes on Different Nodes











high water mark

The high water mark is the boundary between used and unused space in a segment.

password file

A file created by the ORAPWD command. A database must use password files if you want to connect as SYSDBA over a network.

past image (PI)

A past image is a copy of dirty block that is used by the Global Cache Service (GCS). Past images of blocks are maintained until writes covering those versions are recorded. Past images are used in failure recovery.

PCTFREE

A storage parameter file that defines the percentage of space to leave in an extent.






The database stores undo data in undo extents, and there are three distinct types of undo extents:
Active: Transactions are currently using these extents.
Unexpired: These are extents that contain undo that's required to satisfy the undo retention time specified by the UNDO_RETENTION initialization parameter.
Expired: These are extents with undo that's been retained longer than the duration specified by the UNDO_RETENTION parameter.












Concurrent Reads on Multiple Nodes :
Concurrent reads occur when two instances need to read the same data block. Real Application Clusters easily resolves this situation because multiple instances
can share the same blocks for read access without cache coherency conflicts.

Concurrent Reads and Writes on Different Nodes
A read of a data block that was recently modified can be either for the current version of the block or for a read-consistent previous version.
In both cases, the block will be transferred from one cache to the other.


Enthusiasm : Zeal , passion , interest ,intense , eager.
One Can Succeed at Almost Anything For Which He Has Enthusiasm .
dba_advisor_findings af, dba_advisor_objects ao, dba_advisor_log al
select * from PSAPMSGPUBHDR;
Rakesh, setup conf call with myself only  as these are end user questions which with my help you should be able to answer.
setup a call only with me...(there is some difference using "with me" and "with myself")

set define off if prompt should not trigger for input values

if you are installing sofware then definately you need ram , disk space to store softwares , temp and swap required for every software , it's not standalone its cluster
database so network required, now-a-days Oracle using it's own filesystem ASM which  requires shared storage space.
Oracle critical files are there O

Cloning Database .
=================

1)First Check do we have enough space or not in target space.
2)Please ensure that no duplicate datafiles are present in database.
If there are duplicate datafiles in the database,make sure to map them to different mountpoints in the restore script .
If restore fails due to duplicate file issues(datafiles switch not happends),then only restore the failed datafiles(get the information from log),
then we need to switching for all datafiles .
3) Make sure that the once restore and recovery of the database is completed, please rename the redo logfiles as per cloned server
mountpoint details and permission of the redo logfiles should be oracle:dba


Now actual steps

1) Backup the database with controlfile to any mountpoint.
2) copy to source database Pfile to Target database and change the Database name.
3) Create a restore script .
How restore script look like ?
it contains..
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
SET NEWNAME FOR DATAFILE 1 to '/glerpq02/data/pnecqa2/data01/pnecqa2/abc.dbf';
SET NEWNAME FOR DATAFILE 2 to '/glerpq02/data/pnecqa2/data01/pnecqa2/bcd.dbf';
…………….
………..
restore database;
switch datafile all;
release channel c1;
release channel c2;
}

Drop the database if it is already existing. First shutdown the database,listener and then use rm -rf command from OS level
to remove the datafiles,tempfiles,controlfile,logfiles present in the mount points of existing database.

Use the following commands to check and remove the datafiles,tempfiles,controlfile,logfiles present in the mount points of existing database.:---
SQL> select name from v$datafile;   (To view datafiles)
SQL> select name from v$controlfile; (To view controlfiles)
SQL> select member from v$logfile;  (To view logfiles)
SQL> select name from v$tempfile; (To view tempfiles)

Create a restore shell script that would call this restore script.
rman target=/ cmdfile=restore.rcv log=restore.log


CLoning starts now.
1) Edit pfile
2) startup nomount
go to rman prompt
3) rman  target /
Restore the controlfile from the backup piece.
RMAN> restore controlfile from 'fullbackup_ctl<along with full path>';
8) Mount the database
9) Now start the restore script.
Now restore the datafiles to new locations and recover
$ nohup restore.sh &
RMAN> run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
recover database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

12) Once database is recovered .

 please rename the redo log files for the database as per existing mountpoints.
SQL>select member from v$logfile ;
SQL> alter database rename file ‘source path’  to ‘destination path’;

13) open the database using resetlogs
14) Shutdown the database
15) Mount the database

SQL> startup mount;

16) Invoke nid utility and allow to complete

nid target=/ dbname=clone


Note:--- If we are performing manual recovery we by using backup controlfile,
First we should have to set the clone database archive destination to the mount point
in which archives logs resides and also change the archive log format of clone server to the archive log format of target server.


RECOVER BY USING BACKUP CONTROLFILE COMMAND:-----
SQl> recover database using backup controlfile;
Specify auto if it prompts for auto | manual | cancel


RECOVER CANCEL BY USING BACKUP CONTROLFILE COMMAND:-----
SQl> recover database using backup controlfile until cancel;
Specify cancel if it prompts for auto | manual | cancel


















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




To start or stop your entire cluster database,
================================================

srvctl start database -d name [-o start_options] [-c connect_str | -q]
srvctl stop database -d name [-o stop_options] [-c connect_str | -q]
EXAMPLE :
srvctl start database -d orcl -o mount






To start or stop instances.
================================================

srvctl start instance -d db_name -i "inst_name_list" [-o start_options] [-c connect_str | -q]
srvctl stop instance -d name -i "inst_name_list" [-o stop_options] [-c connect_str | -q]

Example .
srvctl stop instance -d orcl -i "orcl3,orcl4" -o immediate -c "sysback/oracle as sysoper"









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



srvctl start

srvctl start database

Starts the cluster database and its instances



srvctl start instance

Starts the instance





srvctl start service

Starts the service




srvctl start nodeapps

Starts the node applications


srvctl start asm

Starts ASM instances



srvctl start listener

Starts the specified Listener or Listeners.

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

srvctl start
srvctl start database

Starts the cluster database and its instances
srvctl start database -d db_unique_name [-o start_options] [-c connect_str | -q]
where -c for

Connect string (default: / as sysdba)

-q :

Prompt for user credentials connect string from standard input.

ex :
srvctl start database -d crm -o open

========================================================================================================
srvctl start instance

Starts the instance

srvctl start instance -d db_unique_name -i inst_name_list [-o start_options] [-c connect_str | -q]


srvctl start instance -d crm -i "crm1,crm4"


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

srvctl start asm
Starts an ASM instance.



srvctl start asm -n node_name [-i asm_inst_name] [-o start_options] [-c connect_str | -q]



-n node_name

Node name



-i inst_name

ASM instance name.


-h

Display help.

EX:
srvctl start asm -n crmnode1 -i asm1

An example to start all ASM instances on a node is:
srvctl start asm -n crmnode2





========================
srvctl start listener


srvctl start listener -n node_name [-l listener_name_list]
srvctl start listener -n mynode1


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

Time for action – starting, stopping, and monitoring MRP


Before starting Redo Apply services, the physical standby database must be in the MOUNT status.
 From 11g onwards, the standby database can also be in the OPEN mode. If the redo transport service is in the ARCH mode, the redo will be applied from the archived redo logfiles after being transferred to the standby database. If the redo transport service is in LGWR, the Log network server (LNS) will be reading the redo buffer in SGA and will send redo to Oracle Net Services for transmission to the standby redo logfiles of the standby database using the RFS process. On the standby database, redo will be applied from the standby redo logs.


To execute the following commands, the control file must be a standby control file.
If you execute these commands in a database in the primary mode, Oracle will return an error and ignore the command.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;



Start Redo Apply in the foreground.
Connect to the SQLPlus command prompt and issue the following command. If the media recovery is already running, you will run into the error ORA-01153: an incompatible media recovery is active.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
Database altered.
Whenever you issue the preceding command, you can monitor the Redo Apply status from the alert logfile. Managed standby recovery is now active and is not using real-time apply. The SQL session will be active unless you terminate the session by pressing Ctrl + C or kill the session from another active session. Press Ctrl + C to stop Redo Apply.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
*
*
ERROR at line 1:
ORA-16043: Redo apply has been canceled.
ORA-01013: user requested cancel of current operation





Start Redo Apply in the background.
In order to start the Redo Apply service in the background, use the disconnect from session option. This command will return you to the SQL command line once the Redo Apply service is started. Run the following statement on the standby database:

SQL> alter database recover managed standby database disconnect from session;
Database altered.
Check the Redo Apply service status.
From SQL*Plus, you can check whether the Media Recover Process (MRP) is running using the V$MANAGED_STANDBY view:

SQL> SELECT THREAD#,SEQUENCE#,PROCESS,CLIENT_PROCESS,STATUS,BLOCKS FROM V$MANAGED_STANDBY;

   THREAD#  SEQUENCE# PROCESS   CLIENT_P STATUS           BLOCKS
---------- ---------- --------- -------- ------------ ----------
         1        146 ARCH      ARCH     CLOSING            1868
         1        148 ARCH      ARCH     CLOSING               6
         0          0 ARCH      ARCH     CONNECTED             0
         1        147 ARCH      ARCH     CLOSING               8
         1        149 RFS       LGWR     IDLE                  1
         0          0 RFS       UNKNOWN  IDLE                  0
         0          0 RFS       UNKNOWN  IDLE                  0
         0          0 RFS       N/A      IDLE                  0
         1        149 MRP0      N/A      APPLYING_LOG     204800

9 rows selected.
From the PROCESS column, you can see that the background process name is MRP0; Media Recovery Process is ACTIVE and the status is APPLYING_LOG, which means that the process is actively applying the archived redo log to the standby database. From the OS, you can monitor the specific background process as follows:

[oracle@oracle-stby ~]$ ps -ef|grep mrp
oracle    5507     1  0 19:26 ?        00:00:02 ora_mrp0_INDIA
From the output, you can simply estimate how many standby instances are running with background recovery. Only one Media Recovery Process can be running per instance.

Also, you can query from v$session.

SQL> select program from v$session where program like '%MRP%';
PROGRAM
-------------------------
oracle@oracle-stby (MRP0)
Stop Redo Apply.
To stop the MRP, issue the following command:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Da
tabase altered.



From the alert logfile, you will see the following lines:

Sun Aug 05 21:24:16 2012
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Sun Aug 05 21:24:16 2012
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u02/app/oracle/diag/rdbms/india_un/INDIA/trace/INDIA_mrp0_5507.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
After stopping the MRP, no background process is active and this can be confirmed by using the V$MANAGED_STANDBY or V$SESSION view shown as follows:

SQL> SELECT THREAD#,SEQUENCE#,PROCESS,CLIENT_PROCESS,STATUS,BLOCKS FROM V$MANAGED_STANDBY;

   THREAD#  SEQUENCE# PROCESS   CLIENT_P STATUS           BLOCKS
---------- ---------- --------- -------- ------------ ----------
         1        146 ARCH      ARCH     CLOSING            1868
         1        148 ARCH      ARCH     CLOSING               6
         0          0 ARCH      ARCH     CONNECTED             0
         1        147 ARCH      ARCH     CLOSING               8
         1        149 RFS       LGWR     WRITING               1
         0          0 RFS       UNKNOWN  IDLE                  0
         0          0 RFS       UNKNOWN  IDLE                  0
         0          0 RFS       N/A      IDLE                  0

8 rows selected.

SQL>  select program from v$session where program like '%MRP%';
no rows selected
Start real-time apply.
To start Redo Apply in real-time apply mode, you must use the USING CURRENT LOGFILE option as follows:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
From the standby alert logfile, you will see the following lines:

Sun Aug 05 15:31:21 2012
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (INDIA)
Sun Aug 05 15:31:21 2012

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








Time for action – closing a gap with an RMAN incremental backup
Let's see all the required steps to practice this recovery operation:

In this practice, assume that there are missing archived logs (gap) in the standby database, and we're not able to restore these archived logs. We'll synchronize Data Guard using the RMAN incremental backup. To represent this situation, execute the DEFER command to defer the log destination in the primary database, and execute the following operation that will generate redo in the primary database:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'DEFER';
Now we have a standby database behind the primary database, and we'll use RMAN to reflect the primary database's changes to the standby database. Stop Redo Apply in the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Query the current system change number (SCN) of the standby database that will be used as the limit for an incremental backup of the primary database. Run the following statement on the standby database:
SQL> SELECT MIN(FHSCN) FROM X$KCVFH;

MIN(FHSCN)
----------------
20606344
Run an RMAN incremental backup of the primary database by using the obtained SCN value.
Tip
This backup job will check all the blocks of the primary database and back up the blocks that have a higher SCN. So even if the backup size is small, it may take a long time.
RMAN> BACKUP INCREMENTAL FROM SCN 20606344 DATABASE FORMAT '/tmp/Standby_Inc_%U' tag 'STANDBY_INC';

Starting backup at 20-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=165 device type=DISK
backup will be obsolete on date 27-DEC-12
archived logs will not be kept or backed up
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/oracle2/datafile/ORCL/system01.dbf
...
input datafile file number=00007 name=/u01/app/oracle2/datafile/ORCL/system03.dbf
channel ORA_DISK_1: starting piece 1 at 20-DEC-12
channel ORA_DISK_1: finished piece 1 at 20-DEC-12
piece handle=/tmp/Standby_Inc_03nt9u0v_1_1 tag=STANDBY_INC comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
using channel ORA_DISK_1
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 20-DEC-12
channel ORA_DISK_1: finished piece 1 at 20-DEC-12
piece handle=/tmp/Standby_Inc_04nt9u3a_1_1 tag=STANDBY_INC comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-DEC-12
Copy the backup files from the primary site to the standby site with FTP or SCP.
scp /tmp/Standby_Inc_* standbyhost:/tmp/
Register the backup files to the standby database control file with the RMAN CATALOG command, so that we'll be able to recover the standby database using these backup files:
RMAN> CATALOG START WITH '/tmp/Standby_Inc';

using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/Standby_Inc

List of Files Unknown to the Database
=====================================
File Name: /tmp/Standby_Inc_03nt9u0v_1_1
File Name: /tmp/Standby_Inc_04nt9u3a_1_1

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /tmp/Standby_Inc_03nt9u0v_1_1
File Name: /tmp/Standby_Inc_04nt9u3a_1_1
Recover the standby database with the RMAN RECOVER statement. The Recovery operation will use the incremental backup by default as we have already registered the backup files:
RMAN> RECOVER DATABASE NOREDO;

Starting recover at 20-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1237 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle2/datafile/INDIAPS/system01.dbf
...
destination for restore of datafile 00007: /u01/app/oracle2/datafile/INDIAPS/system03.dbf
channel ORA_DISK_1: reading from backup piece /tmp/Standby_Inc_03nt9u0v_1_1
channel ORA_DISK_1: piece handle=/tmp/Standby_Inc_03nt9u0v_1_1 tag=STANDBY_INC
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 20-DEC-12
In this step, we'll create a new standby control file in the primary database and open the standby database using this new control file. We've performed this process at the beginning of this chapter, so we won't be explaining it again; only the statements are given as follows:
In the primary database you will see the following command lines:

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/Standby_CTRL.bck';
scp /tmp/Standby_CTRL.bck standbyhost:/tmp/
In the standby database you will see the following command lines:

RMAN> SHUTDOWN;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/Standby_CTRL.bck';
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
If OMF is being used, execute the following commands:

RMAN> CATALOG START WITH '+DATA/mystd/datafile/';
RMAN> SWITCH DATABASE TO COPY;
If new datafiles were added during the time when Data Guard had been stopped, we will need to copy and register the newly created files to the standby system, as they were not included in the incremental backup set.
We will determine if any files have been added to the primary database, as the standby current SCN will run the following query:

SQL>SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 20606344;
If the flashback database is ON in the standby database, turn it off and on again:
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
Clear all the standby redo log groups in the standby database:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 5;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 6;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 7;
Start Redo Apply in the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;


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








Time for action – resolving UNNAMED datafile errors

How to you check the datafiles that needs to be recovered ?
out put is from STandby database ?
SELECT * FROM V$RECOVER_FILE WHERE ERROR LIKE '%MISSING%';
SQL> SELECT * FROM V$RECOVER_FILE WHERE ERROR LIKE '%MISSING%';
     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME
---------- ------- ------- ----------------- ---------- ----------
       10  ONLINE  ONLINE  FILE MISSING                  0




2.Identify datafile 10 in the primary database

SQL> SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=10;
     FILE# NAME
---------- -----------------------------------------------
       536 /u01/app/oracle2/datafile/ORCL/users03.dbf


3.Identify the dummy filename created in the standby database:
SQL> SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=10;
     FILE# NAME
---------- -------------------------------------------------------
       536 /u01/app/oracle2/product/11.2.0/dbhome_1/dbs/UNNAMED00010


4.If the reason for the creation of the UNNAMED file is disk capacity or a nonexistent path, fix the issue by creating the datafile in its original place.

5.Set STANDBY_FILE_MANAGEMENT to MANUAL:
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.

6.Create the datafile in its original place with the ALTER DATABASE CREATE DATAFILE statement:
SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle2/product/11.2.0/dbhome_1/dbs/UNNAMED00010' AS '/u01/app/oracle2/datafile/ORCL/users03.dbf';
Database altered.

7.Set STANDBY_FILE_MANAGEMENT to AUTO and start Redo Apply:
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;
System altered.
SQL> SHOW PARAMETER STANDBY_FILE_MANAGEMENT
NAME                                 TYPE        VALUE
----------------------------------- ----------- ------------------
standby_file_management              string      AUTO
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
8.Check the standby database's processes, or the alert log file, to monitor Redo Apply:
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;








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

Fixing NOLOGGING changes on the standby database
It's possible to limit redo generation for specific operations on Oracle databases, which provide higher performance. These operations include bulk inserts, creation of tables as select operations, and index creations. When we work using the NOLOGGING clause, redo will not include all the changes to data on the related segments. This means if we perform a restore/recovery of the related datafile, or of the whole database after the NOLOGGING operations, it'll not be possible to recover the data created with the NOLOGGING option.
The same problem exists with Data Guard. When the NOLOGGING operation is executed in the primary database, Data Guard is not able to reflect all the data changes in the standby database. In this case, when we activate a standby database or open it in the read-only mode, we'll see the following error messages:
ORA-01578: ORACLE data block corrupted (file # 1, block # 2521)
ORA-01110: data file 1: '/u01/app/oracle2/datafile/INDIAPS/system01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

For this reason, Data Guard installation requires putting the primary database in the FORCE LOGGING mode before starting redo transport between the primary and standby database. The FORCE LOGGING mode guarantees the writing of redo records even if the NOLOGGING clause was specified in the SQL statements. The default mode of an Oracle database is not FORCE LOGGING, so we need to put the database in this mode using the following statement:
SQL> ALTER DATABASE FORCE LOGGING;

In this section, we'll assume that the primary database is not in the FORCE LOGGING mode, and some NOLOGGING changes were made in the primary database. One method to fix this situation in the standby database is restoring the affected datafiles from backups taken from the primary database after the NOLOGGING operation. However, in this method we have to work with backup files that are most likely much bigger in size than the amount of data that needs to be recovered. A method that uses the RMAN BACKUP INCREMENTAL FROM SCN statement is more efficient because the backup files will include only the changes from the beginning of the NOLOGGING operation.
We'll now see two scenarios. We'll use the BACKUP INCREMENTAL FROM SCN statement for an incremental datafile backup in the first scenario, and use the same statement for an incremental database backup in the second one. For a small number of affected datafiles and relatively less affected data, choose the first scenario. However, if the number of affected datafiles and amount of data are high, use the second scenario that takes an incremental backup of the whole database.




Time for action – fixing NOLOGGING changes on a standby database with incremental datafile backups
As a prerequisite for this exercise, first put the primary database in the no-force logging mode using the ALTER DATABASE NO FORCE LOGGING statement. Then perform some DML operations in the primary database using the NOLOGGING clause so that we can fix the issue in the standby database with the following steps:
1.Run the following query to identify the datafiles that are affected by NOLOGGING changes:
SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;
FILE#      FIRST_NONLOGGED_SCN
---------- -------------------
         4            20606544

2.First we need to put the affected datafiles in the OFFLINE state in the standby database. For this purpose, stop Redo Apply in the standby database, execute the ALTER DATABASE DATAFILE ... OFFLINE statement, and start Redo Apply again:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE DATAFILE 4 OFFLINE FOR DROP;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

3.Now we'll take incremental backups of the related datafiles by using the FROM SCN keyword. SCN values will be the output of the execution of the queries in the first step. Connect to the primary database as an RMAN target and execute the following RMAN BACKUP statements:
RMAN> BACKUP INCREMENTAL FROM SCN 20606544 DATAFILE 4 FORMAT '/data/Dbf_inc_%U' TAG 'FOR STANDBY';

4.Copy the backup files from the primary site to the standby site with FTP or SCP:
scp /data/Dbf_inc_* standbyhost:/data/

5.Connect to the physical standby database as the RMAN target and catalog the copied backup files to the control file with the RMAN CATALOG command:
RMAN> CATALOG START WITH '/data/Dbf_inc_';

6.In order to put the affected datafiles in the ONLINE state, stop Redo Apply on the standby database, and run the ALTER DATABASE DATAFILE ... ONLINE statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE DATAFILE 4 ONLINE;

7.Recover the datafiles by connecting the standby database as the RMAN target. RMAN will use the incremental backup automatically because those files were registered to the control file previously:
RMAN> RECOVER DATAFILE 4 NOREDO;

8.Now run the query from the first step again to ensure that there're no more datafiles with the NOLOGGING changes:
SQL> SELECT FILE#, FIRST_NONLOGGED_SCN FROM V$DATAFILE WHERE FIRST_NONLOGGED_SCN > 0;

9.Start Redo Apply on the standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;




















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










Cloning Database .
=================

1)First Check do we have enough space or not in target space.
2)Please ensure that no duplicate datafiles are present in database.
If there are duplicate datafiles in the database,make sure to map them to different mountpoints in the restore script .
If restore fails due to duplicate file issues(datafiles switch not happends),then only restore the failed datafiles(get the information from log),
then we need to switching for all datafiles .
3) Make sure that the once restore and recovery of the database is completed, please rename the redo logfiles as per cloned server
mountpoint details and permission of the redo logfiles should be oracle:dba


Now actual steps

1) Backup the database with controlfile to any mountpoint.
2) copy to source database Pfile to Target database and change the Database name.
3) Create a restore script .
How restore script look like ?
it contains..
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
SET NEWNAME FOR DATAFILE 1 to '/glerpq02/data/pnecqa2/data01/pnecqa2/abc.dbf';
SET NEWNAME FOR DATAFILE 2 to '/glerpq02/data/pnecqa2/data01/pnecqa2/bcd.dbf';
…………….
………..
restore database;
switch datafile all;
release channel c1;
release channel c2;
}

Drop the database if it is already existing. First shutdown the database,listener and then use rm -rf command from OS level
to remove the datafiles,tempfiles,controlfile,logfiles present in the mount points of existing database.

Use the following commands to check and remove the datafiles,tempfiles,controlfile,logfiles present in the mount points of existing database.:---
SQL> select name from v$datafile;   (To view datafiles)
SQL> select name from v$controlfile; (To view controlfiles)
SQL> select member from v$logfile;  (To view logfiles)
SQL> select name from v$tempfile; (To view tempfiles)

Create a restore shell script that would call this restore script.
rman target=/ cmdfile=restore.rcv log=restore.log


CLoning starts now.
1) Edit pfile
2) startup nomount
go to rman prompt
3) rman  target /
Restore the controlfile from the backup piece.
RMAN> restore controlfile from 'fullbackup_ctl<along with full path>';
8) Mount the database
9) Now start the restore script.
Now restore the datafiles to new locations and recover
$ nohup restore.sh &
RMAN> run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
recover database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

12) Once database is recovered .

 please rename the redo log files for the database as per existing mountpoints.
SQL>select member from v$logfile ;
SQL> alter database rename file ‘source path’  to ‘destination path’;

13) open the database using resetlogs
14) Shutdown the database
15) Mount the database

SQL> startup mount;

16) Invoke nid utility and allow to complete

nid target=/ dbname=clone


Note:--- If we are performing manual recovery we by using backup controlfile,
First we should have to set the clone database archive destination to the mount point
in which archives logs resides and also change the archive log format of clone server to the archive log format of target server.


RECOVER BY USING BACKUP CONTROLFILE COMMAND:-----
SQl> recover database using backup controlfile;
Specify auto if it prompts for auto | manual | cancel


RECOVER CANCEL BY USING BACKUP CONTROLFILE COMMAND:-----
SQl> recover database using backup controlfile until cancel;
Specify cancel if it prompts for auto | manual | cancel