Create a profile with unlimited access and other unlimited privileges in Oracle 11g

Create a profile with unlimited access and other unlimited privileges in Oracle 11g

SQL> CREATE PROFILE "NEW" LIMIT
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
SESSIONS_PER_USER UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
PRIVATE_SGA UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL;

Profile created.

SQL> select * from dba_profiles WHERE PROFILE='NEW';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

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

NEW                           COMPOSITE_LIMIT                  KERNEL   UNLIMITED

NEW                           SESSIONS_PER_USER                KERNEL   UNLIMITED

NEW                           CPU_PER_SESSION                  KERNEL   UNLIMITED

NEW                           CPU_PER_CALL                     KERNEL   UNLIMITED

NEW                           LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED

NEW                           LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED

NEW                           IDLE_TIME                        KERNEL   UNLIMITED

NEW                           CONNECT_TIME                     KERNEL   UNLIMITED

NEW                           PRIVATE_SGA                      KERNEL   UNLIMITED

NEW                           FAILED_LOGIN_ATTEMPTS            PASSWORD UNLIMITED

NEW                           PASSWORD_LIFE_TIME               PASSWORD UNLIMITED

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT

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

NEW                           PASSWORD_REUSE_TIME              PASSWORD UNLIMITED

NEW                           PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

NEW                           PASSWORD_VERIFY_FUNCTION         PASSWORD NULL

NEW                           PASSWORD_LOCK_TIME               PASSWORD UNLIMITED

NEW                           PASSWORD_GRACE_TIME              PASSWORD UNLIMITED



Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Percona XtraDB Cluster configuration on CentOS 6.4

Percona XtraDB Cluster configuration on CentOS 6.4

Host 1: 192.168.72.22
Host 2: 192.168.72.23
Linux version : Centos 6.4
Percona version : 5.5

Prerequisites:-

All the nodes must have a CentOS 6.4 installation.
Firewall Must be disabled or atleast 3306 port must be open.
SELinux must be disabled.
Check if Mysql is already installed in the server also check if its running.
Remove mysql if its present by doing
$yum erase mysql
This has to be done in all nodes where mysql is present.Else this will conflict with the installation files of Percona.

Step 1:-(On both nodes)
Create Percona yum Repository

$ rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

Step 2:-(On both nodes)
Install XtraDB Cluster

$ yum install Percona-XtraDB-Cluster-server-55 Percona-XtraDB-Cluster-client-55 Percona-XtraDB-Cluster-galera-2

Step 3:-(On both nodes)
Percona yum Experimental repository

$ rpm -Uhv http://repo.percona.com/testing/centos/6/os/noarch/percona-testing-0.0-1.noarch.rpm


Step 4:-
Configuring the node1

Create a file in the following location

vim /etc/my.cnf  and enter the following lines

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.72.22,192.168.72.23

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This is a recommended tuning variable for performance
innodb_locks_unsafe_for_binlog=1

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=192.168.72.22

# SST method
wsrep_sst_method=xtrabackup

# Cluster name
wsrep_cluster_name=my_centos_cluster

# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
wsrep_replicate_myisam=1
~
~
--save & exit (:wq)

After saving the file run the following command
/etc/init.d/mysql start --wsrep-cluster-address="gcomm://"

Now to enter in mysql:-
mysql -u root
Now update mysql root password:-
UPDATE mysql.user SET password=PASSWORD("r00t123007") where user='root';

mysqladmin -u root password NEWPASSWORD
Step 5:-Check the cluster status in node1

$mysql -u root -predhat
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | c2883338-834d-11e2-0800-03c9c68e41ec |
...
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
...
| wsrep_cluster_size         | 1                                    |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
...
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+
40 rows in set (0.01 sec)


This output shows that the cluster has been successfully bootstrapped.

In order to perform successful State Snapshot Transfer using XtraBackup new user needs to be set up with proper privileges:

mysql@percona1> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cr3t123#';
mysql@percona1> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
mysql@percona1> FLUSH PRIVILEGES;

Note MySQL root account can also be used for setting up the SST with Percona XtraBackup, but it’s recommended to use a different (non-root) user for this.

Step 6:-
Configuring node2

Create a file in the following location

vim /etc/my.cnf  and enter the following lines

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.72.22,192.168.72.23

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This is a recommended tuning variable for performance
innodb_locks_unsafe_for_binlog=1

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #2 address
wsrep_node_address=192.168.72.23

# Cluster name
wsrep_cluster_name=my_centos_cluster

# SST method
wsrep_sst_method=xtrabackup

#Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"
wsrep_replicate_myisam=1


