>> Create SSH Directory, and Create SSH Keys On Each Node
Complete the following steps on each node:
1. Log in as the software owner (in this example, the grid user).
$ id
uid=502(grid) gid=501(oinstall) groups=501(oinstall),502(grid,asmadmin,asmdba)
$ id grid
uid=502(grid) gid=501(oinstall) groups=501(oinstall),502(grid,asmadmin,asmdba)
3. If necessary, create the .ssh directory in the grid user's home directory, and set permissions on it to ensure that only the oracle user has read and write permissions:
$ mkdir ~/.ssh
$ chmod 700 ~/.ssh
Note: SSH configuration will fail if the permissions are not set to 700.
4. Enter the following command:
$ /usr/bin/ssh-keygen -t dsa
At the prompts, accept the default location for the key file (press Enter).
Note: SSH with passphrase is not supported for Oracle Clusterware 11g release 2 and later releases. So press enter.
This command writes the DSA public key to the ~/.ssh/id_dsa.pub file and the private key to the ~/.ssh/id_dsa file.
5. Repeat steps 1 through 3 on each node that you intend to make a member of the cluster, using the DSA key.
>>Add All Keys to a Common authorized_keys File
Complete the following steps:
1. user is grid. Add the DSA key to the authorized_keys file using the following commands:
[grid@host01 ~]$ cd ~/.ssh
[grid@host01 .ssh]$
[grid@host01 .ssh]$ cat id_dsa.pub >> authorized_keys
[grid@host01 .ssh]$ ls -ltr
total 16
-rw-r--r-- 1 grid oinstall 601 Sep 23 13:07 id_dsa.pub
-rw------- 1 grid oinstall 672 Sep 23 13:07 id_dsa
-rw-r--r-- 1 grid oinstall 400 Sep 23 13:07 known_hosts
-rw-r--r-- 1 grid oinstall 601 Sep 23 13:12 authorized_keys
[grid@host01 .ssh]$
[grid@host01 .ssh]$
[grid@host01 .ssh]$ scp authorized_keys host02:/home/grid/.ssh/
grid@host02's password:
authorized_keys 100% 601 0.6KB/s 00:00
[grid@host01 .ssh]$ scp authorized_keys host03:/home/grid/.ssh/
The authenticity of host 'host03 (192.0.2.103)' can't be established.
RSA key fingerprint is e8:aa:00:2c:2e:5c:e4:d8:fe:fd:9b:3f:8c:8b:d4:0b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'host03,192.0.2.103' (RSA) to the list of known hosts.
grid@host03's password:
authorized_keys 100% 601 0.6KB/s 00:00
[grid@host01 .ssh]$
** repeat this work [Add All Keys to a Common authorized_keys File] for host02 and host03
>> Enabling SSH User Equivalency on Cluster Nodes
[grid@nodename]$ ssh host01 date
[grid@nodename]$ ssh host022 date
Implementation
================
[grid@host01 ~]$
[grid@host01 ~]$ id
uid=54322(grid) gid=54321(oinstall) groups=54321(oinstall),54327(asmdba),54328(asmoper),54329(asmadmin)
[grid@host01 ~]$
[grid@host01 ~]$
[grid@host01 ~]$ rm -rf ~/.ssh
[grid@host01 ~]$
[grid@host01 ~]$ mkdir ~/.ssh
[grid@host01 ~]$ chmod 700 ~/.ssh
[grid@host01 ~]$
[grid@host01 ~]$ /usr/bin/ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/home/grid/.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/grid/.ssh/id_dsa.
Your public key has been saved in /home/grid/.ssh/id_dsa.pub.
The key fingerprint is:
ae:ab:0e:7d:ad:11:cf:3d:74:59:95:3d:6e:95:6a:67 grid@host01
The key's randomart image is:
+--[ DSA 1024]----+
| =|
| =o|
| + o|
| = E |
| . S . + + |
| . * o . |
| . . o = o |
| . . + . |
| .o.+. |
+-----------------+
[grid@host01 ~]$
[grid@host01 ~]$ ssh grid@host02
The authenticity of host 'host02 (192.0.2.102)' can't be established.
RSA key fingerprint is e8:aa:00:2c:2e:5c:e4:d8:fe:fd:9b:3f:8c:8b:d4:0b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'host02,192.0.2.102' (RSA) to the list of known hosts.
grid@host02's password:
[grid@host02 ~]$
[grid@host02 ~]$
[grid@host02 ~]$ rm -rf ~./ssh
[grid@host02 ~]$ rm -rf ~/.ssh
[grid@host02 ~]$ mkdir ~/.ssh
[grid@host02 ~]$ chmod 700 ~/.ssh
[grid@host02 ~]$
[grid@host02 ~]$ /usr/bin/ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/home/grid/.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/grid/.ssh/id_dsa.
Your public key has been saved in /home/grid/.ssh/id_dsa.pub.
The key fingerprint is:
f8:3d:73:d8:96:65:4c:29:30:c5:66:35:a5:1d:4a:6e grid@host02
The key's randomart image is:
+--[ DSA 1024]----+
| oo.ooo.|
| o* .=.|
| o.E+ .|
| . .+ |
| . S + |
| . . o + |
| . = = |
| = |
| |
+-----------------+
[grid@host02 ~]$
[grid@host02 ~]$
[grid@host02 ~]$
[grid@host02 ~]$ ssh grid@host03
The authenticity of host 'host03 (192.0.2.103)' can't be established.
RSA key fingerprint is e8:aa:00:2c:2e:5c:e4:d8:fe:fd:9b:3f:8c:8b:d4:0b.
Are you sure you want to continue connecting (yes/no)? y
Please type 'yes' or 'no': yes
Warning: Permanently added 'host03,192.0.2.103' (RSA) to the list of known hosts.
grid@host03's password:
[grid@host03 ~]$
[grid@host03 ~]$
[grid@host03 ~]$ rm -rf ~/.ssh
[grid@host03 ~]$
[grid@host03 ~]$ mkdir ~/.ssh
[grid@host03 ~]$ chmod 700 ~/.ssh
[grid@host03 ~]$
[grid@host03 ~]$
[grid@host03 ~]$ /usr/bin/ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/home/grid/.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/grid/.ssh/id_dsa.
Your public key has been saved in /home/grid/.ssh/id_dsa.pub.
The key fingerprint is:
bd:91:2c:57:93:b9:9f:22:bc:23:7a:9b:e7:fb:07:71 grid@host03
The key's randomart image is:
+--[ DSA 1024]----+
| |
| o |
| = |
| o o.oE |
| S * .o |
| + o.. . |
| + ..o |
| o.oo .. |
| .oo=++.. |
+-----------------+
[grid@host03 ~]$
[grid@host03 ~]$
[grid@host03 ~]$
[grid@host03 ~]$ logout
Connection to host03 closed.
[grid@host02 ~]$ logout
Connection to host02 closed.
[grid@host01 ~]$
[grid@host01 ~]$
[grid@host01 ~]$ cd ~/.ssh
[grid@host01 .ssh]$
[grid@host01 .ssh]$ cat id_dsa.pub >> authorized_keys
[grid@host01 .ssh]$ ls -ltr
total 16
-rw-r--r-- 1 grid oinstall 601 Sep 23 13:07 id_dsa.pub
-rw------- 1 grid oinstall 672 Sep 23 13:07 id_dsa
-rw-r--r-- 1 grid oinstall 400 Sep 23 13:07 known_hosts
-rw-r--r-- 1 grid oinstall 601 Sep 23 13:12 authorized_keys
[grid@host01 .ssh]$
[grid@host01 .ssh]$
[grid@host01 .ssh]$ scp authorized_keys host02:/home/grid/.ssh/
grid@host02's password:
authorized_keys 100% 601 0.6KB/s 00:00
[grid@host01 .ssh]$ scp authorized_keys host03:/home/grid/.ssh/
The authenticity of host 'host03 (192.0.2.103)' can't be established.
RSA key fingerprint is e8:aa:00:2c:2e:5c:e4:d8:fe:fd:9b:3f:8c:8b:d4:0b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'host03,192.0.2.103' (RSA) to the list of known hosts.
grid@host03's password:
authorized_keys 100% 601 0.6KB/s 00:00
[grid@host01 .ssh]$
[grid@host01 .ssh]$
[grid@host01 .ssh]$ ssh grid@host02
[grid@host02 ~]$
[grid@host02 ~]$
[grid@host02 ~]$ cd /home/grid/.ssh
[grid@host02 .ssh]$
[grid@host02 .ssh]$ cat id_dsa.pub >> authorized_keys
[grid@host02 .ssh]$
[grid@host02 .ssh]$ ll
total 16
-rw-r--r-- 1 grid oinstall 1202 Sep 23 13:16 authorized_keys
-rw------- 1 grid oinstall 668 Sep 23 13:08 id_dsa
-rw-r--r-- 1 grid oinstall 601 Sep 23 13:08 id_dsa.pub
-rw-r--r-- 1 grid oinstall 400 Sep 23 13:09 known_hosts
[grid@host02 .ssh]$
[grid@host02 .ssh]$
[grid@host02 .ssh]$ scp authorized_keys host01:/home/grid/.ssh/
The authenticity of host 'host01 (192.0.2.101)' can't be established.
RSA key fingerprint is e8:aa:00:2c:2e:5c:e4:d8:fe:fd:9b:3f:8c:8b:d4:0b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'host01,192.0.2.101' (RSA) to the list of known hosts.
grid@host01's password:
authorized_keys 100% 1202 1.2KB/s 00:00
[grid@host02 .ssh]$ scp authorized_keys host03:/home/grid/.ssh/
grid@host03's password:
authorized_keys 100% 1202 1.2KB/s 00:00
[grid@host02 .ssh]$
[grid@host02 .ssh]$ ssh grid@host03
[grid@host03 ~]$
[grid@host03 ~]$
[grid@host03 ~]$ cd /home/grid/.ssh
[grid@host03 .ssh]$
[grid@host03 .ssh]$ cat id_dsa.pub >> authorized_keys
[grid@host03 .ssh]$ ls
authorized_keys id_dsa id_dsa.pub
[grid@host03 .ssh]$
[grid@host03 .ssh]$
[grid@host03 .ssh]$ ll
total 12
-rw-r--r-- 1 grid oinstall 1803 Sep 23 13:17 authorized_keys
-rw------- 1 grid oinstall 668 Sep 23 13:10 id_dsa
-rw-r--r-- 1 grid oinstall 601 Sep 23 13:10 id_dsa.pub
[grid@host03 .ssh]$
[grid@host03 .ssh]$
[grid@host03 .ssh]$ scp authorized_keys host01:/home/grid/.ssh
The authenticity of host 'host01 (192.0.2.101)' can't be established.
RSA key fingerprint is e8:aa:00:2c:2e:5c:e4:d8:fe:fd:9b:3f:8c:8b:d4:0b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'host01,192.0.2.101' (RSA) to the list of known hosts.
grid@host01's password:
authorized_keys 100% 1803 1.8KB/s 00:00
[grid@host03 .ssh]$
[grid@host03 .ssh]$ scp authorized_keys host02:/home/grid/.ssh/
The authenticity of host 'host02 (192.0.2.102)' can't be established.
RSA key fingerprint is e8:aa:00:2c:2e:5c:e4:d8:fe:fd:9b:3f:8c:8b:d4:0b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'host02,192.0.2.102' (RSA) to the list of known hosts.
grid@host02's password:
authorized_keys 100% 1803 1.8KB/s 00:00
[grid@host03 .ssh]$
[grid@host03 .ssh]$
[grid@host03 .ssh]$ logout
Connection to host03 closed.
[grid@host02 .ssh]$ logout
Connection to host02 closed.
[grid@host01 .ssh]$
[grid@host01 .ssh]$ ssh hos02 date
ssh: Could not resolve hostname hos02: Name or service not known
[grid@host01 .ssh]$ ssh host02 date
Sat Sep 23 13:19:29 UTC 2017
[grid@host01 .ssh]$
[grid@host01 .ssh]$ ssh host03 date
Sat Sep 23 13:19:36 UTC 2017
[grid@host01 .ssh]$
Saturday, September 23, 2017
Wednesday, August 9, 2017
Datapump Backup Status
select
round(sofar/totalwork*100,2) percent_completed,
v$session_longops.*
from
v$session_longops
where
sofar <> totalwork
order by
target,
sid;
round(sofar/totalwork*100,2) percent_completed,
v$session_longops.*
from
v$session_longops
where
sofar <> totalwork
order by
target,
sid;
RMAN Backup Restore
1. pfile create
2. startup nomount with pfile
3. restore control file (backup control file)
4. shutdown immediate
5. Again in Pfile, changes control file location/name according to restore control file
6. Startup nomount with pfile
7. alter datbase mount
8. restore database preview; (pickup scn for restore this point)
9. restore and recover datbase;
**Note: when restore level 0 and Level 1 backup. For control file restoring, restore latest level 1 control file of that backup.
===========PFILE======
*.audit_file_dest='/u02/app/oracle/admin/dcpdb/adump'
*.audit_sys_operations=TRUE
*.audit_trail='DB','EXTENDED'
*.compatible='11.2.0.4.0'
*.control_files='+REDO/dcpdb/controlfile/current.256.951579285','+CONTROL/dcpdb/controlfile/current.256.951579285'
*.cursor_sharing='EXACT'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.db_create_online_log_dest_2='+CONTROL'
*.db_domain='nblitd.com'
*.db_name='dcpdb'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=448256040960
*.db_unique_name='dcpdb'
*.dg_broker_start=FALSE
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dcpdbXDB)'
*.filesystemio_options='SETALL'
#*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb1-vip.nblitd.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb1-vip.nblitd.com)(PORT=1971))))'
#*.log_archive_config='DG_CONFIG=(dcpdb,drpdb)'
#*.log_archive_dest_1='LOCATION=DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dcpdb'
*.log_archive_dest_1='LOCATION=+fra/dcpdb/ARCHIVELOG'
#*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=10
#*.memory_target=82678120448
*.memory_target=4294967296
*.open_cursors=5000
*.optimizer_index_cost_adj=100
*.processes=5000
*.query_rewrite_integrity='TRUSTED'
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=5000
*.sessions=7524
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
--====01_control_restore.sh===Restore control file script===
#!/bin/sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4.0/dbhome_1
export ORACLE_SID=dcpdb
export PATH=$ORACLE_HOME/bin:$PATH
LOG_FILE=/db_rmanbackup/rman_Restore_controlfile_${ORACLE_SID}_`date +%y%m%d%H%M`.log
echo "dcpdb Controlfile Restore STARTED on `date` " >$LOG_FILE
rman target / <<! >>$LOG_FILE
run {
restore controlfile from '/db_rmanbackup/t24_ctl_l1_1_6682_060820170800.bkp';
}
!
echo "dcpdb Controlfile Restore FINISHIED on `date` " >>$LOG_FILE
exit 0
--=====02_restore_db.sh=====Restore and recover database script===
#!/bin/sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4.0/dbhome_1
export ORACLE_SID=dcpdb
export PATH=$ORACLE_HOME/bin:$PATH
LOG_FILE=/db_rmanbackup/rman_${ORACLE_SID}_Restore_`date +%y%m%d%H%M`.log
echo "dcpdb database Restore STARTED on `date` " >$LOG_FILE
rman target / <<! >>$LOG_FILE
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
SET UNTIL SCN 35660797287;
RESTORE DATABASE;
RECOVER DATABASE;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
!
echo "dcpdb database Restore FINISHIED on `date` " >>$LOG_FILE
exit 0
--====03_recover_db.sh====recover database (standalone)===
#!/bin/sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4.0/dbhome_1
export ORACLE_SID=dcpdb
export PATH=$ORACLE_HOME/bin:$PATH
LOG_FILE=/db_rmanbackup/rman_${ORACLE_SID}_Recover_`date +%y%m%d%H%M`.log
echo "dcpdb database Recover STARTED on `date` " >$LOG_FILE
rman target / <<! >>$LOG_FILE
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
SET UNTIL SCN 33986365999;
RECOVER DATABASE;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
!
echo "dcpdb database Reccover FINISHIED on `date` " >>$LOG_FILE
exit 0
--=====run script command===
oracle@drp4db [/db_rmanbackup]$ nohup 01_control_restore.sh &
oracle@drp4db [/db_rmanbackup]$ nohup 02_restore_db.sh &
2. startup nomount with pfile
3. restore control file (backup control file)
4. shutdown immediate
5. Again in Pfile, changes control file location/name according to restore control file
6. Startup nomount with pfile
7. alter datbase mount
8. restore database preview; (pickup scn for restore this point)
9. restore and recover datbase;
**Note: when restore level 0 and Level 1 backup. For control file restoring, restore latest level 1 control file of that backup.
===========PFILE======
*.audit_file_dest='/u02/app/oracle/admin/dcpdb/adump'
*.audit_sys_operations=TRUE
*.audit_trail='DB','EXTENDED'
*.compatible='11.2.0.4.0'
*.control_files='+REDO/dcpdb/controlfile/current.256.951579285','+CONTROL/dcpdb/controlfile/current.256.951579285'
*.cursor_sharing='EXACT'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDO'
*.db_create_online_log_dest_2='+CONTROL'
*.db_domain='nblitd.com'
*.db_name='dcpdb'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=448256040960
*.db_unique_name='dcpdb'
*.dg_broker_start=FALSE
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dcpdbXDB)'
*.filesystemio_options='SETALL'
#*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb1-vip.nblitd.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=dcpdb1-vip.nblitd.com)(PORT=1971))))'
#*.log_archive_config='DG_CONFIG=(dcpdb,drpdb)'
#*.log_archive_dest_1='LOCATION=DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dcpdb'
*.log_archive_dest_1='LOCATION=+fra/dcpdb/ARCHIVELOG'
#*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=10
#*.memory_target=82678120448
*.memory_target=4294967296
*.open_cursors=5000
*.optimizer_index_cost_adj=100
*.processes=5000
*.query_rewrite_integrity='TRUSTED'
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=5000
*.sessions=7524
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
--====01_control_restore.sh===Restore control file script===
#!/bin/sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4.0/dbhome_1
export ORACLE_SID=dcpdb
export PATH=$ORACLE_HOME/bin:$PATH
LOG_FILE=/db_rmanbackup/rman_Restore_controlfile_${ORACLE_SID}_`date +%y%m%d%H%M`.log
echo "dcpdb Controlfile Restore STARTED on `date` " >$LOG_FILE
rman target / <<! >>$LOG_FILE
run {
restore controlfile from '/db_rmanbackup/t24_ctl_l1_1_6682_060820170800.bkp';
}
!
echo "dcpdb Controlfile Restore FINISHIED on `date` " >>$LOG_FILE
exit 0
--=====02_restore_db.sh=====Restore and recover database script===
#!/bin/sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4.0/dbhome_1
export ORACLE_SID=dcpdb
export PATH=$ORACLE_HOME/bin:$PATH
LOG_FILE=/db_rmanbackup/rman_${ORACLE_SID}_Restore_`date +%y%m%d%H%M`.log
echo "dcpdb database Restore STARTED on `date` " >$LOG_FILE
rman target / <<! >>$LOG_FILE
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
SET UNTIL SCN 35660797287;
RESTORE DATABASE;
RECOVER DATABASE;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
!
echo "dcpdb database Restore FINISHIED on `date` " >>$LOG_FILE
exit 0
--====03_recover_db.sh====recover database (standalone)===
#!/bin/sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4.0/dbhome_1
export ORACLE_SID=dcpdb
export PATH=$ORACLE_HOME/bin:$PATH
LOG_FILE=/db_rmanbackup/rman_${ORACLE_SID}_Recover_`date +%y%m%d%H%M`.log
echo "dcpdb database Recover STARTED on `date` " >$LOG_FILE
rman target / <<! >>$LOG_FILE
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
SET UNTIL SCN 33986365999;
RECOVER DATABASE;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
!
echo "dcpdb database Reccover FINISHIED on `date` " >>$LOG_FILE
exit 0
--=====run script command===
oracle@drp4db [/db_rmanbackup]$ nohup 01_control_restore.sh &
oracle@drp4db [/db_rmanbackup]$ nohup 02_restore_db.sh &
Tuesday, July 18, 2017
ORA-12514: TNS:listener does not currently know of service requested in connect
Problem
=========
$
$ sqlplus nt24db/xxxx@racdb
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 18 16:33:15 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name:
$
$
=========
$
$ sqlplus nt24db/xxxx@racdb
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 18 16:33:15 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name:
$
$
Solution
=========
SQL> alter system set local_listener = '(address_list=(address=(protocol=tcp)(host=drp4db-vip.nblbd.com)(port=1521)))';
As soon as set the value of local_listener, PMON will know the port number on which listener is running and it will dynamically register the instance with listener.
Check Connection:
==============
$ sqlplus nt24db@racdb
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 18 17:01:42 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
SQL>
SQL>
Monday, July 3, 2017
Dataguard SYNC Check
Dataguard SYNC Check
Primary:
SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
PhyStdby:
SQL> select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
PhyStdby:
SQL>select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY')
group by thread# order by 1;
PhyStdby:
SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" , almax-lhmax "Sequence Difference"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) lh
where al.thrd = lh.thrd;
Primary:
SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
PhyStdby:
SQL> select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
PhyStdby:
SQL>select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY')
group by thread# order by 1;
PhyStdby:
SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" , almax-lhmax "Sequence Difference"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) lh
where al.thrd = lh.thrd;
Sunday, July 2, 2017
RMAN Incremental Level 0 and Level 1 backup with Archivelog.
1. RMAN Incremental Level 0 Disk Backup with archivelog
oracle@d**db1 [/u02/app/oracle]$ cat 01.daily_rman_level0_disk_backup.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
export ORACLE_SID=d**db1
export PATH=$ORACLE_HOME/bin:$PATH
export TZ=GMT6
dt=`date '+%d%m%Y%H%M'`
LOG_FILE=/db_rmanbackup/rman_logs/${ORACLE_SID}_DAILY_LEVEL0_BACKUP_${dt}.log
echo "DCPDB DAILY LEVEL 0 BACKUP STARTED on `date` " >$LOG_FILE
rman <<! >>$LOG_FILE
connect target r**nbkp/r**nbkp
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
sql 'alter system archive log current';
RUN
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
sql "alter system switch logfile";
backup incremental level 0 AS COMPRESSED BACKUPSET database tag 'LEV0_T24_DB_$dt' format '/db_rmanbackup/t24_db_l0_%p_%s_$dt.bkp';
backup AS COMPRESSED BACKUPSET archivelog all tag 'daily_arch_bkp_disk' format '/db_rmanbackup/t24_arch_%p_%s_$dt.bkp';
backup current controlfile tag T24_CTL_$dt format '/db_rmanbackup/t24_ctl_lv0_%p_%s_$dt.bkp';
backup spfile tag T24_SPF_$dt format '/db_rmanbackup/t24_spf_lv0_%p_%s_$dt.bkp';
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt archivelog until time 'sysdate -3';
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
}
!
echo "DCPDB DAILY LEVEL 0 BACKUP FINISHIED on `date` " >>$LOG_FILE
exit 0
oracle@d**db1 [/u02/app/oracle]$
2. RMAN Incremental Level 1 Disk Backup with Archivelog
oracle@d**db1 [/u02/app/oracle]$ cat 02.daily_rman_level1_disk_backup.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
export ORACLE_SID=d**db1
export PATH=$ORACLE_HOME/bin:$PATH
export TZ=GMT6
dt=`date '+%d%m%Y%H%M'`
LOG_FILE=/db_rmanbackup/rman_logs/${ORACLE_SID}_DAILY_LEVEL1_BACKUP_${dt}.log
echo "DCPDB DAILY LEVEL 1 BACKUP STARTED on `date` " >$LOG_FILE
rman <<! >>$LOG_FILE
connect target r**nbkp/r**nbkp
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
sql 'alter system archive log current';
RUN
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
sql "alter system switch logfile";
backup incremental level 1 cumulative AS COMPRESSED BACKUPSET database tag 'LEV1_T24_DB_$dt' format '/db_rmanbackup/t24_db_l1_%p_%s_$dt.bkp';
backup AS COMPRESSED BACKUPSET archivelog all tag 'daily_arch_bkp_disk' format '/db_rmanbackup/t24_arch_%p_%s_$dt.bkp';
backup current controlfile tag T24_CTL_$dt format '/db_rmanbackup/t24_ctl_lv0_%p_%s_$dt.bkp';
backup spfile tag T24_SPF_$dt format '/db_rmanbackup/t24_spf_lv0_%p_%s_$dt.bkp';
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt archivelog until time 'sysdate -3';
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
}
!
echo "DCPDB LEVEL 1 BACKUP FINISHIED on `date` " >>$LOG_FILE
exit 0
oracle@d**db1 [/u02/app/oracle]$
3. RMAN level 0 disk backup (Monthly)
oracle@d**db1 [/u02/app/oracle]$ cat 03.monthly_rman_level0_disk_backup.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
export ORACLE_SID=d**db1
export PATH=$ORACLE_HOME/bin:$PATH
export TZ=GMT6
dt=`date '+%d%m%Y%H%M'`
LOG_FILE=/db_rmanbackup/rman_logs/${ORACLE_SID}_Monthly_LEVEL0_BACKUP_${dt}.log
echo "DCPDB MONTHLY LEVEL 0 BACKUP STARTED on `date` " >$LOG_FILE
rman <<! >>$LOG_FILE
connect target r**nbkp/r**nbkp
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
sql 'alter system archive log current';
RUN
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
sql "alter system switch logfile";
backup incremental level 0 AS COMPRESSED BACKUPSET database tag 'MONTHLY_L0_T24_DB_$dt' format '/db_rmanbackup/t24_db_month_%p_%s_$dt.bkp' keep until time 'sysdate+1095';
backup AS COMPRESSED BACKUPSET archivelog all tag 'monthly_arch_bkp_disk' format '/db_rmanbackup/t24_arch_%p_%s_$dt.bkp' keep until time 'sysdate+1095';
backup current controlfile tag T24_CTL_$dt format '/db_rmanbackup/t24_ctl_lv0_%p_%s_$dt.bkp';
backup spfile tag T24_SPF_$dt format '/db_rmanbackup/t24_spf_lv0_%p_%s_$dt.bkp';
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt archivelog until time 'sysdate -3';
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
}
!
echo "DCPDB MONTHLY LEVEL 0 BACKUP FINISHIED on `date` " >>$LOG_FILE
exit 0
oracle@d**db1 [/u02/app/oracle]$
3. RMAN level 0 disk backup (Yearly)
oracle@dcpdb1 [/u02/app/oracle]$ cat 04.yearly_rman_level0_disk_backup.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
export ORACLE_SID=d**db1
export PATH=$ORACLE_HOME/bin:$PATH
export TZ=GMT6
dt=`date '+%d%m%Y%H%M'`
LOG_FILE=/db_rmanbackup/rman_logs/${ORACLE_SID}_Yearly_LEVEL0_BACKUP_${dt}.log
echo "DCPDB YEARLY LEVEL 0 BACKUP STARTED on `date` " >$LOG_FILE
rman <<! >>$LOG_FILE
connect target r**nbkp/r**nbkp
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
sql 'alter system archive log current';
RUN
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
sql "alter system switch logfile";
backup incremental level 0 AS COMPRESSED BACKUPSET database tag 'YEARLY_L0_T24_DB_$dt' format '/db_rmanbackup/t24_db_year_l0_%p_%s_$dt.bkp' keep until time 'sysdate+3650';
backup AS COMPRESSED BACKUPSET archivelog all tag 'yearly_arch_bkp_disk' format '/db_rmanbackup/t24_arch_%p_%s_$dt.bkp' keep until time 'sysdate+3650';
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt archivelog until time 'sysdate -3';
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
}
!
echo "DCPDB YEARLY LEVEL 0 BACKUP FINISHIED on `date` " >>$LOG_FILE
exit 0
oracle@d**db1 [/u02/app/oracle]$
oracle@d**db1 [/u02/app/oracle]$ cat 01.daily_rman_level0_disk_backup.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
export ORACLE_SID=d**db1
export PATH=$ORACLE_HOME/bin:$PATH
export TZ=GMT6
dt=`date '+%d%m%Y%H%M'`
LOG_FILE=/db_rmanbackup/rman_logs/${ORACLE_SID}_DAILY_LEVEL0_BACKUP_${dt}.log
echo "DCPDB DAILY LEVEL 0 BACKUP STARTED on `date` " >$LOG_FILE
rman <<! >>$LOG_FILE
connect target r**nbkp/r**nbkp
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
sql 'alter system archive log current';
RUN
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
sql "alter system switch logfile";
backup incremental level 0 AS COMPRESSED BACKUPSET database tag 'LEV0_T24_DB_$dt' format '/db_rmanbackup/t24_db_l0_%p_%s_$dt.bkp';
backup AS COMPRESSED BACKUPSET archivelog all tag 'daily_arch_bkp_disk' format '/db_rmanbackup/t24_arch_%p_%s_$dt.bkp';
backup current controlfile tag T24_CTL_$dt format '/db_rmanbackup/t24_ctl_lv0_%p_%s_$dt.bkp';
backup spfile tag T24_SPF_$dt format '/db_rmanbackup/t24_spf_lv0_%p_%s_$dt.bkp';
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt archivelog until time 'sysdate -3';
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
}
!
echo "DCPDB DAILY LEVEL 0 BACKUP FINISHIED on `date` " >>$LOG_FILE
exit 0
oracle@d**db1 [/u02/app/oracle]$
2. RMAN Incremental Level 1 Disk Backup with Archivelog
oracle@d**db1 [/u02/app/oracle]$ cat 02.daily_rman_level1_disk_backup.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
export ORACLE_SID=d**db1
export PATH=$ORACLE_HOME/bin:$PATH
export TZ=GMT6
dt=`date '+%d%m%Y%H%M'`
LOG_FILE=/db_rmanbackup/rman_logs/${ORACLE_SID}_DAILY_LEVEL1_BACKUP_${dt}.log
echo "DCPDB DAILY LEVEL 1 BACKUP STARTED on `date` " >$LOG_FILE
rman <<! >>$LOG_FILE
connect target r**nbkp/r**nbkp
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
sql 'alter system archive log current';
RUN
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
sql "alter system switch logfile";
backup incremental level 1 cumulative AS COMPRESSED BACKUPSET database tag 'LEV1_T24_DB_$dt' format '/db_rmanbackup/t24_db_l1_%p_%s_$dt.bkp';
backup AS COMPRESSED BACKUPSET archivelog all tag 'daily_arch_bkp_disk' format '/db_rmanbackup/t24_arch_%p_%s_$dt.bkp';
backup current controlfile tag T24_CTL_$dt format '/db_rmanbackup/t24_ctl_lv0_%p_%s_$dt.bkp';
backup spfile tag T24_SPF_$dt format '/db_rmanbackup/t24_spf_lv0_%p_%s_$dt.bkp';
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt archivelog until time 'sysdate -3';
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
}
!
echo "DCPDB LEVEL 1 BACKUP FINISHIED on `date` " >>$LOG_FILE
exit 0
oracle@d**db1 [/u02/app/oracle]$
3. RMAN level 0 disk backup (Monthly)
oracle@d**db1 [/u02/app/oracle]$ cat 03.monthly_rman_level0_disk_backup.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
export ORACLE_SID=d**db1
export PATH=$ORACLE_HOME/bin:$PATH
export TZ=GMT6
dt=`date '+%d%m%Y%H%M'`
LOG_FILE=/db_rmanbackup/rman_logs/${ORACLE_SID}_Monthly_LEVEL0_BACKUP_${dt}.log
echo "DCPDB MONTHLY LEVEL 0 BACKUP STARTED on `date` " >$LOG_FILE
rman <<! >>$LOG_FILE
connect target r**nbkp/r**nbkp
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
sql 'alter system archive log current';
RUN
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
sql "alter system switch logfile";
backup incremental level 0 AS COMPRESSED BACKUPSET database tag 'MONTHLY_L0_T24_DB_$dt' format '/db_rmanbackup/t24_db_month_%p_%s_$dt.bkp' keep until time 'sysdate+1095';
backup AS COMPRESSED BACKUPSET archivelog all tag 'monthly_arch_bkp_disk' format '/db_rmanbackup/t24_arch_%p_%s_$dt.bkp' keep until time 'sysdate+1095';
backup current controlfile tag T24_CTL_$dt format '/db_rmanbackup/t24_ctl_lv0_%p_%s_$dt.bkp';
backup spfile tag T24_SPF_$dt format '/db_rmanbackup/t24_spf_lv0_%p_%s_$dt.bkp';
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt archivelog until time 'sysdate -3';
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
}
!
echo "DCPDB MONTHLY LEVEL 0 BACKUP FINISHIED on `date` " >>$LOG_FILE
exit 0
oracle@d**db1 [/u02/app/oracle]$
3. RMAN level 0 disk backup (Yearly)
oracle@dcpdb1 [/u02/app/oracle]$ cat 04.yearly_rman_level0_disk_backup.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1
export ORACLE_SID=d**db1
export PATH=$ORACLE_HOME/bin:$PATH
export TZ=GMT6
dt=`date '+%d%m%Y%H%M'`
LOG_FILE=/db_rmanbackup/rman_logs/${ORACLE_SID}_Yearly_LEVEL0_BACKUP_${dt}.log
echo "DCPDB YEARLY LEVEL 0 BACKUP STARTED on `date` " >$LOG_FILE
rman <<! >>$LOG_FILE
connect target r**nbkp/r**nbkp
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
sql 'alter system archive log current';
RUN
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
sql "alter system switch logfile";
backup incremental level 0 AS COMPRESSED BACKUPSET database tag 'YEARLY_L0_T24_DB_$dt' format '/db_rmanbackup/t24_db_year_l0_%p_%s_$dt.bkp' keep until time 'sysdate+3650';
backup AS COMPRESSED BACKUPSET archivelog all tag 'yearly_arch_bkp_disk' format '/db_rmanbackup/t24_arch_%p_%s_$dt.bkp' keep until time 'sysdate+3650';
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt archivelog until time 'sysdate -3';
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
}
!
echo "DCPDB YEARLY LEVEL 0 BACKUP FINISHIED on `date` " >>$LOG_FILE
exit 0
oracle@d**db1 [/u02/app/oracle]$
ORA-03137: TTC protocol internal error : [3147] [] [] [] [] [] [] []
ORA-03137:
TTC protocol internal error : [3147] [] [] [] [] [] [] []
This
error is only telling us that the client and server process communication was
terminated due to some "event".
Client
/ server message exchanges use an Oracle specific protocol (TTC). ORA-3137 is
reported if something unexpected is seen that does not conform to the expected
protocol. The error can be raised by the client or server side of a connection.
Solution-1:
Disable
bind peeking by setting:
SQL>
alter system set "_optim_peek_user_binds"=false;
Restart
the database and listener services.
This
hidden parameter was introduced since 9i in order to help optimizer makes
better decisions for producing better executions plans. This hidden parameter
lets Optimizer peeks the values of binds at hard parsing phase using them for
looking for the best exec plans based on current stats and values of binds.
When
bind variables are used in a statement, it is assumed that cursor sharing is
intended and that different invocations are supposed to use the same execution
plan.
The
parameter ( "_optim_peek_user_binds") controls this feature's
behavior, default value is TRUE.
Solution-2:
If
error raise continuously, then you can install the following patch:
Patch
Number# 18841764
How to collect ADDM and AWR Report
Below
are the procedure to collect ADDM & AWR report:
1. Collect ADDM report
[oracle@qpaydb
~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@qpaydb
admin]$ pwd
/u01/app/oracle/product/11.2.0.4.0/dbhome_1/rdbms/admin
[oracle@qpaydb
admin]$ sqlplus / as sysdba
SQL*Plus:
Release 11.2.0.4.0 Production on Sun Jul 2 16:03:56 2017
Copyright
(c) 1982, 2013, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
@addmrpt.sql
2. Collect AWR report
[oracle@qpaydb
admin]$
[oracle@qpaydb
admin]$ pwd
/u01/app/oracle/product/11.2.0.4.0/dbhome_1/rdbms/admin
[oracle@qpaydb
admin]$
[oracle@qpaydb
admin]$ sqlplus / as sysdba
SQL*Plus:
Release 11.2.0.4.0 Production on Sun Jul 2 16:08:19 2017
Copyright
(c) 1982, 2013, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
@awrrpt.sql
Cleaning Disk header or making ASM disk from member to candidate
We have to below operation for disk
cleaning OR making ASM member disk to candidate disk:
#
dd if=/dev/zero of=/dev/oracle/asm_ocr01 bs=1024k count=1000
1000+0
records in
1000+0
records out
#
dd if=/dev/zero of=/dev/oracle/asm_ocr02 bs=1024k count=1000
1000+0
records in
1000+0
records out
#
dd if=/dev/zero of=/dev/oracle/asm_ocr03 bs=1024k count=1000
1000+0
records in
1000+0
records out
#dd
if=/dev/zero of=/dev/oracle/asm_vot01 bs=1024k count=1000
1000+0
records in
1000+0
records out
#
dd if=/dev/zero of=/dev/oracle/asm_vot02 bs=1024k count=1000
1000+0
records in
1000+0
records out
#
dd if=/dev/zero of=/dev/oracle/asm_vot03 bs=1024k count=1000
1000+0
records in
1000+0
records out
#
#
dd if=/dev/zero of=/dev/oracle/asm_data01 bs=1024k count=1000
1000+0
records in
1000+0
records out
#
dd if=/dev/zero of=/dev/oracle/asm_data02 bs=1024k count=1000
1000+0
records in
1000+0
records out
#
dd if=/dev/zero of=/dev/oracle/asm_data03 bs=1024k count=1000
1000+0
records in
1000+0
records out
#
dd if=/dev/zero of=/dev/oracle/asm_fra01 bs=1024k count=1000
1000+0
records in
1000+0
records out
#
dd if=/dev/zero of=/dev/oracle/asm_control1 bs=1024k count=1000
1000+0
records in
1000+0
records out
#
dd if=/dev/zero of=/dev/oracle/asm_control2 bs=1024k count=1000
1000+0
records in
1000+0
records out
#
dd if=/dev/zero of=/dev/oracle/asm_redo1 bs=1024k count=1000
1000+0
records in
1000+0
records out
#
#
dd if=/dev/zero of=/dev/oracle/asm_redo2 bs=1024k count=1000
1000+0
records in
1000+0
records out
Subscribe to:
Posts (Atom)