RMAN-03015: error occurred in stored script Memory Script RMAN-06136: Oracle error from auxiliary database: ORA-17629: Cannot connect to the remote database server ORA-17627: ORA-01017: invalid username/password; logon deniedRMAN> duplicate target database to 'orcld' from active database nofilenamecheck; Starting Duplicate Db at 20-AUG-23 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=38 device type=DISK 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 6710885896 bytes Fixed Size 9149960 bytes Variable Size 1174405120 bytes Database Buffers 5519704064 bytes Redo Buffers 7626752 bytes contents of Memory Script: { sql clone "alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set db_unique_name = ''orcld'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone from service 'orcl3' primary controlfile; alter clone database mount; } executing Memory Script sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set db_unique_name = ''orcld'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area 6710885896 bytes Fixed Size 9149960 bytes Variable Size 1174405120 bytes Database Buffers 5519704064 bytes Redo Buffers 7626752 bytes Starting restore at 20-AUG-23 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=35 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 08/20/2023 19:58:01 RMAN-05501: aborting duplication of target database RMAN-03015: error occurred in stored script Memory Script RMAN-06136: Oracle error from auxiliary database: ORA-17629: Cannot connect to the remote database server ORA-17627: ORA-01017: invalid username/password; logon denied ORA-17629: Cannot connect to the remote database server**************************************************************[oracle@oracentd19c dbs]$ ps -ef|grep -i smon oracle 19234 1 0 20:21 ? 00:00:00 ora_smon_orcldcp orapworcl orapworcld
Sunday, 20 August 2023
RMAN-06136: Oracle error from auxiliary database: ORA-17629: Cannot connect to the remote database server ORA-17627: ORA-01017: invalid username/password; logon denied
Monday, 14 August 2023
Create Pluggable Database(PDB) in Oracle 12c
Create Pluggable Database(PDB) in Oracle 12c..
Create Pluggable Database(PDB) in Oracle 12c
if required!!!
alter system set "_exclude_seed_cdb_view"=FALSE scope=both;
System altered.
select distinct substr(name, 1, instr(name, '/',-1)) PATH
from (
select name from v$datafile
union all
select NAME from v$controlfile
union all
select MEMBER name from v$logfile
union all
select name from v$tempfile
) order by 1;
take seed database file path
set linesize 400
col datafile_name for a90
col container for a20
select hxfil file#, d.name datafile_name , c.name container, fhsta fh_status, fhscn fh_scn, fhrba_seq fh_seq , fhafs fh_abs from x$kcvfh x, v$datafile d, v$containers c
where d.file# = x.hxfil
and d.con_id = c.con_id
and c.name='PDB$SEED'
order by c.name,fhscn;
FILE# DATAFILE_NAME CONTAINER FH_STATUS FH_SCN FH_SEQ FH_ABS
---------- ------------------------------------------------------------------------------------------ -------------------- ---------- -------------------- ---------- --------------------
14 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/system.359.1133670139 PDB$SEED 40960 52755005 2043 0
15 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/sysaux.1322.1133670139 PDB$SEED 32768 52755005 2043 0
20 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/pdb9_undotbs.4153.1133670139 PDB$SEED 32768 52755005 2043 0
17 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/users.1419.1133670139 PDB$SEED 32768 52755005 2043 0
16 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/undotbs1.1320.1133670139 PDB$SEED 32768 52755005 2043 0
for temp file alter session set container=PDB$SEED ;
select NAME from V$TEMPFILE;
NAME
------------------------------------------------------------------------------------------
+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/TEMPFILE/temp.4195.1133671127
ww
create pluggable database anujy admin user vihaan identified by vihaan123
default tablespace anujy datafile '+DATA' size 250m autoextend on
file_name_convert=('+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/','+DATA','+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/TEMPFILE/temp.4195.1133671127','+DATA');
Pluggable database created.
create pluggable database anujz admin user vihaan identified by vihaan123
default tablespace anujy datafile '+DATA' size 250m autoextend on
file_name_convert=('+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/','+DATA','+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/TEMPFILE/temp.4195.1133671127','+DATA');
Pluggable database created.
Saturday, 12 August 2023
TOP SQL reporting
TOP SQL reporting
TOP SQL reporting
---- mon_topsql.sql
https://github.com/vishaldesai/Oracle_Scripts/blob/master/Exadata/mon_topsql.sql
-- Author: Tanel Poder ( http://blog.tanelpoder.com | tanel@tanelpoder.com )
SET LINES 999 PAGES 5000 TRIMSPOOL ON TRIMOUT ON TAB OFF
COL pct FOR A10 JUST RIGHT
COL cpu_pct FOR 999.9
COL io_pct FOR 999.9
col OWNER for a20
col OBJECT_NAME for a20
col PROCEDURE_NAME for a20
BREAK ON day SKIP 1
DEF days=1
PROMPT Displaying daily top SQL for last &days days...
WITH ash AS (
SELECT
day
, owner
, object_name
, procedure_name
, sql_id
, sql_plan_hash_value
, total_seconds
, io_seconds
, cpu_seconds
, LPAD(TRIM(TO_CHAR(RATIO_TO_REPORT(total_seconds) OVER (PARTITION BY day) * 100, '999.9'))||'%', 10) pct
, RATIO_TO_REPORT(total_seconds) OVER (PARTITION BY day) * 100 pct_num
FROM (
SELECT
TO_CHAR(sample_time, 'YYYY-MM-DD') day
, sql_id
, sql_plan_hash_value
, p.owner
, p.object_name
, p.procedure_name
, SUM(10) total_seconds
, SUM(CASE WHEN wait_class = 'User I/O' THEN 10 ELSE 0 END) io_seconds
, SUM(CASE WHEN wait_class IS NULL THEN 10 ELSE 0 END) cpu_seconds
FROM
dba_hist_active_sess_history a
, dba_procedures p
WHERE
a.plsql_entry_object_id = p.object_id (+)
AND a.plsql_entry_subprogram_id = p.subprogram_id (+)
AND sample_time > SYSDATE - &days
AND session_type != 'BACKGROUND' -- ignore for now
GROUP BY
sql_id
, sql_plan_hash_value
, p.owner
, p.object_name
, p.procedure_name
, TO_CHAR(sample_time, 'YYYY-MM-DD')
)
)
, sqlstat AS (
SELECT /*+ MATERIALIZE */
TO_CHAR(begin_interval_time, 'YYYY-MM-DD') day
, sql_id
, plan_hash_value
, SUM(executions_delta) executions
, SUM(rows_processed_delta) rows_processed
, SUM(disk_reads_delta) blocks_read
, SUM(disk_reads_delta)*8/1024 mb_read
, SUM(buffer_gets_delta) buffer_gets
, SUM(iowait_delta)/1000000 awr_iowait_seconds
, SUM(cpu_time_delta)/1000000 awr_cpu_seconds
, SUM(elapsed_time_delta)/1000000 awr_elapsed_seconds
FROM
dba_hist_snapshot
NATURAL JOIN
dba_hist_sqlstat
WHERE
begin_interval_time > SYSDATE - &days
GROUP BY
TO_CHAR(begin_interval_time, 'YYYY-MM-DD')
, sql_id
, plan_hash_value
)
SELECT /*+ MONITOR */
day
, pct
, owner
, object_name
, procedure_name
, sql_id
, sql_plan_hash_value plan_hash
, ROUND(total_seconds / 3600,1) total_hours
, total_seconds
, executions
, ROUND(total_seconds / NULLIF(executions,0),2) seconds_per_exec
, io_pct
, cpu_pct
, mb_read
, ROUND(mb_read / NULLIF(executions,0),2) mb_per_exec
, buffer_gets
, ROUND(buffer_gets / NULLIF(executions,0),2) bufget_per_exec
, CASE WHEN sql_id IS NOT NULL THEN
'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('''||sql_id||''','||CASE WHEN sql_plan_hash_value = 0 THEN 'NULL' ELSE TO_CHAR(sql_plan_hash_value) END||', format=>''ADVANCED''));'
END extract_plan_from_awr
FROM (
SELECT
day
, pct
, owner
, object_name
, procedure_name
, sql_id
, sql_plan_hash_value
, total_seconds
, io_seconds/total_seconds*100 io_pct
, cpu_seconds/total_seconds*100 cpu_pct
, (SELECT executions FROM sqlstat s WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) executions
, (SELECT mb_read FROM sqlstat s WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) mb_read
, (SELECT buffer_gets FROM sqlstat s WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) buffer_gets
FROM
ash
WHERE
ash.pct_num >= 1
)
ORDER BY
day DESC
, total_seconds DESC
/
DAY PCT OWNER OBJECT_NAME PROCEDURE_NAME SQL_ID PLAN_HASH TOTAL_HOURS TOTAL_SECONDS EXECUTIONS SECONDS_PER_EXEC IO_PCT CPU_PCT MB_READ MB_PER_EXEC BUFFER_GETS BUFGET_PER_EXEC EXTRACT_PLAN_FROM_AWR
---------- ---------- -------------------- -------------------- -------------------- ------------- ---------- ----------- ------------- ---------- ---------------- ------ ------- ---------- ----------- ----------- --------------- --------------------------------------------------------------------------------------------------------------------------
2023-08-12 78.6% ctyc1af1abg5u 200298931 91 327510 .0 100.0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));
4.4% 0 5.1 18340 22.3 72.7
4.2% 3xjw1ncw5vh27 2487977020 4.9 17500 88.3 11.7 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('3xjw1ncw5vh27',2487977020, format=>'ADVANCED'));
4.2% c7ngymcfjtb1c 1658157256 4.8 17450 43.8 56.2 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('c7ngymcfjtb1c',1658157256, format=>'ADVANCED'));
2023-08-11 55.1% ctyc1af1abg5u 200298931 55.9 201130 .0 100.0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));
8.7% 1zuyhdtsmmx5s 3008400300 8.8 31680
define sql_id='ctyc1af1abg5u'
SET TERMOUT OFF pagesize 5000 tab off verify off linesize 999 trimspool on trimout on null ""
SET TERMOUT ON
COL exec_per_sec FOR 99999990
COL ela_ms_per_sec FOR 99999990
COL rows_per_sec FOR 99999990
COL lios_per_sec FOR 99999990
COL blkrd_per_sec FOR 99999990
COL cpu_ms_per_sec FOR 99999990
COL iow_ms_per_sec FOR 99999990
COL clw_ms_per_sec FOR 99999990
COL apw_ms_per_sec FOR 99999990
COL ccw_ms_per_sec FOR 99999990
SELECT
CAST(begin_interval_time AS DATE) begin_interval_time
,st.con_id
, sql_id
, plan_hash_value
, ROUND(SUM(executions_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) exec_per_sec
, ROUND(SUM(elapsed_time_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ela_ms_per_sec
, ROUND(SUM(rows_processed_delta) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) rows_per_sec
, ROUND(SUM(buffer_gets_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) lios_per_sec
, ROUND(SUM(disk_reads_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) blkrd_per_sec
, ROUND(SUM(cpu_time_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) cpu_ms_per_sec
, ROUND(SUM(iowait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) iow_ms_per_sec
, ROUND(SUM(clwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) clw_ms_per_sec
, ROUND(SUM(apwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) apw_ms_per_sec
, ROUND(SUM(ccwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ccw_ms_per_sec
FROM
dba_hist_snapshot sn
, dba_hist_sqlstat st
WHERE
sn.snap_id = st.snap_id
AND sn.dbid = st.dbid
AND sn.instance_number = st.instance_number
AND sql_id = '&sql_id'
--AND plan_hash_value LIKE '2'
--AND begin_interval_time >= 3
--AND end_interval_time <= 4
-- AND begin_interval_time > sysdate -1
GROUP BY
CAST(begin_interval_time AS DATE)
,st.con_id
, CAST(end_interval_time AS DATE)
, sql_id
, plan_hash_value
ORDER BY
begin_interval_time
, sql_id
, plan_hash_value
/
SET LINESIZE 200 PAGESIZE 200 TRIMSPOOL ON
COLUMN username FORMAT a20
COLUMN total_WaitTime FORMAT 999,999.99 HEADING "WAIT_SEC"
COLUMN comment FORMAT a30
COLUMN inst_id FORMAT 9999
COLUMN sid FORMAT 999999
col kill for a17
col EVENT for a20
SELECT t.*,
CASE
WHEN ROWNUM <= 5 THEN 'Highest wait: ' || t.username || ' with ' || t.total_WaitTime || ' seconds'
ELSE 'Waits by user last 15 minutes'
END AS "comment"
FROM (
SELECT
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
--s.inst_id,
-- s.sid,
s.con_id,
s.username,
SUM(ash.wait_time + ash.time_waited) / 1000000 AS total_WaitTime,
s.sql_id,
s.event
FROM gv$active_session_history ash
JOIN gv$session s ON ash.session_id = s.sid AND ash.inst_id = s.inst_id
WHERE 1=1
-- ash.sample_time > (SYSDATE - 1/96) -- Last 15 minutes
and ash.sample_time > SYSDATE - INTERVAL '15' MINUTE
AND s.username IS NOT NULL
GROUP BY ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''',s.con_id, s.username,s.sql_id,s.event
ORDER BY 4 DESC
) t
WHERE ROWNUM < 20;
from https://www.pythian.com/blog/recent-spike-report-from-vactive_session_history-ash
AVG — the average “load” (active sessions) over a 5-minute interval. This should help you spot a problem when you scroll through the results.
TOT — total “load” (active sessions) for that sample time. RAC users: each RAC node will have its own sample time, within 1 second of each other, but not exactly spot-on. So, even if you have sessions waiting on the same event, they will not be grouped together. I kind of like it this way, for now.
SAMPLE_TIME — self-explanatory
CNT — the number of active sessions waiting on the same event and query
EVENT — the event been waited on
SQL_TEXT — self-explanatory, except when empty which means either not found in shared pool or not available in ASH
SQL_ID — if you need to find the SQL
CHD — the child number being executed
COLUMN event FORMAT a25 TRUNCATED
COLUMN "SUBSTR(SQ.SQL_TEXT,1)" FORMAT a40 HEADING "SQL_TEXT" TRUNCATED
col SAMPLE_TIME for a25
-- Metric Columns
COLUMN "sec p" FORMAT a15
COLUMN "disk p" FORMAT 999,999
COLUMN "gets p" FORMAT 9,999,999
COLUMN "rows p" FORMAT 999,999
COLUMN "cpu p" FORMAT 99.999
COLUMN exec FORMAT 999,999,999
COLUMN open FORMAT 999
COLUMN e FORMAT 999
col SQL_TEXT for a50
set linesize 300 pagesize 300
SELECT
ROUND(AVG(MAX(cnt_tot)) OVER (ORDER BY sample_time RANGE BETWEEN INTERVAL '5' MINUTE PRECEDING AND CURRENT ROW)) AS avg,
MAX(cnt_tot) AS tot,
sample_time,
MAX(cnt) AS cnt,
event,
SUBSTR(sq.sql_text, 1, 100) as sql_text, -- Truncated for display
ash.sql_id,
ash.sql_child_number AS chd,
TO_CHAR(ROUND(SUM(elapsed_time) / NULLIF(SUM(executions), 0) / 1000000, 6), '9,990.999999') AS "sec p",
ROUND(SUM(disk_reads) / NULLIF(SUM(executions), 0), 0) AS "disk p",
ROUND(SUM(buffer_gets) / NULLIF(SUM(executions), 0), 0) AS "gets p",
ROUND(SUM(rows_processed) / NULLIF(SUM(executions), 0), 0) AS "rows p",
ROUND(SUM(cpu_time) / 1000000 / NULLIF(SUM(executions), 0), 3) AS "cpu p",
SUM(executions) AS exec,
SUM(users_opening) AS open,
SUM(users_executing) AS e
FROM (
SELECT SUM(COUNT(*)) OVER (PARTITION BY sample_time) AS cnt_tot,
COUNT(*) AS cnt,
sample_time,
event,
sql_id,
sql_child_number
FROM gv$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '1' HOUR
GROUP BY event, sql_id, sql_child_number, sample_time
HAVING COUNT(*) >= 10
) ash
LEFT JOIN gv$sql sq ON ash.sql_id = sq.sql_id
AND ash.sql_child_number = sq.child_number
GROUP BY event, sql_text, ash.sql_id, ash.sql_child_number, ash.sample_time
ORDER BY sample_time DESC;
connnect to pdbs
col PLAN_TABLE_OUTPUT for a150
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));
Friday, 11 August 2023
-bash: /bin/rm: Argument list too long
-bash: /bin/rm: Argument list too long
rm *.aud -bash: /bin/rm: Argument list too long
Try below command!!!!
find /u01/app/oracle/admin/TROD/adump -name '*.aud' -mtime +5 -delete
find /u01/app/oracle/admin/TROD/adump -name '*.aud' -delete
find /u01/app/oracle/admin/TROD/adump -name '*.aud' -mtime +5 -exec rm {} \;
Tuesday, 1 August 2023
MAX() KEEP (DENSE_RANK LAST ORDER BY ) OVER() PARTITION BY()
MAX() KEEP (DENSE_RANK LAST ORDER BY ) OVER() PARTITION BY()
KEEP DENSE_RANK
-- Create table CREATE TABLE test (name, sal, deptno) AS SELECT 'a', 1, 1 FROM DUAL UNION ALL SELECT 'b', 1, 1 FROM DUAL UNION ALL SELECT 'c', 1, 1 FROM DUAL UNION ALL SELECT 'd', 2, 1 FROM DUAL UNION ALL SELECT 'e', 3, 1 FROM DUAL UNION ALL SELECT 'f', 3, 1 FROM DUAL UNION ALL SELECT 'g', 4, 2 FROM DUAL UNION ALL SELECT 'h', 4, 2 FROM DUAL UNION ALL SELECT 'i', 5, 2 FROM DUAL UNION ALL SELECT 'j', 5, 2 FROM DUAL; select * from test ; N SAL DEPTNO - ---------- ---------- a 1 1 b 1 1 c 1 1 d 2 1 e 3 1 f 3 1 g 4 2 h 4 2 i 5 2 j 5 2 10 rows selected. SQL !! set linesize 300 col min_name_first_sal for a16 col max_name_first_sal for a16 col min_name_last_sa for a16 col max_name_last_sal for a16 col min_name_last_sal for a16 SELECT DISTINCT deptno, MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS min_sal_first_sal, MAX(sal) KEEP (DENSE_RANK FIRST ORDER BY sal desc ) OVER (PARTITION BY deptno) AS max_sal_first_sal, MIN(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS min_name_first_sal, MAX(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS max_name_first_sal, MIN(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS min_name_last_sal, MAX(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS max_name_last_sal, deptno FROM test; DEPTNO MIN_SAL_FIRST_SAL MAX_SAL_FIRST_SAL MIN_NAME_FIRST_S MAX_NAME_FIRST_S MIN_NAME_LAST_SA MAX_NAME_LAST_SA DEPTNO ---------- ----------------- ----------------- ---------------- ---------------- ---------------- ---------------- ---------- 2 4 5 g h i j 2 1 1 3 a c e f 1 select * from test ; N SAL DEPTNO - ---------- ---------- a 1 1 b 1 1 c 1 1 d 2 1 e 3 1 f 3 1 g 4 2 h 4 2 i 5 2 j 5 2 10 rows selected. col least_salary_person for a18 select o.deptno ,min(o.sal) keep (dense_rank first order by o.sal, o.name) least_salary ,max(o.sal) keep (dense_rank last order by o.sal , o.name ) MAX_salary ,min(o.name) keep (dense_rank first order by o.sal, o.name ) least_salary_person from test o group by o.deptno; DEPTNO LEAST_SALARY MAX_SALARY LEAST_SALARY_PERSO ---------- ------------ ---------- ------------------ 1 1 3 a 2 4 5 g
====
select empno,
deptno,
sal,
dense_rank() over (partition by deptno order by sal) as myrank
from scott.emp;
SQL> SQL> 2 3 4 5
EMPNO DEPTNO SAL MYRANK
---------- ---------- ---------- ----------
7934 10 1300 1
7782 10 2450 2
7839 10 5000 3
7369 20 800 1
7876 20 1100 2
7566 20 2975 3
7788 20 3000 4
7902 20 3000 4
7900 30 950 1
7654 30 1250 2
7521 30 1250 2
7844 30 1500 3
7499 30 1600 4
7698 30 2850 5
14 rows selected.
SQL> SQL>
select * from (select empno,
deptno,
sal,
dense_rank() over (partition by deptno order by sal ) as myrank
from scott.emp
)
where 1=1
and MYRANK=4
;
EMPNO DEPTNO SAL MYRANK
---------- ---------- ---------- ----------
7788 20 3000 4
7902 20 3000 4
7499 30 1600 4
SQL>
Monday, 31 July 2023
How to Install Oracle database 23c rpm on Linux Step by Step?
How to Install Oracle database 23c rpm on Linux Step by Step?
Oracle 23ai downloadhttps://www.oracle.com/database/free/get-started/============================ Check Os !!
cat /etc/os-release NAME="Oracle Linux Server" VERSION="8.8" ID="ol" ID_LIKE="fedora" VARIANT="Server" VARIANT_ID="server" VERSION_ID="8.8" PLATFORM_ID="platform:el8" PRETTY_NAME="Oracle Linux Server 8.8" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:oracle:linux:8:8:server" HOME_URL="https://linux.oracle.com/" BUG_REPORT_URL="https://github.com/oracle/oracle-linux" ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8" ORACLE_BUGZILLA_PRODUCT_VERSION=8.8 ORACLE_SUPPORT_PRODUCT="Oracle Linux" ORACLE_SUPPORT_PRODUCT_VERSION=8.8 ===
Set below value!! for SELinux
cat /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
# SELINUXTYPE= can take one of these three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUX=permissive
SELINUXTYPE=targeted
====
download pre rpm !!!
[root@oracle21 ~]# curl -L -o oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/developer/x86_64/getPackage/oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 30688 100 30688 0 0 134k 0 --:--:-- --:--:-- --:--:-- 134k
dnf -y localinstall oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
dnf -y localinstall oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
Last metadata expiration check: 1:49:55 ago on Mon 31 Jul 2023 12:03:43 PM BST.
Dependencies resolved.
=====================================================================================================================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================================================================================================================
Installing:
oracle-database-preinstall-23c x86_64 1.0-0.5.el8 @commandline 30 k
Installing dependencies:
compat-openssl10 x86_64 1:1.0.2o-4.el8_6 ol8_appstream 1.1 M
libnsl x86_64 2.28-225.0.3.el8 ol8_baseos_latest 108 k
Transaction Summary
=====================================================================================================================================================================================================================
Install 3 Packages
Total size: 1.3 M
Total download size: 1.2 M
Installed size: 3.1 M
Downloading Packages:
(1/2): libnsl-2.28-225.0.3.el8.x86_64.rpm 661 kB/s | 108 kB 00:00
(2/2): compat-openssl10-1.0.2o-4.el8_6.x86_64.rpm 2.7 MB/s | 1.1 MB 00:00
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 2.8 MB/s | 1.2 MB 00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : compat-openssl10-1:1.0.2o-4.el8_6.x86_64 1/3
Running scriptlet: compat-openssl10-1:1.0.2o-4.el8_6.x86_64 1/3
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17-2011.1.2.el8uek.x86_64.conf:6: hwcap directive ignored
Installing : libnsl-2.28-225.0.3.el8.x86_64 2/3
Installing : oracle-database-preinstall-23c-1.0-0.5.el8.x86_64 3/3
Running scriptlet: oracle-database-preinstall-23c-1.0-0.5.el8.x86_64 3/3
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17-2011.1.2.el8uek.x86_64.conf:6: hwcap directive ignored
Verifying : libnsl-2.28-225.0.3.el8.x86_64 1/3
Verifying : compat-openssl10-1:1.0.2o-4.el8_6.x86_64 2/3
Verifying : oracle-database-preinstall-23c-1.0-0.5.el8.x86_64 3/3
Installed:
compat-openssl10-1:1.0.2o-4.el8_6.x86_64 libnsl-2.28-225.0.3.el8.x86_64 oracle-database-preinstall-23c-1.0-0.5.el8.x86_64
Complete!
[root@oracle21 ~]#
Check below rpm
yum install sysstat compat-openssl10 glibc-devel ksh libnsl make
Last metadata expiration check: 1:52:04 ago on Mon 31 Jul 2023 12:03:43 PM BST.
Package sysstat-11.7.3-9.0.1.el8.x86_64 is already installed.
Package compat-openssl10-1:1.0.2o-4.el8_6.x86_64 is already installed.
Package glibc-devel-2.28-225.0.3.el8.x86_64 is already installed.
Package ksh-20120801-257.0.1.el8.x86_64 is already installed.
Package libnsl-2.28-225.0.3.el8.x86_64 is already installed.
Package make-1:4.2.1-11.el8.x86_64 is already installed.
Dependencies resolved.
Nothing to do.
Complete!
[root@oracle21 ~]#
=========================
dnf -y localinstall /root/oracle-database-free-23c-1.0-1.el8.x86_64.rpm
Last metadata expiration check: 1:56:39 ago on Mon 31 Jul 2023 12:03:43 PM BST.
Dependencies resolved.
=====================================================================================================================================================================================================================
Package Architecture Version Repository Size
=====================================================================================================================================================================================================================
Installing:
oracle-database-free-23c x86_64 1.0-1 @commandline 1.6 G
Transaction Summary
=====================================================================================================================================================================================================================
Install 1 Package
Total size: 1.6 G
Installed size: 5.2 G
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Running scriptlet: oracle-database-free-23c-1.0-1.x86_64 1/1
Installing : oracle-database-free-23c-1.0-1.x86_64 1/1
Running scriptlet: oracle-database-free-23c-1.0-1.x86_64 1/1
[INFO] Executing post installation scripts...
[INFO] Oracle home installed successfully and ready to be configured.
To configure Oracle Database Free, optionally modify the parameters in '/etc/sysconfig/oracle-free-23c.conf' and then run '/etc/init.d/oracle-free-23c configure' as root.
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17-2011.1.2.el8uek.x86_64.conf:6: hwcap directive ignored
Verifying : oracle-database-free-23c-1.0-1.x86_64 1/1
Installed:
oracle-database-free-23c-1.0-1.x86_64
Complete!
[root@oracle21 ~]#
[root@oracle21 ~]#
[root@oracle21 ~]# ls -ltr /etc/init.d/oracle-free-23c
-r-xr-xr-x. 1 root root 19880 Mar 26 06:02 /etc/init.d/oracle-free-23c
[root@oracle21 ~]# cat /etc/init.d/oracle-free-23c
[root@oracle21 ~]# ls -ltr /opt/oracle/product/23c/dbhomeFree/
total 72
-rw-r--r--. 1 oracle oinstall 852 Aug 18 2015 env.ora
-rw-r--r--. 1 oracle oinstall 2927 Jul 20 2020 schagent.conf
-rwxr-x---. 1 oracle oinstall 1991 Jan 19 2022 runInstaller
-rw-r--r--. 1 oracle oinstall 878 Jul 15 2022 ss_oracle.sdo.acl
-rwx------. 1 oracle oinstall 519 Mar 26 06:01 root.sh
-rwxrwxr-x. 1 oracle oinstall 5780 Mar 26 06:02 LICENSE
=======create Quick database !!!!
# export DB_PASSWORD=sys
# (echo "${DB_PASSWORD}"; echo "${DB_PASSWORD}";) | /etc/init.d/oracle-free-23c configure
[root@oracle21 ~]# export DB_PASSWORD=sys
[root@oracle21 ~]# (echo "${DB_PASSWORD}"; echo "${DB_PASSWORD}";) | /etc/init.d/oracle-free-23c configure
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Configuring Oracle Listener.
========
[root@oracle21 ~]# ps -ef|grep -i smon
oracle 153252 1 0 14:26 ? 00:00:00 db_smon_FREE
root 153755 150622 0 14:31 pts/2 00:00:00 grep --color=auto -i smon
[root@oracle21 ~]# su - oracle
[oracle@oracle21 ~]$ cat /etc/oratab
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME::
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
FREE:/opt/oracle/product/23c/dbhomeFree:N
[oracle@oracle21 ~]$
[oracle@oracle21 ~]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon Jul 31 14:32:31 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FREEPDB1 READ WRITE NO
SQL>
===
set profile su - oracle [oracle@dbdocs ~]$ vi .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi umask 022 ORACLE_SID=FREE ORACLE_BASE=/opt/oracle ORACLE_HOME=$ORACLE_BASE/product/23c/dbhomeFree LD_LIBRARY_PATH=$ORACLE_HOME/lib TMP=/tmp TMPDIR=/tmp PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch export ORACLE_SID ORACLE_BASE ORACLE_HOME LD_LIBRARY_PATH TMP TMPDIR PATH SQL> !echo $ORACLE_BASE /opt/oracle SQL> !echo $ORACLE_HOME /opt/oracle/product/23c/dbhomeFree [root@oracle21 ~]# su - oracle [oracle@oracle21 ~]$ sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon Jul 31 15:02:53 2023 Version 23.2.0.0.0 Copyright (c) 1982, 2023, Oracle. All rights reserved. Connected to: Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0
[oracle@oracle21 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /opt/oracle/product/23c/dbhomeFree/network/admin/tnsnames.ora # Generated by Oracle configuration tools. FREE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-9U3H4SE)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREE) ) ) LISTENER_FREE = (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-9U3H4SE)(PORT = 1521))-rw-r-----. 1 oracle oinstall 371 Jul 31 14:12 listener.ora -rw-r-----. 1 oracle oinstall 415 Jul 31 14:18 tnsnames.ora[oracle@oracle21 admin]$ cat listener.ora # listener.ora Network Configuration File: /opt/oracle/product/23c/dbhomeFree/network/admin/listener.ora # Generated by Oracle configuration tools. DEFAULT_SERVICE_LISTENER = FREE LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-9U3H4SE)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )[oracle@oracle21 ~]$ sqlplus sys/sys@//localhost:1521/freepdb1 as sysdba SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon Jul 31 15:05:18 2023 Version 23.2.0.0.0 Copyright (c) 1982, 2023, Oracle. All rights reserved. Connected to: Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0 SQL>
======
How to rename a Pluggable Database?
show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 FREEPDB1 READ WRITE NO col FILE_NAME for a50 select FILE_ID,FILE_NAME from dba_data_files; FILE_ID FILE_NAME ---------- -------------------------------------------------- 12 /opt/oracle/oradata/FREE/FREEPDB1/system01.dbf 13 /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf 14 /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf 15 /opt/oracle/oradata/FREE/FREEPDB1/users01.dbf SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 FREEPDB1 READ WRITE NO alter pluggable database FREEPDB1 close immediate; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 FREEPDB1 MOUNTED alter pluggable database FREEPDB1 open restricted; SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 FREEPDB1 READ WRITE YES SQL> connect FREEPDB1 as sysdba SQL> connect FREEPDB1 as sysdba Enter password: Connected. alter pluggable database FREEPDB1 rename global_name to anuj; SQL> alter pluggable database FREEPDB1 rename global_name to anuj; Pluggable database altered. show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ANUJ READ WRITE YES SQL> alter session set container=ANUJ ; Session altered. col FILE_NAME for a50 select FILE_ID,FILE_NAME from dba_data_files;SQL> SQL> FILE_ID FILE_NAME ---------- -------------------------------------------------- 12 /opt/oracle/oradata/FREE/FREEPDB1/system01.dbf 13 /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf 14 /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf 15 /opt/oracle/oradata/FREE/FREEPDB1/users01.dbf All the fils are in same location !!!! mkdir /u02/oradata/CDB2/hugo SQL> ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/system01.dbf' TO '/opt/oracle/oradata/FREE/ANUJ/system01.dbf'; ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/system01.dbf' TO '/opt/oracle/oradata/FREE/ANUJ/system01.dbf' * ERROR at line 1: ORA-00439: feature not enabled: online move datafile SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 ANUJ READ WRITE YES SQL> shutdown immediate ; Pluggable Database closed. SQL> startup; Pluggable Database opened. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 ANUJ READ WRITE NO SQL> SQL> !oerr ora 439 00439, 00000, "feature not enabled: %s" // *Cause: The specified feature was not enabled. // *Action: Do not attempt to use this feature. SQL> Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0 [oracle@oracle21 ~]$ sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Mon Jul 31 18:47:41 2023 Version 23.2.0.0.0 Copyright (c) 1982, 2023, Oracle. All rights reserved. Connected to: Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release <<<<< This feature in not enabled . Version 23.2.0.0.0
Friday, 28 July 2023
sqlplus output in html
sqlplus output in html sqlplus -S -M "HTML ON TABLE 'BORDER="2"'" '/ as sysdba' @/home/oracle/DBA/dd.sql > dd.html echo "Database Size -- " | mailx -s "Database Size" -a dd.html anuj@xxxx.com This will send the email with attachment
Subscribe to:
Posts (Atom)
