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:
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';
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
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?
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!!!
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
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.
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]........
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.
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.
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.
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"
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
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.
'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.
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...
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"
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.
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
