SQL & PL/SQL :: Generate Command To Rename Log File From ASM To Non-ASM

Apr 9, 2013

I am in the process of recovering a database from an ASM environment to a non-ASM environment using RMAN. I've got a query to generate the command to RENAME the file:

SELECT 'ALTER DATABASE RENAME FILE '''||MEMBER||''' TO '''||'/u01/data/dev'||
SUBSTR(MEMBER,
INSTR(MEMBER, '/', -1, 1),
INSTR(MEMBER, '.', 1, 1) - INSTR(MEMBER, '/', -1, 1)) ||
DECODE(SUBSTR (MEMBER, 1, INSTR(MEMBER, '/', 1, 1) -1), '+DATA', 'a', '+FRA', 'b', 'c') ||'.log'';'
FROM V$LOGFILE;

When I run the query, this is an example of the result I get:

ALTER DATABASE RENAME FILE '+FRA/db1/onlinelog/group_3.259.766075171' TO '/u01/data/dev/group_3b.log';
ALTER DATABASE RENAME FILE '+DATA/db1/onlinelog/group_1.262.766075829' TO '/u01/data/dev/group_1a.log';

But I want to replace the "group" with "redo" and get the below result:

ALTER DATABASE RENAME FILE '+FRA/db1/onlinelog/group_3.259.766075171' TO '/u01/data/dev/redo_3b.log';
ALTER DATABASE RENAME FILE '+DATA/db1/onlinelog/group_1.262.766075829' TO '/u01/data/dev/redo_1a.log';

View 7 Replies


ADVERTISEMENT

Windows :: Rename Command In Dos

Mar 13, 2011

I want to rename

cf(1)
cf(2)
cf(3)

to

cf1
cf2
cf3

in DOS.

View -1 Replies View Related

Server Administration :: ORA-01111 / Name For Data File 636 Is Unknown - Rename To Correct File

Nov 3, 2012

i have two tablespaces dictionary managed (SYSTEM,APPLSYSX) i tried to change to locally cause it will cause problem in future when trying to run OATM migration.i did it successfully on APPLSYSX,when i did it on system upon oracle procedure.i have to change all tablespaces to read only when i did that with tablespace APPLSYSD(alter tablespace APPLSYSD read only) i received errors

SQL> alter tablespace APPLSYSD READ ONLY;
alter tablespace APPLSYSD READ ONLY
*
ERROR at line 1:
ORA-01230: cannot make read only - file 636 is offline
ORA-01111: name for data file 636 is unknown - rename to correct file
ORA-01110: data file 636: '/vol5u/oracle/prddb/9.2.0/dbs/MISSING00636'
i have not this file on the OS

View 1 Replies View Related

Reports & Discoverer :: Rename Report Output File

May 10, 2012

I'm calling myreport.rdf from a form using the rwservlet and destype=mail and desformat=pdf. The report file is attached to the email as myreport.pdf. However, I want to have the output file named myfilename.pdf. Is there any way to accomplish this short of using destype=file and then renaming it and sending that file?

View 2 Replies View Related

SQL & PL/SQL :: Query To Generate SET NEWNAME Command

Apr 7, 2013

I want to run a query to generate command to rename ASM files:

This is how the ASM data looks:

+DATA/db1/datafile/report.180.248540158

This is how I want the command to look like:

SET NEWNAME FOR DATAFILE 1 TO '/u02/data/dev/db1/report.180.dbf';

How can I write the query, using SUBSTR and INSTR to generate the command:
e.g. - SELECT 'SET NEWNAME FOR DATAFILE '||FILE#||' TO '''||'/u02/......

To look like:

SET NEWNAME FOR DATAFILE 1 TO '/u02/data/dev/db1/report.180.dbf';

View 7 Replies View Related

Generate A Text File

Apr 28, 2008

i have be requested to create a .txt file. Its for a program that will read the txt file i create to produce a letter i.e I will be extracting, TITLE, FORENAME, SURNAME etc

MR
JOE
BLOGGS

Here is the my code so far,

SELECT
LPA_INPUT.INPUT_TITLE,
LPA_INPUT.INPUT_SURNAME,
LPA_HISTORY.LPA_AMT,
LPA_HISTORY.ELIG_RATE,
LPA_HISTORY.RATE_REBATE,
LPA_HISTORY.RR_AMT,
LPA_HISTORY.LPA_APPLIC,
LPA_HISTORY.LPA_AMT
FROM LPA_HISTORY, LPA_INPUT
WHERE LPA_HISTORY.CLAIM_NO = LPA_INPUT.CLAIM_NO
----------------------------------------------------
Iv been asked to have these eight fields looping over and over for all the records in the database, So im not sure how to do that and how to generate it in a txt file?! I have to produce it in a script and not by a Export/Import wizard in sql server mangement studio!!!

View 11 Replies View Related

SQL & PL/SQL :: Image In XML To Generate Excel File

Jul 8, 2010

how to attach an IMAGE in XML and that will be used in PL SQL procedure to generate the Excel file with the IMAGE. I cannot able to save the xml as excel with the image

