SQL> set pages 999 lines 999;
col instance_name for a10;
col host_name for a20;
col startup_time for a30;
set colsep |
set underline =
select instance_name,host_name,name,open_mode,log_mode,status,to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') startup_time,version
from v$database, v$instance;SQL> SQL> SQL> SQL> SQL> SQL> 2
INSTANCE_N|HOST_NAME |NAME |OPEN_MODE |LOG_MODE |STATUS |STARTUP_TIME |VERSION
==========|====================|=========|====================|============|============|==============================|=================
bihar |oel7.malam.com |BIHAR |READ WRITE |ARCHIVELOG |OPEN |20-mar-2022 22:26:58 |12.1.0.2.0
SQL>
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area|1593835520|bytes
Fixed Size | 2924880|bytes
Variable Size | 503320240|bytes
Database Buffers |1073741824|bytes
Redo Buffers | 13848576|bytes
Database mounted.
SQL>
SQL>
SQL>
SQL> set pages 999 lines 999;
col instance_name for a10;
col host_name for a20;
col startup_time for a30;
set colsep |
set underline =
select instance_name,host_name,name,open_mode,log_mode,status,to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') startup_time,version
from v$database, v$instance;SQL> SQL> SQL> SQL> SQL> SQL> 2
INSTANCE_N|HOST_NAME |NAME |OPEN_MODE |LOG_MODE |STATUS |STARTUP_TIME |VERSION
==========|====================|=========|====================|============|============|==============================|=================
bihar |oel7.malam.com |BIHAR |MOUNTED |ARCHIVELOG |MOUNTED |20-mar-2022 23:11:57 |12.1.0.2.0
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@oel7 u01]$
[oracle@oel7 u01]$
[oracle@oel7 u01]$ env | grep ORACLE
ORACLE_SID=bihar
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0
[oracle@oel7 u01]$
[oracle@oel7 u01]$
[oracle@oel7 u01]$ nid target=sys dbname=patna
DBNEWID: Release 12.1.0.2.0 - Production on Sun Mar 20 23:12:57 2022
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database BIHAR (DBID=2907458956)
Connected to server version 12.1.0
Control Files in database:
/u01/app/oracle/oradata/BIHAR/controlfile/o1_mf_jt3q97ly_.ctl
/u01/app/oracle/fast_recovery_area/BIHAR/controlfile/o1_mf_jt3q97xq_.ctl
Change database ID and database name BIHAR to PATNA? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2907458956 to 1164850626
Changing database name from BIHAR to PATNA
Control File /u01/app/oracle/oradata/BIHAR/controlfile/o1_mf_jt3q97ly_.ctl - modified
Control File /u01/app/oracle/fast_recovery_area/BIHAR/controlfile/o1_mf_jt3q97xq_.ctl - modified
Datafile /u01/app/oracle/oradata/BIHAR/datafile/o1_mf_system_jt3q4j9v_.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/BIHAR/datafile/o1_mf_sysaux_jt3q2h3d_.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/BIHAR/datafile/o1_mf_undotbs1_jt3q797l_.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/BIHAR/datafile/o1_mf_system_jt3q9t24_.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/BIHAR/datafile/o1_mf_users_jt3q75kc_.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/BIHAR/datafile/o1_mf_sysaux_jt3q9t13_.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/BIHAR/D1C14FD96D5329A2E0550A0027A09EBC/datafile/o1_mf_system_jt3r6bcc_.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/BIHAR/D1C14FD96D5329A2E0550A0027A09EBC/datafile/o1_mf_sysaux_jt3r6bcp_.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/BIHAR/D1C14FD96D5329A2E0550A0027A09EBC/datafile/o1_mf_users_jt3r7wvk_.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/BIHAR/datafile/cdb_data_files_o1.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/BIHAR/D1C14FD96D5329A2E0550A0027A09EBC/datafile/pdb_data_files_o1.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/BIHAR/datafile/o1_mf_temp_jt3q9nq4_.tm - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/BIHAR/datafile/pdbseed_temp012021-11-27_12-55-37-PM.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/BIHAR/D1C14FD96D5329A2E0550A0027A09EBC/datafile/o1_mf_temp_jt3r6bcr_.db - dbid changed, wrote new name
Control File /u01/app/oracle/oradata/BIHAR/controlfile/o1_mf_jt3q97ly_.ctl - dbid changed, wrote new name
Control File /u01/app/oracle/fast_recovery_area/BIHAR/controlfile/o1_mf_jt3q97xq_.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to PATNA.
Modify parameter file and generate a new password file before restarting.
Database ID for database PATNA changed to 1164850626.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
Make entry in /etc/oratab for patna
create pfile for patna db at /u01/app/oracle/product/12.1.0/dbs/initpatna.ora
cat $ORACLE_HOME/dbs/initpatna.ora
*.audit_file_dest='/u01/app/oracle/admin/patna/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/BIHAR/controlfile/o1_mf_jt3q97ly_.ctl','/u01/app/oracle/fast_recovery_area/BIHAR/controlfile/o1_mf_jt3q97xq_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_name='patna'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=patnaXDB)'
*.enable_pluggable_database=true
*.open_cursors=300
*.pga_aggregate_target=502m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1506m
*.undo_tablespace='UNDOTBS1'
[oracle@oel7 u01]$
[oracle@oel7 u01]$ . oraenv
ORACLE_SID = [bihar] ? patna
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel7 u01]$
[oracle@oel7 u01]$ env | grep ORACLE
ORACLE_SID=patna
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0
[oracle@oel7 u01]$
[oracle@oel7 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 20 23:20:12 2022
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initpatna.ora';
ORACLE instance started.
Total System Global Area 1593835520 bytes
Fixed Size 2924880 bytes
Variable Size 503320240 bytes
Database Buffers 1073741824 bytes
Redo Buffers 13848576 bytes
SQL>
SQL>
SQL> create spfile from pfile='$ORACLE_HOME/dbs/initpatna.ora';
File created.
SQL> shu immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
SQL>
SQL> startup;
ORACLE instance started.
Total System Global Area 1593835520 bytes
Fixed Size 2924880 bytes
Variable Size 503320240 bytes
Database Buffers 1073741824 bytes
Redo Buffers 13848576 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> set pages 999 lines 999;
col instance_name for a10;
col host_name for a20;
col startup_time for a30;
set colsep |
set underline =
select instance_name,host_name,name,open_mode,log_mode,status,to_char(startup_time,'dd-mon-yyyy hh24:mi:ss') startup_time,version
from v$database, v$instance;SQL> SQL> SQL> SQL> SQL> SQL> 2
INSTANCE_N|HOST_NAME |NAME |OPEN_MODE |LOG_MODE |STATUS |STARTUP_TIME |VERSION
==========|====================|=========|====================|============|============|==============================|=================
patna |oel7.malam.com |PATNA |READ WRITE |ARCHIVELOG |OPEN |20-mar-2022 23:24:13 |12.1.0.2.0
SQL>
Sunday, 20 March 2022
Rename Oracle database using NID
How to add new and drop old online redo log;
COL INSTANCE_NAME FOR A10;
COL HOST_NAME FOR A20;
COL STARTUP_TIME FOR A30;
SET COLSEP |
SET UNDERLINE =
SELECT INSTANCE_NAME,HOST_NAME,NAME,OPEN_MODE,LOG_MODE,STATUS,TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') STARTUP_TIME,VERSION
FROM V$DATABASE, V$INSTANCE;
INSTANCE_N|HOST_NAME |NAME |OPEN_MODE |LOG_MODE |STATUS |STARTUP_TIME |VERSION
==========|===============|=========|============|============|========|=====================|==========
bihar |oel7.malam.com |BIHAR |READ WRITE |ARCHIVELOG |OPEN |20-MAR-2022 22:26:58 |12.1.0.2.0
SET PAGES 999 LINES 999;
COL MEMBER FOR A80;
SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------------------------------------- --- ----------
3 ONLINE /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_3_jt3q9gpd_.log NO 0
3 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_3_jt3q9gsn_.log YES 0
2 ONLINE /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_2_jt3q9dvr_.log NO 0
2 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_2_jt3q9dys_.log YES 0
1 ONLINE /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_1_jt3q9blf_.log NO 0
1 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_1_jt3q9btr_.log YES 0
6 rows selected.
SQL> select group#, sequence#, bytes, members, status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 25 52428800 2 CURRENT
2 23 52428800 2 INACTIVE
3 24 52428800 2 INACTIVE
SQL> alter database add logfile group 4
('/u01/app/oracle/oradata/BIHAR/redo_bihar_grp4_a.redo',
'/u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp4_b.redo') size 10M; 2 3
Database altered.
SQL> alter database add logfile group 5
('/u01/app/oracle/oradata/BIHAR/redo_bihar_grp5_a.redo',
'/u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp5_b.redo') size 10M; 2 3
Database altered.
SQL> alter database add logfile group 6
('/u01/app/oracle/oradata/BIHAR/redo_bihar_grp6_a.redo',
'/u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp6_b.redo') size 10M; 2 3
Database altered.
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------------------------------------- --- ----------
3 ONLINE /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_3_jt3q9gpd_.log NO 0
3 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_3_jt3q9gsn_.log YES 0
2 ONLINE /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_2_jt3q9dvr_.log NO 0
2 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_2_jt3q9dys_.log YES 0
1 ONLINE /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_1_jt3q9blf_.log NO 0
1 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_1_jt3q9btr_.log YES 0
4 ONLINE /u01/app/oracle/oradata/BIHAR/redo_bihar_grp4_a.redo NO 0
4 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp4_b.redo NO 0
5 ONLINE /u01/app/oracle/oradata/BIHAR/redo_bihar_grp5_a.redo NO 0
5 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp5_b.redo NO 0
6 ONLINE /u01/app/oracle/oradata/BIHAR/redo_bihar_grp6_a.redo NO 0
6 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp6_b.redo NO 0
12 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select group#, sequence#, bytes, members, status,ARCHIVED from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 31 52428800 2 INACTIVE YES
2 29 52428800 2 INACTIVE YES
3 30 52428800 2 INACTIVE YES
4 32 10485760 2 CURRENT NO
5 27 10485760 2 INACTIVE YES
6 28 10485760 2 INACTIVE YES
6 rows selected.
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
SQL> select group#, sequence#, bytes, members, status,ARCHIVED from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
4 32 10485760 2 CURRENT NO
5 27 10485760 2 INACTIVE YES
6 28 10485760 2 INACTIVE YES
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------------------------------------- --- ----------
4 ONLINE /u01/app/oracle/oradata/BIHAR/redo_bihar_grp4_a.redo NO 0
4 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp4_b.redo NO 0
5 ONLINE /u01/app/oracle/oradata/BIHAR/redo_bihar_grp5_a.redo NO 0
5 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp5_b.redo NO 0
6 ONLINE /u01/app/oracle/oradata/BIHAR/redo_bihar_grp6_a.redo NO 0
6 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp6_b.redo NO 0
6 rows selected.
Sunday, 29 August 2021
Oracle RMAN Full and Archive backup.
For RMAN Archive Backup
SET PAGES 999 LINES 999
COL STATUS FORMAT A9
COL "HRS" FORMAT 999.99
COL "MIN" FORMAT 99999.99
COL HOST_NAME FOR A10;
SELECT NAME,INSTANCE_NAME,HOST_NAME,SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'MM/DD/YY HH24:MI') "START_TIME",
TO_CHAR(END_TIME,'MM/DD/YY HH24:MI') "END_TIME",
ELAPSED_SECONDS/3600 "HRS",
ELAPSED_SECONDS/60 "MIN"
FROM V$RMAN_BACKUP_JOB_DETAILS,
(SELECT NAME FROM V$DATABASE),
(SELECT INSTANCE_NAME,SUBSTR(HOST_NAME,1,10) "HOST_NAME" FROM GV$INSTANCE)
WHERE INPUT_TYPE='ARCHIVELOG'
ORDER BY SESSION_KEY;
For RMAN Full backup
SET PAGES 999 LINES 999
COL STATUS FORMAT A9
COL "HRS" FORMAT 999.99
COL "MIN" FORMAT 99999.99
COL HOST_NAME FOR A10;
SELECT NAME,INSTANCE_NAME,HOST_NAME,SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'MM/DD/YY HH24:MI') "START_TIME",
TO_CHAR(END_TIME,'MM/DD/YY HH24:MI') "END_TIME",
ELAPSED_SECONDS/3600 "HRS",
ELAPSED_SECONDS/60 "MIN"
FROM V$RMAN_BACKUP_JOB_DETAILS,
(SELECT NAME FROM V$DATABASE),
(SELECT INSTANCE_NAME,SUBSTR(HOST_NAME,1,10) "HOST_NAME" FROM GV$INSTANCE)
WHERE INPUT_TYPE NOT IN ('ARCHIVELOG')
ORDER BY SESSION_KEY;
Oracle RMAN backup expected time to complete
COL DBSIZE_MBYTES FOR 99,999,990.00 JUSTIFY RIGHT HEAD "DBSIZE_MB"
COL INPUT_MBYTES FOR 99,999,990.00 JUSTIFY RIGHT HEAD "READ_MB"
COL OUTPUT_MBYTES FOR 99,999,990.00 JUSTIFY RIGHT HEAD "WRITTEN_MB"
COL OUTPUT_DEVICE_TYPE FOR A10 JUSTIFY LEFT HEAD "DEVICE"
COL COMPLETE FOR 990.00 JUSTIFY RIGHT HEAD "COMPLETE %""
COL COMPRESSION FOR 990.00 JUSTIFY RIGHT HEAD "COMPRESS|% ORIG"
COL EST_COMPLETE FOR A20 HEAD "ESTIMATED COMPLETION"
COL RECID FOR 9999999 HEAD "ID"
COL INSTANCE_NAME FOR A10;
COL HOST_NAME FOR A10;
SELECT RECID
, OUTPUT_DEVICE_TYPE
, DBSIZE_MBYTES
, INPUT_BYTES/1024/1024 INPUT_MBYTES
, OUTPUT_BYTES/1024/1024 OUTPUT_MBYTES
, (OUTPUT_BYTES/INPUT_BYTES*100) COMPRESSION
, (MBYTES_PROCESSED/DBSIZE_MBYTES*100) COMPLETE
, TO_CHAR(START_TIME + (SYSDATE-START_TIME)/(MBYTES_PROCESSED/DBSIZE_MBYTES),'DD-MON-YYYY HH24:MI:SS') EST_COMPLETE
FROM V$RMAN_STATUS RS
, (SELECT SUM(BYTES)/1024/1024 DBSIZE_MBYTES FROM V$DATAFILE)
, (SELECT NAME FROM V$DATABASE)
, (SELECT INSTANCE_NAME,SUBSTR(HOST_NAME,1,10) FROM V$INSTANCE)
WHERE STATUS='RUNNING'
AND OUTPUT_DEVICE_TYPE IS NOT NULL;
DDL of User in ORACLE
#!/bin/ksh
# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep ora_pmon_$1|grep -v grep | awk '{print $8}' | wc -l)
# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
then
echo "No Database Running !"
exit
else
echo "###############################################################"
echo " Connecting $($1)..............."
echo "###############################################################"
fi
export ORAENV_ASK=NO
export ORACLE_SID=$1
. /usr/local/bin/oraenv
echo $ORACLE_SID
echo $ORACLE_HOME
DBNAME=$ORACLE_SID
export BACKUP_LOG_PATH=`date +%y%m%d%H%M%S`
listofuser=/u01/ListOfUsers_${DBNAME}_${BACKUP_LOG_PATH}.log
userddl=/u01/allusersddl_${DBNAME}_${BACKUP_LOG_PATH}.sql
######## Start Taking list of users ##########################
${ORACLE_HOME}/bin/sqlplus -S '/ as sysdba' << EOF
PROMPT
spool ${listofuser}
set pages 0
set echo off heading off feedback off
select username from dba_users where username not in ('SYS','SYSTEM','SYSBACKUP','SYSKM','OUTLN','SYSDG','SYSRAC','SYS\$UMF','DBSNMP','APPQOSSYS','GSMADMIN_INTERNAL','XDB','DBSFWUSER','GGSYS','ANONYMOUS',
'GSMCATUSER','OJVMSYS','AUDSYS','DIP','ORACLE_OCM','GSMUSER','REMOTE_SCHEDULER_AGENT','XS\$NULL') order by 1 asc;
spool off
EOF
################ END ###################################
########### Generating DDL of users #####################
for USERNAME in `cat ${listofuser}`
do
export USERNAME
${ORACLE_HOME}/bin/sqlplus -S '/ as sysdba' << EOF
spool ${userddl} APPEND
set termout off
set linesize 190
set pages 50000
set feedback off
set trim on
set echo off
col USERNAME for a30
col account_status for a23
col profile for a10;
set pages 0
set echo off heading off feedback off
SELECT 'CREATE USER ' || u.username ||' IDENTIFIED ' ||' BY VALUES '''|| c.spare4||''' DEFAULT TABLESPACE ' || u.default_tablespace
||' TEMPORARY TABLESPACE ' || u.temporary_tablespace ||' PROFILE ' || u.profile || case when account_status= 'OPEN' then ';' else ' Account LOCK;' end
FROM dba_users u,user$ c where u.username=c.name and u.username=upper('$USERNAME')
UNION
select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE|| case when ADMIN_OPTION='YES' then ' WITH ADMIN OPTION;' else ';' end
from dba_role_privs where grantee= upper('$USERNAME')
UNION
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE|| case when ADMIN_OPTION='YES' then ' WITH ADMIN OPTION;' else ';' end
from dba_sys_privs where grantee= upper('$USERNAME')
UNION
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||case when GRANTABLE='YES' then ' WITH GRANT OPTION;' else ';' end
from DBA_TAB_PRIVS where GRANTEE=upper('$USERNAME');
spool off
EOF
done
echo "sql script save as : ${userddl}]"
[oracle@oel7 u01]$ ksh -x ddl.sh abc
Thursday, 20 June 2019
RMAN Backup Shell Script for Oracle
vi rmanbackup.sh
#!/bin/bash
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
BACKUP_LOG_PATH=/home/oracle/myproject
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=ORCL
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/backup_db.log
$ORACLE_HOME/bin/rman msglog=${LOG_FILE} << EOF
connect target /
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
backup database format '/u02/oracle/backup/ORCL/database_%d_%u_%s';
release channel t1;
release channel t2;
release channel t3;
sql 'alter system archive log current';
allocate channel a1 type disk;
allocate channel a2 type disk;
allocate channel a3 type disk;
backup archivelog all format '/u02/oracle/backup/ORCL/arch_%d_%u_%s';
release channel a1;
release channel a2;
release channel a3;
allocate channel c1 type disk;
backup current controlfile for standby format '/u02/oracle/backup/ORCL/Control_%d_%u_%s';
release channel c1;
}
exit;
EOF
Wednesday, 19 June 2019
How to check last modified table in Oracle 10g and Above
SQL> conn
Enter user-name: scott
Enter password:
Connected.
SQL> create table emp_new as select * from emp where 1=1;
Table created.
SQL> insert into emp_new values (2790,'alam','SMITH',7698,sysdate,5600,340,30);
1 row created.
SQL> commit;
Commit complete.
SQL> conn
Enter user-name: / as sysdba
Connected.
SQL>exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL>
set linesize 500;
select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES,TRUNCATED,
to_char(TIMESTAMP,'YYYY-MON-DD HH24:MI:SS')
from all_tab_modifications
where table_owner='SCOTT' and
EXTRACT(YEAR FROM TO_DATE(TIMESTAMP, 'DD-MON-RR')) > 2010
order by 6;SQL> SQL> SQL> SQL> 2 3 4 5 6
TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES TRU TO_CHAR(TIMESTAMP,'Y
------------------------------ ------------------------------ ---------- ---------- ---------- --- --------------------
SCOTT EMP_NEW 1 0 0 NO 2019-JUN-20 11:55:02
SQL>