After saving the file run the following command
[root@percona2 ~]# /etc/init.d/mysql start


Step 7:-Checking the cluster status on node2
$mysql -u root -predhat
mysql> show status like 'wsrep%';
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | c2883338-834d-11e2-0800-03c9c68e41ec |
...
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
...
| wsrep_cluster_size         | 2                                    |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
...
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+
40 rows in set (0.01 sec)


Step 8:-
Testing the replication

mysql@percona2> CREATE DATABASE percona;
Query OK, 1 row affected (0.01 sec)

Creating the example table on the second node:
mysql@percona3> USE percona;
Database changed

mysql@percona3> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
Query OK, 0 rows affected (0.05 sec)

Inserting records on the first node:
mysql@percona1> INSERT INTO percona.example VALUES (1, 'percona1');
Query OK, 1 row affected (0.02 sec)

Retrieving all the rows from that table on the second node:
mysql@percona2> SELECT * FROM percona.example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
|       1 | percona1  |
+---------+-----------+
1 row in set (0.00 sec)






Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Install Cassandra (Standalone) on CentOS 6x

Install Cassandra (Standalone) on CentOS 6x ==================================================

Step: 1. Install JAVA :

# cd /tmp
# wget --no-check-certificate --no-cookies --header 'Cookie: oraclelicense=accept-securebackup-cookie' http://download.oracle.com/otn-pub/java/jdk/8u5-b13/jdk-8u5-linux-x64.rpm
# yum -y install jdk-8u5-linux-x64.rpm
# export JAVA_HOME=/usr/java/jdk1.8.0_05
# export PATH=$PATH:$JAVA_HOME
# echo $JAVA_HOME
# vi /etc/profile.d/java.sh

#!/bin/bash
JAVA_HOME=/usr/java/jdk1.8.0_05
PATH=$JAVA_HOME/bin:$PATH
export PATH JAVA_HOME
export CLASSPATH=.

-- Save & Quit (:wq)

chmod +x /etc/profile.d/java.sh
source /etc/profile.d/java.sh
Step: 2. Install the Java Native Access (JNA) :

# yum -y install jna
Step: 3. Add a symbolic link to the Oracle Java SE Runtime Environment 7 installation :

alternatives --install /usr/bin/java java /usr/java/jdk1.8.0_05/bin/java 20000
Step: 4. Then use the alternatives command to verify that the Oracle Java SE Runtime Environment 8 is selected :

alternatives --config java
Selection Command
-----------------------------------------------
+ 1 /usr/lib/jvm/jre-1.7.0-openjdk.x86_64/bin/java
2 /usr/lib/jvm/jre-1.6.0-openjdk.x86_64/bin/java
3 /usr/java/jdk1.8.0_05/bin/java

Enter to keep the current selection[+], or type selection number: 3

Step: 5. Verify Java Version :

java -version
Step: 6. Add the DataStax Community Repository :

vi /etc/yum.repos.d/datastax.repo
[datastax]
name = DataStax Repo for Apache Cassandra
baseurl = http://rpm.datastax.com/community
enabled = 1
gpgcheck = 0

-- Save & Quit (:wq)

Step: 7. Install Apache Cassandra 2 :

yum -y install dsc20
Step: 8. Configure the Apache Cassandra 2 Environment :

export JAVA_HOME=/usr/java/jdk1.8.0_05/
export PATH=$PATH:/usr/java/jdk1.8.0_05/bin/
Step: 9. Get Cassandra Running :

service cassandra start
chkconfig cassandra on
Step: 10. Enter the Cassandra Command Line

cqlsh
The HELP command displays a synopsis & a brief description of all cqlsh commands. Given below is the usage of help command.

cqlsh> help

Documented shell commands: ===========================
CAPTURE COPY DESCRIBE EXPAND SHOW TRACING
CONSISTENCY DESC EXIT HELP SOURCE

CQL help topics: ================
ALTER CREATE_TABLE_OPTIONS SELECT
ALTER_ADD CREATE_TABLE_TYPES SELECT_COLUMNFAMILY
ALTER_ALTER CREATE_USER SELECT_EXPR
ALTER_DROP DELETE SELECT_LIMIT
ALTER_RENAME DELETE_COLUMNS SELECT_TABLE
ALTER_USER DELETE_USING SELECT_WHERE
ALTER_WITH DELETE_WHERE TEXT_OUTPUT
APPLY DROP TIMESTAMP_INPUT
ASCII_OUTPUT DROP_COLUMNFAMILY TIMESTAMP_OUTPUT
BEGIN DROP_INDEX TRUNCATE
BLOB_INPUT DROP_KEYSPACE TYPES
BOOLEAN_INPUT DROP_TABLE UPDATE
COMPOUND_PRIMARY_KEYS DROP_USER UPDATE_COUNTERS
CREATE GRANT UPDATE_SET
CREATE_COLUMNFAMILY INSERT UPDATE_USING
CREATE_COLUMNFAMILY_OPTIONS LIST UPDATE_WHERE
CREATE_COLUMNFAMILY_TYPES LIST_PERMISSIONS USE
CREATE_INDEX LIST_USERS UUID_INPUT
CREATE_KEYSPACE PERMISSIONS
CREATE_TABLE REVOKE

In Cassandra, a keyspace is a container for your application data. It is similar to the schema in a relational database.

cqlsh> desc keyspaces;

system system_traces

Step: 11. To create the keyspace "demo", at the CQL shell prompt, type :

cqlsh> create keyspace demo
WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };

cqlsh> desc keyspaces;

system system_traces demo

cqlsh> use demo;

cqlsh:demo>

Now we have we have a keyspace, we can create tables within that keyspace to store our data in. Tables, or column families,
consist of columns and rows.

Step: 12. Create a “users” table within the keyspace “demo” so that we can insert some data into our database :

cqlsh> USE demo;

cqlsh:demo> create table users ( firstname text,lastname text,age int,city text,primary key (lastname));

cqlsh:demo> DESC SCHEMA;

CREATE KEYSPACE demo WITH replication = {
'class': 'SimpleStrategy',
'replication_factor': '1'
};

USE demo;

CREATE TABLE users (
lastname text,
age int,
city text,
firstname text,
PRIMARY KEY ((lastname))
) WITH
bloom_filter_fp_chance=0.010000 AND
caching='KEYS_ONLY' AND
comment='' AND
dclocal_read_repair_chance=0.100000 AND
gc_grace_seconds=864000 AND
index_interval=128 AND
read_repair_chance=0.000000 AND
replicate_on_write='true' AND
populate_io_cache_on_flush='false' AND
default_time_to_live=0 AND
speculative_retry='99.0PERCENTILE' AND
memtable_flush_period_in_ms=0 AND
compaction={'class': 'SizeTieredCompactionStrategy'} AND
compression={'sstable_compression': 'LZ4Compressor'};

Step: 13. Insert some rows of Data into our newly created ‘users’ table :

Type ENTER after each statement to insert the row into the table:

cqlsh:demo> INSERT INTO users (firstname, lastname, age,city) values ('Soumya', 'Das', 30, 'Calcutta');
cqlsh:demo> INSERT INTO users (firstname, lastname, age,city) values ('udit', 'Gujar', 24, 'Pune');

Now that we have a few rows of data in our table, let’s perform some queries against it. Using a SELECT statement will let us take a peek inside our table. To see all the rows from the users table we’ve created, type

cqlsh:demo> select * from users;

lastname     | age | city    | firstname
--------------+-----+---------+-----------
   das          |  30 | Calcutta| soumya
   Gujar      |  24 |  Pune   | Udit
(2 rows)

cqlsh:demo> exit

Step: 14. Check Cassandra Node Status :

nodetool status
Step: 15. Shutdown Cassandra :

service cassandra stop
service cassandra status
Done...!!!




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Master-Slave replication on PostgreSQL on Rhel/Centos 6.5

Master server:-192.168.100.122
Hostname:-server2.soumya.com

Slave server:-192.168.100.175
Hostname:-server1.soumya.com

Postgresql Version:9.4-1
Linux version:Rhel 6.3


Install postgresql in both server:-
Step 1:-Download the repository
yum install http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm

# vi /etc/yum.repos.d/centos.repo
add the following lines
[centos-6-base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
enabled=1


step 2:- Install Postgresql required packages:-
# yum install postgresql94-server postgresql94-contrib
Incase if the above command gives error regarding the public key, we can use the following command
cd /etc/pki/rpm-gpg
rpm --import RPM-GPG-KEY-CentOS-6

#yum install postgresql94-server

# service postgresql-9.4 initdb

OR

# service postgresql initdb

# chkconfig postgresql-9.4 on

Step 3:-Start postgresql:-

[root@infosystem ~]# service postgresql-9.4 start
Starting postgresql service: [  OK  ]

Check status of postgresql:-
[root@infosystem ~]# service postgresql status
postmaster (pid  4260) is running...

Step 4:- Perform this in both server:-

PostgreSQL creates a user called "postgres" in order to handle its initial databases.
We will configure ssh access between our servers to make transferring files easier.

#passwd postgres
login to postgres user

#su - postgres

Generate an ssh key for the postgres user:(Do this is both server)
In Master server:-
$ ssh-keygen -t rsa
$ ssh-keygen -t dsa
$ cd /var/lib/pgsql/.ssh
$ cat id_rsa.pub >>authorized_keys
$ cat id_dsa.pub >>authorized_keys


In Slave server:-
$ ssh-keygen -t rsa
$ ssh-keygen -t dsa
$ cd /var/lib/pgsql/.ssh
$ cat id_rsa.pub >>authorized_keys
$ cat id_dsa.pub >>authorized_keys


In Master server:-
$ cd /var/lib/pgsql/.ssh
$ cp authorized_keys server2_authorized_keys
$ scp server2_authorized_keys postgres@server1:/var/lib/pgsql/
$ rm -rf authorized_keys

In slave server:-
$ cd /var/lib/pgsql/.ssh
$ cat server2_authorized_keys >>authorized_keys
$ scp authorized_keys postgres@server2:/var/lib/pgsql/

Now test the connectivity from both server.
From master server:-
ssh server1 date

From slave server:-
ssh server2 date

Step 5:-Configure the Master Server.

# su - postgres

$ psql -c "CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'redhat';"

Next, we will move to the postgres configuration directory:
$ cd /var/lib/pgsql/9.4/data

At any place not at the bottom of the file, add a line to let the new user get access to this server:
$ vi pg_hba.conf

host    replication     rep     192.168.100.175/32   md5

:wq

Next, open the main postgres configuration file:

$ vi postgresql.conf

Find these parameters. Uncomment them if they are commented, and modify the values according to what we have listed below:

listen_addresses = 'localhost,192.168.100.122'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on
Save and close the file.


Restart the master server to implement your changes from root user:-
# service  postgresql-9.4 restart

Step 6.Configure the Slave Server.

Begin on the slave server by shutting down the postgres database software:

# service postgresql-9.4 stop


On the slave server, We then will transfer the database data from master server:-

$ cd /var/lib/pgsql/9.4/data
$ rm -rf *
$ pg_basebackup -D /var/lib/pgsql/9.4/data -h 192.168.100.122 -U rep

Now we can see all the files from the master server has been copied into the slave server.

We will be making some similar configuration changes to postgres files,
so change to the configuration directory:

# su - postgres
$ cd /var/lib/pgsql/9.4/data


Adjust the access file to allow the other server to connect to this.
This is in case we need to turn the slave into the master later on down the road.

$ vi pg_hba.conf

Again, add this line somewhere not at the end of the file:

host    replication     rep     192.168.100.122/32  md5

Save and close the file.

Next, open the postgres configuration file:

$ vi postgresql.conf

You can use the same configuration options you set for the master server, modifying only the IP address
to reflect the slave server's address:

listen_addresses = 'localhost,192.168.100.175'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on


--Save and exit.

Step 7.Create recovery.conf file

Here, we need to create a recovery file called recovery.conf:
$ cd /var/lib/pgsql/9.4/data
$ vi recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=192.168.100.122 port=5432 user=rep password=redhat'
trigger_file = '/tmp/postgresql.trigger.5432'

--save and exit.

Test the replication:-
On Master server:-
$ su - postgres
$psql
#CREATE TABLE rep_test (test varchar(40));
Now, we can insert some values into the table with the following commands:

INSERT INTO rep_test VALUES ('data one');
INSERT INTO rep_test VALUES ('some more words');
INSERT INTO rep_test VALUES ('lalala');
INSERT INTO rep_test VALUES ('hello there');
INSERT INTO rep_test VALUES ('blahblah');
To exit from psql shell
\q

Now on slave server:-
$ su - postgres
$psql
#SELECT * FROM rep_test;

      test    
-----------------
 data one
 some more words
 lalala
 hello there
 blahblah
(5 rows)

Now lets see if try insert data from slave server :-

#INSERT INTO rep_test VALUES ('oops');
ERROR:  cannot execute INSERT in a read-only transaction

As we can see, we are unable to insert data into the slave. This is because the data is only being
transferred in one direction. In order to keep the databases consistent, postgres must make the slave
read-only.




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Duplicate a database in a new host/server where only incremental level 0 backup is available in oracle 11g

Oracle Version :-11g
Target Database : prim
Target Database Server : server1.soumya.com
IP: 192.168.2.102

Oracle Version :-11g
Auxiliary Database : stand
Auxiliary Database Server : server3.soumya.com
IP:192.168.2.12

Step 1:
Take the incremental level 0 backup of the Target database using RMAN.
In my case, I had the backup of my target database (prim) taken at the location '/u01/bkp'
[oracle@server1 bkp]$ pwd
/u01/bkp

[oracle@server1 bkp]$ ls -ltrh
total 1.1G
-rw-r-----. 1 oracle oinstall 9.4M Feb 17 15:06 o1_mf_ncsnf_TAG20150217T150631_bg62x0o8_.bkp
-rw-r-----. 1 oracle oinstall  13M Feb 17 15:08 o1_mf_annnn_TAG20150217T150811_bg63043x_.bkp
-rw-r-----. 1 oracle oinstall 1.1G Feb 17 15:09 o1_mf_nnndf_TAG20150217T150813_bg6305j3_.bkp
-rw-r-----. 1 oracle oinstall 9.4M Feb 17 15:09 o1_mf_ncsnf_TAG20150217T150813_bg632lxo_.bkp
-rw-r-----. 1 oracle oinstall 8.0K Feb 17 15:09 o1_mf_annnn_TAG20150217T150932_bg632nml_.bkp


Step 2:
Copy these backup pieces from the Target server (location /u01/bkp) to the auxiliary server (location /u01/bkp)
Also, copy the pfile (initprim.ora) of the Target database to the Auxiliary server.

[oracle@server1 ]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@server1 ]$ cp initprim.ora /u01/bkp/

[oracle@server1 $ scp -r *.* oracle@192.168.2.12:/u01/

oracle@192.168.2.12's password:
initprim.ora  100% 912Kb 0.9KB/s  00:00    100% 911KB 9.0MB/s  00.00
o1_mf_ncsnf_TAG20150217T150631_bg62x0o8_.bkp                                                                  100% 9600KB   9.4MB/s   00:00  
o1_mf_annnn_TAG20150217T150932_bg632nml_.bkp                                                                  100% 8192     8.0KB/s   00:00  
o1_mf_nnndf_TAG20150217T150813_bg6305j3_.bkp                                                                  100% 1060MB  20.0MB/s   00:53  
o1_mf_annnn_TAG20150217T150811_bg63043x_.bkp                                                                  100%   12MB  12.2MB/s   00:00  
o1_mf_ncsnf_TAG20150217T150813_bg632lxo_.bkp                                                                  100% 9600KB   9.4MB/s   00:00  

Step 3:

On the Auxiliary server, edit the pfile that was copied earlier to the desired entries (dump locations, control file location, datafile locations,
if using ASM then specify the desired disk group) and rename it to the desired instance name file (init<SID>.ora). Below is the sample I had it done.

vi initprim.ora

stand.__db_cache_size=16777216
stand.__java_pool_size=20971520
stand.__large_pool_size=4194304
stand.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
stand.__pga_aggregate_target=142606336
stand.__sga_target=272629760
stand.__shared_io_pool_size=0
stand.__shared_pool_size=213909504
stand.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/stand/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/stand/control01.ctl','/u01/app/oracle/flash_recovery_area/stand/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='stand'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.memory_target=415236096
*.open_cursors=300
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/u01/app/oracle/oradata/prim/','/u01/app/oracle/oradata/stand/'
*.log_file_name_convert='/u01/app/oracle/oradata/prim/','/u01/app/oracle/oradata/stand/'

:wq

Make sure db_file_name_convert and log_file_name_convert parameters are present in pfile else it would
throw error while creating auxiliary database .
Once changes are done rename the pfile with your instance sid and copy it inside ORACLE_HOME/dbs
[oracle@server3] mv initprim.ora initstand.ora
[oracle@server3] cp initstand.ora $ORACLE_HOME/dbs

Step 4:

Create a password file for the Auxiliary Database using the ORAPWD utility.

orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstand password=redhat


Step 5:

Start the auxiliary instance using the modified by pfile(initstand.ora)

[oracle@uat ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@uat ~]$ export ORACLE_SID=stand

Keep in mind that the auxiliary instance doesnt have any instance running on, only oracle binaries are installed on this.

Step 6 :-
Create the required directory structures are mentioned in pfile.
oracle@server3 ~]$ mkdir -p /u01/app/oracle/admin/stand/adump
[oracle@server3 ~]$ mkdir -p /u01/app/oracle/oradata/stand/
[oracle@server3  ]$ mkdir -p /u01/app/oracle/flash_recovery_area/stand/

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora'
ORACLE instance started.
Total System Global Area  754974720 bytes
Fixed Size                  2928968 bytes
Variable Size             637537976 bytes
Database Buffers          109051904 bytes
Redo Buffers                5455872 bytes


Step 6:

Connect the auxiliary instance through RMAN and start the duplication.
The duplication is done by specifying the location of the backup pieces. The command to be used is
DUPLICATE DATABASE TO '<auxiliary dbname>' BACKUP LOCATION '<location of the backup pieces on the auxiliary server>'

[oracle@server3 dbs]$ rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Jul 11 18:04:20 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: stand (not mounted)

RMAN> duplicate database to 'stand' backup location '/u01/bkp' NOFILENAMECHECK;

Starting Duplicate Db at 08-JAN-15

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     413372416 bytes

Fixed Size                     2213896 bytes
Variable Size                394266616 bytes
Database Buffers              12582912 bytes
Redo Buffers                   4308992 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PRIM'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''STAND'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/2015_02_17/o1_mf_ncsnf_TAG20150217T150813_bg632lxo_.bkp';
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     413372416 bytes

Fixed Size                     2213896 bytes
Variable Size                394266616 bytes
Database Buffers              12582912 bytes
Redo Buffers                   4308992 bytes

Starting restore at 08-JAN-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/stand/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/stand/control02.ctl
Finished restore at 08-JAN-15

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   set until scn  58614791;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/stand/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/stand/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/stand/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/stand/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/stand/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 08-JAN-15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/stand/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/stand/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/stand/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/stand/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/stand/example01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/2015_02_17/o1_mf_nnndf_TAG20150217T150813_bg6305j3_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/2015_02_17/o1_mf_nnndf_TAG20150217T150813_bg6305j3_.bkp tag=TAG20150217T150813
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:07
Finished restore at 08-JAN-15

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

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf

contents of Memory Script:
{
   set until scn  58614791;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 08-JAN-15
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=16
channel ORA_AUX_DISK_1: reading from backup piece /u01/2015_02_17/o1_mf_annnn_TAG20150217T150932_bg632nml_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/2015_02_17/o1_mf_annnn_TAG20150217T150932_bg632nml_.bkp tag=TAG20150217T150932
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/flash_recovery_area/STAND/archivelog/2015_01_08/o1_mf_1_16_bbxko71g_.arc thread=1 sequence=16
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/STAND/archivelog/2015_01_08/o1_mf_1_16_bbxko71g_.arc RECID=1 STAMP=868491263
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-JAN-15

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
 ''STAND'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     413372416 bytes

Fixed Size                     2213896 bytes
Variable Size                394266616 bytes
Database Buffers              12582912 bytes
Redo Buffers                   4308992 bytes

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

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     413372416 bytes

Fixed Size                     2213896 bytes
Variable Size                394266616 bytes
Database Buffers              12582912 bytes
Redo Buffers                   4308992 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "STAND" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/stand/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/stand/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/stand/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/stand/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/stand/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/stand/sysaux01.dbf",
 "/u01/app/oracle/oradata/stand/undotbs01.dbf",
 "/u01/app/oracle/oradata/stand/users01.dbf",
 "/u01/app/oracle/oradata/stand/example01.dbf",
 "/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf",
 "/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

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

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/sysaux01.dbf RECID=1 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/undotbs01.dbf RECID=2 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/users01.dbf RECID=3 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/example01.dbf RECID=4 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf RECID=5 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf RECID=6 STAMP=868491274

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 08-JAN-15

RMAN>

SQL> select name from v$database;

NAME
---------
STAND

Done..!!!




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Password less authentication between linux servers

Password less authentication between linux servers:-
Source server:-192.168.2.11
Target server:-192.168.2.12

Hostfile entry On both source and target server:

cat /etc/hosts
192.168.2.11 server1.soumya.com server1
192.168.2.12 server2.soumya.com server2

On source server:-

# ssh-keygen -t rsa
# ssh-keygen -t dsa
# cd /root/.ssh
# cat id_rsa.pub >>authorized_keys
# cat id_dsa.pub >>authorized_keys
# scp authorized_keys root@server2:/root/.ssh/

On target server:-
# ssh-keygen -t rsa
# ssh-keygen -t dsa
# cd /root/.ssh
# cat id_rsa.pub >>authorized_keys
# cat id_dsa.pub >>authorized_keys
# scp authorized_keys root@server1:/root/.ssh/


Now lets test if both servers can ssh each other w/o password or not:-

on source server:-
[root@server1 ~]# ssh server2
Last login: Fri May 29 08:32:06 2015 from 192.168.2.50

[root@server1 ~]# ssh server2 date
Fri May 29 08:37:28 IST 2015


on target server:-

[root@server2 ~]# ssh server1
Last login: Fri May 29 08:38:00 2015 from server2.soumya.com

[root@server1 ~]# ssh server1 date
Fri May 29 08:38:14 IST 2015

So we can see both servers can connect each other without prompting for any password.

Happy learning...




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Switchover of Databases (Primary to standby and standby to primary) in Dataguard oracle 11g.

Switchover of Databases (Primary to standby and standby to primary) in  Dataguard  oracle 11g.

Oracle Version:- Enterprise Edition Release 11.2.0.1
OS:- Rhel 6.4
Primary database:- Prim
Standby database:- Stand


Switchover is a planned event, it is ideal when we might want to upgrade the primary database or change
the storage/hardware configuration (add memory, cpu networking), we may even want to upgrade the
configuration to Oracle RAC .

What happens during a switchover is the following :

1.) Notifies the primary database that a switchover is about to occur
2.) Disconnect all users from the primary database
3.) Generate a special redo record that signals the End of Redo (EOR)
4.) Converts the primary database into a standby database
5.) Once the standby database applies the final EOR record, guaranteeing that no data loss has been lost,
converts the standby database into the primary database.



Before doing the switchover lets run few select queries to find out some info:-
In primary:-

SQL> select status,INSTANCE_NAME ,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         prim             PRIMARY

In standby:-

SQL> select status,INSTANCE_NAME ,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
MOUNTED      stand            PHYSICAL STANDBY


In primary:-
Archive log destination of Prim:-

SQL> show parameter log_archive_dest_1;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=/u01/app/oracle/flash
                                                 _recovery_area VALID_FOR=(ALL_
                                                 LOGFILES,ALL_ROLES) DB_UNIQUE_
                                                 NAME=prim


SQL> show parameter log_archive_dest_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=stand LGWR ASYNC VALID
                                                 _FOR=(ALL_LOGFILES,PRIMARY_ROL
                                                 E) DB_UNIQUE_NAME=stand

*Archivelog Destination 1 of Primary Database is "/u01/app/oracle/flash_recovery_area" and Archivelog
destination 2 of Primary database is pointing to the service-name of the standby database.


In standby:-
Archive log destination of Stand:-
SQL> show parameter log_archive_dest_1;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=/u01/app/oracle/flash
                                                 _recovery_area VALID_FOR=(ALL_
                                                 LOGFILES,ALL_ROLES) DB_UNIQUE_
                                                 NAME=stand


SQL> show parameter log_archive_dest_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=prim LGWR ASYNC VALID_
                                                 FOR=(ALL_LOGFILES,PRIMARY_ROLE
                                                 ) DB_UNIQUE_NAME=prim

*Archivelog Destination 1 of Standby database is "/u01/app/oracle/flash_recovery_area" and archivelog
destination 2 of standby database is pointing to the service-name of the primary database.
(Note: destination 2 in standby database is not mandatory. It is required only if the standby database
would be running in as primary database during switchover or failover. Hence it would be a good practice
to set this parameter to avoid problems during the switchover or failover.)

Pre-Switchover Checks:-

In standby database:-

Verify whether Managed Recovery process is running on the standby database

Use the following query to check if the managed recovery process is running on the standby database.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CLOSING              27
RFS       IDLE                  0
RFS       IDLE                 28
MRP0      WAIT_FOR_LOG         28

The above result shows that the Managed Recovery Process (MRP0) is running on the standby database.
If MRP is not running, then start the process with real time enabled using the below query in the
standby database.

SQL>alter database recover managed standby disconnect from session;

Once when the MRP has started on the standby database, make sure that the archive logs generated at the
primary database end are shipped and getting applied to the standby database.

At primary database:-
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            27

At standby database:-
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            27

In the above case, sequence# 27 is the maximum sequence generated at the primary database and the
same has been applied to the standby database.So both database's log files are in sync.

*Verify primary and standby tempfiles match :-

For each temporary tablespace on the standby, verify that temporary files associated with that
tablespace on the primary database also exist on the standby database. Tempfiles added after initial
standby creation are not propagated to the standby. Run this query on both the primary and target
physical standby databases and verify that they match.

In primary database:-
SQL> select ts#, name ,status from v$tempfile;
      TS# NAME                                     STATUS
---------- ---------------------------------------- -------
         3 /u01/app/oracle/oradata/prim/temp01.dbf  ONLINE

SQL> select ts#,name from v$tablespace;

       TS# NAME
---------- ----------------------------------------
         0 SYSTEM
         1 SYSAUX
         2 UNDOTBS1
         4 USERS
         3 TEMP
         6 EXAMPLE


In Standby database:-
SQL> select ts#, name ,status from v$tempfile;
       TS# NAME                                     STATUS
---------- ---------------------------------------- -------
         3 /u01/app/oracle/oradata/stand/temp01.dbf ONLINE


SQL> select ts#,name from v$tablespace;

       TS# NAME
---------- ----------------------------------------
         0 SYSTEM
         1 SYSAUX
         2 UNDOTBS1
         4 USERS
         3 TEMP
         6 EXAMPLE

*Verify that all datafiles are online on both primary and standby databases:-

On both primary and standby database:-

SQL> select name from v$datafile where status='OFFLINE';

no rows selected

If there are any offline datafiles, then bring them online using the below query
SQL>alter database datafile<datafile name> online;


Switchover Steps:

These steps are performed during the switchover process at the primary database side.

Check if there are any jobs running on the primary database using the below query.

SQL>select * from dba_jobs_running;
no rows selected

If there are any jobs running on the primary database and if it’s execution is not very important,
then terminate the job to continue further.

Block further job submission by setting the job_queue_processes parameter to 0 so that there would be no
jobs running during switchover.

SQL> show parameter job_queue_process;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000

Set this parameter to the value 0.
SQL> alter system set job_queue_processes=0 scope=spfile;                  

System altered.


*Verify that the primary database can be switched over to the standby

In primary database:-

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

A value of TO STANDBY or SESSIONS ACTIVE (which requires the WITH SESSION SHUTDOWN clause on the switchover command)
indicates that the primary database can be switched to the standby role. If neither of these values is
returned, a switchover is not possible because redo transport is either mis-configured or is not functioning
properly.


*Switchover the primary database to standby

Once when value of switchover_status returns “TO STANDBY” or “SESSIONS ACTIVE” on the primary database,
then perform the switchover using the below query

In primary:-
SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

Now the primary database is switched over to the standby database. The execution of the above command
may take some time and the archive logs generated during its execution would be automatically applied to
the standby database. Once when the command is executed with the output as “Database altered”, it means
that the primary database has been switched over to the standby.

Note: Always perform the switchover of the primary database to standby database first and then
switchover the standby database to primary. If not, then you would end up landing with two primary
databases.


*Switchover the standby database to primary
Query the switchover_status column from the v$database view at the standby side to determine whether
the standby database can be switched over to the primary database.

In standby database:-
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to
the primary role. If neither of these values is returned, verify that redo apply is active and that redo
transport is configured and working properly. Continue to query this column until the value returned is
either TO PRIMARY or SESSIONS ACTIVE.

Once when the value of switchover_status returns “TO PRIMARY” or “SESSIONS ACTIVE” on the standby
database, then perform the switchover using the below query

SQL>alter database commit to switchover to primary with session shutdown;

Now the standby database has been switched over to the primary database.

*Open the new primary database (stand)

The new primary database will be in mount state. Open this new primary database using the below query.

SQL>alter database open;

SQL> select status,INSTANCE_NAME ,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         stand            PRIMARY


*Restart the new standby database
Restart the new standby database (old primary database prim), bring it to the mount stage and
start the managed recovery process.

Shutdown the new standby database (prim)
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

Startup the new standby database (prim) in mount stage
SQL>startup mount;
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             335546360 bytes
Database Buffers           71303168 bytes
Redo Buffers                4308992 bytes
Database mounted.

Start the managed recovery process on the the new standby database (prim)
SQL>alter database recover managed standby database disconnect from session;
Database altered.

QL> select status,INSTANCE_NAME ,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
MOUNTED      prim             PHYSICAL STANDBY

*Post-Switchover tasks

Reset the job_queue_processes parameter to its previous value
Set the job queue processes to its original value on the new standby (prim).

SQL> show parameter job_queue

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0

SQL> alter system set job_queue_processes=1000 scope=spfile;

System altered.


Now the roles of the databases have been changed. The primary database (prim) has been changed to
standby database and the standby database (stnd) has been changed to primary database.
The archive logs that get generated in the new primary database (stnd) get shipped automatically to the
new standby database (prim) and they are applied on it automatically.

Maximum archivelog generated at the new primary database (stnd)

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            34

Maximum archivelog that has been shipped and applied to the new standby database (prim)
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            34


*Now if we want the real time apply enabled on new standby database(prim)
Perform the following queries on new standby database(prim)
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

 

Please share your ideas and opinions about this topic. If you like this post, then please share with others. Please subscribe on email for every updates on mail.