View 3 Replies View Related

Forms :: How To Generate Excel File

Jun 12, 2012

I want to gerate the excel(Office 2010)file using oracle forms 6i. How we can achieve this with forms 6i?

View 4 Replies View Related

Generate Trace File With Required Name

Jul 26, 2013

As a sys user i want to trace a user session. so i am using 

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE); 

to trace. but this trace file is mixing with another trace files in udump. did some Google and found that we can use

ALTER SESSION SET TRACEFILE_IDENTIFIER = "MY_TEST_SESSION".

but this will generate trace file for sys user account not for the desired user session. how we can define trace file name for desired user session trace. 

View 2 Replies View Related

PL/SQL :: Generate Excel File And Send It As Attachment

Apr 10, 2013

I have a requirement to send email with attachment using PL/SQL procedure of 10g database. We will be querying data from database, generating excel(csv) file and attaching to email. How can I achieve this goal.

I have a procedure that will send email as HTML but it has limitations of sending 32K data.

CREATE OR REPLACE procedure send_email(
p_to            in varchar2,
p_from          in varchar2,
p_subject       in varchar2,
p_text          in varchar2 default null,
[code]........   

View 21 Replies View Related

Reports & Discoverer :: Generate PDF File With Password Protection?

Aug 18, 2010

Is it possible to generate .pdf file with password protection.

View 2 Replies View Related

Reports & Discoverer :: Unable To Generate To File XML In Character Mode

Feb 17, 2012

I have a Report which run in character mode format, well i cannot convert or Generate to File as a XML File.

XML Conversion Error:

REP-1401: 'cf_1formula': Fatal PL/SQL error occurred. ORA-01403: no data found

PDF Conversion Error:

REP-1920: Character mode runtime incompatible with DESFORMAT of PDF, HTML, HTMLCSS, or RTF

But, when i change Character Mode to Bitmap or Default i can able to Generate to File.

So i Like to know is it possible to Generate to File in Character Mode?

View 1 Replies View Related

Reports & Discoverer :: How To Generate Matrix Report To Excel File

Aug 22, 2007

how to generate matrix report to excel file. i have done this . but the exact layout is not getting displayed in the excel file. I have attache the layout i ma trying to export to excel.

View 8 Replies View Related

SQL & PL/SQL :: Generate A Select Query In Runtime And Store Results Of It Into A File?

Aug 15, 2011

I need to generate a select query in runtime and store the results of it into a file.Each time the column name and table name in the query will differ.Now im able to generate the select query through for cursor but problem is to store the results to the file.I tried using plsql table,im able to get the values to that table and store the results to a file,but the results of the query is more then 10000 lines (it might increase also)where only 4000 characters where able to store in the plsql table.so rest of them are not stored in the file.

View 3 Replies View Related

Forms :: Command To Run SQL File?

Mar 8, 2011

i want to run sql file by forms...is there command do that

View 3 Replies View Related

Forms :: Generate File From Database Into Server And Copy It To Client Machine

Feb 2, 2012

I have a forms that generate a file from database into database server and copy it to client machine by the follow process:

IF webutil_file_transfer.Is_AS_readable(V_server_way || V_file_name) THEN

l_success := webutil_file_transfer.AS_to_Client
(clientFile => :V_screen_way

[code]...

But is showing the erro ora-105100 in one client machine

View 9 Replies View Related

Reports & Discoverer :: Generate A PDF Or HTML File From A Report Previewer / REP-0999 Error

Sep 16, 2002

while trying to generate a PDF or HTML file from a report previewer, I came upon a REP-0999 error message and the previewer was closed!

View 8 Replies View Related

Which Command Use To Set File Storage In Oracle

Oct 26, 2006

I am new to oracle which command do I use to set the file storage in oracle? I learnt the we have the option of specifying the storage pattern as per our queries.

View 2 Replies View Related

SQL & PL/SQL :: BFILENAME / Unable To Get The File Using The Command?

Oct 16, 2012

i am using a SQL statement as below but unable to get the file using the command :

SELECT bfilename('$APPLCSF/out','XXMSPOSTD_3798178_1.pdf') FROM dual;

View 6 Replies View Related

Format Output File By Command Spool

Jan 20, 2011

I use sqplus in oracle to output the command output to text file .

I use below set environment variable.

SQL> set echo off;
SQL> set linesize 3999;
SQL> set feedback off;
SQL> set feedback off;
SQL> set termout off;
SQL> set pagesize 0;
SQL> spool mapping.txt
select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;

In ouput file , it look like

SQL> select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
060010007 10007
:
:
:
SQL> spool off;

any setting or command that allow me to remove the first sql command line" SQL>select XXXX" and the last command "SQL>spool off" after start up the "spool mapping.txt"

View 1 Replies View Related

Import Data From Dmp File Using Command IMPDP?

May 2, 2011

I am trying to import data from a dmp file using the command IMPDP Here is the command :

impdp
USERID= core_edb_20112_ct/local
DIRECTORY=dir_core20112
DUMPFILE=CORE_EDB_20112_CT_20110426.DMP
LOGFILE=log_core02112_1.log
SCHEMAS=core_edb_20112_ct
REMAP_SCHEMA=core_edb_20112_ct:core_edb_20112_ct
PARALLEL=4
METRICS=Y TRANSFORM=OID:N
TRANSFORM=SEGMENT_ATTRIBUTES:N
REMAP_TABLESPACE=C64_EDB_TS:C64_EDB_TS

I am trying to import data in the following user : core_edb_20112_ct/local
This user is already created , using the tablespace named C64_EDB_TS
The dmp file resides in the location dir_core20112 ( e:\oracle)

I am getting the following error while i try to import

Import: Release 11.2.0.1.0 - Production on Mon May 2 12:47:54 2011

Copyright © 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-31694: master table "CORE_EDB_20112_CT"."SYS_IMPORT_FULL_01" failed to load/
unload
ORA-02354: error in exporting/importing data
ORA-02368: the following file is not valid for this load operation
ORA-02369: internal number in header in file e:\oracle\core_edb_20112_ct_20110426.dmp is not valid.

The DMP is copied from a different network location into the local drive where the command is running.

View 1 Replies View Related

SQL & PL/SQL :: Redirect Output Of A Command To Variable Or File?

Jun 18, 2010

I have a command which get the versions of the schema's for which I dont have access to. It is a utility.

the command goes:

SQL>EXEC ite_dta_tool.dta_open_utl.get_db_version('HH2_SGH21_SS')
DATABASE_VERSION_ID
-----------------------
hh2_ax_db_0.8.1.2.0
Elapsed: 00:00:00.25
SQL>

'HH2_SGH21_SS' is a schema I dont have access to. I have a whole bunch of schemas like this and would like to get versions of all those schemas putting it in a loop in pl/sql.

1: whats the best way get this output to a variable.

2: whats the best way to get the output to a file.

View 8 Replies View Related

PL/SQL :: Data To Existing File Using Spool Command?

Jul 19, 2012

I am calling a stored procedure from a shell script. This stored procedure is having a CLOB object as an OUT parameter. How to write the data in CLOB object a existing file which is there in my client system. Below is the shell and sql scripts.

Shell script
( echo "hello" ) > /root/file.txt
sqlplus -s $user/$pass@$tns @/root/proc.sql /root/file.txt << EOF
EOFSQL script
set pages 0
set trimspool off

[Code]...

spool offThis code writes contents of the OUT variable to file.txt, but my existing data ('hello') is lost.

I figured the it...Use append in the spool command...

View 1 Replies View Related

Server Utilities :: Load / Unload Csv File Using Sql Command?

Apr 5, 2010

load/unload csv file using sql command

View 1 Replies View Related

Client Tools :: Format Output File By Command Spool?

Jan 20, 2011

I use sqplus in oracle to output the command output to text file .

I use below set environment varialble.

SQL> set echo off;
SQL> set linesize 3999;
SQL> set feedback off;
SQL> set feedback off;
SQL> set termout off;
SQL> set pagesize 0;
SQL> spool mapping.txt
select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;

In ouput file , it look like

SQL> select C_SIM_MSISDN,C_SIM_IMSI from RCA_SMART_CARD order by C_SIM_MSISDN;
060010007 10007
:
:
:
SQL> spool off;

any setting or command that allow me to remove the first sql command line" SQL>select XXXX" and the last command "SQL>spool off" after start up the "spool mapping.txt"

View 8 Replies View Related

Windows :: How Search Specific String In PDF File From Command Prompt

Nov 21, 2012

I have a requirement in which i have list pdf files(from thousands of pdf files) having particular string from a command prompt.I have tried find command but that has some limitation. its working upto 3 digit string only.how can i acheive this.remember i have to do this through command prompt only.

View 1 Replies View Related

Backup & Recovery :: 11g R2 - How To Update Control File With Switch Command

Oct 3, 2011

I have restored all datafile to new location (that is ASM) earlier it was on file system using set new name command (RMAN). Now when i'm trying to update control file with swicth command it is prompting error. Version is 11g R2.

RMAN> SWITCH DATAFILE ALL;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "all": expecting one of: "double-quoted-string, integer, single-quoted-string"
RMAN-01007: at line 1 column 17 file: standard input

View 3 Replies View Related

Performance Tuning :: Command To Transfer File From Local To Remote Server

Apr 21, 2011

I ran a scp command to transfer a file from local server to a remote server. when I am trying to kill the process, its giving an error.

ksh: kill: 19258: No such process

If I do a ps -ef | grep scp its showing the process running, but if i try to kill it, its showing me error.

View 7 Replies View Related

SQL & PL/SQL :: How To Rename A Index

Aug 8, 2011

How can i rename a index? is there a way except for drop and create.

View 2 Replies View Related

SQL & PL/SQL :: RENAME Table In Oracle 8i?

Nov 21, 2011

how to rename table in oracle 8i ?

ALTER TABLE olname RENAME TO nwname;

Above command is not working.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved